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.
Author(s): Liam Bastick, Jonathan Liau
Publisher: Holy Macro! Books
Year: 2022
Language: English
Pages: 353
City: Chicago
Cover
Title page
Copyright
About the Author
Preface
Editor’s notes
Downloadable Resources
Contents
CHAPTER 1: Introduction
CHAPTER 2: Introduction to Power BI
Getting Power BI
And finally…
CHAPTER 3: Best Practice Methodology
Consistency
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
