Task 1: Spreadsheet Auditing and Design
Dan Dodgy, the proprietor of an online camping goods store, has heard of your great skill in spreadsheet auditing and design. He has designed his own spreadsheet that will keep track of his sales, cost of goods sold and profit for each of the five items his store carries. He thinks his spreadsheet is pretty good, but his nerdy nephew knows that it is not and has convinced his uncle to ask you to review the spreadsheet for errors.
You meet, virtually of course, with Dan and manage to get the following information from him prior to your investigation of his spreadsheet:
- Dan sells only five items; Coolman LED Lantern, Fishing Tackle Bag, High Back Chair, Warm Night Sleeping Bag, and First Aid Kit.
- Each of the different items costs Dan a different amount to buy and the retail price differs. The table below is a summary of the items:
|ITEM||COST TO BUY ($)||SELLING PRICE ($)|
|Coolman LED Lantern||$12.50||$13.00|
|Fishing tackle bag||$85.00||$155.00|
|High back chair||$129.75||$200.00|
|Warm night sleeping bag||$156.00||$185.00|
|First aid kit||$53.47||$95.00|
Part A – Error Detection Method
Prepare an outline that explains the method you will use to audit the spreadsheet Dan has given you. This should be explained in such a way so as to be repeatable.
Part B – Error Report
Your first task is to examine and audit the spreadsheet that Dan designed (DansCampingGoods) for the last three months. You are trying to find any errors that exist in the spreadsheet. When you find an error, you should note it down, explain why you believe it to be an error and suggest how that error could be fixed. In the event, you find a value that seems as though it may be result of a data entry error, you should note this as a potential error, so the source data can be checked at a later time.
Your error report should look something like this:
|CELL, OR RANGE OF CELLS IN ERROR||NATURE OF ERROR (OR POTENTIAL ERROR))||SUGGESTED REMEDY|
Part C – Fix the Spreadsheet
Redesign the spreadsheet so that it still shows the information that Dan requires. Make sure that your redesigned spreadsheet conforms with best practice in this unit.
TO SUBMIT FOR THIS TASK:
1.One page word processed report outlining the error detection method.
2.Error report table
3.Amended/corrected/improved spreadsheet for Dan.
Task 2: Decision Table
Dan’s Scooter Raceway
Dan is rapidly becoming very wealthy indeed. He is a keen scooter rider and so has used some of his profits from his camping goods business to set up Dan’s Scooter Raceway (see http://www.youtube.com/watch?v=gsf6T-hg2es ).
He has set up the Members-only Raceway with a number of different fee levels depending on several criteria. For example, if the Member is over 18, then the fee charged is at level F1. If they are under 18 and accompanied by an adult member, then they would also be charged F1. If the member is under 18 and not accompanied, they will be charged F2. If they have had an accident, then they will be charged an additional 5%. Finally, Members overs 55 will get a 10% discount.
What do you have to do?
Part A – Decision Table
Create a decision table based on the narrative above. If you think the description above is incomplete or inconsistent, then you should clearly identify and describe these issues. You should include the initial decision table and the simplified decision table that does not include any redundant or impossible rules.
Part B – Explanation
You should include an explanation as to how the decision table should be used. This explanation will be given as training to the staff on the front gate.
Task 3: Optimisation
Dan Dodgy has now decided to branch out into manufacturing surfboards. His model range is currently somewhat limited in that he only manufactures one type of board, which he calls the Dodgy. He is now thinking about adding a second board to the range, the Super Dodgy; he is really excited because he thinks he will now be able to become wealthy and retire before he turns fifty.
His problem is that he still has limited production facilities and needs to know how many of each of the boards to produce to maximise his profit. He can make $375 profit from a Dodgy and $732 profit from a Super Dodgy, so is tempted to only produce Super Dodgies. However, as the Super Dodgy is more expensive for buyers than the Dodgy, Dan thinks he will only be sell a total of 30 Super Dodgies, but, according to his contract with an international surfboard company, must produce at least 125 Dodgies in a year. A Dodgy takes 22 hours of labour to produce and a Super Dodgy 30. The maximum number of hours available for manufacturing is 3600 (i.e., 2 labourers at 37.5 hours per week for 48 weeks per year). The material costs for a Dodgy are $275 per unit, and the Super Dodgy $325. The total available to spend on materials is $45,000. Your job is to advise Dan how many of each of the models to manufacture in order to maximise his profit.
Part A – Spreadsheet
You will need to create a spreadsheet for Craig with the following functionality:
1.Calculate the optimal production levels of both models given the scenario above.
2.Calculate the optimal production levels of both models if Craig decides he must make a minimum of 150 Dodgies, and thinks he can sell 45 Super Dodgies by adding an additional member of staff (i.e., an additional 1800 hours of labour).
NB: Craig should be able to see the impacts of both scenarios at a glance.
Part B – Report
You also need to write a report for Craig that explains the following:
- What the spreadsheet does
- What it means for him (i.e., what does the spreadsheet demonstrate)
How he could use it to try different scenarios