please solve using excel

The Tire Warehouse (TTW) sells a certain brand tire that has a normally distributed daily demand with a mean of 15 tires and a standard deviation of 2 tires. TTW noticed that after fitting a tire

in a

vehicle, 10% of the tires purchased daily by customers, shows some defects and would thus need immediate replacement with another one from stock (model defective tires using a Binomial

distribution).

TTW employs a continuous review policy for managing their inventory; where, TTW replenishes their inventory by ordering 300 tires from the factory when their current inventory reaches

40 tires. The lead-time in days to receive their order from the

factory follows the distribution below:

The cost to hold one tire in inventory for one day is $0.20. The cost to place an order with the factory is $100. Stock out costs are estimated at $50 per tire. Initial inventory level is 60 tires.

Requirements:

a. Simulate an entire year (300 working days) of operation

of the warehouse.

1. Calculate the various average inventory costs of their policy. Analyse the different cost items. Do you think TTW is ordering excessive number of tires?
2. Evaluate the economics of ordering 100 and 300 tires with reorder points of 20 and 40 tires. Show the result of each simulation here or in your Excel file. Which order quantity and reorder quantity would you recommend?

The Tire Warehouse (TTW) sells a certain brand tire that has a normally distributed dally demand with a mean of 15 tires and a standard deviation of 2 tires. TTW noticed that after fitting a tire in a vehicle, 10% of the tires purchased daily by customers, shows some defects and would thus need immediate replacement with another one from stock (model defective tires using a Binomial distribution). TTW employs a continuous review policy for managing their inventory: where, TTW replenishes their inventory by ordering 300 tires from the factory when their current inventory reaches 40 tires. The lead-time in days to receive their order from the factory follows the distribution below: ng of peits, p The cost to hold one tire in inventory for one day is $0.20. The cost to place an order with the factory is $100. Stock out costs are estimated at $50 per tire. Initial inventory level is 60 tires. Requirements: (a) Simulate an entire year ( 300 working days) of operation of the warehouse. (16 points) (b) Calculate the various average inventory costs of their policy. Analyse the different cost items. Do you think TTW is ordering excessive number of tires? ( 8 points) (c) Evaluate the economics of ordering 100 and 300 tires with reorder points of 20 and 40 tires. Show the resulc of each simulation here or inyour Excel file. Which order quantity and reorder quantity would you recommend? (6 points)