. and Value Creation Midwest Canning Corporation Case Midwest Canning Corporation is a manufacturer and distributor of canned vegetables. The company is considering entering a new product market, the canning of premium-grade canned vegetables. Production would occur in an unused portion of the existing plant. Last year, the company spent $100,000 to repair this section of the plant. Machinery would cost $250,000. Shipping cost would be $20,000 and installation charges would be $30,000. Working capital would increase by $10,000 at the time of the initial investment. . The machinery will be depreciated under MACRS using a 3 year life, with 33%, 45%, 15%, and 7% of the cost being depreciated in years 1-4 respectively. The machinery is expected to have a salvage value of $25,000 after 4 years of use. Sales are expected to be 200,000 cans for each of the next four years at a price of $2.00 per can. Fixed and variable operating costs are expected to be $1. . per can The premium-grade vegetables will cannibalize some sales of standard-grade vegetables. It is expected that standard-grade sales will be $20,000 less each year, and operating costs will be $10,000 less Midwest's cost of capital is 10 percent and its marginal tax rate is 40 percent. Any losses on this project will be offset against taxable income from Midwest's other product lines. Questions: 1. Build a spreadsheet model to compute the NPV and IRR of the project 2 Perform a scenario analysis for the following scenarios: Best Case (25% probability)-Sales, Op. Costs, and Sal. Value are 25% improved. Worst Case- (25% probability)Sales, Op. Costs, and Sal. Value are 25% worse. Most Likely Case (50% probability)-As given. 3. Perform a sensitivity analysis, allowing Unit Sales, Salvage Value, and Operating Cost Percent to vary . and Value Creation Midwest Canning Corporation Case Midwest Canning Corporation is a manufacturer and distributor of canned vegetables. The company is considering entering a new product market, the canning of premium-grade canned vegetables. Production would occur in an unused portion of the existing plant. Last year, the company spent $100,000 to repair this section of the plant. Machinery would cost $250,000. Shipping cost would be $20,000 and installation charges would be $30,000. Working capital would increase by $10,000 at the time of the initial investment. . The machinery will be depreciated under MACRS using a 3 year life, with 33%, 45%, 15%, and 7% of the cost being depreciated in years 1-4 respectively. The machinery is expected to have a salvage value of $25,000 after 4 years of use. Sales are expected to be 200,000 cans for each of the next four years at a price of $2.00 per can. Fixed and variable operating costs are expected to be $1. . per can The premium-grade vegetables will cannibalize some sales of standard-grade vegetables. It is expected that standard-grade sales will be $20,000 less each year, and operating costs will be $10,000 less Midwest's cost of capital is 10 percent and its marginal tax rate is 40 percent. Any losses on this project will be offset against taxable income from Midwest's other product lines. Questions: 1. Build a spreadsheet model to compute the NPV and IRR of the project 2 Perform a scenario analysis for the following scenarios: Best Case (25% probability)-Sales, Op. Costs, and Sal. Value are 25% improved. Worst Case- (25% probability)Sales, Op. Costs, and Sal. Value are 25% worse. Most Likely Case (50% probability)-As given. 3. Perform a sensitivity analysis, allowing Unit Sales, Salvage Value, and Operating Cost Percent to vary