Overview
For this individual assignment, you will use skills acquired through practical laboratory exercises to automate a business process, and to visualize the impact of the automation.
You should use Microsoft Excel (or equivalent open-source software) for this assessment task. Important: This assignment specification is generated just for you. Do not distribute this specification.
Timelines and Expectations
Percentage value of task: 25%
Due: Sunday, Week 11 (26th May, 2024, 11:59pm)
Learning Outcomes Assessed
The following course learning outcomes are assessed by completing this assessment:
A1. Prepare a basic solution to a business problem;
A2. Select appropriate IT solutions for business functions;
A3. Apply business information software for data visualization and analysis purposes. S1. Write basic programming logic;
S3. Interpret and construct representations of business data flow and processes;
K8. Outline the basic principles of programming.
Assessment Details
For this assignment, you will complete the following set of tasks using Spreadsheet, and create a report to describe your work.
Ensure you submit Spreadsheet files created for ALL tasks, along with your report.
Scenario Details
Trendy Gallery is a small bag manufacturer, specializing in creating custom, one-of-a-kind backpacks bag. They exclusively sell their product through their online store.
Hours of operation
Team members work the following shifts:
April works 6.5 hours on Mondays, Tuesdays;
Bernard works 6.5 hours on Wednesdays, Thursdays and Fridays; and
Caitlin works 2.5 hours every weekday.
Additionally, April and Caitlin share responsibility for managing the Trendy Gallery social media presence, with April working one extra hour in the evenings on Monday, Tuesday and Wednesday, and Caitlin doing the same on Thursday, Friday and Saturday.
Costs
April and Bernard, as skilled manufacturing staff, cost $32 per hour to employ.
Caitlin, a part-time employee, costs $21 per hour to employ.
All staff are also entitled to four weeks of paid annual leave, during which a casual replacement is required at a cost of $40 per hour.
Fixed costs such as rent and insurance are $3000 per year, and utilities costs are $205 per month. Using Spreadsheet, create a spreadsheet called ITECH1100_A2_Trendy_Gallery.xlsx.
Task 1 – Costs of operating the business
Create a sheet that calculates the projected annual outgoing costs of running Trendy Gallery.
Your spreadsheet should be configured such that the working hours, hourly rates, and fixed and utility costs can be varied easily.
Using a word processor (Word, Libre Office or Google docs) to document the ratio of each type of costs in running the business.
Manufacturing one bag
Trendy Gallery receive requests through their online store, and Caitlin responds with a quote, after consulting with a member of the manufacturing team. If the customer confirms their order, April and Bernard manufacture the ordered products, and Caitlin organizes delivery.
Currently, the process for generating a quote is entirely subjective, based on the requested material and size. Staff members quote an amount based on their personal experience.
For several months, the staff has been recording the quote date, material, size, quote amount, and material cost for each order. Since each product is hand-made, the cost of materials may vary between similar orders.
If a quote was not accepted by the customer, then material cost is not included.
This file (in .csv format) is available for download on Moodle.
Task 2 – Price variation
In the same Spreadsheet, create a new sheet to process the history of orders spreadsheet and use appropriate charts to visualize:
How some aspect of quoting or material cost has changed over time; and
How the quote amount differed for each staff member.
You will need to find a way to use Spreadsheet to associate each quote with a staff member.
Describe your approach and your findings in your report (approximately 250-400 words). Your findings may include preferences or any other hidden patterns in the data.
Process Automation
Task 3 – Price Setting
In the same Spreadsheet file, create a new sheet to calculate the quote amounts. Using the historical data record of prices offered, determine sets of prices which meet the objectives of the following scenarios:
Scenario one: Total quote prices are approximately the same as for the historical data, ie: calculate the proposed nylon, canvas and leather prices based on the vaues in the historical data.
Scenario two: Calculate the quote prices for all bags to ensure enough revenue is generated to cover the total operating costs of the business. You must take into account the costs of materials.
Validate your quoting approach by applying your formula to historical data.
Task 4 – Automation
In the same Spreadsheet file, create a new sheet that allows a member of the Trendy Gallery team to enter the backpacks bag material and whether the client wants express shipping before giving a quote amount. The system should then display the quote amount, the shipping cost and the total cost. The cost of a standard shipping is $10, while an express is $20. The automation process is described as a BPMN below.
Ensure that you include enough text and formatting to make your spreadsheet usable by a member of the Trendy Gallery team (or by a University lecturer or tutor).
You may base your quote amount either on historical averages (scenario one), or to ensure that Trendy Gallery breaks even (scenario two).
Justify your approach in your report (approximately 150 words).
Bonus challenge task (optional!)
Disclaimer: This task is 100% optional, and you can receive full marks without attempting or completing it. It is intended to be a challenge if you are interested in such things, and the marks available do not reflect the significant research and effort required to implement it correctly. Tutors will not provide significant assistance for this challenge task.
If you attempt this task, you must indicate it this in your report, otherwise it may not be marked
Optional task
Reimplement Task 1 and Task 4 using either Python or HTML+JavaScript.
Write a brief overview of how to run your solution, and attach a zip file containing your code to your ePortfolio page.
There are no partial marks awarded for this bonus task – you must complete all features to attain the bonus marks.
It is possible to attain full marks for this assignment without completing this challenge task.
Further details
Assignment support
This assignment is supported by the first 7 lectures and labs. Work on the assignment should be spread over several weeks after the relevant lab has been mastered.
Submission
All files should be submitted to Moodle by the due date and time.
To be eligible for assessment, submissions must include an Excel spreadsheet (or spreadsheets) containing the calculations for each task that is included in the assignment report. Each task in the spreadsheet (or spreadsheets) must be clearly identified.
Marking Criteria/Rubric
Refer to the attached marking guide.
Feedback
Feedback will be supplied through Moodle. Authoritative marks will be published through fdlMarks
Academic Integrity
To submit your assessment task, you must indicate that you have read and understood, and comply with, the Federation University Australia Academic Integrity and Student Plagiarism policies and procedures.
You must also agree that your work has not been outsourced, and is entirely your own except where work quoted is duly acknowledged. Additionally, you must agree that your work has not been submitted for assessment in any other course or program.
Marking Guide: Assignment 2
Feature
Criteria
Maximum
Obtained
Operating costs
Appropriate projection calculation – no manual calculation
3
Configurable hours, rates, fixed annual and monthly costs
2
Description of findings
1
Sales team offers
Visualization of prices offered per staff member
3
Visualization of change in prices over time
3
Description of findings
2
Price setting
Appropriate nylon, canvas and leather prices for scenario 1
2
Appropriate nylon, canvas and leather prices for scenario 2
2
Description of strategy
1
Verification of findings against historical data
1
Automation
Spreadsheet accurately models the BPMN process with clear instruction
3
Spreadsheet has clear instructions
1
Description of automation
1
Bonus optional
challenge task
Faithful reimplementation of tasks 1 and 3 using Python or HTML+JavaScript
(+2)
Total:
25