Exploiting business opportunities ahead of the competition relies upon strong analytical tools which are agile yet are also powerful. Analysts within the business need such tools to be useful out of the box but will also need them to support advanced techniques when necessary.
Spreadsheets have always been the tool of choice for financial analysis and financial modelling. What about for data analysis and data modelling? Legacy spreadsheets had lookup functions that helped with data, non-numeric analysis, but as mentioned in an earlier post the fit was clumsy and error prone. Modern Excel, 2010 and beyond, include data tools either built right in or as add-ins from Microsoft. Power Query is a case in point. It’s built into Excel 2016 under the Data tab as “Get and Transform”.
Power Query uses a language informally called “M”. In this blog post with just a few lines of code a table of sales records is put through a “Get and Transform” process. The example here will add a cumulative running total automatically using the M language.
The Excel Query Editor contains three items:
- A query to extract the Sales data from a source and to add the index column
- A function called YTD() which returns a summation of the sales value for the given index plus all earlier periods; and
- A query that references the first query but adds the column “Running Total” which transforms the table into a richer data set to be loaded into Excel.
For example to calculate a result for the index value of 9, the function call is made “=YTD(9)” in another query. It will evaluate the sum of 285 plus 3714 automatically. Here’s the advanced part; It gets 3714 from @YTD(8) which it calls itself from within the “else” calculation inside the function itself. That inner result is evaluated as sum 280 plus 3434. Similarly it gets 3434 from YTD(7) which is evaluated as sum 368 plus 3066 and so on….until it reaches YTD(0) which is a terminal value detected by the “If index=0” then 497
The way that M handles recursion is that a function can call itself from inside that same function provided that it uses the @ symbol when calling itself. so @YTD(Index-1) is used each time that Index is above the value 0.
Here is a link to the above solution in a workbook.
At the heart of Excel’s “Get and Transform” or Power Query capability in the case of Power BI, the M language provides advanced transformational capability for the savvy analyst. The M language is not procedural but is a declarative and functional language which does not require the analyst to spell out every step; in contrast to VBA which does. So looping through data record by record is handled using elegantly concise function blocks, leaving time to instead analyse the insights and get ahead of your competition.
“What-if analysis” goes visual using Power BI
Financial models in Excel are usually limited to calculating results for a single scenario at a time. “What-if analysis” allows financial models to also calculate results for other scenarios; varying the underlying drivers and assumptions.
Visually compare many scenarios
As a decision maker, why not look at the results for several scenarios at once using data visualisations – say an array of charts?
Why not do this on an iPad or tablet using touch to change the scenario assumptions interactively?
Power BI in action
Power BI is a Microsoft suite of tools to ‘bring your data to life’. In the example below, an existing Excel financial model comes to life…
Go ahead and open this Power BI report in your browser and give it a try.
There are five key drivers of the results. The first set of dashboards in this report show you the effect of holding the other 4 constant and moving this up or down by 20%. The final dashboard shows all possible combinations up or down and the distribution of financial results from best to worst.
Want to find out more?
This is an example of Power BI giving you an easier way to get insights; by letting you visualise the dynamics of your business quite interactively.
This combination is formidable – Excel Financial Models coupled with Power BI to bring the model to life for better decision making.
To find out more or have your existing financial model visualised using Power BI then contact David Taylor at FAST LANE BI, a specialist Power BI and Financial modelling consultancy.
If you are in Melbourne, join us for the Financial Modellers Meet-up group where I will be showcasing the Excel financial model used in this example. The model is surprisingly simple as it uses the FAST financial modelling standard which supports scenarios.
World Class Financial Modelling – starts with a simple rule of thumb
Financial models underpin so many important business decisions the world over. Why then are they often such a challenge to get your head around? How can we do better?
Firstly, consider the arbitrary nature of spreadsheet layout and style. Financial Models from different developers can be as unique as their fingerprints, or thumbprints. Think about where and how the process of model building all begins.
Financial Modelling starts with a blank spreadsheet and a question; eg ” When will break-even sales volume be reached and what is our forecast market share at that point?”
Next, a financial modeller gets on with the job, applying their own unique layout and style.
Standardisation does exist
What about World Standards for good financial modelling? Do they exist and would they embody a consistency and best practice for layout and style? The answer is yes! One such standard goes by the Acronym FAST, which stands for Flexible, Appropriate, Structured & Transparent. Standardisation in Financial Modelling aims to reduce risk and improve collaboration. The standard is a set of rules prescribing how compliant financial models are fashioned.
For example, here is a style rule from the FAST standard; called the “rule of thumb”…
Do not write a formula longer than your thumb
So, according to FAST 3.03-01, a formula longer than your thumb likely means that it should be broken into more than one step. Have you encountered overly long formulas within financial models?
The value of such standardisation is real; not only for individual modellers but more importantly for groups of modellers. Also for consumers of the information. Organisations across the globe who are recognising this value are signing up and endorsing the FAST standard. But even small teams can start getting value right away just by adopting the standard themselves.
Are you aiming to share your model with others? Are you developing this on your own or co-creating this with other financial modellers? Do you expect that from time to time your modellers will come and go, even when models are incomplete? Would you expect that the next modeller who picks up this unfinished work will try to re-model much of it into their own unique style?
Benefits are real
For me, those days are gone. I adopted the FAST standard within my teams years ago. It has meant that all of us could quickly read, understand and extend a model built by anyone else within the team. So readability was a quick win. Next the FAST standard provided best practice at the modelling construction level. It reduced risk, improved efficiency and collaboration.
In conclusion, the mechanics of financial modelling can be optimised quite easily by adopting a standard like FAST. The only thing left for you and your team to focus on are the more important aspects of financial modelling. Not the mechanics but rather getting the business logic right, and getting out all of the salient financial insights.
Want to find out more?
If you are in Melbourne, join us on the 17th of August for the next Melbourne Financial Modellers’ Meetup for “Quest for Best Practice – Introduction to the FAST Standard” where I will be speaking further on this topic.
About the author
David Taylor is Senior Consultant (Freelance) at FAST LANE BI, a Microsoft partner and focused on modern Excel and accessible Business Intelligence. Talk to David about in house training, consulting and development of financial and data models using Microsoft Excel, Power BI and Azure.
Spreadsheet models can become error prone, labour intensive and unable to scale if they are allowed to grow un-checked. A culprit is the VLOOKUP formula.
LOOKUP allows authors to connect and cross-reference between tables of values from one area or document to another.
Workbooks that are reliant upon VLOOKUP formulae can be re-structured to more cleanly combine sets of data using Power BI instead.
VLOOKUP can be eliminated by Power BI using PowerPivot – which is the Excel feature that organizes your information separately within a data model having relationship linkages that are set up once and remain fully established regardless of data updates that may come through over time.
So rather than using VLOOKUP to connect up independent Excel tables the PowerPivot feature places these tables into an Excel data model, or ‘Cube’. Now the relationships are managed properly within the ‘Cube’. This avoids the error situations often encountered using VLOOKUP where spreadsheet cell values are extended or updated but then lookup connections fail.
How then can the values be accessed? Cube values can now be easily be referenced from a spreadsheet formula function called =CubeValue(). Alternatively Pivot Tables can access the Cube.
So there you have it; no more VLOOKUPs!
Why is this better? A Power BI approach creates a scalable solution that can handle tens of millions of rows with ease. It’s also the cleaner approach because you are segregating your data from your calculations.
In a future blog I’ll provide some technical details in a worked example.
Until then; Power on!