Electronic purchasing card: user guide for card controllers

5. Reports available to card controllers

5.1 My Exports

In “My Exports” you will find the following reports

Cardholders and Approvers

The report is in TSV format and will need to be converted to an Excel format

It gives you information about a card and their hierarchy

It will look like this

And gives you the following information:

• Column A: card status (active, inactive, closed)
• Column B: cardholder’s name
• Column C: account number
• Column D: directorate’s name/partner’s name
• Column E: hierarchy details

Merchant Category Spend

Used for details on Merchant Category Code (MCC) and codes

And gives the following information:

• Cardholder’s name (column A)
• Supplier’s name (Column B)
• Transaction date (Column C)
• Merchant category description (Column D)
• Merchant category code (Column E)
• Transaction amount (Column F)

Monthly Report

Used for the spend on a card, it can be set up for the entire period of the card being active

It will look like this

And gives you the following information

• Financial Information assigned to the transaction by the cardholder (Column B to H)
• Transaction Amount (Column I for debits and J for credits)
• Cardholder Name (Column K)
• Transaction Description (Column L)
• Transaction Date (Column M)
• Line Item Description (Column N)

Receipt Report

Used to match receipts with transaction details when downloaded, it is the Monthly Report with extra information

It will look like this

And gives you the following information

• Financial Information assigned to the transaction by the cardholder (Column B to H)
• Transaction Amount (Column I for debits and J for credits)
• Cardholder Name (Column K)
• Merchant Name(Column L)
• Transaction Date (Column M)
• Line Item Description (Column N)
• Transaction Reference (column O)
• Reviewed By (column P)
• Reviewed date (column Q)
• Approved by (column R)
• Approved Date (column S)

Reviewed/Approved

Used for checking at what exact date a transaction was reviewed and/or approved

It will look like this

And gives you the following information

• Cardholder Name (Column A)
• Last 4 Digits of the card number (Column B)
• Transaction Amount (Column C)
• Transaction Posting Date (Column D)
• Merchant Name (Column E)
• If the transaction has been reviewed yet (Column F). Y stands for Yes and N stands for No
• Reviewing Date (Column G)
• Reviewer User ID (Column H)
• If the transaction has been approved yet (Column I). Y stands for Yes and N stands for No
• Approval Date (Column J)
• Approver User ID (Column K)
• If the transaction has been exported yet (Column L)

Statement Report

Used for extra information about a merchant and the currency the transaction was originally in

It will look like this

And gives you the following information

• Cardholder Name (Column A)
• Last 4 Digits of the card number (Column B)
• Transaction Date (Column C)
• Posting Date on SDoL (Column D)
• Transaction Amount in GBP (Column E)
• Merchant Name (Column F)
• Merchant City (Column G)
• Merchant Postcode (Column H)
• Original Currency Amount (Column I)
• Original Transaction Currency (Column J)
• Conversion Rate (Column K)

Statement Report plus MCC

Used for extra information about a merchant category and category description

It will look like this

And gives you the following information

• Cardholder Name (Column A)
• Last 4 Digits of the card number (Column B)
• Transaction Date (Column C)
• Posting Date on SDoL (Column D)
• Transaction Amount (Column E)
• Merchant Name (Column F)
• Merchant City (Column G)
• Merchant Postcode (Column H)
• Merchant Category Code (Column I)
• Merchant Category Description (Column J)

Transaction Report with Descriptions

Used for transaction information and description

It will look like this

And gives you the following information

• Cardholder Name (Column A)
• Merchant Name (Column B)
• Transaction Date (Column C)
• Transaction Amount (Column D)
• Expense Description (Column E)

Transaction Status Report

Used for checking at what exact date a transaction was reviewed and/or approved

It will look like this

And gives you the following information

• Cardholder Name (Column A)
• Transaction Amount (Column B)
• Merchant Name (Column C)
• Transaction Date (Column D)
• If the transaction has been reviewed yet (Column E). Y stands for Yes and N stands for No
• Reviewing Date (Column F)
• Reviewer User ID (Column G)
• If the transaction has been approved yet (Column H). Y stands for Yes and N stands for No
• Approval Date (Column I)
• Approver User ID (Column J)

addendum

Not to be used

5.2 Accounting and Reconciliation Reports

Accounting Code Detail

Available in a PDF or Excel format

Used to get all the financial information about a transaction

It will look like this

And gives you the following information

• Cardholder Name
• Last 4 digits of the card number
• First line of the address
• Second line of the address
• Posting Date
• Transaction Date
• Merchant Name and Address
• Transaction Amount
• Expense Description
• Directorate/Partner
• Entity
• Sub Analysis 1, 2 and 3
• Cost Centre
• Account Code
• Programme Code

Accounting Codes Analysis

Available in a PDF or Excel format

Used for analysis of all transactions made by a cardholder during a set period of time

It will look like this

And gives you the following information

• Cardholder Name
• Last 4 digits of the card number
• First line of the address
• Second line of the address
• Transaction Date
• Posting Date
• Merchant Category Code
• Merchant Category Description
• Merchant Name
• Merchant’s Address
• Transaction Amount
• Total Amount by type of spend
• Total Amount for all transactions on this period

