Harley purchases components from three suppliers. The demand, pricing, and holding cost rates for each component are included in the Excel template. Currently, Harley purchases a separate truckload from each supplier. As part of a JIT drive, Harley is considering aggregating the purchases from the three suppliers. Currently, the trucking company charges a fixed cost of $2300 for each delivery. They have agreed to pick up three shipments using one truck for an additional cost of $600 (i.e., $300 per additional stop.) Note: this additional stop" charge was not included in the example model; to account for it, it should be added into S*. Using the provided Excel template, please do the following: 1) Analyze the total costs of the current individual ordering strategy. 2) Analyze the total costs of the proposed aggregated ordering strategy. 3) Complete the Data Table to find the Total Costs for different order frequencies and use the MIN and MATCH Excel functions to find the lowest cost order frequency. Use your completed Excel file to answer the quiz questions. Please make sure to submit your file when you complete the quiz in order to receive credit for the exercise. Known Information Supplier A Supplier B Supplier Monthly Demand 25000 3000 1000 Annual Demand Holding Cost %, h 26.00% 30.00% 20.00% Unit Cost= C $ 6.00 $ 4.00 $ 8.00 Fixed Order Cost (Transportation) $ 2,300.00 $ 2.300.00 $ 2,300.00 Fixed Order Cost (receiving & Sorting) $ 150.00 $ 75.00 $ 120.00 Annual Holding per Unit= h*c Part 1: Independent Order Analysis Supplier A Supplier B Supplier C EOQ Cycle Inventory (Avg. Inv.) Annual Holding Cost Order Frequency Annual Ordering Cost Total Annual Order and Setup Costs Total Cost Part 2: Joint Ordering Replenishment Analysis $ 600.00 Additional Stop Charges (Add into S* Aggregated Order Cost (S*) Optimal Number of Aggregated Orders (n*) Rounded Number of Aggregated Orders (n*) Total Cost Order Quantity by Product (D*) Cycle Inventory (Avg. Inv.) Annual Holding Cost Annual Ordering Cost Total Annual Order and Setup Costs Cost Savings Due to Aggregation (Joint Replenishment) Part 3: Data Table Minimum Total Cost = n* for Min. Total Cost = Total Cost n M + L ON 9 1 2. 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25