Questions? We have answers.

General Ledger Export - Sage 50 (UK)

Article author
Jonathon Plowman-Samson
  • Updated

General Ledger

  1. Navigate to the Nominal Codes section of Sage 50.
  2. Click Reports.
  3. Select the Nominal Activity section.
  4. Click the Export button.
  5. Select ‘csv’ from the drop-down menu.
  6. Ensure that all codes are selected by leaving the first nominal code blank and the final one as 99999999.
  7. Enter your financial period start and end dates.
  8. Do not tick the Inc B/Fwd Tran button.
sage_50_uk1.png

 

Once the CSV file has been downloaded, add the appropriate column names to the columns as below;

  • A: ‘Account Number’
  • B: ‘Account Description’
  • D: ‘Transaction ID’
  • E: ‘Transaction Type’
  • F: ‘Date’
  • G: ‘Vendor or Customer’
  • I: ‘Memo’
  • H: ‘Ref’
  • M: ‘Debit
  • N: ‘Credit’

 

Your data should now look like this:

image__57_.png

 

Save this file as an Excel spreadsheet and upload.

On the Review Data panel, ensure that you select Custom Running Total and use the Transaction ID column. Do not use the Smart Splitter when uploading a Sage 50 (UK) general ledger.

a36c7aed-345f-45f8-a1b3-920eef66ae3d.png

 

Closing Balance (Optional)

Open the Financials section of your company and click Trial Balance.

image__58_.png

 

Select Trial Balance and then Period Trial Balance.

image__59_.png

 

Choose to export to csv, and then select period 12 for your period. 

image__60_.png

 

Opening Balance (Optional)

Follow the same procedure listed in the Closing Balances section above, but select Brought Forward.

image__61_.png

 

- Accounts Payable - 

AP General Ledger

Begin by following all the steps for the General Ledger section of this guide and copy these transactions to a new spreadsheet.

Copy the transaction dates to a new column titled Document Date.

Create the following columns: User and PO Reference - with a 1 on every row.

Create a column called AP Type and paste the following formula in every row.

=IF(E2="PI","Invoice",IF(E2="PP","Payment",""))

Copy and paste the AP Type column as values. Your data should now look like this:

image__62_.png
 

Be sure that the Supplier and Debtor column has the title ‘Vendor or Customer.

Save this file for use on the Accounts Receivable section.

Take the completed Excel file and remove all transactions except for the purchase ledger transactions (PI, PC, PA, PD, PP, PR).

image__63_.png

 

This data can now be uploaded to MindBridge.

On the Column Mapping screen within MindBridge, ensure all columns are mapped appropriately, including Account Description and Vendor or Customer.

On the Review Data panel, ensure that you select Custom Running Total and use the Transaction ID column. Do not use the Smart Splitter when uploading a Sage 50 (UK) general ledger.

a36c7aed-345f-45f8-a1b3-920eef66ae3d__1_.png

 

Vendor List

Go to the Suppliers section.

image__64_.png

 

Click Send to Excel.

image__65_.png

 

AP Opening Balances (Optional)

Go to the Supplier section (see Vendor List above).

Click Aged Balances.

image__66_.png

 

Enter your prior period end date in both the Aged Balance Report Date and Include Payments Up To fields. 

If you are auditing from 1st of January to 31st December 2018, for instance, you will enter 31/12/2017 in both these fields.

image__67_.png

 

Click Send to Excel.

image__68_.png

 

Order List (Optional)

Purchase orders are not maintained in Sage 50. Please see relevant documentation for your purchase order systems. This data must also be present in Sage 50 in order to match fields. The Purchase Order Control Point Indicator (CPI) will be disabled.

 

Received List (Optional)

This is a list of goods received, which isn’t maintained in Sage 50. The Purchase Order Control Point Indicator (CPI) will be disabled.

 

User List (Optional)

Create a new Excel file and type the following data:

image__69_.png

 

- Accounts Receivable -

AR General Ledger

Take the file that was created in the Accounts Payable Guide and create a column called AR Type. Paste the following formula in the new column:

=IF(E2="SI","Invoice",IF(E2="SR","Payment",""))

Your data should now look like this:

image__70_.png

 

Filter out all transactions that are not sales transactions (sales transactions start with the letter ‘S’ in the Transaction Type column’).

image__71_.png

 

Save your file (.xlsx or .xls) and you can now upload it to MindBridge.

On the Review Data panel, ensure that you select Custom Running Total and use the Transaction ID column. Do not use the Smart Splitter when uploading a Sage 50 (UK) general ledger.

a36c7aed-345f-45f8-a1b3-920eef66ae3d__2_.png

 

Customer List

Click the Customers tab and then Send to Excel.

image__72_.png

 

AR Prior Open Transactions (Optional)

Click the Customers tab and then Aged Debts.

image__73_.png

 

Enter your Prior Period End Date in both the Aged Balance Report Date and Include Payments Up To fields. 

If you're auditing from 1st of January to 31st December 2018, for instance, you will enter 31/12/2017 in both these fields.

image__74_.png

 

User List (Optional)

Create a new Excel file and type the following data in it:

image__75_.png
Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.