Question

Question 3B

CWD Electronics sells Televisions (TV), which it orders from the USA. Because of shipping and handling costs, each order must be for 10 TVs. Because of the time it takes to receive an order, the company places an order every time the present stock drops to 5 TVs. It costs $50 to place an order. It costs the company $200 in lost sales when a customer asks for a TV and the warehouse is out of stock. It costs $50 to keep each TV stored in the warehouse. If a customer cannot purchase a TV 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 TV has been and the time required to receive an order once it is placed (lead time) has the following probability distribution:

Lead time (weeks)	Probability	Demand/week	Probability
1	0.45	1	0.15
2	0.30	2	0.25
3	0.25	3	0.40
4		4	0.20

The company has 10 TVs in stock. Orders are always received at the beginning of the week.

Note that a lead time of 2 weeks imply that an order placed in week one will arrive in week 4.

Hint. No order is placed until the current order has arrived. Usage of all random numbers for lead time depend on stock arrivals in the model hence all numbers may not be used.

Required

a) Construct the appropriate random number mappings for the random variables starting with .00. (2.5 marks for demand and 1.5 mark for lead time)

b) Simulate CWD's ordering and sales policy for 20 weeks. (12 marks)

c) Compute the average cost of the policy ( 4 marks)

Demand	.15	.84	.16	.12	.55	.16	.84	.63	.33	.57	.18	.26	.23	.52	.37	.70	.56	.99	.16	.31
Lead time	.47	.74	.35	.56	.64	.21	.55	.01	.40

Use the following headings

Month

OI

UR

AI

RN

D

DF

EI

SO

order

RN

Lead-time

IC

SOC

OC

TC

Answer 1

------------------------------------------------------------------------------------------------------------------------------------------------------------

1) Calculate the cumulative probability distribution for the given demand and lead-time probability distributions as done in Table A and Table B. The random number interval is then upto the cumulative probability.

For example, cumulative probability for 1st one is 0.15, so the random number interval is 0.01-0.15. The lower number is 0.01 and the upper number is 0.15. This is going to be useful when we are assigning demands to weeks using random numbers. So, if the random number generated for demand in the simulation is 0.04 which lies between 0.01-0.15, hence the weekly demand is going to be 1. If the random number for demand is 0.84, then it lies in the bucket 0.81-1 and hence the weekly demand is going to be 4. Similar logic applies for the lead time as well.

0 TABLE A: DEMAND PROBABILITY DISTRIBUTION TABLE B: LEAD TIME PROBABILITY DISTRIBUTION Random Random Lead Weekly Upper Demand Cumulative Cumulative Time (in Probability Probability Demand Interval Interval 0.15 0.25 0.4 0.2 0-0.15 0.4 0.16-0.4 0.8 0.41-0.8 0.81-1 0.15 0.4 0.8 1.4% 0.75 0.15 0.01 0.16 0.41 0.81 0.45 45 0-0.45 .750.46-0.75 0.76-1 0.01 0.46 0.76 0.25

------------------------------------------------------------------------------------------------------------------------------------------------------------

2) Setup the simulation table (Table C) and the formulas. We know that inventory at hand currently is 10, so that becomes the beginning inventory for the next week i.e. week 1.

------------------------------------------------------------------------------------------------------------------------------------------------------------

3) Calculate demand and lead time according to the random numbers provided and the distribution table calculated. Please note that the question says that orders are placed only when the inventory reaches 5 or less and only when the previous order has already been received, so the lead time is simulated only when the order is placed. Order quantity is always 10, as per the problem statement. If the ending inventory at the end of every week is more than 5, or there is an order that has been placed but not received yet, then no order is placed. Accordingly, the formula is setup in column J for the 2 conditions for the order.

------------------------------------------------------------------------------------------------------------------------------------------------------------

4) Lead time of 2 means that order placed in week 2 will come at the beginning of week 5. Lead time of 1 means that order placed in week 11 will come at the beginning of week 13. Hence, week that the order is due is: Current week + Lead time + 1

I have set this up as an extra column in column R just for your understanding. Also, a formula has been setup in column C for units received, to account for units received in the week that the order is due. If you remove column R, then you have to manually enter the values in column C whenever the order is to be received.

------------------------------------------------------------------------------------------------------------------------------------------------------------

5) Adjusted Inventory = Beginning inventory + Units received from order

Units sold/Demand fulfilled will be equal to the demand if we have sufficient inventory at hand, otherwise it will be equal to whatever units are available at the moment. Ending inventory = Adjusted Inventory - Demand Fulfilled

------------------------------------------------------------------------------------------------------------------------------------------------------------

6) Order cost is fixed for every order i.e. 50. So, whenever order is placed as per explanation above, 50 is the cost. If order is not placed due to conditions not being met i.e. Order flag is FALSE, the order cost is 0.

