Question

Dana is in charge of planning for her sorority's social events. She is currently determining the best way to supply the posters needed for upcoming social events. In the upcoming winter semester, she expects to need 400 posters. Following that, in the spring, she will need 600 posters. Finally, the summer semester will require 250 posters. If she makes a poster ahead of time, she can store it in a locker for $1.50 per poster per semester. She has also decided that the event must have the planned number of posters; Back-orders are not allowed. To meet these demands, utilize the Transportation Model and the following information: make the posters 'in-house' by using members of the social committee during their regular meeting hours. Based on time and effort, each poster would cost about $12.50, and her team could make 150 posters per semester ask the social committee to stay late to work on posters. This would cost about $17 per poster, based on the materials, time and a 'bribe' of pizza and drinks. This could generate up to 250 posters per semester. ask another club to help out. She would have to pay this club $20 per poster, and they could create up to 500 per semester What are the lowest possible costs for Dana? How many posters are made by: the social committee during regular meetings? the social committee when staying late? the other clubs? Before she finishes creating her plan, Dana finds out that she has 50 posters already completed and ready to use. What is the impact on her total costs?

Answer 1

In order to formulate the problem as a transportation model, first create the parameter table.

Rows of the parameter table show supply period and columns indicate demand period

Regular cost of production in a supply period to fulfil the demand of same period is as given in the problem. For example regular production cost in period 1 to fulfil the demand of period 1 is $ 12.5 and overtime cost in the same supply and demand period is $ 17. Subcontract cost is $ 20

Regular time Cost of fulfilling demand of n period later than the current period increases by n times the carrying cost. For example, cost of fulfilling demand of period 3 (2 period later) from overtime time supply of period 1 = 17+2*1.5 = 20 Total supply exceeds the Net demand. Therefore, there is a dummy demand node to make it a balanced transportation problem

Resulting parameter table is as follows

Parameter Table
Supply	winter	spring	summer	dummy	Supply
Initial Inventory	0	1.5	3	0	0
winter Regular	12.5	14	15.5	0	150
winter Overtime	17	18.5	20	0	250
winter Other club	20	21.5	23	0	500
spring Regular		12.5	14	0	150
spring Overtime		17	18.5	0	250
spring Other club		20	21.5	0	500
summer Regular			12.5	0	150
summer Overtime			17	0	250
summer Other club			20	0	500
Net Demand	400	600	250	1,450

Create spreadsheet model as follows:

I J K L M M24 - fx A B C D 1 Demand & Supply winter spring summer 2 Demand 400 600 250 3 Capacity 4. Regular 1 150 150 150 5 Overtime 250 250 250 6 Other club 500 500 500 7 Initial inventory 8 Ending Inventory 0 =SUMPRODUCT(B12:E21,112:L21) E F G H Cost data Regular time cost Overtime cost Other club cost Carrying Cost $12.50 $17.00 $ 20.00 $1.50 0 9 150 o 10 Parameter Table 11 Supply winter spring summer dummy Supply 12 Initial Inventory 0 1. 5 3 0 0 13 winter Regular 12.5 14 15. 5 0 14 winter Overtime 17 18.5 20 250 15 winter Other club 20 21.5 230 500 16 spring Regular 12.5 14 150 17 spring Overtime 17 18.5 0 250 18 spring Other club 20 21.5 0 500 19 summer Regular 12. 5 0 150 20 summer Overtime 17 0 250 21 summer Other club 200 500 22 Net Demand 400 600 250 1,450 Optimal production plan Supply winter spring summer dummy Row Total Initial Inventory winter Regular winter Overtime winter Other club spring Regular spring Overtime spring Other club summer Regular summer Overtime summer Other club Column Total 0 0 0 23 24 Total Cost = 0 .

EXCEL FORMULAS:

Cell	Formula	Copy to
M12	=SUM(I12:L12)	M12:M21
I22	=SUM(I12:I21)	I22:L22
M24	=SUMPRODUCT(B12:E21,I12:L21)

Enter following Solver Parameters :

S Solver Parameters Set Objective: SM$24 To: O Max Min O Value Of: O By Changing Variable Cells: $1$12:$L$15, $J$16:$L$18,SK$19:5L521 Subject to the Constraints: $1$22:$L$22 = $B$22:$E$22 SM$12:$M$21 = $F$12:$F$21 Add Change Delete Reset All Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems. Solve Close

