Johnson Electric produces small electric motors for four appliance manufacturers (customers 1, 2, 3, 4) at each of its three plants (A, B, C). The unit cost of production varies by plant location due to differences in production equipment and labor productivity. The customer orders that must be produced next month are shown in the following table.

Client	Demand
1	300
2	500
3	400
4	600

The unit production costs and the monthly capacities (supplies) of the plants are shown in the following table.

Planta	Production Unit Cost ($)	monthly production capacity
A	17	800
B	20	600
C	24	700

The cost of supplying these customers varies from plant to plant. Unit transportation costs (in $) are given in the following table.

Of the plant	To the client
	1	2	3	4
A	3	2	5	7
B	6	4	8	3
C	9	1	5	4

Johnson must decide how many units to produce at each plant and how much of each customer's demand to supply from each plant. He wants to minimize the total costs of production and transportation. Formulate Johnson's problem as a transportation model and find the optimal solution.

a) Prepare a design to solve the problem in an Excel spreadsheet. The layout should have the proper formatting, tags, highlighting, and appropriate identifiers. Wherever relevant, cells should have an Excel formula.

b) Set up the linear programming model using Excel Solver and solve the problem to minimize the total costs of production and transportation.

Based on the results of the solver, answer the following questions in one sentence each.

c) How many small electric motors are to be manufactured at each of the three plant locations A, B, and C? 

d) Which Customer location(s) would receive how many motors from which plant(s)? 

e) What will be the total cost of production for the next month? 

f) What will be the total transportation cost for the next month? 

g) What will be the total production plus transportation cost for the next month?