Finance - Daily Cashflows

Overview

A client once asked me in Mar 2020 (before Covid-19 began in the UK!) to see if I could improve how they were preparing their business’ daily cashflow forecasts. The workbook was being maintained by one of their Finance Managers, but the C.F.O. felt there might be some room for further improvement – so I said to her, I would take a look. (Before Covid-19, cashflows were less of a concern, but as the pandemic hit, it became a growing concern for the department)

The workbook was ok – many manually linked cells with lengthy, difficult to update formulae. Quite a few manual foreign exchange conversions, several workbook sheets, quite time consuming to update. The forecast only went out for 10 days.

The workbook had a sheet for scheduled payments. This had two classes of payments: ‘Confirmed’ – payments they knew quite confidently would be paid out, such as Payroll, and V.A.T. payments to H.M.R.C. The other ‘Estimated’ – for payments they weren’t sure would be paid out – but still wanted to record.

The workbook also had another sheet for scheduled receipts, but which they used to copy and paste from an entirely separate ‘Debtors’ workbook, that had the upcoming ‘Estimated’ or ‘Confirmed’ receipts.

In this article, I show how I was able to help the client prepare a 92-day daily cashflow forecast.

Data Inputs

The 4 main input tables that would be needed are:

Input FX Rates

Such data would probably have to be updated monthly.

Daily Opening Bank Balances

This would have to be updated daily

Payment Inputs

These are the projected payments, in the local currency of each country. Depending on the certainty of payment, each one would be classified as ‘Confirmed’ or ‘Estimated’.

Receipt Inputs

These are the projected receipts, in the local currency of each country. Depending on the certainty of receipts, each one would be classified as ‘Confirmed’ or ‘Estimated’. This kind of data might be directly linked to a separate Debtors schedule. Here, I assume data is keyed in a schedule like shown below:

Steps

1. Load FX data

To kick things off, the first thing to do is to load the FX data into the ETL editor.

  • Once loaded, the FX data should looks something like this:
  • Call the query ‘FX_Rates’

2. Payments data

Now, we need to get the Payments data into the right form:

  • Load the payments sheet into the ETL editor, so it looks like this
  • Clean the data. So, remove any blank rows using the following ‘M’ code:
