*** Please attach the Excel solution for the data below.

A Walmart Distribution Center purchases 120,000 bottles of Tylenol annually at a relatively constant rate. Orders for more Tylenol can be placed whenever Walmart wants. Each order has a fixed cost of $6.00 regardless of the order size. Walmart believes it incurs a holding cost annually of 20%. Walmart buys each bottle of Tylenol for $3.50. The supplier, Johnson & Johnson (J & J) , requires that Walmart purchase Tylenol in quantities equal to a multiple of either a full Tier (20 Cases: 1,440 bottles) or full Pallet (140 cases: 10,800 bottles.) J & J offers a 4% discount on the price if Walmart orders an integer number of pallets. If the order is for less than a pallet there is no discount and the price is $3.50 per unit. Use the information above to complete the Excel Shell worksheet. Specifically, find the EOQs for the 1-Tier and Full Pallet options. Then find the total costs for each option. Use your results to answer the first several quiz questions. Goal Seek Use Goal Seek to determine how many cases Walmart would need to purchase each time they place an order to make the total costs of the two options equal. Use this result to answer the last question. A B D E 1 Order Quantity Determination Parameters 2 Annual Demand Rate (bottles) 120,000 3 Ordering Cost per Order $ 6.00 4 Regular Price Per Bottle $ 3.50 5 Order Quantity Multiple One tier = 20 cases = 1,440 bottles 6 Minimum Order Quantity for Discount One pallet = 140 cases = 10,080 bottles 7 Full Pallet Discount % % 4.00% 8 Annual Holding Cost percentage, h 20% 9 Holding Cost per Year Depends on a 10 Order Quantity, Q To Be Determined 11 Quantity required to receive discount 108001 12 13 Base Case: Order Size = 1 Tier (1440 units) Suppose J & J offers a 4% discount on the price if Walmart orders an integer number of pallets. If the order is for less than a pallet there is no discount and the price is $3 per unit. Discount Case: Order Size Full Pallets EOQ w/ Discount / 1.00 1,440 15 EOQ 16 17 18 Actual 1 Tier ordering Q = 1440 19 H/Bottle / Year (1 Tier) 20 # of Orders (1 Tier) 21 Ordering Cost (D/Q)*S 22 Holding Cost = (0/2)*H 23 Purchasing Cost = DC 24 TOTAL COST OF 1 TIER 25 26 27 28 # of Full Pallets to Order Actual Order Q = # of Pallets x 10,800 H/Bottle / Year (Pallet Option) # of Orders (Pallet Option) Ordering Cost = (0/0)*5 Holding Cost = (0/2)*H Purchasing Cost = DC Total COST OF PALLET OPTION Cost Difference (1 Tier - Pallet Option) Question 1 What is the EOQ if Walmart for the full priced product (C = $3.50/bottle)? Question 2 What is the EOQ if Walmart for the 4% discounted full-pallet priced product (C = $3.36 / bottle)? Question 3 What are the total costs if Walmart sets their order quantity equal to one tier (Q=1.440) (ie., they do not receive the 4% discount)? Question 4 What are the total costs if Walmart sets their order quantity equal to one pallet (Q = 10.800) and receives the 4% discount? Question 5 To the nearest full pallet, how many pallets would Walmart need to set their order quantity at to make the cost difference between the pallet and 1 Tier option equal to So? (Use Goal Seek) O2 05 7 09