Finance - Daily Cashflows

Finance - Daily Cashflows

This sample provides insights from the daily cashflows dataset produced at the end of this ETL article: Finance - Daily Cashflows, that shows projected payments and receipts over a 92 day period in both GBP and LCY (Local Country) Currencies, as well as each day’s Closing Cash Balance, by Country. All measures when shown in this report are converted to the currency, GBP.

Daily Cashflows Measures

The following measures are shown in this report:

1. Payments [GBP] by Country
2. Receipts [GBP] by Country
3. Payments [GBP] excl Estimates by Country
4. Receipts [GBP] excl Estimates by Country
5. Cash Balance [GBP] by Country
6. Cash Balance [GBP] excl Estimates by Country

Model Extensions

• Although the modelling done up to this point allow for the recording of multiple currencies for bank accounts in one country (for example, Australia has one bank account in AUD, and another in USD), the payment and receipt schedules assume that the amounts are entered in the local currency of that country – and not in multiple contract currencies.
• The Payment and Receipt schedules could be extended to allow for the recording of multiple currencies, for payments and receipts, too. Doing so would require ensuring that any amounts entered in these schedules are assigned to the correct contract currency - which would require some extra effort to check and keep track of.

Dashboard - Snippet

Click to view large-sized image

Open interactive version - 2 pgs

Click to view large-sized image

Data Model - Snippet

Click to view large-sized image

Measures

Balance [GBP]

[Balance [GBP]]] = 
SUMX (
    SUMMARIZE (
        'Projected Cash Balances (Confirmed & Estimated)',
        [Country],
        [Dates],
        "Balance [GBP]",
            CALCULATE (
                AVERAGE ( 'Projected Cash Balances (Confirmed & Estimated)'[Balance [GBP]]] ),
                FILTER (
                    'Projected Cash Balances (Confirmed & Estimated)',
                    'Projected Cash Balances (Confirmed & Estimated)'[Record]
                        = MAX ( 'Projected Cash Balances (Confirmed & Estimated)'[Record] )
                )
            )
    ),
    Balance[GBP]
)

Balance [GBP] excl Est.

[Balance [GBP]] excl Est.] = 
SUMX (
    SUMMARIZE (
        'Projected Cash Balances (Confirmed & Estimated)',
        [Country],
        [Dates],
        "Balance [GBP]",
            CALCULATE (
                AVERAGE ( 'Projected Cash Balances (Confirmed & Estimated)'[Balance [GBP]] excl Est.] ),
                FILTER (
                    'Projected Cash Balances (Confirmed & Estimated)',
                    'Projected Cash Balances (Confirmed & Estimated)'[Record]
                        = MAX ( 'Projected Cash Balances (Confirmed & Estimated)'[Record] )
                )
            )
    ),
    Balance[GBP]
)

Payments [GBP]

[Payments [GBP]]] = SUM('Projected Cash Balances (Confirmed & Estimated)'[Payment [GBP]]])

Payments [GBP] excl Est.

[Payments [GBP]] excl Est.] = 
CALCULATE (
    [Payments [GBP]]],
    'Projected Cash Balances (Confirmed & Estimated)'[Type] <> "Estimated"
)

Receipts [GBP]

[Receipts [GBP]]] = SUM('Projected Cash Balances (Confirmed & Estimated)'[Receipt [GBP]]])

Receipts [GBP] excl Est.

[Receipts [GBP]] excl Est.] = 
CALCULATE (
    [Receipts [GBP]]],
    'Projected Cash Balances (Confirmed & Estimated)'[Type] <> "Estimated"
)

Today

Today = TODAY()