Home

State Purchasing Card Audit Guide

Contents

1. gt L gt gt X X X 71583 Textbooks Adult Education 71584 Textbooks Special Education X 71585 Textbooks Kindergarten thru 5th Grade Includes Student and Teacher Editions Page 71 P Card Audit Guide Teacher Editions Teacher Editions Editions Prerecorded 71595 Recycled Publications and Audio Visual Materials X _ 85515 _ Costumes and Accessories O O O O O O O OOOO X f 85522 Curtins Cydoramas Draperies Drops etc x S53 DaceHoosPotabe Xx S59 lifes Orchestra Theater Pictarea SSS 85544 Lighting and Dimming Controls Autotransformer Dimmers gt Um eee Computer Microprocessor Type X 85548 Lighting Distribution Hardware Borderlight Cables Pipe Battens Plugging Strips and Boxes Wall and Floor Pockets etc X 85550 Lighting Instruments Border and Cyclorama Striplights Follow Spotlights Floodlights Special Effects Lighting Spotlights etc P x esss3 Lighting systems Complete x 856 Mkep x esses scenery and Pros Xx 85570 Seudtfecstqupment 85575 Stage Hardware and Supplies Braces Canvas Clamps Paints etc X 8557 Standards Portable and Ropes For Theater Lobbies x 8555 Stage Rigging and Tracks Xx 855 stages Theatre All Types x 85588 TumsiesThester x __ 85595 Recycled Theatrical Equipment Accessories and Supplies gt
2. aiaaaaaaaasaaasansnannnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnia 47 CONFIGURABLE REPORTS IN WORKS PAYMENT MANAGER nennen 49 UV E l r AA A 49 GRANA PO Dita Eb HH i a need 49 Page ii P Card Audit Guide Report Configuration Screen Sections 2iaaaaaaaaaaasanannnnnnnnnnnnnnnnnnnnnnnnnnnnninnnar 50 SCHEDULING BEPOR ESL ect dE Edad ER YER RG EE FERE ERE EIN REFER REF FERE EHI EROS PX FER ame 54 MEAN ICE nut cat TTTRRR 54 MANAGING BOOKMARKS Leste decks suce vins iutaXev erra Ernie E Gin wea suMbe S Orne bwbazeF eU ixc eli ecu tevisaV Odes rA nananana 56 USING EQUOTE FOR OPEN STATE ENTITY CONTRACTS eere nennen nennt nennen 57 VIR a E a E M a a a a EE 57 1 PURCHASING STAGE 1 NEEDS IDENTIFICATION cesssscsensecscennssssensnssnennnsnenensnsnnnensneennsssennunss 57 A PURCHASING STAGE 2 PRE SOLICITATION oorr aaro aaa a uuu aa aa uuu aaa aru aa aa ERR Ra EE RR RR ERE RR RR ERE 57 3 PURCHASING STAGE 3 SOLICITATION PREPARATION ooo rara aaa uu aaa uu aaa nn 58 4 PURCHASING STAGE 4 SOLICITATION PROCESS 11 oorr aaa aaa uuu aaa uuu aa aaa p ua aa RR RR n aa RR RR RR ERE 59 5 PURCHASING STAGE 5 EVALUATION PROCESS 1 1 oorr nau a oaa aa aa aua u uu aa aa aa aaa RR Rana aaa uuu H 60 6 PURCHASING STAGE 6 AWARD PROCESS 1 rare iara aaa aua u aa aa uuu aaa a au aa au ERR Rao R RR RR aa ERR R RR ERE 60 r
3. x sis ienConutimg gt X XX XX XX X Page 74 P Card Audit Guide 91878 Medical Consulting Select Exemptions NL LUN EDUCATIONAL SERVICES X 92478 Teaching and Instruction Services Including Elementary and Secondary Education Higher Education and Adult Education Select Exemptions maw ENGINEERING SERVICES PROFESSIONAL x 32586 Select Exemptions EQUIPMENT MAINTENANCE RECONDITIONING AND REPAIR SERVICES GENERAL EQUIPMENT 93680 Theatrical Equipment Maintenance and Repair Select Exemptions FINANCIAL SERVICES x 94615 Appraisal Services RealEstate SOS x 94630 tasSecutiesandBondingSenies Select Exemptions 948 HEALTH RELATED SERVICES FOR HUMAN SERVICES SEE CLASS 952 34864 X 94874 Professional Medical Services Including Physicians and All Specialties 94876 Psychologists Psychological Services Select Exemptions e HUMAN SERVICES 35221 Select Exemptions INSURANCE ALL TYPES Not subject to DOAS authority DCH handles health insurance and SPA is authorized to handle flexible benefits for state employees 95337 Disability X 95348 Health Hospitalization Including Dental and Visual Insurance 35363 Select Exemptions LIBRARY SERVICES SEE CLASS 908 FOR BOOKBINDING REBINDING AND REPAIRING 95635 Internet Database Subscription 95640 Magazine Subscriptions 95685 Training Material Subscription Select Exemptions MISCELLANEOUS PROFESSIONAL SERVICES
4. Any of the spreadsheet columns can also be put in the Data area The most common use is to present the total of an amount field such as transaction or purchase order amount To present the spreadsheet columns as rows or columns on the pivot table drag and drop the columns to the Row or i S bein es eval tex Lm Column areas of the design screen tothedagramontheleit The Page area would be a good place to put the Program Name field in order to be able to filter on one Agency or University at a time For Compliance audits the vendor name from the 19D query would be a good candidate for a Page Here we have put the cardholder name as the rows going down the Merchant Category Code as the column and the sum of the transaction amounts as the data The result will be the amount of spend each cardholder made at merchants assigned to those MCC s Step 5 Formatting the Numbers To format the Sum of Amount data field double Lakki click on the Sum of Amount button in the Data area circled in the previous diagram Source Field Amount Most of the time you will want to choose the Sum of the amount field There might be times when you want to count the occurrence of the amounts such as when you want to know how many transactions for 50 92 Number we have This is probably not very useful However you might want to count the number of transaction options gt
5. Select a table or range rr C Use an external data source Choose Gonnection zl Connection name Choose where you want the PivotTable report to be placed New Worksheet C Existing Worksheet Location Ex 12 Cancel 19 h 19 2124 o Yes ne gt P Card Audit Guide Step 3 Design the Layout of the Pivot Table This is the step where you determine what your pivot table will look like and how the data will be summarized The different areas of a pivot table are e Values fka Data this is the information that you are trying to summarize or measure or count e Report Filters fka Page a Report Filter is useful if you have a lot of data and you will want to look at only one main group at a time For example if you have 100 000 purchasing card transactions at 100 Agencies it might be easier to review one Agency at a time rather than all Agencies at once e Row the row determines what columns from the data will be in the left hand columns of the table You can have as many rows as you have columns in the spreadsheet of data e Column the column determines the names of the columns going across the pivot table The diagram to the right shows the relationship between rows and columns on the pivot table What was called Pages in Excel 2003 see purple box is now called Report Filters in Excel 2007 J Ta build a report choose theless tr
6. These exercises are going to use purchasing card transaction data Some Preliminaries Make sure your data spreadsheet has no gaps or spaces in it In order for Excel to properly identify the data source all columns must be contiguous For example you cannot create a pivot table from data in columns A D and columns L N The same holds true for rows you cannot create a pivot table from data in rows 1 10 and 20 30 All columns of the spreadsheet with the data must have column headings Excel will not recognize a column as being in the range if there is not one A good rule of thumb is that if you don t want to do a calculation on a column the data should be formatted as text even if it is a number For some Excel formulas to work correctly these fields must formatted as text Page 87 P Card Audit Guide Step 1 Initiate Pivot Table The examples transaction data used Click on Insert Pivot Table that follow use purchasing card The data tab of the workbook has the individual transactions You can click anywhere in the data range and Excel will identify the data to be id c OuRHIAGGBE Page Layout Formulas Data E Existing Connections Insert Er From Other Sources T Get External Data Home EP From Access E From Web Fe Fram Text Connections Properties Refresh Edit Links All Connections 4 CENTRAL GA TECH COLL RAL GA TECH COLL ep 2 Define Data Range amp
7. EMCC El MCC Description EJ Single Txn Limit z E Credit Limit E Number N AA N A NA at mods BARE sage sub e g ZZ Number Options gt gt The pivot table now displays the Sum of Amount the Count of Number and the Largest Transaction amount for each cardholder To view the details behind any number double click on the number Excel will display the individual lines in a new worksheet 4 ha s Pivot Table Extras Filtering a Pivot Table Ed Microsoft Excel Pivot Table Exercise xls is File Edit View Insert Format Tools Data Window Help E Cardholder and Cycle v EE Abraham Marilyn 2500 Data Sum of Amount 1674 74 Count of Number 9 Largest Transaction 851 57 7 Alexander Janice 1000 Sum of Amount Count of Number 4 Largest Transaction Allen Heather 5000 Sum of Amount 445 26 Count of Number 3 Largest Transaction 306 02 Allen Larry 2500 Sum of Amount 461 88 Count of Number 8 Largest Transaction 143 38 Alligood Jeffery 10000 Sum of Amount 105 39 Count of Number 5 Largest Transaction Sum of Amount l P Card Audit Guide AS R i S Coastal Drop down lists for the row values can be used to filter what is shown on the screen Click the drop down arrow and select the values to show To de select all click on the Show All box to remove all check marks Use the scroll bar to scroll through
8. The Works download file is a comma e DataFiles B X C Name 1 Navigate to 090 selme Date Moai TcsG One Year txt folder where data 6 663 KB Text Document TET file is stored 2 Change the file type to find to Text Files dnd ye 3 Click on the file name 4 Click the Open button L File name Files of type Text Files prn txt csv gem delimited text file This means that the file uses a comma to separate the data fields columns To open the text file open Excel Select Open from the menu 1 Navigate to the folder where your data file is 2 Select Text Files in the Files of type drop down list Note that Excel file type is the default You must change the file type in order to see your data file dd Click on the file name 4 Click Open The Import Text Wizard dialog box will walk you through opening the file Step 1 of 3 Click on the Delimited radio button and then Next Step 2 of 3 The Tast Wigand h r determined that your daba is Food Wicth F this ie correct choos Nast or cheeses the data type that best describes voor data Original dita hype that best Seszribes pour datar Chariebant nach act come r aber Ge puar ute ici Fidel Fikh ane Abyad im column vsith epus bebasan puc Fidel Start inpet at penn 2 reg ar cen unted sae a Pew Gh TECH COLL MIENS BARBARA 2504 209 00 04fo0 0007 OF OL 2007 z NL
9. 49508 Amphibia For Laboratory Research Preserved 49512 Animals Experimental Not Specially Bred 49513 Animals Experimental Specially Bred or Selected e g Albino Mice and Rats 49525 Biology Specimens and Cultures Except Microbial 49526 Biology Specimens and Cultures Including Microbial Complete Ecosystems etc 58025 Electronic Musical Instruments Electronic Organs Synthesizers Visualizers etc 58030 Metronomes and Tuners Conventional or Electronic Page 70 P Card Audit Guide 58034 58035 Music Sheet and Folio 58040 Musical Instrument Repair Parts and Supplies Except Organ and Piano 58055 Organs Conventional Accessories Parts and Supplies 58060 58070 58080 58085 59506 59507 59510 59515 59526 59528 Flowers Fresh Flowers for Teaching Purposes Commencement Exercises Banquets Conferences Workshops etc 59533 Grates Guards and Other Protective Devices For Trees and Shrubs 59535 Groundcovers and Vines Products 59540 Nursery Greenhouse and Floral Supplies Labels Planters Pots Tags Trellises etc 59550 59552 59555 59557 xi 5958 Pantse O x 59559 Pans Heb 59560 Plant Sprayers and Respirators Plant Propagation Mats etc and Accessories x 595 Ransindor x 59565 shrubbery Evergreen X 59566 shrubbery Flowering x 597 Trees FruitandNut 59575 trees Ornamental andShade
10. State Eligible MCCs are those that are in one of the four State authorized groups These groups are 1 GASTATE 2 GASTATE1 3 GASTATE2 and 4 GAATHTRAV The formula in this cell looks at the mccs range on the Lookups tab It then pulls either Yes or No based on what is in the third column of that range This column corresponds to the old MCC Analysis workbook It uses the formula VLOOKUP T3 mccs 3 e RedFlag MCC The formula in this cell looks at the mccs range on the Lookups tab It then pulls the value in the eighth column of that range There might be MCCs that are legitimate authorized MCCs but that for any number of reasons the user might want to review transactions at these merchants To customize this function go to column X of the Lookups tab Enter Yes on the row for that MCC This column uses the formula IF VLOOKUP T3 mccs 8 FALSE z Yes Yes No The remainder of the columns contains the transaction data downloaded from Works Payment Manager If you decide not to download one or more of those fields be sure to delete those columns from the spreadsheet The formulas above look at specific columns The formulas will automatically adjust to reference the new column For example if you delete column S the merchant s phone number then the MCC currently in column T will be in column S The VLOOKUP formulas will then look at column S for the MCC xempt 2 FALSE Page 34 Refre
11. X 50680 Sewage Collection Treatment and Disposal Architectural x 90582 solid wastes Disposal Systems Architectural Services x 730684 Telephone Systems Architectural 777 x 30688 Towers Architectural Services SSS x sesa Tunnels and Subways Architectural x 730692 Utilities Gas Steam Electric Architectural X 90693 Video Systems Design Architectural Services X 90654 Water Supply Treatment and Distribution Architectural _ Classified Computer 90735 Designing Services 90738 Drafting Services 90740 Engineering Services Non Licensed Not Otherwise SEEE Xx 3072 Geotechnical Sols X 3072 Safety Engineering and Accident Studies OSHA Studies X 730775 Site assessment and site Field Observation x S075 Surveying Services Not Aerial or Research x sores testingsenices SS gt X OK X lt lt X gt X XX XX XX X gt X _ 91502 Advertising Notice of Bid Solicitation X f arsos Advertising Public Relations x S104 f advertising Outdoor Billboard etc X Sisi Broadcasting Services Radio X 91571 Newspaper and Publication Advertising gt X X X x sso Accounting Auditing Budget Consulting x 91814 Appraisals Consulting x sis architectural Consulting x _ 91842 Engineering Consulting
12. numbers or purchase order numbers Count Mums To format the number e g currency with a S sign click on the Number button on the right side of the dialog box You can then format the data fields like you would any cells in Excel Page 80 Format Cells Click the OK button in the Format Cells dialog box when finished Then click the OK button on the PivotTable Field dialog box P Card Audit Guide ample G Program Card Name General Format cells have no specific number Format PivotTable and PivotChart Wizard Step 3 of 3 Click the Finish button on the Step 3 of 3 dialog box The pivot table wil be on a new spreadsheet in the workbook Up Step 6 Modifying the Pivot Table The pivot table will display on the screen From here you can remove columns or rows MCC column added in Step 4 has been removed You can also drag and drop other spreadsheet columns to rows or columns of the pivot table Abraham Marilyn 2500 B Alexander Janice 1000 7 Allen Heather 5000 8 Allen Larry 2500 9 Alligood Jeffery 10000 10 Anderson Donna 5000 11 Archie Herman 1000 12 Argo Stephanie 2500 13 Armistad Audrey 5000 14 Aultman Kevin 5000 115 Banks Tammie 5000 1B Banks Tanzy 5000 Barrs Holly 5000 In this spreadsheet the Number column represents the transaction number Drag the Number column from the Pivot Table Field List
13. Click Cancel to stop the download process c Clicking Run Report Again takes you back to the report configuration screen so that you can modify the report design or enter a different set of parameters for the same report Page 55 P Card Audit Guide MANAGING BOOKMARKS Bookmarks are the templates for the reports that you have created You can delete those that are no longer needed by going to Reports gt Bookmarks gt Management on the menu on the left hand side of the screen PS Scheduled Reports Completed Reports Click on Management to display the reports divided into sections for Personal Bookmarks those only you have access to and Shared Bookmarks those that multiple users have access to Only someone with administrator privileges can create a shared bookmark B a To delete a bookmark click on the box next to the gt eilling sag Ss Bookmark Name and then the Delete button in the lower right hand corner Make sure that you want to delete the bookmark because you will not be prompted to make sure once you click Delete T Tools If you need additional help with Works Payment Manager reports you can access the Works training information by clicking on Tools gt Training and Help gt Documentation to display the Works Payment Manager user manual This manual can also be downloaded and saved as a PDF file F Live Training Ll Release Notes R Tip of the Week Page 56 De
14. Location of Pivot Table Excel will identify the range of data on the spreadsheet Most of the time you will want to include all of the data in the pivot table However you can select the range manually and include just the contiguous columns and rows you want For example if you have data in A1 M2000 but you only want to use data in A1 D200 you can manually select that range However in our work it is highly unlikely that we would want to pivot just part of the data or data from multiple spreadsheets Click the radio button for either New Worksheet or Existing Worksheet The default setting is New Worksheet and you will use this option most of the time The other option is helpful if you want to 2 d 9 c 2 AHE ig Home Insert Page Layout Clip rana Smart TF Art T Tilustrations a from d sheet ct have multiple pivot tables in a single workbook either on multiple data tabs or on the same data Click OK Excel Extra To place the pivot table on an existing worksheet click on the radio button and the red arrow at the end of the Location box Navigate to the spreadsheet and the cell location not just to the particular spreadsheet where you want to put the table Hit Enter and Excel will take you back to this dialog box Click OK Page 88 I vi LY Create PivotTable T BE e 4 Other EE Choose the data that you want to analyze
15. Page 75 I P Card Audit Guide X 96147 Law Enforcement Services Including Process Server Services 96149 Legal Services Attorneys 6 etc ee a MEME Choice PX 96186 Veterinary Services 0 Veterinary Services 0 96248 Interior Design Decorator Services X 96357 Postage Related Purchases Incl Postage Stamps Pre Sort exes CEN 97165 Room Rental for Conferences Seminars etc X 98174 Theatrical Equipment and Supplies Including Costumes Rental or Lease 99844 X Direct Resale through a state Agency Institution operated service i e bookstore cafeteria etc K Teaching Forms and Publications for Testing X Contractors for services only to be provided by Non Profit Entities that comply with the requirements of O C G A 50 20 1 et seq Page 76 APPENDIX D EXCEL PIVOT TABLES EXCEL 2003 Pivot tables allow you to get different views of data through summarizing in two dimensions rows that go down and columns that go across Any or all of the columns in the original spreadsheet can be reflected as rows columns or data in the pivot table to present the information you want in summary form You are probably already familiar with what one looks like The table below shows summary sales data from multiple regions for multiple quarters 10 000 15 000 12 000 13 000 Midwestern 20 000 30 000 15 000 25 000 Region Southern 30 000 40 000 20 000 10 000 Region Western 2
16. Sorting a pivot table is not quite the same as sorting a regular gas denen a FJ kam 1 il Sort Pitter T Agen spreadsheet The table automatically sorts by the row values sku mem However we might want to view data in descending order by amount to see which vendor has the most spend To sort the pivot table by the amount click anywhere in the Total column Click the Data tab and Sort on the menu bar The pivot table Sort dialog box will appear Click on the Smallest to Largest or Largest to Smallest radio button Click OK to return to the pivot table The Row Labels will be h sorted in the selected order by amount The labels automatically mE ix SO rt corre ct y i Manual you can drag items to rearrange them Ascending 4 to Z by C Descending Z to A by Comp Supp Name You might also want to sort the pivot table by merchant name summary Click anywhere in the merchant name column and click Data gt etenim Sort A different dialog box will display Click on the radio button for either Ascending or Descending and select the field to sort Wore options cancel in the drop down list The choices will be the column that you have clicked in on the pivot table and the Value fields e g Sum of Amount C Manual you can drag items to rearrange them Ascending A to z by Comp Supp Mame Comp Supp Mame 55 d Sum of Amount Max of Amount Page 9
17. Analysis Once data has been copied to the template ensure that the Data Source for the pivot table includes all rows on the Data tab Configure the pivot table to have the following layout e Row Vendor name e Column Purchase Date e Data Sum of transaction amount Analysis On the pivot table replace the Cycle End column with the Purchase Date If any vendors have more than 5 000 in a single day determine if these transactions were e Froma contract either SWC or State Entity or e Theresult of a competitive bid process e fthere are any single transactions gt 5 000 at a non contract vendor determine if these purchases were bid e Obtain and review the documentation to determine the circumstances e Ensure that appropriate documentation is retained if it was an emergency purchase and that the Emergency Authorization was sent to the State Purchasing Division Page 23 P Card Audit Guide FINANCIAL SYSTEM PURCHASES BASIC EXCEL Run the financial system query that will provide the data shown below to Excel Name the spreadsheet Vendor PO Analysis See analysis steps below Run the PeopleSoft query OPOO19D PO LIST BY BU DETAIL State Accounting Office PeopleSoft users or equivalent query in non SAO PeopleSoft or non PeopleSoft financial systems that shows the following information Items in red font are required the other items are the ones that come in the standard 19D query You can delete those columns onc
18. Dashboard Click on Reports to display the list of report categories CxCo Dashboard T Tools 2 3 Click on Completed Reports to display all saved reports 4 The list of reports that have been run and are still in the Works system and available for download displays on the l o es Screen Scheduled Reporta Spend Reports 5 Click on the report name E Maa to highlight it Report name 1S Previous Month Select Enti E Previous Month Select Entity Previous Month Select Entity ME Previous Month Selected COSS Page 54 P Card Audit Guide 6 The Report Info screen will display at the bottom with information about the report and with three option buttons in the lower left 5 Reed e hand corner a Clicking the Add Another Format button allows you to select a different format e g PDF instead of Excel Works will run the report without any other input from you The report will show as Awaiting Processing and both output formats will display in the Requested Export Types column b Clicking the Download file format button will start the download process for the OSEE HENCE report You can either Open Save or Name Previous Month Selected bt 6 Type Text Document Cancel the operation Ete payer weaken Click Open to open the file and Q me _ save view on the screen Click Save to save the file to a network drive or the hard drive of your computer
19. Entity Second Contract AC Marketplace or eQuote es Home aspx Page 65 P Card Audit Guide Agreement between Governmental Entities No dollar Not Required limit Intergovernmental IGA Post justification with Notice of Intent to Award on GPR HFQ if Team Georgia Marketplace eQuote or RFP Unlimited DPA Requires Market Research Sole Source SS Sole Brand Specific Brand Product SB Doesn t alter the responsibility to comply with the Order of Precedence or the requirement Multiple Purchase Types MUL Multiple PO Type which may be used for the following combinations of purchases on a single PO to a single vendor OMP less than 5 000 SWC OMP less than 5 000 AC OMP less than 5 000 MAN to follow the competitive bidding process for Open Market purchases gt 5 000 Note RFQ Mandatory to be processed through AC OMP or SB that exceeds State Entity s DPA the eQuote System with unlimited DPA must be submitted to SPD for processing by submitting One Time Request to Exceed DPA form SPD NIO06 to SPD for approval RFP 250K DPA Unless other designation has been determined by the Assistant Commissioner of Procurement Page 66 P Card Audit Guide APPENDIX C NIGP EXEMPT LIST x tm Xx 0532 tte x oat ruts es Xx 01942 Fruts Deciduous
20. Entity s name The score will display on the second line and will increase as more questions are answered either Yes or Needs Improvement Questions that are answered No will receive zero points and the score will not change Column C of the spreadsheet has a drop down list from which you select the proper response to each question Does the Agency University have a policy and procedures manual governing how the program isto be administered on the local level Yes Needs Improvement ie date of the current policy withig Column D contains a formula that evaluates the response in Column C and assigns a numerical value Column E provides a place to enter comments Column F provides a place to enter the page number where the topic of the question is covered Page 2 P Card Audit Guide MODIFYING THE CHECKLIST Advanced users of Excel who are familiar with the IF Then statement concept can modify the checklist to change the grading and rating functions The following areas can be modified e The number of points for a Yes or Needs Improvement answer A No answer should always receive zero points e The score needed to achieve a low medium or high risk ranking e The score needed to achieve a grade of A B C D or F Modifying the Individual Question Point Values The formula that assigns points is shown below K dit WWorkboo IF C6 Yes 3 1F C6 No 0 1F C6 Needs Improvement 1
21. Not Required RFP RFQ i Team Georgia No dollar limit Mandatory Statewide Contract SWC State Entity can buy from GCI if same product is offered on Mandatory SWC otate Entity Contract renewals are allowed when eligible per terms of contract and entity follows renewal requirements Mandatory Source i No dollar Not Required Certified Products from MAN limit GCI and GEPS Mandatory GEPS products are identified on the State Use Procurement List located at http doas ga gov State Local SPD Contracts P ages Home aspx Convenience Fourth Optional No dolar Not Required Convenience Statewide Statewide Contract choose one from limit Contracts are available SWC this level as a convenience because prices and contract terms are finalized Preferred Products Fourth Optional Not Required Prices for certain through GEPS choose one from products are identified this level on the State Use Procurement List located at http doas ga gov State Local SPD Contracts P ages Home aspx Open Market Fourth Optional ithi RFQ if Team No competition required OMP choose one from iiid if 5K and can be this level i Marketplace used with the P Card eQuote or where applicable gt RFP 5K requires competitive bidding No State No dollar Not Required Refer to the NIGP EXM Requirements limit Exempt List located at http doas ga gov State Local SPD Policies Pag State
22. State Entity contract be beneficial Does another State Entity have a contract for these goods or services that we could piggyback from Pull purchase order information from the financial system see below and include these amounts for those vendors where there is both purchasing card and purchase order spend For advanced Excel users see the following pages on use of a pivot table to facilitate the review Page 22 P Card Audit Guide CYCLE SPEND ANALYSIS PIVOT TABLE ADVANCED EXCEL Use of a pivot table will make analyzing vendor spend much quicker since you will be able to display a list of vendor names and the total spend for each one without having to insert rows and use the SUM function Refer to the Appendices for pivot tables in Excel 2003 and Excel 2007 for step by step instructions on creating a pivot table Once data has been copied to the template ensure that the Data Source for the pivot table includes all rows on the Data tab Configure the pivot table to have the following layout e Row Vendor name e Column Cycle ending date e Data Sum of transaction amount Analysis Review the pivot table for vendors with unusual trends in dollar amounts Review the merchant names for suspicious merchants Double click on the dollar amount to copy these transactions to a new spreadsheet Rename the spreadsheet to the vendor name Obtain and review the documentation for the transactions Split Transactions
23. X X X X gt XX X X X gt XX XX XX X 90602 Acoustics Noise Abatement Architectural Services 90604 Agricultural Development Architectural Services 90606 Airports Lighting Fueling Navaids Architectural Services Page 72 P Card Audit Guide 90607 Architect Services Professional X 90608 Automation Controls Instrumentation Architectural Services X S081 buildings Architectural Design Xx 90612 Building Sanitation Architectural Services X 90614 Cemeteries Planning and Relocation Architectural Services _ X 30616 Chemical Processing and Storage Architectural x 730618 Cod Storage Refrigeration Fast Freeze Architectural x 90619 Concrete Architectural Services 90622 Corrosion Control Cathodic Protection Electrolysis Architectural X 3062 Desalinization Process and Facilities Architectural _ x 3095 Design BulldServices X 305826 Hectronics Architectural Services SS x 30627 Energy Management Architectural 90628 Energy Conservation New Energy Sources Solar etc Architectural Services X 3099 Environmental Architectural Xx 306030 Fire Protection Architectural Services 777o x 30632 Fisheries Fish Ladders Architectural x 9083 Fornsc Arhtetural SOS x 30634 FreightHanding Materials Handling Architectural x 30636 Gas Systems Propane Natural e
24. and use it As contract merchants are identified they must be added to the lookup range The State Purchasing Division Process Improvement Team will update the P Card Compliance Analysis Workbook on a monthly basis if any new merchants were identified You can also maintain the list on your version of the workbook If you identify a new SWC or State Entity contract merchant those merchant names should be entered on the list Go to the first empty row in Column B and enter the new vendor name and Yes in column C The formula on the Data tab will automatically reflect the new value You will need to refresh the pivot table in order for transactions at the merchant to be excluded Excluded Excluded Merchant Answer 47132 FLETCHER OIL C HARRISON TIRE COMPANY FLETCHER OIL CO INC HIGHLAND TIRE AND AUTO PHOENIX OIL CO New vendor name here F 179 180 182 Level III Merchants columns E F The Process Improvement Team has already identified several merchants that transmit this SST level of detail These are merchants are already LLL L on the Lookups tab You can determine if a merchant transmits Level III line item detail by 1800GOFEDEX 10010007 creating a report in Works Payment Manager AIMPRINT that pulls data fields from the Transaction 2614 RAPID Details section There will be one line for each ne e ING line on the invoice or receipt When you find a new one simply add the merchant name to the list as yo
25. area You will now see another Sum of Amount shown as Sum of Amount2 This kind of summary doesn t make much sense but you might want to see the largest transaction amount or the smallest transaction amount To change the summarize option for the new amount field of the Sum of Amount2 click on the Field Settings button on the Pivot Table tool bar second button from the right If you don t like the default names these can be changed in the PivotTable Field dialog box Change the Max of Amount2 to Largest Transaction here Click on the Max option in the Summarize by area Click on the Number button to format the number as Currency and click OK Notice that you can also do average amount and smallest min amount Click OK on the PivotTable Field dialog box Page 82 Ed Microsoft Excel Pivot Table Exercise xls EA File Edit View Insert Format Tools Data Window Help 128 pee a DUUS Drop Page Fields Here Cardholder and Cycle v Data 4 Abraham Marilyn 2500 Count of Number 9 2 Count of Number E Count of Number Count of Number 4 445 26 3 451 88 8 105 39 Ea 6 Ed E 10 Bx an 13 Count of Number nn Ne 115 Count of Number 16 17 18 E 20 21 22 23 5 598 80 3 255 06 3 Archie Herman 1000 Sum of Amount lf Count of Number Count of Number Count of Number Sum of Am
26. bid conference If so should supplier attendance be mandatory How much time should be permitted for supplier questions How will pricing be requested There are three common approaches for requesting pricing and all three approaches require a core items list representative market basket The first approach is to send the core item list as part of the solicitation and have offerors bid on the specific items referenced on the core items list The second approach is to request the offerors provide their catalogs and provide a discount off their catalog price The third approach is to standardize on and reference a specific manufacturer s catalog and request offerors bid a discount off the catalog price There are strengths and weaknesses for each approach that need to be explored by the Page 58 P Card Audit Guide sourcing team prior to selecting an approach Additionally the sourcing team must decide if the core items will be identified by line item within eQuote OR if the APO CUPO will attach an Excel sheet with the list of the core items e Note SPD will update eQuote training and the eQuote manual to address these options e Note Items requiring particular branding will require Sole Brand Justification SPD NIOO3b What other documents will be included with the RFQ For example for all contracts with an estimated value in excess of 100 000 the APO CUPO must request verification from the Department of Revenue that the supplier
27. cardholder should attach the statement from that company showing all shipments and the amounts for each individual delivery Conference Registrations e The Department of Audits and Accounts does not consider conferences registration fees to be employee travel Therefore cardholders can use the card for registration fees It is up to the agency or institution to prohibit this type of transaction The documentation must include e Copy of the registration showing who is attending e Copy of the agenda or program Airline Tickets e Acopy ofthe itinerary showing the name of the employees flying and the purpose of the trip must support charges for airline tickets If the agency pays for non employee flights then the documentation must include an explanation for the charge e The cost of shuttles to and from the hotel and the airport are employee travel expenses and cannot be put on the purchasing card Employees must pay these charges and receive reimbursement on a travel reimbursement form Group Meals The State Accounting Office issued its Employee Group Meals Policy on October 1 2006 Refer to the Statewide Travel Regulations on their website Meals that meet the requirements for group meals must have the following documentation in addition to the invoice from the caterer or restaurant providing the meals e Prior written approval from the agency or department head e Roster signed by all employees participating in the event e Agenda o
28. e Compare individual cardholder average cycle spend to the cycle spend for the profile Ifthe cardholder consistently reaches the cycle limit and needs temporary increases this might be justification for moving the cardholder to the next higher profile It could also mean that the cardholder performs actual job responsibilities not in keeping with the job title Ifa cardholder consistently uses a lower percentage of the cycle limit this might indicate that the limit is too high or that the cardholder is not using the card when possible e Calculate total spend by cardholder Use the sort feature in Excel to sort these by job title and by amount Evaluate the types of job titles found in each natural grouping Dojobtitles appear in more than one group Are all cardholders with the same or similar job titles found in the same natural group Sortthe data by job title and then by total spend Determine the maximum transaction amount and maximum cycle amount within each job title These figures can provide insight into appropriate limits for the profile that will include that job title e Compare individual cardholder s maximum and average transaction amount and maximum and average cycle spend to the assigned profile Doesthe cardholder consistently reach the STL or cycle limit Doesthe cardholder consistently fall significantly short of the STL or cycle limit for the profile Determine the cardholder with the highest c
29. end users project facilitator etc APO CUPO facilitates meetings by the sourcing team to prepare for the solicitation preparation stage Review existing solicitations contracts etc to identify suppliers with whom the State Entity has conducted business and also analyze the market to identify additional sources of supply market dynamics and industry standards i e minimal warranty terms etc Discussion Analysis of the market and sources of supply provides critical information to the sourcing team including understanding how items may typically be bundled or sold Page 57 P Card Audit Guide which will assist the source team in organizing the solicitation For example if all suppliers provide all of the needed items then the sourcing team may consider making a single award However if the market analysis reveals there are suppliers that provide some but not all items then the needed items may be best organized into categories or subcategories with split awards Establish a core item list for current needs and possible future needs The core item list contains the essential items expected to be purchased by the State Entity as a whole Determine what other items in addition to the core items list should be included in the Open State Entity Contract For example are there value added items such as extended warranties or additional services which should be included Is it desirable to establish a minimum percentage discount off
30. for the Report Filters Column Labels and Row Labels areas can be used to filter what is shown on the screen just like using filters on a standard spreadsheet Click the drop down arrow and select the values to show Note that you can also begin a sort on the column from this option To apply custom filters click on the Label Filters bar and then use one of the custom options This will save time if you have a long list of values and you know that a lot of them begin with the same letter or word For example you can select Begins With and enter the letter B to see all values in the Row Label that begin with the letter B ram Card Name icomp sup sortAtoz Ej SortZtoA ual Filters gram Card Mar THE HOME TUBETAP BUMPE STAPLI IDEC More Sort Options Clear Filter From Program Card Label Filters Value Filters v Select All Z CENTRAL GA TECH COLL V GA ATHENS TECH COLL c Ereg am End Mame X Comp Supp Name L GA ATLANTA TECH COLL SCR l Sortatoz LOWES 00546 v GA AUGUSTA TECH INST I 41 SartztoA GLOBAL PARTS ai Siento Fick a a of Amaza L K Clear Filter From Program Card Name eee a meeenbabech Filta IK Clear Filter Cancel CNT G Value Filters k Equals SKILLP NATION 7L Select All a Does Not Equal LEES P 0C blank CENTRAL GA TECH COLL GA ALBANY TECH COLLEGE siaa GA ATHENS TE
31. for the price increase or decrease Non Inventoriable Assets State Entities must record any single piece of equipment with a cost greater than 1 000 for agencies or 3 000 for colleges and universities in asset inventory State policy prohibits use of the purchasing card for the purchase of any asset that must be included in the asset inventory The State Purchasing Card Policy allows the purchase of equipment under these thresholds and recommends that entities also maintain any easily misappropriated equipment such as digital cameras in the asset inventory Documentation for this type of purchase must include e Location of the items purchased e Asset tag decal number if not indicated on the invoice auditors will request to verify the existence and location of the asset Page 47 Department of Administrative Services State Purchasing Division Process Improvement 5 CONFIGURABLE REPORTS IN WORKS PAYMENT Georgia MANAGER OVERVIEW Works Payment Manager Works the on line card management system provided by Bank of America uses reports to download transaction data for analysis with either Microsoft Excel or Microsoft Access or to a PDF document for reports that will be printed For most analyses a State Entity will want to do the data can be downloaded to Excel However if the number of rows exceeds the row limit in older versions of Excel 65 535 then the transactions must be downloaded to a text file or
32. i It uses an Excel IF formula In this example e Ifthe answer is Yes the question receives three points e Ifthe answer is No the question receives zero points e Ifthe answer is Needs Improvement the question receives one point To modify the formula simply change the numbers Remember however that the total score must equal 100 points If you add a point to the Yes answer then you need to either subtract one point from the score of Needs Improvement in the formula for that cell or subtract one point from another question s Yes or Needs Improvement formula Modifying the Overall Points Grade and Rating The scores are maintained on the risk levels for scores tab to the right of the Checklist tab The illustration to the right shows that a policy that receives a score of 83 or above can be either an A or a B grade and will be a Low risk level meaning that there is a low risk that the policy does not provide adequate guidance A policy that receives 82 would be a B score with a Medium risk level There are similar cutoffs for C D and F scores and for High risk bloo co co co eo oo eo es t CO V cm e c uou Page 3 P Card Audit Guide The VLOOKUP formula in cell E2 evaluates the score in cell D2 and assigns the risk level for that score from the risk levels for scores tab A similar VLOOKUP formula in cell F2 pulls the letter grade associated with
33. i lect len theist TS AMERICAN TYPE CULT sana Iter I e Select every value in tne IIS S MAGNA PUBLICATION V Select Multiple Items Mt except the No to determine merchants ce that have not been previously excluded AND Eae a that have the same MCC as an excluded merchant These transactions could be cases where the cardholder could have or should have purchased the items from a DIE unm Ea Excl Merchant contract vendor or from a mandatory 2 Exempt MCC 3 Purchase Day source 4 Contract MCC US Ye a 2 7 Page 38 To see these transactions double click on the dollar value P Card Audit Guide The transactions will be copied to a new spreadsheet in the workbook Review the documentation to determine if the items purchased were available on a contract Select No from both the Excl Merchant and the Exempt MCC Report Filters The pivot table will now reflect transactions at merchants that have not been identified as contract merchants AND where the MCC is not exempt You can also select the known contract MCCs from the Contract MCC Report Filter as described in the previous activity Review these transactions to determine if the cardholder s should have used contract merchants Remember that even if a purchase is exempt from State procurement regulations the cardholder must still follow the Order of Precedence and use contract vendors Split Purchases Add the Purchase Date field to the pi
34. is not a prohibited source Therefore in this example the APO CUPO must attach the Sales and Use Tax Compliance form n addition to identifying necessary forms the APO CUPO must attach an approved contract which identifies any renewal options and how those renewal options will be utilized Note SPD must provide prior written approval in the event the State Entity desires to establish a contract which will exceed five 5 years 3 2 The APO CUPO must finalize the evaluation process with the sourcing team For example the APO CUPO and the sourcing team must understand that any requirements of the RFQ must be evaluated on a pass fail basis and as a result any supplier incapable of meeting the identified requirements will be disqualified as nonresponsive By discussing this with the sourcing team the APO CUPO can confirm whether certain identified requirements are actually desirable needed With respect to the pricing evaluation the APO CUPO and the sourcing team should determine whether certain items will include estimated quantities for the purpose of evaluation Utilizing estimated quantities will allow certain products to have greater weighting for purposes of determining the supplier providing the best pricing For example if the sourcing team determines certain items will be purchased with much greater frequency than other items weighting the more popular items with a greater quantity will provide a better analysis of which supplier will p
35. is the Monday of the week during which the transaction took place It uses this formula ZVLOOKUP WEEKNUM L3 weeks 2 FALSE The VLOOKUP formula looks at the week numbers shown in the weeks range on the Lookups tab This table needs to be updated annually as explained below e Excl Merchant Excluded Merchants are those merchants that have already been identified as Statewide Contract or State Entity contract merchants The formula in this cell looks at the excluded range on the Lookups tab If it finds the merchant name it puts a Yes in the cell if not it puts a No When if you find a contract vendor that has not been excluded simply copy the vendor name to the last row of this range The formula on the data tab will automatically change The same field on the pivot table will update when you refresh the pivot table It uses this formula IF ISNA VLOOKUP N3 excluded 2 FALSE TRUE No VLOOKUP N3 exclude d 2 FALSE e Exempt MCC 1 Exempt MCCs are those MCCs that are probably equivalent to one of the exempt NIGP codes listed on the State Purchasing Division web site The formula in this cell looks at the exempt range on the Lookups tab If it finds the MCC it puts a Yes in the cell if not it puts a No It uses this formula IF ISNA VLOOKUP2 T3 exempt 2 FALSE ZTRUE No Yes e Contract MCC Contract MCCs are MCCs that are associated with known Statewide Contract merchants The formula in
36. of the supplier s catalog 2 2 APO CUPO should determine best sourcing method based on items to be sourced The remainder of this document has been developed based on the assumption that the RFQ is the desired sourcing method and that the APO CUPO will utilize eQuote to conduct the RFQ PURCHASING STAGE 3 SOLICITATION PREPARATION 34 To fully develop the RFQ the sourcing team must address several key questions in conjunction with the APO CUPO What are the bid factors For example will the RFQ request references or minimum experience Is there any required certification which the supplier must possess What are the key requirements and terms For example are there certain minimum performance levels which must be met by the supplier such as delivery or response times How will the State Entity address requests for price increases The RFQ contract must stipulate that cost will remain fixed throughout the duration of the contract In addition the RFQ contract must state that no price increases will occur unless it is approved in writing by the State Entity The APO CUPO may wish to include a price escalation clause which establishes the basis for determining whether a price increase may be granted for example whether there is an increase in pricing according to a designated product index as well as the limits to the number of price increases which may occur as well as the amount Should the APO CUPO schedule a site visit and or pre
37. on the Specify Sort Directions button to a k neis reveal the sort fields and click the radio button R ee A Txn Number Ascending Descending for either Ascending or Descending Step 4 Add General and Column Filters Any field regardless of whether or not it is included in the field Item Description list for the report can be used as tor Gl Conbiaion x ltem Number a filter A filter is where you tell tem Price Works Payment Manager to limit abu NR the transactions it pulls based on EE o Transaction Type x v Cash advance certain parameters such as Puce between certain dates or for a 7 Misc Debit 2 m IV Purchase specific cardholder or merchant m v Payment 1 Click on the drop down Last 4 Digits x seccy mp2 arrow to scroll the fields Mee a Eom og Click on the desired field name to display it in the filter list 2 The filter list displays below the IE drop down list When Works runs P e the report only transactions that au meet ALL of the criteria will be p e x peel pulled In the example above only C Cyde to date Selected cycle transactions on the card that ends x jets R in the Last 4 Digits and are on the 2 EREE past 6__ days o s e e s pojaaje Post Date shown will be retrieved 3 Click on the x button next to a filter to remove it Page 52 P Card Audit Guide Specifying parameters Some fi
38. the Refresh button ue eeu TT All E All E All E All v vx MCC MCC Description 7 blank blank 5169 CHEMICALS T Change PivatTable Data Source Choose the data that vou want to analyze Select a table or range DataltAtz f C Use an external data source Table Range Ghoose Gonnpection Connection name Page 35 P Card Audit Guide Pivot Table Design The pivot table uses Report Filters known as Pages in Excel 2003 to isolate transactions based on specific criteria When the filters are set to pull all transactions as shown by the All in column B see diagram then all transactions are included in the pivot table When the values in the filters are changed transactions that meet all criteria will be included in the pivot table data The transactions are still on the Data tab They just aren t included in the values reflected on the pivot table Users who are comfortable with pivot tables can change the design to suit their needs It is recommended though that you keep a master copy of the P Card Compliance Workbook with any Lookups tab customizations that you might have done Definitions For more information see Getting to Know the Data Tab above and Maintaining the Lookups Tab below e Excl Merchant This term refers to merchants that have been previously identified as contract merchants such as Staples or W W Grainger This filter corresponds to Co
39. this cell looks at the contract range on the Lookups tab If it finds the MCC it puts a Yes in the cell if not it puts a NO It uses this formula IF ISNA VLOOKUP T3 contract 2 FALSE TRUE No VLOOKUP T3 contract 1 FALSE e Level Ill Level IIl refers to a list of merchants that are known to transmit Level Ill detail information As more are identified the user can copy these names to the A NOTE ABOUT MERCHANT CATEGORY CODES If you exported transactions from Works Payment Manager to an Excel spreadsheet then Excel assumed that the MCC will be used as a number As a result it removes the leading zero in the four codes that begin with zero 0701 0742 0763 and 0780 You will need to go back and designate the column as text and then manually add the zero to the beginning of those codes in any transactions where they occur A NOTE ABOUT THE VLOOKUP FUNCTION This function will only work if it is looking up a value in a cell formatted as text Page 33 P Card Audit Guide level3 range on the Lookups tab The formula in this cell looks at the level3 range on the Lookups tab The formula on the data tab will automatically change The same field on the pivot table will update when you refresh the pivot table If the formula finds the merchant name it puts a Yes in the cell if not it puts a No It uses this formula IF ISNA VLOOKUP N3 level3 2 FALSE TRUE No Yes e State Eligible
40. to multiple Excel files Both types of files can be used in Excel or in Access Works allows the user to select which data fields to include in a report or download This is done in the Configurable Reports section C https payment works com CREATING A REPORT Bankof America gt After logging into Works Tasks u E Administration 1 Click on Reports to display the list of report Van ELI options BERLIN Reports Bookmarks Dashboard CxCo Dashboard 3 Report categories are shown in the R e it ools illustration to the right Transaction Po downloads are done in the Spend Reports section 2 Click on Reports to display the list of report categories Audit Reports CxCo Audit Reports Card Reports CxCo Card Reports 4 Click on Spend Reports open e CxCo Spend Reports Scheduled Reports Completed Reports Page 49 P Card Audit Guide Works has certain standard reports that can be run as is These reports can also be configured by adding and removing data fields In order to configure a report the user has to start with one of the existing reports or templates e Click on the arrow in the Report Template drop down list Choose a Spend Reports Template e To download transactions start with the Billing Statement report by clicking MEM LI select a report template Please select a re on it i Biling Statement gt Uisoutec N c
41. v The Paper Size Orientation Add summary data to ool Add summary data to the header the header options are only available for PDF documents Page 50 P Card Audit Guide If you select Delimited Text you have the option to select a delimiter e g comma what to do Export as PDF Excel V Delimited Text if the selected delimiter occurs in Delimiter comma v When delimiter occurs in text Quote entire field X Newline style Unix style LF v Include headers V the field value itself and to include the field names as column headings on the first row of the file This is helpful if the text file will eventually be opened with Excel Step 3 Configure Report Columns This is the section where you tell Works Payment Manager which data fields you want to include in the report The Included Columns area displays the fields currently Debit on the report These fields can be moved up or down deleted or edited by clicking on the buttons to the right of the listing The field can be moved to the Sort Columns list by clicking the down arrow button beneath the listing The Sort Columns list allows you to move fields up and down to change the sort order or to delete the fields from the sort order using the same type of buttons as the Included Columns listing The Available Columns listing categorizes available data fields based on the type of data The categories that
42. when condition is true No Format Set Format Click on Cell Value Is in the first drop down list This is the default value Click on a criteria in the second drop down list depending on what values you want to display In this example we are looking for values greater than a certain dollar amount Enter a value in the third drop down list In this example we want to filter on all cells where the value is greater than 5 000 Click the Format button to open the Format Cells C nll dialog box This is the same format box as you Condition Turati would see for regular cell formatting Select how you want to display the cells In this example we timate Wewwmeme 0 eromese want to see the cells highlighted with pink Click on er m the pink box lower left hand corner duce p m SSOWERANSWFO cyst pie Ho Cor INA TECCETT Click OK on both the Format Cells dialog box and the E X B UDOB L Conditional Formatting dialog box oe Bn ENENH Cells matching the criteria you entered will be displayed in pink 5104 04 57 59 64 51 917 37 Page 84 P Card Audit Guide Removing Subtotals Ed Microsoft Excel Four Day Spend 08 08 08 xls File Edit View Insert Format Tools Data Window Help Whenever you put in two or more spreadsheet columns in the row Ogee Rai Sle area the p ivot table Will tta Ma a ad Q B 5 78 Pe R
43. 5
44. 5 000 15 000 30 000 25 000 Region 85 000 00 100 000 00 77 000 00 73 000 00 Behind all of these summary numbers lie the thousands of individual transactions that make up those amounts Instead of manually adding up all of those individual transaction amounts Excel can do it for you These exercises are going to use transaction data from the purchasing card and from the PeopleSoft 19D query Some Preliminaries Make sure your data spreadsheet has no gaps or spaces in it In order for Excel to properly identify the data source all columns must be contiguous For example you cannot create a pivot table from data in columns A D and columns L N The same holds true for rows you cannot create a pivot table from data in rows 1 10 and 20 30 All columns of the spreadsheet with the data must have column headings Excel will not recognize a column as being in the range if there is not one A good rule of thumb is that if you don t want to do a calculation on a column the data should be formatted as text even if it is a number For some Excel formulas to work correctly these fields must formatted as text Page 77 P Card Audit Guide Step 1 Initiate Pivot Table The data tab of the workbook has the individual transactions Click on Data PivotTable and PivotChart Report Step 2 Define Type of Pivot Table You can create a pivot table from external data sources if you want or from multiple spreadsheets Yo
45. A PURCHASING STAGE 7 CONTRACT PROCESS error ara aaa a uuu aa aa uuu aa aa RR aa RR ERR Ra EE RR RR ERR RR RR ERE 60 APPENDICES RR 61 APPENDIX A STATE USE PROCUREMENT LIST 11 rare oaa aaa uu aaa u ua aaa uua aa au aaa uuu u aa ERR a R RR Ra RR RR R45 62 APPENDIX B HOW TO PURCHASE ietizebekiast bre Ese MEEFRREEKTEUEXVE sedate 65 APPENDIX C NIGP EXEMPT LIST secesssssessncessavsnsecossrsvcssescssnnessrscsecrsnccsesocssenassescsessecsersenesssosessessssss 67 APPENDIX D EXCEL PIVOT TABLES EXCEL 2003 ssssssssssssssssessnsssnsenssnsssnsenssnsssnsenssnssnsssnsennsnnsenss 77 APPENDIX E EXCEL PIVOT TABLES EXCEL 2007 iiec eint tante puo oat usn etra reo tonat utra 87 Page iii Department of Administrative Services State Purchasing Division Process Improvement EVALUATING THE PURCHASING CARD POLICY Georgia OVERVIEW The purpose of the policy evaluation is to determine if the policy provides adequate guidance to card program administrators supervisors cardholders and other employees affected by the purchasing card e g employees for whom cardholders make purchases The Policy Evaluation Checklist is an Excel spreadsheet that rates the policy as a high medium or low risk that the policy does not provide adequate guidance The rating is based on the answers to questions about the policy The answers can be Yes Needs Improvement or No The evaluat
46. CH COLL USLANTA TECH COLL _ tog ome Show items For which Ehe label H begins with z Use to represent any single character Use to represent any series of characters Page 93 P Card Audit Guide Applying Conditional Formatting You can apply conditional formatting to a cell or range of cells just like you k od rmas Formatting ai Table would in a standard spreadsheet Click on the cell or the range of cells you aa uiu wish to apply conditional formatting to On the Home tab click on Conditional Formatting The Conditional Formatting drop down menu will appear In Excel 2003 you can have up to three conditional formats However in Excel 2007 you can apply more than this Format Cell ing as Table v Styles al Highlight Cells Rules gt E Top Bottom Rules Select Manage Rules Clear Rules f Manage Rules 19 00 hy a W M T Select New Rule K Conditional Formatting Rules Manager Show fomating rules for EGRESS Click on Format only cells that contain and 1 Ef New Rule SP Edit Rule X Delete Rule enter the criteria and desired format in the dialog box Click OK when finished In this example cells containing a value greater than 5 000 will be in red font disabled Options New rormatnorue A 8 211 Seni gt Format all cells based on their
47. EFH LER FE FEX IFR CERE CHE FO RR FREE 21 LESS S LIOS LR r a m 22 P Card Audit Guide CYCLE SPEND ANALYSIS PIVOT TABLE ADVANCED EXCEL 1 1 rrr error rnnt 23 Split Trdnssctioris ATdlVSIS cesena RE EO a Pope Eu M A UREP GUI REOS 23 FINANCIAL SYSTEM PURCHASES BASIC EXCEL 1 1 11 erre rrr rara rear aan nana aua a naa n aaa uana an nass 24 P CARD COMPLIANCE ANALYSIS WORKBOOK ADVANCED EXCEL 27 FAIT 5 oir Tn TA 27 DOWNLOADING TRANSACTIONS FROM WORKS PAYMENT MANAGER ee nn nn nnn nuu 28 FINE Rz OUIT REED TRU a a j 28 Copy Transaction Data to the P Card Compliance Workbook 31 Getting to Know the Data Tab a2aaaaaaaaaaaa aa navanavnasanannnanannnnnnnnnnnnnnnnnnnnnannna 33 Refresh the Pivot Tab Ci ices sactesanatircouaceeasentectend satetoauadatnousaasianeeadeeesentessseadeseneniee 35 ife PACS OI ceria saan E A 36 VS TNIV VS se cnsete ast tency ease sneneie ottitinn tequn E 36 MODIFYING THE PIVOT TABLE DESIGN vicicisexsciscstsessniecsuneciinininedinaniucrsivcnenvucnstieanautencs 37 SUCCESTLDANALT entertainer ite netic 38 Open Market Purchases cccccscccssecccsececseccenessenseeneeeeeceencseeeseeeeseeeeeteuseeeeseees 38 SIT PUCAO E nee ee a ee ee eee ee 39 Weekend Purchases aaaaaaaaaaaansansnnsnnsnnnnnnnnnnnnnnnnnnnnnnnnnnnnsnnnnnnnnnnnnnnnnnnnnnnnnninnnar 39 Merchant U
48. GA TECH COLL TUEHS BAFRARER 2504 7297 90 pied aad osrorrzooa Specify that the mark used to define the fields is the Comma You can leave the Tab box checked as well Click the Next button Step 3 of 3 Page 30 Treat consecutive ud Text qualifier r Other L preview S P Card Audit Guide Use the scroll bar to review the fields Notice that the wizard gives a preview of what the data will look like The default data format is General which means that Excel will import text as text numbers as Text Import Wizard Step 3 of 3 This screen lets you select each column and set the Data Format numbers and dates as dates The rule of thumb for non date fields is 3 des A aa ae a A A Date MDY v dvanced if you are not going to do Prete iene em calculations on the field or the field is not a date then you should specify Text as the field type e nthe Works file there are some fields that contain numbers last four digits of the card and the Merchant Category Code that you will want to specify as text fields There are also other fields such as the merchant phone number zip code or address that will sometimes have just numbers instead of alpha characters Designate these fields as text To do this click on the field and then click on the Text radio button e Import the Transaction Date and Post Date columns as Date e Import the Transaction Amount STL and C
49. Purchasing Card Program m With Ease State Purchasing Card Audit Guide Georgia Department of boue ervic es nance Driven Maggie Clarke Process Improvement Audit Supervisor 404 656 5907 Maggie Clarke doas ga gov Copyright 2010 Department of Administrative Services Department of Administrative Services State Purchasing Division Process Improvement CONTENTS EVALUATING THE PURCHASING CARD POLICY vscssccssccscsssecssccssussecnsecsenssencesseussenssussenssennsussennsess 1 OVIN EPR Se M dM MEME MM REMIT M MM M IE 1 USING THE LHECKLIS PRO 2 MODIFYING THE CHECKLIST av nnn rnn nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn annan nna ana a 3 EVALUATING CARD PROGRAM TRAINING saunaaaaaaan aaaaaa nnnnnnnnnnnnnnnnnnnnnnnnnnnnnunnnnnnnnnnnanannnnnannnnannnaa 5 DHT e ha ee I eo DIE dM OR REET MM E 5 PROCUREMENT TRAINING INFORMATION vassccnsnccensccennccsennnsnenncsenenssenensnancnsnenensenenssenensnansnsennnnsenenssnanss 5 CARDHOLDER AND SUPERVISOR TRAINING CRITERIA suscsscscnsnccencnceenccsenncnsenenssenecsenenssenenssenecnsenesssnanss 6 EVALUATING SPENDING LIMIT Snassssesosshassiannnannkanubarnianukannkanubann anaba skasu a skana anakasabanukasa anukninin 9 PROPIE BEST CLE LA o ttt ani a 9 Limit the Number of Profiles aaaaaaaaaasasasanannuna annnanananannnnnnannnnnnnnnnannnananinnina 9 Base Profiles on Job Titles a aaaaaaasaaassasasasanannnna
50. RBARA BAILEY STREET BURNS BARBARA Amazon com ADAMS DAWN C ADAMS TRACEY ADKINS KENNY Akins Harold ALLEN REBECCA ashworth Dennis t RRETT JEANNIE sford Amory SOUTIER PATRICK Lamm ape Fm Blank rows in column A indicate multiple cardholders with the same vendor Page 39 P Card Audit Guide card the abuse will probably not be at contract or exempt MCCs simply because of the nature of the transactions However you might want to investigate some contract transactions such as automotive parts or office supplies Merchant Utilization There are times when you might want to see if there is a merchant that only one cardholder is using It is more likely that any personal use will be at merchants that other cardholders do not use To determine if there are any merchants that fit this category configure the pivot table so that the merchant name is the first Row Label and the cardholder name is the second Row Label Keep in mind that if only one cardholder uses a merchant this does not indicate fraudulent use The cardholder s job description work unit and other factors determine which merchants a cardholder uses Moreover it is possible for a cardholder to make personal purchases at merchants that everybody uses such as Staples or Home Depot Combined P Card and Financial System Spend Periodically you should download purchase order spend and vouchers without purchase orders from
51. Red Flag 2 Purchase Day Begin Date Excl Merchant Exempt MCC Contract MCC Level lll Eligible MCC CH User Name Wednesday 04 26 2009 No No No 0 EU Thursday 04 26 2009 No No No BURNS BARBARA Wednesday 04 26 2009 No No No BURNS BARBARA iclas C a S T rhon S RN gee ARS When the data is pasted into the spreadsheet the formulas in columns A through H will calculate To verify that all rows of data are complete click on cell 3 and then click End and the Down Arrow key to go to the last row of data Ensure that the formulas are on each row of data If not go up to the last row that has the formulas and copy columns A through H down to all data rows If there are rows with formulas but no transactions those rows can be deleted All rows beyond this point can be ZOR OA deleted N A N A w EP a O lh tO andi dos For more information on the formulas and the lookup tables see Maintaining the Lookups Tab below Page 32 P Card Audit Guide Getting to Know the Data Tab The Data tab contains more than just your transaction data Formulas in Columns A through H display information based on the values shown in the columns of the spreadsheet e Purchase Day This is the day of the week that the purchase was made on based on the Purchase Date column It uses this formula 4 LOOKU P WEEKDAY L3 1 2 3 4 5 6 7 Su nday Monday Tuesday We dnesday Thursday Friday Saturday e Begin Date This
52. T OF ECONOMIC DEVLPIMNT BAJA 671 008 FBBEN ALISON DEPT OF ECONOMIC DEVLPRANT EET B11 2008 HAMPTON KAREN H DEPT OF ECONOMIC D VLPNINT BATTA EUER HUNT Py FF DY ID IPLA ILA t Tool Example xls Text to Columns Consolidate Group and Outline e 1 2008 CAIN an 1 2005 CLARK 8 7 2008 Bm 12008 6 6 2008 8 11 2008 MCCORKLE Pv PivotTable and PiyotChart Wizard Step 1 of 3 Pi Pl Ph Pi Phy We is EE daa Da you m to pei E End data source C Multiple consolidation ranges C Another PivotTable report or PivotChart report What kind of report do you want to create PivotTable C PivotChart report with PivotTable report MINNI D P Card Audit Guide Step 3 Define Data Range Excel will identify the range of data on the spreadsheet Most of the time you will want to include all of the data in the pivot table However you can select the range manually and include just the contiguous columns and rows you want For example if you have data in A1 M2000 but you only want to use data in A1 D200 you can manually select that range However in our work it is highly unlikely that we would want to pivot just part of the data or data from multiple spreadsheets Accept the data range that Excel chose by clicking the Next button Step 4 Design the Layout of the Pivot Table This is the step where you determine what your pivot table will look like and how the data will be summarized The differe
53. TINZATION cesekesenntkr eti WATER Ex RENT ERO x rac E n ur cue ku paa ubt v ua ux 40 Combined P Card and Financial System Spend 2aa2aaaaaaaaa aassssssassssnansnanina 40 SAMPLE CRITERIA PIVOT TABLE Gercenseti nniesznutctevericden imb enc AU 41 MAINTAINING THE EOORUPS TAB S assercosiexk ba evi bai F cei la Db rcd R 42 Excluded Merchant columns A C aaaiiiaaaa aaaa aanasa unnnnnnnnnnnnnnnnnnnnnnnnnnnnnsnnnnnnsnnnnnia 42 Level II Merchants columns E F eeeeeeeernnnnnennnnnenne nnns 42 Weeks coumis oss Du soos ipsom vobi E a kai 42 State Eligible MCCs columns P W aaaaaaaaaaaanaaaaannnnnnnnnnnnnnnnnnnnnnnnnsnnnnnnsnnnnnisnnnnaa 43 Red Flag MCCS columtis O eX assssseteesvo Een vane Repo SERERE XA ava aa Stara E UM DH 43 Exempt MCCs columns Z AA aaiaaaaaaaaaaaaannannnnnnnun n nannnnnnnnnnnnnnnnnnnnnnnnnnnnsnnnnnnsnnnnnina 43 Contract MCCs columns AC AD eese ennemis 44 D ido EE M NLE MN sd oS 125 m 45 PURCHASES OF SUPPLIES AND MATERIALS niemexissn v xet EaiE i dabpi rudi Ew VERE Aa ER Rn OVE ea abr ado 45 Specialized Transactions aaaaaaaaaasaaasananannnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnanina 45 DTHERTPBEER a AE NONE UK UPEC Torna 47 Payment from Statements a aaaaaaasaassassanananannnnnnsnnnnnnnnnnnnnnnnnnnnnnnnnnnnannnnannnna 47 Recurring Monthly Charges Services eese 47 Non Inventoriable AssetsS
54. Tree xi esso foss xi oss melons Xx 0956 ese Xx 060 Potatoes sh Xx ose Pumpkins x 0566 ieS Xx oa sm Xx o soybeans Xx 01578 sugarcane and sugarbeets Xx os tobacco Xx 090 Vegetables x 0 994 Whest SSS X 04001 Amphibia Frogs Toads Salamanders and other Cold LOI Bloodied Vertebrates Live Xx 9405 X 94003 Animal Care Supplies Collars Clothing Leashes etc __ _ X 9400 Animal Training Equipment and Supplies X 04007 04008 Cages Shelters Animal and Marine Life See Class 495 for Laboratory Type x 999 cats alltypes Pets X 10 Cattle Beef Breeding Stock Xx won Cattle Beef Commercial x wo Swe oiy x on Gm Xx 02 ts Meat Type Baby S S SO x ooa Chicks Meat Type Starter SOS Xx oos Coker Al types Baby x oos Cockerels AlTypesStater O O O Xx 00 DesAltpes O Xx oos earthworms Ht gt XX 2 X lt lt lt lt Xx X lt X lt gt X Page 67 P Card Audit Guide X 04037 Electronic Identification Systems Used for Inventory and 7 It cingotanmas RRRR Px 04040 Fish Brood and Fingering X 0402 Fish Not Otherwise Specified xi 0945 fshWopkd X 04048 Fur Bearing Animals Not Otherwise Classified 04050 X 0408 O Hibernation Boxe
55. al spend and spending limits Once the data is copied onto the Data tab you will refresh the pivot table In Excel 2003 select Data Refresh Data from the menu bar In Excel 2007 click on the Data tab and click Refresh All The pivot table will do more than one calculation on the transaction amount only if there are three or fewer row fields If you are not interested in the card number for your analysis you can leave it off the pivot table If you leave it off you can display both the Cycle Limit and the Single Transaction Limit at the same time However if you want to see it for the either analysis you will need to have just the Cycle Limit field as a row or just the STL field as a row To load the transaction data into the spreadsheet e Open the Works Payment Manager download file as described above e Click anywhere in the download spreadsheet e Click CTRL A to highlight all data e Click CTRL C to copy all data to the Windows clipboard e Click on Cell B3 on the Data spreadsheet e Click CTRL V to paste the data into the Data tab spreadsheet Page 20 Department of Administrative Services State Purchasing Division Process Improvement EVALUATING VENDOR SPEND LEVELS Georgia State purchasing regulations as found in the Official Code of Georgia Annotated require competitive bids for any purchases anticipated to be 5 000 or more The 5 000 competitive bid requirement is based on purchase s and is interprete
56. and the STL is in cell B5 the formula would read 2C5 B5 2 Name the column range with the percentages as perstl 3 Enter the following text and formulas in any three columns to the right of the 96 of STL column Column letters and row numbers are shown for illustration Page 18 P Card Audit Guide purposes only Your columns and rows might be different and you will need to use your column letters and row numbers instead of these if they are different Note that The numbers shown in the formula have decimal points They must be put in exactly as shown The greater than less than symbols and the number must be enclosed in quotation marks as shown of Cards in 3 Range in column AC RE E av G3 COUNTIF perstl lt 01 G4 F3 COUNTIF perstl 1 G5 G4 COUNTIF perstl 2 G6 G5 COUNTIF perstl 5 G7 COUNTIF perstl gt 5 15 amp mA soO 6 eajaooo SB 5000 Fs aei P QUU SPENDING LIMITS ANALYSIS USING THE TEMPLATE BASIC INTERMEDIATE EXCEL If you want review individual cardholders quickly or if you are not comfortable with Excel pivot tables the Process Improvement team has developed an Excel template to calculate Total Spend and Average Transaction Amount to compare to Cycle Limit and Single Transaction Limit respectively You will need the following data elements in the order on the spreadsheet from Works Payment Manager e Program Card Name This colu
57. are most useful for the State of Georgia are e Allocation information on how a transaction was allocated in the General Ledger e Card information about the cardholder s card e Card Profile spending limits for the profile to which the cardholder is assigned e Cardholder information about the cardholder The most useful field here are the CH User Name e Cardholder Group the work unit or other grouping to which the cardholder is assigned e Company Supplier information about the merchant e Orig inal Transaction basic information about the transaction e Program Card information about the State Entity s card program e Transaction additional information about the transaction e Transaction Detail information about Level III detail when available Page 51 P Card Audit Guide To select a field to include in the report 1 Click on the field name CH Payee ID CH Phone 1 2 Click the arrow button to move it to the CH Phone 2 Included Columns listing Grp Create Date Use the up and down arrow buttons shown above Da to arrange the columns in the desired order Use the down arrow button under the field listing to indicate the fields to be used f or sorting Arrange the columns in the desired sort order using the up and down arrow buttons Click on a field name and then the X button to delete a field from the sort layout Insert page breaks for summary data to no page breaks Click
58. ase order spend to the purchasing card in order to increase purchasing efficiency Refer to the self audit tool Evaluating Vendor Spend for suggestions on incorporating purchase order spend and purchasing card spend into one analysis SINGLE TRANSACTION LiMiT STL ANALYSIS WITH PIE CHART ADVANCED EXCEL The Single Transaction Limit Analysis should be done using two different calculations on the transaction amount field The instructions below use the average transaction amount to compare to Single Transaction Limits However since the STL also needs to accommodate the highest transaction amount in a given time period the analysis should be done using the highest transaction amount Construct second pivot table Single Transaction Limit Analysis e Row Labels Cardholder name Card Single Transaction Limit e Column Labels None e Values Largest Max of transaction amount This will display the largest transaction amount Calculate the average transaction amount as a percent of the Single Transaction Limit See illustration below On row four of the spreadsheet with the pivot table label the column to the right of the Grand Total as of STL 1 Format the column as percentage with two decimal places Enter a formula on the next row in the same column as the largest transaction amount divided by the Single Transaction Limit For example if the average transaction amount created in the previous step is in cell C5
59. ce or receipt must contain However all invoices should contain basic information about the transaction These are Vendor or merchant name Transaction date Line item information Quantity Description Unit Price Line Price quantity times unit price Total invoice amount Attestation of receipt of goods or services Ifthe documentation is a cash register receipt then the cardholder s signature should be on it Ifthe items were shipped the receiving employee should sign the packing slip and the invoice if it comes in the shipment and send to the documentation to the cardholder The cardholder should keep the original signed packing slip and invoice with the transaction log or monthly billing statement Specialized Transactions In addition to the above information good accounting practices dictate that certain transactions have the following documentation Subscriptions Newspapers magazines Length of subscription or renewal Name of periodical Name of employee for whom periodical was ordered Delivery address for the periodical Cardholder should sign original order or renewal form Page 45 P Card Audit Guide Organizational Memberships e Name of employee for whom the membership is paid e Length of the membership period Freight Charges when billed by a third party e Origination point e Destination point e Nature of items being shipped e For payments to shippers such as FedEx and UPS the
60. columns from the spreadsheet prior to using If you add any fields from Works Payment Page 28 P Card Audit Guide Manager then a corresponding column must be inserted into the appropriate place on the Data tab of the workbook The corresponding column letters are shown next to the field name in the list below The layout in Works Payment Manager is shown below and in the screen shots to the right Refer to the section in this document entitled Configurable Reports in Works Payment Manager for more details on how to run configurable reports e CH User Name e Last 4 Digits e Amount e Purchase Date e Post Date e Comp Supp Name e Comp Supp Street 1 e Comp Supp City e Comp Supp State e Comp Supp Zip e Comp Supp Phone e MCC e MCC Description e Grp Group Name e Single Txn Limit e Credit Limit Cash advance Under Transaction Type ensure that Reimbursement and Payment are not checked Page 29 P Card Audit Guide Exporting to Excel If you know that you have fewer than 65 535 rows then you can export the transactions from Works directly into Excel You can then skip to the section entitled Copy Transaction Data to the P Card Compliance Workbook below Exporting to a Text File If you know that you will have more than 65 535 rows then you will need to export the data to a text file and then open with Excel Excel 2003 though will only handle 65 535 rows Excel 2007 can handle more
61. corner as shown in the diagram to the right Enterthe text and formulas in any three columns to the right Column letters and row numbers are shown for illustration purposes only You columns and rows might be different and you will need to use your column letters and row numbers instead of these if they are different Note that e The numbers shown in the formulas in Column AC have decimal points They must be put in exactly as shown e The greater than less than symbols and the number must be enclosed in of Cards in Range in quotation marks column AC AC5 COUNTIF percycle lt 01 AC6 AB5 COUNTIF percycle lt 1 AC7 AC6 COUNTIF percycle 2 AC8 AC7 COUNTIF percycle lt 5 ACS COUNTIF percycle 5 Page 16 P Card Audit Guide The results will appear as shown in the diagram to the right Create the graph or chart using the data shown in the orange box to show the number of cards in each range and the percentage of the total End number of cards for each range See next page 4 in Range for a three dimensional pie chart example Cycle Limit Utilization u 8 13 E 2 3 m lt 1 m1 10 m 10 20 H 20 50 H 15 25 E gt 50 HE 13 2196 Analysis Thoughts There are no industry benchmarks so it is up to management to determine the level of risk that they are comfortable with As you can see from this illustration only 1396 of cardholders had an average cycle
62. ctly in Excel Excel will assume that the field should be numeric The leading zeroes will be removed The MCC 0742 for example would display in Excel as 742 e The MCC field is used in VLOOKUP formulas The formula will only work if the field is a text field It will not work with numeric fields You can download the transactions for any time period However it is recommended that you download based on your cycle end date For example if your cycle ends on the 15 of the month you would want to download from the 14 of one month to the 15 of the next month This will enable you to more accurately determine if cardholder cycle limit levels are set appropriately for the cardholders needs It is recommended that you configure and bookmark a report in Works Payment Manager so that the report does not have to be re created each time Open either the Billing Statement or the Spend by MCC report to get started Add delete and re arrange fields as needed For assistance with configuring a report in Works Payment Manager click on the Tools link on the left hand side menu and select Training Navigate to the Quick Reference Guide entitled Creating Configurable Reports Refer to the chapter Configurable Reports in Works Payment Manager for additional information File Layout The workbook uses the following data fields The fields in red font are required If any of the others are not downloaded from Works the user must remove those
63. d as the total of a single purchase either purchase order or purchasing card transaction or combination of both A single purchase is defined as any purchase of like or similar items where the need is foreseeable or known at the time of the purchase Using the purchasing card as a method of payment does not relieve the State Entity or cardholders from following competitive bidding procedures when foreseeable and or known purchases are determined to be 5 000 or more Refer to the Georgia Procurement Manual for additional information The APO CUPO is responsible for including purchasing card transactions in the quarterly spend evaluation required by the Georgia Procurement Manual This exercise explained below allows the user to Quickly identify vendors with more than 5 000 in transactions for any one day regardless of the number of cardholders in order to determine if cardholders are splitting purchases to circumvent bid requirements e Assist in the quarterly vendor spend analysis to identify areas where State Entity contracts could result in cost savings COMPILING TRANSACTION DATA Works Payment Manager allows the user to download transaction information into an Excel spreadsheet However if the user expects that there will be more than 65 535 rows the transactions must be downloaded to a text file and then opened with Excel The following data fields should be exported using a custom report If analyzing for the same time peri
64. d h t HK File Edit View Insert Format Tools Data Window Help same as sorting a regular spreadsheet insuBaieliv amp iibm cio c amp r The table automatically sorts by the row fia ta fa ad G 0 PO M Wo renly with Changes End Review s values However we might want to view our data in descending order by amount OE to see which vendor has the most spend Drop Page Fields Here un RR 3 Total Spend fC 4 Supp Name vw Total 1 800 GOT JUNK B A 1 LOCK AND SECURITY 7 A 1 VACUUM CLEANER 8 A AND A CHEMICAL PRODU 9 A LOCKSMITH amp SECURITY 10 A W SMITH SPORTING GO E FIRE EXTINGUISHE Sort To sort the pivot table by the amount click To sort automatically every time the report is anywhere in the Total column Click updated use the Sort and Top 10 command on Data Sort on the menu bar The pivot table the PivotTable menu Sort dialog box will appear Sort by Click on the Value radio button if not already kegs N Ascending clicked and choose Descending Descending Click OK to return to the pivot table The values Sar as will be sorted in descending order by amount Values Labels The labels automatically sort correctly Summary r Sort Supp Mame in ascending order by Total Spend Ed Microsoft Excel Pivot Table Exercise xls File Edit Vi I ol f Data Window Help u E fe Eg Aer Sl o a
65. e in either ascending or descending order and then by transaction amount in descending order Scroll through the cardholders and compare the largest transaction amount to the Single Transaction Limit CYCLE LIMIT ANALYSIS WITH PIE CHART ADVANCED EXCEL Construct first pivot table Cycle Limit Analysis Create a pivot table with the following configuration H escription e Row Labels Ta 1 Cardholder name Drag fields between areas below Card Cycle Limit Status e Column Labels Cycle Ending Date e Values Sum of transaction amount Page 14 P Card Audit Guide Calculated Columns On the same line as the column headings row four of the spreadsheet label the six columns to the right of the data with the names shown in a f below Put the formulas below in each column on the first row beneath the column names fifth row of the spreadsheet Once all formulas are entered on the first row copy them down for all rows e of Positive Cycles This step will calculate the number of cycles during which a cardholder had net positive activity The formula for positive spend is COUNTIF range 0 Range is all of the cycle ending columns excluding Grand Total for the row For example if the cycle ending columns are column D through column O and the Grand Total is shown in column P then the range for the first row will be D5 P5 The formula would read COUNTIF D5 P5 gt 0 e of Negative Cycle
66. e purchases will fall into two categories 1 those at exempt MCCs NIGPs and 2 those at MCCs that are not exempt a Excl Merchant Exempt MCC ATT Purchase Day All e Select No from the Excl Merchant Report Filter The pivot table will reflect the merchant name and the total dollar amount of transactions at merchants that have not been previously identified as a Statewide or State Entity contract or as mandatory source regardless of the NIGP If you have customized the merchant list to include your State Entity merchants these will be excluded as well e Perform any or all of the following steps Each activity is designed to be done separately not at the same time e famerchant has more than 5 000 in spend during the cycle pull all transactions with that merchant from Works Payment Manager for the previous six months to determine if this is an opportunity for a State Entity contract If you have access to queries in PeopleSoft you can also pull spend there to determine if between the Purchase Day all two methods of procurement P Card and Snc Al a f l OA A purchase order there is enough spend to ee Em pd justify the effort to create a contract CompsuppName 7 e Select No from the Excl Merchant SDISPLAYS2GO 9739 SDISTINCTIVE DESIGNS 715599 Report Filter and the Merchant Category SUMMER CLASSICS IN pase Codes shown in the Contract MCC Report gsun Filter
67. e the same for both versions of Excel Page 31 P Card Audit Guide e Open the data download spreadsheet if not already open Delete the first row with the column headings If you do not delete it you can delete it once you copy the data to the workbook f you downloaded reimbursements and payments to the bank with your transactions delete these rows as well e Click anywhere in the data download spreadsheet e Click CTRL A to highlight all of the transaction data NOTE Do not simply highlight the entire spreadsheet If you do the data will not fit on the P Card Compliance Analysis Workbook s Data tab when you try to paste it into cell I3 e Click CTRL C or use any other copying method that you are familiar with to copy the highlighted area to the Windows clipboard e Navigate to the P Card Compliance Analysis Workbook and click on cell I3 See diagrams below e Click CTRL V or use any other paste method that you are familiar with to paste the data into the P Card Compliance Analysis Workbook a RENE RENT RN E F G H Do not delete or overwrite columns A thru H Copy data from downloag State Red Flag Purchase Day Begin Date Excl Merchant Exempt MCC Contract MCC Level Ill Eligible MCC CH UserName N A No No No No N A N A N A No N A N A P MP WERDE WALT EET Un r z he Do not delete or overwrite columns A thru H Copy data from dowr State
68. e you download the query to Excel if desired e Unit Agency code e Origin e PO Number e Change Order e PO Date Type e PO Reference e Status e Budget Status e Vendor this field is the 10 digit vendor ID e Name vendor name e Vendor Classification e Payment Terms e Buyer Phone e Line ft e 5 Digit NIGP e 10 Digit NIGP e Description e Line Amount e Build a pivot table with the following layout e Rows e Name vendor name e Buyer Phone e Data Sum of Line Amount format as currency Analysis e Using the filter box on the Buyer Phone field select all of the buyers who are also cardholders Page 24 P Card Audit Guide e Review for vendors that also accept the P card e Focus on vendors shown on the listing of Statewide Contracts and Agency contracts Make a list of the purchase orders Buyer name Vendor name PO number PO date Total dollar amount o 9o o o e Determine if P card spending limits both Single Transaction and Cycle Limit for the buyers could have accommodated using the P card instead of a purchase order for the purchases e Determine if other non cardholder buyers are using vendors that accept the purchasing card e Evaluate to determine if it would be beneficial to issue purchasing cards to these buyers e Encourage buyers with cards to use the card instead of a purchase order for all vendors that accept cards Page 25 Department of Administrative Service
69. either natural or man made disasters anywhere in the State In these situations cardholders might need more flexibility in how much they can spend and where they can use the cards The DOT might send road crews from South Georgia to North Georgia to assist when there is a major winter event Road crew foremen might need to pay for lodging and meals for the South Georgia crews Once the emergency is over the Card Program Administrator can move the cardholders back to the standard profile Data Analysis for Profile Evaluation In order to perform a spend analysis on profiles the Card Program Administrator needs the following information for at least 12 cycles It is highly recommended that you evaluate your profiles annually for the first three years of your program and at least every 18 months thereafter You can use either Microsoft Excel or Microsoft Access to work with the data Transaction data and profile information are available through Works Payment Manager e Cardholder name e Cardholder job title not in Works Payment Manager e Current spending limits e Single Transaction STL e Cycle e Hoftransactions per day e Individual transaction dollar amounts Page 11 P Card Audit Guide e Individual transaction post date The following list of analysis techniques can help you analyze your data to determine appropriate single transaction and cycle limits Number of transactions per day is up to the discretion of management
70. elds used as filters have a button to click in order to enter the parameters In the illustration above clicking on the Date button next to Post Date displays the Select Dates dialog box Select from any of the pre designed parameters or manually enter the dates in mm dd yyyy format Click Finish to return to the report design screen Step 5 Bookmarking Bookmarking is the Works word for saving the report template Bookmark this report template 1 Click on the checkbox next to t name My Statement Bookmark this report template Description State for my purchasing card 2 Enter a Bookmark name report name and Description for the report 3 Make sure that the Personal radio button is selected Only Works administrators can create Company reports Company reports can be seen by all Works users at your Entity Step 6 Scheduling amp Expiration Reports can be run Now or at a later specified time Reports can also be scheduled to run on a recurring schedule e g every Monday a morning at 7 00 Works will also keep a copy Job name Biling Statement Lie of the report for a specified number of days Create reports for employees i Evzicyess unsp 1 Specify the Job Name This can be the pus C Later unspecified C e Recuning unspecified 3 Keep the generated report for name of the report However if you are running several versions of the report e g multiple cardholde
71. ent e Cardholder responsibilities including formal cardholder agreement 12 What materials does management provide to cardholders and supervisors during training for them to keep as references Check all that apply L State Purchasing Card Policy Agency Institution Policy L Written procedures describe L Supply of Sales and Use Tax Exemption certificates _ Other describe 13 Does the Entity require cardholders to demonstrate an adequate knowledge of State and Entity policy prior to issuance of the card e g written test 14 Does the Entity require supervisors to demonstrate an adequate knowledge of State and Entity policy prior to providing supervisory review of transactions e g written test Page 7 P Card Audit Guide Page 8 Department of Administrative Services State Purchasing Division Process Improvement EVALUATING SPENDING LIMITS Georgia The goal in evaluating cardholder spending limits is to determine if the profile groups you have established provide the cardholders with the credit limits needed to do their job without exposing your Entity to unnecessary risk By assigning cardholders to profile groups you can assign the same spending limits to multiple cardholders without manually setting the same limits for each cardholder individually There are two required spending limits 1 the Single Transaction Limit STL and 2 the cycle limit also known as the c
72. ep a ek s Refresh Pivot Table a Click anywhere on the pivot table b On the Pivot Table Options tab click the green refresh button a vl a Column A Filter on No to exclude merchants not already identified as contract or mandatory b Column B Filter on No to exclude MCC s not associated with an exempt NIGP c Column D Double click on dollar amount for the No value in column C to see transactions that are gt Notat excluded merchants gt Notat exempt merchants gt Notat a possible contract MCC a Column A Filter on No to exclude merchants not already identified as contract or mandatory b Column B Filter on No to exclude MCC s not associated with an exempt NIGP c Column C Filter on all values except No and Blank i e filter on all MCC s shown d Column D Double click on dollar amount to see transactions that are gt Notat excluded merchants gt Notat exempt merchants gt Are at a possible contract MCC 1 P Page 41 P Card Audit Guide MAINTAINING THE LOOKUPS TAB The Lookups tab contains all of the ranges of data used in the VLOOKUP formulas on the Data tab Some of these ranges can be customized to fit your needs Excluded Merchant columns A C Merchant names do not come across from the Bank in the same format as the vendor name in the PeopleSoft financial system As a result you cannot simply put all of the contract merchant names on a spreadsheet
73. eply with changes End Review id Napa eer inel CERAM al i automatically insert totals for edt row mienne nt Program Card Name r ENTRAL GA TECH COLL yy Supp Nam 1 v AAA LOCK amp SAFE CO 33 04 ACT PROGRAMS 2 300 00 Amazon com BUMPER TO BUMPER CNT GA TCH MILLEDGEVILLE DMI DELL HIGHER EDUC FOX APPLIANCE PARTS OF LOWES 00546 MEDIBAG CO 84 80 13 58 Sometimes this is helpful 21 97 sometimes it is not especially when 58 48 468 00 MICHAELS 1686 154 37 you have two or more Spreadsheet R amp S INDUSTRIAL SUPPLY 400 00 STAPLES 449 82 columns represented as rows To ULTIMATE OFFICE 02 82 WW GRAINGER 938 eliminate the subtotals double click 2Z04DARTON COLLEGE A j on the section that has the subtotals In this illustration that would be the Card Program Name row section PiyotTable Field Mame Program Card Name To remove the subtotals click on the None radio button and click ahaoe a OK f Automatic tees mB o wr Hi ev o Brum wf M g Tere eh Deo ES ATIS PE fH fs eum ss K Prasrars Cand Name This looks neater especially if the subtotals for the agency are not that important i L 1 DTC COLLEGE Page 85 P Card Audit Guide Sorting a Pivot Table Sorting a pivot table is not quite the Ed Microsoft Excel Pivot Table Exercise xls ti
74. following fields to a comma delimited text file from Works Payment Manager using a custom report e Cardholder User Name e Last Four Digits of the card number e Amount e Purchase Date e Post Date e Merchant Vendor Name e Merchant City e Merchant State e MCC e MCC Description e Card Profile Name e Group Name cardholder group name e Credit Limit Page 13 P Card Audit Guide e Single Transaction Limit Open the text file in Excel Designate all fields except the following as text data type By keeping the General data type the amount will be a number not currency though and the dates will display as dates e Amount e Purchase Date e Post Date Load all transactions into a new Excel workbook NOTE Illustrations in this document are from Excel 2007 If using 2003 the user will have to follow steps for that version to place the fields BASIC EXCEL SKILLS Basic Cycle Limit Evaluation Once the transaction data has been downloaded into an Excel spreadsheet sort the spreadsheet by cardholder name in either ascending or descending order and then by transaction amount in ascending or descending order Insert two rows between each cardholder section Use the SUM function to determine the total spend for the cycle Compare this to the cardholder s cycle limit Basic Single Transaction Limit Evaluation Once the transaction data has been downloaded into an Excel spreadsheet sort the spreadsheet by cardholder nam
75. he merchant name Comp Supp Name Click the box next to the cardholder name CH User Name You will want to have the cardholder name in the first column of the pivot table column A Simply drag and drop the field name to the top See diagram below You can also show the merchant name along with the cardholder either as the first column or as the second column Manipulate the pivot table to determine the best way to meet your review objectives E PivotTable Field List Choose fields to add to report Purchase Day Begin Date v Excl Merchant v Exempt MCC v Contract MCC Level III Purchase Date Post Date Hi imp Supp tres Comp Supp City Ll comp Supp State LI Comp Supp ZIP LI Camp Supp Phone r Page 37 P Card Audit Guide SUGGESTED ANALYSIS STEPS Using the filters and reconfiguring the pivot table allows you to select transactions for review based on specific criteria The suggestions for use in this user s guide are not the only possibilities but are intended to get you started As you become more familiar with the pivot table and the various formulas used to provide more information about the transactions you can reconfigure the pivot table in multiple ways to fine tune your review Open Market Purchases Transactions at vendors that have not been previously identified as contract or mandatory source merchants would be considered open market purchases Thes
76. her Procurement personnel attended the Introduction to Purchasing Card Principles training If so attach an additional sheet showing names and titles Have any other Procurement personnel attended the How to Conduct a Self Audit P card training If so attach an additional sheet showing names and titles Have any cardholders attended the Introduction to Purchasing Card Principles training Have any cardholders supervisors attended the Introduction to Purchasing Card Principles training Does management communicate changes in State or Entity policies and procedures to cardholders and supervisors If so how is this done Does the Entity require cardholders and supervisors to attend training prior to issuance of the card How is this training conducted select one e Group class e One on one e On line tutorial e Other explain 8 Explain Other method of training 9 Doesthe Entity offer refresher training at least annually Page 6 P Card Audit Guide Yes No Needs Improvement Training Need 10 Does management maintain records of who has attended training including refresher training 11 Are the following areas covered in the training materials e Allowed purchases e Prohibited purchases e Spending limits including STL and cycle e How to complete the transaction log or approved alternative e Tax exemption certificates and procedures e How to review monthly billing statem
77. highlighted and circled in this screen shot to the Total column Heime PAMELA SI 20 Benjamin Brandy 2500 until the Total column with the Re ee eee Ania 2000 i Bing Alicia M 10000 values is outlined with a broken lliscat Michelle 500 li Bohannon April 2500 Ine brantley dianne 1000 Branyan Gail 5000 Bray Natasha 5000 Brinkley Michelle 10000 29 29 5ORD pF dA q tions 18 BECK PAMELA SUE 5000 Where do you want to put the PivotTable report New worksheet C Existing worksheet Click Finish to create your PivotTable report Cancel lt Back Ed Microsoft Excel Pivot Table Exercise xls HO File Edit view Insert Format Tools Data Window Help PivotTable v 3 fg 95 t E x B PivotTable Field List X Drag items to the PivotTable report E Month mE Amount P Supp Name Pe E Supp City P amp Supp State m B mcc El MCC Description Ej Single Txn Limit Page 81 P Card Audit Guide The pivot table now displays the Count of Number The table will automatically display the data fields the way it thinks you want to Spreadsheet columns that are formatted as numbers or currency will automatically be inserted in the data area as a sum of the amount Text fields will automatically be inserted as count of the values If any of this is not correct you can change it Now drag and drop the amount column into the data
78. his kind of summary doesn t make much sense but you might want to see the largest transaction amount or the smallest transaction amount To change the summarize option for the additional amount field click on the Max option in the Summarize by area Click on the Number button to format the number as Currency and click OK Notice that you can also do average amount and smallest min amount You will now see the Total Amount of spend at the merchant as well as the largest transaction at the IG Source Mame Amount same merchant Custom Mame max of Amount If you don t like the default names these can be Summarize by show values as Summarize value field by ch a nged i n t h P ivotTa b e F i e d d la og box Choose the type of calculation that you want to use to summarize the data from selected field Change the Max of Amount to Largest Transaction here In this example the total spend and the largest transaction were the same for Marvair QPS so there must have been just one transaction However the total spend at Sherwin Williams was 377 77 with the largest transaction being 176 69 To view the details behind any number double click MARVAIR QPS 49 47 49 47 ai on the number Excel will SHERWIN WILLIAMS 377 77 176 69 7 display the individual lines qu 7 9p PARTS 50 73 em in a new worksheet z Page 92 P Card Audit Guide Pivot Table Extras Filtering a Pivot Table Drop down lists
79. ils are reviewed When conducting an audit the user should sample these transactions to determine if they are exempt Page 43 P Card Audit Guide This is especially critical if a single purchase or multiple purchases exceed the 5 000 bid limit or were purchased from a non contract vendor Remember cardholders must still follow the Order of Precedence even if the NIGP MCC is exempt While the user can add MCCs to this list it is not recommended unless the Process Improvement Team updates it since you might exclude some transactions that are in fact non compliant Contract MCCs columns AC AD Contract MCCs are those MCCs that are associated with OTU aL known Statewide Contract vendors Purchases at merchants that have not been previously identified as Contract IMCC contract AND have the same MCC represent possible failure to use a contract merchant As with the exempt MCCs this list is not guaranteed to be infallible The user should not assume that all transactions could have been purchased from a contract vendor Only review of the documentation will determine that Page 44 Department of Administrative Services State Purchasing Division Process Improvement DOCUMENTATION REVIEW Georgia PURCHASES OF SUPPLIES AND MATERIALS There is no one right definition of adequate documentation for all transactions The nature of the goods or services received determines what information the invoi
80. ion checklist is available on line at the State Purchasing Division SPD web site under Process amp Performance The address is http doas ga gov StateLocal SPD Process Pages Home aspx Policy evaluation checklist in Excel format that rates a policy as Bd Policy Risk Evaluation a high medium or low risk that card program personnel do not 4 30 2010 have adequate guidance Click on the Excel icon to open the document The version of the checklist on the web site will always be the most current version that the Process Improvement Team from the State Purchasing Division uses However Entities can modify the checklist to assign different ratings to individual questions and to the score that results in a particular risk rating The number of possible points all questions get a Yes answer should always be 100 In the SPD version of the spreadsheet some questions receive four points and others three for a Yes answer depending on the importance SPD places on that aspect of the policy You might want to place more or less emphasis on some questions and that is acceptable This tool is designed for you to evaluate your policy based on your organization s emphases Page 1 P Card Audit Guide USING THE CHECKLIST Go to the SPD web site and download the spreadsheet It is recommended that you save a master copy of the spreadsheet if you make any customizations The blank spreadsheet will provide a place to enter your
81. lculate e The average cycle spend for a 12 month period as well as the largest cycle amount for each cardholder e The average cycle spend and largest cycle amount for the same period for all job titles For example determine the average spend for all Clerk II personnel Procurement Officer personnel etc Page 9 P Card Audit Guide e Compare the average cycle spend and largest cycle spend for individual cardholders with the average spend by cardholders with the same job title Determine if the calculations look reasonable for the type of job each cardholder has A large volume of spend on a non procurement job title might indicate that the cardholder has job responsibilities not normally associated with the title A small volume of spend on a procurement job title might indicate that the employee is not using the card to its full potential or that the employee fulfills non procurement related job duties The chart below shows an example of a system of standard profiles plus a special purpose profile explained below Single DFT IW me i Transaction Transactions Profile Cycle Limit MCC Groups Merchant Category Code Groups MCCG e GASTATE e GASTATE1 e GASTATE2 e GAATHTRAV As shown management can structure the card profiles so that cardholders can make transactions only at merchants assigned to specific merchant category code groups The Page 10 P Card Audit Guide State of Georgia currently has four group
82. led Art Objects 05280 Sculptures Marble Metal Plastic etc 05288 Wood Carvings 23227 Dried Flowers and Plants 23232 Floral Arrangements Artificial 23235 Floral Supplies Artificial Flowers Floral Tape etc 32548 Milk Products X 37515 Bread Rolls etc Including Brown and Serve Items Available on SWC X 37530 Cakes Cookies and Pastries Available on SWC x 37545 Doughnuts Fried Pies Bagels Available on SWC x 7550 Pastryshels OO X 3s Rma rrGJJ UGGGGG S j X 37560 Taco shells and Toras gt X XX XX XX X Px oww Butter x 3885 totsgeCheee O O O OOOO Xx 3890 emyAlTypes Xx 3803 Desserts Milk Based Not Otherwise Classified Xx os eggnogand Mix xi 3845 keGem OOS x 3850 keMk x 386 Melee x 3875 MiAlWpes x 3880 Sheet Xx 3808 Toppings whipped Refrigerated x 3890 You SS gt X gt X gt gt X X gt X L X gt lt Xx gt lt X lt gt lt X 39002 Aquatic Invertebrates Octopus Squid Anemones Sea Cucumbers Xx 3995 f casings Meat Al Typs x 399 tee OOOO Xx 39m sek xi 3998 Fruits Fresh T Page 69 P Card Audit Guide X 39033 Hors d oeuvres meat trays vegetable trays dessert trays ot m ee Shortening Except Vegetable and Lard
83. liance audits the vendor name from the 19D query would be a good candidate for a Report Filter Here we have put the State Entity name and the vendor name in that order as the rows going down and the sum of the transaction amounts as the data The total in column C will be the TURA es amount of spend for the State Entity column A at the merchant column B LYP The Report Filters will be above the pivot table to allow filtering i Pi S CUEN MUM a a 2 2 t Excl Merchant All x v Labels X Values Pi 2 Exempt MCC All x TER Sumo Ame 3 Purchase Day x upp N v am 4 Contract MCC v u Er We heu Total 8 CENTRAL GA TECH COLL DIRECT BRANDS INC 250 58 SPENRO INDST9729886161 sag r Layout Update FACTORY D i i B PS When using the Report Filters only data rows that match ALL of the values selected in the filters will display For example in the illustration here records where the merchant has been identified as Excluded from review regardless of whether or not the MCC is exempt because of the probable NIGP code regardless of the day of the week on which the purchase was chosen and regardless of whether or not there is a contract merchant with the same MCC m No Exempt MCC No Purchase Day All Contract MCC All Po Quum lw ee gt e Excl Merchant No 2 Exempt MCC All Purchase Day All Contract MCC A
84. ll As another example applying the filters as shown here will pull all transactions at merchants where 1 the merchant has not been excluded and 2 the MCC is not exempt because of the probable NIGP code Page 90 P Card Audit Guide Step 4 Formatting the Numbers Click on the Sum of Amount button in the Values area When the pop up menu appears click on Value Field Settings nant MV Move to Report Filter 1 love to Row Labels a alue Field Settings i1 x Source Name Amount Custom Name sum of Amount Summarize by Show values as F Move ta Column Label gt Move to Values Summarize value field by X Remove Field i vl EJ F Yr t i ta D L I FI d LL l ELT ETET oo Choose the type of calculation that you want to use to summarize the data from selected Field Most of the time you will want to choose the Sum of the amount field Click on the Number Format button to select the format e g General or Currency Click the OK button in the Value Field Settings dialog box when finished Excel Extra There might be times when you want to count the occurrence of the amounts such as when you want to know how many transactions for 50 92 we have This is probably not very useful However you might want to count the number of transaction numbers or purchase order numbers Step 5 Modifying the Pivot Table The pivot table will di
85. lumn C on the Data tab As more contract vendors are identified the vendor name as it comes across from the Bank can be added to the lookup table so that it can be excluded in the future e Exempt MCC The State Purchasing Division has classified multiple NIGP codes as being exempt from State Purchasing Division rules The complete list of these is found on the State Purchasing Division web site Certain MCC s have been classified as exempt based on the NIGP that it would probably correspond with This filter corresponds to Column D on the Data tab e Purchase Day This filter allows the user to focus on purchases based on the day of the week such as Saturday or Sunday in order to identify possible misuse of the card However not all weekend purchases are inappropriate The cardholder s job requirements will influence when purchases are made This filter corresponds to Column A on the Data tab e Contract MCC This filter allows the user to focus on transactions at merchants that have the same MCC as a known contract vendor It is helpful in identifying possible failure to use contracts This filter corresponds to Column E on the Data tab Page 36 MODIFYING THE PIVOT TABLE DESIGN P Card Audit Guide The pre designed pivot table on the Compliance Analysis tab uses the merchant name as one of the Row Labels However in your work you might want to see cardholder names instead of merchant names To do this 1 Unclick the box next to t
86. mn as percentage with two decimal places This is average cycle spend as a percentage of a single cycle limit Page 15 P Card Audit Guide For example assume the average cycle spend created in the previous step is in cell U5 and the single cycle limit is in cell C5 the formula would read U5 C5 The formulas and results should look like this amp of Positive Cycles of Negative Cycles amp of Active Cycles Total Cycle Limit Avg Cycle Spend of Cycle COUNTIF D5 P5 0 COUNTIF DS P5 0 Q5 R5 85 B5 P5 85 U5 B5 COUNTIF D6 P6 0 COUNTIF D6 amp P6 0 Q6 R6 S6 B6 P6 S6 U6 B6 COUNTIF D7 P7 gt 0 COUNTIF D7 P7 lt 0 Q7 R7 ST B7 PT ST UT B7 COUNTIF D8 P8 lt 0 O8 R8 S5 B8 Us B8 LJUNTIF DS PS 0 QS R9 aaa OMS U9 B9 of Positive Cycles of Negative Cycles of Active Cycles Total Cycle Limit Avg Cycle Spend of Cycle 10 000 00 40 12 0 8095 8 000 00 314 12 15 7196 3 000 00 5 314 38 31 4496 25 000 00 749 13 14 3895 15 000 00 112 30 2 2595 10 000 00 15 28 0 3195 90 000 00 4 342 10 43 42 4 3 328 37 33 28 Poo oO oO oO oO oO Scd Preparing a Pie Chart If desired you can create a chart or graph to illustrate Cycle Limit and Single Transaction Limit discussed below utilization e Name the column range for the 26 of Cycle as percycle e Hi lite the entire column and enter percycle in the cell address field in the top left hand
87. mn is not essential However the template has a column for it If you do not download or enter your card program name then you will need to delete column B from the Data tab e Cardholder s Group Name e Cardholder name e Last four digits of the card number e Transaction amount e Purchase date e Post date e Merchant name e Supplier city e Supplier state e MCC e MCC description e Card Profile Name Page 19 P Card Audit Guide e Credit cycle Limit e Single Transaction Limit The Read Me tab of the workbook contains instructions on how where to enter the data and how to refresh the pivot table The columns on the Data tab of the workbook are named according to the above list of data elements on the second row of the spreadsheet e The first row of the spreadsheet has a drop down list in cell A1 for you to select the day of the month on which your cycle ends 4 or 15 or 27 These are the only values that the cell will accept e There is also a note instructing you not to delete rows one or two e Note that in cell A3 there is already a date value of 01 15 1900 This field has a formula that you will copy to all data rows during the analysis Do not delete this formula However if you do accidentally delete it here it is IF DAY H3 lt AS1 MONTH H3 IF MONTH H3 212 1 MONTH H3 41 amp amp SAS1 The Pivot Table tab of the workbook has the pivot table that you will use to review cardholder actu
88. nsidered equivalent to the extent the RFQ named certain brands but permitted equivalent products to be offered PURCHASING STAGE 6 AWARD PROCESS Public notice of the results of the RFQ must be provided as required by the GPM through the posting of the Notice of Intent to Award as appropriate and the Notice of Award The Notice of Award should not be issued until the State Entity has either issued a PO or the State Entity s contract has been fully executed Please note the Notice of Award must be issued within one day of the PO being issued or the contract being signed whichever comes first Any supplier challenges to the process will be resolved in accordance with the protest process defined by the GPM PURCHASING STAGE 7 CONTRACT PROCESS 7 1 Contract will be maintained in accordance with the GPM and posted to the Agency Contract Index on the Georgia Procurement Registry as appropriate 7 2 Contract Kick off meeting should be conducted with the vendor and stakeholders to review the terms and conditions order process and general contract information This will help with getting the contract off to a good start Please review GPM Chapter 6 Contract Administration for additional guidance in contract management 7 3 Annual evaluation will be conducted with end users utilizing the Contract Assessment Report SPD CPO25 In the event the State Entity desires to renew the Open State Entity Contract the renewal shall be exercised in acc
89. nt areas of a pivot table are e Data this is the information that you are trying to summarize or measure or count e Page a page is useful if you have a lot of data and you will want to look at only one main group at a time For example if you have 100 000 purchasing card transactions at 100 Agencies it might be easier to review one Agency at a time rather than all Agencies at once e Row the row determines what columns from the data will be in the left hand columns of the table You can have as many rows as you have columns in the spreadsheet of data e Column the column determines the names of the columns going across the pivot table The diagram to the right shows the relationship between rows and columns on the pivot table 8 7 2008 8 11 2008 CAIN PAM 9 7 2008 8 11 2008 CLARK MELISA AM MANA 941 908 CcDFEIS MADE LIL L L IC DEV PivotTable and PivotChart Wizard Step 3 of 3 Ix Where do you want to put the PivotTable report New worksheet C Existing worksheet Click Finish to create your PivotTable report Layout Options Cancel lt Back Next gt ii to 2016 04 1 2002NVARLIB K P r P Card Audit Guide Click the Layout button PivotTable and PivotChart Wizard Layout T h e C O u mn n a m e S fr O m t h e d a ta I i is Construct your PivotTable report by dragging the field buttons on the right spreadsheet are shown on the right 7 RSC ase hand side of the layout box
90. od as the Spending Limits Analysis you can use that export and or the spreadsheet used for that analysis It is recommended that you use at least three cycles one quarter of spend for the vendor spend analysis e Cardholder user name e Post Bank Date e Purchase Date e Merchant Name e Merchant City e Transaction Amount Page 21 P Card Audit Guide Transaction MCC MCC Description Copy the appropriate data to the corresponding columns on the Spending Limits Analysis Template xls used for the cardholder spending limits analysis Use this spreadsheet because you will need it to calculate the cycle ending date There is a column with a built in formula for calculating the cycle end date based on the bank posting date and when your Entity s cycle ends There is a Read Me tab on the spreadsheet with general instructions BASIC EXCEL SKILLS Once the transaction data is downloaded from Works Payment Manager into an Excel spreadsheet sort the spreadsheet by vendor name and transaction date Review each vendor individually to determine if Any single transaction or combination of transactions in a single day exceeded 5 000 This could indicate split transactions Insert two rows beneath each vendor name in order to facilitate calculating total spend for the time period Usethe SUM function in the transaction amount column to determine what was spent by all cardholders with each vendor during the period Ask Would a
91. oen tha PeeatTabla A Field List The pivot table layout screen is slightly different from that in Excel 2003 It has built in formatting for Rows To see the layout screen in the Excel 2003 version click on Options in the top left hand corner Click on the H Display tab and the Classic Pivot Table layout check box Then click 4 OK The column names Pivot Table Field List from the data mz spreadsheet are shown on the right hand side of the screen 2 To put any column in the Row Labels section click on the sitrwamin NEZ box next to the column name The rows will appear in the b 8 amp o LEE bale Row Labels box in the order shown in the list If you want to re arrange them you can drag and drop the fields in that section into the desired order You can also drag and drop field names to any of the areas shown instead of clicking on P Card Audit Guide the box Any of the spreadsheet columns can also be put in the Values area The most common use is to present the total of an amount field such as transaction or purchase order amount If Excel detects a numeric column it should automatically choose the Sum of that amount for the Value For any non numeric columns Excel defaults to Count of the rows The Report Filter area would be a good place to put the Program Name field in order to be able to filter on one Agency or University at a time For Comp
92. ordance with the terms of the contract and the applicable provisions of the GPM which identifies the applicable forms such as the Notice of Award Amendment Form SPD CP024 Forms are located at http doas ga gov StateLocal SPD Seven Pages Home aspx Page 60 Department of Administrative Services State Purchasing Division Process Improvement APPENDICES Georgia Page 61 APPENDIX A STATE USE PROCUREMENT LIST The items listed on the STATEUSE PROCUREMENT LIST PeopleSoft Contract GEPS CERT can be viewed and purchased from the Window Shopper catalogue or from Georgia Enterprises for Products and Services GEPS The links for these sites are Window Shopper https solutions sciquest com apps Router Login OrgName Georgia GEPS http www georgiaenterprises com The current State Use Procurement List can be found on the State Purchasing Division web site doas ga gov StateLocal SPD Contracts Pages Home aspx NIGP Code Product Description price Product Status 2019800003 Yellow Rubber Work Gloves per case 144 pairs 91 52 Food Service Polyethylene Gloves per case 10 000 2019800005 gloves 67 82 Mandatory 2019800002 Leather Work Gloves XL per case 120 pairs 189 27 4754161100 Medical Exam Gloves Small per case 1 000 gloves 69 97 Medical Exam Gloves Medium per case 1 000 4754161200 gloves 69 97 Preferred 4754161300 Medical Exam Gloves Large per case 1 000 gloves 69 97 Medical E
93. ould never be reviewed though When doing an audit a sample should be chosen from the total population in order to get an adequate picture The criteria used in this workbook are based on the Georgia Procurement Manual and the State P Card Policy The workbook can be customized to incorporate unique State Entity business rules as well This workbook makes extensive use of lookup formulas pivot tables and filters Users should have intermediate to advanced skills in Excel or access to helpful resources such as reference books and other users If the user needs help in this area the Process Improvement Team has also provided a brief overview on how to use pivot tables on the State Purchasing Division web site DOWNLOADING TRANSACTIONS FROM WORKS PAYMENT MANAGER The workbook is a template with pre established columns based on a specific Works Payment Manager download file layout The data can be downloaded from Works either to an Excel file or to a text file However the download for this workbook must be a text file because some fields that the workbook needs as text come out of Works in a numeric format Numeric fields will not work with the VLOOKUP formulas on the spreadsheet If you download the transactions to a text file you can specify that the Merchant Category Code can be a text field instead of a numeric field when you open the file There are two reasons for this e There are some MCC s that have leading zeroes If opened dire
94. ount 5 547 56 Count of Number I 50 1 3 495 52 n TE nm ww co P 2 ES Hicrirsoll Enori nel Talde Feri hero TM Be pdt yew peet Forme Ios paa Wade bek B3 f Data 3 Cardholder and Cyek Dial wn T Alexander Jarice Ton Sum of Amount Count of Number EFJ Sum af Amount 13 Alen Larry 2500 Sum of Amount 1451 58 Count of Humber B 15 Sum of Amaunt2 461 Bay 16 Sum af Amount 105 38 M Count of Nurnber 5 1B ium af Amenuntz Ie 38 18 Anderson Donna 500 Sum of Armaunt V Br Sum of Arun Bet EI 3 Allgood Jeffery 10000 22 Archin Herman 1000 Sum of Amaunt 255 06 2 Count of Number 3 E Bum of Amount 25506 35 Argo Stephanie 7500 Sum af Amaunt 3 B7 5 Count af Number 1 EE Sum ol Amount 1387 2B Anmistad Audrey S000 Sum afAmaunt 3405 52 NE nunt of umbar PivotTable Field Source Field Amount Mame Max of Amountz Summarize by Count Mums PivotTable Field List Drag items to the PivotTable report ID Bagel tke oc amp r Hi dg Ze Fa a a 28 o a 12 76 D o teer sali ge teien VEA ab D a iDsubOi lis ixem s T Niele a 2d Uh al 75 GR Ye Reply with Changes End Review i Aa abl 5 2 iv c EB EB Ga ERI SP BAS E Month be EJ Amount P El Supp Name E Supp City E H Supp State
95. partment of Administrative Services State Purchasing Division Process Improvement Georgia USING EQUOTE FOR OPEN STATE ENTITY CONTRACTS OVERVIEW The APO CUPO identifies a need for an Open State Entity Contract through analysis of purchases The need for an Open State Entity Contract is justified by the following e The need cannot be met through a statewide contract an existing entity contract or a mandatory source e The need exceeds or is expected to exceed the 5000 00 threshold within a given fiscal year and must be competitively bid and e The exact quantities or total number of orders are not known The following describes the action steps for the APO CUPO for creating an Open State Entity Contract according to 7 Stage Process 1 PURCHASING STAGE 1 NEEDS IDENTIFICATION 1 1 Through purchase analysis through PeopleSoft and WORKS Payment Manager the APO CUPO determines that an Open State Entity Contract is needed as described above 1 2 The APO CUPO s analysis of previous and anticipated future purchases should assist the APO CUPO in identifying associated stakeholders and the benefits associated with a State Entity contract e g savings associated with aggregated spend The APO CUPO should review purchases and future needs of all of the State Entity s divisions business units A PURCHASING STAGE 2 PRE SOLICITATION 2 1 APO CUPO assembles a sourcing team which consists of existing stakeholders i e
96. r other documentation showing the purpose of the event at which the meal was served Printing e Copy of finished product e Job order or other document showing the number of documents printed Page 46 P Card Audit Guide Newspaper or Internet Advertisements e Copy of the newspaper advertisement as printed and the date s printed e Print screen of the actual advertisement on the Internet site OTHER PURCHASES Payment from Statements Cardholders must pay from an invoice when at all possible If a merchant regularly holds invoices and generates charges only from a monthly statement to its customers then the cardholder must retain all individual invoices and attach them to the statement as documentation that the statement reflects the total amount of charges The cardholder should staple the invoices to the statement and attach an adding machine tape proving that the total of the invoices matches the amount on the statement Recurring Monthly Charges Services If merchants do not send out monthly statements either through regular mail or via email then the cardholder should attach a copy of the original order for the service showing what the monthly amount will be and the length of the term of service The cardholder must document any change in the amount charged compared to the amount shown on this documentation with a new notification from the merchant Notes generated by the cardholder to this effect are not adequate documentation
97. redit Limit columns as General Notice that you can skip a field if desired However since your download was designed specifically for this workbook you will not want to skip any fields If you do skip any fields be sure to delete the corresponding column from the Data tab spreadsheet Click Finish to complete the import process after you have assigned data formats to all fields You data will display in Excel Keep the file open It will be referred to as the data download spreadsheet from now on It is recommended that you save this file so that you do not have to run the report again if you have to start over Copy Transaction Data to the P Card Compliance Workbook In the next few steps you will copy the transaction from the data download spreadsheet to the template on the Data tab in the P Card Compliance Analysis Workbook It is recommended that you read through these steps prior to actually performing them in order to have an understanding of what you will do e Open the P Card Compliance Analysis Workbook and click on cell I3 on the Data tab This will be the first blank row in the CH User Name column See the screen shot below NOTE The screen shots in this document will be of the workbook using Excel 2007 If you have Excel 2003 your screens will look slightly different The wording of instructions uses Excel 2007 terminology You will need to perform the same tasks according to Excel 2003 steps All cell references will b
98. redit limit State Purchasing Division SPD does not dictate the number of profiles an Entity can establish Except for the Single Transaction Limit of less than 5 000 4 999 99 or less and cycle limit 25 000 SPD does not dictate the spending limits an Entity can impose on cardholders Card Program Administrators also do not have to obtain any approvals from SPD to raise cardholder spending limits if the amount will still be within these limits Bank of America also provides an optional limit An Entity can also choose to limit the number of transactions a cardholder can make in one day PROFILE BEST PRACTICES Limit the Number of Profiles Basing profiles on job titles and actual spend levels permits an Entity to limit the number of profiles it must maintain For example an Entity can group job titles with similar spending habits into one profile When an employee receives a purchasing card the Card Program Administrator can assign the cardholder to the profile for the employee s job title For individual cardholders whose spend does not fit the profile or who have unusual situations management can make exceptions to the rule that the cardholder must be in a specific profile Base Profiles on Job Titles Develop profiles based on job titles Generally employees with the same or similar job titles will need the same spending limits Assign a cycle limit that will accommodate the cardholder s in that group with the largest spend Ca
99. rovide the best savings based on the State Entity s expected buying pattern PURCHASING STAGE 4 SOLICITATION PROCESS 4 1 Post RFQ in accordance with GPM guidelines to eQuote and ensure you indicate Pre Bid Conference information in the bid description Attach all documents applicable as suggested above 4 2 Address all supplier questions in writing and post answers to questions publicly as an attached document to the RFQ prior to the closing of the RFQ 4 3 Make any necessary changes to the RFQ in writing by posting an addendum to the RFQ prior to the closing of the RFQ The APO CUPO may need to extend the RFQ closing date in the event an addendum is posted near the current RFQ closing date Remember no changes may be made to the RFQ after the RFQ has closed 4 4 Receive all bids through eQuote eQuote terminates suppliers access to the RFQ once the RFQ closing date and time has expired Page 59 P Card Audit Guide 5 PURCHASING STAGE 5 EVALUATION PROCESS As noted in the Georgia Procurement Manual the RFQ must be awarded to the lowest priced responsive and responsible bidder with whom the State Entity is able to reach agreement as to contract terms There may be more than one awarded supplier in the event the State Entity elects to make split awards Although the majority of the evaluation may be conducted by the APO CUPO the APO CUPO should utilize its subject matter experts to determine whether alternate precuts may be co
100. rs you will want to give each one a different name so that you will be able to find it easily later 2 Click Now to run the report now 3 Indicate the number of days to keep the report in the Works system 4 Click the Submit Report button in the lower right hand corner Page 53 P Card Audit Guide SCHEDULING REPORTS Choose a Date and Time e Click on the Later radio button and the Later button The Choose a Date and Time dialog box will Date July zH 2010 2 display Enter the date and time and click OK Time 1 md 36 md Cam ew e Click on the Recurring radio button and the Recurring button The Report recurrence dialog box will display Enter the information and click OK Report recurrence Every 1 days at Midnight x C Every Sunday at Midnight Every month on the 1st E at Midnicht El Cun cu C Every quarter starting January 1st ox Cancel e Click on the Submit Report button to schedule the reports ox Cancel RETRIEVING A REPORT Works Payment Manager saves reports for the number of days https payment2 works com A specified when the report was run The default for this time period is seven days but the user can change this to any number of days E tasks E Administration l Reports are stored in the Reports section under Completed Reports T Reports 1 Click on Reports to display the list of report options Bookmarks
101. s State Purchasing Division Process Improvement 5 P CARD COMPLIANCE ANALYSIS WORKBOOK Ge 9 a ADVANCED EXCEL OVERVIEW The State of Georgia Purchasing Card P Card is a procurement tool in the same way that a purchase order is a procurement tool All legal requirements for procurement in the State of Georgia as found in the Official Code of Georgia Annotated O C G A and the Georgia Procurement Manual GPM govern use of the P Card Training provided to cardholders and supervisors or approving officials prior to receiving cards or supervising a cardholder must include guidance on these laws and administrative rules Card Program Administrators Auditors and other third party reviewers must include compliance with these laws and administrative rules in their audit processes If cardholders are not abiding by the requirements then additional training and or corrective action will be necessary P Card industry best practices state that it is not necessary to audit every transaction This should be done at the supervisory level The auditor s role is to evaluate the controls in place and to review an adequate sample of transactions to determine with reasonable certainty that controls are working and that the card is being used according to State and internal policy requirements A review method known as continuous monitoring is also becoming widely recommended and practiced Continuous Monitoring allows you to identify
102. s This step will calculate the number of cycles during which the cardholder had a net credit negative amount The formula for negative spend is COUNTIF range lt 0 Range is all of the cycle ending columns excluding Grand Total for the row Forexample if the cycle ending columns are column D through column O and the Grand Total is shown in column P then the range for the first row will be D5 P5 The formula would read COUNTIF D5 P5 lt 0 e iH of Active Cycles Addthe number of negative and positive spend cycles to determine the total number of cycles during which the cardholder had activity The formula for the total number of cycles is Q5 R5 assuming that the number of positive and negative cycles are in columns R and S respectively e Total Cycle Limit Cycle Limit times of active cycles The cardholder could have spent this amount for all cycles during which there was activity on the card The formula is 8 of Active Cycles times the cardholder s credit limit Forexample assume the cardholder s cycle limit is in cell B5 and the number of active cycles created in step a iii is in cell the S5 formula would read B5 S5 e Average Cycle Spend Total Cycle Spend Grand Total column divided by of Active Cycles from 3 above For example if Total Cycle Limit is in cell T5 and the of Active Cycles is in cell S5 the formula would read T5 S5 e of Cycle Format the colu
103. s Butterfly Ladybug etc x 0060 Hogs Breeding Stock m gt x 04061 Hogs Commerc O O O0 E oom C x won Houses and Cages Bid x 04075 Marine Life Not Otherwise Specified o x 04080 tutes and Donkeys SSS xi om emes x 0408s PuleslayngTypesBaby Xx 04084 ulets Laying Types Starter Xx oo ews 04086 Recycled Animal Bird and Marine Life Accessories and Supplies EE EE 04087 Rodents See 495 12 13 for Experimental Types X 04088 Reptiles Live X 04090 04091 Specialty Items for Shows and Entertainment Perches Hoods Hoops etc Xx 0992 Tosfores Xx 04093 Turkeys Baby xi 04094 Turkeys starter OOS x _04095 Wildlife and Pet Bands Labels and Tags Not Electronic __ x 04096 Zoo Animals All Types _ _ _ 7 7 7 7 7gg gt gt X X X lt lt X lt lt gt gt XX X X gt XX gt X XX X gt PX 05202 Antiques x 95208 Ceramic and Glass Objects X 08212 Collections Museum Pieces x 99e Drawings Originals O 05224 Engravings Etchings Lithographs Serigraphs and Similar a a temduaons Coox 0932 Fabric Designs O X 0548 Mixed Media X 05256 Paintings Originals X 0924 Phtogaps O X 0572 jPostersandPrits NotOriinals gt XX XX X H Page 68 P Card Audit Guide 05276 Recyc
104. s three that are available to all cardholders and a travel group that can be used on a limited or temporary basis Assign job titles to a profile based on actual historical spend information or potential spend for that type of job For example the Card Program Administrator could assign all procurement personnel to Profile 7 because they will need more flexibility than an Administrative Assistant who orders office supplies for one unit The Card Program Administrator could assign the Administrative Assistant to a profile with more restrictions such as Profile 42 Management must address each case individually because there can be many reasons for spend not in keeping with a job title Profile procedures should address what to do if an individual cardholder needs larger cycle or single transaction limits than those for the job title A move up to the next profile might be in order Use Special Purpose Profiles Each Entity will determine the number of standard profiles it needs based on job requirements and actual spend Some agencies such as GEMA DNR and DOT could have emergencies or other special situations arise where cardholders need more flexibility in the types and amounts of purchases they can make The Card Program Administrator can move cardholders from the standard profile to the emergency profile and then back to the standard profile when the emergency is over For example certain agencies such as GEMA and DOT must respond to
105. sanananananannnnannannnannnnnnnnannansnnnnnaa 9 Use Special Purpose Profiles aaaaaaaaasaaassnannannnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnam 11 Data Analysis for Profile Evaluation aaaaaaaaasaaassanannnannnnnnnnnnnnnnnnnnnnnnnnninnnnam 11 PROCEDURE FOR SPENDING LIMITS ANALYSIS ree erre nnne nnnnnnn aane n anra nana 13 GETTING TAR OD Toc et oe 13 BASICEXCEL SRILES oeneetiicbHerodoxiietrthv bois ed parc HERE ORE EL RHOD RE FUE EN ER IR E RR YR FER 14 Basic Cycle Limit Evaluatio enee cunis eura veo LU cH Eu cues a 14 Basic Single Transaction Limit Evaluation ccccceeccccseccesectencceeceeeeetensenenses 14 CYCLE LIMIT ANALYSIS WITH PIE CHART ADVANCED EXCEL 1 rere rr errare nnns 14 Construct first pivot table Cycle Limit Analysis eene 14 Preparing a Pie 80 RN TE TT T OD 16 Alternate Cycle Limit RevieW aaaaaaaaaaasaasaaansnnunanannnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnninnna 18 SINGLE TRANSACTION LIMIT STL ANALYSIS WITH PIE CHART ADVANCED EXCEL 18 Construct second pivot table Single Transaction Limit Analysis 18 SPENDING LIMITS ANALYSIS USING THE TEMPLATE BASIC INTERMEDIATE EXCEL 19 EVALUATING VENDOR SPEND LEVELS cernere nnn nennen nennen nenas saa aaa a waa a aan A1 COMPILING TRANSACTION DATA ouetxus ekiv aariiE FEBRE FEX ERFEEEEE VER EE E ERE FEY
106. sh the Pivot Table P Card Audit Guide The most efficient method to review the data is to use the pivot table found on the P Card Compliance Pivot Table tab Click on that tab and then anywhere in the empty pivot table that Is Excl Merchant there Exempt MCC Purchase Day Contract MCC Sum of Amount Program Card Name blank sTeTeTeTeTs IN On the Options menu tab Excel 2007 click on the green refresh arrows All v All v x v All z MCC v MCC Description blank Grand To x Comp Supp Name All 7 blank blant blank Add Ins Options Design The pivot table will populate If you see Refresh Change Data Clear Select Wy blank in any of the columns this means source 7 id Piva that the pivot table data range Dia BeOS encompasses more rows than yOU have data on This is not a problem If you Want to Exempt MCC Purchase Day change the data Contract MCC source to include 4 Excl Merchant only rows with h Sum of Amount 7 Comp Supp Name transactions you 2 blank can do that S AIRGAS SOUTH ns Click on the Change Data Source button see above next to the Refresh button In the dialog box click at the end of the cell range backspace out the current row number and enter the new row number as shown and click OK The pivot table will automatically refresh If it does not click
107. spend of more than 5096 of their cycle limit The largest percentage of cardholders averaged between 196 and 1096 of their cycle From these figures it appears that cycle limits for this Entity s cardholders are set higher than what they need Cardholders who consistently use a high percentage of their cycle limit are probably set at realistic levels Those who consistently use less than 5096 of their cycle limit could be set too high for their needs However if these cardholders are also buyers who create purchase orders they could be encouraged to use the purchasing card in lieu of purchase orders in order to increase efficiency Page 17 P Card Audit Guide Alternate Cycle Limit Review The Cycle Limit should also be able to handle the largest per cycle spend for each cardholder To determine if cardholders are using the purchasing card to the greatest extent needed instead of the number of cycles with activity a c above enter the formula MAX range in the column next to the Grand Total Range is all of the cycle ending columns excluding Grand Total for the row The columns will show the amount of spend in each cycle The Grand Total column will show the largest cycle amount for the time period for the cardholder Once again there are no industry benchmarks However doing this analysis paying special attention to cardholders who also create purchase orders will identify areas where cardholders have the capacity to transfer purch
108. splay on the screen From here you can remove columns or rows You can also drag and drop other spreadsheet columns to rows or columns of the pivot table Drag the Credit Limit field to the Values area Notice that the default is Count of the Credit Limit Excel assumes that you want to count the number of rows with something in this field In this example it will count the number of rows for each merchant name In this spreadsheet the Number column represents the transaction number You can use a count of any field to determine the number of data rows associated with the field names shown in the Row Labels area The pivot table now displays the Count of Credit Limit The table will automatically display the data fields the way it thinks you want to Spreadsheet columns that are formatted as numbers or currency will automatically be inserted in the data area as a sum of the amount Text fields will automatically be inserted as count of the values If any of this is not correct you can change it See diagram at top of next page Page 91 P Card Audit Guide Hid Row Labels 2 Values Sum of Amount Jim rogram Card Name Comp Supp Name CENTRAL GA TECH COLL DIRECT BRANDS INC SPEMRO INDST372988616 FACTORY DIEI at of Credit Limit 4 5um of Amount Now delete the Credit Limit field from the Values area and drag and drop the amount column into the data area You will now see Count of Amount T
109. tc Architectural _ X 30638 General Construction Architectural x 305840 Graphic Design Architectural 90644 Heating Ventilating Air Conditioning Architectural gt X X X X X X X gt X X X X gt X gt X gt X gt X gt X gt lt X lt X Services X 90646 Highways Streets Airport Pay Parking Lots Architectural x 30648 90650 Industrial Processes Quality Control 90652 Interior Design Space Planning and Exhibits Displays 90654 Irrigation Drainage Flood Control Architectural i i H 90658 ighting Interior Exterior Architectural Services 90660 Mining and Mineralogy Architectural 90661 Models of Layouts and Buildings to Scale Architectural 90662 Petroleum and Fuel Storage and Distribution Architectural Page 73 Hi 90656 Landscape Architecture 90657 Land Development and Planning Architectural T P Card Audit Guide 90664 Planning Urban Community Regional Areawide and State 90666 Planning Site Installation and Project 90668 Power Generation Transmission Distribution Architectural 90670 Railroad Rapid Transit Monorail Architectural 90672 Recreation Facilities Parks Marinas etc Architectural Services 90674 Recycling Systems Architectural Design Systems Recycling Systems Architectural Design Design 90678 Security Systems Intruder and Smoke Detection Architectural
110. tention on transactions at merchants with specific Merchant Category Codes even if they ss inousrma supsues NOT ESEWHERE CLASSIFIED are allowable you can use the Red Flag DURABLE GOODS NOT HME CUSED column on the MCC table to do this i ae Simply put the word Yes in column X on the row for that MCC The Data tab will reflect the Yes in column H You can either filter on those transactions on the Data tab or add this column to the Report Filters on the pivot table You can also use this column to focus on specific spend areas Exempt MCCs columns Z AA The State Purchasing Division has ruled that certain Fr OO NIGP National Institute of Governmental Purchasing pa 1 2 codes are exempt from the State Purchasing Act This Exempt lExemptwcc list is available on the SPD website at http doas ga gov StateLocal SPD Docs SPD General NIGPExemptList pdf The State Purchasing Division updates this list if other NIGPs are determined to be exempt The Process Improvement Team has identified Merchant Category Codes that are possible equivalents of the NIGP code These are shown on the Lookups tab in the Exempt range name Since there is no direct correlation between MCCs and NIGP codes this list is not guaranteed to be infallible However this Report Filter can exclude some transactions that might be exempt Transactions at some merchants with exempt MCCs might not be exempt when the deta
111. the financial system for the same time period and compare to spend on the P Card The Agency University Procurement Officer should be doing this as part of the regular spend analysis Use of the P Card increases efficiency in most procurement operations and eases the burdens on the Accounts Payable staff Review the list of vendors used in PeopleSoft with the merchant names in the P Card spend data Answer these questions e Are buyers who are also cardholders both issuing purchase orders and using the P Card with the same merchant e Arethere local businesses on the list of purchase order vendors that probably serve the general public If so then they probably accept credit cards and would accept the P Card Page 40 P Card Audit Guide SAMPLE CRITERIA PIVOT TABLE The Compliance Pivot Table tab summarizes transactions by merchant name This allows you to view the merchants involved when you are looking for suspicious transactions The Sample Criteria Pivot Table tab contains a pivot table that summarizes transaction data according to Excluded Merchants Exempt MCC and Contract MCC status The tab contains instructions on how to use the filters to isolate transaction dollar amounts at all merchants meeting the criteria selected in the filters This tab is useful if you want to kick transactions out to separate spreadsheets based on their criteria in order to select a representative sample from each category Lu om w jo
112. the list Click one or more of the check boxes to show the information for that Agency University and or Supplier Name and or any other row labels Click OK when finished Applying Conditional Formatting You can apply conditional formatting to a cell or range of cells just like you would in a standard spreadsheet Click on the cell or the range of cells you wish to apply conditional formatting to EH D EB E ENS 3 OEE ee v Supp Name v SG AAA LOCK amp SAFE CO l Se ee eS A ACT PROGRAMS Amazon com BUMPER TO BUMPER CNT GA TCH MILLEDGEVILLE DMI DELL HIGHER EDUC FOX APPLIANCE PARTS OF LOVVES 00546 MEDIBAG CO MICHAELS 1686 R amp S INDUSTRIAL SUPPLY STAPLES ULTIMATE OFFICE WW GRAINGER 938 NTRAL GA TECH COLL DEPT OF EARLY CARE amp L DEPT OF ECONOMIC DEV GA 8TH DIST CRTS FISCA GA ABAC G ADMIN OFF OF THE C GA ALBANY STATE UNIV el Four Day Spend 08 08 08 xls Window trl 1 View Insert Tools Data Help i Calls Column Sheet fhe A aunt style ird Name Supp Name 3A TEH CO AAA LE n mp Page 83 P Card Audit Guide On the menu bar click on Format Conditional Formatting The Conditional Formatting dialog box will appear In Excel 2003 you can have up to three conditional formats ondition 1 cei Value Is v between v and Preview of Format to use
113. the score from the risk levels for scores tab pA VLOOKUP D2 risk_level 3 FALSE Jo r E D LLL 7 Policy Evaluation Risk Level and Rating Page 4 Department of Administrative Services State Purchasing Division Process Improvement EVALUATING CARD PROGRAM TRAINING Georgia OVERVIEW The following checklist provides guidance to determine if an Entity conducts adequate training for 1 personnel involved in program administration 2 personnel responsible for supervisory review of transactions 3 cardholders and 4 personnel responsible for conducting the self audit internal audit If any answers to the questions on the second table below are No or Needs Improvement make comments as to what the deficiency is and how the area could be improved PROCUREMENT TRAINING INFORMATION In the table below indicate the training the following personnel have had as it relates to the purchasing card program based on the following key e Introduction to Purchasing Card Principles yes no in drop down list e SA How to Conduct a Self Audit P card yes no in drop down list e Other Indicate any other procurement training Position Name How Long r OO T LLL Canana a sss Pd G memo 0 0 m T1 pedem a Page 5 P Card Audit Guide CARDHOLDER AND SUPERVISOR TRAINING CRITERIA Yes No Needs Improvement Training Need 1 Have any ot
114. tions e The Report Configuration screen will ed Tra GL Memo Sta I I I I Payable All stn display This screen has six sections Herr re eid MCC Spend Used Heports system Billing Statement system Spend By MCC Report Configuration Screen Sections Step1 Choose a Report Template Once you select your template shown above options for the template display The default display is Show Expert View which allows the user to see all data fields available and other report options The Simple View allows the user to change a template to specify the output format e g Excel and to enter parameters in the filter options 1 You can change the template if you decide the one you chose is not correct 2 Simple View displays just the fields to enter criteria for transaction selection To show just the Simple View click the button here The button name will change to Show Expert View Both views are discussed in more detail be OW as with all system reports you are able to configure 3 Use Last Run Settings allows you to 2 3 immediately run a report using the Show Simple View Use Last Run Settings Reset To Defaults template as it was configured for the previous run 4 Reset to Defaults sets the template back to the original Works design settings Step 2 Choose the Export Formats Available formats are PDF Excel and Delimited Text Click the desired check box Se eee eee iBesege tu Paper size US Letter
115. transactions where cardholders are not abiding by either State regulations or your own internal policies and procedures In Microsoft Excel VLOOKUP formulas can look at specific fields in a transaction to determine if they comply with a set of pre established business rules for that particular data element such as the vendor name or the Merchant Category Code MCC Those that pass the test can be eliminated from a high level review Those transactions that do not pass are the ones that you can focus your attention on to determine if your State Entity is at risk for financial loss due to such things as failure to bid large purchases or failure to use established contracts The P Card Compliance Analysis Workbook is an Excel workbook designed to help the Card Program Administrator or auditor monitor P Card transactions without actually looking at each one The workbook can be used with transaction data over any period of time even on a weekly basis if resources permit Use of this workbook is optional The P Card Compliance Analysis Workbook uses formulas in one cell to compare the values in another cell on the row to a list of a values It provides a Yes or a No response to indicate that the value in the cell matches or does not match the criteria This can eliminate transactions that for any number of reasons do not need to be Page 27 P Card Audit Guide included in a high level audit This is not to say that these transactions sh
116. u z Options Cancel ta fa ta a FT dla a ey P Drop Page Fields Here np Spend V epe Name Office Depot ee raul 51 37 573 95 B BOB BARKER COMPANY 7 Wal Mart 25 523 09 8 GEORGIA CORRECTION OF 25 302 11 9 FLOWERS FOODS INC 19 755 22 10 STAPLES BUSINE00348045 18 451 00 FULTON PAPER CO 17 909 47 NATL BUSINESS FURNITUR 15 345 47 13 CORP EXPR 800 582 4774 15 102 04 14 DMI DELL K 12 GOVT 14 260 03 154 PPDP 3 06 T Page 86 APPENDIX E EXCEL PIVOT TABLES EXCEL 2007 Pivot tables allow you to get different views of data by summarizing in two dimensions rows that go down and columns that go across Any or all of the columns in the original spreadsheet can be reflected as rows columns or values in the pivot table to present the information you want in summary form You are probably already familiar with what one looks like The table below shows summary sales data from multiple regions for multiple quarters Eastern 10 000 15 000 12 000 13 000 Region Midwestern 20 000 30 000 15 000 25 000 Region Southern 30 000 40 000 20 000 10 000 Region Western 25 000 15 000 30 000 25 000 Region 85 000 00 100 000 00 77 000 00 73 000 00 Behind all of these summary numbers lie the thousands of individual transactions that make up those amounts Instead of manually adding up all of those individual transaction amounts Excel can do it for you
117. u can also do a pivot table and chart Leave the default radio buttons checked Excel list and pivot table only and click the Next button Page 78 EARNING a ye t v Yes me hna px Ua Wnde nep d EIDG MATE XSmB Tt o wr NiWw ee iiw i TEA S E o NEM Ead Ee mee abii C za aT ER Go E ae AGENCYANETITUTION pel SPD MOC Audit Teal En spells a a Se ee E ae l TRAMSDATE BANKDATE LASTMAME PRST B Baron BA 12006 DOMINY ROBERT I CENTRAL GA TECH COLL paa 611008 DOMINY ROBERT A CENTRAL G TECH COLL Bg BT 12008 HALL BEN PLI i CENTRAL GA TECH COLL ATAA Boa KELLY CARL CENTRAL G TECH COLL BT oes amp 12008 KELLY CARL L ETN GA TECH COLL El Tyr H HE Bo 2008 KELLY CARL LA CENTRAL GA TECH COLL i r BY 12006 KELLY CARL 3 CENTRAL GA TECH COLL BT a n BAL HELLY CARL DAETON COLLEGE Els l a E BA VSG Causey Joy Ath DEPT OF EARLY CARE amp LEARNING ETA BTA EMANUELS MIREELE El IR DEST OF EARLY CARE amp LEARNING Rao B 11 2008 MCCORELE WA P DEPT OF EARLY CARE amp LEARNING BA BLOODS MCCORMLE KAY D MIDEPT OF EARLY CARE amp LEARNING Bae BGR MCCORKLE KAY DEPT OF EARLY CARE LEARNING aem BOO MCCORKLE HAY DEPT OF ECONOMI DEVLPIMNT avr 2008 By 2008 GAIN PAM Lap OF ECONOMIC DEVLERNT ETATE BEER CLARK MELISA E DEPT OF ECONOMIC DEVLPRANT Baai 6112008 CHEEKRMORE ULE isi OF ECONOMIC DEVLPIMNT Be Enea CHEERMORE ULKU A IDEPT OF ECONOMIC DEVLPINMINT Beaten DE B1 2008 FIBBEN ALEON Fi DEP
118. u did for the Excluded Merchants Weeks columns L N Lm SS There might be an occasion such as looking at t T Yow o o split purchases when you might want to see all Weeks Weekit Vs Begins transactions during a certain week Column B on the Data tab reflects the Monday of the al 1 4 2010 week during which a transaction took place 3 1 11 2010 This table must be maintained annually Enter the Monday of the week during which the new year starts For example if January 1 is on a Page 42 P Card Audit Guide Friday the previous Monday would have been December 28 Enter that date in cell N2 The remaining date cells will calculate based on the previous date plus seven days State Eligible MCCs columns P W These columns show all Merchant Category Codes and the State authorized MCC groups to which they belong There are not many MCCs that are not in at least one group The Process Improvement Team will update this list if new MCCs are added to groups or if new groups are added If you have received approval to use an MCC that is not in one of the State authorized groups you can change the No in column S to Yes so that the formula on the Data tab will mark that transaction as conducted at an authorized MCC 0742 VETERINARY SERVICES Yes r 07 RI URAL COOPEGRATIVE Yes V W g TI Red Flag MCCs columns Q X If your want to focus at
119. values AI MI MI MI Format only cells that contain gt Format only top or bottom ranked values gt Format only values that are above or below average gt Format only unique or duplicate values gt Use a formula to determine which cells to format omp Su Edit the Rule Description DWES Format only cells with LOBAL F cell Value v greater than 5000 Es EHOM BETAPI iti MPER To add additional conditional UMPER preview tee mo gun formatting repeat the above jprocc steps for all desired formatting EM L e Emm Page 94 P Card Audit Guide Removing Subtotals Whenever you put in two or more spreadsheet columns in the row area the pivot table will automatically insert totals for each row Sometimes this is helpful sometimes it is not especially when you have two or more spreadsheet columns represented as rows To eliminate the subtotals right click on the Row Label that has the subtotals In this illustration that would be the Program Card Name row section LX contre MCC Toi it A X 35 F 0 a To remove the subtotals click on the Program Card Name Subtotal bar to toggle the subtotals off CENTRAL GA TECH COL e To reinstate the subtotals simply right click z EE Refres on the Row Label name and click on the Subtotal bar Filter v Subtotal Program Card Name Sorting a Pivot Table
120. vot table as a Column Label Determine if any vendor had spend in excess of 5 000 on a single day or on consecutive days HINT The easiest way to scan through the data quickly is to apply conditional formatting to highlight any cell greater than or equal to 5 000 and then look for the highlighted cells If the answer to either of the following questions is Yes then investigate for possible split transactions e Dida single cardholder have all transactions e Did two or more cardholders in the same work unit make the transactions Weekend Purchases Select All from each Report Filter to reset the pivot table to its original configuration Using the Purchase Day Report Filter select Saturday and Sunday to review transactions made on the weekends Review such combinations as merchant name MCC description and cardholder user group to determine if a weekend transaction would be reasonable If not investigate those transactions You can also select No from both the Excl Merchant and the Exempt MCC Report Filters to eliminate transactions from contract merchants or at exempt MCCs If a cardholder is abusing the Bd Excl Merchant b All x Exempt MCC Be All x ES Purchase Day EX Contract MCC He 6 Sum of Amount 8 SAIRGASSOUTH 9 1800GOFEDEX E DDISPh XS 2 GO BURNS BARBARA EXPLORATRACK BURNS BARBARA 4 ORGANIZE COM BURNS BA
121. xam Gloves Extra Large per case 1 000 4754161400 gloves 69 97 Preferred NEN First Aid Kits 50 minimum order for kits and or replacements items 3453200001 Plastic 10 Person First Aid Kit 18 65 _ First Aid Kit Content Replacement _ ss0minorden Bandage 1 x3 16 Non Stick Pad Bandage 4 Non Stick Pad 1 Page 62 P Card Audit Guide Gauze 24 x 72 1 Gauze Roll 4 x 6 yards 1 Tape Adhesive Roll 1 x 2 1 2 yd 2 3459800048 51873 EE Floor Pads minimum of 6 cases per order 20 Combo Tan Burnishing Pad 5 per case MopHandles amp Heads oOo __ Soap Pouches NENNEN 4859800099 Soap Pouches enriched Case of 12 71 35 4859800100 Soap Pouches antimicrobial Case of 12 75 75 5609800002 7 board 40 x 48 10 67 5609800001 8 board 40 x 40 10 63 5609800004 Light Weight 40 x 48 9 47 5609800005 Heavy Weight 40 x 48 14 48 __ Wiping Rags NENNEN 7359800001 Wiping Rags 5 pound box 20 65 Poe Code Toner Cartridges Remanufactured E WEE NIE UM Product Status 20798000905 12A6765 Lexmark 19876 Page 63 P Card Audit Guide Mandatory Q5942X HP Q5949A HP Q6511X HP Updated July 09 2010 Page 64 P Card Audit Guide APPENDIX B HoW TO PURCHASE Note that the purchasing card can be used for any of these types of purchases provided the cardholder complies with all procurement laws and administrative rules
122. ycle spend within each job title This figure might be an indication of a good cycle limit for the job title e Determine if cardholders within a job title have similar spending habits If there is a cardholder who has significantly more spend than cardholders in the same job title this cardholder might be a candidate for an exception to the standard profile Other cardholders could remain in the lower spending limit profile Page 12 Department of Administrative Services State Purchasing Division Process Improvement o Georgia PROCEDURE FOR SPENDING LIMITS ANALYSIS The Spending Limits Analysis is designed to help you answer the question Could we reduce our risk of loss due to abuse while still providing our cardholders with the spending capacity they need in order to do their jobs This chapters provides guidance on what to look for in a spending limits analysis as well as how to do this using basic Excel skills For advanced Excel users the chapter also explains how to create the analysis pivot tables from scratch as well as how to use a pre designed template available from the State Purchasing Division s Process Improvement Team It is highly recommended that you read these instructions through completely prior to beginning an analysis in order to ensure that you know where you are going with it Refer to the Chapter on Evaluating Spending Limits for suggestions for your analysis GETTING STARTED Download the

Download Pdf Manuals

Related Search

Related Contents

Gear Head KB6500BTIP  複合GRシステム  BrightEye 90-FA Manual 2.4  Ayuda rápida del operador TLS-300 TLS-350 TLS - Veeder-Root  Manual de instruções  Fiche technique: Pâte de polissage 7890 F  Epson PhotoPC 750Z User's Manual  TVR Speed Six diagnostic software  Holmes 13ESM1 User's Manual  

Copyright © All rights reserved. Failed to retrieve file