I need to see tis chart filled out for this in base, upside and downside

3 M N N o 0 a P Q R R S T U v A B D E F H J L 1 CAPEX Problem Instructions 2 3 Using the following assumptions, Calculate the NPV and IRR for a Base Case, a Downside Case, and an Upside Case 4 Note that the worksheet has spaces for each case, set up (hopefully) in print friendly format 5 6 Assumptions: 7 8 Kinston Pachaging is considering expanding its capacity by purchasing a new machine, the XC-450. The cost is $2.75 milion. 9 Unfortunately, installing this machine will take several months and will partially disrupt current production. The fim has just 10 completed a $50,000 feasibility study to analyze the decision to buy the XC-750, resulting in the folowing estimates: 11 12 Marketing: 3 Once the XC-750 is operational next year, the extra capacity is expected to generate $10 milion per year in additional sales 14 which will continue for the ten-year life of the machine. 15 16 Operations 17 The disruption caused by the installation wil decrease sales by $5 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 19 will also require increased inventory on hand of S1 milion during the life of the phoject, including year 0. 20 21 Human Resources: 22 The expansion will require additional sales and adminstrative personnel at a cost of $2 million per year. 23 24 Accounting: 25 The XC-750 will be depreciated via the straight line method over the ten-year life of the machine. 6 Consistent with current experience, the firm expects receivables to be 15% of revenue and payables to be 10% of cost of goods sold 27 on ANY incermental business assoclated with this project {positive or negative). 28 Kinston's marginal tax rate is 35%. No salvage value is assumed for the machine. 29 0 COO's instructions: 81 While the base case assumptions seem reasonable, she believes that actual sales could range from $8 million to $12 million 2 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. 33 $4 35 36 37 38 89 10 11 12 13 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 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 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 O 0 0 0 0 0 0 0 0 0 0 0 0 NPV @ 10% IRR #NUM! 0 0 Incr. AVR 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 o 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