Financial Modelling in Power BI E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 315

Veröffentlichungsjahr: 2024

Beschreibung

This book introduces readers to the fundamentals of financial modeling using Power BI, starting with an overview of the tool and best practices for creating robust, transparent, and flexible models. Early chapters lay the groundwork by explaining financial statement theory and control accounts, essential concepts for any financial analyst. Readers are guided step-by-step through creating parameters and calculating sales, ensuring a solid foundation in Power BI's core functionalities.
As the book progresses, readers delve into more advanced topics such as inventory calculations, operating and capital expenditures, and tax computations. Practical examples and hands-on exercises make complex concepts like DAX functions, FIFO inventory modeling, and control account measures accessible to users of all experience levels. Detailed sections on cash flow statements, income statements, and balance sheets tie the lessons together, showing how these elements integrate into a comprehensive financial model.
The final chapters explore advanced features like interest and debt modeling, recursion aversion, and equity calculations, culminating in the creation of fully dynamic and optimized models. Readers also learn to design compelling visualizations to present financial insights effectively. By the end of the journey, users will have the tools and confidence to apply their knowledge to real-world scenarios, mastering financial modeling with Power BI.

Leseprobe

Financial Modelling

in Power BI

Forecasting Business Intelligently

Dr. Liam Bastick with Jonathan Liau

Copyright © 2022 SumProduct Pty Limited.

Published in 2022 by SumProduct Pty Limited, Ground Floor, 470 St Kilda Road, Melbourne, VIC 3004, Australia. Simultaneously published in the USA by Holy Macro! Books, PO Box PO Box 541731, Merritt Island FL 32954.

All rights reserved.

Authors: Dr. Liam Bastick with Jonathan Liau

Editor: Kathryn Newitt

Indexer: Bill Jelen

Compositor: Joseph Kirubakaran

Cover Design: Shannon Travise

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-072-3 Print, 978-1-61547-161-4 Digital

Library of Congress Control Number: 2022934126

Version 20220729c

No parts of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, without either the prior written permission of the publisher, or authorisation through payment of the appropriate photocopy fee to the Copyright Clearance Center. Requests for permission should be addressed to the publisher, SumProduct Pty Limited, Ground Floor, 470 St Kilda Road, Melbourne, VIC 3004, Australia, tel: [phone], email: [email protected].

This publication is designed to provide accurate and authoritative information in regard to the subject matter covered. It is sold with the understanding that the publisher is not engaged in rendering public services. If professional advice or other expert assistance is required, the services of a competent professional person should be sought. SumProduct (www.sumproduct.com) is such an organisation that undertakes both tailored training and consulting services. Neither the author nor the publisher is liable for any actions prompted or caused by the information presented in this book. Any views expressed or implied herein are those of the author and do not represent the views of the organisations he works for.

Microsoft and Excel are registered trademarks of the Microsoft Corporation.

About the Author

Dr. Liam Bastick FCA FCMA CGMA MVP

Starting off as a university lecturer, Liam has over 30 years’ experience in financial model development / auditing, valuations, mergers and acquisitions, project finance, public private partnerships, strategy, training and consultancy. Indeed, he has been appointed as an independent expert for the courts of Victoria and New South Wales, in Australia.

He has considerable experience in many different sectors (e.g. banking, energy, media, mining, oil and gas, private equity, retail, transport and utilities) and has worked in many countries (including Australia, Belgium, Denmark, France, Germany, Hong Kong, Indonesia, Malaysia, Netherlands, New Zealand, Philippines, Singapore, Switzerland, United Kingdom, United States and Vietnam). He has worked with many internationally recognised clients, constructing and reviewing strategic, operational, planning and valuation models for many high profile International Public Offerings(IPOs), Leveraged Buy-Outs (LBOs) and strategic assignments.

With over 1,000 articles written for the accounting profession, he is a regular contributor to the American Institute of Certified Public Accountants (AICPA), Chartered Accountants Australia and New Zealand (CAANZ), Certified Practising Accountants Australia (CPAA), the Chartered Institute of Management Accountants (CIMA), the Institute of Chartered Accountants in England and Wales (ICAEW), Finance 3.0, Microsoft’s Excel Blog and various LinkedIn specialist discussion groups.

