Question

FuncoLand has developed an efficient, new cloud server that that it can sell to other corporations to boost online operations and stability. For FuncoLand, it would cost $10,000,000 at Year 0 to buy the equipment necessary to manufacture the server. The project would require net working capital at the beginning of each year in an amount equal to 10% of the year’s projected sales; for example, NWC0 = 10% (Sales1). The servers would sell for $24,000 per unit, and specialists estimate that variable costs would amount to $17,500 per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 3%. The company’s nonvariable costs would be $1,000,000 at Year 1 and would increase at the inflation rate each year thereafter. The server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 years. Also, the project’s returns are expected to be highly correlated with returns on the firm’s other assets. The firm believes it could sell 1,000 units per year. The equipment would be depreciated over a 5-year period, using MACRS rates (see page 500). The estimated market value of the equipment at the end of the project’s 4-year life is $500,000. FuncoLand’s federal-plus-state tax rate is 30%. Its cost of capital is 10%.

Cash Flow Estimation

A) Fill in the input data for the model.

Equipment Cost
Net Operating Working capital/sales
First year sales (in units)
Sales price per unit
Varible cost per unit (excel. depr.)
Nonvariable cost (excl. depr.)
Market value of equipment at year 4
Tax rate
Wacc
Inflation in price and costs

B) Calculate the sales revenues for each year.

Year	0	1	2	3	4
Units sold
Sales price per unit (excl. depr.)
Variable costs per unit (excl. depr.)
Nonvariable costs (excl. depr.)
variable costs
Sales revenue
Net Operating Working Capital

C) Calculate the net cash flow due to salvage.

Year	0	1	2	3	4
Basis for depreciation
Annual equipment depr. rate
Annual depreciation expense
Ending Bk Val: Cost - Accum Dep
Salvage value
Profir (or loss) on salvage
Tax on profit (or loss)
Net cash flow due to salvage

D) Calculate net cash flows for each year.

Years	0	1	2	3	4
Sales revenue (per 1000 units)
Variable costs (per 1000 units)
Nonvariable operating costs
Depreciation (equipment)
Oper. income before tax (EBIT)
Taxes on operating Income (30%)
Net operating profit after taxes
add back depreciation
Equippment purchases
Cash flow due to change in NOWC
Net cash flow due to salvage
Net Cash Flow

Answer 1

	b	c	d	e	f	g	h
2	A) Input Data
3
4	Input Data
5	Equipment cost		10000000
6	Net operating working capital/Sales		0.1
7	First year sales (in units)		1000
8	Sales price per unit		24000
9	Variable cost per unit (excl. depr.)		17500
10	Nonvariable costs (excl. depr.)		1000000
11	Market value of equipment at Year 4		500000
12	Tax rate		30%
13	WACC		10%
14	Inflation in prices and costs		3%
15
16	B) Sales Revenues
17
18	Sales Revenues
19	Year	0	1	2	3	4
20	Units sold		1000	1000	1000	1000
21	Sales price per unit (excl. depr.)		24000	24720	25461.6	26225.448		24000 x 1.03
22	Variable costs per unit (excl. depr.)		17500	18025	18565.75	19122.723		17500 x 1.03
23	Nonvariable costs (excl. depr.)		1000000	1030000	1060900	1092727		1000000 x 1.03
24	Variable costs		17500000	18025000	18565750	19122723		units x VC p.u.
25	Sales revenue		24000000	24720000	25461600	26225448		Units x SP P.u,
26	Net Operating Working Capital	2400000	2472000	2546160	2622544.8	0		Next year sales x 10%
27
28	C) Salvage
29
30	Year	0	1	2	3	4
31	Basis for depreciation\		10000000	10000000	10000000	10000000
32	Annual equipment depr. rate		20.00%	32.00%	19.20%	11.52%
33	Annual depreciation expense		2000000	3200000	1920000	1152000		(Base x Rate)
34	Ending Bk Val: Cost – Accum Dep'rn		8000000	4800000	2880000	1728000		(Last year ending value - current year depreciation)
35	Salvage value					500000
36	Profit (or loss) on salvage					-1228000		Salvage value - Carrying value at the end)
37	Tax on profit (or loss)					-368400		Loss x 30%
38	Net cash flow due to salvage					868400		Salvage value - Tax
39
40	D) Cash Flows
41
42	Years	0	1	2	3	4
43	Sales revenue (per 1,000 units)		24000000	24720000	25461600	26225448
44	Variable costs (per 1,000 units)		17500000	18025000	18565750	19122723
45	Nonvariable operating costs		1000000	1030000	1060900	1092727
46	Depreciation (equipment)		2000000	3200000	1920000	1152000
47	Oper. income before taxes (EBIT)		3500000	2465000	3914950	4857998.5		(sales - VC - FC - Dep)
48	Taxes on operating income (30%)		1050000	739500	1174485	1457399.6
49	Net operating profit after taxes		2450000	1725500	2740465	3400599
50	Add back depreciation		4450000	4925500	4660465	4552599
51	Equipment purchases	-10000000
52	Cash flow due to change in NOWC	-2400000	-72000	-74160	-76384.8	2622544.8
53	Net cash flow due to salvage					868400
54	Net Cash Flow	-12400000	4378000	4851340	4584080.2	8043543.8
55
56
57	NPV =	4527307.81	=NPV(10%,D54:G54)+C54
58	IRR =	24.3755%	=IRR(C54:G54)
59	MIRR =	18.9007%	=MIRR(C54:G54,10%,10%)
60	Payback and discounted payback =
61	Year	Cashflow	PV factor @ 10% =1/1.1^year	PV of cashfllow	Cumulative cashflow	cumulative PV of cashflows
62	0	-12400000	1	-12400000	-12400000	-12400000
63	1	4378000	0.909091	3980000	-8022000	-8420000
64	2	4851340	0.826446	4009371.9	-3170660	-4410628.1
65	3	4584080.2	0.751315	3444087.3	1413420.2	-966540.8
66	4	8043543.75	0.683013	5493848.6	9456964	4527307.8
67
68	Payback =	2.691667655	=2+(3170660/4584080.2)
69	Discounted payback =	3.175931458	= 3 +(966540.8/5493848.6)