Garca and Martinez manufacture widgets and currently have $3 million in taxable income. The company is considering

Fantastic news! We've Found the answer you've been seeking!

Question:

García and Martinez manufacture widgets and currently have $3 million in taxable income. The company is considering an expansion, and they’ve asked you to evaluate the project. The expansion requires the firm to produce 80,000 widgets a year for 6 years, and the company estimates they can sell them for $28 per widget. García and Martinez estimate they will need an additional $4,000,000 worth of machinery. The machinery costs $200,000 a year to operate and maintain. The machinery’s depreciable life is 8-years, and the company expects to salvage the machinery for $1,000,000 at the end of Year 6. If the project is accepted, the company will immediately increase inventory by $500,000 and maintain the new inventory level over the project’s life. Similarly, the company will immediately add $75,000 to their cash balance at start-up and maintain that higher cash balance over the project’s life. The investments in cash and inventory will be recovered when the project is completed. The marginal cost of producing a widget is $6.00 and the cost of capital is 12%. Calculate the project’s NPV by linking to the named variables in Column K.

1) Define a base-case, best-case and worst-case scenario using the Scenario Manager in Excel’s What-If Analysis. Use the following value ranges for the best and worst cases:

a. Per unit price is plus/minus $5

b. Quantity sold is plus/minus 15,000 units

c. Marginal cost of producing a widget is plus/minus $1.50

2) Using the Scenario Manager in Excel’s What-If Analysis, create a scenario summary (this will create a new worksheet titled Scenario Summary; do NOT change the name). The left-hand column in the scenario summary on the new worksheet should list the changing cells as: Quantity, VariableCost, Price (example below).

SHOW WORK IN EXCEL WITH FORMULAS AND BROKEN-DOWN STEPS.