Question: please help with the graph b. Now conduct a sensitivity analysis to determine the sensltivity of NPV to changes in the sales price, varlable costs
b. Now conduct a sensitivity analysis to determine the sensltivity of NPV to changes in the sales price, varlable costs per unit, and number of units sold. Set these variables' values at 10% and 20% above and below their base-case values. Include a graph In your analysis. % Deviation 1st YEAR UNIT SALES from Base NPV Base Case 1,000 $3,473 -20% 800 $1.00 -10% 900 $1,000 3 0% 1,000 $3,473 4 10% 1,100 $1,000 5 20% 1,200 $833 6 7 8 9 % Deviation SALES PRICE 0 from Base NPV 1 Base Case $24.00 $3,473 2 -20% $19.20 $24 3 -10% $21,60 $24 4 0% $24.00 $24 15 10% $26.40 $24 16 20% $28.80 $24 Note about data tables. The data in the column Input should NOT be Input using a cell reference to the column Input cell. For example, the base case 1st Year Unit Sales In Cell B100 should be the number 1,000 and NOT have the formula =D31 In that cell. This is because you'll use D31 as the column Input cell in the data table and If Excel tries to iteratively replace Cell D31 with the formula =D31 rather than a series of numbers, Excel will calculate the wrong answer. Unfortunately, Excel won't tell you that there is a problem, so you'll Just get the wrong values for the data table! VON % Deviation from Base Case -20% -10% 0% 10% 20% VARIABLE COST Base NPV $18.00 $3,473 $14.40 $18 $16.20 $18 $18.00 $18 $19.80 $18 $21.60 $18 18 19 20 21 22 23 24 25 26 27 28 129 30 131
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