Liam is a Fellow of the Institute of Chartered Accountants (FCA), a Fellow of the Institute of Chartered Management Accountants (FCMA), a Chartered Global Management Accountant (CGMA), and is also a professional mathematician, specialising in probability and number theory. In 2021, he was recipient of the inaugural Lifetime Achievement Award for financial modelling by the Financial Modeling Institute.

A frequent public speaker, Liam attends (virtually) Excel and Power BI conferences around the globe and has been a central organiser for the Excel Summit South and Excel Virtually Global. He has also authored and edited several books including Introduction to Financial Modelling, Continuing FinancialModelling, as well as the Power BI MVP Book and Excel Insights.

Since 2012, he has been recognised by Crimewatch and Microsoft, the latter as a Most Valuable Professional (MVP) in Excel, one of c.80 such awardees worldwide (as at the time of writing).

He still follows Derby County (if they survive bankruptcy) and the England cricket team(if they survive their current performances).

Jonathan Liau

Jonathan Liau was born in Singapore and lived in China as an ex-pat for most of his formative years. He served in the military in Singapore for two years before enrolling in the University of Sydney, studying Economics, Finance and IT. Until recently, Jonathan was a Senior Analyst at SumProduct Australia, specialising in Power BI projects.

Aside from work, Jonathan enjoys hiking and driving, the casual whiskey with friends, power lifting and tinkering with computers.

Preface

Thank <expletive deleted> that’s over. Advertised forever, over a year late in its delivery, countless technical hurdles, software forever changing, something called COVID-19, a key member of the team leaving midstream, this was not the easiest book to write. On countless occasions, I was advised to scrap trying to do certain parts, but I steadfastly refused. It reminded me of a previous life as a post-graduate researcher…

I have (re)discovered I am still tenacious. They say if at first you don’t succeed, then skydiving is not for you. For me, my reserve, reserve, reserve, reserve parachute failed to open, but fortunately, the ground broke my fall. But not my spirits. So I got inebriated and carried on regardless. I guess there are parts of this book where you will figure that is precisely what I did!

At the risk of sounding arrogant, I do think this is a ground-breaking book – and not just because of my (fictional) parachute accident. Detailed within these pages is a first stab to build a three-way integrated financial model inPower BI.

This book contains no discussion on visualisation. It doesn’t really consider forecasting. It sidesteps opening balances. The model is very simple, but the concepts are deceptively awkward in places (e.g. inventory, depreciation, tax, and heck, even creating a line total!). Others have imported models or created one or two of the financial statements in Power BI, but this includes allthree. This is one small step for a modeller but a giant leap for people who like to steal other people’s quotations.

Please accept this book in the manner it’s intended. This highlights the start of the financial modelling journey in Power BI. I invite fellow explorers to follow me in this expedition – maybe into oblivion – to gain greater insight into your financials, turning disorganised data into structured information, gleaned knowledge and segmented wisdom. It will take time, but it’s a collaborative voyage and I am starting at Base Camp (once I have tidied up all my parachutes).

I have been lucky enough to have been appointed a Most Valuable Professional (MVP) by Microsoft for services to Excel – one of 80 or so – recognised "experts" as at the time of writing. Last year, I was fortunate enough to be awarded the inaugural Lifetime Achievement Award for Financial Modelling by the Financial Modeling Institute (yes, the spellings are deliberate!) too. In my acceptance speech, I promised I wasn’t about to retire / go out to pasture. I insisted I was off to explore as noisily as possible the Brave New World of financial modelling in Power BI. Well, I’ve done it. See what you think!

I’d like to thank those that helped contribute to this book over the years (this one has been three years in the making). Thanks to Bill Jelen for sticking with me (this book has taken an eternity to put together), Kathryn Newitt for technically editing it and ignoring all the dad jokes, Jonathan Liau for the initial proofs of concept, Tim Heng and the SumProduct team for proofreading this thing to death (we’ve probably still missed things as you, dear reader, will be delighted to point out).

