Simulation
This is a work integrated assessment item. The tasks are similar to what would be carried out in the workplace.
The Wallabies Football Club gains significant revenue from ticket sales at each game played at home during the season. The sale of programs for these games also adds to profitability. Each program costs $2.00 to produce and sells for $5.00. Any programs unsold at the end of any game are sent to a recycling center and do not produce any revenue.
Records of the programs sold for each game show the following:
Number of Programs Sold Probability
2300 0.10
2400 0.20
2500 0.30
2600 0.25
2700 0.15
(a) Your manager has asked you as the management accountant for the club to determine the profitability of program production. In particular you have been asked to investigate two strategies where the number of programs to be printed are either (i) the number demanded the previous match, or (ii) 2500 every match. You decide to use Excel to simulate the sale of programs at 10 games in a season together with the profit or loss on programs for each game under scenarios (i) and (ii). Include a calculation of the total profit/loss for the season and the average profit/loss per game.
Hints: Your model should have 8 columns: Game #, RN Sales, Demand, Print #, Sales Units, Sales Revenue, Production Costs, Profit/Loss. The model must be completely formula driven - there must be no data in the model or the model formulas – all data should be in a data input section above the model. An IF or MIN function is required in the formulas in the Sales Units column. After completing the model you can vary the results by pressing F9 (recalculate) a number of times to view such variations resulting from changes in the random numbers generated.
Because there are two scenarios to simulate you must have two copies of your simulations. Under scenario (i) you will have to generate a dummy sales demand to kick start the process. This would not be necessary under scenario (ii).
Show the data and the model in two printouts: (1) the results, and (2) the formulas. Both printouts must show row and column numbers and be copied from Excel into Word. See Spreadsheet Advice in Interact Resources for guidance.
(b) To check on your simulation conclusions use marginal analysis to determine the optimum number of programs to print.
(c) Write a report to your manager explaining which of the two production strategies you would recommend and why. You may support your report by including reference to the profits under each strategy, noting any limitations to the methods you have adopted to analyse the situations.
The report must be dated, addressed to the Manager and signed off by you.
(Word limit: No more than 150 words)


Regression Analysis and Cost Estimation
A local real estate agent asks you to help estimate the sale prices of homes. The agent believes that the floor space area of the house and the number of bedrooms help explain selling prices, and has collected the following data from past sales.
House Price m2 Bedrooms
1 $304,000 200 3
2 600,000 370 4
3 456,000 270 3
4 344,000 250 3
5 704,000 400 5
6 356,000 260 3
7 320,000 230 2
8 336,000 180 2
9 560,000 350 5
10 584,000 330 4
11 440,000 290 3
12 448,000 300 4
(a) Using the high-low method to estimate a selling price function based on square metres of floor space, what would be the estimated selling price (to the nearest $) for a house with 275 m2 of floor space?
(b) Using Excel, perform three regression analyses to regress selling price against square metres (m2), then number of bedrooms, then against both of them simultaneously. State the cost equation from each. Analyses and comment on the results of each regression as you perform it and determine the best one to use as a basis for future use.
(c) Using the best regression what would be the predicted selling price in a month when there were 300 m2 of floor space and 3 bedrooms?