Question

Discounted Cash Flow Analysis (Model) Excel Case:

Your employer is considering a capital project that involves installing a new manufacturing facility(to manufacture a new product) at a cost of $30,800,000. The facility will be built on land that was purchased in 2018 for $1,250,000. If the facility is not built on this land, the land will remain unused. The new manufacturing facility, if built, will be depreciated on a straight-line basis over five years, to a salvage value of $2,000,000. If the facility is built, the production there will cause an immediate increase in Inventory of $1,300,000. It will also cause immediate increases in Accounts Receivable of $5,900,000, Accounts Payable of $850,000, and Long-Term Debt of $15.2million.

If built and produced, the new product is expected to generate annual sales of $20,375,000 by the end of the first year. Sales are expected to increase 8% per year. COGS expense is expected to be of $9,780,000 during the first year. Thereafter, COGS is expected to remain at a constant percentage of Sales. Because operating efficiency is expected to improve each year, SG&A expense is expected to remain at $3,750,000 for each of the five years of the project. At the end of the project’s five-year life, production will cease, and the manufacturing facility will be sold for an estimated $4,500,000. At that time, Inventory, Accounts Receivable and Accounts Payable will return to their pre-project levels.

If the project is implemented, it will likely increase sales of the company’s existing complimentary products. The net impact of those sales is expected to be a $2,225,000 annual increase in pre-tax profits.

Your employer’s tax rate is 21%. The firm has 5 million shares of common stock outstanding. The firm requires a 11% rate of return on capital projects of this risk.

Prepare a discounted cash flow analysis to determine whether your employer should implement this capital project. Your analysis should reveal answers to each of the following questions. Clearly label all cells. Highlight the cells that answer the following questions:

1. What is the initial investment amount (Year 0)?
2. What are the total cash flows each year (Years 1-5)
3. What is the NPV?
4. What is the IRR?

Answer 1

Discounted cash flow analysis
Year	0	1	2	3	4	5
Installation of manufacturing facility	-30800000
Tax savings on depreciation [(30800000-2000000)/5]*Tax rate		1209600	1209600	1209600	1209600	1209600
Salvage value of manufacturing facility net of tax(2000000*79%)						1580000
Increase in inventory	-1300000
Increase in account recievable	-5900000
Increase in accounts payable	850000
Increase in Long term debt	-15200000
WN-1   Increase in sales net of tax		8370050	9039654	9762826	10543852	11387361
SG & A expenses		-3750000	-3750000	-3750000	-3750000	-3750000
Sale of maufacturing facility net of tax 4500000*79%						3555000
Increase in sales of existing products 2225000*79%						1757750
Return of working capital to preproject level(1300000+5900000-850000)					6350000
Initial Investment	-52350000
Total cash flows of each year		5829650	6499254	7222426	8003452	22089711
Discount rate @11%	1	0.9	0.812	0.731	0.659	0.593
Discounted cash flows	-52350000	5246685	5277394	5279594	5274275	13099198
Net Present value	-18172854
Lower discount rate for IRR @ 10%	1	0.909	0.826	0.751	0.683	0.62
Discounted Cash flows	-52350000	5299152	5368384	5424042	5466358	13695621
Lower NPV	-17096444
higher discount rate for IRR @ 20%	1	0.833	0.694	0.578	0.482	0.401
Discounted Cash flows	-52350000	4856098	4510482	4174562	3857664	8857974
Higher NPV	-26093219
IRR= lower rate+ NPV at lower rate/(NPV at lower rate- NPV at higher rate)*Higher rate-lower rate
10+(-17096444)/-17096444+26093219)*10
IRR= 29.0028%
WN-1               Sales		20375000	22005000	23765400	25666632	27719963
Cogs 9780000/20375000=48% of sales for remaining years		9780000	10562400	11407392	12319983	13305582
		10595000	11442600	12358008	13346649	14414381
	Net of tax @100%-21%	8370050	9039654	9762826	10543852	11387361

Assumptions: Land has already been purchased. It will be held vaccant. So, it is not considered for analysis.