Consider Figure 10.10 for Simkin’s Hardware Store. Column J in the spreadsheet indicates a value of 1 if an order is placed and a value of 0 if an order is not placed. Modify the spreadsheet to make it more “manager-friendly” by having “Yes” indicate that an order is placed and “No” indicate that an order is not placed. Make all necessary adjustments to other formulas in the spreadsheet to accommodate this change.

Figure 10.10

Transcribed Image Text:

Columns A through P are the same as in Figure 10.9. Values in cells W5:AA203 are automatically filled in by Data Table. Formula in cell AA4: -P29 BICIOEF 1 Simkin's Hardware Store GH U VVX Data Table NO POR Z AA Demand Distribution RN intvl RN intvi lower upper imit 2 End Place Arrive Hold- Stock- Hold- Stock- out Order Total Begin Units Avail Dem- Demand End Stock inv. order Lead filed out Order Total cost cost on ing Proba- ing Run cost $1.46 $104 $140 $245.46 $2.46 $1.28 $128 $120 $249. 28 $1.90 $1.46 $120 $120 $241.46 $1.72 $1.58 3 Day inv rec inv and 0 7 inv out Order? time day cost cost cost cost Demand bilty imit cost $0.14 s0 $20 $20.04 s0 $16.00 $0.14 $0 $20 $20.0e $0.06 $0.02 $0 $24.00 so $20 $20.10 Reorder point,R $0.04 $0.00 Min leadtime s0 $24.00 Max leadtime $0.16 so $20 $20.10 Holding cost 4 7 0 $0.14 $0.04 S0.00 0.05 0.10 0.20 0.05 52 3 0.05 0.15 $16 $140 $158 48 7. 2 10 4 1 0.15 0.35 6 7 4 8. 2 2 4. 2. $16 $0.14 $0.06 $56 $140 $197. 90 10 10 3. 3 3 13 0.40 0.15 0.10 0.35 0.75 0.75 0.90 1.00 4 4. $88. $120 $209.72 596 $120 $217.58 598 $120 $217.88 3 3 3 $0.06 $0.02 0.90 10 3 3 2 11 $0 1 10 10 Order quantity, a 10 $0.00 $0.10 $24 10 $1.88 6. $1.58 $128 $120 $249.58 10 $2.18 11 $1.90 $1.76 $136 $120 $257.76 13 $1.70 $112 $120 $233.70 14 S1.90 15 $1.12 $168 $140 $308.12 16 $1.74 17 $1.88 18 $1.44 $152 $120 $273.44 19 $1.40 $104 $140 5245.40 20 s1.46 $168 $120 $289.46 21 $1.78 $112 $120 $233.78 22 $1.80 23 52.04 24 $1.94 25 $1.84 $168 $120 $289.84 26 $2.00 27 $1.86 199 51.56 $152 $100 $253.56 200 $1.84 13 $48 $140 S190.18 S88 $120 S209.90 10 3 2 12 $0.04 2 2 10 $0.00 $0.00 14 11 15 12 3. 10 $24 3 12 16 13 10 10 2 2 $0.16 17 14 2 17 $0. 10 S0.02 $24 $120 $145.90 18 15 10 $0.00 $0.00 Liper unit per day) $0.00 $0. 12 $0.02 $0 $16.00 $0.12 s0 $20 $20.02 (per unt) $0 $24.00 $20 $20.10 S0.02 Liper order) $0.16 $40 $140 S181.74 s80 $120 $201.88 19 16 2. 2 10 $16 20 17 21 18 10 10 4 6 so Stockout cost $8 1 20 22 19 1. 3 10 $0.00 $0.10 $0.02 1. $24 $20 23 20 24 21 25 22 26 23 6 10 10 5 1. 22 SO lOrder cost 1 11 S0. 16 $0.12 $96 $140 S237.80 S80 $120 S202.04 $S6 S120 $177 94 10 11 8 6 1 $0. 12 Based on 200 replications: $1.79 $98.64 $122.20 $222 63 2 27 24 28 25 Average holding cost- Average stockout cost= Total per month = $1.48 $104 $140 $24S.48 Average order cost = $0.06 $0.00 S0 $20 $20.06e $0.00 6 3 3 3 3 28 3 3. 10 $80 $120 $202.00 S84 $120 $185 88 29 30 Average total cost= Hidden rows 202 203 580 $140 $221.84 Results based on current replication Results based on 200 replications Select cells W4:AA203 before clicking Data| What-lf Analysis| Data Table.