Part 1 - Profit & Loss
Helping you get to the final result
Last updated
Helping you get to the final result
Last updated
The first step you need to complete is merging the datasets together. You need information from the accounts table which contains the text description of the account (e.g. 210 = Sales, 360 = Rent, etc.). Similarly, you want to add the text description for the territory (e.g. 1 = USA). This beginning of the workflow looks like the below.
You want to use the Combine tool twice where you create an Inner join on the account_key and territory_key. The configuration of the first Combine looks like this.
The next step of the analysis is to being summarising the transactions around the year they occurred in (e.g. 2018) and the account they belong to (e.g. Cost of Sales). The second part of the analysis looks like this:
The key parts of this step of the workflow are the two Group By's and the Pivot. In Tool ID 7, we group our transactions by date_year (created in the Date Format), Subclass, Subclass2 and Account while we sum our amounts and take the first of our account_key (for sorting later). The config looks like this:
After the first Group By, we need to Pivot our data. The intention is to transform our Year into column instead of rows. So we end up with three column defining the accounts/subclass/subclass2 (e.g. Sales) and three columns with the transaction amount for each year. The configuration looks like this:
Ultimately, after the last Group By (Tool ID 27), you'll end up with a dataset that looks like the below:
In the most complex step of our analysis, we need to create the helper lines of our Income State (e.g. >GROSS PROFIT<). We do this by creating five streams of data to create each of the 5 helper lines. The sixth stream (the one at the bottom) contains our entire income statement without the helper lines. You entire step looks like this:
Each of the four top streams follow a similar logic. They are done by using an IF Column, a Filter to remove all the rows we don't want to be included in our helper line (e.g. (e.g. >GROSS PROFIT<), a Group By to summarise the amount and a Text Column to create an invented account_key which helps us sort the Income Statement correctly. It looks like this:
The IF Column is the tricky part here. We essentially replace the content of the SubClass column. We do this by saying if the subclass column is equal to "Sales"/"Cost of Sales" then label the Subclass column ">GROSS PROFIT<". Else (#3) add nothing to the column. We then remove all the empty ("", else) rows, summarise our amounts and add the Account_no.
The last step is easier. We simply sort by our account_key to make sure the accounts are in the right order, calculate the YoY difference with a Calculate tool and use a Change Columns to tidy up our data set.
You'll end up with a dataset the looks like this:
