Financial Modelling in Power BI: Forecasting Business Intelligently

354 Pages·2022·29.262 MB·English

by  Liam Bastick, Jonathan Liau

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 20220729a
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 Financial Modelling, 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 in Power 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 all three. 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 ����������������������������������������������������������������������������������������������������������iii
Preface ��������������������������������������������������������������������������������������������������������������������������iv
Editor’s notes ����������������������������������������������������������������������������������������������������������������vi
Downloadable Resources �������������������������������������������������������������������������������������������vii
CHAPTER 1: Introduction ���������������������������������������������������������������������������������������������1
CHAPTER 2: Introduction to Power BI �������������������������������������������������������������������������3
Getting Power BI .......................................................................................................5
And finally… ..............................................................................................................6
CHAPTER 3: Best Practice Methodology ��������������������������������������������������������������������7
Robustness ...............................................................................................................8
Flexibility ...................................................................................................................8
Transparency ............................................................................................................8
In summary ...............................................................................................................9
Chapter 4: Financial Statements Theory ��������������������������������������������������������������������10
Income Statement ...................................................................................................10
Balance Sheet .........................................................................................................13
Cash Flow Statement ..............................................................................................16
Linking financial statements .....................................................................................20
Appropriate order of the financial statements ..........................................................20
CHAPTER 5: Control Accounts �����������������������������������������������������������������������������������22
Building a Financial Model in Power BI ....................................................................23
CHAPTER 6: Getting Started �������������������������������������������������������������������������������������25
Power BI keeps updating! .......................................................................................25
Unable to open document (old PBI version) .............................................................25
Privacy warning .......................................................................................................26
Programming languages .........................................................................................28
Star schema ...........................................................................................................28
Introducing the data ................................................................................................29
Using Windows Settings to control Power Query date display .................................30
Opening the Power Query editor .............................................................................33
FilePath ...................................................................................................................34
Source data ............................................................................................................36
Referencing a query ................................................................................................39
Reliable references ..................................................................................................40
Source tables ..........................................................................................................43
Depreciation ............................................................................................................43
Grouping queries ....................................................................................................46
Dividends ................................................................................................................48
Equity .....................................................................................................................49
Actuals ....................................................................................................................50
Appending queries ..................................................................................................51
Close & Apply .........................................................................................................52
Auto Date / Time .....................................................................................................53
Date Table ...............................................................................................................56