How to solve using excel. Please include formulas and steps

10-25. ?Mattress Heaven orders a certain brand of mattress from its supplier and sells the mattresses at its retail location. The store currently orders 400 ?mattresses whenever the inventory level drops to 200. ?The cost to hold one mattress in inventory for one day is $0.75. ?The cost to place an order with the supplier is $75, ?and stockout costs are $150 ?per mattress. Beginning inventory is 150 ?mattresses. The daily demand probabilities are shown in the following table:

631

\table[[Daily Demand,Probability],[20,0.08],[30,0.14],[40,0.20],[50,0.26],[60,0.22],[70,0.10]]

Lead time follows a discrete uniform distribution between 2 ?and 5 ?days (both inclusive). ?Simulate this inventory policy for a quarter (90 ?days) ?and calculate the total quarterly cost. Also calculate the percentage of stockouts for the quarter. Replicate these calculations N ?times each to calculate the average values for these measures.

10-26. ?Consider the Mattress Heaven problem described in Problem 10-25.

(a) ?Mattress Heaven would like to evaluate ordering 350,400,450, ?and 500 ?mattresses when the reorder point of 200 ?is reached. Based on the average total quarterly cost, which order quantity would you recommend?

(b) ?Mattress Heaven would like to evaluate reorder points of 150,200,250, ?and 300 ?mattresses, with an order quantity of 400 ?mattresses. Based on the average total quarterly cost, which reorder point would you recommend?

A B C D E F G H J K L M N O P Q R S T U V W 1 Problem 10-26 Data Table 2 (Calculations set to Manual. Press F9 to recalculate). Demand Distribution Begin Units Avail Dem Deman End Stock End Inv Place Lead Arrive Stockout Order % stoc- 3 Day Inv Rec Inv and d Filled Inv out + Order order? time on day | Inv cost cost cost Demand Probability RN intvl LL Run Total cost kout 150 20 0.08 30 0.14 40 0.20 50 0.26 60 0.22 70 0.10 Order Quantity 400 Reorder Point 200 nv Cost $0.75 (per unit per day) 12 13 Stockout cost $150 14 (per unit) 15 16 20 Order cost $75 17 (per order) 18 19 Lead time minimum 20 24 Lead time maximum 21 22 26 Based on 1 replication: 23 Total cost = 24 28 Percentage stockouts = 25 26 30 Based on 200 replications: 27 Total cost = 28 32 Percentage stockouts = 29 33 30 30 34 31 31 35 32 32 36 33 33