Question

Pioneer Corporation sells car speakers. They have two factory locations in the Midwest where they manufacture car speakers. One is in Flint, MI and one is in Kalamazoo, MI. They also have two major stores in the Midwest where they sell their speakers, one in Chicago and one in Detroit.
Below is a table that gives the shipping costs for each set of car speakers from each factory to each store.
Shipping Costs   Stores
Factories   Detroit   Chicago
Kalamazoo   $7.43   $5.08
Flint   $3.29   $8.47

The factory in Kalamazoo has a supply of 700 sets of speakers, and the factory in Flint has a supply of 900 sets of speakers. So the factories cannot ship more than they currently have.
The store in Detroit has a demand for 500 sets of speakers, and the store in Chicago has a demand for 1000 sets of speakers. So the stores need to receive at least this many speakers (possibly more).
What should be the shipping schedule that fulfills the demand for each store and minimizes shipping costs?
Set up a spreadsheet to solve this problem. You may use the template provided below.
Speakers   Detroit   Chicago   Sent
Kalamazoo        
Flint        
Received        
         
Shipping Costs   Detroit   Chicago  
Kalamazoo        
Flint        
         Total Cost  

Use the Excel “solver” function to solve this linear programming problem with 4 decision variables. Highlight the optimal decision variables in green and the minimum shipping cost in blue. Do not hard code the target or changing cells (do not enter specific numbers into those cells).

Answer 1

The Shipping Costs Matrix and Volume Matrix can be set in excel as:

Solver Parameters:

Here, the factories cant send more than their capacity and a store should at least satisfy the demand

Optimal Solution:

Answer is:

Speakers	Detroit	Chicago	Sent
Kalamazoo	0	700	700
Flint	500	300	800

Shipping Costs	Detroit	Chicago
Kalamazoo	0	$3556
Flint	$1645	$2541

Shipping Costs Total = $7742

********

IF YOU HAVE ANY DOUBT, KINDLY COMMENT. I WOULD LOVE TO HELP YOU!!!

IF YOU LIKE THE ANSWER, PLEASE GIVE AN UP-VOTE OR THUMB UP. THIS WILL ENCOURAGE ME TO ANSWER MORE!!