Question

Given the information below, calculate the NPV's for the following scenarios: 1 Unit price goes up by 10% 2 Variable price goes up by 10% 3 Unit sales goes down by 10% 4 Fixed price goes up by 10% Base information remains the same between scenarios except for the variables 1-4. Which scenerio is the most sensitive and why? Base Info $ $ $ Unit price Variable cost per unit Fixed Costs Expected Sales Required rate of return Tax Rate Cost of machine Salvage Value Life Working Capital Increase 120 70 240,000 10,000 10% 21% 1,000,000 $ S $ . 10 Years !

Please, I need the answer with the formula in Excel? Thank you

Answer 1

Year 10,000 120 10,000 120 10,000 120 5 10,000 120 10,000 120 10,000 120 10,000 120 10,000 120 10 10,000 120 Expected sales Unit Price Variable cost Fixed Cost 10,000 120 70 240,000 70 70 70 70 70 70 70 70 240,000 240,000 240,000 240,000 240,000 240,000 240,000 240,000 240,000 Sales Less : Variable cost Contribution Less: Fixed Cost Net cash flow Less : Depreciation Net income before tax Less : Taxes @ 21% Income After tax Add: Depreciation Net cash flow after tax Discount rate @ 10% Present value of cash flows 1,200,000 700,000 500,000 240,000 260,000 100,000 160,000 33,600 126,400 100,000 226,400 0.9091 205,818 1,200,000 700,000 500,000 240,000 260,000 100,000 160,000 33,600 126,400 100,000 226,400 0.8264 187,107 1,200,000 700,000 500,000 240,000 260,000 100,000 160,000 33,600 126,400 100,000 226,400 0.7513 170,098 1,200,000 700,000 500,000 240,000 260,000 100,000 160,000 33,600 126,400 100,000 226,400 0.6830 154,634 1,200,000 700,000 500,000 240,000 260,000 100,000 160,000 33,600 126,400 100,000 226,400 0.6209 140,577 1,200,000 700,000 500,000 240,000 260,000 100,000 160,000 33,600 126,400 100,000 226,400 0.5645 127,797 1,200,000 700,000 500,000 240,000 260,000 100,000 160,000 33,600 126,400 100,000 226,400 0.5132 116,179 1,200,000 700,000 500,000 240,000 260,000 100,000 160,000 33,600 126,400 100,000 226,400 0.4665 105,617 1,200,000 700,000 500,000 240,000 260,000 100,000 160,000 33,600 126,400 100,000 226,400 0.4241 96,016 1,200,000 700,000 500,000 240,000 260,000 100,000 160,000 33,600 126,400 100,000 226,400 0.3855 87,287 1,391,130 Present value of cash inflows Less : Investment Net Present Value 1,391,130 1,000,000 391,130

If the unit price goes up by 10% Year Expected sales Unit Price Variable cost Fixed Cost 10,000 132 10,000 132 10,000 132 10,000 132 70 240,000 10,000 132 70 240,000 10,000 132 70 240,000 10,000 132 70 240,000 10,000 132 70 240,000 10,000 132 70 240,000 10 10,000 132 70 240,000 70 70 70 240,000 240,000 240,000 Sales Less : Variable cost Contribution Less: Fixed Cost Net cash flow Less : Depreciation Net income before tax before tax Less : Taxes @ 21% Income After tax Add : Depreciation Net cash flow after tax Discount rate @10% Present value of cash flows 1,320,000 700,000 620,000 240,000 380,000 100,000 280,000 58,800 221,200 100,000 321,200 0.9091 292,000 1,320,000 700,000 620,000 240,000 380,000 100,000 280,000 58,800 221,200 100,000 321,200 0.8264 265,455 1,320,000 700,000 620,000 240,000 380,000 100,000 280,000 58,800 221,200 100,000 321,200 0.7513 241,322 1,320,000 700,000 620,000 240,000 380,000 100,000 280,000 58,800 221,200 100,000 321,200 0.6830 219,384 1,320,000 700,000 620,000 240,000 380,000 100,000 280,000 58,800 221,200 100,000 321,200 0.6209 199,440 1,320,000 700,000 620,000 240,000 380,000 100,000 280,000 58,800 221,200 100,000 321,200 0.5645 181,309 1,320,000 700,000 620,000 240,000 380,000 100,000 280,000 58,800 221,200 100,000 321,200 0.5132 164,826 1,320,000 700,000 620,000 240,000 380,000 100,000 280,000 58,800 221,200 100,000 321,200 0.4665 149,842 1,320,000 700,000 620,000 240,000 380,000 100,000 280,000 58,800 221,200 100,000 321,200 0.4241 136,220 1,320,000 700,000 620,000 240,000 380,000 100,000 280,000 58,800 221,200 100,000 321,200 0.3855 123,837 1,973,635 Present value of cash inflows Less : Investment Net Present Value 1,973,635 1,000,000 973,635

