I spend an awful lot of my time producing management accounts for a number of different clients. Most of the time I’m grabbing that data from Sage 200 based accounting systems.
My method of choice for this is using a standard tool that comes complete within the Microsoft Office package, within Excel called ODBC (Object Database Connectivity).
I use this to analyse the background data that sits in a sequel database, and from that I produce management information, usually in the form of big data from the client systems. A lot of this is then manipulated to show trend analysis, comparatives and visual aids such as charts and graphs.
Most of the information I will go on to summarize at a top level, noting perhaps two or three key indicators as to the nature of the way the business is performing, and then a slightly more elongated summary.
I then go into rather more detail looking at the monthly trend analysis. So we can spot peaks or troughs, anything that looks out of the ordinary.
The methodology that I’m using for most of this reporting is Excel, a couple of places that we tend to look are going to;
Insert > Pivot tables.
This is very very useful, it gives very good indicators of how you can get your data to work for you.
Another one is;
Insert > Charts and graphs.
This means you can visually display the information and helps whoever your giving the data to, to visualise the information that you’re trying to put over through to them.
Perhaps then another one if you’re wanting a rather more in interactive spreadsheet perhaps using them slicers to select some of the data that is there, that is drawn from your back office accounting system.
Now most the data spends all of its life sitting in the background of your Sage 200 accounting system and nobody really knows how to get that out. K Consultants can manage this relatively easily, so that you can get particularly high speed amounts of data from the system to generate the sort of management accounts that really can change the performance of a business.
One of my clients turned around to me after I’d spent about 10 days working on their system, saying that they couldn’t tell the Board of Trustees that I’d saved her and her team between three and four days a month on their management reporting, because they just find her more work to do.
The methodology of getting this data is relatively simple, but it is then how you manipulate the data, how you produce the data together to get results that starts to take the time.
The first step is to go to (in Excel again);
Data > Get data
Then choose from the source of the database you’d like to get.
Most of the time it’s likely to be a sequel server, sequel, but I use another sources and from ODBC (Object Database Connectivity). This way we can look at the tables individually or perhaps when I’m working on the information that we have, I’d go into sequel and write some sequel views that can amalgamate as much of the data as we need into any one table so that it makes it far easier to access.
If you would like to know more please just contact us at K Consultants.
Jon Waller
K consultants.