A company has requested you to analyze and create a model to help decide whether or not it should manufacture a new product in-house or outsource the production of it. Inputs into the model are:

Fixed Cost of Manufacturing In-House: $25,000

Variable Costs Associated with the Decision:

In-house Labor Cost per Unit: $2

In-house Material Cost per Unit: $2.15

Outsourced Purchase Cost per Unit: $4.50

1. Build a spreadsheet model clearly indicating with formatting the inputs and calculated costs associated with each option.

2. Perform a "What-If" Analysis using a one-variable Data Table in Excel to identify the production volume where breakeven occurs between in-house and outsourcing of the new product:

a. Create an output formula in a cell that calculates the "savings" (i.e., the difference in costs between in-house production and outsourcing) for a given production volume.

b. Then, using Excel's "What-if" functionality, create a one-variable data table to evaluate the savings (or cost difference) between the two options as production volume varies from 0 to 100,000 (in increments of 10,000 units).

c. In which interval of production volumes does "breakeven" occur? Highlight these cells in your table.