If the Variable cost goes up by 10% Year Expected sales Unit Price Variable cost Fixed Cost 10,000 120 10,000 120 10,000 120 77 240,000 10,000 120 77 240,000 10,000 120 77 240,000 10,000 120 77 240,000 10,000 120 77 240,000 10,000 120 77 240,000 10,000 120 77 240,000 10 10,000 120 77 240,000 77 77 240,000 240,000 Sales Less : Variable cost Contribution Less : Fixed Cost Net cash flow Less : Depreciation Net income before tax Less : Taxes @ 21% Income After tax Add: Depreciation Net cash flow after tax Discount rate @ 10% Present value of cash flows 1,200,000 770,000 430,000 240,000 190,000 100,000 90,000 18,900 71,100 100,000 171,100 0.9091 155,545 1,200,000 770,000 430,000 240,000 190,000 100,000 90,000 18,900 71,100 100,000 171,100 0.8264 141,405 1,200,000 770,000 430,000 240,000 190,000 100,000 90,000 18,900 71,100 100,000 171,100 0.7513 128,550 1,200,000 770,000 430,000 240,000 190,000 100,000 90,000 18,900 71,100 100,000 171,100 0.6830 116,864 1,200,000 770,000 430,000 240,000 190,000 100,000 90,000 18,900 71,100 100,000 171,100 0.6209 106,240 1,200,000 770,000 430,000 240,000 190,000 100,000 90,000 18,900 71,100 100,000 171,100 0.5645 96,581 1,200,000 770,000 430,000 240,000 190,000 100,000 90,000 18,900 71,100 100,000 171,100 0.5132 87,801 1,200,000 770,000 430,000 240,000 190,000 100,000 90,000 18,900 71,100 100,000 171,100 0.4665 79,819 1,200,000 770,000 430,000 240,000 190,000 100,000 90,000 18,900 71,100 100,000 171,100 0.4241 72,563 1,200,000 770,000 430,000 240,000 190,000 100,000 90,000 18,900 71,100 100,000 171,100 0.3855 65,966 1,051,335 Present value of cash inflows Less : Investment Net Present Value 1,051,335 1,000,000 51,335

If the unit sales goes down by 10% Year 9,000 120 9,000 120 9,000 120 Expected sales Unit Price Variable cost Fixed Cost 9,000 120 70 240,000 9,000 120 70 240,000 9,000 120 70 240,000 9,000 120 70 240,000 9,000 120 70 240,000 9,000 120 70 240,000 70 10 9,000 120 70 240,000 70 70 240,000 240,000 240,000 1,080,000 Sales Less : Variable cost Contribution Less: Fixed Cost Net cash flow Less: Depreciation Net income before tax Less : Taxes @ 21% Income After tax Add : Depreciation Net cash flow after tax Discount rate @ 10% Present value of cash flows 630,000 450,000 240,000 210,000 100,000 110,000 23,100 86,900 100,000 186,900 0.9091 169,909 1,080,000 630,000 450,000 240,000 210,000 100,000 110,000 23,100 86,900 100,000 186,900 0.8264 154,463 1,080,000 630,000 450,000 240,000 210,000 100,000 110,000 23,100 86,900 100,000 186,900 0.7513 140,421 1,080,000 630,000 450,000 240,000 210,000 100,000 110,000 23,100 86,900 100,000 186,900 0.6830 127,655 1,080,000 630,000 450,000 240,000 210,000 100,000 110,000 23,100 86,900 100,000 186,900 0.6209 116,050 1,080,000 630,000 450,000 240,000 210,000 100,000 110,000 23,100 86,900 100,000 186,900 0.5645 105,500 1,080,000 630,000 450,000 240,000 210,000 100,000 110,000 23,100 86,900 100,000 186,900 0.5132 95,909 1,080,000 630,000 450,000 240,000 210,000 100,000 110,000 23,100 86,900 100,000 186,900 0.4665 87,190 1,080,000 630,000 450,000 240,000 210,000 100,000 110,000 23,100 86,900 100,000 186,900 0.4241 79,264 1,080,000 630,000 450,000 240,000 210,000 100,000 110,000 23,100 86,900 100,000 186,900 0.3855 72,058 1,148,420 Present value of cash inflows Less : Investment Net Present Value 1,148,420 1,000,000 148,420

10 If the fixed cost goes up by 10% Year Expected sales Unit Price Variable cost Fixed Cost 9,000 120 9,000 120 9,000 120 9,000 120 70 264,000 9,000 120 70 264,000 9,000 120 70 264,000 9,000 120 70 264,000 9,000 120 70 264,000 9,000 120 70 264,000 9,000 120 70 264,000 70 70 70 264,000 264,000 264,000 Sales Less : Variable cost Contribution Less: Fixed Cost Net cash flow Less : Depreciation Net income before tax Less : Taxes @ 21% Income After tax Add : Depreciation Net cash flow after tax Discount rate @ 10% Present value of cash flows 1,080,000 630,000 450,000 264,000 186,000 100,000 86,000 18,060 67,940 100,000 167,940 0.9091 152,673 1,080,000 630,000 450,000 264,000 186,000 100,000 86,000 18,060 67,940 100,000 167,940 0.8264 138,793 1,080,000 630,000 450,000 264,000 186,000 100,000 86,000 18,060 67,940 100,000 167,940 0.7513 126,176 1,080,000 630,000 450,000 264,000 186,000 100,000 86,000 18,060 67,940 100,000 167,940 0.6830 114,705 1,080,000 630,000 450,000 264,000 186,000 100,000 86,000 18,060 67,940 100,000 167,940 0.6209 104,278 1,080,000 630,000 450,000 264,000 186,000 100,000 86,000 18,060 67,940 100,000 167,940 0.5645 94,798 1,080,000 630,000 450,000 264,000 186,000 100,000 86,000 18,060 67,940 100,000 167,940 0.5132 86,180 1,080,000 630,000 450,000 264,000 186,000 100,000 86,000 18,060 67,940 100,000 167,940 0.4665 78,345 1,080,000 630,000 450,000 264,000 186,000 100,000 86,000 18,060 67,940 100,000 167,940 0.4241 71,223 1,080,000 630,000 450,000 264,000 186,000 100,000 86,000 18,060 67,940 100,000 167,940 0.3855 64,748 1,031,919 Present value of cash inflows Less : Investment Net Present Value 1,031,919 1,000,000 31,919