1 CAPEX Problem Instructions 3 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: 3 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 6 Incr. Sales Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 \begin{tabular}{l|l|l|l} Year 7 & Year 8 & Year 9 & Year 10 \\ \hline \end{tabular} 7 COGS@70\% 8 Incr. fixed costs 9 Depreciation 10 EBIT 11 Tax @ 35\% 12NI 4 Capital Expenditure 15WC 16 Depreciation add-back 17 Net Cash Flow \begin{tabular}{|r|r|r|r|r|r|r|r|} \hline & 0 & 0 & 0 & & 0 \\ \hline & 0 & 0 & 0 & 0 & 0 & 0 \\ \hline & 0 & 0 & 0 & 0 & 0 & 0 \\ \hline & & 0 & & & \\ \hline \#NUM! & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline \end{tabular} NPV @ 10\% Incr. A/R 23 Incr. Inv Incr A/P Total incr. WC WC