Power BI: Ten scenarios to consider when analysing sales data from Sage 50 Accounts
Power BI can produce some very powerful and insightful analysis of your Sage 50 sales data. There are however some important things to consider if you want to get the right results. This is not a reflection of Power BI, but more to do with how Sage 50 Accounts stores sales information.
The detail is not specific to Power BI and is relevant regardless of what tool(s) you are using for your data analysis. The example charts shown have been produced using Power BI.
Content seriesView full content series
Sage 50 Accounts tables with sales information
There are three main areas to go in your Sage 50 ODBC tables to find sales invoice information and within those areas five tables:
Sales Invoices (INVOICE)
Sales Invoice Details (INVOICE_ITEM)
Transaction Details (AUDIT_SPLIT)
Stock Transactions (STOCK_TRAN)
Which table(s) should you use? Well the answer, like most things with Power BI and Sage 50 is “it depends”. It depends on how you are using Sage 50 and what kind of analysis you are trying to do. I have listed 10 different scenarios below and broken them down by whether or not you should use the Audit tables for your sales analysis.
Five scenarios where you must use the Audit tables for sales analysis
Batch sales invoices
The Sage 50 Accounts “Batch Customer Invoice” function can be used to post quickly a batch of sales that have already been made. This function does not update stock though, nor will a sale recorded this way appear in the Invoices tables. They go straight to the Audit tables. This means that if you want to include batch-created sales invoices in your Power BI analysis then you must use the Audit tables.
Imported sales transactions
Sage 50 Accounts provides the ability to import sales transactions from a CSV file. This is often done where sales are made in a separate system (e.g. an eCommerce system) and the import utility provides you a way of bringing the sales information into Sage 50 without having to key it in manually. This utility does not record the sales in the Invoices tables though. They go straight to the Audit tables. This means that if you want to include imported sales invoices in your Power BI analysis then you must use the Audit tables.
Nominal Sales Analysis
In Sage 50 Accounts it is possible to use the Transactions module to edit the nominal code information of a sales invoice line item. If you do this, it is probably for good reason but it won’t change the nominal code information of the previous stages of the same sales invoice in the Invoices tables. So for reliable sales analysis by Nominal Code you must use the Audit tables.
Deleted sales invoices
If you delete a Sales Invoice in the Transactions list in Sage 50 Accounts, this will mark the sales invoice as deleted in the Audit tables, but NOT in the Sales Invoice tables. Therefore if you are driving your sales analysis from your Sales Invoices tables this will give you the wrong results. Analysing sales using the Audit tables will still be correct. If you need to drive your analysis from the Invoice tables for the reasons given above, then you should issue a credit note rather than delete the sales invoice.
Sales Receivables Analysis
Although the Customers table (SALES_LEDGER) In Sage 50 Accounts contains the current balance by customer, this does not give a view as to how this is broken down by sales invoice. This information is held in the Audit tables. Pulling this information into Power BI can drive some very useful receivables analysis.
Four scenarios where you cannot use the Audit tables for sales analysis
Product Sales Analysis
If you want to perform product sales analysis you should use the Invoice or Stock tables. This is because the Audit tables do not carry stock code information and there is no robust link from the Audit tables to your stock information.
Product Gross Margin Analysis
For product margin analysis, we need product cost information as well as the sales information. The Stock table in Sage 50 Accounts contains two cost fields for a given Stock Code; Average Cost Price and Last Purchase Price. These however are the current figures. In Power BI we may be performing historical sales analysis; if product costs vary over time, then these fields will not give the correct values to perform historical margin analysis.
We will need to use the Stock Transactions table. A “Goods Out” transaction in this table contains the Last Purchase Price for that Stock Code at the time of shipping. In the Stock Transactions table this field is called “COST_PRICE”. The Stock Transactions table also contains the selling price, the date and quantity for each product shipped. So although the Stock Transactions table is not a list of sales invoices, if you want to do sales and margin analysis by product code this could be a very useful table to use.
If you are using the Sales Invoices tables to do your sales analysis, It is possible to create a link between the Stock Transactions table and your Invoices tables and therefore use the appropriate Last Cost price that was applicable for each line item on the sales invoice. You can establish a relational link using the Stock Code to filter the Stock Transactions table and then lookup the correct Last Cost price using the Reference field in the Stock Transactions table. It is a little complicated by the fact that this Reference field will contain the Sales Order number if the invoice was generated by a Sales Order, or the Sales Invoice number if it were created directly in the Sales Invoices module.
Sales Analysis by Custom Sales Invoice Fields, or “Taken By” field
Sage 50 Accounts provides custom fields “Analysis 1,2 and 3” within the Sales Invoice header. If you are wanting to analyse your Sales Invoices using these fields, then you will need to do your Sales Analysis using the Invoice tables.
This also applies if you want to analyse your sales invoices using the “Taken By” field (for example if you want to analyse sales by Sales Rep if you are using this field to represent the sales rep.
Unposted sales invoices
There may be times when you want to analyse your unposted sales invoices. In this case, you have no option but to use the Invoices tables as the basis for your analysis.
A scenario where “it depends” - Departmental Sales Analysis
The right sales table to use will also depend on if and how you use Departments in Sage 50 Accounts. In short, if you are using Departments in their most complete sense to group transactions of any type then it is the Audit tables that you will need to use for sales analysis. If you are using Departments as a way to segment your product codes, then you will need to use the Invoice tables or the Stock transactions table since the Audit tables do not have stock code information.
Sage 50 Accounts can store a wealth of really useful sales information, but if you want to analyse this it is important to understand how Sage 50 Accounts stores and modifies sales information. It is important also to understand how you are using Sage 50 Accounts in your organisation to record sales information. If you do this, and follow these guidelines then you can produce some very insightful and correct analysis with tools like Power BI.
Here is an example analysis in Power BI of Sage 50 Accounts sales data. This example is very focused on sales by product and so uses the Sales Invoices tables from Sage 50 Accounts.
YouTube stream to accompany this article.
I am a founder director of Accounting Insights Ltd, a specialist provider of Power BI reporting solutions to accountants in practice and in industry. I help accountants to use Power BI to create intuitive, engaging reports from their accounting data. I deliver management packs,...