ACL Data Analysis – Cost Management Data Requirements Review

1. ACL Data Analysis – Cost Management Data Requirements Review [Organization Name] [Date]

2. Opening • Our goal - To review the data file requirements for the proposed ACL software. • Identify the systems involved • Identify any data concerns that may exist • Estimate the effort required to develop the data sources.

3. Applications / Data Sources • Pathways Financial Management (PFM) • Accounts Payable data • Pathways Materials Management (PMM) • Purchasing data • Lawson • Payroll & Employee data • Star • Accounts Receivable data • Third Party Sources • Credit Card vendor transaction data

4. ACL Software PFM PMM Lawson Star Third Party Source AP Payments PO Transactions Employees AR Invoices Credit Card Trans. AP EFT Payments PO Cancellations Terminated Emp. AR Outstanding Prohibited Merchants AP Payment Adj Sales Orders Payroll Payments AR Transactions Holiday Table AP Vendors AP Invoices Travel & Ed. data AR Clients AP Vendor Bank Info AP Vendors AP Invoices System Diagram

5. Summary – Data Concerns • PFM & PMM • Most of the data is available with a few difficult fields. • Invoices reside in both PMM & PFM but and item level descriptions, unit price & qty only exist on the PMM side. • Vendor data – vendor bank account info does not exist (not true EFT actually wire transfers) • Vendor data – limited vendor tax information typically just 1099 vendors • Vendor data – employees exist in our vendor file as expense checks were formerly done in PFM until the change to Lawson last year • Vendor data – large file with many similar or duplicate vendors, prior versions of software did not allow inactivation

6. Summary – Data Concerns • PFM & PMM • Vendor data – exists in both PMM & PFM and PFM will also contain patient refunds from Star & IDX systems • AP EFT Payments – not true EFT but cash wire transfers with our bank • Sales Orders Canceled – not applicable to compare with Purchase Orders canceled

7. Summary – Data Concerns • Lawson • Travel & Education data - Trip level data that does not exist, detail available is typically Payee, Amount & GL Account number associated • Most of the data exists except some calculated fields such as gross pay • Confidentiality is the primary HR concern with payroll and bank account information

8. Summary – Data Concerns • Star • Will require further definition including the IT Development team, Corporate Compliance staff, Patient Financial Services staff and the software vendor (ACL). • Some of the analytics may have to be rewritten by ACL Consulting Services completely to align with healthcare billing process and procedures • AR Invoices – some combination of our patient charge master and billing tables • AR Clients – credit limits and credit review data may not be applicable • AR Outstanding Invoices – older accounts may be outsourced which could be a factor • An ongoing process - defining the data needed and how it will be analyzed will be the biggest challenge

9. Summary – Data Concerns • Third Party data • Will require further investigation to determine data availability for detailed credit card transactions from our credit card vendors. AP data will consist of lump sum payments and any detail to back up departmental transactions is paper based. • Developing secure file transfer protocols with the third party source • Data upload frequency • ACL suggests monthly uploads

10. Summary – Data Concerns • Data file consistency – Best Practice • Engage the IT Technology staff to produce files from the various core applications • Automated file creation limits the dependence upon individuals • Utilizing desktop exports and other tools such as sql query analyzer are not reliable as data file creation sources. • Data can shift and get skewed with desktop version changes (MS Office, Excel, SQL) causing the imports to be remapped frequently • Common delimiters such as comma or space delimited create data issues – suggest bar delimited or fixed width files

11. B01 – PMM or PFM AP Invoice Table Test 1: Invoices w/same Vendor No, Trans Type, Inv Amt & Inv Date Test 4: Invoices w/same Vend No, Inv No & different Inv Date Test 2: Invoices w/same Vendor No, Trans Type, Inv No, Inv Date & Inv Amt Test 5: Summary Outputs Test 3: Invoices w/same Vendor No, & similar Amts B01 – AP Duplicate Invoice Review (Novice ACL skills required)

