# Part 1 - Profit & Loss ### 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.

The Combine configuration on Tool_ID = 3

### *** ### 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 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 IF column configuration of the Gross Profit helper line.

### *** ### 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.

You'll end up with a dataset the looks like this:

--- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://resources.less.tech/less-tech/exercises/automated-financial-analysis/hints/part-1-profit-and-loss.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.