I ONLY Need Question 3, 4, and 5!

Please Use Excel and show Calculations. Thank you so much in advance!

CASE 10.2 TV Gadgetry TV Gadgetry (TVG)a distributor of inexpensive kitchen tools and small appliances that are promoted on late-night in fomercialsis facing challenges in its DC operations. Compared to the previous month, both the numbers of orders and customer complaints regarding service quality have increased dramatically in September. The company's manual picking systems and paper-based order management methods seem to be affecting perfor- mance, as is the heavy labor turnover occurring in the DC. Dylan Larking, the company's vice president of logistics is determined to uncover the source of these complaints. He asks the new intern, Connor McDavis, to collect data and evaluate a variety of KPIs. McDavis jumps on the task and captures the information needed to evaluate order ll rate, fulllment accuracy, in voice accuracy, and on-time di spatch. These metrics, McDavis believes, will help him identify the real problems so that changes can be made. His effort generates the following data: FULFILLMENT DATA SEPTEMBER AUGUST Customer Orders Processed 50,000 40,000 Units Ordered 300,000 200,000 Units Shipped 287,333 192,507 Correct Units Shipped 247,385 188,263 Correct Customer Invoices 46,310 39,124 Orders Ready by Deadline 49,188 38,791 Total Labor Hours Paid 2,000 1,500 After thanking McDavis for the collecting the data, Larking asks the intern to do three things: (a) Calculate relevant distribution KPIs; (b) Compare the results to the TVG fulll- ment goals; and (c) Identify the major problem areas and potential solutions. "Get busy with this." says Larking. I want you to investigate the situation and report back to me on Monday" CASE QUESTIONS 1. Help McDavis with the analysis by calculating KPIs for: (a) Unit ll rate; (b) Fulllment accuracy; (c) Document accuracy; (d) On-time dispatch; and (e) Productivity. 2. Compare your KPIs to the following TVG goals and comment on the problem areas: GOAL KPI Unit Fill Rate Fulfillment Accuracy Document Accuracy On-Time Dispatch Productivity 95% 98% 99% 95% 135 units shipped per labor hour 3. Based on your KPI calculations, what is the perfect order index for TVG? 4. What actions should TVG management take to address the problems that you identied? 5. What beneats could TVG gain by adopting a warehouse management system? A B D E F G H wN August 50000 300000 287333 247385 46310 49188 2000 1 2 3 Fullfillment Data Sep 4 Customer orders processed 5 Units ordered 6 Units Shipped 7 Correct units shipped 8 Correct customer invoices 9 Orders ready by deadline 10 Total Labor hours paid 11 12 Unit fill rate 13 Fullfillment Accuracy 14 Document accuracy 15 On-time dispatch 16 Productivity 17 18 19 Perfect order index % on time * % unit fill rate % fullfillment accuracy *% correct 20 documentation 40000 200000 192507 188263 39124 38791 1500 Benchmark 96.2596 97.8096 97.81% 96.989 128.34 95.7896 86.10% 92.62% 98.3896 143.67 95% 98 99% 95% 135 KPI met KPI met for one month KPI not met Sep August 75.196 POI as per KPI 89.396 87.6% 1 2 3 Fulfillment Data 4 Customer orders processed 5 Units ordered 6 Units Shipped 7 Correct units shipped 8 Correct customer invoices 9 Orders ready by deadline 10 Total Labor hours paid 11 12 Unit fill rate 13 Fullfillment Accuracy 14 Document accuracy 15 On-time dispatch 16 Productivity 17 18 19 Perfect order index Sep 50000 300000 287333 247385 46310 49188 2000 August 40000 200000 192507 188263 39124 38791 1500 =B6/B5 =B7/B6 =B8/B4 =B9/B4 =B6/B10 =C6/C5 =C7/C6 =C8/04 =C9/04 =C6/C10 Benchmark 0.95 0.98 0.99 0.95 135 Sep =812*813*B14*B15 August -C12*013*C14*015 POI as per KPI =D12*D13*D14*D15 % on time * %unit fill rate % fullfillment accuracy *% correct 20 documentation 21