For problems 4-5, consider a project with an upfront cost of $1,000,000 and a ve
ID: 2701646 • Letter: F
Question
For problems 4-5, consider a project with an upfront cost of $1,000,000 and a ve-year life. The rm has a
tax rate of 27% and a required rate of return of 10%. The rm forecasts the following annual sales and cost
gures associated with the project:
Base Case Lower Bound Upper Bound
Unit Sales 200,000 150,000 250,000
Price / unit $5.00 $3.00 $7.00
Variable cost / unit $0.75 $0.50 $1.00
Annual xed costs $75,000 $65,000 $85,000
4. Using the Excel , perform a scenario analysis, calculating the net income, operating cash flow, NPV, and IRR for three dierent scenarios (base case, worst case, best case).
5. Now focus on the base case and perform a sensitivity analysis. Holding all other variables constant, let
the forecast for unit sales fluctuate and calculate the net income, operating cash flow, NPV, and IRR for
three different scenarios (unit sales = 200,000, unit sales = 150,000 and unit sales = 250,000).
Explanation / Answer
under base case
net income = 200000 * 5 - 200000 * 0.75 - 75000 = 775000
operating cash flow = 775000 * (1-0.27) = 565750
NPV = -1000000 + 565750 /1.1 + 565750 /1.1^2 + 565750 /1.1^3 + 565750 /1.1^4 + 565750 /1.1^5 = 1144637.61479
0 = -1000000 + 565750 /(1+IRR) + 565750 /(1+IRR) ^2 + 565750 /(1+IRR) ^3 + 565750 /(1+IRR) ^4 + 565750 /(1+IRR) ^5
IRR = 48.83%
lower bound
net income = 150000 *3 - 150000 * 0.5 - 65000 = 310000
operating cash flow = 310000 * (1-0.27) = 226300
NPV = -1000000 + 226300 /1.1 +226300 /1.1^2 + 226300 /1.1^3 + 226300 /1.1^4 + 226300 /1.1^5 = -142144.9541
0 = -1000000 +226300 /(1+IRR) +226300 /(1+IRR) ^2 + 226300 /(1+IRR) ^3 + 226300 /(1+IRR) ^4 + 226300 /(1+IRR) ^5
IRR = 4.26%
upper bound
net income = 250000 *7 - 250000 * 1 -85000 = 1415000
operating cash flow = 1415000 * (1-0.27) = 1032950
NPV = -1000000 + 1032950 /1.1 +1032950 /1.1^2 + 1032950 /1.1^3 + 1032950 /1.1^4 + 1032950 /1.1^5 = 2915693.193
0 = -1000000 +1032950 /(1+IRR) +1032950 /(1+IRR) ^2 + 1032950 /(1+IRR) ^3 + 1032950 /(1+IRR) ^4 + 1032950 /(1+IRR) ^5
IRR = 100.07%
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.