Question

	High	Base	Low
Cost ($)	500,000	400,000	300,000
Sales (units)	85,000	60,000	35,000
Net profit per unit ($)	1.15	1.00	.85

You are evaluating the NPV of a project to expand sales into a new region. The cost of the expansion is paid in year 0. The benefits of the expansion start in year 1 and last for 10 years, after which they end. Profits are determined by multiplying net‐profit‐per‐unit by the amount of sales. There is some uncertainty about the cost and other parameters. We have high, low and base (most likely) estimates for each of the parameters.

a) The annual interest rate is 5%. What is the NPV at the base values of the parameters?

b) Show how sensitive the results are to the assumptions about sales, net‐profits‐per‐unit, and cost (use the high and low values of the parameters where appropriate).

c) Do a scenario analysis and determine the NPV for the best and worst cases. (Be careful here, what combination of results is best? What combination is worst?)

Answer 1

Answer- (Part-a) Year 61 0 1 2 3 4 5 7 9 10 Net Profit per Unit in BASE Sales in Units Sales in Value Cost incurred in Year Zero Present value Factor @5% NPV (Expert Remarks NPV is Summation of all cash flows in Present value terms) 1 1 1 1 1 1 1 1 1 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 -400000 1 0.952380952 0.9070294780.863837599 0.822702475 0.783526166 0.746215397 0.71068133 0.676839362 0.644608916 0.613913254 63,304.10 FINAL ANWERS (Part -a) 54,421.77 51.830.26 49,362.15 44,772.92 40,610.36 -400000 57,142.86 47,011.57 42,640.88 38,676.53 36,834.80

Answer B Sensitivity analysis is used in capital budgeting for more precisely measuring the risk.it helps in assessing information as to how sensitive are the estimated parameters of the project such as cash flows, discount rate, Sales prices, initial cost. Future being always uncertain and estimation are always subject to error, sensitivity analysis take care of estimation errors by using no. of possible outcomes in evaluating a project Sensitivity Formula = % change in NPV/ % change in various factors(like Cost, Price per unit)

CASE 1, IF we reduce cost from 500000 to 300000 i.e 40% and rest everthing remain constant, then Our NPV would increse by 78%. ( Details Solution is provided below). So sensitivity to Cost is 78%/40 % i.e 1.95 4 Year 0 2 6 8 9 10 Net Profit per Unit in High Sales in Units Sales in Value Cost incurred in Year Zero Present value Factor @5% NPV 1.15 1.15 1.15 1.15 1.15 1,15 1.15 1.15 1.15 1.15 85,000 85,000 85,000 85,000 85,000 85,000 85,000 85,000 85,000 85,000 97,750 97,750 97,750 97,750 97,750 97,750 97,750 97,750 97,750 97,750 -500000 1 0.952380952 0.907029478 0.863837599 0.822702475 0.783526166 0.746215397 0.71068133 0.676839362 0.644608916 0.613913254 -500000 60,010.02 2,54,799.59 93.095.24 84,440.13 66,161.05 88,662.13 80,419.17 76,589.68 72,942.56 69,469.10 63,010.52 Year Net Profit per Unit in High Sales in Units Sales in Value Cost incurred in Year Zero Present value Factor @5%| NPV 0 6 10 1.15 1.15 1.15 1.15 1.15 1.15 1.15 1.15 1.15 1.15 85,000 85,000 85,000 85,000 85,000 85,000 85,000 85,000 85,000 85,000 97,750 97,750 97,750 97,750 97,750 97,750 97,750 97,750 97,750 97,750 -300000 1 0.952380952 0.907029478 0.863837599 0.822702475 0.783526166 0.746215397 0.71068133 0.676839362 0.644608916 0.613913254 -300000 80,419.17 66,161.05 60,010.02 4,54,799.59 93,095.24 88,662.13 84,440.13 76,589.68 72,942.56 69,469.10 63,010.52 Change in NAV % Change in Cost 40% 78%

