complete the excel sheets. there all 0 so they can be filled in

CAPEX Problem Instructions 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 Packaging 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 $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 64% 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 27%. 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 Incr. Sales cocs of 64% Incr, fixed costs Depreciation EBIT Tax 27% NI Capital Expenditure WC Depreciation add-back Net Cash Flow NPV 10% IRR Err.523 Inet. A/R incr. In incr A.P Total iner. WC WC \begin{tabular}{lllllllllll} \hline 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \end{tabular} DOWNSIDE CASE incr. Sades cogs 84% incr. fxed costs Depreciation EBIT Tax 6 (6) 27% N Capital Expendihure A WC Depreciation add-back Net Cash Flaw NPV 810% IPR incr. AR incr. In Incr AP Total iner. WC A WC \begin{tabular}{llllllllll} 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \end{tabular} UPSIDE CASE Incr. Sales Yeac 0 Year 1 Year 2 Year 3 Year 4 Year 5 Yeac 6 Year 7 Year.8 Year 9 Year 10 COGs 864% Incr, fixed costs Depreciation EBIT Tax 027% Ni 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0000 0000 0 0 0 0 0 Capital Expenditure 0 00 0 A WC Depreciation add-back Net Cash Flow 0 0 0 0 0 0 0 \begin{tabular}{ll} 0 & 0 \\ 0 & 0 \\ 0 & 0 \end{tabular} 0 0 Net Cash Flow 0 0 0 0 0 0 NPV (4) 10% IRR 0 En:523 Incr. AR incr. in incr AP Total incer. WC A WC \begin{tabular}{llllllllll} 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \end{tabular} Using the Morin Mothod formulas and the budget and actual data assumptions below, calculate the total variance and the composition between volume, rate and mix and show the results in the Variance and Due to worksheet given. Volume = Change in tatal volume x old product rate x old product mix % Rate = Change in product rate new total volume old product mix\% Mix = Change in product mix % new total volume x new product rate