Question $3$: Capital Budgeting $($Excel$)$

$6/35$ Points

Billingham Packaging is considering expanding its production capacity by purchasing a new machine, the XC$-750.$ The cost of the XC$-750$ is $$2\mathrm{.}68$ million. Unfortunately, installing this machine will take several months and will partially disrupt production. The firm has just completed a $$45,000$ feasibility study to analyze the decision to buy the XC$-750,$ resulting in the following estimates:

I. Marketing: Once the XC$-750$ is operating next year, the extra capacity is expected to generate $$10\mathrm{.}15$ million per year in additional sales, which will continue for the $10-$year life of the machine.

II$.$ Operations: The disruption caused by the installation will decrease sales by $$5\mathrm{.}01$ million this year. Once the machine is operating next year, the cost of goods for the products produced by the XC$-750$ is expected to be $71\%$ of their sale price. The increased production will require additional inventory on hand of $$1\mathrm{.}12$ million to be added in year $0$ and depleted in year $10.$

III. Human Resources: The expansion will require additional sales and administrative personnel at a cost of $$1\mathrm{.}97$ million per year.

IV$.$ 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 $15\%$ of revenues and payables to be $10\%$ of the cost of goods sold. Billingham$$s marginal corporate tax rate is $35\%.$

Using an excel file, answer the following questions.

$1.$ Determine the incremental earnings from the purchase of the XC$-750.$

$2.$ Determine the free cash flow from the purchase of the XC$-750.$

$3.$ If the appropriate cost of capital for the expansion is $9\mathrm{.}9\%,$ compute the NPV of the purchase.

$4.$ While the expected new sales will be $$10\mathrm{.}15$ million per year from the expansion, estimates range from $$8\mathrm{.}20$ million to $$12\mathrm{.}1$ million. What is the NPV in the worst case? In the best case?

$5.$ What is the break$-$even level of new sales from the expansion?

$6.$ What is the break$-$even level for the cost of goods sold?