Question

With the growing popularity of casual surf print clothing, two recent MBA graduates decided to broaden this casual surf concept to encompass a “surf lifestyle for the home.” With limited capital, they decided to focus on surf print table and floor lamps to accent people’s homes. They projected unit sales of these lamps to be 8,500 in the first year, with growth of 7 percent each year for the next five years. Production of these lamps will require $50,000 in net working capital to start. The net working capital will be recovered at the end of the project. Total fixed costs are $110,000 per year, variable production costs are $22 per unit, and the units are priced at $50 each. The equipment needed to begin production will cost $190,000. The equipment will be depreciated using the straight-line method over a five-year life and is not expected to have a salvage value. The effective tax rate is 25 percent and the required rate of return is 24 percent. What is the NPV of this project? (Do not round intermediate calculations and round your answer to 2 decimal places, e.g., 32.16.) I need this calculation in Excel to compare toward my calculation. I cannot get correct result due to some number is not adding up.

Answer 1

Sol-

• Initial Investment = Equipment Cost + Working Capital = 190,000+ 50,000 = $240,000
• Calculation of Cash Flow each year :-

Particulars	Year 1	Year 2	Year 3	Year 4	Year 5
Unit Sales (increasing @ 7% p.a.)	8,500	9,095	9,732	10,413	11,142
Contribution per unit [50-22]	28	28	28	28	28
Total Contribution	238,000	254,660	272,496	291,564	311,976
(-) Fixed Costs	110,000	110,000	110,000	110,000	110,000
(-) Depreciation [190,000/5]	38,000	38,000	38,000	38,000	38,000
= Net Income	90,000	106,660	124,496	143,564	163,976
(-) Tax @ 25%	22,500	26,665	31,124	35,891	40,994
Profit after Tax	67,500	79,995	93,372	107,673	122,982
(+) Depreciation	38,000	38,000	38,000	38,000	38,000
= Cash Flow after Tax	105,500	117,995	131,372	145,673	160,982

• Calculation of NPV:-

Year	Cash Flow	DF @ 24%	Present value of cash inflows
1	105,500	0.8065	85,086
2	117,995	0.6504	76,744
3	131,372	0.5245	68,905
4	145,673	0.4230	61,620
5	210,982[160,982+50000]	0.3411	71,966

Total present value of all cash inflows = $ 364,321

Initial Investment = $ 240,000

Hence Net Present Value = Total present value of all cash inflows - Initial Investment = $364,321- $240,000= 124,321

Net Present Value [NPV] of this project is $ 124,321