Financial Modelling in Power BI: Forecasting Business Intelligently

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