ACCM4100 Cost Volume Profit Analysis Using Excel: Report to Management Assessment 3 Answer

Assessment 3 Information

Your Task

Your task is to effectively use spreadsheet software (Excel) to provide cost-volume-profit analysis and provide a report to senior management which uses your understanding of management accounting.

1. You are required to read the scenario described below
2. You are required to use a spreadsheet to answer the individual questions below
3. You are required to provide conclusions and make recommendations to senior management based on your analysis

Learning Objectives

After completing this Individual Assignment, you will be able to:

1. Apply cost-volume-profit analysis in a real-world scenario
2. Formulate conclusions and recommendations based on your analysis
3. Communicate your findings and suggested courses of action in a persuasive manner

Assessment Instructions

Assignment details

You work for Calvert-Lewin Experiences Ltd. The company specializes in the provision of trips for tourists in and around the major cities of Australia. Your manager is currently investigating introducing a new product, which are ‘Luxury’ helicopter rides over the Hunter Valley in NSW. Each trip would be 50km in total.

Your manager wants you to use cost-volume-profit analysis in order to help assess the plan’s feasibility, via Microsoft Excel software.

She provides you with the following estimated data:

Selling price per trip: $630 (total for 3 customers – trips only run with 3 customers) Costs:

Fuel: $50 per trip

Gourmet snack per customer: $45 Helicopter rental per month:$20,000

Insurance per month (unlimited trips): $1,000 Pilot costs:$4,000 per month plus $120 pertrip

Maintenance costs are difficult to estimate but data from a similar company in a different location shows that these monthly costs were $9,000 when 5,000 kms were flown and $5000 when 1,500 kms were flown.

Your manager has also undertaken some limited market research and is not aware of any other business currently offering this product in the Hunter Valley. However, she also found that one business did offer a similar product before the covid pandemic hit and it provided around 3 trips per day but struggled to make a profit. Also, she is mindful that after covid things are changing quickly as restrictions ease.

She believes the estimated cost data above may be conservative and that lower costs (particularly for helicopter rental and pilot costs) may be possible.

REQUIRED: Calculate the following based on the estimates given:

1. The Break-even point in trips per month                                                              (3 Marks)
2. The Break-even point in dollars of revenue per month                                          (2 Marks)
3. Assuming a profit after tax requirement from the Helicopter trip business of $120,000 per year and a tax rate of 30%, calculate:

Trips required per month to obtain target profit                                                                      (3 Marks)

Revenue required per month to obtain target profit                                                                 (2 Marks)

Your manager has requested that the spreadsheet is easy to use for ‘What-if’ analysis – so she would like to be able to change some of the inputs to see the impact on the calculations above – for example, if the Helicopter were able to be rented more cheaply or the selling price was increased.

Hence 3 marks are allocated to ease of use and accuracy for ‘what-if’ analysis (which will also depend on the formulas used).2 marks are allocated for the general presentation of the spreadsheet.

1. You are required to copy and paste your excel work into a written document in which you follow the report format to provide context to your analysis and offer recommendations as to whether this business should be undertaken. Issues to consider include (but are not limited to):

• Research into real-world competitors and information
• Limitations of CVP analysis
• The impact of changes to variables (inputs)on the results
• Whether the business should be undertaken even though a loss is expected
• The risks and rewards of the business                                                                (11 Marks)

4 Marks are awarded for appropriate presentation  the written analysis, including using a report format, wording, grammar, spelling and referencing.