STATS - Kamm Industries

Carefully read the case in the textbook and then review the model to familiarize yourself with the integration of the LP into Excel.
On the "Case Update" tab, there are five questions. After loading the ASPE solver, solve the model, obtain the sensitivity report, and use the sensitivity report to answer Questions 1 though 5. Each question is worth 16 points: 8 points for correctly identifying the cell value(s) you used from the sensitivity report to justify your response and 8 points for a clear, concise, grammatically response correctly answering the question. Your responses should be typed in unbolded black font right after the statement of each question. (HINT: If you have loaded the ASPE solver correctly, Cell N22 should reflect a total cost between $1.9M and $2.0M to fill this order)
Remember, cases are individual work. Good luck and as always...HAVE FUN!

Kamm Industries
Carpet
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Demand
Make
(Yds)
Dobbie Pantera
14,000
0
na
52,000
0
na
44,000
0
na
20,000
0
na
77,500
0
0
109,500
0
0
120,000
0
0
60,000
0
0
7,500
0
0
69,500
0
0
68,500
0
0
83,000
0
0
10,000
0
0
381,000
0
0
64,000
0
0

Buy
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

Total
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

Dobbie
Pantera
Yd/Hr
Hr/Yd
Yd/Hr
Hr/Yd
4.510 0.221729
na
na
4.796 0.208507
na
na
4.629 0.216029
na
na
4.256 0.234962
na
na
5.145 0.194363 5.428
0.18423
3.806 0.262743 3.935
0.25413
4.168 0.239923 4.316 0.231696
5.251 0.19044 5.356 0.186706
5.223 0.191461 5.277 0.189502
5.216 0.191718 5.419 0.184536
3.744 0.267094 3.835 0.260756
4.157 0.240558 4.291 0.233046
4.422 0.226142 4.558 0.219394
5.281 0.189358 5.353 0.186811
4.222 0.236855 4.288 0.233209
Used
0
Used
0
Available
32370 Available 172640

Make Cost
Dobbie
Pantera
$2.66
na
$2.55
na
$2.64
na
$2.56
na
$1.61
$1.60
$1.62
$1.61
$1.64
$1.61
$1.48
$1.47
$1.50
$1.50
$1.44
$1.42
$1.64
$1.64
$1.57
$1.56
$1.49
$1.48
$1.31
$1.30
$1.51
$1.50
$0
$0
Total Cost

Buy Cost
$2.77
$2.73
$2.85
$2.73
$1.76
$1.76
$1.76
$1.59
$1.71
$1.63
$1.80
$1.78
$1.63
$1.44
$1.69
$0
$0

1. (16 Points) Load the solver. To get you started, I have defined the objective cell and decision
variables in the solver. From there, the LP can be modeled with three constraints: total demand for
each of the 15 types of carpet, max hours on Dobbie machines, and max hours on Pantera
machines. What is the cost required to meet demand?
2. (16 Points) In the sensitivity report, refer to the Constraints section and to the information
provided for cell K21 which is associated to the numer of Pantera hours used. What would happen
to the total cost if one of the Pantera machines broke and could not be used during the 13-week
production period?
3. (16 Points) In the sensitivity report, refer to the Constraints section and to the information
provided for cell I21 which is associated to the numer of Dobbie hours used. What would happen
to the total cost if an additional Dobbie machine was added for the 13-week production period?
4. (16 Points) In the Shadow Price column of the Constraints section, format the cells to show
these values to two decmial places. Explain the shadow prices and the values in the Allowable
Increase column of the Sensitivity Report for the products that are being outsourced.
5. (16 Points) If the carpets in Orders 5 through 15 all sell for the same amount, which type of
carpet should Kamm encourage its sales force to sell more of? Why?