/ Data Analysis / By William Rodriguez
If you haven't done so already, please make sure to review part one of this series: Financial Modeling in Business Intelligence (Part 1), Analytical Ants. Effectively, we are building financial statements and reports based on raw transactional general ledger (GL) data. In this article, we will be procuring the following financial statements and metrics:
Indifferent of the organization or data structure, building financial reports and gleaning insights should be accessible to anyone with raw data and a will to automate and improve their processes.
If you haven't done so already, please reference the section Financial Modeling with Business Intelligence in part one of this series to see the optimal data structure and explore an interactive model | report. Effectively, we are pursuing a star schema with an outrigger designed for financial statements.
In this implementation, we are expanding on these concepts slightly by introducing a second fact table for budgets (more information about multiple fact tables: Handling Multiple fact tables in Power BI, Guy in a Cube). The model still follows star schema best practices and functions in the same way! You can review the Entity Relationship (ER) Diagram below:
An interesting question was posed by a client when presenting this information. Effectively, they asked when to apply a single consolidated fact table versus multiple fact tables. The answer lies in correlation of metrics. If a single fact table can express the same information (grain) and substantially reduce the number of rows, then implement a 'consolidated fact table' (Consolidated Fact Table, Kimball). However, if the grain is different, and there isn't correlation between the tables (i.e., the result looks like a UNION of the tables), it's probably best to keep them separate.
Lastly, it's important to note the many-to-many relationship between the Calendar and Budgets table. The Budgets table is currently at the year granularity. Please note that if budgets are at a different grain (e.g., quarterly, monthly, etc.), then the relationship could change and the budget measure could potentially need adjustments.
The Power Query ETL has remained largely unchanged since the initial model. A quick recap and update:
We will review metrics in the following order: financial statement metrics, financial ratio metrics, and other metrics.
There are two main metrics that drive the financial statements: transactional and cumulative actuals. Profit and Loss and Cashflow statements (and respective accounts!) utilize transactional actuals; Balance Sheet statements and accounts use cumulative actuals.
Transactional actuals simply aggregate based on the filter context (e.g., all transactions between a date range). These transactional actuals are used in the Profit and Loss statement as well as the Cashflow statement where you need to know the account changes over a period (i.e., increases/decreases per period). The logic is extremely simple:
= IF ( 'Chart of Accounts'[Debit Increase Indicator] = 1 , SUM ( 'General Ledger'[DR] ) - SUM ( 'General Ledger'[CR] ) , SUM ( 'General Ledger'[CR] ) - SUM ( 'General Ledger'[DR] ) )
Effectively, if the account increases with debits, add debits minus credits. Else, add credits minus debits. Going forward, we will simply call this metric, "Actuals".
Cumulative actuals are exactly what they infer: cumulative values of all prior transactions. This is valuable for the Balance Sheet and respective accounts where the true balance is a cumulative sum of all prior transactions (until a certain point). The code looks like this:
= CALCULATE ( [Actuals] , FILTER ( ALL ( 'Calendar'[Date] ) , 'Calendar'[Date] <= MAX ( 'General Ledger'[Date] ) ) )
Effectively, find the last available with data, and sum everything up-until that point. Note how the cumulative actuals utilizes the transactional actuals from earlier.
Between the model and prior two metrics, we have everything we need to procure our financial ratios. There are two main components to consider:
We will demo two ratios as examples: Gross Profit Ratio and the Current Ratio
Standard equation: Gross Profit / Revenue
Excel equation:
= CALCULATE ( [Actuals] , 'Financial Statements'[Financial Statement] = "Profit and Loss" , 'Financial Statements'[Category] = "Gross Margin" ) / CALCULATE ( [Actuals] , 'Financial Statements'[Financial Statement] = "Profit and Loss" , 'Financial Statements'[Category] = "Sales Revenue" )
Notes: Notice how (1) we utilize 'Actuals' in the metric as these are 'transactional' accounts. (2) How we are constraining the appropriate accounts on the Financial Statements table. For example, Revenue constrains the Financial Statement column to 'Profit and Loss' and the Category column to 'Revenue'.
Standard equation: Current Assets / Current Liabilities
Excel equation:
= CALCULATE ( [Cumulative Actuals] , 'Financial Statements'[Financial Statement] = "Balance Sheet" , 'Financial Statements'[Category] = "Assets" , 'Financial Statements'[Subcategory] = "Current Assets" ) / CALCULATE ( [Cumulative Actuals] , 'Financial Statements'[Financial Statement] = "Balance Sheet" , 'Financial Statements'[Category] = "Liabilities and Owners Equity" , 'Financial Statements'[Subcategory] = "Liabilities" )
Notes: (1) We utilize the [Cumulative Actuals] metric as these are cumulative accounts. (2) not only constrain the Category to 'Assets', but also the subcategory to 'Current Assets'. This allows us to target and only propagate desired accounts (note, our model only has current liabilities, so all liabilities are selected; we could change this in the future).
Budgets aren't typically codified as 'debits and credits', but rather as a single 'amount'. In this example, budgets are loaded at the account level and based on the final desired outcome. Therefore, we need only one metric to calculate the value based on the account.
The Excel expression is shown below:
= IF ( HASONEVALUE ( 'Chart of Accounts'[Debit Increase Indicator] ) , SUM ( Budgets[Budget Amount] ) , CALCULATE ( SUM ( Budgets[Budget Amount] ) , 'Chart of Accounts'[Debit Increase Indicator] = 0 ) - CALCULATE ( SUM ( Budgets[Budget Amount] ) , 'Chart of Accounts'[Debit Increase Indicator] = 1 ) )
Effectively, if there is a single value for 'Chart of Accounts' [Debit Increase Indicator], then simply add everything up. Because all the accounts go in a similar way, adding the amounts as debits and credits only yields the correct positive amount. However, if there is more than one 'Debit Indicator' then subtract credits minus debits (reference the transactional actuals section for more information on how and why this works).
Variances are super straight forward— either take the delta of Actuals and Budgets or the percent change from Actuals and Budgets. There will be two variance types, one for transactional accounts and another for cumulative.
Below are standard ratio equations as listed in Accounting & Financial Management for Residential Construction, Shinn:
With the right model and metrics, report generation is now a breeze! However, one more step is needed. Each report dedicated to a Financial Statement needs a page level filter on that page. That's it! Now dragging and dropping every metric and attribute will automatically be focused based on your desired financial statement.
Let's work through building the Balance Sheet:
To build the balance sheet, bring over the following fields: 'Financial Statements'[Category], 'Financial Statements'[Subcategory], and 'Financial Statements'[Detail Sorted]. Now, bring over your desired metrics: [Cumulative Actuals] , [Budgets], and [Cumulative Actuals Variance %]. Voilà! You have a Balance sheet that lists accounts and orders then appropriately.
Adding Ratios is even easier. Simply bring over your desired metrics to display KPI cards. You can use the new Card visual to add context such as budgets and conditional formatting.
Lastly, adding trendlines and visuals is also extremely easy. Simply bring over your desired metrics (e.g., [Cumulative Actuals] and [Budgets]) and desired dimensional attributes (e.g., 'Calendar'[Month Year]), filter the visual based on the desired account (e.g., 'Financial Statements'[Description] or 'Chart of Accounts'[Description] = Cash, AP, Inventory, and AR ), and choose your visual type. That's it! No code required. The model and metrics do the heavy lifting for you.
Now that all the visuals are built, you will need to design them in a way that articulates a story and delivers your narrative. Two great resources for building dashboards are the following: Power BI Dashboard Design Video Course, SQLBI and Designing effective report in Power BI, Kurt Buhler.
My final output looks like below, but feel free to create the metrics and visuals that tell your story!
Accounting and Finance are historic disciplines with well-defined rules that are well suited for business intelligence modeling and reporting. We hope you have taken away fundamental understandings from Finance, Accounting, and Business Intelligence to craft your own beautiful financial reports in Excel.
Below are the final screenshots of the financial reports.
Downloadable report available via our store!Free download coupon code: Financial-Reporting-in-Excel-202310
This article was created & published by William Rodriguez, President and Principal Consultant at Analytical Ants
Reach out to Analytical Ants LLC for all your data/analytical needs. We specialize in #DataEngineering #DataScience, and #DataAnalysis.