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

i hope the template i shared is visible this time.

3 5 Tyr dpn percentage dpn amount 0.3333 0.4445 0.1481 0.0742 Inputs 0 1 2 3 5 Cap Expense SGA Tax yr Price/un Cost/un Sales Un 75 40 50,000 3,750,000 2,000,000 Term CF MKT Book Gain Tax Term CF NWC NWC reduct Sales CGS Gross Profit SGA Dpn Prof B4T Tax Prof Aft Tx Rev dpn OP CF Cap Expense Term CF NWC Inc NWC Units Price Cost 50000 75 40 Discrate growth 5% FCF Disc fact vo NPV Min price/un for NPV >=0 Min cost/un for NPV >=0