In: Finance
Spider Lamp is considering the manufacture of a new lamp. Equipment necessary for production will cost $9 million and be depreciated on a straight-line basis over the eight-year life of the product to $1 millions salvage value. The lamp will retail for $110. The company expects to sell 100,000 lamps per year. Fixed costs will be $2,000,000 per year and variable costs are $45 per lamp. Production will require an investment in net working capital of $500,000. The tax rate is 40 percent Perform a scenario analysis using 10 percent, 20 percent, and 30 percent cost of capital. Calculate IRR and NPV for each case.
Calculation of Cash Flows of the Project | |||||||||
Particulars | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
Initial Investment | |||||||||
Cost of Equipment | -9000000 | ||||||||
Investment in net working capital | -500000 | ||||||||
Net Investment (A) | -9500000 | ||||||||
Operating Cash Flows | |||||||||
Sales Units (B) | 100000 | 100000 | 100000 | 100000 | 100000 | 100000 | 100000 | 100000 | |
Annual Sales (C = B*$110) | 11000000 | 11000000 | 11000000 | 11000000 | 11000000 | 11000000 | 11000000 | 11000000 | |
Variable Costs (D = B*$45) | 4500000 | 4500000 | 4500000 | 4500000 | 4500000 | 4500000 | 4500000 | 4500000 | |
Fixed Costs (E ) | 2000000 | 2000000 | 2000000 | 2000000 | 2000000 | 2000000 | 2000000 | 2000000 | |
Depreciation (F) ($9 million - $1 million) / 8 years |
1000000 | 1000000 | 1000000 | 1000000 | 1000000 | 1000000 | 1000000 | 1000000 | |
Profit Before Tax (G = C-D-E-F) | 3500000 | 3500000 | 3500000 | 3500000 | 3500000 | 3500000 | 3500000 | 3500000 | |
Tax @40% (H = G*40%) | 1400000 | 1400000 | 1400000 | 1400000 | 1400000 | 1400000 | 1400000 | 1400000 | |
Profit After Tax (I = G-H) | 2100000 | 2100000 | 2100000 | 2100000 | 2100000 | 2100000 | 2100000 | 2100000 | |
Add back Depreciation (J = F) | 1000000 | 1000000 | 1000000 | 1000000 | 1000000 | 1000000 | 1000000 | 1000000 | |
Net Operating Cash Flows (K = I+J) | 3100000 | 3100000 | 3100000 | 3100000 | 3100000 | 3100000 | 3100000 | 3100000 | |
Terminal Value | |||||||||
Salvage Value (L) | 1000000 | ||||||||
Tax on sale (M = L*40%) | 400000 | ||||||||
After tax salvage Value (N = L-M) | 600000 | ||||||||
Recovery of Working Capital (O) | 500000 | ||||||||
Net Terminal Value (P = N+O) | 1100000 | ||||||||
Total Cash Flows (Q = A+K+P) | -9500000 | 3100000 | 3100000 | 3100000 | 3100000 | 3100000 | 3100000 | 3100000 | 4200000 |