Finance - Debtors

Overview

The goal of this article to see how we can turn a live debtor’s stream (from your ERP), into something you can add extra information to (from within Excel), and supplement with additional calculations - so you can keep on top of any uncollected receivables – an very useful tool if you’ve ever spent some time in a company’s finance department.

Several techniques are incorporated in this ETL, that will allow you to:

• Directly & instantaneously retrieve structured data from your ERP or API sources
• Work with data feedback loops and self-referencing data tables
• Build self-constructing lists that can automatically ‘row-match’ manual user inputs with live streaming ERP / API inputs, based on ‘multi-column’ logic
• Group values across multiple rows into a single cell, when traversing granularities (e.g. Invoices to Customers) (see “Owner” & “Status” columns in the first tab)
• Make FX calculations (Contract to > Country to > Common Currency) very easy!

The article explains how such a workbook can be created!

Data Inputs

To start with, we work with the following data inputs:

Debtors Data

Debtors for different countries, by customer by invoice is something you should be able to easily get as a live stream from your ERP.

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

FX Data

Similarly, FX data should be relatively easy to get from your ERP, or the internet. Shown below are Average rates for every month of the year, for different countries converting into GBP. End of month Balance Sheet rates could be chosen instead, but we stick with Average rates for now.

Click to view large-sized image

Steps

1. Import Data

To kick things off,

• Import all the raw datasets into your ETL editor, so they look something like this:

• A direct link to the source tables is sufficient as the only step to each query, but you could optionally apply data types too at this stage

2. Create Self-Maintaining Customer List

• Next, create a list of all customers by appending the 3 debtors tables together, using this code:

= Table.Combine({UK_Debtors, US_Debtors, China_Debtors})

• After appending, the data will look something like this:

• Remove all columns apart from the ‘Customer’ column
• Then, remove all Duplicates, so the data looks like this:

• Call the query “All Customers”
• Load this to Excel. Then in Excel itself, add another column so you can manually add the Payment terms for each Customer, by just keying them in here!

• Now load this Excel table back to the ETL editor (with the extra column)
• Call the query ‘All Customers (with Payment Days)’

• Go to the ‘All Customers’ query, and add the following Merge step:

• Expand only the ‘Days’ field, so it looks like this:

• Load this table to Excel (it should load automatically, after thethe newly added Merge and Expand step – as we set the query to load Excel already, earlier)
• In Excel, you should now see two columns, one called ‘Days’ and another called ‘Days2’ – delete the one that says ‘Days2’ - but the payment term days will be logically linked to the correct row (i.e. correct Customer in this case)

3. Create a Debtors by Invoice Query

• To create a Master Debtors table by invoice, start by appending the three source Debtors feed tables, with the following code:

= Table.Combine({UK_Debtors, US_Debtors, China_Debtors})

• Rename the ‘Amount’ column to ‘Amount [CCY]’ to make it clear and explicit that these values are in their ‘Contract Currency’

• Add a column with Today’s date with the following code:

= Table.AddColumn(#"Changed Type", "Today", each DateTime.LocalNow() as datetime)

• Apply the ‘Date’ datatype to this column:

• Next, add a ‘Start of Month’ column based on the ‘Today’ column

• Get the FX rates for each Contract Currency, for a given month, from the FX table, using the following multi-column Merge:

• Expand only the ‘Rate’ field, as an extra column
• Then add the following column, to show the Amounts in GBP

• Now, you can convrt the GBP amounts into local country Amounts with the following multi-column Merge (a 2 column merge on the Date, and Country columns).

• Expand only the ‘Rate’ field, as another column (after the expand operation, the column might be called ‘Rate.1’ as there is already a column called ‘Rate’)
• Add the following column to show amounts in LCY

• Next, remove both the ‘Rate’ columns and the ‘Today’ column as they are no longer needed
• Add the following additional column so we can identify what might be an Inter-Company account

• Re-order the columns (group the Amount columns together, and bring the Inter-Company column to somewhere more suitable near the front of the dataset)
• Do another Merge operation to get the Payment Terms, like shown below:

• Expand the field ‘Days’ and call it ‘Payment Terms’
• Call the Query ‘All Debtors – by Invoice’
• Finally, load the dataset to Excel, so it looks something like this:

Click to view large-sized image

4. Add Calculated and Free-Text Columns

• We now need to add two additional types of columns to this Excel table as it stands – (i) Calculated columns, and (ii) some Free-Text columns.
• Although the Calculated columns could have been added in the Query, we’ll add them in Excel at this particular point, as if they need to be changed, this can easily be done just by modifying the Excel formulae. The Free-text columns of course can only be added in Excel.
• So, let’s add the Excel columns one by one, within the table, as follows:

Calculated Columns

• Today

=TODAY()

• Days since Invoiced

=[@Today]-[@Date]

• Days Overdue / (Underdue)”

=[@[Days since Invoiced]]-[@[Payment Terms]]

• Not Due Yet

=IF([@[Days Overdue / (Underdue)]]<=0,[@[Amount '[GBP']]], 0)

