Question

Consider the following expansion capital budgeting problem.

A capital budgeting decision is being considered that would involve an expansion and simultaneous replacement of old equipment. The project is expected to have a 6 year life for the firm.

This project will replace some existing equipment which currently has a book value (BV) of $200k and an estimated market salvage value of $375k. The new project will require new equipment costing $2000k, which will be depreciated straight-line to a book value of $200k at the end of 6 years. Due to new energy efficient technology, replacing the old equipment with the new more efficient equipment will generate an immediate tax credit of 5% of the equipment’s cost. The expansion will require an additional investment in NWC of $200k.

Sales are expected to increase by $1000k the first year and grow by 15% in years 2 and 3, then by 5% annually during the remaining 6 year life. Cost of goods sold is forecasted to be 45% of the increased sales, and other selling and general administrative expenses are forecasted to be 10% of the increased sales.

It is forecasted that the new equipment will have a salvage value of $300k at the end of the project’s 6 year life.

The firm’s weighted average cost of capital (WACC) for projects of this risk level is 8%. The firm’s marginal tax rate is T = 40%.

Use the Excel template to complete the capital budgeting analysis. Your Excel analysis should clearly indicate the cash flow analysis timeline and should provide the project’s NPV, IRR, PBP, PI, and also illustrate the project’s NPV Profile.

___

Can you please help to fill out the arrays on the right of the following google sheet

https://docs.google.com/spreadsheets/d/1Z2pFSPrg99XXZE0wSj6k89AmloH8mkgLWzvNwaawFPs/edit?usp=sharing

Answer 1

NPV Scenario / Risk Analysis:
	Complete the grids below to report 5x5 Grids of NPV vs input variable changes noted
NPV Analysis Grid: NPV vs Discount Rate & Salvage Value Ranges
	SV ->	SV-20%	SV-10%	SV Base	SV+10%	SV+20%
	ATSV ->	ATSV-20%	ATSV-10%	ATSV Base	ATSV+10%	ATSV+20%
$NPV in Cells:	$ 10,53,284	240000	270000	3,00,000	330000	360000
Cost of capital	4%	$   14,04,024.80	$14,18,250.46	$       14,32,476.12	$14,46,701.78	$14,60,927.45
	6%	$   12,07,407.55	$12,20,096.84	$       12,32,786.13	$12,45,475.42	$12,58,164.71
	8%	$   10,30,597.63	$10,41,940.68	$       10,53,283.73	$10,64,626.79	$10,75,969.84
	10%	$    8,71,163.13	$ 8,81,323.66	$         8,91,484.19	$ 9,01,644.72	$ 9,11,805.25
	12%	$    7,27,015.91	$ 7,36,135.27	$         7,45,254.63	$ 7,54,373.99	$ 7,63,493.35
NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges
	Sales Yr.1 ->	Sales Yr.1 -20%	Sales Yr.1 -10%	Sales Yr.1 Base	Sales Yr.1 +10%	Sales Yr.1 +20%
$NPV in Cells:	$ 10,53,284	800000	900000	10,00,000	1100000	1200000
	4%	1065501.119	1248988.621	1432476.123	1615963.625	1799451.127
	6%	890101.0607	1061443.597	1232786.134	1404128.671	1575471.207
	8%	732551.7049	892917.7195	1053283.734	1213649.749	1374015.763
	10%	590645.2092	741064.6989	891484.1886	1041903.678	1192323.168
	12%	462487.5456	603871.0895	745254.6334	886638.1772	1028021.721

First of all N32 cell (=D42)

Then fill the New salvage value figures in the row of tablein o32,p32,q32,r32,s32

Also in cell d12 use the cell references from the pink table rather then using the figures directly.

Now select the whole table from N32 to S37 and while the table area is selected

Use what if analysis

Go to DATA> What if analysis> Data table

A window will pop in row refer the salvage value(of new) cell from the pink table (For 2nd table select year 1 sales cell (i.e.e16)

in column refer the cell you have used as cost of capital cell for calculating the Original NPV.(D41).

Thats it just enter and your table will be automatically filled.

In case any further quary please leave a comment...

Use the same for the other table.