Save content
Have you found this content useful? Use the button above to save it to your profile.
KPIs
iStock_putilich_kpi

ABC of Power BI: ‘K’ is for KPI rethink

by

Continuing his ABC of Power BI series, Hugh Johnson gives four examples where Power BI may be able to give you deeper, more relevant, and timely insights than conventional KPIs.

17th Aug 2022
Save content
Have you found this content useful? Use the button above to save it to your profile.

Power BI provides extremely flexible ways to aggregate and summarise your accounting data and therefore new ways to calculate your key performance indicators (KPIs), or new variations of existing KPIs.

Finance KPIs that are taught as part of today’s MBA programmes are typically calculated from summary income statements, Bbalance sheets and cashflow statements. This is for a good reason, because very often it is the only information available. 

What would you change, though, if you had at your disposal a much more granular level of detail, with the power to analyse a company’s data by individual date, nominal code, customer account, or even transaction? Would you carry on as before, as you learned on your MBA programme, or would you seize the opportunity to reach for something a little more refined?

Here are four KPIs that you may want to rethink, depending on your specific situation.

Sales/gross margin performance

You will get the best insights into your true underlying sales and gross margin performance by aligning your measurement with the purchasing cycles of your customers, rather than the artificial fiscal periods of your company. You can of course incentivise your customers with discounts or other means to align their purchases with your fiscal time periods, but this is costly and your KPIs will be corrupted towards measuring your customers’ response to incentives rather than the underlying health of your business.

It is common that customer buying patterns are weekly rather than monthly, particularly in the leisure and tourism, hospitality and retail sectors and the industries supplying them. In these cases, tracking sales/gross margin figures weekly, or per rolling x-week time period can be much more insightful than tracking them per calendar month. It is easy to set this up in Power BI, along with your monthly view that you may still need for statutory/other purposes. I have discussed this specific point in more detail here, and show an example where steady-state weekly sales and monthly overheads produced a 700% variation in net month-on-month profit.

Chart, histogramDescription automatically generated

Liquidity/working capital

Like ducks paddling like hell under water while maintaining a calm appearance above the surface, some companies have massive daily swings in their working capital requirement, only to finish the month in pretty much the same position as at the start. If your business is like this, then a monthly working capital KPI snapshot is not going to be very helpful. Instead, it might be more useful to track working capital daily, along with peaks, troughs, and trends. This is very simple to automate using a tool like Power BI.

Debtor days

The classic formula to calculate a company’s debtor days is the following:

Debtor days = (trade receivables ÷ annual credit sales) x 365

It is a nice, simple formula and can be used to calculate a year-end position for a company simply by extracting the input numbers from the income statement and balance sheet. It is a quick calculation to do on the back of an envelope based on a set of annual accounts.  

There are some drawbacks to this.

  1. The credit sales figure taken from the income statement is usually the net figure before VAT, whereas the trade receivables figure in the balance sheet will represent the gross figure, including VAT. If this difference is ignored, then any debtor days figure will be inflated by the ratio of gross sales/net sales (this could be up to 1.2 given the current UK VAT rates). A simple calculation to undo this will not work either unless all sales are at the same VAT rate.
  2. The formula assumes relatively steady-state monthly sales. If there is a sudden spike in sales just before the year end (this is common if you are incentivising your customers with year-end discounts), then the debtor days value will shoot up because of the higher receivables figure. This will happen with any sales increase and is of course misleading. The reverse problem applies if there is a slump in sales.
  3. The formula only works where individual transactions are not material to the total. It is therefore almost always unsuitable for calculating a debtor days value for an individual customer or a small customer group.

Countback method for calculating debtor days

If you have access to all your customer invoices and receipts, then you can tackle the problem of calculating debtor days in a totally different way, by counting back from today how many days’ gross sales make up today’s receivables balance. The principle is quite simple. Let’s say that today, a customer has an outstanding balance of £1,000. Then from today, step back in time, day by day, summing the gross value of that customer’s invoices until you reach £1,000. The debtor days value for this customer will be the number of days that you needed to count back. The beauty of this method is that it equally applies to a single customer with a debt for one invoice, through to your entire debtor book. 

If you implement this calculation as a measure in Power BI, then it will automatically adjust to the number of customers visible in the filter context and you don’t need to give it a second thought. One thing that you will need to watch, in low-volume environments, is part, or “almost” payments. If there is a customer with just one invoice that is for £2,501 and you receive a payment of £2,500, then until you collect, write off, or otherwise credit the remaining £1, the debtor days calculation will return an ever-increasing value when just looking at this customer.
The illustration here shows the very marked difference between the two ways to calculate debtor days.  Graphical user interface, applicationDescription automatically generated

Setting up the Countback method in Power BI is relatively straightforward, though perhaps not immediately obvious. For efficiency, rather than step back one day at a time, you can iteratively “guess” the debtor days value, successively narrowing down the answer to get the right result within four or five iterations. 

YTD whatever

I have written before about my feelings regarding year-to-date (YTD) calculations. On the one hand, they are useful as they track progress to a full-year goal, or budget. On the other hand, a YTD calculation generally offers little business value before the end of the first quarter and perhaps not until the second half. I am not advocating to dispense with YTD calculations as they are so engrained in our psyche, along with fiscal year targets. So, keep your YTD KPIs because they are familiar and very goal-driven, but why not supplement them with rolling 12-month measures that will give you a continuous and much-improved insight into your underlying trends?

If your business has an annual cyclical cycle, then a rolling 12-month view will give a much better insight into underlying performance than a YTD view. Additionally, if compared with a previous rolling 12-month period, you can do a regression on the difference to drive a continuous, seasonally adjusted, data-driven trend, or even forecast. 

Chart, waterfall chartDescription automatically generated

The chart above shows a rolling 12-month income statement to the end of Q1. The first quarter YTD would show a positive net profit, but with growing expenses in June. The 12-month view shows a very different story, with the impact of massive expenses in the previous September and October. It legitimately raises the question about whether these were one-off, or cyclical; a question that may not be apparent with just the YTD figures.

This kind of thing is relatively simple to do in Power BI, which can easily aggregate over any range of dates regardless of fiscal year boundaries.  I have used this technique in the past to create rolling 12-month financial statements as well as seasonally adjusted product demand and cashflow forecasts.

Summary

A good Power BI (or similar) model gives you a much more powerful and flexible way to analyse your accounting data than a typical blizzard of spreadsheets. It gives you new freedoms to analyse your data over whatever time periods give you better insights into the true underlying performance of your business. A Power BI model will also enable you to hold your data at a much finer level of detail than you can in Excel. This opens possibilities for more precise, more frequent, and finer-grained KPIs than perhaps you have used in the past. The idea is not to increase measurement for measurement’s sake, but to deliver KPIs that give you better insights into the real underlying trends of your business.

Useful links

 

Tags:

Replies (1)

Please login or register to join the discussion.

By Silver Birch Accts
18th Aug 2022 13:16

There is nothing new here, these KPIs used to be called Ratios and were paryt of accountancy foundation courses well over fifty years ago.

Thanks (3)