Widget Incorporated has developed a new widget. It would cost $XX1 million to buy the equipment necessary to manufacture the widgets, and it would require net working capital equal to SXX2. The widgets are for industrial application and market research has shown they could sell for $XX3 per unit, with variable costs of $16,500 per widget. After the first year, the sales price and variable costs would increase at the inflation rate of 3.5%. Financial analysts expect fixed costs to be $1 million per year, and would increase with inflation. In the initial year, equipment would be purchased and setup, and the widget project would run for a life of 4 years. The firm believes it could sell 1,000 units per year. The manufacturing equipment can be depreciated over a 5-year period, using MACRS rates. The estimated market value of the equipment at the end of the project's 4-year life is $500,000. The federal-plus-state tax rate is 40% and the financial analysts have estiamted a 10% cost of capital. Develop a spreadsheet model to analyse the project. Calculate NPV, IRR, MIRR and payback. Part 4. Projected Net Cash Flow (Time line of annual cash flows) Years Investment Ourlays at Time Zero: Equipment NOWC needed Operating Cash Flows over the Project's Life: Units sold Sales price per unit Variable costs per unit Sales revenue Variable costs Fixed operating costs Depreciation (equipment) Oper. Income before taxes (EBIT) Taxes on operating income (40%) After-tax operating profit Add back depreciation Operating cash flow Terminal Year Cash Flows: Return of NOWC Net salvage value Total termination cash flows Widget Incorporated has developed a new widget. It would cost $XX1 million to buy the equipment necessary to manufacture the widgets, and it would require net working capital equal to SXX2. The widgets are for industrial application and market research has shown they could sell for $XX3 per unit, with variable costs of $16,500 per widget. After the first year, the sales price and variable costs would increase at the inflation rate of 3.5%. Financial analysts expect fixed costs to be $1 million per year, and would increase with inflation. In the initial year, equipment would be purchased and setup, and the widget project would run for a life of 4 years. The firm believes it could sell 1,000 units per year. The manufacturing equipment can be depreciated over a 5-year period, using MACRS rates. The estimated market value of the equipment at the end of the project's 4-year life is $500,000. The federal-plus-state tax rate is 40% and the financial analysts have estiamted a 10% cost of capital. Develop a spreadsheet model to analyse the project. Calculate NPV, IRR, MIRR and payback. Part 4. Projected Net Cash Flow (Time line of annual cash flows) Years Investment Ourlays at Time Zero: Equipment NOWC needed Operating Cash Flows over the Project's Life: Units sold Sales price per unit Variable costs per unit Sales revenue Variable costs Fixed operating costs Depreciation (equipment) Oper. Income before taxes (EBIT) Taxes on operating income (40%) After-tax operating profit Add back depreciation Operating cash flow Terminal Year Cash Flows: Return of NOWC Net salvage value Total termination cash flows