(please solve using excel)

Whitby Corp. is considering investing in a new machine for its factory to produce brake pads. The company can choose either Machine A or Machine B. The life span of each machine is 5 years and depreciation is straight line to zero salvage value. Machine A has a delivered cost of $4 million and Machine B has a delivered cost of $10 million. The annual fixed costs, excluding factory rent, are estimated to be $650,000 for Machine A and $300,000 for machine B. The new machine will be located in factory space that is currently unoccupied. The accounting department will allocate annual factory space rental costs of $500,000 to Machine A and $400,000 to Machine B which has a smaller footprint. The marketing and feasibility study to determine if Whitby should enter the brake pad business cost $1,000,000.

The brake pads are to be sold for $7.00 per unit. The variable production costs are estimated to be $2.50 per unit for Machine A and $1.75 per unit for Machine B. The corporate tax rate is 35.5%.

Using the weighted average cost of capital (WACC) from Question 1, build a financial model in Excel to determine the following.

1. Calculate the annual free cash flow (FCF) for eachMachine and the net present value (NPV) of each Machine. Which Machine should Whitby buy if it can sell 1 million units of the product per year?
2. Determine if your recommended Machine changes if the annual unit sales are 500,000 units. Will your recommended Machine change it the annual unit sales are 1,500,000? Show your calculations.

Please provide excel sheet, if possible.