Question

I'm having trouble figuring out how to calculate the numbers that go into the NPV analysis grids. The three values that I came up with in the first grid next to NPV in cells I'm not sure if it's correct. Can you help with both grids please? The questions below must be answered after.

2D. Based on your NPV Scenario / Risk Analysis Grids, is NPV more sensitive to changing cost of capital or changing salvage values? How do you determine this?

2E. Based on your NPV Scenario / Risk Analysis Grids, is NPV more sensitive to changing cost of capital or changing year one sales level assumptions? How do you determine this?

2 Yellow highlighted cells are cells for inputs. Team should verify all other calculations & formats 4. Inputs 5ATSV old a t-0 305000 2,000,000 100000.00 ATSV formula -market value-(market value-book value)*tax rate 6 Equipment 7 Tax Credit 8Depreciaton per year 300,000 equipment-bv/6 9 Sales period 1 101 CoGS %of sales | | | SG&A exp. %of sales 12 ATSV new (at-6 13 14 Operating Life CFs 15 Time 16 Sales 17- COGS 18 SG&A expenses 19|- Depreciation 20-EBIT 21 -Taxes (40%) 22Net Income 23 +Depreciation 24-Operating CF 25 26 Time 0 Investments 27 | Equipment 28 ATSV old 29 Tax credit 30 NWC 1,000,000 45% 10% 260000 growth: g yrs 2-3- 15% gyrs 4-6- market value book value tax credit 375,000 200,000 market value at 6 book value at 6 marginal tax rate 300,000 200,000 4 $1,000,000 450,000 100,000 300,000 150,000 60,000 90,000 300,000 390,000 $1,150,000$1,322,500 595,125 132,250 300,000 295,125 118,050 177,075 300,000 477,075 517,500 115,000 300,000 217,500 87,000 130,500 300,000 430,500 $1,388,625$1,458,056.25$1,530,959 688,932 153,096 300,000 388,932 155,573 233,359 300,000 533,359 624,881 138,863 300,000 324,881 129,953 194,929 300,000 494,929 656,125 145,806 300,000 356,125 142,450 213,675 300,000 513,675 2,000,000 305,000 100,000 200,000

Answer 1

	2D:-	ATSV
		208,000	234,000	260,000	286,000	312,000
Cost of Capital	4%	200,000	225,000	250,000	275,000	300,000		Bold & Italic Amounts are Present Values
	6%	196,226	220,755	245,283	269,811	294,340		For Cell C4 figure, formula used is "=C$3/(1+$B4)"
	8%	192,593	216,667	240,741	264,815	288,889
	10%	189,091	212,727	236,364	260,000	283,636
	12%	185,714	208,929	232,143	255,357	278,571
				0.90	Change in SV (234000/260000). Thus Change of 10%
				0.88	Change in Present value of SV with increase in Cost of Capital (220755/250000). Change of 12%
					Hence, Cost of Capital has more impact than change in SV
	2E:-	Sales Yr-1
		800,000	900,000	1,000,000	1,100,000	1,200,000		Bold & Italic Amounts are Present Values
Cost of Capital	4%	769,231	865,385	961,538	1,057,692	1,153,846		For Cell C17 figure, formula used is "=C$16/(1+$B17)"
	6%	754,717	849,057	943,396	1,037,736	1,132,075
	8%	740,741	833,333	925,926	1,018,519	1,111,111
	10%	727,273	818,182	909,091	1,000,000	1,090,909
	12%	714,286	803,571	892,857	982,143	1,071,429
				0.90	Change in SV (900000/1000000). Thus Change of 10%
				0.88	Change in Present value of Sales with increase in Cost of Capital (849057/961538). Change of 12%
					Hence, Cost of Capital has more impact than change in Sales