------------------------------------------------------------------------------------------------------------------------------------------------------------

7) Holding cost or Inventory cost = Ending inventory * 50 (Each unit that remains poses a cost of 50)

------------------------------------------------------------------------------------------------------------------------------------------------------------

8) Lost sales cost or stock out cost = 200 per unit which were lost in sales. Lost sales only occur when demand is more than adjusted inventory.

So, if Demand> adjusted inventory: stock out = demand - adjusted inventory, otherwise it is 0.

------------------------------------------------------------------------------------------------------------------------------------------------------------

9) Total cost = Sum of inventory costs, order costs and stock out costs (Cell B32). Average weekly cost is the total cost/number of weeks = total cost/20 = $308

------------------------------------------------------------------------------------------------------------------------------------------------------------

Final Solution:

File Home Insert Page LayoutFormulas ta Review ViewDeveloper Help Tell me what you want to do sha 2 TABLE C: SIMULATION nemand Ending stodk-outorder Fulfilled Inventory ed Random Demand Fulfilled NumberLead-Time Inventory (Lead Time) Units Adjusted Order Order Cost Total Cost week#| Inventory | Received | Inventory | Number Al (Demand) 티 Soc 10 2 $ 250 $ S150 $ 0.47 50 $ 300 9 0.74 2 $ 150 $ 400 400 0.35 1250 50 S300 10 400 300 0.56 2$ 150 $ 6 $ 400 300 -S300 S Toa6,150 Average weekly $308

--------------------------------------------------------------------------------------------------------------------------------------------------------------

Formulae used:

