Question

please show work

TV Gadgetry TV Gadgetry (TVG)-a distributor of inexpensive kitchen tools and small appliances that are promoted on late-night infomercials-is 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 fill rate, fulfillment accuracy, invoice accuracy, and on-time dispatch. 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 300,000 200,000 Units Ordered 192,507 Units Shipped 287,333 247,385 188,263 Correct Units Shipped 39,124 46,310 Correct Customer Invoices 38,791 49.188 Orders Ready by Deadine 1,500 2,000 Total Labor Hours Paid After thanking McDavis for the collecting the data, Larking asks the intern to do three things: (a) Calculate relevant distribution KPls; (b) Compare the results to the TVG fulfill- ment goals, and (e) 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 fill rate (b) Fulfillment accuracy; (c) Document accuracy; (d) On-time dispatch; and (e) Productivity 2. Compare your KPls to the following TVG goals and comment on the problem areas GOAL KPI 95% Unit Fill Rate 98% Fulfillment Accuracy 99% Document Accuracy 95% On-Time Dispatch 135 units shipped per labor hour Productivity Distribution-Managing Fulfillment Operations 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 identified? What benefits could TVG gain by adopting a warehouse management system? 5. September August 40,000 Customer Order Processed 50,000 Units Ordered 300,000 200,000 Units Shipped Correct Units Shipped 192,507 287,333 247,385 188,263 39,124 Correct Customer Invoices 46,310 49,188 Orders Ready by Deadline Total Labor Hour (units/hr) 38,791 2,000 1,500 Metric September August Unit Fill Rate Fulfillment Accuracy Document Accuracy On-Time Dispatch Productivity Units/hr.) Metric Goal September August Comments Unit Fill Rate Fulfillment Accurac umen cura Productivity (Units/hr.) Month POI Comments Augu st

Answer 1

Below is the screenshot of the calculation performed in excel along with the formula applied -

Book1 Excel Abhishek Insert Page Layout Formulas Data Review ViewHelp Tell me what you want to do File Home A. Cut 費Wrap Text Calibri ▼ 田田 EB copy . Conditional Format as Ce nsert Delete Format Formatting Table Styles Paste 上·송· 園Merge & Certer $. % , '68.8 B 1 u . 田 Format Painter IEA . Number Clipboard Font Alignment Styles Cells A1 SeptemberAugust 2 Customer Order Processed 00000 200000 287333 192507 3 Units Ordered 4 Units Shipped 5 Correct Units Shipped 247385 188263 46310 6 Correct Customer Invoices 39124 49188 7 Orders Ready by deadline 38791 8 Total Labor Hour 2000 1500 September August 10 Metric Goal Comment 11 Unit Fill Rate 95% Exceed the set goa Lower than the set goal, hence needs to be reviewed Lower than the set goal, hence needs to be reviewed 95 96.25 86.10% 97 12 Fulfillment Accura 98% 9996 92.62% | 97.81% 13 Document Accuracy 14 On-Time Approach 95% 98.38% | 96.98% Exceed the set goal For August value is less than the goal but there is considerable increse in next month to exceeed the set goal 15 Productivity 135 16 Goa September August Comment 17 POI 87.56% 18

Abhishek Rai Book1 -Excel Insert Page Layout Formulas Data Review View Help Tell me what you want to do Σ AutoSum . A Cut 11A A Wrap Text Calibr E Copy Fill Sort & Clear Filter S $, % , 旨:, Conditional Format as. Cell Formatting Table Styles Insert Delete Format B 1 u, B-2.A. 一 军Merge & Center. Format Painter Font Number Editing Clipboard Alignment Styles Cells A1 September Au 2 Customer Order Processed 50000 40000 3 Units Ordered 4 Units Shi 287333 192507 5 Correct Units Shi 247385 188263 46310 6 Correct Customer Invoices 39124 7 Orders Rea deadline 49188 38791 8 Total Labor Hour 2000 1500 10 Metric Goal September August Comment Exceed the set 11 Unit Fill Rate 0.95 12 Fulfillment Accura 0.98 Lower than the set goal, hence needs to be reviewed 13 Document Accura 0.99 Lower than the set goal, hence needs to be reviewed 7/C2 Exceed the set 14 On-Time Approach 0.95 -D7/D2 135 15 Productivit For August value is less than the goal but there is considerable increse in next month to exceeed the set goal 16 Goal September August 17 POI B11 B12 B13 B14 18