Finally, and most importantly, sincerest thanks to my immediate family, Nancy and no-longer-so-little Layla, who have always supported me – thinking that this book is going to earn them millions (good luck with that, gang). To that end, yet again, I let my 13-year old daughter, Layla Bastick, have the last (fore)word:

"So, he’s written another book. Whether it’s for the better or worse, I have no idea as I am yet to read it, but he has spent a lot of time on this so it’s probably going to be, in the very least, interesting. I apologise in advance for the terrible jokes and puns in this book, but I have to admit they are funny. I am proud of my dad, especially with how long he has taken on writing this book and the effort he has put into it."

Liam Bastick, March 2022

www.sumproduct.com

Editor’s notes

A few years ago, when we were undergraduates, I would studiously produce long and very boring assignments. I would be amazed to see the two sides of hand-written A4 that Liam would produce. Much to my frustration at the time, he always got better grades than me! That one piece of paper was always full of original and relevant ideas.

That is true of this book too, with the exception of the jokes. It is a brilliant idea, and it’s taken a lot of hard work to complete. To get the most out of this book, you need to build the financial model. Start with our deliberately simple data set and work through the steps. Part of my role as editor has been to build the model repeatedly, and I can attest to the satisfaction of completing the Balance Sheet and admiring the result.

This is just the starting point. This is Power BI. The Financial Statements are more than just lines of data, they can be refreshed and drilled into, and they are fully integrated. All the visualisations you could wish for are at your disposal, along with insights and suggestions.

Since nothing like this has been successfully achieved before, your constructive comments and questions would be welcome. Less positive comments will be filtered before reading: I know how to clean up data.

So please, join us on a journey of discovery and find out how to build your Financial Statements in the intuitive and insightful world of Power BI. We hope you will get results as excellent as Liam’s assignments.

Kathryn Newitt

www.sumproduct.com

(Unsolicited response from Liam: As always, Kathryn is far too modest. She was always the smarter of the two of us – and the more industrious. I spent my final year as an undergraduate trying to emulate her sheer dedication and diligence. If it wasn’t for her, I would never have finished my academic studies in the first place. Ask any of my friends: I have always said that. Thank you Kathryn. You are far too generous with me.)

Downloadable Resources

Getting to grips with new ideas can be challenging. The easiest way to explore this book, is to use the same data file as us, and check your progress against the many images provided. We have set up a page on our website, where we will (for as long as our website and the internet as we know it still exist) provide the data file, example Power BI files and any other material that we think would be useful.

Head to our website at https://www.sumproduct.com/book-3-resources to download any files referred to in this book, as well as any additional information and examples.

Contents

About the Author

Preface

Editor’s notes

Downloadable Resources

CHAPTER 1: Introduction

CHAPTER 2: Introduction to Power BI

Getting Power BI

And finally…

CHAPTER 3: Best Practice Methodology

Robustness

Flexibility

Transparency

In summary

Chapter 4: Financial Statements Theory

Income Statement

Balance Sheet

Cash Flow Statement

Linking financial statements

Appropriate order of the financial statements

CHAPTER 5: Control Accounts

Building a Financial Model in Power BI

CHAPTER 6: Getting Started

Power BI keeps updating!

Unable to open document (old PBI version)

Privacy warning

Programming languages

Star schema

Introducing the data

Using Windows Settings to control Power Query date display

Opening the Power Query editor

FilePath

Source data

Referencing a query

Reliable references

Source tables

Depreciation

Grouping queries

Dividends

Equity

Actuals

Appending queries

Close & Apply

Auto Date / Time

Date Table

StartDate

EndDate

Calendar

Creating a Fulldates query

Relationships

Cross filter direction

Cardinality

Control account measures and financial account measures

Memory usage

Table.Buffer

Table.View() optimisations

Dividends Table.View step

Equity Table.View step

