Question

Show your work with excel

The operations manager at XYZ manufacturing is analyzing options for a new production line. He can refurbish some old equipment for $70,000 or purchase new equipment for $130,000. The variable costs per unit will be quite different in each case. If he refurbishes, variable cost per unit will be $7.25; if he purchases new equipment, variable cost per unit will be $4.75.    The product will sell for $10 per unit.

a. Create a profit model for each option assuming that all units produced will be sold.

b. Using the Excel Goal Seek function, find the break-even volume for each option

c. Graph Total Profit vs Volume for both options on one graph (show volume from 0 to 50,000)   

d. According to the graph at what volumes (approx.) would the Refurbish option be preferred?

Answer 1

Let Q units be the Volume produced

The revenue from producing and selling Q is

Revenue=$10Q

The cost of producing Q, when using refurbished old equipment is

Cost = Fixed Cost + Variable Cost = $70,000 + $7.25 Q

The profit of producing Q, when using refurbished old equipment is

Profit = Revenue - Cost = $10Q - ($70,000 + $7.25 Q)

The cost of producing Q, when using new equipment is

Cost = Fixed Cost + Variable Cost = $130,000 + $4.75 Q

The profit of producing Q, when using new equipment is

Profit = Revenue - Cost = $10Q - ($130,000 + $4.75 Q)

a) Create the following in excel

get this sheet

b)break even volume is when the profit is zero.

Setup using data-->what if analysis-->goal seek

get this

The break-even volume when using old refurbished equipment is 25,455

Next we do this for new equipment

Setup using data-->what if analysis-->goal seek

get the following

The break-even volume when using new equipment is 24,762

c) We create rows with volume increasing from 0 to 50,000, in a step of 1,000

Copy the rows till you get 50,000 in the Volume (Row 53)

Get this sheet

Copy the Volume and Profit columns in one sheet as below (paste as values)

Select the data and use insert-->scatter-->lines

get this raw graph

format as needed

d) From the graph we can see that Refurbish option is preferred when the volume is below 24,000 units (approximate)