12. B04 – PMM or PFM PFM AP Invoices Test 1: Review credit invoices to identify Vendor with a positive balance B04 – AP Vendor Balance Review (Novice ACL skills required)

13. B07 – Lawson Travel & Education Test 1: Identifies travel expense in excess of daily limits B07 – Travel and Entertainment – Meal Review (Novice ACL skills required)

14. B08 – Star AR Outstanding Invoices AR Invoices/Sales Test 1: Outstanding AR Aging Invoice Due Date Test 3: Identify inconsistent patient charging Test 2:Profile unpaid accounts by due date Invoice Due Date B08 – AR Open Item & Sales Price Review (Novice ACL skills required)

15. B09 – Third Party Data Charge Card Transactions Test 1: Identify possible duplicate charges. Same card, merchant, date, amt, vendor no & description Test 4: Identify possible circumvention of daily limits • Test 2: Identify possible duplicate charges. Same card, description, & amt within 7 days Test 5: Transactions to same card, vendor, product no, date & sum exceeds daily limits Test 3: Identify possible duplicate Charges. Same card, description, amt & vendor within 7 days Test 6: Transactions to same card, product over several days to various Vendors and the net amt exceeds limits B09 – Charge Card Duplicate Transactions & Limit Review (Novice ACL skills required)

16. B13 – PFM Vendor Banking Information Vendors Test 1: Identify vendors with the same banking information Test 4: Identify vendors with same address details (exact match) Test 2: Identify vendors with the same name but different code Test 5:Identify vendors with same Numeric address details Test 3:Identify vendors with similar names Test 6: Identify vendors with same Fed Tax ID B13 – AP Vendor Master Review (Novice ACL skills required)

17. B14 – PMM or PFM PMM AP Transactions Test 1: Identify invoice data that is possible circumvention of purchasing approval limits Item Description, Unit Price, Qty B14 – AP Invoice Split Review (Novice ACL skills required)

18. B15 – PFM AP Payments Test 1: Review payment activity pattern by payment counts and amounts B15 – AP Payment Activity Review (Novice ACL skills required)

19. B16 – PMM or PFM AP Invoices Suggest PO data instead Test 1: Review purchase detail by vendor and product code to assess purchase volume opportunities B16 – AP Vendor Purchase Review (Novice ACL skills required)

20. B18 – Star AR Clients Test 1: Review AR Clients to identify potential credit vulnerability based on credit limits and credit review dates. Test may need to be rewritten for healthcare environment B18 – Accounts Receivable Client Master Review (Novice ACL skills required)

21. B20 – Star AR Clients Test 1: Review AR clients to identify clients with different address, duplicate clients with same address & duplicate clients with same name. B20 – Accounts Receivable Duplicate Clients Review (Novice ACL skills required)

22. B02 – PMM and or PFM AP Invoices, AP Payments, & PO Transactions Test 1: Identify Invoices Where a discount appears to not be applied or applied incorrectly Test 3: Identify vendors who appear to increase purchase price PO Detail available Product No, Qty & Unit Price Test 2: Identify vendors who appear to increase purchase price No PO Detail available Product No, Qty & Unit Price B02 – AP Discounts & Prices Review (Intermediate ACL skills required)

23. B03 – PMM & PFM PMM AP Invoices & AP Payments Test 1: Identify Invoices Where a discount appears to not be applied or applied incorrectly Test 3: Identify vendors who appear to increase purchase price PO Detail available Test 2: Identify vendors who appear to increase purchase price No PO Detail available Test 4: Identify payments paid after due date B03 – AP Payment Review (Intermediate ACL skills required)

24. B05 – PMM or PFM PFM AP Invoices & AP Payments Test 1: Identifies inconsistent payment terms by vendor B05 – AP Payment Terms Review (Intermediate ACL skills required)

