Using the following assumptions, Calculate the NPV and IRR for a Base Case, a Downside Case, and an Upside Case Note that the worksheet has spaces for each case, set up (hopefully) in print friendly format. Assumptions: Kinston Pachaging is considering expanding its capacity by purchasing a new machine, the XC-450. The cost is $2.75 million. Unfortunately, installing this machine will take several months and will partially disrupt current production. The firm has just completed a $50,000 feasibility study to analyze the decision to buy the XC-750, resulting in the following estimates: Marketing: Once the XC-750 is operational next year, the extra capacity is expected to generate $10 million per year in additional sales which will continue for the ten-year life of the machine. Operations: The disruption caused by the installation will decrease sales by $4 million this year. As with Kinston's existing products, the cost of goods for the producted produced by the XC-750 is expected to be 70% of their sale price. The increased production will also require increased inventory on hand of $1 million during the life of the project, including year 0. Human Resources: The expansion will requi additional sales and adminstrative personnel at a cost of $2 million per year. Accounting: The XC-750 will be depreciated via the straight line method over the ten-year life of the machine. Consistent with current experience, the firm expects receivables to be 15% of revenue and payables to be 10% of cost of goods sold on ANY incermental business associated with this project (positive or negative). Kinston's marginal tax rate is 35%. No salvage value is assumed for the machine. COO's instructions: While the base case assumptions seem reasonable, she believes that actual sales could range from $8 million to $12 million. Therefore she has asked that you determine both the NPV and IRR for not only the base case, but also for a downside and upside. BASE CASE Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Incr. Sales COGS @ 70% Incr. fixed costs Depreciation EBIT Tax @ 35% NI 0 0 0 Ooo OOO OOO ooo 0 0 0 0 0 0 0 Ooo 0 0 Capital Expenditure AWC Depreciation add-back Net Cash Flow OOO 0 Ooo 0 0 0 0 Ooo 0 0 0 0 O O O ooo 0 0 Ooo OOO 0 0 o NPV @ 10% IRR A O 0 0 0 0 0 0 0 Incr. A/R Incr. Inv Incr A/P Total incr. WC AWC 0 Ooo 0 0 0 0 0 0 0 OO 0 DOWNSIDE CASE Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Incr. Sales COGS @ 70% Incr. fixed costs Depreciation EBIT Tax @ 35% NI 0 0 0 ooo 0 0 O O o OOO OOO 0 0 0 0 0 0 Capital Expenditure WC Depreciation add-back Net Cash Flow O O O Ooo 0 0 OOO 0 0 0 0 NPV @ 10% IRR O 0 0 0 0 Incr. AR Incr. Inv Incr A/P Total incr. WC WC Oo ooo 0 ooooo olo o 0 Oo ooo 0 OOOOO ooooo OOOOO ooooo 0 0 0 0 O o 0 0 0 UPSIDE CASE Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Incr. Sales COGS @ 70% Incr. fixed costs Depreciation EBIT Tax @ 35% NI 0 0 0 O O o O O O OOO OOO 0 O O O OOO OOO OOO ooo 0 OOO 0 0 0 0 0 0 Capital Expenditure AWC Depreciation add-back Net Cash Flow OOo 0 ooo OOO OOO 0 0 OOO ooo OOO OOO 0 0 0 0 0 0 NPV @ 10% IRR A 0 0 0 0 0 0 Incr. A/R Incr. Inv Incr A/P Total incr. WC AWC O O OOO 0 0 0 ooooo 000 0 Oo ooo 0 Oo ooo 0 OOOOO 0 0 o o 0