Calendar Table.View step

CHAPTER 7: Creating Parameters

Days receivable

Back to creating parameters

CHAPTER 8: Calculating Sales

The SUM function

The CALCULATE function

The DATEADD function

Sales cash receipts measure

The FILTER function

Power BI – CALCULATE function update

The ALL function

The MAX function

Cumulative sales measures

Sales control account

CHAPTER 9: Formatting Matrix Visualisations

CHAPTER 10: Calculating COGS (Part 1)

CHAPTER 11: VAR Variables

Considerations with variables

CHAPTER 12: Calculating Inventory (FIFO)

Important side note

Creating the inventory query

Creating the inventory query (continued)

The SUMX function

The MAXX function

Inventory (FIFO) DAX columns

Purchases control account

Inventory control account

CHAPTER 13: Average Inventory Calculation

How it works

Why we aren’t using this

Average inventory table setup

List.Buffer

Average inventory custom function

Inventory cost table query

Average inventory calculation query

Table.NestedJoin vs. Table.Join

Average inventory cost control account

CHAPTER 14: Calculating COGS (Part 2)

CHAPTER 15: Calculating Operating Expenditure (Opex)

CHAPTER 16: Calculating Capital Expenditure (Capex)

Accounting depreciation

Creating the depreciation function

Creating the depreciation table

Capex control account

CHAPTER 17: Calculating Debt

The 3 R’s of debt modelling

Returning to the case study

CHAPTER 18: Calculating Interest

Capitalised vs. rolled up

Avoiding circularity

Returning to the case study

Calculating the cumulative debt drawdown

Calculating the interest control account

CHAPTER 19: Income Statement (Part 1)

Gross Profit

Creating blank lines

EBITDA

EBIT

NPBT

CHAPTER 20: Calculating Tax (Part 1)

Liam’s Law of Tax

Creating the tax depreciation table

Creating the custom function for tax depreciation

Creating the tax depreciation table (continued)

Creating the tax measures (depreciation timing difference)

Creating the tax measures (tax losses memorandum)

CHAPTER 21: Recursion Aversion

Tax recursion workaround method A

Tax recursion workaround method B

CHAPTER 22: Calculating Tax (Part 2)

Calculating the DTA control account

Tax payable and paid

Tax control account

CHAPTER 23: Income Statement (Part 2)

CHAPTER 24: Calculating Equity and Dividends

Creating the equity control account

Creating the dividends control account

CHAPTER 25: Cash Flow Statement

Operating Cash Flows

Investing Cash Flows

Financing Cash Flows

Net increase / (decrease) in cash held measure

Indirect cash flow extract

CHAPTER 26: Balance Sheet

Calculating Total Assets

Calculating Total Liabilities

Calculating Equity

Checks

CHAPTER 27: And Finally…

Index

CHAPTER 1: Introduction

They say third time’s the charm.

You may or may not be familiar with the first two books in this series, Introduction to FinancialModelling and Continuing Financial Modelling. These weighty tomes covered how to model in Excel. But time moves on. And more and more of us are realising that whilst the humble spreadsheet is a powerful enabler, there’s a young upstart in town, gaining traction every day.

Power BI – Microsoft’s self-service business intelligence and data visualisation tool – is becoming both more versatile and more popular each month. Today’s analysts need to use the best, most powerful and flexible analytical software for their work. More and more of us are realising Power BI fits the bill. We’d be foolish to stand still.

This book addresses the way forward for financial modelling. Many working in banking and finance are creating their financial models in Excel and then importing them into Power BI for graphical interpretation and further analysis. I am not going to do that. I’m going to build the model in Power BI.

I can’t stress how far off the range we’re taking the horses. If you are reading this, you are a true pioneer. I know of one or two IT programmers building the odd financial statement in Power BI, but all three? This is where you can gain a major advantage in the workplace.

Why?

If I build the calculations for financial statements in Power BI, I can produce statements by product, by customer, by geography, by … Get the picture? The limitation will be restricted to the granularity of the underlying data. If you build the statements elsewhere and import them in, how do you propose drilling down to that level of detail?