Click Solve to generate the solution:

I J K L M N O P M24 @ fx =SUMPRODUCT(B12:E21,112:L21) A B C D E F G H 1 Demand & Supply winter spring summer Cost data 2 Demand | 400 600 250 Regular time cost 3 Capacity Overtime cost 4. Regular 1150 150 150 Other club cost 5 Overtime 250 250 250 Carrying Cost 6 Other club 500 500 500 7 Initial inventory 8 Ending Inventory 0 $12.50 $17.00 $ 20.00 $1.50 S Solver Results 0 9 Solver found a solution. All constraints and optimality conditions are Reports Answer Keep Solver Solution Sensitivity Limits O Restore Original Values 150 Return to Solver Parameters Dialog o O 0 500 OK Cancel Optimal production plan Supply winter spring summer dummy Row Total Initial Inventory 0 0 0 0 winter Regular 150 0 0 0 150 winter Overtime 250 0 0 0 250 winter Other club 0 500 spring Regular 150 150 spring Overtime 0 250 spring Other club 2000 300 summer Regular 150 150 summer Overtime 100 150 250 summer Other club 0 500 500 Column Total 400 600 250 1,450 0 10 Parameter Table 11 Supply winter spring summer dummy Supply 12 Initial Inventory 0 1. 5 3 0 0 13 winter Regular 12.5 14 15. 5 0 14 winter Overtime 17 18.5 20 250 15 winter Other club 20 21.5 230 500 16 spring Regular 12.5 14 150 17 spring Overtime 17 18.5 0 250 18 spring Other club 20 21.5 0 500 19 summer Regular 12. 5 0 150 20 summer Overtime 17 0 250 21 summer Other club 200 500 22 Net Demand 400 600 250 1,450 23 0 250 Solver found a solution. All constraints and optimality conditions When Simplex LP is used, this means Solver has found a global optim 500 O 24 Total Cost = 19825

The optimal production plan is:

Production	winter	spring	summer
Regular	150	150	150
Overtime	250	250	100
Other club		200

Lowest possible costs = $ 19,825

Number of posters made by

the social committee during regular meetings = 150+150+150 = 450

the social committee when staying late = 250+250+100 = 600

the other clubs = 200

-----------------------------------------------------------------------------------------

Enter the initial inventory in cell F12 and again Run the Solver

@ fx B C D winter spring summer 400 600 250 I J K L M S Solver Results M24 A 1 Demand & Supply 2 Demand 3 Capacity 4 Regular 5 Overtime 6 Other club 7 Initial inventory 8 Ending Inventory =SUMPRODUCT(B12:E21,112:L21) E F G H Cost data Regular time cost Overtime cost Other club cost Carrying Cost $12.50 $17.00 $ 20.00 $1.50 150 250 500 50 0 150 250 500 150 250 500 Solver found a solution. All constraints and optimality conditions are satisf Reports Answer Keep Solver Solution Sensitivity Limits O Restore Original Values Return to Solver Parameters Dialog OK | Cancel 50 0 50 Solver found a solution. All constraints and optimality conditions are s When Simplex LP is used, this means Solver has found a global optimal sol 20 0 150 10 Parameter Table 11 Supply winter spring summer dummy Supply 12 Initial Inventory 0 1 . 5 3 13 winter Regular 12.5 14 15.5 150 14 winter Overtime 17 18.5 20 250 15 winter Other club 21.5 500 16 spring Regular 12.5 14 0 17 spring Overtime 17 18.5 250 18 spring Other club 20 21.5 0 500 19 summer Regular 12.5 150 20 summer Overtime 17 250 21 summer Other club 20 500 22 Net Demand 400 600 250 1,500 23 24 Optimal production plan Supply winter spring summer dummy Row Total Initial Inventory0 50 0 winter Regular 150 winter Overtime 250 0 250 winter Other club 500 500 spring Regular 150 spring Overtime 250 spring Other club 150 350 500 summer Regular 150 150 summer Overtime 100 250 summer Other club 500 500 Column Total 400 600 250 1,500 150 Total Cost = 18900

The new production plan is following:

Production	winter	spring	summer
Regular	150	150	150
Overtime	250	250	100
Other club		150

Minimum total cost = $ 18,900

We see that after accounting for the initial inventory, total costs decreased by = 19825 - 18900 = $ 925