Recent Question/Assignment
The State Opera Theatre gains significant revenue from ticket sales at each opera performed during the season. The sale of souvenir programs for all performances of each opera also adds to profitability. Each program costs $1.60 to produce and sells for $4.00. Any programs unsold at the end of any opera are donated to a recycling centre and do not produce any revenue.
Records of the programs sold for each opera show the following:
Number of Programs Sold Probability
2500 0.15
2750 0.22
3000 0.24
3250 0.21
3500 0.18
(a) 12 marks
Your manager has asked you as the management accountant to determine the profitability of the souvenir program production. In particular you have been asked to investigate the strategy where the number of programs to be printed should equal the number demanded at the previous opera. You decide to use Excel to simulate the sale of programs at 10 operas in a season together with the profit or loss on programs for each opera. You will have to generate a dummy sale for a “previous†opera (Opera 0) to begin with so that you have a starting point for Opera 1. Include a calculation of the total profit/loss for the season and the average profit/loss per opera.
Hints: Your model should have 8 columns: Opera #, RN Demand, Demand, Production #, 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.
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) 8 marks
The manager has also asked you to compare the strategy of printing the number of programs demanded at the previous opera [results already calculated in (a)] with an alternative strategy of printing 3000 programs for each opera. Calculate the profit or loss in each of the 10 operas and the average profits for the whole season when adopting this second strategy. You can do this by copying your simulation model in (a) into a second worksheet and adjusting the model where necessary to incorporate any additional or different calculations. You may generate different random numbers for (b) but press F9 a number of times to see the comparisons between (a) and (b).
When you are satisfied with the results 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.
(c) 5 marks
Write a report to your manager explaining which of the two production strategies [the one in (a) or the one in (b)] 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)
Records of the programs sold for each opera show the following:
Number of Programs Sold Probability
2500 0.15
2750 0.22
3000 0.24
3250 0.21
3500 0.18
(a) 12 marks
Your manager has asked you as the management accountant to determine the profitability of the souvenir program production. In particular you have been asked to investigate the strategy where the number of programs to be printed should equal the number demanded at the previous opera. You decide to use Excel to simulate the sale of programs at 10 operas in a season together with the profit or loss on programs for each opera. You will have to generate a dummy sale for a “previous†opera (Opera 0) to begin with so that you have a starting point for Opera 1. Include a calculation of the total profit/loss for the season and the average profit/loss per opera.
Hints: Your model should have 8 columns: Opera #, RN Demand, Demand, Production #, 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.
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) 8 marks
The manager has also asked you to compare the strategy of printing the number of programs demanded at the previous opera [results already calculated in (a)] with an alternative strategy of printing 3000 programs for each opera. Calculate the profit or loss in each of the 10 operas and the average profits for the whole season when adopting this second strategy. You can do this by copying your simulation model in (a) into a second worksheet and adjusting the model where necessary to incorporate any additional or different calculations. You may generate different random numbers for (b) but press F9 a number of times to see the comparisons between (a) and (b).
When you are satisfied with the results 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.
(c) 5 marks
Write a report to your manager explaining which of the two production strategies [the one in (a) or the one in (b)] 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)
Looking for answers ?
Recent Questions
Assessment 2: (20%) Case Study Presentation Due Date weeks 10This assessment builds on your theoretical knowledge and tests your ability to apply this knowledge to practice.you are required to formulate...Assessment 2Assessment 2Report: 30%Word count: 1400 – 1600 wordsDue date: Sunday of Week 8DescriptionYou are playing the role of a community service worker supporting a young adult son who is 18 years...1500-1800 wordsInstructions From TutorPlease download the eTask3 spreadsheet from Canvas and save the eTask spread as an Excel file. The main content of the force method is taught in Lectures 6,7&8.eTask3.1 is a...ASSIGNMENT INSTRUCTIONSAssessment Practical assessmentAssessment code: 011Academic Year: 2023/2024Trimester: 2Module Title: Critical Perspectives on Cross-Border BusinessModule Code: MOD009194Level: 6Module...Information Systems Analysis & DesignAssignmentDue date: Week 5 Friday (5 Apr 2024) 11:59 pm AESTWeighting: 30%Length: 1,200 to 1,500 words (penalty will apply if the length is outside this limit)Assessment...Problem:University of Technology Sydney 48353: CONCRETE DESIGN Autumn 2024 - Assignment 2 (13 Marks)The figure illustrates the cross-section of a reinforced concrete column with a hexagonal shape (equal...Show All Questions