M-Code 
    = Table.SelectRows(Payments, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
  • Next, remove the top 2 rows as they are not needed
  • Then, promote top row to Headers. The payments data should now look like this:
  • Now, unpivot the data, so that all the values are in just one column (exactly what we need!):

  • Add a column, to show the ‘Start of the Month’, based on the value in the ‘Date’ column

  • Now, apply the following Merge with the FX query created earlier

  • Expand the columns, ‘Local Currency’, and ‘FX Rate’
  • Now, add a Custom column that divides the ‘Value’ column, by the imported FX rates, to show all values in ‘GBP’. Use the following M-code:
M-Code 
    = Table.AddColumn(#"Expanded FX_Rates", "Custom", each [Value] / [FX Rate], type number)
  • Now, rename the two columns with Amounts in them, to ‘Payment [LCY]’, and ‘Payment [GBP]’
  • Reorder the columns so the two payment columns are next to each other
  • Remove the ‘Start of Month’ column as it is no longer needed (it was only needed for the Merge operation)
  • Finally, sort the dataset in ascending ‘Date’ order
  • When you load the data back to Excel, it should now be in the following neater form:
  • Call the query ‘Projected Payments’

The final M-code for this query should look something like this. The first few steps may differ slightly, depending on how you layout your original data.

M-Code 

let
    Source = Excel.CurrentWorkbook(),
    Payments = Source{[Name="Payments_Raw_Data"]}[Content],
    #"Removed Blank Rows" = Table.SelectRows(Payments, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Top Rows" = Table.Skip(#"Removed Blank Rows",2),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers1", {"Type", "Date", "Payment Type", "Detail"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Date", type date}, {"Value", type number}, {"Type", type text}, {"Payment Type", type text}, {"Detail", type text}}),
    #"Inserted Start of Month" = Table.AddColumn(#"Changed Type1", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Start of Month",{"Start of Month", "Attribute"},FX_Rates,{"Date", "Country"},"FX_Rates",JoinKind.LeftOuter),
    #"Expanded FX_Rates" = Table.ExpandTableColumn(#"Merged Queries", "FX_Rates", {"Local Currency", "FX Rate"}, {"Local Currency", "FX Rate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded FX_Rates", "Custom", each [Value] / [FX Rate], type number),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Value", "Payment [LCY]"}, {"Custom", "Payment [GBP]"}, {"Attribute", "Country"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Type", "Date", "Payment Type", "Detail", "Country", "Local Currency", "Payment [LCY]", "Payment [GBP]", "Start of Month", "FX Rate"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Start of Month"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

3. Receipts data

You can repeat and mirror the same steps as before to the Receipts data, so it looks like this:

  • Call the query ‘Projected Receipts’

The final M-code, once you have finished building the query should look something like this:

M-Code 

let
    Source = Excel.CurrentWorkbook(),
    Receipts = Source{[Name="Receipts_Raw_Data"]}[Content],
    #"Removed Blank Rows" = Table.SelectRows(Receipts, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Top Rows" = Table.Skip(#"Removed Blank Rows",2),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers1", {"Type", "Date", "Receipt Type", "Detail"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Date", type date}, {"Value", type number}, {"Type", type text}, {"Receipt Type", type text}, {"Detail", type text}}),
    #"Inserted Start of Month" = Table.AddColumn(#"Changed Type1", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Start of Month",{"Start of Month", "Attribute"},FX_Rates,{"Date", "Country"},"FX_Rates",JoinKind.LeftOuter),
    #"Expanded FX_Rates" = Table.ExpandTableColumn(#"Merged Queries", "FX_Rates", {"Local Currency", "FX Rate"}, {"Local Currency", "FX Rate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded FX_Rates", "Custom", each [Value] / [FX Rate], type number),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Value", "Receipt [LCY]"}, {"Custom", "Receipt [GBP]"}, {"Attribute", "Country"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Type", "Date", "Receipt Type", "Detail", "Country", "Local Currency", "Receipt [LCY]", "Receipt [GBP]", "Start of Month", "FX Rate"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Start of Month"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

4. Combined the Payments & Receipts data

Now, I need to combine the Payments and receipt data into one table. This is not so hard.

  • Create a new query that references to the earlier Payments query
  • Then, append to it the earlier Receipts query, so the data looks like this (not all columns are shown in the picture below – but both the Payment and Receipt columns are there!):
  • Call the query ‘Projected Payments + Receipts’

5. Opening Daily, Bank balances

Now, I need to get the Bank balances into the right form.

  • Load the Bank balance data, so it looks like this:
  • Insert a ‘Start of Month’ column, based on the value of the column called ‘Today’
  • After this, apply the following Merge operation (this is NOT quite the same as the Merge operation for the Payments and Receipts data earlier, as I need to convert from Contract Currency first, which is what the Bank Balances are stated in)
  • Extract only the FX rates field
  • Add a new column, to convert from the Contract Currencies to GBP
M-Code 
    = Table.AddColumn(#"Expanded FX_Rates", "GBP", each [CCY]/[FX Rate], type number)
  • Now, apply the following Merge operation
  • Extract only the ‘FX rate’ field
  • Add the following column to convert from GBP to LCY (Local Country Currencies)
M-Code 
    = Table.AddColumn(#"Expanded FX_Rates1", "LCY", each [GBP]*[FX Rate.1], type number)
  • Remove the two ‘FX rate’ columns and the ‘Start of Month’ column, as they are no longer needed
  • Now, group the Data as follows, by Date, and Country

The data should look as follows, after renaming the ‘Today’ column to ‘Start Date’:

  • Next, add the following column, to show the ‘End Date’ (assumed to be 92 days later)
M-Code 
    = Table.AddColumn(#"Renamed Columns", "End Date", each Date.AddDays([Start Date],92))
  • After this, add another column to list, for each row, all the dates between the ‘Start Date’ and ‘End Date’
M-Code 
    = Table.AddColumn(#"Changed Type1", "Dates", each { Number.From([Start Date])..Number.From([End Date]) })

The data should now look like this:

  • Expand the ‘Dates’ column, then apply the relevant field types to the columns. Also, remove the ‘Start Date’ and ‘End Date’ columns, as they are not needed.

The data should look as follows, showing the opening balance, of each country, for each date:

  • Call the query ‘Opening Cash Balances’

6. Merge Opening Cash Balance, and Projected Payments + Receipt data – Part 1

Now, I am almost there! I now need to combine the Payments and Receipts data to the Opening Cash Balance, so the correct Closing balance is shown for each day. This will involve having to create some Running Total functions, too, which we will see later.

  • First, start by creating a new query (called ‘Projected Cash Balances (Confirmed & Estimated)’) by referencing the query called ‘Opening Cash Balances’ created earlier
  • Next, merge the query with the ‘Projected Payments + Receipts’ data created earlier
  • Extract the following columns, which are the ones that are needed – the others can be ignored:
  • Replace any nulls with 0s by adding the following step:
M-Code 

    = Table.ReplaceValue(#"Expanded Projected Payments1",null,0,Replacer.ReplaceValue,{"Bank [LCY]", "Bank [GBP]", "Payment [LCY]", "Payment [GBP]", "Receipt [LCY]", "Receipt [GBP]"})
  • Next, add two Net Receipts columns, with the following code, that add the relevant Receipt and Payment columns together:
M-Code 

    = Table.AddColumn(#"Replaced Value", "Net Receipts [LCY]", each [#"Payment [LCY]"]+[#"Receipt [LCY]"])
M-Code 

    = Table.AddColumn(#"Added Net Receipts LCY", "Net Receipts [GBP]", each [#"Payment [GBP]"]+[#"Receipt [GBP]"])
  • Next, sort the rows by ‘Country’ and then ‘Date’:
M-Code 

    = Table.Sort(#"Added Net Receipts GBP",{{"Country", Order.Ascending}, {"Dates", Order.Ascending}})
  • …Before we go onto the next step, I have to create two Running Total functions, as these will be used in the next few steps:

7. Create Running Total functions

I need to create two running total functions. One that will return the normal Running Total of the values in a specified column that is passed into it. The other function needs to do the same, but this time skip adding any numbers, when the value in the ‘Type’ column of the table equals ‘Estimated’

The first running total function is quite straightforward:

  • This takes in 2 parameters, tbl, and sumcolumn
  • Then, buffers the table, so the sort order is not lost – which sometimes happens, due to the lazy evaluation of ‘M’
  • Then, renames the column to be summed, to ‘Temp’
  • Then, creates a Running Total, using the List.Accumulate ( ) function
  • Then, adds the column, calling it ‘Running Total’, to the original table

The second function is similar, but skips any rows where the value in the ‘Type’ column = ‘Estimated’

The new steps here are:

  • TableCombineColumns – because the List.Accumulate ( ) function can only accept one column / one list as its first argument, not two, we have to combine the ‘Type’ column with the values in the column to be summed (the ‘Temp’ column) into a single column of records (where each record has two fields, ‘Type’ and ‘Temp’). The new column is called ‘TypeTemp’
  • ListofRecords – this step turns the relevant column into a List that can be accepted by List.Accumulate ( )
  • CumulativeTotal – similar to before, except with a condition in the accumulator function argument of List.Accumulate ( ) – that nothing will get summed if the value of ‘Type’ field of the current record equals ‘Estimated’
  • The other steps here are the same as the earlier function

8. Merge Opening Cash Balance, and Projected Payments + Receipt data – Part 2

With the two Running Total functions created, I return to where I was before with this Query

  • Group the table by Country, with the following code, calling the first Running Total function
M-Code 

= Table.Group(#"Sorted Rows", {"Country"}, {{"AllData", (_) => fnRunningTotal(_, "Net Receipts [LCY]"), type table}})
  • Expand all fields, except the Country field, as it is already there from the Group By operation

You should now see a new column at the end called ‘Running Total’, that returns a Running Total, by Country, of the column ‘Net Receipts [LCY]’

  • Rename this column ‘Cumulative Net Receipts [LCY]’
  • Now, group the table again by Country, with the following code
M-Code 

= Table.Group(#"Renamed RT LCY", {"Country"}, {{"AllData", (_) => fnRunningTotal(_, "Net Receipts [GBP]"), type table}})
  • Expand all fields, except the Country field, as it is already there

  • Rename the new ‘Running Total’ column to ‘Cumulative Net Receipts [GBP]’

  • Group the table again by Country, with the following code, this time using the 2nd Running Total function
M-Code 

= Table.Group(#"Renamed RT GBP", {"Country"}, {{"AllData", (_) => fnRunningTotalexclEst(_, "Net Receipts [LCY]"), type table}})

  • Rename the new ‘Running Total’ column to ‘Cumulative Net Receipts [LCY] excl Est.’

  • Group the table again by Country, with the following code, using the 2nd Running Total function

M-Code 

= Table.Group(#"Renamed RT LCY excl Est.", {"Country"}, {{"AllData", (_) => fnRunningTotalexclEst(_, "Net Receipts [GBP]"), type table}})
  • Rename the new ‘Running Total’ column to ‘Cumulative Net Receipts [GBP] excl Est.’

The last four columns of the data should now look like this:

Now, I need to create 4 new columns that add these Cumulative Totals to the Opening Bank Balance columns further to the left, to get to the Closing Balance on each day (which is what we are after!).

  • Add the following 4 columns:
M-Code 
= Table.AddColumn(#"Renamed + RT GBP excl Est.", "Balance [LCY]", each [#"Bank [LCY]"]+[#"Cumulative Net Receipts [LCY]"])
M-Code 
= Table.AddColumn(#"Added Balance LCY", "Balance [GBP]", each [#"Bank [GBP]"]+[#"Cumulative Net Receipts [GBP]"])
M-Code 
= Table.AddColumn(#"Added Balance GBP", "Balance [LCY] excl Est.", each [#"Bank [LCY]"]+[#"Cumulative Net Receipts [LCY] excl Est."])
M-Code 
= Table.AddColumn(#"Added Balance LCY excl Est.", "Balance [GBP] excl Est.", each [#"Bank [GBP]"]+[#"Cumulative Net Receipts [GBP] excl Est."])
  • Group the data again, by Country as follows:
  • Add an Index column like so (this Index Column will be needed were we to build any DAX Measures, from this Dataset – see the DAX article: Finance - Daily Cashflows):
  • Remove the column called ‘All Data’, so the data just looks like this, grouped by Country
  • Expand only the following fields. The others are not needed:
  • Reorder the Index column to the front, and then assign a final Data type to each column

The data when loaded back to Excel, should now give the final result (we are after!) – the closing balance each day, in GBP, and LCY, for each Country we have a bank account in - as shown below:

Click to view large-sized image

Once the inputs have been updated, this output data can now be re-created by clicking a button!

  • Some people prefer to see graphs rather than tables. Therefore, to more easily visualise this data, we can then create a Data Model, and Report, from this final dataset. How to do this, has been explained further in this DAX article, here: Finance - Daily Cashflows.

The final ‘M’ code to this query should look something like this:

M-Code 

let
    Source = #"Opening Cash Balances",
    #"Merged Queries" = Table.NestedJoin(Source,{"Dates", "Country"},#"Projected Payments + Receipts",{"Date", "Country"},"Projected Payments",JoinKind.LeftOuter),
    #"Expanded Projected Payments1" = Table.ExpandTableColumn(#"Merged Queries", "Projected Payments", {"Type", "Payment Type", "Payment [LCY]", "Payment [GBP]", "Receipt Type", "Receipt [LCY]", "Receipt [GBP]"}, {"Type", "Payment Type", "Payment [LCY]", "Payment [GBP]", "Receipt Type", "Receipt [LCY]", "Receipt [GBP]"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Projected Payments1",null,0,Replacer.ReplaceValue,{"Bank [LCY]", "Bank [GBP]", "Payment [LCY]", "Payment [GBP]", "Receipt [LCY]", "Receipt [GBP]"}),
    #"Added Net Receipts LCY" = Table.AddColumn(#"Replaced Value", "Net Receipts [LCY]", each [#"Payment [LCY]"]+[#"Receipt [LCY]"]),
    #"Added Net Receipts GBP" = Table.AddColumn(#"Added Net Receipts LCY", "Net Receipts [GBP]", each [#"Payment [GBP]"]+[#"Receipt [GBP]"]),
    #"Sorted Rows" = Table.Sort(#"Added Net Receipts GBP",{{"Country", Order.Ascending}, {"Dates", Order.Ascending}}),
    #"Grouped + RT Column LCY" = Table.Group(#"Sorted Rows", {"Country"}, {{"AllData", (_) => fnRunningTotal(_, "Net Receipts [LCY]"), type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped + RT Column LCY", "AllData", {"Bank [LCY]", "Bank [GBP]", "Dates", "Type", "Payment Type", "Payment [LCY]", "Payment [GBP]", "Receipt Type", "Receipt [LCY]", "Receipt [GBP]", "Net Receipts [LCY]", "Net Receipts [GBP]", "Running Total"}, {"Bank [LCY]", "Bank [GBP]", "Dates", "Type", "Payment Type", "Payment [LCY]", "Payment [GBP]", "Receipt Type", "Receipt [LCY]", "Receipt [GBP]", "Net Receipts [LCY]", "Net Receipts [GBP]", "Running Total"}),
    #"Renamed RT LCY" = Table.RenameColumns(#"Expanded AllData",{{"Running Total", "Cumulative Net Receipts [LCY]"}}),
    #"Grouped + RT Column GBP" = Table.Group(#"Renamed RT LCY", {"Country"}, {{"AllData", (_) => fnRunningTotal(_, "Net Receipts [GBP]"), type table}}),
    #"Expanded AllData1" = Table.ExpandTableColumn(#"Grouped + RT Column GBP", "AllData", {"Bank [LCY]", "Bank [GBP]", "Dates", "Type", "Payment Type", "Payment [LCY]", "Payment [GBP]", "Receipt Type", "Receipt [LCY]", "Receipt [GBP]", "Net Receipts [LCY]", "Net Receipts [GBP]", "Cumulative Net Receipts [LCY]", "Running Total"}, {"Bank [LCY]", "Bank [GBP]", "Dates", "Type", "Payment Type", "Payment [LCY]", "Payment [GBP]", "Receipt Type", "Receipt [LCY]", "Receipt [GBP]", "Net Receipts [LCY]", "Net Receipts [GBP]", "Cumulative Net Receipts [LCY]", "Running Total"}),
    #"Renamed RT GBP" = Table.RenameColumns(#"Expanded AllData1",{{"Running Total", "Cumulative Net Receipts [GBP]"}}),
    #"Grouped + RT LCY excl Est." = Table.Group(#"Renamed RT GBP", {"Country"}, {{"AllData", (_) => fnRunningTotalexclEst(_, "Net Receipts [LCY]"), type table}}),
    #"Expanded AllData2" = Table.ExpandTableColumn(#"Grouped + RT LCY excl Est.", "AllData", {"Bank [LCY]", "Bank [GBP]", "Dates", "Type", "Payment Type", "Payment [LCY]", "Payment [GBP]", "Receipt Type", "Receipt [LCY]", "Receipt [GBP]", "Net Receipts [LCY]", "Net Receipts [GBP]", "Cumulative Net Receipts [LCY]", "Cumulative Net Receipts [GBP]", "Running Total"}, {"Bank [LCY]", "Bank [GBP]", "Dates", "Type", "Payment Type", "Payment [LCY]", "Payment [GBP]", "Receipt Type", "Receipt [LCY]", "Receipt [GBP]", "Net Receipts [LCY]", "Net Receipts [GBP]", "Cumulative Net Receipts [LCY]", "Cumulative Net Receipts [GBP]", "Running Total"}),
    #"Renamed RT LCY excl Est." = Table.RenameColumns(#"Expanded AllData2",{{"Running Total", "Cumulative Net Receipts [LCY] excl Est."}}),
    #"Grouped + RT GBP excl Est." = Table.Group(#"Renamed RT LCY excl Est.", {"Country"}, {{"AllData", (_) => fnRunningTotalexclEst(_, "Net Receipts [GBP]"), type table}}),
    #"Expanded AllData3" = Table.ExpandTableColumn(#"Grouped + RT GBP excl Est.", "AllData", {"Bank [LCY]", "Bank [GBP]", "Dates", "Type", "Payment Type", "Payment [LCY]", "Payment [GBP]", "Receipt Type", "Receipt [LCY]", "Receipt [GBP]", "Net Receipts [LCY]", "Net Receipts [GBP]", "Cumulative Net Receipts [LCY]", "Cumulative Net Receipts [GBP]", "Cumulative Net Receipts [LCY] excl Est.", "Running Total"}, {"Bank [LCY]", "Bank [GBP]", "Dates", "Type", "Payment Type", "Payment [LCY]", "Payment [GBP]", "Receipt Type", "Receipt [LCY]", "Receipt [GBP]", "Net Receipts [LCY]", "Net Receipts [GBP]", "Cumulative Net Receipts [LCY]", "Cumulative Net Receipts [GBP]", "Cumulative Net Receipts [LCY] excl Est.", "Running Total"}),
    #"Renamed + RT GBP excl Est." = Table.RenameColumns(#"Expanded AllData3",{{"Running Total", "Cumulative Net Receipts [GBP] excl Est."}}),
    #"Added Balance LCY" = Table.AddColumn(#"Renamed + RT GBP excl Est.", "Balance [LCY]", each [#"Bank [LCY]"]+[#"Cumulative Net Receipts [LCY]"]),
    #"Added Balance GBP" = Table.AddColumn(#"Added Balance LCY", "Balance [GBP]", each [#"Bank [GBP]"]+[#"Cumulative Net Receipts [GBP]"]),
    #"Added Balance LCY excl Est." = Table.AddColumn(#"Added Balance GBP", "Balance [LCY] excl Est.", each [#"Bank [LCY]"]+[#"Cumulative Net Receipts [LCY] excl Est."]),
    #"Added Balance GBP excl Est." = Table.AddColumn(#"Added Balance LCY excl Est.", "Balance [GBP] excl Est.", each [#"Bank [GBP]"]+[#"Cumulative Net Receipts [GBP] excl Est."]),
    #"Grouped Rows" = Table.Group(#"Added Balance GBP excl Est.", {"Country"}, {{"All Data", each _, type table [Country=nullable text, Dates=date, Type=nullable text, Payment Type=nullable text, #"Payment [LCY]"=number, #"Payment [GBP]"=number, Receipt Type=nullable text, #"Receipt [LCY]"=number, #"Receipt [GBP]"=number, #"Balance [LCY]"=nullable number, #"Balance [GBP]"=nullable number, #"Balance [LCY] excl Est."=nullable number, #"Balance [GBP] excl Est."=nullable number]}}),
    #"Added Index" = Table.AddColumn(#"Grouped Rows", "With Index", each Table.AddIndexColumn([All Data], "Record", 1, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"All Data"}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Removed Columns", "With Index", {"Dates", "Type", "Payment Type", "Payment [LCY]", "Payment [GBP]", "Receipt Type", "Receipt [LCY]", "Receipt [GBP]", "Balance [LCY]", "Balance [GBP]", "Balance [LCY] excl Est.", "Balance [GBP] excl Est.", "Record"}, {"Dates", "Type", "Payment Type", "Payment [LCY]", "Payment [GBP]", "Receipt Type", "Receipt [LCY]", "Receipt [GBP]", "Balance [LCY]", "Balance [GBP]", "Balance [LCY] excl Est.", "Balance [GBP] excl Est.", "Record"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Index",{"Record", "Country", "Dates", "Type", "Payment Type", "Payment [LCY]", "Payment [GBP]", "Receipt Type", "Receipt [LCY]", "Receipt [GBP]", "Balance [LCY]", "Balance [GBP]", "Balance [LCY] excl Est.", "Balance [GBP] excl Est."}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Record", Int64.Type}, {"Dates", type date}, {"Type", type text}, {"Payment Type", type text}, {"Payment [LCY]", type number}, {"Payment [GBP]", type number}, {"Receipt Type", type text}, {"Receipt [LCY]", type number}, {"Receipt [GBP]", type number}, {"Balance [LCY]", type number}, {"Balance [GBP]", type number}, {"Balance [LCY] excl Est.", type number}, {"Balance [GBP] excl Est.", type number}})
in
    #"Changed Type"

9. Query Dependencies

So, you can better see at a high level what is going on, the final dependency chain of the ETLs should look something like this:

Click to view large-sized image

10. ETL Extensions

In this article, although the transformations done up til this point allow for the recording of multiple currencies for ‘bank accounts’ in one country (Australia has one bank account in AUD, and another in USD, for example), the ‘payment and receipt’ schedules assume that the amounts are entered in the local currency of that country – not in multiple contract currencies. The Payment and Receipt schedules could be extended to allow for multiple currencies too. One would need to ensure that any amounts entered are assigned to the correct contract currency - which would require some extra effort to keep track of.