Question

In: Accounting

Do in Excel and explain. thanks You work for Theo Walcott Tours Ltd which provide tourists...

Do in Excel and explain. thanks

You work for Theo Walcott Tours Ltd which provide tourists and visitors with ‘experiences’ of Perth and its surrounds. Your manager is currently investigating introducing another product, which are ‘Luxury’ helicopter rides over beautiful bushland. 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.

She provides you with the following estimated data:

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

Costs:

Fuel: $50 per trip

Walcott ‘goodie bag’ per customer: $40

Helicopter rental per month: $20,000

Insurance per month (unlimited trips): $1,000

Pilot costs: $5,000 per month plus $100 per trip

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

REQUIRED: Calculate the following:

1) The Break-even point in trips per month

2) The Break-even point in dollars of revenue per month

3) Assuming a profit after tax requirement from the Helicopter trip business of $120,000 per year and a tax rate of 30%, calculate:

  1. a) Trips required per month to obtain target profit
  2. b) Revenue required per month to obtain target profit

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)

4) Answering briefly in words in excel, include some notes to your manager explaining the limitations of your analysis and the assumptions included in it.

Solutions

Expert Solution

Selling price per trip          600.00
No. of trips          116.37
Total no. of km = 50km*no. of trips      5,818.71
Fixed costs:
Helicoptor rental    20,000.00
Insurance      1,000.00
Pilot costs      5,000.00
Maintenance costs      2,428.57
Total fixed costs    28,428.57
Variable costs:
Fuel costs            50.00
Goodie bag (40*3)          120.00
Pilot costs          100.00
Maintenance costs            85.71
Total variable cost per trip          355.71
Total costs    69,824.56
Total revenue    69,824.56
1: Break even point in trips         116.37
2: Break even point in $ revenue per month 69,824.56
3: Trips per month for target profit         818.13
4: The limitations of my analysis is based on the assumption that the maintenance costs is in the form of y = a+bx.
In case the maintenance costs is not in this form then it will be difficult to break it into fixed and variable components

Calculations: I have assumed that the maintenance costs have a fixed and varible part associated with it and is in linear form i.e. y = a+bx. Hence 11,000 = a+5000x and 5000 = a+1500x. Deducting the second equation from the 1st one we get 6000 = 3500b or b = 1.71429. Putting this value in equation 1 we get 11000 = a+(5000*1.71429). Thus a = 2428.57

Note that all cells in the excel sheet is linked to the break even formula and so if variable of any 1 cell is changed then answer will also change. Thus you can do the what if analysis quite easily.


Related Solutions

CASE STUDY USING EXCEL SPREADSHEET You work for Theo Walcott Tours Ltd which provide tourists and...
CASE STUDY USING EXCEL SPREADSHEET You work for Theo Walcott Tours Ltd which provide tourists and visitors with ‘experiences’ of Perth and its surrounds. Your manager is currently investigating introducing another product, which are ‘Luxury’ helicopter rides over beautiful bushland. 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. She provides you with the following estimated data: Selling price per trip: $600 (total for 3 customers...
Show work and explain how you got your answer, do not just post an excel spreadsheet...
Show work and explain how you got your answer, do not just post an excel spreadsheet with answers please. You are deciding between two mutually exclusive investment opportunities. Both require the same initial investment of $ 10.3 million. Investment A will generate $ 2.18 million per year​ (starting at the end of the first​ year) in perpetuity. Investment B will generate $ 1.51 million at the end of the first​ year, and its revenues will grow at 2.2 % per...
Explain how knowledge work systems provide value for a firm. Why do you think knowledge work...
Explain how knowledge work systems provide value for a firm. Why do you think knowledge work systems are important today? Discuss how CAD, virtual reality, and augmented reality are used today. What has been your experience with these three tools? Are there similar technologies to CAD, VR, and AR that you see on the horizon that will benefit firms in the future?
In EXCEL Please: you must show your work in Excel, which includes providing the formulas in...
In EXCEL Please: you must show your work in Excel, which includes providing the formulas in the cells, not just the summary value. You may not earn full points if you do not show your work in detail. JLR Enterprises provides consulting services throughout California and uses a job-order costing system to accumulate the cost of client projects. Traceable costs are charged directly to individual clients; in contrast, other costs incurred by JLR, but not identifiable with specific clients, are...
Please provide excel functions to answer!! Thanks! An experiment was conducted to investigate the machine performance...
Please provide excel functions to answer!! Thanks! An experiment was conducted to investigate the machine performance of deep hole drilling when chip congestion exists. The length, in mm, of 50 drill chips had a sample mean of 71.2 mm. Assume the population standard deviation is 15.2 mm. Conduct a test to determine whether the true mean drill chip length is less than 75 mm at α=.01. Sample size xbar sigma null hypothesis alternative hypothesis standard error test statistic (z) p...
Please show all work in order to get credit on an excel file. If you provide...
Please show all work in order to get credit on an excel file. If you provide only the answers without showing your work, I will not be able to provide any credit if your answer is wrong. Petrus has an opportunity to make two investments, but he can only afford to make one of them. Each one costs $ 25,000,000. The first investment can be sold in 14 years for $ 98,500,000 and has no periodic cash flow. The second...
Please show all work in order to get credit on an excel file. If you provide...
Please show all work in order to get credit on an excel file. If you provide only the answers without showing your work, I will not be able to provide any credit if your answer is wrong. Mr. X bought a house for $293,000. He put 20% down and obtained a fully amortized monthly loan for the balance at 5.75% interest for 30 years. a. Find the amount of X’s monthly payment. b. Find the total interest paid by X....
1) (note: Please also provide the excel formulas used to solve it )  You work for a...
1) (note: Please also provide the excel formulas used to solve it )  You work for a soft-drink company in the quality control division. You are interested in the standard deviation of one of your production lines as a measure of consistency. The product is intended to have a mean of 12 ounces, and your team would like the standard deviation to be as low as possible. You gather a random sample of 18 containers. Estimate the population standard deviation at...
Do your work in an Excel file. Make Excel do the test statistic calculation.!!!!!!!!! Do people...
Do your work in an Excel file. Make Excel do the test statistic calculation.!!!!!!!!! Do people generally receive the asking price when they sell their home? The accompanying table lists the asking price and sale price for a random sample of ten single-family home sales in Tahlequah, OK. Home Asking price Selling price 1 98,000 94,300 2 122,500 119,800 3 109,900 105,000 4 98,000 100,000 5 79,900 77,000 6 275,000 265,000 7 99,700 96,000 8 97,900 99,200 9 209,000 209,000...
14. PLEASE SHOW EXCEL WORK THANKS! Based on the following financial data, calculate the ratios requested....
14. PLEASE SHOW EXCEL WORK THANKS! Based on the following financial data, calculate the ratios requested. (Round your answers to 4 decimal places.)   Liabilities $ 7,800     Net worth $ 58,000   Liquid assets $ 4,600     Current liabilities $ 1,300   Monthly credit payments $ 640     Take-home pay $ 2,575   Monthly savings $ 130     Gross income $ 2,850 a.   Debt ratio    b.   Current ratio    c.   Debt-payments ratio    d.   Savings ratio   
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT