Question

Cleveland Enterprises is considering the addition of a new product line. The firm would not need additional factory space, but it would require the purchase of $2.25 million of equipment installed. The equipment would be depreciated using a 7-year accelerated depreciation schedule. Additional inventory of 11% of the projected increase in next year's sales would be necessary prior to each year of operation, but the entire value will be recovered at the end of the project. The firm expects to sell 350,000 units during the first year of the project, increasing to 375,000 units during the next three years before decreasing to 100,000 during the fifth and final year of the project. The product is expected to be obsolete at that point. The expected sales price is $12 per unit with a variable cost of $5 per unit during the first year of operations. Variable costs will increase by 5% per year, but the sales price remains fixed. Fixed costs are estimated at $600,000 during the first year, but will increase by 6% per year. The firm's tax rate is 25%. The equipment has an estimated salvage value of $700,000. What is the estimated net present value of the project assuming a required return of 16%? Management of the firm is concerned about a potential increase in the firm's fixed costs and labor costs. The worst case scenario is an increase of fixed costs to $1,000,000 in the first year due to increasing insurance costs. In addition, health insurance premiums may increase variable costs to $8 per unit. Both of these costs would increase by the percentages listed above. What is the estimated net present value of the project assuming this potential increase in costs? Based on your calculations, what recommendations would you make to the management of Cleveland Enterprises?

would love to see this be shown in two excel spreadsheets, but will need the work shown and solved out

Answer 1

7-year MACRS schedule:

Formula	Year (n)	0	1	2	3	4	5	6	7	8
BVn-1 - Dn	Book value (BV)	2250000	1928475	1377450	983925	702900	501975	301275	100350	0
	Depreciation rate (')		14.29%	24.49%	17.49%	12.49%	8.93%	8.92%	8.93%	4.46%
BV0*r	Depreciation (D)		321525	551025	393525	281025	200925	200700	200925	100350

1). Base-case NPV calculation:

Formula	Year (n)	0	1	2	3	4	5
	Initial investment (II)	2250000
	Sales units (u)		350000	375000	375000	375000	100000
	Price per unit (p)		12.00	12.00	12.00	12.00	12.00
vcn-1*(1+5%)	Variable cost per unit (vc)		5.00	5.25	5.51	5.79	6.08
FCn-1*(1+6%)	Fixed cost (FC)		600000	636000	674160	714610	757486
From the MACRS schedule	Depreciation (D)		321525	551025	393525	281025	200925
u*(p-vc)-FC-D	EBIT		1528475	1344225	1365128	1333819	-366164
25%*EBIT	Tax @ 25%		382119	336056	341282	333455	-91541
EBIT-Tax	Net income (NI)		1146356	1008169	1023846	1000364	-274623
	Add: Depreciation (D)		321525	551025	393525	281025	200925
NI+D	Operating Cash Flow (OCF)		1467881	1559194	1417371	1281389	-73698
Formula	Year (n)	0	1	2	3	4	5
	Sales units (u)		350000	375000	375000	375000	100000
	Price per unit (p)		12.00	12.00	12.00	12.00	12.00
u*p	Sales (S)		4200000	4500000	4500000	4500000	1200000
11%(Sn - Sn-1)	Net Working Capital (NWC)	462000	33000	0	0	-363000	0
Return of NWC in year 6	Less: Inc. in NWC	-462000	429000	33000	0	363000	-363000
	Salvage value (SV)						700000
II - dep. over 5 years	Book value (BV)						501975
salvage value*(1-Tax rate)	Add: After-tax salvage value (ASV)						650494
OCF-Inc. in NWC + ASV - II	Free Cash Flow (FCF)	-2712000	1896881	1592194	1417371	1644389	213796
1/(1+16%)^n	Discount factor @ 16%	1.000	0.862	0.743	0.641	0.552	0.476
FCF*Discount factor	PV of FCF	-2712000	1635242.46	1183259.33	908049.37	908181.35	101790.83
Sum of all PVs	NPV	2024523.33

2). If first year fixed cost is $1,000,000 and first year variable cost per unit is $8 then the NPV is -1,666,933.61

Formula	Year (n)	0	1	2	3	4	5
	Initial investment (II)	2250000
	Sales units (u)		350000	375000	375000	375000	100000
	Price per unit (p)		12.00	12.00	12.00	12.00	12.00
vcn-1*(1+5%)	Variable cost per unit (vc)		8.00	8.40	8.82	9.26	9.72
FCn-1*(1+6%)	Fixed cost (FC)		1000000	1060000	1123600	1191016	1262477
From the MACRS schedule	Depreciation (D)		321525	551025	393525	281025	200925
u*(p-vc)-FC-D	EBIT		78475	-261025	-324625	-444916	-1235807
25%*EBIT	Tax @ 25%		19619	-65256	-81156	-111229	-308952
EBIT-Tax	Net income (NI)		58856	-195769	-243469	-333687	-926855
	Add: Depreciation (D)		321525	551025	393525	281025	200925
NI+D	Operating Cash Flow (OCF)		380381	355256	150056	-52662	-725930
Formula	Year (n)	0	1	2	3	4	5
	Sales units (u)		350000	375000	375000	375000	100000
	Price per unit (p)		12.00	12.00	12.00	12.00	12.00
u*p	Sales (S)		4200000	4500000	4500000	4500000	1200000
11%(Sn - Sn-1)	Net Working Capital (NWC)	462000	33000	0	0	-363000	0
Return of NWC in year 6	Less: Inc. in NWC	-462000	429000	33000	0	363000	-363000
	Salvage value (SV)						700000
II - dep. over 5 years	Book value (BV)						501975
salvage value*(1-Tax rate)	Add: After-tax salvage value (ASV)						650494
OCF-Inc. in NWC + ASV - II	Free Cash Flow (FCF)	-2712000	809381	388256	150056	310338	-438436
1/(1+16%)^n	Discount factor @ 16%	1.000	0.862	0.743	0.641	0.552	0.476
FCF*Discount factor	PV of FCF	-2712000	697742.46	288537.64	96134.69	171396.91	-208745.31
Sum of all PVs	NPV	-1666933.61

Recommendation: As per the base case evaluation, the project can be accepted as it has a positive NPV. However, under the worst case scenario, it is not acceptable. If the probability of the worst case scenario is calculated and other real options are taken into consideration then a more realistic decision can be reached.