Redtree Manufacturing is considering purchasing a machine for $300,000 . This machine would increase sales by 50,000 units in the first year and by 5% in each year after the first year. The sales price is $75.00/unit and the cost of goods sold (CGS) is $40.00/unit. The machine would also reduce selling general and administration costs (SGA) by $100,000 annually. Redtree uses 3-year MACRS for depreciation purposes and the 3-year MACRS depreciation schedule is 33.33%, 44.45%, 14.81% and 7.42% for each year (years 1-4) for the machine. The machine would be depreciated to a zero salvage value but would have a market value of $33,000 at the end of its 5-year operating life. Working capital would increase by $35,000 immediately, but decline by 10% for each year of the project until the last year when working capital would revert to its pre-project level. Redtrees marginal tax rate is 25% and the discount rate is 10%.

1.Calculate the NPV for the project in Excel by using the template I give below as a starting point i.e. recreate the template below in Excel and then complete the spreadsheet. Once the excel sheet is completed, Make sure to highlight your estimates of:

a. Operating CF by year

b. FCF by year

c. Discount factor by year

d. Value at year 0 of each CF

2. Estimate the projects NPV.

3. Use Solver to calculate,

a. The minimum price/unit that could be charged to make the NPV greater than or equal to 0.

b. The maximum cost/unit that could be incurred that would still make the NPV greater than or equal to 0.

Here's the template