Financial Modelling in Power BI: Forecasting Business Intelligently

by Jonathan Liau, Liam Bastick

Overview

Just like a shovel, this book is genuinely ground-breaking. It hits you over the head with the proverbial gardening tool, implementing the way forward for financial modelling. Many working in banking and finance create their financial models in Excel and then import them into Power BI for graphical interpretation and further analysis. Not on our watch. We’re going to jettison the universal spreadsheet and build the entire model in Power BI. We can’t stress how far off the range we’re taking the horses. If you are reading this, you are a true pioneer. Some have managed to build the odd financial statement in Power BI, but all three? This is where you can gain a major advantage in the workplace. If you build the calculations for financial statements in Power BI, you can produce statements by product, by customer, by geography... Get the picture? The limitation will be restricted to the granularity of the underlying data and your imagination. This book unearths some of the tricks, measures, logic and tools needed to build the model (there is no need to bury your mistakes). We just can’t promise you a rose garden... With the usual jokes in spades, it’s just a shame we couldn’t get Doug (get it?) to assist.

Product Details

ISBN-13:	9781615470723
Publisher:	Holy Macro! Books
Publication date:	09/01/2022
Pages:	338
Product dimensions:	6.50(w) x 10.00(h) x 0.70(d)

About the Author

Starting off as a university lecturer, Dr. Liam Bastick 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. 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.

Table of 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

StartDate 56

EndDate 58

Calendar 59

Creating a Fulidates query 64

Relationships 66

Cross filter direction 67

Cardinality 68

Control account measures and financial account measures 68

Memory usage 69

Table.Buffer 69

Table.View() optimizations 73

Dividends Table.View step 79

Equity Table.View step 80

Calendar Table.View step 82

Chapter 7 Creating Parameters 84

Days receivable 84

Back to creating parameters 84

Chapter 8 Calculating Sales 93

The SUM function 104

The CALCULATE function 105

The DATEADD function 107

Sales cash receipts measure 111

The FILTER function 115

Power BI - CALCULATE function update 118

The ALL function 118

The MAX function 120

Cumulative sales measures 121

Sales control account 122

Chapter 9 Formatting Matrix Visualisations 126

Chapter 10 Calculating COGS (Part 1) 131

Chapter 11 VAR Variables 141

Considerations with variables 144

Chapter 12 Calculating Inventory (FIFO) 147

Important side note 147

Creating the inventory query 148

Creating the inventory query (continued) 156

The SUMX function 156

The MAXX function 157

Inventory (FIFO) DAX columns 158

Purchases control account 167

Inventory control account 170

Chapter 13 Average Inventory Calculation 174

How it works 174

Why we aren't using this 175

Average inventory table setup 176

List.Buffer 180

Average inventory custom function 186

Inventory cost table query 191

Average inventory calculation query 192

Table.NestedJoin vs. Table.Join 195

Average inventory cost control account 202

Chapter 14 Calculating COGS (Part 2) 206

Chapter 15 Calculating Operating Expenditure (Opex) 209

Chapter 16 Calculating Capital Expenditure (Capex) 212

Accounting depreciation 214

Creating the depreciation function 217

Creating the depreciation table 225

Capex control account 228

Chapter 17 Calculating Debt 232

The 3 R's of debt modeling 232

Returning to the case study 233

Chapter 18 Calculating Interest 237

Capitalised vs. roiled up 237

Avoiding circularity 237

Returning to the case study 238

Calculating the cumulative debt drawdown 238

Calculating the interest control account 248

Chapter 19 Income Statement (Part 1) 252

Gross Profit 252

Creating, blank lines 255

EBITDA 255

EBIT 256

NPBT 257

Chapter 20 Calculating Tax (Part 1) 259

Liam's Law of Tax 260

Creating the tax depreciation table 263

Creating the custom function for tax depreciation 265

Creating the tax depreciation table (continued) 275

Creating the tax measures (depreciation timing difference) 277

Creating the tax measures (tax losses memorandum) 281

Chapter 21 Recursion Aversion 287

Tax recursion workaround method A 287

Tax recursion workaround method B 290

Chapter 22 Calculating Tax (Part 2) 294

Calculating the DTA control account 294

Tax payable and paid 295

Tax control account 298

Chapter 23 Income Statement (Part 2) 302

Chapter 24 Calculating Equity and Dividends 303

Creating the equity control account 304

Creating the dividends control account 308

Chapter 25 Cash Row Statement 311

Operating Cash Flows 311

Investing Cash Flows 314

Financing Cash Flows 315

Net increase / (decrease) in cash held measure 317

Indirect cash flow extract 318

Chapter 26 Balance Sheet 321

Calculating Total Assets 322

Calculating Total Liabilities 324

Calculating Equity 327

Checks 331

Chapter 27 And Finally 334

Index 335