University of Iowa Excel Statistics Worksheet

Units of product must be transported from warehouses W1 and W2 to customers C1 and C2.Because of contractual rights, the amount of product shipped from W2 to C2 must be at least half of the amount o
(For extra clarity on this last point, this means that if, for example, W1 were to ship 250 units to C2, then W2 would
The costs involved in shipping a unit from a particular warehouse to a particular customer are shown below, along
Transportation
Costs ($/unit)
W1
W2
Demand (units)
C1
15
7
175
Supply
(units)
10
350
13
300
330
C2
Answer the following 4 questions to formulate and solve the transportation LP to minimize transportation costs w
1) (1pt) Write down the list of decision variables. You’ll need to introduce the variables, and provide a brief exp
(For example, on one of the problems in the HW, you had to decide how many full-size ovens and how m
The variables would be F and C, where F represents the number of full-size ovens produced, etc. The lett
2) (1pt) Write down the objective function, as a mathematical expression in term of the variables that you wrote
Here you are not coding the Excel cells, but instead writing a mathematical expression, i.e. it’s what you’d write
3 (2 pts) Write down all applicable constraints as mathematical expressions in terms of the variables in part (a).
Here you can use = for “greater than or equal.” Your answer is not a coded
but is instead a list of mathematical expressions, i.e. it’s what you’d write on paper if you were writing the co
4) (2pt) What is the minimum transportation cost achievable while still satisftying all constraints?
(To receive full credit, you must properly code your workbook and solve this with Excel Solver.)
re shown below, along with the supply available at each warehouse and the demand required by each customer.
produced, etc. The letter/symbol you choose is arbitrary, but the explanations of the variables are not.)
by each customer.