Newsflash: you can’t.

Don’t get me wrong; there is nothing wrong with building financial models in Excel or else downloading them from some third party management information system. This is just the next step. It’s evolution. You can carry on building interactive spreadsheets if you wish. The dinosaurs were useful but did not inherit the Earth. Instead, they power our economies and pollute our skies. Or you can start the revolution of evolution. In just a few short years, Power BI has come from nowhere to become the business intelligence software of choice. Want to push it to its limits?

I thought so.

So, what’s this book about? I am planning to take you on a journey of discovery. You will realise my jokes are very bad (having a co-author has had little effect) and that Power BI can do more than you might think. I will have to cover some old ground for those that have read this book’s predecessors, as I will have new readers. If you are a returning reader, don’t let that put you off: approaches may be similar, but precise techniques will differ.

This is NOT a "basic" book on Power BI. If you know nothing about Power BI, you might be able to follow the gist here, but I would strongly advise reading an introductory text first. All functions used here will be explained, but not in copious detail, as and when they are needed (almost as "asides"). Knowing your way around Power BI is "assumed knowledge". Having said that, most of the formulae used do not require a PhD in Mathematics either.As always with these books, the devil is in the concept, not the detail.

The plan for this book is as follows:

Power BI introduction:Just in case you have been living under a rock for the past millennium, I provide a brief overview of Power BIDiscussion on "Best Practice" methodology: To be honest, just getting the model to work in Power BI may seem a sufficient challenge, but here I remind what constitutes "Best Practice" financial modelling at a high-level. This will help keep these principles front and centre when developing our modelFinancial statements theory:I tell this joke every time, but it’s no secret that it was the phrases "double entry" and "working with models" that attracted me to this profession. How disappointed was I? On a serious note though, I want to revisit the key outputs of a financial model to fully understand what "three-way integrated" means and the ramifications for the modeller. Further, I actually go back to understand what is an Income Statement, a Balance Sheet and a Cash Flow Statement. Yes, you may know what they are – but I want to do it from the perspective of understanding the purpose of each statement so that it guides you in determining the order of building a financial model. No matter what you build, the derived order may be applied to all future model developmentsControl accounts: Just like the Excel version of this topic, it all revolves around control accounts, i.e. analysing what causes movements in Balance Sheet items. Using this very simple approach – adopted throughout this book – you will find Balance Sheet errors will become a thing of the pastGetting started in Power BI: This section doesn’t deal with installation points (that was earlier, see above); this section is more about tips on setting up the data, so that loading and calculation times are not unnecessarily long, as well as considering parameterisation for scenario and sensitivity analyses at the outset. These are key considerations if working with voluminous dataExample of a model build: Oh yes, it might be an idea to actually build up a case study, and this constitutes the main body of this text. The approach will be methodical and discuss the order of calculations and alternative methods available at times. Formulae will chiefly be built in DAX, rather than M, for reasons that will become clear later in the book.

As always, let me stress one last thing: this book is a practical book. There are lots of supporting examples to play with and use, to visualise the important concepts discussed here. The aim is to understand the concepts, not the specifics, because you will never build this case study again in your life – but you will want to apply the lessons learned to your own scenarios.

So, no excuses, make sure you are sitting comfortably and open up Power BI. It’s a great visualisation tool, but now you and I, dear reader, we are going to pummel it into submission and make it a modelling tool too.

Let’s get going!

CHAPTER 2: Introduction to Power BI

Power BI is a business intelligence (BI) tool developed by Microsoft for the purposes of data preparation, analysis and visualisation. Unlike many other business intelligence and data visualisation tools on the market, it offers the analyst a user-friendly environment to connect to and transform their datasets, without needing to rely upon IT departments to obtain the results for them.

When people look at Power BI, they see it as being valuable for several different reasons:

Data transformation and cleansing The first step of the business intelligence process is to collect data and ensure that it is in a format that is ready for analysis. This is also known as data cleansing. This process involves taking a raw set of data and removing any errors, inconsistencies and inserting any calculation steps or transforming the data in order to prepare it for presentation and analysis.

