Question

1. A manufacturing firm is planning to open a new factory. There are four countries under consideration: USA, Canada, Mexico, and Panama. The table below lists the fixed costs and variable costs for each site. The product is mainly sold in the U.S. for $950 per unit.

Location Fixed Cost Variable cost

USA $500,000 $210

Mexico $150,000 $250

Canada $300,000 $230

Panama $ 50,000 $300

a- Using cross-over analysis, find the range of production that makes each country optimal with lowest total cost.

b- Using Excel, construct total production cost linear graph for all 4 locations and verify cross-over points obtained in part (a). In your graph, use quantity values from 0 to 11,000 at increment of 200.

c- If the company forecasts that market demand will be around 8000 per year, which country is the best choice and what is the yearly profit?

d- Construct Total cost, Total revenue, and Total profit graphs for the optimal location.

Answer 1

a) Cross-over point between USA and Mexico = (Fixed cost of USA - Fixed cost of Mexico) / (Variable cost of Mexico - Variable cost of USA)

= (500000 - 150000) / (250 - 210)

= 8750

Cross-over point between USA and Canada = (Fixed cost of USA - Fixed cost of Canada ) / (Variable cost of Canada - Variable cost of USA)

= (500000 - 300000) / (230 - 210)

= 10000

Cross-over point between USA and Panama = (Fixed cost of USA - Fixed cost of Panama) / (Variable cost of Panama - Variable cost of USA)

= (500000 - 50000) / (300 - 210)

= 5000

Cross-over point between Mexico and Canada = (Fixed cost of Canada - Fixed cost of Mexico) / (Variable cost of Mexico - Variable cost of Canada)

= (300000 - 150000) / (250 - 230)

= 7500

Cross-over point between Mexico and Panama = (Fixed cost of Mexico - Fixed cost of Panama) / (Variable cost of Panama - Variable cost of Mexico )

= (150000 - 50000) / (300 - 250)

= 2000

Cross-over point between Canada and Panama = (Fixed cost of Canada - Fixed cost of Panama) / (Variable cost of Panama - Variable cost of Canada)

= (300000 - 50000) / (300 - 230)

= 3572

The minimum cross over point is 2000 between Mexico and Panama. The fixed cost of Panama is minimum, therefore, Panama is the optimal country for volume of production upto 2000. Thereafter, Mexico has the lowest total cost. The next cross-over point of Mexico is 7500 units. So Mexico is the lowest cost option for volume of production range from 2000 to 7500 units. This crossover of 7500 units is between Mexico and Canada. So Canada is the lowest cost option for volume greater than 7500 units.

Next cross-point of Canada is 10000 with USA. So the range of production for Canada is 7500 upto 10000 units. Thereafter, USA is the lowest cost country.

So the range of production for each country is as follows:

Panama: 0 to 2000

Mexico: 2000 to 7500

Canada: 7500 to 10000

USA: >= 10000

b) Total production curve is shown below:

Total cost = Fixed cost + Variable cost * Volume of production

Cross over points are verified.

c) 8000 lies between 7500 and 10000, which pertains to Canada. Therefore, best choice is Canada for this volume.

Yearly Profit = Volume * ( Selling price - Variable cost) - Fixed cost

= 8000*(950 - 230) - 300000

= $ 5,460,000

d)

Formula:

B5 =B$1+$A5*B$2 copy to B5:B60

F5 =A5*950   copy to F5:F60

G5 =MIN(B5:E5)   copy to G5:G60

H5 =F5-G5 copy to H5:H60