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 require 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 o 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 Ooo 0 0 0 0 0 0 ooo 0 0 0 Ooo 0 0 0 0 : 0 0 0 0 0 0 0 0 0 0 0 0 0 Capital Expenditure A WC Depreciation add-back Net Cash Flow Ooo 0 0 0 0 Ooo 0 0 Ooo ooo 0 0 NPV @ 10% IRR #NUM! 0 0 0 0 0 0 Incr. A/R Incr. Inv Incr A/P Total incr. WC A WC olo o 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 DOWNSIDE CASE Year o 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 0 0 0 0 ; 0 ; 0 0 0 0 ; 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Capital Expenditure A WC Depreciation add-back Net Cash Flow 0 0 0 0 0 ; 0 0 0 0 0 0 0 0 0 0 0 0 0 NPV @ 10 IRR #NUM! 0 0 0 0 0 Incr. A/R Incr. Inv Incr A/P Total incr. WC A WC 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 UPSIDE CASE Year o 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 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Capital Expenditure A WC Depreciation add-back e Net Cash Flow ooo 0 0 0 0 0 0 0 0 0 O O O ooo 0 0 0 0 NPV @ 10% IRR #NUM! o 0 0 0 0 0 0 0 Incr. A/R Incr. Inv Incr A/P Total incr. WC A WC 0 0 0 0 0 0 0 0 000 Ooooo ooooo 0 0 0 0 0 0 0 0 0 0 0 0 Ooood 0 0 0 0 0 0 0 0 0
