A Company Asked One of Their Analysis Team to Analyze

Essay

A company asked one of their analysis team to analyze and create models that help decide whether they should manufacture a particular product or outsource its production. The different components are given below:
Fixed Cost, FC = $25,000
Material Cost per Unit, MC = $2.15
Labor Cost per Unit, LC = $2.00
Outsourcing Cost per Unit, O = $4.50
Note that per-unit material and labor cost together make up the variable cost per unit. a. Using the spreadsheet model, construct a one-way data table with production volume as the column input and savings due to outsourcing as the output. Breakeven occurs when profit goes from a positive to a negative value, that is, breakeven is when total outsource cost = total cost, yielding savings due to outsourcing equal to zero. Vary production volume from 0 to 100,000 in increments of 10,000. In which interval of production volume does breakeven occur?
b. Use Goal Seek to find the exact breakeven point. Assign Set cell: equal to the location of savings due to outsourcing, To value: = 0, and By changing cell: equal to the location of the production volume in your model.