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
Program: Master of Information SystemsAssessment 1: Portfolio 1 (Session 2 Topic: Input, Processing andOutput) Semester 2: Block 8-2024Course Code: MBIS4003 Course Title: Software Development Total Marks:...AssessmentAssessment item 2—Assignment 2Due date 11:45pm on Sunday, 24Nov.2024Weighting 35%Length Not applicableObjectivesThis assessment item relates to the unit learning outcomes numbers 1, 2 and 3 as...Plan, construct and write a long-form essay on a particular area of media research that interests you. Using an established theoretical framework, explain how your research offers new insights into the...DescriptionIn this assessment, you are required to demonstrate that you understand how an Older Adult can develop mental health problems associated with cognitive decline leading to cognitive impairment...School of Engineering, Design and Built EnvironmentENGR7008 Fire Engineering Design and AssessmentAssignment 4: Peer Review of Fire Engineering ReportASSIGNMENT 4PEER REVIEW OF A FIRE ENGINEERING REPORTSee...Structural Analysis Etask 5The aim of this assessment task is to evaluate the appropriate use of the Force Method and Moment Distribution Method for structural analysis• Please download the eTask4 from...Purpose:The purpose of this task is for the student to demonstrate how to create a holistic plan of care that is person-centred, trauma-informed, and recovery-oriented in focus.Description:In this written...Show All Questions