• 1 – 30 Days Overdue

=IF(AND([@[Days Overdue / (Underdue)]]>0,[@[Days Overdue / (Underdue)]]<=30),[@[Amount '[GBP']]],0)

• 31 – 60 Days Overdue

=IF(AND([@[Days Overdue / (Underdue)]]>30,[@[Days Overdue / (Underdue)]]<=60),[@[Amount '[GBP']]],0)

• 61 – 90 Days Overdue

=IF(AND([@[Days Overdue / (Underdue)]]>60,[@[Days Overdue / (Underdue)]]<=90),[@[Amount '[GBP']]],0)

• 91 – 120 Days Overdue

=IF(AND([@[Days Overdue / (Underdue)]]>90,[@[Days Overdue / (Underdue)]]<=120),[@[Amount '[GBP']]],0)

• 121 – 150 Days Overdue

=IF(AND([@[Days Overdue / (Underdue)]]>120,[@[Days Overdue / (Underdue)]]<=150),[@[Amount '[GBP']]],0)

• 151 – 180 Days Overdue

=IF(AND([@[Days Overdue / (Underdue)]]>150,[@[Days Overdue / (Underdue)]]<=180),[@[Amount '[GBP']]],0)

• Over 180 Days Overdue

=IF([@[Days Overdue / (Underdue)]]>180,[@[Amount '[GBP']]],0)

• Cash Received [GBP]

=IFERROR(
    [@[Cash Received '[CCY']]]/(INDEX(
            FX_Rates[Rate],
            MATCH(1,
                ( FX_Rates[Month] = [@[Start of Month]]) * ( FX_Rates[Currency] = [@Currency] ),
                0)
        ) ),
    ""
)

• Not Due

=IF([@[Not Due Yet]] <> 0, [@[Not Due Yet]] -[@[Cash Received '[GBP']]], [@[Not Due Yet]] )

• 0 – 30 Days

=IF([@[1 - 30 Days Overdue]] <> 0, [@[1 - 30 Days Overdue]]-[@[Cash Received '[GBP']]], [@[1 - 30 Days Overdue]])

• 31 – 60 Days

=IF([@[31 - 60 Days Overdue]]<>0, [@[31 - 60 Days Overdue]]-[@[Cash Received '[GBP']]], [@[31 - 60 Days Overdue]])

• 61 – 90 Days

=IF([@[61 - 90 Days Overdue]]<>0, [@[61 - 90 Days Overdue]]-[@[Cash Received '[GBP']]], [@[61 - 90 Days Overdue]])

• 91 – 120 Days

=IF([@[91 - 120 Days Overdue]]<>0,[@[91 - 120 Days Overdue]]-[@[Cash Received '[GBP']]],[@[91 - 120 Days Overdue]])

• 121 – 150 Days

=IF([@[121 - 150 Days Overdue]]<>0,[@[121 - 150 Days Overdue]]-[@[Cash Received '[GBP']]],[@[121 - 150 Days Overdue]])

• 151 – 180 Days

=IF([@[151 - 180 Days Overdue]]<>0,[@[151 - 180 Days Overdue]]-[@[Cash Received '[GBP']]],[@[151 - 180 Days Overdue]])

• More than 180 days

=IF([@[Over 180 Days]]<>0,[@[Over 180 Days]]-[@[Cash Received '[GBP']]],[@[Over 180 Days]])

• Balance [GBP] after Payments

=SUM(All_Debtors___by_Invoice[@[Not Due]:[More than 180 days]])

• Full Paid?

=IF(AND([@[Balance '[GBP'] after Payments]]<0.5,[@[Balance '[GBP'] after Payments]]>-0.5),"Yes","No")

Free Text Columns

• Cash Received [CCY]
• Date Last Chased
• Date Chased Again
• Owner
• NOTES
• Status
• Estimated Payment Date
• Amount [CCY] Est
• Confirmed Payment Date
• Amount [CCY] Cfd

5. Load Debtors by Invoice Query back to ETL

Now, we have to ensure the free-text columns logically link back to the correct rows, in case the debtors to change, and the row’s shift places (which could happen as the data is live linked to ERP inputs). Calculated columns won’t matter here, as the same formula is used in every row - we though need to be careful with the free-text columns.

• Load the table (with all the extra columns in it) back to the ETL editor.
• Call the re-imported table: ‘All Debtors – by Invoice (with Manual Inputs)’
• Filter out any rows called ‘Total’ if you had added this to the Excel Table – using the ‘Total’ row feature

• Now go back to the ‘All Debtors – by Invoice’ query and add the following Merge step:

• After the Merge, expand out the following free-text fields (Not all ticked fields are shown):

• Now when you load this query (‘All Debtors – by Invoice’) back to Excel, it will have duplicates of the free-text columns (most likely appended with a 2, or a 1). Delete these duplicate columns and ensure the other Calculated Fields still work as expected - as they may be referencing to the duplicated columns
• You may have to cut and paste the columns in the Excel table, to re-order them if they are not in the desired order.
• After all of this, you should now have a nicely logically linked table that looks something like this - showing the original ETL columns, the calculated columns, and the free-text columns all in one table!

Click to view large-sized image

6. Debtors by Invoice M-Code:

The final M-Code to this query should look something like this:

let
    Source = Table.Combine({UK_Debtors, US_Debtors, China_Debtors}),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Amount", "Amount [CCY]"}}),
    #"Inserted Today" = Table.AddColumn(#"Renamed Columns", "Today", each DateTime.LocalNow() as datetime),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Today",{{"Today", type date}}),
    #"Inserted Start of Month" = Table.AddColumn(#"Changed Type1", "Start of Month", each Date.StartOfMonth([Today]), type date),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Start of Month", {"Start of Month", "Currency"}, FX_Rates, {"Month", "Currency"}, "FX_Rates", JoinKind.LeftOuter),
    #"Expanded FX_Rates" = Table.ExpandTableColumn(#"Merged Queries", "FX_Rates", {"Rate"}, {"Rate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded FX_Rates", "Amount [GBP]", each [#"Amount [CCY]"] / [Rate]),
    #"Merged Queries1" = Table.NestedJoin(#"Added Custom", {"Start of Month", "Country"}, FX_Rates, {"Month", "Country"}, "FX_Rates", JoinKind.LeftOuter),
    #"Expanded FX_Rates1" = Table.ExpandTableColumn(#"Merged Queries1", "FX_Rates", {"Rate"}, {"Rate.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded FX_Rates1", "Amount [LCY]", each [#"Amount [GBP]"] * [Rate.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Rate", "Rate.1", "Today"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Inter-Company", each if Text.StartsWith([Customer], "VidStar") then "Inter-Company" else null),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"Office", "Country", "Inter-Company", "Customer", "Start of Month", "Date", "Transaction Ref", "Invoice No.", "Invoice Narrative", "Currency", "Amount [CCY]", "Amount [LCY]", "Amount [GBP]"}),
    #"Merged Queries2" = Table.NestedJoin(#"Reordered Columns", {"Customer"}, #"All Customers (with Payment Days)", {"Customer"}, "All_Customers with Payment Days", JoinKind.LeftOuter),
    #"Expanded All_Customers with Payment Days" = Table.ExpandTableColumn(#"Merged Queries2", "All_Customers with Payment Days", {"Days"}, {"Payment Terms"}),
    #"Merged Queries3" = Table.NestedJoin(#"Expanded All_Customers with Payment Days", {"Transaction Ref"}, #"All Debtors - by Invoice (with Manual Inputs)", {"Transaction Ref"}, "All Debtors - by Invoice (with Manual Inputs)", JoinKind.LeftOuter),
    #"Expanded All Debtors - by Invoice (with Manual Inputs)" = Table.ExpandTableColumn(#"Merged Queries3", "All Debtors - by Invoice (with Manual Inputs)", {"Cash Received [CCY]", "Date Last Chased", "Date Chased Again", "Owner", "NOTES", "Status", "Estimated Payment Date", "Amount [CCY] Est", "Confirmed Payment Date", "Amount [CCY] Cfd"}, {"Cash Received [CCY]", "Date Last Chased", "Date Chased Again", "Owner", "NOTES", "Status", "Estimated Payment Date", "Amount [CCY] Est", "Confirmed Payment Date", "Amount [CCY] Cfd"})
