Directions: You need to set up the model to calculate NPV in an excel file, and show the answer to each question in an excel worksheet. In other words, the excel file you submit should have four worksheets, corresponding to each question. (Show each step on excel for each problem). Explain how each answer was solved.

Billingham Packaging is considering expanding its production capacity by purchasing a new machine, the XC-750. The cost of the XC-750 is $2.74 million. Unfortunately, installing this machine will take several months and will partially disrupt 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.10 million per year in additional sales, which will continue for the 10 -year life of the machine. Operations: The disruption caused by the installation will decrease sales by $5.03 million this year. As with Billingham's existing products, the cost of goods for the products produced by the XC750 is expected to be 71% of their sale price. The increased production will also require increased inventory on hand of $1.11 million during the life of the project, including year 0 . Human Resources: The expansion will require additional sales and administrative personnel at a cost of $1.94 million per year. Accounting: The XC-750 will be depreciated via the straight-line method over the 10 -year life of the machine. The firm expects receivables from the new sales to be 14% of revenues and payables to be 9% of the cost of goods sold. Billingham's marginal corporate tax rate is 35%. a. Base-case scenario: If the appropriate cost of capital for the expansion is 9.9%, compute the NPV of the purchase. b. Sensitivity Analysis: While the expected new sales will be $10.10 million per year from the expansion, estimates range from $8.00 million to $12.20 million. What is the NPV in the worst case? In the best case? c. Break-even analysis: What is the break-even level of new sales from the expansion? What is the break-even level for the cost of goods sold (in terms of percentage of sales)? d. Billingham could instead purchase the XC-900, which offers even greater capacity. The cost of the XC-900 is $3.93 million. The extra capacity would not be useful in the first two years of operation, but would allow for additional sales in years 3 through 10 . What level of additional sales (above the $10.10 million expected for the XC750 ) per year in those years would justify purchasing the larger machine