Manoj Kumar | Freelancer Finance Insights Unit Dashboard

Finance Insights Unit Dashboard

Power BI Desktop Financial Insights Unit Operations Dashboard Volumetric Report Volume Data Reporting Time Period: Jan-19 to Dec-22 O2C Analytics P2P Analytics Order to Cash Data Insights Procure to Pay Data Insights Time Period: Jan-22 to Dec-22 Time Period: Jan-22 to Dec-22 Analytics & Insights R2R Analytics Record to Report Data Insights Time Period: Jan-22 to Dec-22 Last Refreshed: 17/08/2022 Power BI Desktop SSCs MARC OHGFC OMS 17/08/2022 OSSCA STL & (St. Louis) OSSCA JC1 OSSCA UK RAPP US OML OMG US IMS EG+ Critical Mass Clemenger - Aus Agency TBWA World health Agency RAPP UK One & All OMG-UK OMG Canada GMR DDB Canada CPM - UK BBDO Canada Agency RAPP US Adelphi-UK 493 492  496 495 386 498 559 373 526 520 479 446 5.1K 4.7K 5.2K 5.1K 6.0K 3.9K 3.9K 3.6K 3.9K 4.4K 443 491 475 457 441 474 440 453 3.9K 3.1K 227K 224K 255K 251K 230K 264K 259K 241K 263K 259K 286K 280K 251K 245K 289K January Febru… March April May June July August Septe… October Nove… Dece… January Febru… March 279K Dece… 227K 193K August Nove… 204K July 253K 184K June October 190K May 242K 231K April 5.5K 2021 Y-O-Y Volume Comparision Total 2019 Total 2020 Total 2021 5.6K 5.0K 4.4K 2020 14.0K 6.2K 5.8K 5.3K 259K 4.2K Septe… 203K March  244K  Volume Febru… All 514 484 3.8K 277K Delivery Location 484   2022 Total 2022 279K 280K July 227K 286K June 253K 259K 204K 259K 259K May 242K 263K 184K 264K 280K April 193K 241K 190K 230K 269K March 231K 251K 268K February 203K 255K 289K 244K 224K 245K 277K 227K 251K Reset Filters January 572 570 567 July 3.2K 3.2K 2.4K 2.5K 2.6K 2.9K 3.0K January All 564 556 545 456 3.1K Process 507 489 448 7.0K $ Value 520 390   515 280K All 511 411 412 Domain 514 June Vol/FTE 513 509 507 495 488 472 571 499 498 541 490 12/31/2022 495 558 269K Range 498 494 550 545 544 May FTE 492 544 268K 486 536 April Agency 1/1/2020 Last Updated: Volume Trend Analysis Summary Home August September October November December Power BI Desktop AR Summary Key Metrics UK MARC Billing (Jan'22Dec'22) Cash Application (Jul'22) OMS STL JC1 OHGFC Note: • 70% of the input data is coming from system reports • 30% of the data comes from team's produc on trackers • Values for UK are in GBP. Avg Days to Bill Avg Days for Approval 2.8 1.46 Unapplied Cash % of Unapplied Cash % of Applied Cash $2.5M 3.7% 96.3% Debtor o/s (aging>90 Days) Collection (Jul'22) $1.5M WIP Analysis (Jul'22) WIP Balance (Debit) WIP Balance (Credit) WIP Balance $56.1M ($80.4M) ($24.3M) Power BI Desktop Billing Process Summary SSC # of Invoices Avg Posting Days Avg Approval Days 17676 2.8 1.46  UK DARL DDAE TBEG TBMT 1.4K 469 527 403 424 Jan 22 288 Feb 22 1.6K 1.5K 1.4K 1.4K 450 411 461 429 552 289 271 May 22 Jun 22 1.2K 486 482 538 491 277 272 Mar 22 Apr 22 212 Avg Approval Days By Top 10 Agencies (Volume) All 3.4 2.4 2.3 2.3 3.4 Bill Type 3.2 2.7 2.5 All 2.3 1.4 DDEU Jul 22 TBMT DACW By Rush Request  Month DACH DARL TBML DACL TBEG BBAM 7/1/2022 DDAE Top 3 Bill Type Yes 80% 13.5K 79.0% 2.5% 2.7 3.4  1/1/2022 No 3.5 2.9 Agency 4.5 3.5 1.3K 1) Rush requests are not tracked in OMS & MARC; No rush requests in OHGFC & STL (Billing Request TAT is 24 hours) 2) Data for approval mestamp not tracked or retrievable in OMS & MARC 3) Values for UK are in GBP Avg Days to Bill By Top 10 Agencies (Volume) # of Invoices By Month, Top 5 Agencies (Volume) DACW Note: 60% 2.1 1.8 40% 1.7 1.4 2.5K 1.1K 6.3% Quick Bill DDEU DARL CEDA DACW TBMT DACH REDW TBEG DACL TBML 97.5% Customer Advance Bill Type Invoice Processing 20% 0% Power BI Desktop Billing Process Summary SSC # of Invoices Avg Posting Days Avg Approval Days 131872 1.4 1.12  All # of Invoices By Month JC 1 MARC OHGFC OMS Avg Days to Bill STL UK JC 1 MARC 23K 18K 16K 3K 2K 2K 2K 3.0 3K 19K 19K 2K 18K 2K 18K 3K 3K 2K 2K 2K 2K 2K 6K 5K 5K 5K 7K 5K 5K 6K 4K 3K 5K 4K 3K 4K 3.2 1.0 4K 3K 3K 3K 3K 1.0 Jan 22 Feb 22 Mar 22 Apr 22 May 22 Jun 22 Jul 22 Jan 22 Avg Approval Days By Month OHGFC 2.1 0.8 1.0 1.0 0.9 1.0 Jan 22 OMS STL 1.0 1.0 UK JC 1 - Feb 22 Mar 22 Agency 3.0 2.9 1.0 1.2 1.2 1.0 1.2 - Feb 22 Mar 22 Apr 22 All 1.0 1.0 1.9 1.9 - 1.4 1.3 1.0 1.2 1.0 1.0 1.0 1.3 1.0 May 22 Jun 22 Jul 22 4.8% 3.8% - Apr 22 2.4% 2.3% 2.9% 1.0 0.7% May 22 Jun 22 Jul 22 0.0% 0.1% 0.1% 0.8% Jan 22 Feb 22 Mar 22 Apr 22 0.9% 0.3% 0.6% May 22  3.2 UK 0.3 0.7 UK Rush Request by Month 1.9 1.2 1.0 STL 1.8 4K MARC OMS 2.6 3K JC 1 OHGFC 0.7% Jun 22 Jul 22 Bill Type All  Month 1/1/2022 7/1/2022 Power BI Desktop Summary  UK Amount Invoice Total Cash Applied Cash Unapplied Cash Unapplied Cash % $428.34M 417.21M $11.1M 2.6% Total Cash Applied (Top 10 Agencies) DDAE 86M (28%) dach 14M (5%) DDEU 16M (5%) $2.0M All 22.0% DACW 19M (6%) 15.9% DARL 53M (17%) DARL DASY 30% $949.9K 20% $769.9K Pfizer Gulf FZ-LLC Nissan Motor (GB) Limited 8.7% British VIRGIN TBWA Broadc… ATLAN… Chiat Day Corpor… AIRWAYS (NY) LTD All $1.0M $0.9M 9.9% 9.4% tbln BBAM DDAE  10% DDEU $0.6M $0.5M 5.7% 5.4% DDCS DACW $0.4M 4.3% TBEG Month $0.3M 3.1% DDRY 1/1/2022 7/1/2022 0% Unapplied Cash By Top 5 Agencies, Month $1,434.8K 9.4% $1.1M 11.1% Unapplied Cash By Top 10 Customers $513.3K $477.3K $1.3M $0.5M $0.0M BBAM 53M (17%) Customer 13.1% $1.0M TBML 30M (10%)  20% $1.6M $1.5M 14.1% Agency $2.2M TBEG 14M (4%) 17.4% 1) JC1, OMS, OHGFC, STL & UK: Unapplied cash % is reported as receipts that remain uniden ﬁed and unapplied at the end of the month, post research 2) MARC: Unapplied cash % is reported as total dollar value of receipts in the month that were not iden ﬁed/applied at ﬁrst a empt 3) Values for UK are in GBP % of Unapplied Cash by Top 10 Agencies tbgr 13M (4%) 26.2% Note: Cash Application SSC Xero Limited $256.9K $223.6K $212.7K $205.7K 4.7% CILAG GmbH Interna… 4.1% 3.9% 3.8% Johnson GlaxoS… KFC & Services Adverti… Johnson Unlimited / KFC Consu… (Great … 10% $1.0M 0% DARL DASY DDAE tbln $2.0M $1.5M $422.9K 7.7% BBAM $1.8M $0.5M $1.4M $0.5M $1.1M $0.3M $0.4M $1.0M $0.6M $0.6M $0.5M $0.0M $0.4M $0.2M $0.4M $0.2M Jan-22 Feb-22 Mar-22 Apr-22 $0.7M $0.2M $0.3M $0.4M $0.3M $0.9M $0.3M May-22 Jun-22 Jul-22 Power BI Desktop Summary Cash Application SSC All  Total Cash Applied Cash Unapplied Cash Unapplied Cash % $10,839M 10,669.17M $169.7M 1.6% Agency Unapplied Cash by SSCs All 4% $80M  3.5% $73.9M 3.2% 3% $60M Customer All $40M $20M $18.4M 1.5% JC 1 MARC STL UK Total Cash by SSCs $4,860.0M 4786.1M $4bn 4bn 2bn $2bn 1635.4M 1606.7M 1059.6M 822.3M 0bn 7/1/2022 0.9% OMS Total Applied Cash by SSCs 7/1/2021 1% 0.7% OHGFC Month $15.1M $8.0M 1.1% $0M 2% $27.4M $27.0M  MARC OHGFC OMS STL UK $1,067.5M $849.3M 759.1M $0bn JC 1 $1,650.4M $1,625.1M JC 1 MARC OHGFC OMS $786.6M STL UK Power BI Desktop Summary Collection Process SSC  UK Total Receivables Total Overdue Total Overdue % $641.8M $152.1M 23.69% Note: 1) Values for UK are in GBP Time Period: As of Nov'21 Overdue By Aging Bucket Overdue By Top 10 Agencies 30% $27.1M Agency All $77.1M 26% $13.9M 13% DDAE DACW 12% DARL $10.9M 10% TBEG $9.3M $28.0M $7.5M 9% 7% BBAM TBMT $7.4M 7% NSS $5.9M $5.1M $5.0M 6% 5% 5% DDCS DDHG DASY  Customer 20% $12.5M $26.7M $12.0M 10% All   1/1/2022 0% 0-30 31-60 61-90 ($2.8M) 91-120 121-150 151-180 181+ Expected Cash Flow 30% $8.2M $0.30bn 27.5% 20% 10.7% $2.8M 9.2% $2.5M 8.4% $2.4M 8.2% $2.2M 7.5% $2.2M 7.4% $2.2M 7.3% $2.1M $2.1M 6.9% 6.9% C000007… OMNICOM MERCED… C000032… C200005… C000046… Designory C200005… Mars LLC - Omnicom GROUP CARS UK - DDB - DDB - DDB Finance LTD OMNICO… EUROPE … Europe L… CHICAGO C Space US $0.15bn 10% $0.03bn 0-30 31-60  Month $8.6M $2.5M Overdue By Top 10 Customers $3.2M  61-90 $0.00bn 90+ 7/1/2022 Power BI Desktop Summary WIP Analysis SSC WIP Balance (Debit) WIP Balance (Credit) WIP Balance $553.5M ($562.4M) ($8.9M)  UK WIP Balance By Month CEDA DDEU DDHG DDTL Note: 1) Values for UK are in GBP Time Period: As of Nov'21 Reporting Date By Aging Bucket 1/1/2022 REDW $50.2M $50.8M $49.9M $10.3M $10.3M $10.2M $9.8M $3.5M $1.1M $34.7M $34.1M $16.1M $16.0M $15.6M $10.5M $10.4M $10.3M 30 Feb 22 Mar 22 Apr 22 May 22 Jun 22 By Top 10 Clients WIP Balance Jul 22 By Agency %GT WIP Balance 40% $85.3M 0 -9 61 20 -1 91 50 -1 1 12 80 -1 1 15  Client Name 10 -2 1 18 1+ 21 All   Project Owner All   By Top 5 Project Owner CEDA 11% 60% $157.5M $71.1M 40% 28.2% 20% $32.0M $21.8M MAS… ARG… 0 -6 31  All ($1.8M) ($67.9M) 0Jan 22 Agency $0.9M ($2.3M) ($2.5M) $33.9M 7/1/2022 $60.1M $50.1M $33.8M  $11.5M $9.7M 4.5% 3.8% $6.3M 2.5% $5.6M $4.6M $4.5M No Mazda Google British AKZO HP Inc Iag Ba FedEx SC Client Moto… UK L… Airw… Nobe… UK L… Uk In… Expr… John… 0% $33.9M REDW 31% 14.5% DDEU 58% Robert Cruikshank ERIN JELFS 20% $23.7M 10.1% Vanessa Salter $11.4M 4.9% $7.8M Emma Looby KATHERINE LE CUIROT 0% Power BI Desktop Key Metrics UK MARC Invoice Processing (Jan'22-Dec'22) Payment (Jan'22Dec'22) T&E Expense (Jan'22Dec'22) Query Management (Jan'22-Dec'22) Vendor Master (Jan'22-Dec'22) AP Summary OMS STL JC1 OHGFC Note: >80% of the input data comes from team's produc on trackers Values for UK are in GBP Total Invoices Processed Invoices Discrepant Invoices 45292 41781 3511 Payment Requests Total Amount Urgent Payment Request 39677 $207.2M 5.1% Total Expense Reports Total Expense Amount 8378 $3.4M Inflow Queries Resolved Queries 136,714 136,714 Total Requests Processed Requests Query Requests 8,590 6,327 2,263