Number DF H10IF(IFERROR VLOOKUP(A11,SR$10:R10,1,FALSE),0) 0,10,0) 811+C11 0.15 커11 -F(IFERROR(VLOOKUP(A12,$RS10:R11,1,FALSE),0)»D, 10,0) 812+C12|0.84 F(IFERROR(VLOOKUP(A13,SRS 10:R12,1,FALSE),0)>0, 10,0) |-e13-c13 |016 H13IF(IFERROR VLOOKUP(A14,SR$10:R13,1,FALSE),0) 0,10,0) B14+C14 0.12 IF(IFERROR(VLOOKUP(A15,SR$10:R14,1,FALSE),0) 0,10,0) 815+C15 0.55 IF(IFERROR(VLOOKUP(A16,SR$10:R15,1,FALSE),O) 0,10,0 816+C160.16 -H16 |=F(IFERROR(VLOOKUP(A17,SRS10:R16,1,FALSE),0)>0,10,0) |-817tC17|0.84 IF(IFERROR(VLOOKUP(A18,5R$10:R17,1,FALSE),0) 0,10,0) 818+C18 0.63 H18 IF(IFERROR VLOOKUP(A19,SR$10:R18,1,FALSE),0) 0,10,0) 819+C19 0.33 커19 |=F ,0) |-620+C20|0.57 IF(IFERROR(VLOOKUP(A21,5R$10:R20,1,FALSE),0) 0,10,0) 821+C21 0.18 H21IF(IFERROR VLOOKUP(A22,SR$10:R21,1,FALSE),0) 0,10,0) 822+C22 0.26 커22 -F(IFERROR(VLOOKUP(A23,SRS10:R22,1,FALSE),0)»D, 10,0) 823HC23 0.23 F(IFERROR(VLOOKUP(A24,SRS 10:R23,1,FALSE),0)>0, 10,0) |-e24-c24 |O.52 H24IF(IFERROR VLOOKUP(A25,SR$10:R24,1,FALSE),0) 0,10,0) B25+C25 0.37 -VLOOKUP(E11,SES3:SG$6,3,TRUE)I-IF(F11»D11,011,F11).-D11-G11 -VLOOKUP(E12,SES3 $G$6,3,TRUE) IF(F12 D12,D12,F12) D12-G12 VLOOKUP(E13,SES3:SG$6,3,TRUE)I:F(F13-D13,013,F13) |:013-G 13 -VLOOKUP(E14,SES3:SG$6,3,TRUE)-IF(F14>D14,D14,F14) |=D14-G14 VLOOKUP(E15 SES3:$G$6,3,TRUE)IF(F15 D15,D15,F15) D15-G15 -VLOOKUP(E16,SES3:SG$6,3,TRUE)!=IF(F162D16,D16f16)|:016-G16 -VLOOKUPE17,SES3:$G$6,3,TRUE)-IF(F17>D17,D17,F17) |=D17-G17 -VLOOKUPlE18,SES3:SG$6,3,TRUE)!=IF(F 18>D18,018,F18) |:018-G 18 -VLOOKUP(E19,SES3:SG$6,3,TRUE)l=IF(F19>D19,D19,F19)|:019-G19 -VLOOKUP(E20,SES3.SG$6,3,TRUE)-IF(F2p>D20,D20F20)|-D20-G20 VLOOKUP(E21,SES3:SG$6,3,TRUE)=IF(F21>D21,D2LF2 1)|:02 1-G21 VLOOKUP(E22,SES3 $G$6,3,TRUE) IF(F22>D22,D22,F22) D22-G22 VLOOKUP E23,SES3 $G$6,3,TRUE) EIF(F23>D23,D23,F23) D23-G23 VLOOKUP(E24,SES3:SG$6,3,TRUE)!=IF(F24>D24,D24,F24) |:024-G24 -VLOOKUP(E25,SES3:SG$6,3,TRUE)-IF(F25>D2 5,D25,F25)-D25-G25 VLOOKUP(E26,SES3 $G$6,3,TRUE)IF(F26>D26,D26,F26) D26-G26 -VLOOKUP(E27,SES3:$G$6,3,TRUE)!=IF(F27>D27,D27,F27) |:027-G27 -VLOOKUPIE28,SES3:SG$6,3,TRUE)-IF(F2R>D28,D28,F28)!=D28-G28 -VLOOKUP[E29,SES3:SG$6,3,TRUE)-IF(F29>D29,D29,F29) |-D29-G29 -VLOOKUP(E30,SES3:SG$6,3,TRUE)l=IF(F30>D30,D30,F30)|:030-G30 |=F(F11>D11,F11-D1 1,0) F(F12 D12,F12-D12,0) -IF(F13-013,F13-D13,O) |=F(F14»D14,F14-D14,O) IF(F15>D15,F15-D15,0) -FF16»D16,F16-D16,O) |=F(F17>D17,F17-D17,0) -IF(F18>D18,F18-D18,O) |=F(F19>D19,F19-D19,0) |=F(F20>D20,F20-D20,O) -IF(F21>D2 1,F21-D2 1,0) IF(F22>D22,F22-D22,0) FF23>D23,F23-D23,0) -IF(F24>D24,F24-D24,0) |=F(F25»D25,F25-D25,0) IF(F26 D26,F26-D26,0) -IF(F27>D27,F27-D27,0) |=F(F28>D28,F28-D28,0) ー F(F29>D29,F29-D29,0) |=F(F30>D30,F30-D30,0) (I FERROR(VLOOKUP(A20$RS 10 R 19, 1,FALSE)이 0,10 IF(IFERROR(VLOOKUP(A26,SR$10:R25,1,FALSE),0) 0,10,0) 826+C26 0.7 IF(IFERROR(VLOOKUP(A27,SR$10:R26,1,FALSE),O) 0,10,0 827+C270.56 H27IF(IFERROR VLOOKUP(A28,SR$10:R27,1,FALSE),0) 0,10,0) 828+C28 0.99 IF(IFERROR(VLOOKUP(A29,5R$10:R28,1,FALSE),0) 0,10,0) 829+C29 0.16 H29IF(IFERROR VLOOKUP(A30,SR$10:R29,1,FALSE),0) 0,10,0) 830+030 0.31 P31 0123456789 123456789 01234567-890

Order Cost Total Cost TC Inventory Cost Stock-out Cost Order arrival weck IC SOC OC (Lead Time) 10 11 IFIAND(A11>MAXISR$10SE 12 IFIAND A12 MAXISRS10:R11),H12 -5).TRUE FALSE 0.47 13 EIF(AND A13 MAX(SR$10:R12),H13<-5),TRUE,FALSE) 14 IFIAND(A14 MAXISR$10R13),H14<-5),TRUE,FALSE 15 IFIAND(A15 MAXISRS10:R14) H15-5) TRUE FALSE 16 EIF(AND(A16 MAX(SR$10:R15),H16-5),TRUE,FALSE) 17 EIFIAND A17 MAXISR$10R16),H17<-5),TRUE FALSE)074 18 IFIAND(A18 MAXISR$10:R17),H18-5),TRUE,FALSE) 19 EIF(AND(A19 MAX(SR$10:R18),H195),TRUE,FALSE) 20 EIF(AND A20 MAXISR$10R19),H20-5),TRUE,FALSE 21 =IF(AND(A21>MAX(SR$ 10:R20),H21-:5),TRUE,FALSE) |0.35 22 IF(AND(A22 MAX(SR$10:R21),H22 5),TRUE,FALSE) 23 EIF(AND A23 MAX(SR$1 24 IFIAND(A24 MAXISR$10:RSE 25 FIFIAND(A25>MAXIŞR$10:R24),H25 5),TRUE,FALSE) 26 EIFIAND A26>MAX(S 0.56 27 |=IF(AND(A27>MAX(SRS 10:R26),H27<=5),TRUE,FALSE) 28 FIFIAND(A28-MAXISR$10:R27),H28< 5),TRUE,FALSE 29 EIF(AND(A29 MAX(SR$10:R28),H29<5),TRUE,FALSE) 30 EIF(AND ASOPMAXISR$10:R29),H30K-5),TRUE,FALSE 31 :R10) H11-5) TRUE.FA 11-TRUE50,0) M11+N11+011 -IFERROR:S0S5,3,TRUE),0 50 H11 -1 ERROR(VLOOKUP(K 12,SMS3:sos531RUELO 1-50-H12 IFERROR(VLOOKUP(K13,SM$3 $O$5,3,TRUE),O) 50 H13 -IFERROR(VLOOKUP(K14,SMS3:$OS5,3,TRUE),0) 50*H14 -IFERROR(VLOOKUP(K15,SMS3:SOSS,3,TRUE),0 50 H15 IFE3 $O$5,3,TRUE),o) 50 H16 R(VLOOKUP(K11,SMS3 =111"200 -1124200 113 200 -l14 200 15"200 ะเ16"200 -117"200 =118"200 119 200 120 200 121 200 122 200 23"200 =124"200 125-200 126 200 =127"200 =128"200 129 200 130 200 IF(J1 IF(U11-TRUE,A11+L11+1, RUE A12+112+1 IF(13-TRUE,50,0) EM13+N13+013IF(13-TRUE,A13+L13+1," (J14-TRUE,50,0) |-M14tN 14+014 | |坰114-TRUE,A14+L14+1,"") ERROR(VLOOKUP(K16,SMS -IFERROR(VL -IFERROR(VL IFE :F(J 16-TRUE,50,0) ะ F(J17-TRUE,50,0) 카(J18-TRUE,50,0) :F(J19:TRUE,50,0) |=M 16tN 16+016 | |-M 17+N 17+017 | |=M184N 18+018 | |:M19tN 19+019| |-M20+N20+0201 |-M21+N21+021 | |:F(J16-TRUE,A16-L16+1,"*) |-|FU17-TRUE,A17-L17+1,'") -1FU18-TRUE,A184し18+1,..) |:IF(J19:TRUE,A194し19t1,"*) -F(120-TRUE,A20+L20+1,"*) |ะ.FU21-TRUE,A21+L21+1,"") OOKURK 17,SMS 3: sos53,TRUE),0) LOOKUP(K18,SM$3:$OS5,3,TRUE),0) 50*H17 50 H18 $O$5,3,TRUE),o) 50 H19 ERROR VLOOKUP(K19 SM$3 (J20-TRUE,50,0) -F(J21-TRUE,50,0) IF(J22 TRUE,50,0) M22+N22+022F(J22 TRUE,A22+122+1, :F(123-TRUE,50,0) IFERROR(VLOOKUP(K20,SMS3:$O$5,3,TRUE),0) 50*H20 -IFERROR(VLOOKUP(K21,SMS3:S0$5,3,TRUE),O 50 H21 10R22) H235),TRUE,FALSE) R23) H24<-5).TRUE,FA IFERROR 50*H23 -IF:S0S5,3,TRUE),0) 50 H24 R(VLOOKUP(K23, SMS 3 : soss,3,TRUE)이 5,3,TRUE),01 ,TRUE).0 |:M23HN 23+023 -M24tN24+024 |-M25+N 25+025 | | | |:F(123-TRUE,A234L23+1,"*) |:F(J24-TR ") |-1F025-TRUEA25+L25+1,"") ERRORIVLOOKUP(K24,SMS3 PK25 SMS3 (124-TRUE,50,0) - FU25-TRUE,50,0) UE A24+L24+1," SR$10 R25),H25-5) TRUE FALSE) IFERRORIVLOOKUP(K26,$MS3:Soss,3,TRUE),0) 50 H26 -IFERROR(VLOOKUP(K27,SMS3:$OS5,3 50*H27 -IF(J27-TRUE,50,0) M27+N27+027IF(127-TRUE A27+127+1, -IF(J28 TRUE,50,0) M28+N28+028 F(J28-TRUE A28+L28+1, IF(J29-TRUE,50,0) EM29+N29+029IF(129-TRUE,A29+L29+1, IF(J30-TRUE,50,0) M30+N30+030 IF(U30-TRUE A30+L30+1, IFERROR VLOOKUP(K28,SMS3 SOss,3,TRUE,0) 50 H28 IFERROR VLOOKUPK29,SMS3:So$s,3,TRUE,050 H29 I:$O$5,3,TRUE),0) 50*H30 IFERRORIVLOOKUP[(K30,SMS3 -SUMIM 10:M30) -SUM(N10:N30)-SUM(010:030) -SUMP10:P30) 1

--------------------------------------------------------------------------------------END-----------------------------------------------------------------