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%. Projected Unit Sales Year 1 - 80000 Year 2 - 85000 Year 3 - 90000 Year 4 - 95000 Year 5 - 95000 What would the depreciation,EBIT, Taxes, and NI estimation table for years 1-5?

Input Area                              
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%

Answer 1

We will first calculate depreciation

Year	cost of asset	depreciation rate	depreciation expense
1	$23,000,000	14.29%	$3,286,700[$23,000,000*14.29%]
2	$23,000,000	24.49%	$5,632,700[$23,000,000*24.49%]
3	$23,000,000	17.49%	$4,022,700[$23,000,000*17.49%]
4	$23,000,000	12.49%	$2,872,700[$23,000,000*12.49%]
5	$23,000,000	8.93%	$2,053,900[$23,000,000*8.93%]
6	$23,000,000	8.92%	$2,051,600
7	$23,000,000	8.93%	$2,053,900 [$23,000,0008.93%]
8	$23,000,000	4.46%	$1,025,800

we will now prepare income statement

Year	1	2	3	4	5	6	7	8
Sales	$27,600,000(80,000*$`345)	$29,325,000($345*85,000)	$31,050,000($345*90,000)	$32,775,000($345*95,000)	$32,775,000($345*95,000)	0	0	0
Less: variable costs	$18,000,000($225*80,000)	$19,125,000($225*85,000)	$20,250,000($225*90,000)	$21,375,000($225*95,000)	$21,375,000($225*95,000)	0	0	0
Less: fixed costs	$1,350,000	$1,350,000	$1,350,000	$1,350,000	$1,350,000	0	0	0
Less: Depreciation	$$3,286,700	$5,632,700	$4,022,700	$2,872,700	$2,053,900	0	0	0
EBIT (sales-variable cost-fixed cost-depreciation)	$4,963,300‬	$3,217,300	$5,427,300	$7,177,300	$7,996,100	0	0	0
Less: taxes 35%	$1,737,155($4,963,300*35%)	$1,126,055	$1,899,555(5,427,300*35%)	$2,512,055 ($7,177,300*35%)	$2,798,635	0	0	0
Net Income(NI)	$3,226,145[$4,963,300-$1,737,155]	$2,091,245 [$3,217,300-$1,126,055]	$3,527,745[$5,427,300-$1,899,555]	$4,665,245	$5,197,465	0	0	0