Question

XYZ, Inc. is considering a 5-year project. The production will require $1,500,000 in net working capital to start and addition net working capital investments each year equal to 15% of the projected sales increase for the following year. Total fixed costs are $1,350,000 per year, variable production costs are $225 per unit, and the units are priced at $345 each. The equipment needed to begin production has an intalled cost of $23,000,000. The equipment is qualified as seven-year MACRS property. MACRS stands for Modified Accelerated Cost Recovery System, where businesses apply MACRS rates to the capital expenditure for annual depreciation amount. In five years, this equipment can be sold for about $4,600,000. The company is in the 35% marginal tax bracket and has a required rate of return on all its projects of 18%.

Year	1	2	3	4	5	6	7	8
Projected unit sales	80,000	85,000	90,000	95,000	95,000	0	0	0
MACRS Rates	14.29%	24.49%	17.49%	12.49%	8.93%	8.92%	8.93%	4.46%

1. Please complete the cash flow estimation table. What are the projected cash flows for each year?
There should be no hard-keyed numbers in the table - all cell reference! Points will be deducted for hard-keyed numbers.
		Year	0	1	2	3	4	5
	Ending book value
	Sales
	Variable costs
	Fixed costs
	Depreciation
	EBIT
	Taxes
	Net income
	Depreciation
	Operating cash flow
	Change in NWC
	CAPEX
	Salvage value
	Total cash flow

Answer 1

Solution:

Details	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5
Ending Book Value		19,713,300	14,080,600	10,057,900	7,185,200	4,600,000
Sales		27,600,000	29,325,000	31,050,000	32,775,000	32,775,000
variable Costs		18,000,000	19,125,000	20,250,000	21,375,000	21,375,000
Fixed Costs		1,350,000	1,350,000	1,350,000	1,350,000	1,350,000
Depreciation*		3,286,700	5,632,700	4,022,700	2,872,700	2,585,200
Loss on sale						-
EBIT		4,963,300	3,217,300	5,427,300	7,177,300	7,464,800
Taxes		1,737,155	1,126,055	1,899,555	2,512,055	2,612,680
Net Income		3,226,145	2,091,245	3,527,745	4,665,245	4,852,120
Depreciation		3,286,700	5,632,700	4,022,700	2,872,700	2,585,200
operating Cash Flow		6,512,845	7,723,945	7,550,445	7,537,945	7,437,320
Change in NWC	(1,500,000)	(4,140,000)	(258,750)	(258,750)	(258,750)	6,416,250
CAPEX	(23,000,000)
SALVAGE VALUE						4,600,000
Total Cash Flow	(24,500,000)	2,372,845	7,465,195	7,291,695	7,279,195	18,453,570
Discounted Cash Flow factor 18%	1.0000	0.8475	0.7182	0.6086	0.5158	0.4371
Discounted Cash Flow	(24,500,000)	2,010,886	5,361,387	4,437,951	3,754,528	8,066,226
Net present value	(869,024)

* in last year (year 5) Deprfeciation will also includes loss on sale of asserts = Ending balance after 4 year - Salvage value. (For the purpose of actual tax calculation)

Figures in negative means cash outflow.