Q14 - Please help with the following practice exam. Prof provided the answer key but REQUIRES showing the solution/analysis using Excel functions/formulas STEP-BY-STEP. Thank you.

Answer Key:

Accounting break-even QA = 40,600.96 units, a) DOL = 9.99 b) OCF = 316,518, NPV base = 843,597, NPV drop = $54,096.15. c) OCF = $276,738, If variable costs decrease by $1 then OCF would change by $39,780. NPVbest = $3,658,261.89 NPVworst = -$1,631,509.50

Link to Excel Template provided by Prof:

https://docs.google.com/spreadsheets/d/1hUsLoDR6NkcIiYtR6JXIkEwaHifxijno/edit?usp=sharing&ouid=101889030174747117377&rtpof=true&sd=true

Question(s):

We are evaluating a project that costs $845,000, has an eight-year life, and has no salvage value. Assume that depreciation is straight-line to zero over the life of the project. Sales are projected at 51,000 units per year. The price per unit is $53, the variable cost per unit is $27, and the fixed costs are $950,000 per year. The tax rate is 22 percent, and we require a return of 10 percent on this project. (note: an excel template has been provided in Blackboard to assist with this problem if you choose)

a. Calculate the accounting break-even point. What is the degree of operating leverage at the accounting break-even point?

b. Calculate the base-case cash flow and NPV. What is the sensitivity of NPV to changes in the quantity sold? Explain what your answer tells you about a 500-unit decrease in the quantity sold.

c. What is the sensitivity of OCF to changes in the variable cost figure? Explain what your answer tells you about a $1 decrease in estimated variable costs.

d. Sensitivity analysis: suppose the projections given for price, quantity, variable costs, and fixed costs are all accurate to within 10 percent. Calculate the best-case and worst-case NPV figures.