Question

Perfect Lawns and Gardens is a small lawn equipment manufacturer. The company is analyzing a proposed project. It expects to sell 3,000 push lawn mowers, give or take 15 percent. The expected variable cost per unit is $95 and the expected fixed costs are $125,000 per year. Both cost estimates are considered accurate within a plus or minus 5 percent range. The sale price is estimated at $180 a unit, give or take 2 percent. The project requires $240,000 of fixed assets, which will be worthless when the project ends in four years. The assets will be depreciated according to the 5-year MACRS depreciation schedule. Also required is $65,000 of net working capital investment to start the project. The tax rate is 21 percent and the required rate of return is 12 percent. What is the net present value of the worst-case scenario? What is the IRR of the worst-case scenario? Please use excel.

Answer 1

NPV of worst-case scenario: -$75,543.01 [negative amount; See Note 3]
IRR of the worst-case scenario: 0.871% [See Note 4]

Note 1: Calculation of Profits after tax, before Depreciation:

	No. or Amount	Change	Worst Change	Result
Units	3,000	15%	-15%	2,550.00
Sale Price	180.00	2%	-2%	176.40
VC	95.00	5%	5%	99.75
Contribution				76.65
Total Contribution				1,95,457.50
Fixed Costs	1,25,000.00	5%	5%	1,31,250.00
Profit (before depreciation)				64,207.50
Tax				13,483.58
Profit after tax before Depreciation				50,723.93

Note 2: Depreciation and Tax Effect as per MACR
It is assumed that the Depreciation as per MACR on year 5 (which is one year after the project expires in year 4) is claimed as expense and tax effect to be availed in year 5.

Year	Depreciation	Tax savings
1	48,000.00	10,080.00
2	76,800.00	16,128.00
3	46,080.00	9,676.80
4	27,648.00	5,806.08
5	13,824.00	2,903.04

Note 3: Net Present Value:

Year	Profits and Investments	Tax saved on Depreciation	Working capital	Net Cash Flow
0	-2,40,000.00		-65,000.00	-3,05,000.00
1	50,723.93	10,080.00		60,803.93
2	50,723.93	16,128.00		66,851.93
3	50,723.93	9,676.80		60,400.73
4	50,723.93	5,806.08	65,000.00	1,21,530.01
5		2,903.04		2,903.04

Applying NPV function on Net Cash Flows [remember to exclude Year 0 amounts in cell range, but consider them separately), we get NPV as -$75,543.01.

Note 4: IRR
Applying IRR function and selecting entireNet Cash Flow cell ranges, we get IRR as 0.871%.

Hope this helps.
If you are taught to use any assumption different than here, make mention of the same and I can rework accordingly.
Consider leaving a thumbs up.
Good luck!