CASE -2, IF we reduce sales from 97750 to 40250 i.e 59% and rest everthing remain constant, then Our NPV would Decrease by 174%. ( Details Solution is provided below). So sensitivity to sales is 174%/ 59% i.e 2.94 Year 0 1 6 7 8 9 10 Net Profit per Unit in High Sales in Units Sales in Value Cost incurred in Year Zero Present value Factor @5% NPV 1.15 1.15 1.15 1.15 1.15 1.15 1.15 1.15 1.15 1.15 85,000 85,000 85,000 85,000 85,000 85,000 85,000 85,000 85,000 85,000 97,750 97,750 97,750 97,750 97,750 97,750 97,750 97,750 97,750 97.750 -500000 1 0.952380952 0.907029478 0.863837599 0.822702475 0.783526166 0.746215397 0.71068133 0.676839362 0.644608916 0.613913254 60,010.02 2,54,799.59 93,095.24 84,440.13 76,589.68 66,161.05 63,010.52 -500000 88,662.13 80,419.17 72,942.56 69,469.10 Year 0 5 6 9 10 Net Profit per Unit in High 1.15 1.15 1.15 1.15 1.15 1.15 1.15 1.15 1.15 1.15 Sales in Units Sales in Value 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 40,250 40,250 40,250 40,250 40,250 40,250 40,250 40,250 40,250 40,250 -500000 Present value Factor @5 % 1 0.952380952 0.907029478 0.863837599 0.822702475 0.783526166 0.746215397 0.71068133 0.676839362 0.644608916 0.613913254 NPV 24,710.01 -1,89,200.17 33,113.77 30,035.17 27,242.78 -500000 38,333.33 36,507.94 34,769.46 31,536.93 28,604.92 25,945.51 % Change in Sales 59% % Change in NAV -174%

CASE-3, IF we reduce sales from 1.15 to .85 i.e 26 % and rest everthing remain constant, then Our NPV would Decrease by 174%. (Details Solution is provided below). So sensitivity to profit is 174%/26% i.e 7.93 4 10 Year о 1 2 3 5 6 7 8 9 Net Profit per Unit in High Sales in Units Sales in Value Cost incurred in Year Zero Present value Factor @5 % NPV 1.15 1.15 1.15 1.15 1.15 1.15 1.15 1.15 1.15 1.15 85,000 85,000 85,000 85,000 85,000 85,000 85,000 85.000 85,000 85,000 97,750 97.750 97,750 97,750 97,750 97,750 97,750 97,750 97,750 97,750 -500000 1 0.952380952 0.907029478 0.863837599 0.822702475 0.783526166 0.746215397 0.71068133 0.676839362 0.644608916 0.613913254 84,440.13 80,419.17 76,589.68 69.469.10 66,161.05 60,010.02 -500000 93,095.24 88,662.13 72,942.56 63,010.52 2,54,799.59 4 10 Year 6 7 8 Net Profit per Unit in High Sales in Units Sales in Value 0.85 0.85 0.85 0.85 0.85 0.85 0.85 0.85 0.85 0.85 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 29,750 29,750 29.750 29,750 29,750 29,750 29,750 29,750 29,750 29,750 -500000 Present value Factor @5 % NPV 1 0.952380952 0.907029478 0.863837599 0.822702475 0.783526166 0.746215397 0.71068133 0.676839362 0.644608916 0.613913254 18,263.92 -2,70,278.39 -500000 28,333.33 26,984.13 25,699.17 24,475.40 23,309.90 22,199.91 21,142.77 20,135.97 19,177.12 % Change in NAV -206% sensitivity to Cost is 78%/40 % i.e 1.95 sensitivity to sales is 174%/59% i.e 2.94 Final Answer to Part sensitivity to profit is 174%/26% i.e 7.93 B Sheet1

Answer (Part-c) We need to calculate the NPV of Best & Worse 1 NPV calculation of High 0 Year 5 6 7 8 91 10 Net Profit per Unit in High Sales in Units Sales in Value 1.15 1,15 1,15 1.15 1.15 1.15 1.15 1.15 1.15 1.15 85,000 85,000 85,000 85,000 85,000 85,000 85,000 85,000 85,000 85,000 97,750 97,750 97,750 97,750 97,750 97,750 97,750 97,750 97,750 97,750 Cost incurred in Year Zero Present value Factor @5 % NPV -500000 0.952380952 0.907029478 0.863837599 0.822702475 0.783526166 0.746215397 0.71068133 0.676839362 0.6446089160.613913254 2,54,799.59 93,095.24 88,662.13 80,419.17 63,010.52 -500000 84,440.13 76,589.68 72,942.56 69,469.10 66,161.05 60,010.02 2 NPV calculation of Low 10 Year 5 6 Net Profit per Unit in Low 0.85 35,000 0.85 0.85 0.85 0.85 0.85 0.85 0.85 0.85 0.85 Sales in Units Sales in Value Cost incurred in Year Zero Present value Factor @5% NPV 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35.000 29,750 29,750 29,750 29,750 29,750 29,750 29,750 29,750 29,750 29,750 -300000 0.952380952 0.907029478 0.863837599 0.822702475 0.783526166 0.746215397 0.71068133 0.676839362 0.6446089160.613913254 24,475.40 23,309.90 20,135.97 28,333.33 26,984.13 18,263.92 -70,278.39 -300000 25,699.17 22,199.91 21,142.77 19,177.12 3 NPV calculation of Base (Same as Part -a) NPV 63,304.10 Combination of results is best in Case of High scenario i.e NPV of 254799 Combination of worst is in Low scenario i.e NPV is negative 70278 Final Answer of Part C Sheet1