25. B11 – Lawson Travel & Entertainment Transactions Terminated Employees Test 1: identify possible duplicate claims w/same Emp ID, T&E code, date, amt & trip Test 3:Identify T&E amts for terminated Employees before the expense date Test 2: Identify amts just under approval thresholds B11 – Travel & Entertainment Claim Review (Intermediate ACL skills required)

26. B12 – PFM Vendor Banking Information AP EFT Payments Test 1: Identify EFT transactions with same bank info & different vendor code or duplicate transactions B12 – AP Electronic Funds Transfer Payment Review (Intermediate ACL skills required)

27. B17 – Lawson Payroll Payments Employees Test 1:Match payroll payments to Employees to identify payments to ghost Or terminated employees Test 2:Review payroll payments to identify employees receiving more that one per pay cycle B17 – Payroll Payments Review (Intermediate ACL skills required)

28. B19 – Star AR Clients AR Transactions Test 1: Review AR transactions to profile clients with a high number of adjustments or write-offs compared to their charges. B19 – Accounts Receivable Billing Review (Intermediate ACL skills required)

29. B06 – PFM & Lawson Vendors & Employees Test 1: Identify name match between employee And vendor tables Test 4: identify common matches for SSN & Tax ID Test 2: Identify common numeric address fields Numeric address plus postal code Test 5: Identify common matches for bank Account details • Test 3: Identify common alpha address fields Address, city, state, postal code B06 – Conflict of Interest Employee Vendor Review (Advanced ACL skills required)

30. B10 – Third Party data Charge Card Transactions, Holidays Prohibited Merchants Test 1: Identify questionable charges Made on non-business days. Weekends Or Holidays Test 2: Identify questionable charges Based on user defined key words, even dollar amts or for prohibited merchants B10 – Charge Card Questionable Charges Review (Advanced ACL skills required)

31. B21 – PMM Purchase Orders Canceled Sales Orders Canceled – Not applicable Test script may need to be rewritten for Healthcare environment Test 1: Monitor the volume of sales canceled & compare to purchase orders canceled during the same period to identify funds tied up in unnecessary inventory. B21 – Sales Management Sales Order & Purchase Order Review (Advanced ACL skills required)

32. Summary Analytics & Tests • ACL Novice Level • 11 Analytics consisting of 27 test scripts • Green light – 11 test scripts • Yellow light – 7 test scripts • Red light – 9 test scripts

33. Summary Analytics & Tests • ACL Intermediate Level • 7 Analytics consisting of 15 test scripts • Green light – 9 test scripts • Yellow light – 5 test scripts • Red light – 1 test scripts

34. Summary Analytics & Tests • ACL Advanced Level • 3 Analytics consisting of test scripts • Green light – none • Yellow light – 5 test scripts • Red light – 3 test scripts

35. Implementation Challenges • Major implementation challenges • Resource allocation • PMM & PFM perspective • PMM & PFM version upgrades underway (required for FRx) • FRx Financial Board level reporting must be replaced by January • PFM ERP Portal implementation – prefer with FRx • Lawson perspective • Implementation of Lawson Self Service • Hospital wide Job Description Assessment tool • Lawson & Kronos upgrades • Star / IT Development perspective • Relay Health project • All Scripts interfaces • Interface Engine Replacement

36. Timeline & Funding • Timeline • Contract & Purchase • FY 20XX • Implementation Timeline • Fluid timeline based on resource availability with current projects in process • Funding • Will hardware & software be purchased from the Corporate Compliance budget? • Vendor consulting services • Recommend a bank of ACL Consulting Services hours to be used as needed • Implementation & Script Redesign • Advanced analytics

37. Next Steps • Start contract negotiations if purchase is for next FY • Obtain server specifications for hardware purchase • Obtain project plan from vendor to customize to internal resource availability • Start discussions with Human Resources on confidentiality concerns • Start internal discussions on data concerns • Obtain Import File Specifications