Part 3 - Accrual-Based Accounting
Last updated
Last updated
The first part is about splitting the data set in two streams. We only want to adjust when we have some data in our Payment column. So the beginning is about splitting the general ledger data with two Filter tools.
Then we use an IF Column tool to generate a column that specifies the number of months a payment is accounting for. If the payment is monthly then 1 (month), if the payment is quarterly then 3 (months) and else 12 (i.e. because it must then be yearly).
The second section is where things get interesting. We want to use our newly creating Months column to generate a row for each month the payment is accounting for and the divide the amount by each month. So if an original payment of 150 is quarterly and hence accounting for 3 months, we want to have 3 rows with an amount in each of 50 (=150/3).
The Create Rows is relatively simply to configure. You input the column the specifies the number of rows you want to create from each original row - that's our Months column. Then you input (optional) the column you want to disaggregate - that's our Amount column.
In this section of Part 3, we want to create an adjusted Date column for our new rows. With the output from Hint #2, we have a bunch of new rows for each value in the Month columns with disaggregated Amounts. But the Date looks like it is on the same date (from the original transaction. This is what we want to correct. The section looks like this:
In short, this is what happens:
We make our original date column into a Text format (Tool ID 34)
We split that column by "-" (Tool ID 33)
The transform the Day part of the splitted columns called Date_2 and take only the first two characters with a Transform tool (config: left = 2)
Then we split into three streams of data with Filter tools - one stream for each type of Payment / Months
Top + Middle stream: we adjust with custom formulas (IF Column) to create a correct date. First by figuring out if the date is in a new year and then figuring out the new month - you can look at the screenshots for the formulas. We use a Text Column tool to assemble our new date
The Months == 1 doesn't need any adjustment because if the Months = 1 then the Create Rows didn't generate any new rows and the Date is correct
In the final section of Part 3, we merge our datasets together with Stack tools. The Green part to the right is exactly the same is in Part 1 - Profit & Loss. We simply switching out the Generel Ledger input data with what we've built in the exercise. You can then run everything and the Income Statement at the end will change because you adjusted the Date column of the transactions which is what is used to generate the Income Statement.
