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.

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.

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

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:

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

Formulae used:

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