Question

Problem - Capital Budgeting

CGX Transmitters is developing a 2nd generation optical transmitter. The project will last for three years, at that time work will begin on 3rd generation optical transmitters.  CGX always uses straight line depreciation.  The equipment will cost $12.5M and at the end of three years, the salvage value of the equipment will be $3.5M.  Sales are expected to be $20M in year 1 and grow at 35% in years 2 and 3.  Cost of Goods Sold will be 60% of sales. Fixed costs are $5M/year. Net working capital requirements are $1.5M, 1.7M, and 1.4M in years 1-3 respectively.  The company’s tax rate is 25%.  The discount rate is 15%.

Using Excel:

1) Complete a Capital Budget for this project including NPV and IRR

2) Perform an NPV Sensitivity Analysis on Year 1 sales between $17-22M in increments of $1M

Answer 1

Solution:

Following is Capital Budget for this project including NPV and IRR and it also includes NPV Sensitivity Analysis on Year 1 sales between $17-22M in increments of $1M using Excel.

Formulas: