Question

Formulate the problem as a linear programming model use excel and show your excel work. Thank you.

To (cost, in 100's)

From	New york	Philadelphia	Chicago	Boston	Supply
Tampa	$9	$14	$12	$17	200
Miami	11	10	6	10	200
Fresno	12	8	15	7	200
Demand	130	170	100	50

Answer 1

linear programming is used for optimised maximum and minimum value of the data.

to solve linear programming in excel sheet we need 3 variable:

1. decision variable

2.contribution

3.constraints

now make a column for Decision variable as i showd in excel sheet which will be attach below

next make a contribution variable column

now make constraints cell

and make a total row as i did.

now fill the demand data into contribution cell according to respective city

now go to total cell in which total in row and contribution in column

and write formula in it and the formula will be [ contribution of newyork city * decision variable of newyork + contribution of philadelphia * decision of pheladelphia + contribution of chicago * decision of chicago + contribution of boston * decision of boston]

in my case formula is =B10*B9+C10*C9+D10*D9+E10*E9

now go to constraints cell below this cell create 3 cell more for tampa, miami and frenso now each city of total rows cell write formula in it and the formula will be

=B2*B9+C2*C9+D2*D9+E2*E9

as shown in image

similarly write for another two city.

now use supply as maximum capacity.

now all formula work is done now use solver to make max decision

select solver from the data section

in the solver window :

in set objective blank select the total cell of contribution

now select max

now in the changing variable select both cell

now click on add

then click likewise i show in image below

similarly do for next 2 cities.

after adding all three then just click on solve and then hit enter you will get your result.