Question

General Blast Inc. makes plastic components for toy vehicles and has estimated the following for a new design of one of its truck models. Fixed cost = $42,000 Variable cost per unit = $2.20 General Blast Inc. is considering outsourcing its production to one of its subcontractors for $3.73 per unit. The company anticipates that the demand for the new product would be 20,000 units and would like to compare the costs of manufacturing the new plastic component in-house with those of outsourcing its production to its subcontractor. The management of General Blast Inc. would like to do that for various production quantities.

1. Build an influence diagram that illustrates how to calculate the savings/loss from outsourcing.

2. Using a spreadsheet model, what will be the resulting savings/loss if the company decides to make 20,000 units of the new product?

3. Construct a one-way data table with production volume as the column input and profit as the output. Breakeven occurs when profit is zero. Vary production volume from 0 to 100,000 in increments of 10,000. In which interval of production volume does breakeven occur? Answer to the nearest whole number.

4. Using the appropriate Excel tool, find the exact breakeven point. Answer to the nearest whole number.

Please show how to solve all problems using Excel.

Answer 1

sol:

given that

1.

2.

Demand/ production level	20,000
Unit variable cost	$2.20
Fixed cost	$42,000
Unit cost of supply	$3.73
Total production cost	$86,000
Total outsourcing cost	$74,600
Profit (Loss) by producing	$11,400

3.

The output of the data table

Demand	Profit (Loss) by producing
0	$42,000
10,000	$26,700
20,000	$11,400
30,000	($3,900)
40,000	($19,200)
50,000	($34,500)
60,000	($49,800)
70,000	($65,100)
80,000	($80,400)
90,000	($95,700)
100,000	($111,000)

Therefore, in the range [20000, 30000], the breakeven occurs.

4.

Use the goal-seek function as shown to find the exact breakeven point.

Output

Demand/ production level	27,451
Unit variable cost	$2.20
Fixed cost	$42,000
Unit cost of supply	$3.73
Total production cost	$102,392
Total outsourcing cost	$102,392
Profit (Loss) by producing	$0

Therefore, the exact breakeven is at Demand/ production level = 27,451