T Standard number of units profit
N deluxe
539. 539.99 9984 84 252. 252.00 0011 11 10 9 7667.994
constraints Cutting & dyeing Sewing Finishing Inspection and packaging
0.7 0.5 1 0.1
1 630 0.8333 479.9917 0.66667 708 0.25 117.0001 LHS
630 600 708 135
<= <= <= <= SIGN
RHS
maximize Z= 10s+9d
.7s+d<=630 .5s+.833d<=600 s+.66667d<=708 .10s+.25d<=135
Part B Step 1: Use the formulated problem as in part A with the profit and constraint coefficients to set set up E Step 2
Setting up the worksheet Key in profit coefficient per product type in range B4:c4 ■ key in the LHS constraint coefficients in terms of Decision variables, for example variable "x1" or "b" h using the range B6:c9
Use sum product formula in range range d6: d9 similar to cell d4 to compute usage on the LHS Using RHS constraint values values for Part A formulation, Key in these RHS values in range F6: F9
Key in range for Decision Decision variables b3: c3( leave the cells empty or type in zeros) (Solver will will get th ■ In cell d4 use formula Excel to compute total return ( the sumproduct sumproduct is useful here)
Step 3: Instructing Solver( please ensure that you have solver added in or go to Tools, Add-ins ■ Click the Tools menu and click Solver. ■ The Solver Parameters window will appear with the cursor located in the window to the right of Set Target Cell.
■ Click on cell D4 to make this the target target cell, the target target total return. In In the next row Check that to the right of Equal of Equal To:, the button indicating MAX is selected. ■ Under "By Changing Cells" type B3:c3( this represents the decision variables for which values are needed )
■ Click the Add button to the right of Subject to the Constraints, which brings
up the Add Constraint window . With the cursor in the Cell Reference window To add a whole constraint range, click cells F8 to F14(holding down and dragging cursor from F8 to F14) to se
After adding the constraints click Ok
■ You return to the Solver Parameters window and observe that there are entries under Subject to the Constraints.
Click the Options button to the right of the window, and click Assume Linear Model and Assume Non-Negative, then OK.
■ Back in the Solver Parameters window click the Solve button on the top right of the window. The solution will appear in the worksheet. ■ The Solver Results window appears. Click OK to retain the solution after selecting the answer report and
up the Add Constraint window . With the cursor in the Cell Reference window To add a whole constraint range, click cells F8 to F14(holding down and dragging cursor from F8 to F14) to se
After adding the constraints click Ok
■ You return to the Solver Parameters window and observe that there are entries under Subject to the Constraints.
Click the Options button to the right of the window, and click Assume Linear Model and Assume Non-Negative, then OK.
■ Back in the Solver Parameters window click the Solve button on the top right of the window. The solution will appear in the worksheet. ■ The Solver Results window appears. Click OK to retain the solution after selecting the answer report and
cel sheet
as a coefficient of 2 is written 2x1 or 2b
e values for you)
o add solver)
Par, Inc., is a small manufacturer of golf equipme decided to move into the market for medium- an enthusiastic about the new product line and has over the next three months. After a thorough investigation of the steps involv determined that each golf bag produced will req 1. Cutting and dyeing the material, 2. Sewing,3. F Inspection and packaging The director of manufacturing analyzed each of t the company produces a medium-priced standar the cutting and dyeing department, 1/2 hour in t department, and 1/10 hour in the inspection and deluxe model will require 1 hour for c utting and for inspection and packaging. Par’s production is constrained by a limited num
After studying departmental workload projectio 630left, hours forselect cutting dyeing, lect the the whole LHS LHS range at once, then select <= <= in estimates the windowthat to the then theand range H8:H and 135 hours for inspection and packaging will bags during the next three months. The accounting department analyzed the produc costs, and arrived at prices for both bags that wil and $9 for every deluxe bag produced. a)Formulate a linear programming model for thi the related template. 15 marks
sensitivity report
nt and supplies whose management has d high-priced golf bags. Par’s distributor is agreed to buy all the golf bags Par produces ed in manufacturing a golf bag, management uire the following operations: inishing (inserting umbrella holder, club separators, etc.) 4. he operations and concluded that if d model, each bag will require 7/10 hour in he sewing department, 1 hour in the finishing packaging department. The more expensive dyeing, 5/6 hour for sewing, 2/3 hour for finishing, and 1⁄4 hour ber of hours available in each department.
s, the director of manufacturing 600 hours for sewing, 708holding hours for 14 by clicking on H8 and andfinishing, dragging the cursor to H14 then releasing e available for the production of golf tion data, assigned all relevant variable l result in a profit contribution1 of $10 for every standard bag
problem. Solve the model by using the computer. Please see
Microsoft Excel 12.0 Answer Report Worksheet: [Assignment 1 S1 2012-13 solutions.xls]Q 2 LP sensitivity (2) Report Created: 9/16/2012 1:05:22 PM Target Cell (Max) Cell
Name
Original Value
$E$4 profit
Adjustable Cells Cell
0
Name
Original Value
$B$3 number of units Economy $C$3 number of units standard $D$3 number of units deluxe
Constraints Cell
Name
$E$6 Fan motors $E$7 cooling coils $E$8 Manuf time
0 0 0
Cell Value
Final Value
16440
Final Value
80 120 0
Formula
Status
200 $E$6<=$G$6 Binding 320 $E$7<=$G$7 Binding 2080 $E$8<=$G$8 Not Binding
Slack
0 0 320
Microsoft Excel 12.0 Sensitivity Report Worksheet: [Assignment 1 S1 2012-13 solutions.xls]Q 2 LP sensitivity (2) Report Created: 9/16/2012 1:05:22 PM Adjustable Cells Cell
Name
$B$3 number of units Economy $C$3 number of units standard $D$3 number of units deluxe
Final Reduced Value Cost
80 120 0
0 0 -24
Objective Coefficient
63 95 135
Allowable Increase
Allowable Decrease
12 31 24
15.5 8 1E+30
Allowable Increase
Allowable Decrease
80 80 1E+30
40 120 320
Constraints Cell
Name
$E$6 Fan motors $E$7 cooling coils $E$8 Manuf time
Final Value
200 320 2080
Shadow Price
31 32 0
Constraint R.H. Side
200 320 2400
T Economy
standard
80
number of units
63
profit
N deluxe
120 95
0 135
16440
constraints
1 1 8
Fan motors cooling coils Manuf time
1 2 12
1 4 14
200 <= 320 <= 2080 <= LHS
SIGN
200 320 2400 RHS
a) The optimal solution therefore is the best decision that yield the optimal value (profit). F standard.(2 marks)
b)) Fan motors and cooling fan are binding, manuf time show extra capacity of 320 hours avail
c) the question is asking about the constraints since labour time is constraint. Given that man profitable to use more manufacturing time Note An increase/decrease in the ranges for the objective (which means that one is wit hin th change, but the optimal values of the basic variables may/may not change. (3 marks)
d)This question is obviously asking you about information in the Adjustable Cells part of the Se allowable increase is $24 and the range is from negative infinity to 159, and therefore 150 is Note An increase/decrease in the ranges for the objective (which means that one is within th change, but the optimal values of the basic variables may/may not change. (3 marks) e)
f) The question is obviously asking about constraints. This is so because the number of fan mo of the Sensitivity Report, the allowable increase is 80 and the allowable decrease is 40. This m will need to be resolved . (3 marks)
g) The question is obviously asking about constraints because cooling coil is a constraint. The con the allowable increase is 80 and the allowable decrease is 120 . i) The valid range s is (320+8
h)A problem is degenerate when the number of constraints is not equal to the number of POSI then the number of positive variables. Finding the number of constraints is easy. You simply lo positive variables(3) is found from the positive values of the adjustable cells and the positive v variables(3) and hence the problem is not degenerate.(2 marks) i)Alternative Optima is usually found in the adjustable cell part of the printout. One can identif for this problem there is no alternative optima. Answer: There are no alternative optima. A variable in the adjustable cell will have both a Fi J)The value in the slack column is 320( 2400-2080)
a) What is the optimal solution and what is the value of the objective b) Which constraints are binding and which constraint show extra ca 100 hours of manufacturing time became available .Evaluate th marks) d) The profit per unit for deluxe model was increased to 150 per unit. marks) e) . Identify the range of optimality for each objective function coeffi Suppose the profit for the economy model is increased by $6 per u standard model is decreased by $2 per unit, and the profit for the d increased by $4 per unit. ( use the 100% rule)What will be the ne 3 marks c)
f)
If the number of fan motors available for production is increased effect. 2 marks
g) What are the allowable values within the number of cooling coils affecting the shadow price?- (2 marks) h) Is the problem degenerate? Explain! ( 2 marks) i) Are there alternative optima in this problem? Explain ( 2 marks) j) What value should be in the slack column that is given by ???? ( 1
r this problem the Optimal Solution is to make 80 units economy, 120 units
lable.
Quality Air Conditi an economy model profits per unit are The production req
ufacturing time is not binding(shadow price is zero), then it is not
Answer the followi brief. If there are t necessary a range
e range allowed) means that the BASIS( optimal product mix) will NOT
sitivity Report. For deluxe model , the current profit is 135. The ithin the range which means the optimal solution will not change. e range allowed) means that the BASIS( optimal product mix) will NOT
tors is a limitation . The constraint is binding. Based on the constraint part ans that an increase of 100 is outside the range of validity. The problem
straint is binding . Based on the constraint part of the Sensitivity Report, +320-120)
ITIVE VARIABLES. You do this by counting the number of constraints and k at the constraint part of the printout and count them. The number of alues of the slack . Hence the number of constraints(3)= number of positive
Alternative Optima when both the Final Value and the Reduced Cost are 0. al Value and a Reduced cost of O.( 1 mark)
function (2 marks) acity? 2 marks effect? Explain-( 2 Evaluate the effect (2 cient (unit profit). it, the profit for the eluxe model is optimal solution be?
y 100, evaluate the
can vary without
mark)
ning manufactures three home air conditioners: l, a standard model, and a deluxe model. The $63, $95, and $135, respectively. uirements per unit are as follows: ng questions (a-i) using the output below please be o possible answers one will suffice. Where nalysis must be shown.
model
E S D
profit change 63 Increase 6$ 95 decrease $2 135 Increase $4
because changes are 92 % of allowable ( S=120, D=0 will not change. The change in total profit will be E 80 units @ +6=480 S 120units @ -2=-240 240 profit=16440+240=16680
allowable 75-63=12 95-87=8 159-135=24
% 6/12(100)=50 2/8(100)=25 4/24(100)=17 92 <100) changes , the optimal solution of E = 80,
T Economy
N deluxe
standard
number of units profit
63
95
135
0
constraints Fan motors cooling coils Manuf time
1 1 8
1 2 1
1 4 14
0 <= 0 <= 0 <= LHS
SIGN
200 320 2400 RHS
Quality Air Conditioning manufactures three home air conditioners: an economy model, a standard mo deluxe model. The profits per unit are $63, $95, and $135, respectively. The production requirements per unit are as follows: Answer the following questions (a-i) using the output below please be brief. If there are two possible an one will suffice. Where necessary a range analysis must be shown.
el, and a
swers
Probability
Daily Demand
cum prob
0 0.04 0.12 0.4 0.8 0.96 0.98
0.04 0.08 0.28 0.4 0.16 0.02 0.02
RN mapping
0 1 2 3 4 5 6
00-.03 .04-.11 .12-.39 .40-.79 80-95 96-97 98-99
month
OI
UR
AI
RN
D
DF
EI
month
OI
U rcd
A In
RN
D
DF
EI
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
5 5 0 3 1 0 3 0 0 3 0 3 1 4 1 4 1 3 0 3
0 0 5 0 0 5 0 0 5 0 5 0 5 0 5 0 5 0 5 0
5 5 5 3 1 5 3 0 5 3 5 3 6 4 6 4 6 3 5 3
0.03 0.97 0.16 0.12 0.55 0.16 0.84 0.63 0.33 0.57 0.18 0.26 0.23 0.52 0.37 0.7 0.56 0.99 0.16 0.31
0 5 2 2 3 2 4 3 2 3 2 2 2 3 2 3 3 6 2 2
0 5 2 2 1 2 3 0 2 3 2 2 2 3 2 3 3 3 2 2
5 0 3 1 0 3 0 0 3 0 3 1 4 1 4 1 3 0 3 1
Demand Probability per Month
Receive an Probabili Order (mo.) ty
0
0.04
1
0.6
1 2 3 4 5 6
0.08 0.28 0.4 0.16 0.02 0.02 1
2 3
0.3 0.1 1
1 0.57
Demand Probability per Month 0 0.04 1 0.08 2 0.28 3 0.4 4 0.16 5 0.02
Day Demand
dema lead ti
2 0.59
3 47
Random Numbers To Be Us 3 4 0.84 0.19
6
0.02 1
see furniture depot example
Probability cum
Lead time da RN mapping
0 0.6 0.9 1
0.6 0.3 0.1
SO
order
1 2 3
00-.59 .60-89 .90-.99
leadtime IC
RN
SOC OC
TC
SO
0 0 0 0 2 0 1 3 0 0 0 0 0 0 0 0 0 3 0 0
yes
47
1
yes
74
2
yes
76
2
yes
56
1
yes
22
1
yes
42
1
yes
1
1
yes
21
1
yes
60
2
average cost
200 0 0 0 120 0 40 0 0 800 120 0 0 400 0 1200 120 0 0 0 120 0 40 0 160 0 40 0 160 0 40 0 120 0 0 1200 120 0 40 0
100 0 100 0 0 100 0 0 100 0 100 0 100 0 100 0 100 0 100 0
300 0 220 40 800 220 400 1200 220 0 220 40 260 40 260 40 220 1200 220 40 5,940.00 297.00
Random Numbers To Be Used in the Simulation 1 2 3 4 5 6 0.57 0.59 0.84 0.19 0.82 0.9
97 74
16 76
12 56
55 22
16 42
84 1
7 0.02
8 0.11
7 0.02
63 21
33 60
d in the Simulation
5 0.82
6 0.9
9 0.81
8 9 10 0.1 0.8 0.8
10 0.84
57
18
26
Sound Warehouse in Georgetown sells CD players (with speakers), which it orders from Fuji Electronics in Japan. Because of shipping and handling costs, each order must be for five CD players. Because of the time it takes to receive an order, the warehouse outlet places an order every time the present stock drops to five CD players. It costs $100 to place an order. It costs the warehouse $400 in lost sales when a customer asks for a CD player and the warehouse is out of stock. It costs $40 to keep each CD player stored in the warehouse. If a customer cannot purchase a CD player when it is requested, the customer will not wait until one comes in but will go to a competitor. The following probability distribution for demand for CD players has been determined: The warehouse has five CD players in stock. Orders are always received at the beginning of the week. Simulate Sound Warehouse's ordering and sales policy for 20 months, using the first column of random numbers . Compute the average monthly cost.
23
52
37
70
56
99
16
31
68
74
27
0
The coordinator of the summer program in the Faculty of social at the University of the west indies is of the opinion that the staff is being overworked during the months leading up to thecommencemen tof the summer program. The Faculty empoyed the services of CWD consultants . The consultants provided data on services times and customer arrivals, with corresponding service times and probabilioties in the table below.
service and customer time arrivals t me service between time prob arivals prob Random numbers Random numbers 0.00 0.00 0.00 0.10 0.50 0.52
1 2 3 4
time between arivals 0.00
0.25 0.2 0.4 0.15
0.28 0.68 0.36 0.90 0.62 0.27 0.50 0.18 0.36 0.61 0.21 0.46 0.01 0.14
cum distribution
prob 0.10
1 2 3 4 5
RN mapping
0.10 .00-.09
0.35 0.25 0.15 0.1 0.05
service time 0.00
0.37 0.82 0.69 0.98 0.96 0.33 0.50 0.88 0.90 0.50 0.27 0.45 0.81 0.66
cum distrib
prob 0.00
0.00
1 2 3 4 5
0.35 0.25 0.15 0.1 0.05
Time between arrival
customer RN
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
0.45 0.70 0.85 0.95 1.00
0.52 0.37 0.82 0.69 0.98 0.96 0.33 0.50 0.88 0.90 0.50 0.27 0.45 0.81 0.66
totals average percent server busy
2 1 3 2 5 5 1 2 4 4 2 1 2 3 2
.10-.44 .45-.69 .70-.84 .85-.94 .95-.99
1 2 3 4
Arrival time
Begin service
2 3 6 8 13 18 19 21 25 29 31 32 34 37 39
wait for service
2 5 7 10 13 18 21 23 26 29 31 34 35 38 39
0 2 1 2 0 0 2 2 1 0 0 2 1 1 0 14
0.25 0.2 0.4 0.15
RN
0.25 0.45 0.85 1.00
service
0.50 0.28 0.68 0.36 0.90 0.62 0.27 0.50 0.18 0.36 0.61 0.21 0.46 0.01 0.14
3 2 3 2 4 3 2 3 1 2 3 1 3 1 1 34
0.933333
0.85
RN mapping
.00-.00