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 Simulate an entire year (300 working days) of operation of the warehouse Calculate the various average inventory costs of their policy Analyse the different cost items Do you think TTW is ordering excessive number of tires 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 ABET SO 1 2 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 noof poits , 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 resulic of each simulation here or in your Excel file Which order quantity and reorder quantity would you recommend ( 6 points)

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 11, 2024

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

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:

Simulate an entire year (300 working days) of operation of the warehouse.
Calculate the various average inventory costs of their policy. Analyse the different cost items. Do you think TTW is ordering excessive number of tires?
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?

ABET SO: 1;2 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: noof poits , 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 resulic of each simulation here or in your Excel file. Which order quantity and reorder quantity would you recommend? ( 6 points)