This article is a summary of talk that I gave last month at Accountex, identifying situations where you may want to reach beyond Excel and to one of the new-generation business intelligence tools.
I talked specifically about Microsoft Excel and Power BI, since these are tools that I am very familiar with. Many of the points can also be applied more generally to “spreadsheets” and “modern cloud-based BI tools”.
“Excel - I really love you, but sometimes I find you a little difficult to work with”
“It is such a simple, but powerful concept. The possibilities are endless!” said an excited Kevin, who was in his final year of an accounting degree. It was a cold, dark London night in early 1983 and over a beer my flatmate was explaining to me a new computer program that he had just seen, called Lotus 1-2-3. It was, he explained, called a “spreadsheet”, and “it will revolutionise how accountants do their work”.
Kevin was right of course about the spreadsheet, but not specifically Lotus 1-2-3. Microsoft Excel was launched a couple of years later in September 1985 and soon replaced Lotus 1-2-3 as the world’s best-selling spreadsheet. It went on to become one of the most successful computer programs of all time. Some estimates suggest that more than one in ten people on the planet now use Microsoft Excel and of course in the accounting profession I guess that the number is closer to 100%.
I love Excel. I love that if you are just slightly numerate and computer literate you can start to use it with no training at all. Over time you can grow your skills and end up doing some really advanced stuff. You can use it for all kinds of things that it was never really intended for. For example, I used Excel recently to prepare my booth layout for Accountex. By forcing each cell to be square, I could quickly mock-up a 10’ x 10’ layout.
Content seriesView full content series
The point is that it is so ubiquitous, easy-to-use and flexible that people use it for all kinds of things in addition to its original design purpose. I cannot see the day when I will stop using Excel (or its future replacement).
So if it is so great, why would I or anyone else want to reach out for another tool to work with numbers? The answer is simple, there are some (common) situations where I find Excel just a little bit difficult (or impossible) to work with and by contrast, Power BI very easy. I have grouped them into five categories and explain them below in the rest of this article.
Working with large tables of related information
For me, Excel starts to get a bit unwieldy when the dataset is too large to scroll through all of the rows in a worksheet in a couple of seconds and where I want to combine information from a number of connected tables. As an aside, it is technically impossible to work with a sheet that has over one million rows but that doesn’t happen to me very often, but I would often work with 00,000’s of rows. Let’s take a common scenario where I want to analyse sales and margin over time by product and customer.
I want to produce a report like this, where I can slice by customer or product and drill down to see the underlying data:
If my dataset were Sage 50 Accounts, then at a minimum I would need to extract five tables of information from Sage:
Sales Invoice Lines
These would have relationships something like this:
I find that to piece this together in Excel is a bit tricky to say the least. The trickiest part is to calculate the margin on an historic sales transaction.
In Sage 50, the historic cost price of a product can be found in the Stock Transactions table, so to look up the last cost price of a product on the date of a sales invoice you would need to find the most recent purchase for that product before the date of the sales invoice and add this in to the sales line. From there you can calculate the margin for that line item.
Now bear in mind that if you are a trading company with stock, this Stock Transactions table can be extremely large - maybe a few hundred thousand records. Difficult! At least for a mere intermediate Excelian like I am.
In Power BI this kind of thing comes naturally and is in my opinion so much easier to do. The underlying “currency” of Excel is the Cell and the formula that relates it to any other cell(s). This problem is not about individual cells though. It is about tables and their relationships. This is the way of thinking behind Power BI. A single formula in Power BI can act across millions of rows and multiple tables. You can work with tables in Excel, but this is kind of an add-on feature that to be honest I find a bit clunky.
To produce many reports from the same dataset
If we take the scenario above, most of the time spent in preparing the report is to get the data into the form that you need it. Now if you have gone through the heartache of pulling together the five tables of data into a single model for analysis then you can produce many more reports from this data really easily, adding some additional calculations as necessary.
Example reports that you could produce from this same underlying dataset are:
Forecast sales by customer and product
Customer trends heatmap - who is above or below expectations?
Days Free Stock report based on actual stock levels and forecast demand
New customer wins
The investment required up front is to create such an underlying data model is not trivial either in Excel or Power BI. In my opinion though, Power BI has been designed from the outset to support this kind of thing and it is just so much easier to use for this. I mean way easier!
Once I have my model in place, with a well-constructed Dates table, then I can create new report pages very quickly.
To produce a report page like this in Power BI only required six additional calculations (“Measures”):
[Sales this Year]
[Sales last Year]
[Margin this Year]
[Margin last Year]
[Margin % this Year]
[Margin % last Year]
To create stunning visualisations of my data
One of the things that Power BI is famous for is the large and growing portfolio of stunning visualisations that you can create. Building reports that use these visualisations is what you do in Power BI. In my opinion, this is the end game and primary objective of Power BI. There are the standard visualisations that come in Power BI and also a much larger library of 3rd party visualisations.
You can also get these visualisations in Excel via the free Excel plugin called PowerView, but again, like working with tables this is an add-on feature in Excel with all the clunkiness that goes with that. So if my primary objective were to produce some great visualisations of my data then I would reach for Power BI straight away.
To share my reports and dashboards
Perhaps the most common way to share an Excel file is simply to attach it to an email and send a copy. This is really simple to do and fraught with problems. What tends to happen in practice is that an Excel file becomes a snapshot in time, for example “Week 7 Sales Report”.
Data security can be an issue so several reports might be produced to match the different target recipients. Excel provides password protection on spreadsheets, but what if you have a team of six sales people looking after different customers? You may only want to share the sales details of particular customers with the reps who look after them. So now do you produce 7 sales reports every week? One for each rep and one for management? Yes there may be ways to handle this in Excel, but not easily. So this is another trigger for me to start using Power BI.
The core essence of Power BI is in my opinion (and I think in the opinion of Microsoft) is around sharing reports and dashboards. This is where Microsoft makes its money out of Power BI and where it runs rings around Excel. There are two main components to the Power BI Suite: Power BI Desktop and Power BI Pro. Power BI Desktop is free and the oddly-named Power BI Pro comes in at £7.50 per user per month (correct at the time of writing this). It is Power BI Pro that enables you to publish reports and share dashboards online and via the native Power BI apps for smartphones and tablets.
Now, do you remember what I said about restricting access to particular customer data for particular sales reps? Well with what is called “Row Level Security” you can do just that in Power BI. In the desktop application (before you publish to the web) you can define roles for accessing the data in your report.
Here you can set for a given role what rows of data can be seen for each table. Effectively a filter is applied to the entire dataset based on the role of the person accessing it. So one single sales dashboard could serve each of the six members of the sales team with their own sales data without giving them access to the rest of the data.
Now here is a really nice thing about how Power BI and Excel work together. If you have an Excel report in the form of a worksheet or part of a worksheet you can define this as a datasource in Power BI, then use all of these features for sharing and securing your Excel report data.
To automate report production and refresh
As I mentioned earlier, perhaps the most common way to share Excel reports is simply to email them, creating a snapshot of your data at that time. Modern BI tools like Power BI come with some really nice facilities to automate the process of refreshing your dataset.
Unfortunately even with these tools this is not going to happen by magic and what is possible depends on your datasource. If your datasource is online and a supported datasource then this process can be fully automated. If not, then in Power BI you can use free tools such as the Personal Gateway to convert say CSV or Excel files into such an online datasource. You just need to make sure of two things:
That you have somehow figured out how to automate placing your updated CSV / Excel files in a particular named location on a computer.
That the computer running the Personal Gateway is switched on with the gateway running at the the time of the scheduled data refresh.
If you are (or someone in your organisation is) spending more than say half a day per week gathering data, preparing and disseminating Excel (or similar) reports then you may seriously want to consider adding Power BI (or similar) to make your life easier. This is particularly true if you find yourself producing several reports from the same underlying dataset (e.g. Sage 50).
There is cost to doing this, mainly in the form of acquiring a new skill (e.g. working with Power BI), building out your underlying data model and software (e.g. £7.50 per month Power BI Pro licence), but if these reports are important to you then it should deliver significant returns and quite likely new insights into your business.
A copy of my slides from my presentation “Beyond Excel, using BI to reveal new insights from accounting data”
A high-level introduction to Microsoft Power BI.
A link to download a free copy of Microsoft Power BI.
A self-paced, free online course from Microsoft “Analysing and Visualising Data with Power BI”. I have done this course and recommend it without hesitation.
YouTube video Excel and Power BI are better together (40 mins)
About Hugh Johnson
I am a blogger on Power BI and your accounting data and the creator of Accounting Insights for Sage 50 which is the only Microsoft Power BI Content Pack available for Sage 50. Please feel free to try it out in connected to your own Sage 50 system on the web or with Power BI Desktop.
I am a senior vice president of Suntico, an online financial data platform that works with popular small business accounting software. I am responsible for the company’s customer division. This covers products, marketing, sales, customer on-boarding, customer service and renewals. I am also in charge of our product strategy and road map.
I hold a Master of Engineering from City University London (awarded with commendation and the highest marks in my year) and a Post Graduate Diploma in International Selling from Dublin Institute of Technology (awarded with distinction and the highest marks in my year).
My personal passions are high performance Fireball dinghy racing and food.