5.3 Expense Reports

Expense report with tax

Only available in PDF

Used to determine the amount of GST (Good and Services Tax) on products. As GST does not exist in the UK this can only be used for transactions made abroad. For transactions made in the UK the report will show 0.00 as a GST amount.

However, it will show something for transactions made outside of the EU.

It will look like this

And gives the following information:

• Posting Date
• Transaction Date
• Description (merchant name and address)
• reviewed (with a “tick” if the transaction has been reviewed)
• approved (with a “tick” if the transaction has been approved)
• Net amount
• GST amount
• Expense amount (GST inclusive)
• Accounting codes

Expense report with tax (2)

Only available in PDF

Used to determine the amount of GST (Good and Services Tax) on products. As GST does not exist in the UK this can only be used for transactions made abroad. For transactions made in the UK the report will show 0.00 as a GST amount.

This version can be printed and signed if needed.

It will look like this

And gives the following information:

• Posting Date
• Transaction Date
• Description (merchant name and address)
• reviewed (with a “tick” if the transaction has been reviewed)
• approved (with a “tick” if the transaction has been approved)
• Net amount
• Goods and Services Tax (GST) amount
• Expense amount (GST inclusive)
• Accounting codes
• Signature lines

5.4 Financial and Spending Reports

Account Statement

Available in a PDF or Excel format

It is separated in two sheets, a detailed one and a summary one.

It is used for details about transactions and the merchant related to each of them

It will look like this:

Detail page

It will give you the following information

• Transaction date
• Posting date
• Acquirer reference number (merchant reference)
• Description (merchant name)
• Address (merchant address)
• Amount

And summary page

It will give you the following information

• Account name (cardholder’s name)
• Account holder (Scottish Government)
• Transaction Count (number of transaction within that period)
• Transaction amount
• Payment count (the payment count will always be at 0 as we do not pay RBS)
• Payment amount (the payment amount will always be at 0 as we do not pay RBS)
• Total count (number of transaction plus payment count)
• Total amount (amount of transaction plus payment amount)

Account Statement (version 2)

Available in a PDF or Excel format

It is separated in two sheets, a detailed one and a summary one.

The detail one will give you transaction date and posting date, supplier’s name, location, country, amount in GBP and original currency, conversion rate and amount charged.

The summary one will give you the number of transactions per cardholders, the total amount, the payment count, the payment amount, statement date, balance and payment details. Note: payment details will always be at 0 as we do not pay RBS.

It will look like this

Detail page

And summary page

Account Statement (V2)

Available in a PDF, Word or Excel format

It will give you transaction details, merchant reference number, merchant name, address and transaction amount for each transaction.

It will look like this

• Account Statement Report with Signature Lines

Available in a PDF or Excel format

This report is an exact copy of the Account Statement Version 2, with added lines for signature

It will look like this

Details page

And summary page:

• Spend Analysis by Transaction Category

Available in a PDF or Excel format

It is separated in two sheets, a detail one and a summary one.

It shows how spend is organised between 8 categories.

It will look like this

Detail page:

And will give you the following information:

Detail page, lines:

• Amount (first line of the table): total amount spent within the spend category
• Amount % of the total (second line): percentage spent in this category out of the total percentage spend
• Average amount (third line): amount per transaction within the spend category
• Count (fourth line): number of transactions within the spend category
• Count % of Total: percentage number of transaction in this category out of the total percentage number of transaction

Detail page, columns:

• Airline (column A)
• Lodging (column B)
• Vehicle hire (column C)
• Restaurant (column D)
• Retail services (column E)
• Vehicle related (column F)
• Cash Advances (column G, this will always show 0)
• Other (column H)
• Total (column I)

Summary page:

And will give you the following information:

Summary page, lines:

• Amount (first line of the table): total amount spent within the spend category
• Amount % of the total (second line): percentage spent in this category out of the total percentage spend
• Average amount (third line): amount per transaction within the spend category
• Count (fourth line): number of transactions within the spend category
• Count % of Total: percentage number of transaction in this category out of the total percentage number of transaction

Summary page, columns:

• Airline (column A)
• Lodging (column B)
• Vehicle hire (column C)
• Restaurant (column D)
• Retail services (column E)
• Vehicle related (column G)
• Cash Advances (column H, this will always show 0)
• Other (column I)
• Total (column J)

5.5 Merchant and Supplier Reports

Merchant Detail

Available in Excel of PDF

Gives you extra information about the merchant/supplier who was used

It will look like this:

And will give you the following information:

• Column A: merchant’s name and merchant category code
• Column B: merchant’s city/town
• Column C: merchant’s state/province/county
• Column D: cardholder’s name
• Column E: transaction date
• Column F: posting date
• Column G: transaction net amount
• Column H: transaction VAT amount (this will be empty unless the cardholder has entered a VAT when reviewing their transaction on SDoL)
• Column J: transaction total amount

Spend Analysis by Merchant

Available in Excel of PDF

It will give you a breakdown of the transaction per merchant, both as a detail (per card) and as a summary (for all the cards)

The detail page will look like this

