SageForum for UK versions of Sage 50- Help and support.

Hello All,

I am new to sage, but trying to build an excel sheet that from on data export from sage will populate:
P&L by division & consolidated view
Balance sheet consolidated view

I thought this would be simple, and i have achieved it, however oddly i cannot get the bank accounts to reconcile back. Every other nominal code agrees. (when looking into this it looks like on random transactions it thinks its a credit rather than debit??)

My current method was to:
Go to departments: Reports: Monthly Profit and Loss by Department: edit report: Drag in department field and Name: Change the criteria to not exclude any categories. And then do 'data to excel' and i can work on this source data. And this method has worked perfectly for everything other than the bank account.

Therefore i was wondering if there is a better way to do this. In terms of a data dump the important fields i need are:

All Nominal Codes
Department Number
Period (Period 0 being brought forward)(i only need to run this for a year at a time)
Amount
ChartOfAccountsSubCategory.RangeDescription



If anyone has any other suggestions of how i can do this, or had similar experiences with the bank not reconciling on a data download from department reports, i would really appreciate your help.

Many thanks,

Nasser

The trouble is you need to do that every month.

The way I would do that is to dump the entire audit header and splits into a spreadsheet using odbc and msquery.

This gives you the data you need for almost any analysis.

You could export the chart of accounts and build from that as well, or you could pull the nominal ledger then build columns across the sheet to give you a departmental TB or a TB for each of the last 12/24 months or whatever you need.

From there you can build a chart of accounts.

And to run it next month, you change the date and hit refresh or if you find a late entry to this month, just put it in Sage then hit refresh.

Periods do not exist in Sage 50, it is "open item", so you add a period value column and write a formula to be the net amount if in the date range specified.

If you didn't need the departments, you could do this off the nominal ledger "bucket" values for each period, using an offset to get the value for this period.

Hello,

Thank you so much for your reply, it sounds positive, and although i understand what you are saying, im not sure of the method you are referring to.

Would you edit an existing report (if so which one) or go into file create and create a new report (If so what document type would you use)

I keep trying various things but the data coming out is less than usable.

Like i said in the first post, the best luck I've had is to amend the departmental P&L to include all nominal codes, and department fields. this works for every department and every code, apart from bank (I'm not sure if this is an known issue)

Unfortunately when contacting Sage themselves they wouldn't recommend editing any reports and couldn't advise me any further other than trying to sell an excel report creator.

If you could provide a little more detail i would be incredibly great full.


(in terms of having to do a monthly download this is what i'm aiming for -
The current process: For each department (we have around 25) we use the excel report downloader and update the P&L for each one. and then the B&L.
I'm trying to create a new process to do one download a month to update all P&L & balance sheets automatically. A time saver, and means the format is much better.

I have made this work with the method above, however the only codes that do not reconcile are the bank nominal codes. as it randomonly switches the signage on some transactions (which i have managed to identify)

Thanks,

You do not do anything in Sage.

In Excel you "get data" from odbc, select the sage driver and launch msquery, this then lets you select the tables, link them and select the fields you want, this creates a refresh-able link to the data in sage.

To do departmental reporting you essentially pull the audit header and split information from sage into an excel spreadsheet (think of the transactions screen as a spreadsheet), from there you can use a number of techniques to consolidate, sum and analyse the data.