Question

Midwestern Hardware must decide how many snow shovels to order for the coming snow season. Each shovel costs $15.00 and is sold for $29.95. No inventory is carried from one snow season to the next. Shovels unsold after February are sold at a discount price of $10.00. Past data indicate that sales are highly dependent on the severity of the winter season. Past seasons have been classified as mild or harsh, and the following distribution of regular price demand has been tabulated: Mild Winter Harsh Winter No. of Shovels Probability No. of Shovels Probability 250 1,500 0.5 0.2 300 0.4 2,500 0.3 350 0.1 3,000 0.5 Shovels must be ordered from the manufacturer in lots of 200; thus, possible order sizes are 200, 400, 1,400, 1,600, 2,400, 2,600, and 3,000 units. Construct a payoff table to illustrate the components of the decision model, and find the optimal quantity for Midwestern to order if the forecast calls for a 40% chance of a harsh winter.Show your work on worksheet Hardware. 1. To construct the payoff table, calculate the probability of each demand. See the hint on the worksheet. 2. The optimal ordering quantity with the highest expected pay off is how much? Probability of demand being 250 [Choose ] Probability of demand being 300 [Choose 1 ro Mountain Ski Snorte Hardware

Answer 1

Probability of each demand = Probability of Winter weather * Probability of demand in that weather

Probability of demand being 250 = 0.5*0.6 = 0.30

Probability of demand being 300 = 0.4*0.6 = 0.24

Probability of demand being 350 = 0.1*0.6 = 0.06

Probability of demand being 1500 = 0.2*0.4 = 0.08

Probability of demand being 2500 = 0.3*0.4 = 0.12

Probability of demand being 3000 = 0.5*0.4 = 0.20

Payoff matrix is following:

A B с D E F 1 2 3 4 5 Mild Winter Harsh Winter 6 No. of shovels Probability No. of shovels Probability 7 250 0.5 1500 0.2 8 300 0.4 2500 0.3 9 350 0.1 3000 0.5 10 11 Cost 15.00 12 Price 29.95 13 Discount price 10.00 14 15 16 Probability 0.30 0.24 0.06 17 Demand 18 Order size 250 300 350 19 200 2990 2990 2990 20 400 2987.5 3985 4982.5 21 1400 -2012.5 -1015 -17.5 22 1600 -3012.5 -2015 -1017.5 23 2400 -7012.5 -6015 -5017.5 24 2600 -8012.5 -7015 -6017.5 25 3000 -10012.5 -9015 -8017.5 26 0.08 0.12 0.20 1500 3000 2500 2990 5980 2990 5980 2990 5980 20930 21925 17925 16925 14925 20930 23920 35880 36875 34875 20930 23920 35880 38870 44850

EXCEL FORMULA:

In cell B19, write formula  =MIN($A19,B$18)*$B$12-$A19*$B$11+MAX(0,$A19-B$18)*$B$13  

Then drag and copy this formula up to column G and row 25 of Excel .

A B с D 1 2 3 4 5 Mild Winter Harsh Winter 6 No. of shovels Probability No. of shovels Probability 7 250 0.5 1500 0.2 8 300 0.4 2500 0.3 9 350 0.1 3000 0.5 10 11 Cost 15 12 Price 29.95 13 Discount price 10 14 15 16 Probability 0.3 0.24 0.06 17 Dema 18 Order size 250 300 350 19 200 =MIN($A19,B$18)*$B$12-$A19*$B$11+MAX(0,$A19-B$18) *$B$13 =MIN($A19,C$18)*$B$12-$A19*$B$11+MAX(0,$A19-C$18)*$B$13 =MIN($A19,D$18) *$B$12-$A19*$B$11+MAX(0,$A19-D$18)*$B$13 20 400 =MIN($A20,B$18) *$B$12-$A20*$B$11+MAX(0,$A20-B$18)*$B$13 =MIN(SA20,C$18)*$B$12-$A20*$B$11+MAX(0,$A20-C$18)*$B$13 =MIN(SA20,D$18)*$B$12-$A20*$B$11+MAX(0,$A20-D$18)*$B$13 21 1400 =MIN($A21,B$18)*$B$12-$A21*$B$11+MAX(0,$A21-B$18) *$B$13 I=MIN($A21,C$18)*$B$12-$A21*$B$11+MAX(0,$A21-C$18) *$B$13 =MIN(SA21,D$18)*$B$12-$A21*$B$11+MAX(0,$A21-D$18)*$B$13 22 1600 =MIN(SA22,B$18)*$B$12-$A22*$B$11+MAX(0,$A22-B$18)*$B$13 =MIN(SA22,C$18)*$B$12-$A22*$B$11+MAX(0,$A22-C$18)*$B$13 =MIN(SA22,0$18)*$B$12-$A22*$B$11+MAX(0,$A22-D$18)*$B$13 23 2400 =MIN($A23,6$18)*$B$12-$A23*$B$11+MAX(0,$A23-B$18)*$B$13=MIN($A23,C$18)*$B$12-$A23*$B$11+MAX(0,$A23-C$18)*$B$13 =MIN(SA23,D$18)*$B$12-$A23*$B$11+MAX(0,$A23-D$18)*$B$13 24 2600 =MIN($A24,8$18)*$B$12-$A24*$B$11+MAX(0,$A24-B$18)*$B$13=MIN($A24.C$18)*$B$12-$A24*$B$11+MAX(0,$A24-C$18)*$B$13 =MIN($A24.C$18)*$B$12-$A24*$B$11+MAX(0,$A24-C$18)*$B$13 =MIN($A24,D$18)*$B$12-$A24*$B$11+MAX(0,$A24-D$18)*$B$13 25 3000 =MIN($A25,B$18) *$B$12-$A25*$B$11+MAX(0,$A25-B$18)*$B$13 =MIN($A25,C$18) *$B$12-$A25*$B$11+MAX(0,$A25-C$18)*$B$13 =MIN($A25,D$18)*$B$12-$A25*$B$11+MAX(0,$A25-D$18) *$B$13 26 27 28 29

--

Compute Expected Payoff for each order size

А B с D E F G T 1 2 2 3 5 250 4 Mild Winter Harsh Winter 6 No. of shovels Probability No. of shovels Probability 7 0.5 1500 0.2 8 300 0.4 2500 0.3 9 350 0.1 3000 0.5 10 11 Cost 15.00 12 Price 29.95 13 Discount price 10.00 14 15 16 Probability 0.30 0.24 0.06 17 Demand 18 Order size 250 300 350 19 200 2990 2990 2990 20 400 2987.5 3985 4982.5 21 1400 -2012.5 -1015 -17.5 22 1600 -3012.5 -2015 -1017.5 23 2400 -7012.5 -6015 -5017.5 24 2600 -8012.5 -7015 -6017.5 25 3000 -10012.5 -9015 -8017.5 26 27 0.08 0.12 0.20 2500 1500 2990 5980 20930 21925 17925 16925 14925 2990 5980 20930 23920 35880 36875 34875 3000 2990 5980 20930 23920 35880 38870 Expected payoff 2990 4543.6 7523.6 7960 9067.2 9104.6 8700.6 44850

.

FORMULA for expected payoff is: =SUMPRODUCT(B19:G19,$B$16:$G$16)

write this formula in cell H19 and drag to copy down to row 25

Maximum expected payoff is $ 9,104.6

associated with order size of 2600

Therefore,

Optimal order quantity = 2600