Often, this process is conducted wherever the data is being stored. If it is being stored in a database, there are usually queries or views that will present the final, cleaned dataset for BI tools to use. If data is stored in an Excel file, a process will usually require a user to open the file and modify the contents prior to loading it into the BI process.

Power BI gives users the ability to quickly and simply adapt and transform data using the Power Query editor. This is the same technology that you find in Excel (also sometimes known as Get and Transform), but slightly newer and improved. Typically, Power Query in Power BI tends to be six to 12 months ahead of the equivalent tool in Excel, with new functions and features rolled out first.

Data visualisation Once data is cleaned, it can be presented in the form of reports and dashboards. Power BI distinguishes reports as being a series of visualisations that are connected back to a single dataset, whereas dashboards are considered to be summaries of multiple reports. Power BI allows you to generate reports using a combination of visualisations.

While most software tools will give you a wide range of visualisations to use, including charts, tables, maps and so on, Power BI has a unique capability allowing you to download custom visuals that have been created by the community. These custom visuals let you adapt your reports and customise them beyond the built-in charts and tables.

Sharing insights across a team The primary thing people consider Power BI for is to share reports and dashboards that have been created across their wider team. This may be shared to individuals, team workspaces or even embedded into a corporate intranet or external website. Sharing in Power BI has the additional benefit in that roles can be set up to restrict the ability of shared users to access information.Self-service business intelligence Finally, many people are switching to Power BI to allow users to create their own reports. Traditionally, BI tools are managed by IT teams, with reports and dashboards that are pre-defined, and require IT change requests to adjust or to create new ones. Power BI is designed for users to be able to swiftly create their own reports and adjust existing ones, provided that they have access to the dataset.

With all the things that people expect Power BI to do, it needs to be extremely capable. As such, there are several key elements to Power BI that enable it to perform all of these tasks in different ways.

Power BI Desktop This is the tool where a Power BI user will perform the heavy lifting of data transformation and cleansing. Using the Power Query editor to work with data and using Power Pivot technology to relate tables and perform complex aggregations and time-series calculations, Power BI Desktop is where most of the changes and insights are generated. While the visualisation engine is relatively seamless and can display historical records without fuss, any calculations that might be required to report on KPIs or metrics that do not exist in the dataset will need to be created using Power BI Desktop.

Power BI Desktop also has the greatest range of database services that it can connect to. Online or on-premises, you can connect to a range of files, databases, online services or anything that has an ODBC connection.

Power BI Desktop is used for data transformation and cleansing, followed by data visualisation. Once reports have been prepared, the data needs to be published to the Power BI Service in order to share these with colleagues.

Power BI Service This is the formal name for the website version of Power BI that you can log into. It lacks the data transformation and calculation capabilities of Power BI Desktop, but it is a simpler process to connect to certain data sources, even if it doesn’t have the flexibility and range of data services.

The primary role of the Power BI Service is to distribute reports and dashboards and any other insights that might be generated. Once a report has been uploaded to the Service, it can be shared with individuals or with entire teams.

The Power BI Service is also where you can run insights and analytics tools over your datasets. Two key features – Get Quick Insights, and Q&A – are useful at generating quick visualisations to inspire further research or answering spur-of-the-moment questions.

Power BI Mobile Power BI Mobile is the phone and tablet version of the Power BI Service. It loses further functionality (you can no longer create new datasets and reports) but it allows users to move away from their desktop environment and quickly pull open their reports and dashboards in a portable interface.Power BI Gateway Once data is published to the Power BI Service, it sits there until it is subsequently refreshed. Although you can refresh it manually by republishing the data, you can also set it up to be automatically refreshed on a schedule. For online data sources, this is straightforward. However, for on-premises data sources such as SQL servers and Excel files, this requires a gateway to be installed on an on-premises computer that effectively connects the Power BI Service with the local data sources.Power BI Report Server Designed for those companies that don’t want their data being stored in the cloud, the Power BI Report Server is a local version of the Power BI Service that can be installed on a computer. It provides data privacy and control, at the expense of more limited functionality.

