Introduction and Statement of Comprehensive Income
Introduction
Due to their very nature, the Statement of Comprehensive Income (SOCI), Statement of Financial Position (SOFP) and Statement of Cash Flows (SOCF) require different treatment from a modelling approach. Namely, we need to use the appropriate type(s) of FACT table. Fortunately, the Kimball based methodology gives us a strong theoretical launchpad from which to begin; we can adapt this theory to modern day Power BI solutions.
Throughout this series, it will become clear that that there is also more than 1 way of applying Kimball to the Financial Statements and which one is appropriate depends on the scenario. Therefore, it is important to understand the options in order to correctly align with your organisations approach.
Part 1 begins with the Statement of Comprehensive Income
Part 1: Statement of Comprehensive Income
For the SOCI it is advisable to use a TRANSACTIONAL Fact table the definition for which is:
A row in a transaction fact table corresponds to a measurement event at a point in space and time. Atomic transaction grain fact tables are the most dimensional and expressive fact tables; this robust dimensionality enables the maximum slicing and dicing of transaction data. Transaction fact tables may be dense or sparse because rows exist only if measurements take place. These fact tables always contain a foreign key for each associated dimension, and optionally contain precise time stamps and degenerate dimension keys. The measured numeric facts must be consistent with the transaction grain.
Kimball & Ross - The Data Warehouse Toolkit 3rd edition and Kimball Online
To further explain, let's use an example of a Coffee Shop where the following records appear in the accounts:
Coffee Shop General Ledger
Date | ProductKey | LedgerKey | Quantity | Amount |
|---|---|---|---|---|
14th Mar 2022 | 101 | 10100 | 1,000 | £2,000 |
14th Mar 2022 | 101 | 20100 | 1,000 | £50 |
14th Mar 2022 | 102 | 10100 | 1,500 | £3,150 |
14th Mar 2022 | 102 | 20100 | 1,500 | £300 |
14th Mar 2022 | 401 | 30100 | 100 | £30 |
14th Mar 2022 | 401 | 30200 | 0 | £10 |
In this example, "ProductKey" and "LegerKey" are Foreign Keys in the model and amounts for costs are in Positive Numbers; this is likely how it would look in practice. However, for simplicity lets strip out the Foreign Keys and replace with the Product and Ledger Name as well as change the signage which we would commonly do from the ledger dimension with a multiplier (i.e., all Cost ledgers would be multiplied by -1):
Coffee Shop General Ledger (adapted)
Date | Product Name | Ledger Name | Quantity | Amount |
14th Mar 2022 | Americano | Revenue | 1,000 | £2,000 |
14th Mar 2022 | Americano | Cost | 1,000 | (£50) |
14th Mar 2022 | Latte | Revenue | 1,500 | £3,150 |
14th Mar 2022 | Latte | Cost | 1,500 | (£300) |
14th Mar 2022 | Coffee Beans | Waste | 100 | (£30) |
14th Mar 2022 | Coffee Beans | Rebate | 0 | £10 |
Because this is a transactional fact table we can sum up the "Amount" in this table and we have our Gross Profit of £4,780 for 14th Mar 2022.
If we go back to the first line in our Kimball definition:
A row in a transaction fact table corresponds to a measurement event at a point in space and time
Kimball & Ross - The Data Warehouse Toolkit 3rd edition and Kimball Online
Looking at the first row in our table, we can translate this in our example as:
The first row in the example transaction fact table corresponds to 1,000 Americano's sold for the amount of £2,000 on the 14th Mar 2022
Key Observations
1) This isn't Atomic Level Granularity
Ideally, your data would contain these transactions at the lowest possible grain. For instance, rather than having a line showing all 1,000 Americano's sold on one day, you would have a separate line recorded for each transaction. This would allow the most flexibility from an analytical point of view. For instance, you could analyse by time of day.
Power BI can handle billions of rows of data (if done right) and you can use aggregations to ensure your model is performant over these volumes. Atomic level grain for SOCI does, however, introduce another problem of multi-grain fact tables (i.e., Revenue is by customer, but Waste cost is not) which we will tackle later in the series.
2) This doesn't necessarily answer the questions your users want answering
How profitable is my Coffee is hard to answer even if you get the lowest level of granularity, because not all cost ledgers are recorded at the appropriate level of granularity. For instance, if you look at profitability of Americano you can't simply subtract Revenue from Cost; there are other costs such as the Coffee Beans waste which aren't factored in. There are ways of handling this (such as Activity Based Costing), but it's worth paying close attention to what questions your users want to be able to answer from your model. You may have to build in additional logic to your data before bringing it into Power BI, such as to allocate the Coffee Beans waste to each product consuming Coffee Beans (Americano and Latte) based on the volume sold.
3) The quantities have to be handled with care
You cannot sum up the quantity in the above example because the same quantity is recorded twice; once to record the revenue another to record the cost. This is the type of error that occurs without careful attention to the details and nuances of how your accounts are structured.
4) The TRANSACTIONAL fact table is sparse
The fact table does not contain a row for every possible combination of the foreign keys. For instance, if no Americanos where sold on 14th March, the fact wouldn't contain a line for revenue with a zero against the amount and quantity. As we'll see in later articles, not all fact tables are sparse.
5) The ratios are not stored, but can be calculated
Ratios, such as gross profit, are not held in the model but the information to calculate them is. For instance, you wouldn't hold in the model that gross profit is 80% on 14th March 2022 and 82% of 15th March 2022, instead you would hold the numerator and denominator of the fraction. This allows for more precise calculations, for instance, the gross profit across the 2 days cannot simply be calculated by taking the average of 80% and 82% and coming up with 81%.
6. The fact table is semi-additive
This is a special type of transaction table, called a semi-additive fact. The dimension for the ledger, is additive for all the lines related to GLs. Revenue, Cost, marketing, depreciation; we sum up all the related general ledger amounts. However, the Gross Profit is additive only for those items that contribute to GL.
Conclusion
In this first part, we've discussed the SOCI and how we use Transactional fact tables in order to analyse this financial statement. We've also covered the characteristics of this type of fact. In the next section, we'll cover the SOFP and how we use Snapshot or Accumulating Snapshot fact tables.
NEXT: Snapshot and Accumulating Snapshot Tables