**** Please use Excel to answer the questions and Please provide the formulations ****

Troy's Tires replenishes its inventory by ordering 250 tires from the factory whenever its current inventory reaches 40 tires. The lead time (in days) to receive an order from the factory follows the distribution shown in the following table: LEAD TIME PROBABILITY 1 0.10 2 0.22 3 0.28 4 0.15 5 0.15 6 0.10 The cost to hold 1 tire in inventory for one day is $0.20. The cost to place an order with the factory is $100. Stockout costs are estimated at $10 per tire. The initial inventory level is 100 tires. a) Simulate 6 months (180 days) of operation to calculate the total semiannual cost and the percentage of stockouts for the period. Replicate these calculations 200 times each to calculate the average values for these measures. b) Troy's Tires would like to evaluate the economics of ordering 150, 200, 250, 300, and 350 tires, with a reorder point of 40 tires. Based on the average total semiannual cost, which order quantity would you recommend? c) Troy's Tires would like to evaluate the economics of ordering 250 tires, with reorder points of 40, 50, 60, 70, and 80 tires. Based on the average total semiannual cost, which reorder point would you recommend