QUESTION: Using an Excel spreadsheet (please show the formula of each cell). Find the NPV, IRR and Payback of the project by using the pro-forma financial statement method to determine cash flows.

Reminders:

Enter the input variables in cells of their own at the top of the spreadsheet

Set up the necessary equations by referencing to the input variable cells.

The spreadsheet must be formula driven; do not put any numbers in equations, only cell references.

Use Excels built-in functions wherever possible (e.g. PV and IRR functions)

Shrieves Casting Company is considering adding a new line to its product mix, and the capital budgeting analysis is being conducted by Sidney Johnson, a recently graduated MBA. The production line would be set up in unused space in the main plant. The plant is leased out to another firm for $25,000 per year. They have to buy new machinery. The approximate cost of the machine would be $200,000, plus $10,000 in shipping and handling charges. Installing the equipment would also cost an additional $30,0000. The machinery has an economic life of 5 years and would fall under Class 8 with a CCA rate of 20%. The machinery is expected to have a salvage value of $25,000 after 5 years of use. The company will retool one of its existing manufacturing facilities to produce the new model. The one-time retooling cost is $1,700. There will also be $8,000 in retraining costs incurred for workers who lost their jobs manufacturing the existing product. The new product line would generate incremental sales of 1,250 units per year for 5 years and they are expected to grow 10% per year. The cost per unit is estimated at $75 per unit in the first year. Each unit can be sold for $200 in the first year. The sales price and cost are both expected to increase by 2.5% per year due to inflation. The fixed costs are estimated to be $100,000 per year and would increase with inflation. To handle the new product line, the firm's net operating working capital would have to increase by an amount equal to 15% of sales revenues. The firm tax rate is 35%, and its overall weighted average cost of capital (WACC) is 14%. According to the financial department, the project is as risky as the company. Shrieves Casting Company is considering adding a new line to its product mix, and the capital budgeting analysis is being conducted by Sidney Johnson, a recently graduated MBA. The production line would be set up in unused space in the main plant. The plant is leased out to another firm for $25,000 per year. They have to buy new machinery. The approximate cost of the machine would be $200,000, plus $10,000 in shipping and handling charges. Installing the equipment would also cost an additional $30,0000. The machinery has an economic life of 5 years and would fall under Class 8 with a CCA rate of 20%. The machinery is expected to have a salvage value of $25,000 after 5 years of use. The company will retool one of its existing manufacturing facilities to produce the new model. The one-time retooling cost is $1,700. There will also be $8,000 in retraining costs incurred for workers who lost their jobs manufacturing the existing product. The new product line would generate incremental sales of 1,250 units per year for 5 years and they are expected to grow 10% per year. The cost per unit is estimated at $75 per unit in the first year. Each unit can be sold for $200 in the first year. The sales price and cost are both expected to increase by 2.5% per year due to inflation. The fixed costs are estimated to be $100,000 per year and would increase with inflation. To handle the new product line, the firm's net operating working capital would have to increase by an amount equal to 15% of sales revenues. The firm tax rate is 35%, and its overall weighted average cost of capital (WACC) is 14%. According to the financial department, the project is as risky as the company