Part One: Profit & Loss
Task
With the data provided, you will build a table that is identical to the one below. You will in all likelihood be using a couple of new actions such as Pivot, Text Column and Stack.
For reference, we built the solution model with 47 actions.
Metric Definitions
We have a couple of helper rows in our Income Statement - Gross Profit, Operating Income, etc. Essentially, all the rows in >CAPS LOCK< - we’ll define each of these below.
- Gross Profit: sales - cost of sales
- EBITDA: sales - (cost of sales+operating expenses)
- Operating Income: sales - (EBITDA + depreciation and amortization)
- EBIT: Operating Income - (interest income + gain/loss on sales of asset + exchange loss/gain + dividend income)
- Net Income: EBIT - (interest expense + taxation)
- YoY change is defined as: (new value - previous value)/(previous value)
Extra Task
In this extra task, you’ll do some investigative work.
Something happens in 2020. Despite a +30% increase in revenue, our net income is lower than in 2019. Can you find out why?
We recommend doing this step wise. You might for instance start diving into changes in Operating Expenses (and potentially its relation to sales)…
Hints
Hint #1
Hint #1
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.
Hint #2
Hint #2
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:
Hint #3
Hint #3
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 tool 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.
Hint #4
Hint #4
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.
Solution File
Download the solution file for the first task here and for the extra task here. See how to import it below. Remember to import it in a new model.