Getting Power BI

If you are completely new to all this, obviously, the first thing you need is Power BI. From above, you can see there are two parts:

1.Power BI Desktop:where the main modelling work will be undertaken, all done from the familiarity and comfort of your own computer

2.Power BI Service: the website version has three main variants, colloquially known as Power BI Free, Power BI Pro and Power BI Premium.

Here’s how you get what you want…

POWER BI DESKTOP

There are two main ways to get Power BI Desktop. The first way is to head to powerbi.com, follow the links to the Power BI Desktop page, and download it. This requires you to regularly go back to the page to download the latest updates, in order to access the latest and greatest features.

The other option is to download it from the Microsoft Store in Windows. This will keep the software automatically updated through the Windows Update process. However, this version is generally a slightly older version, which trades off access to the latest features for a more stable working environment.

Most importantly, it needs to be noted that Power BI Desktop is absolutely free. No matter how you use it or where you get it from, it will cost nothing at all.

POWER BI FREE

The Power BI Service is free to use as an individual. Go to the powerbi.com website, sign up for an account using a corporate email address (i.e. no Gmail, Hotmail, etc.), and you’re good to get started.

There are a few restrictions with using the free license. The key difference between free and paid versions is that the free version will not allow you to share your dashboards, reports and datasets with other individuals, whether they are a part of your team or not. This essentially means that your free Power BI Service is a tool for self-reporting only, though there is nothing stopping you from emailing the Power BI Desktop file to a colleague, and letting them upload the dataset to their own personal Power BI Service.

POWER BI PRO

Power BI Pro is the paid version of your Power BI Service access. As implied earlier, this gives you the ability to share your dashboards, reports and datasets with your colleagues (and others). This also gives you the ability to create template reports and datasets for your colleagues to use. This is best for smaller businesses (i.e. where there are less than c.300 staff who would use Power BI) who may use the Service only occasionally.

POWER BI PREMIUM

Power BI Premium is an enterprise solution that enables users to cost-effectively have hundreds or thousands of users without paying for a full Power BI Pro license for each of them (you cheapskates: it’s a wonder Microsoft can make ends meet). Given that the vast majority of users in an organisation are likely to be report consumers, and only a handful, usually finance and IT, are likely to be power users, the Power BI Premium service provides dedicated capacity for an organisation at a fixed fee, with Pro licenses only required for those power users.

The Power BI Premium license also gives you access to Power BI Report Server, which enables you to host your dashboards and reports onsite instead of the cloud, meaning that you can work around any privacy and data policy restrictions that might stand in the way of using Power BI. This is only really cost effective if you have several hundred users in an organisation, however.

And finally…

I will mainly be focused on Power BI Desktop in this book as this is where analysts will spend the majority of their time. While the other tools are important for their respective purposes, I will refer to them as and when I need to.

CHAPTER 3: Best Practice Methodology

I am not working in Excel this time, but the principles of "Best Practice" modelling in Excel do still transfer over to Power BI and remain pertinent, so a quick summary is probably worthwhile. The whole intention of this book is to provide a text that can be handed to someone who is looking to build financial models as part of their role. Jokes aside, I am assuming for those of you with no more than a passing acquaintance with Power BI and accounting, this book will get you – as the (would-be?) modeller – up to speed with the requirements and concepts associated with financial model development.

Modellers should strive to build "Best Practice" models. Here, I want to avoid the semantics of what constitutes "best" in "Best Practice". "B" and "P" are in capitals deliberately as I see this as a proper noun insofar no method is truly "best" for all eventualities. There’s plenty of texts out there that include copious amounts on what thou shalt and shalt not do regarding building a spreadsheet. They all have one thing in common: they are not right for all occasions.

I would rather consider the term as a proper noun to reflect the idea that a good model has four key attributes:

Consistency;Robustness;Flexibility; andTransparency.

Our company calls this CRaFT. We try to keep it simple. Looking at these four attributes in turn can help model developers decide how best to design financial models.

Consistency

Models constructed consistently are easier to understand as users become familiar with both their purpose and content. This will in turn give users more comfort about model integrity and make it easier to add / remove business units, categories, numbers of periods, scenarios etc.

Consistent formatting and use of styles cannot be over-emphasised. Humans take in much information on a non-verbal basis. Power BI is a fantastic visualisation tool. Use it!

Those of you familiar with the models supplied with this book may now realise I exploit this mindset: the variables and parameters used to vary outputs and perform what-if? analysis all use slicers, option buttons, timelines and other such obvious visual tools. Nobody ever reads instructions – so make everything so simple a child could use it. And probably delete it.

There are other key elements of a workbook that should be consistent. These include:

Formulae should be copied uniformly across ranges, to make it easy to add / remove periods or categories as necessary (this is pretty much forced in Power BI’s table and matrix visualisations)Sheet titles and hyperlinks should be consistently positioned to aid navigation and provide details about the content and purpose of the particular sheetFor forecast spreadsheets incorporating dates, the dates should be positioned uniformly, the number of periods should be consistent where possible and the periodicity should be constant (the model should endeavour to show all sheets monthly or quarterly, etc.). If periodicities must change, formulae must still work. That’s a key issue here, which differentiates Power BI from Excel. I must model at the lowest level of granularity and aggregate, otherwise calculations will not work. But more on that later.

This should reduce referencing errors, increase model integrity and enhance workbook structure. Talking of which…

Robustness

Models should be materially free from error, mathematically accurate and readily auditable. Key output sheets should ensure that error messages such as #DIV/0!, #VALUE!, #REF!, #Brown, #Pipe etc. cannot occur (ideally, these error messages should not occur anywhere).

Removing these prima facie errors is straightforward and often highlights that the modeller has not undertaken a basic review of their work after completing the task. When building, it is often worth keeping in mind hidden assumptions in formulae. For example, a simple gross margin calculation may calculate profit divided by sales. However, if sales are non-existent or missing, this calculation would give #DIV/0! This is where Power BI is great: the Data Analysis eXpressions (DAX) function takes all of that into account – so use it!

Flexibility

One benefit of modelling in a software package such as Power BI is to be able to change various assumptions and see how these adjustments affect various outputs.

Therefore, when building a model, the user should consider what inputs should be variable and how they should be able to vary. This may force the model builder to consider how assumptions should be entered. Most of the time this will be by using slicers, for example.

I strongly recommend that all inputs are entered as positive numbers, wherever possible, just change the descriptions accordingly. If I were to tell you that last year, costs were $10,000 but they have increased by 10% this year. You would understand me. But what would you make of me telling you costs were minus $10,000 and had increased by -10%!?

The aim is to have a model provide sufficient flexibility without going overboard.

Transparency

As stated above, many modellers often forget that key decision makers base their choices on printed materials: consequently, models must be clear, concise, and fit for the purpose intended. I always say if you can follow it on a piece of paper (i.e. no Formula Bar), it’s transparent.

Most Excel users are familiar with keeping inputs / assumptions away from calculations away from outputs. However, this concept can be extended: it can make sense to keep different areas of a model separate, e.g. revenue assumptions on a different worksheet from cost(s) of goods sold assumptions, and capital expenditure assumptions on a third sheet, and so on. I have many control accounts to construct as part of the case study; it will be best to keep them separate from the rest of the model, in order to avoid confusion.

In summary

It’s all about design and scoping. The problem is, we just like to get on with it. Consequently, we dust off old templates, fit square pegs into round holes and produce mistake-laden spreadsheets time and time again resulting in costly management decisions. But we can’t do that this time. Modelling in Power BI is new. NO ONE is doing this. They might say they are, but all they are doing is importing models into Power BI and then rendering cool visualisations. But let me be clear: that’s not what we are doing. Therefore, since we have no old habits to perpetuate, we can try and set a good example from the outset!