in
    #"Expanded All Debtors - by Invoice (with Manual Inputs)"

7. Create a Debtors by Office, Country & Customer Query

Now finally, let’s use this Invoice query to create a summary of the debtor’s position at a slighly more aggregated level, by Office, Country & Customer!

• Import the ‘Master Debtors by Invoice’ Table in Excel back to the ETL editor
• Filter out any rows in Excel called ‘Total’ in case you had added this in Excel, using the Total Table feature
• Group the data as follows:

• The data will look something like this:

• Add a column to show the distinct ‘Owners’ and distinct ‘Status’ to each of the grouped rows (as shown above), with the following code (as two separate steps):

= Table.AddColumn(#"Grouped Rows", "Owner", each List.Distinct(Table.Column([Details], "Owner")))

= Table.AddColumn(#"Added Owner", "Status", each List.Distinct(Table.Column([Details], "Status")))

The data should now look like this:

• Now extract the data from the lists, with the following code:

= Table.TransformColumns(#"Added Status", {"Owner", each Text.Combine(List.Transform(_, Text.From), ", "), type text})

= Table.TransformColumns(#"Extracted Owner", {"Status", each Text.Combine(List.Transform(_, Text.From), ", "), type text})

The data will now look like this:

• Remove the column called “Details” as it no longer needed

• Click the fx button in the query editor, and add the following step, that references back to the 2nd step of the query (where we filtered out any rows with ‘Total’ in them), and apply the following grouping:

This time, instead of adding All Rows to a column called ‘Details’ add Summation columns to those fields that make sense to add at a ‘Office’, ‘Country’ & ‘Customer’ level granularity.

After this grouping, the data should look something like this (not all columns shown):

• Now apply a Merge operation on the data in this grouped form, to the earlier step where we showed the ‘Owner’ and ‘Status’. Merging to another step in the same query can’t be done through the ETL’s Graphical User Interface, so use the following code:

= Table.NestedJoin(#"Grouped Rows 2", {"Office", "Country", "Inter-Company", "Customer"}, #"Removed Columns", {"Office", "Country", "Inter-Company", "Customer"}, "Grouped Rows 2", JoinKind.LeftOuter)

• After the Merge, then expand the ‘Owner’ and ‘Status’ fields, so the data now looks like this (last 4 columns shown):

• Call the query: ‘All Debtors – by Customer’
• Load the data to Excel so it looks something like this. This is now exactly what we want!

Click to view large-sized image

And that should be it! : )

Of course a similar query could be created if you wanted to show grouping by just Customer, rather than Office, Country & Customer - but lots of different combinations are possible!

8. Debtors by Customer M-Code:

The final M-Code to this query should look something like this:

let
    Source = Excel.CurrentWorkbook(){[Name="All_Debtors___by_Invoice"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Office] <> "Total")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Office", "Country", "Inter-Company", "Customer"}, {{"Details", each _, type table [Office=text, Country=nullable text, #"Inter-Company"=nullable text, Customer=nullable text, Start of Month=nullable datetime, Date=nullable datetime, Transaction Ref=nullable number, #"Invoice No."=nullable text, Invoice Narrative=nullable text, Currency=nullable text, #"Amount [CCY]"=number, #"Amount [LCY]"=number, #"Amount [GBP]"=number, Payment Terms=number, Today=nullable datetime, Days since Invoiced=number, #"Days Overdue / (Underdue)"=number, Not Due Yet=number, #"1 - 30 Days Overdue"=number, #"31 - 60 Days Overdue"=number, #"61 - 90 Days Overdue"=number, #"91 - 120 Days Overdue"=number, #"121 - 150 Days Overdue"=number, #"151 - 180 Days Overdue"=number, Over 180 Days=number, #"Cash Received [CCY]"=nullable number, #"Cash Received [GBP]"=number, Not Due=number, #"0 - 30 days"=number, #"31 - 60 days"=number, 61 to 90 days=number, 91 to 120 days=number, 121 to 150 days=number, 151 to 180 days=number, More than 180 days=number, #"Balance [GBP] after Payments"=number, Date Last Chased=any, Date Chased Again=any, Owner=any, NOTES=any, Status=any, #"Fully Paid?"=nullable text, Estimated Payment Date=any, #"Amount [CCY] Est"=nullable number, Confirmed Payment Date=any, #"Amount [CCY] Cfd"=nullable number]}}),
    #"Added Owner" = Table.AddColumn(#"Grouped Rows", "Owner", each List.Distinct(Table.Column([Details], "Owner"))),
    #"Extracted Owner" = Table.TransformColumns(#"Added Owner", {"Owner", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Added Status" = Table.AddColumn(#"Extracted Owner", "Status", each List.Distinct(Table.Column([Details], "Status"))),
    #"Extracted Status" = Table.TransformColumns(#"Added Status", {"Status", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Status",{"Details"}),
    #"Grouped Rows 2" = Table.Group(#"Filtered Rows", {"Office", "Country", "Inter-Company", "Customer"}, {{"Amount [CCY]", each List.Sum([#"Amount [CCY]"]), type number}, {"Amount [LCY]", each List.Sum([#"Amount [LCY]"]), type number}, {"Amount [GBP]", each List.Sum([#"Amount [GBP]"]), type number}, {"Payment Terms", each List.Average([Payment Terms]), type number}, {"Not Due Yet", each List.Sum([Not Due Yet]), type number}, {"1 - 30 Days Overdue", each List.Sum([#"1 - 30 Days Overdue"]), type number}, {"31 - 60 Days Overdue", each List.Sum([#"31 - 60 Days Overdue"]), type number}, {"61 - 90 Days Overdue", each List.Sum([#"61 - 90 Days Overdue"]), type number}, {"91 - 120 Days Overdue", each List.Sum([#"91 - 120 Days Overdue"]), type number}, {"121 - 150 Days Overdue", each List.Sum([#"121 - 150 Days Overdue"]), type number}, {"151 - 180 Days Overdue", each List.Sum([#"151 - 180 Days Overdue"]), type number}, {"Over 180 Days", each List.Sum([Over 180 Days]), type number}, {"Cash Received [CCY]", each List.Sum([#"Cash Received [CCY]"]), type nullable number}, {"Cash Received [GBP]", each List.Sum([#"Cash Received [GBP]"]), type number}, {"Not Due", each List.Sum([Not Due]), type number}, {"0 - 30 days", each List.Sum([#"0 - 30 days"]), type number}, {"31 - 60 days", each List.Sum([#"31 - 60 days"]), type number}, {"61 - 90 days", each List.Sum([#"61 - 90 days"]), type number}, {"91 - 120 days", each List.Sum([#"91 - 120 days"]), type number}, {"121 - 150 days", each List.Sum([#"121 - 150 days"]), type number}, {"151 - 180 days", each List.Sum([#"151 - 180 Days Overdue"]), type number}, {"More than 180 days", each List.Sum([More than 180 days]), type number}, {"Balance [GBP] after Payments", each List.Sum([#"Balance [GBP] after Payments"]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows 2", {"Office", "Country", "Inter-Company", "Customer"}, #"Removed Columns", {"Office", "Country", "Inter-Company", "Customer"}, "Grouped Rows 2", JoinKind.LeftOuter),
    #"Expanded Grouped Rows 2" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows 2", {"Owner", "Status"}, {"Owner", "Status"})
in
    #"Expanded Grouped Rows 2"

9. Download Excel File

You can download the complete file, if you wish to see all the ETL code and all the details workings, both in the M-Code, and the Excel Sheets, by clicking here: Debtors - Excel File

We hope you found this guide useful!