And give you the following information

• Column A: merchant name
• Column B: amount
• Column C: amount % of total (% of the total amount spent on the card)
• Column D: average amount
• Column E: count (number of transactions for that merchant)
• Column F: count % of total (% of the total number of transactions on that card)

And the summary page will look like this

And give you the following information

• Column A: merchant name
• Column B: amount
• Column C: amount % of total (% of the total amount spent on the programme)
• Column D: average amount
• Column E: count (number of transactions for that merchant)
• Column F: count % of total (% of the total number of transactions on that programme)

Spend Analysis by Merchant Category

Available in Excel or PDF

It will give you a breakdown of the transaction per merchant category, both as a detail (per card) and as a summary (for all the cards)

The detail page will look like this

And give you the following information

• Column A: merchant category name
• Column B: amount
• Column C: amount % of total (% of the total amount spent on the card)
• Column D: average amount
• Column E: count (number of transactions for that category)
• Column F: count % of total (% of the total number of transactions on that card)

And the summary page will look like this

And give you the following information

• Column A: merchant category name
• Column B: amount
• Column C: amount % of total (% of the total amount spent on the programme)
• Column D: average amount
• Column E: count (number of transactions for that merchant category)
• Column F: count % of total (% of the total number of transactions on the programme)

5.6 Programme Management Reports

Approver Summary

Available in Excel of PDF

It will give you details about approver accounts and transactions reviewed and/or approved

It will look like this

And give you the following information

• Column A: user name
• Column B: User ID
• Column C: email address
• Column D: last login date
• Column E: accounts count (the number of account the approver approves for)
• Column F: transactions count (number of transactions to be reviewed/approved)
• Column G: reviewed yes
• Column H: reviewed no
• Column I: approved yes
• Column J: approved no

Dormant Cardholder Report

Available in Excel of PDF

It gives you the details of the last time a card was used

It will look like this

And gives you the following information:

• Column A: account name
• Column B: account number (last 4 digits)
• Column C: Account currency code
• Column D: net balance
• Column E: credit limit
• Column F: dispute amount
• Column G: past due amount
• Column H: over limit amount
• Column I: days since last transaction
• Column J: cycles since last transaction (a cycle is a month)

5.7 Tax Reports

• UK LID VAT Invoice

Only available as a PDF.

Provides detailed VAT-accredited information for Line Item Detail (LID) transactions undertaken with VAT-capable suppliers. Designed as a VAT reclamation invoice for UK companies, the report compiles the required VAT information for merchant-initiated transactions originating from the UK

It will look like this

And will give you the following information:

• transaction number
• transaction date
• merchant name
• address
• postal code
• merchant VAT number
• customer reference number
• item description
• item quantity
• item VAT rate
• unit price
• item discount
• amount (excluding VAT)
• total transaction discount amount
• transaction amount (excluding VAT)
• transaction VAT payable
• transaction amount (including VAT)
  • UK Non-Evidence Invoice

Only available as a PDF

Transactions missing any of the required information are detailed in the UK Non-VAT Invoice Report. Transactions will appear on the UK Summary VAT Invoice report where the complete line item information is not captured, but a commodity description is provided and the total transaction amount is £5,000 or less

It will look like this

And will give you the following information:

• transaction number
• transaction date
• merchant name
• address
• postal code
• merchant VAT number
• customer reference number
• item description
• item quantity
• item VAT rate
• unit price
• item discount
• amount (excluding VAT)
• total transaction discount amount
• transaction amount (excluding VAT)
• transaction VAT payable
• transaction amount (including VAT)

UK Summary VAT Invoice

Only available as a PDF

Gives you information on VAT about transactions

And will give you the following information:

• transaction number
• transaction date
• merchant name
• address
• postal code
• merchant VAT number
• customer reference number
• item description
• item quantity
• item VAT rate
• unit price
• item discount
• amount (excluding VAT)
• total transaction discount amount
• transaction amount (excluding VAT)
• transaction VAT payable
• transaction amount (including VAT)

5.8 Travel Reports

Airline Summary

Available in Excel of PDF

It gives you the detail of transactions for airlines

It will look like this

And gives you the following information

• Column A: carrier name
• Column B: count (number of transactions)
• Column C: debits amount
• Column D: credits amount
• Column E: total amount
• Column F: amount % of the total
• Column G: average amount

Expense Report

Available in Excel of PDF

It gives you the details of a transaction, all the financial information attached to it and if it has been reviewed and approved

It will look like this

And gives you the following information:

• line 1 and 2: information about the transaction itself (posting date, transaction date, description, receipt amount, posted amount, expense amount, reviewed, approved)
• line 3: expense description (as entered by the cardholder)
• line 4: accounting codes
• line 5: directorate, cost centre and account code
• line 6: entity, programme and sub analysis 1
• line 7: sub analysis 2 and sub analysis 3

Lodging Chain and Summary

Available in Excel of PDF

It will give you the details of transaction made to hotels

It will look like this

And gives you the following information

• Column A: Chain Name
• Column B: New Billed
• Column C: Amount % of total
• Column D: Count
• Column E: Count % of total
• Column F: Average Amount