BSP Reconciliation – Importing BSP recon

BSP Reconciliation – Importing BSP recon

STEP 1 : Saving the File

Save the BSP recon file into a folder e.g C:\BSPDISKS (Take note of destination folder and File name)

STEP 2 : Importing the File into Windows Invoicing

• In Windows Invoicing go to OPTIONS \ BSP Reconciliation \ Import BSP:

• Specify the and SCAN to find the billing

you wish to import: (Optional: Filters may be applied, by clicking on the FILTERS Tab)

• Highlight the billing you wish to import:

and click on

STEP 3 : Running the BSP Reconciliation to Excel :

• Export the BSP Reconciliation Report in Windows Invoicing to Excel (Enquiries, BSP Reconciliation)

• Ensure the following check boxes are ticked:
  Differences
  Explanation Notes

• Click on and the following screen will appear:

• Click on and the following screen will appear:

• Click on again.

STEP 4 : Check differences total :

(The system will now display a list of all the reconcilable items, that needs attention)

• The difference between BSP & QTrav should be the same as amount in your difference column on your excel spreadsheet. (Please ensure you capture all ADM’s, ACM’s & Refunds.)

STEP 5 : Time to clean up

(If you wanted to print your differences report with all the applicable columns that exported from Windows Invoicing do it now, before proceeding!)

Delete/Clear all the unwanted columns e.g.: Day, Con, Client, Excl Fare, Fare VAT, Aprt Tax, %, Cash, Commission, Agent VAT, DUE to BSP, Charged and Division.

ONLY KEEP THE INFORMATION IN THE COLUMNS TO BE USED FOR YOUR JOURNAL.

STEP 6 : Joining multiple text columns into ONE :

Option1 : Using a text formula : CONCATENATE
                  Enter the following Formula in the Blank Column D :
                  =CONCATENATE(A7, ” “,B7, ” “, C7)

Explanation : Take the text in Column A, Row 7 followed by a space and the text in ColumnB,Row7, Followed by a Space in ColumnC, Row7 and join them together into this Column where my cursor is currently.

The result: 074 1800764771 C0000036

OR

Option2 : Using normal combining of various Columns into one :
                  Enter the following Formula in the Blank Column D :
                  =A7&” “&B7&” “&C7

Explanation : Take the text in Column A, Row , add a blank space, add the text in ColumnB,Row7, add a blank space,and add ColumnC, Row7, and place the result where my cursor is currently flashing.

The result: 074 1800764771 C0000036

STEP 7: Copy formula to all rows in Column D

Copy the selected formula to all rows with reconcilable items.

STEP 8: Pasting the VALUES only

Highlight Column D – Right click, select, COPY – and right click, select, Paste Special (Values)

(It will remove the Formula and replace with the merged text)

STEP 9 : Time to formulate your Journal

On a new blank worksheet…… Top Left Column and Row

• 1. Type : <TABLE> Add Column Headers :

• 1. Now copy your columns across from your Worksheet (where you calculated the differences)

The following accounts were created under Air Suppliers, with a Payment method as Month End to facilitate the easy capturing of the BSP Journal:

• • • (800/BS2) BSP – ADM & ACM To Be Captured
    • (800/BS3) BSP – Refunds To Be Captured
    • (800/BS4) BSP – Follow Up account

(Please check your setup as different account numbers may have been used)

• 1. Check the differences on your spreadsheet to the BSP Follow Up account (e.g. 800/BS4) account in the GL to see if there are any amounts that must contra out.
  2. All ADM’s & ACM’s as per note bellow to be captured to a Creditors GL account (E.g. 800/BS2)
  3. All Refunds not yet processed in the current month or refunds that relate to the previous months to be captured to a Creditors GL account (E.g. 800/BS3)
  4. All ticket errors that need to be corrected via an invoice or a credit note to be captured to a BSP Follow up Creditors GL account (e.g. 800/BS4)
  5. Make sure the journal totals balance back to difference on your excel spreadsheet.
  6. Finish your journal with : </TABLE>
  7. Save your BSP journal to a place where you will find it e.g C:\BSPDISKS

STEP 10 : Import into Journals in Winmain and finalize Journal

In Windows Main Module: File \ or alternatively select :

Click on the button:

Enter the location and file name e.g.: C:\bspdisks\bsp may09jnl.xls Click on to import the journal.

Finally click on to write the journal in Draft mode to Windows Main Module:

Once the import is complete:

Proceed with Saving of the journal.

STEP 11 : Process the relevant invoices and Credit notes for any tickets or Refunds that still need to be captured in

Windows Invoicing Module.

Note:

• (If TASF in use) Create a New Land Supplier: Ts1 – with “month end” payment method

• Create a new Air Supplier: Bsp as BSP payment method (if not already created)
• We advise that you reconcile the 800/BS2, 800/BS3 & 800/BS4 accounts on a monthly basis.
• These accounts are clearing accounts only.
• Only ADM’s that need to be invoiced or disputed must be allocated to the ADM’s/ACM’s account
• Only ACM’s that reverse ADM’s can be allocated to this account.
• ACM’s received in respect of refunds can be allocated to either the Refunds or ADM’s/ACM’s account.
• Any ADM’s & ACM’s that relate to Revenue or Expenses must be allocated to the relevant Income or Expense account.