Accrual Calculation

macfuller

Active Member
Joined
Apr 30, 2014
Messages
294
Office Version
  1. 365
Platform
  1. Windows
I'm hoping someone has done this already and has some quick formulas to offer - this could be a tough one otherwise.

I would like to show accruals for various time periods. Accrual accounting is complex so my request here is a bit simplified, but it's getting the monthly report tied to a histogram table that's confusing me.

I would like to have a table of accruals such that I can show in what month the accrual hit based on when we received the invoice. Example:
Months AfterMay 2018June 2018July 2018Aug 2018
0$x$x$x$x
1$x$x$x
2$x$x
3$x
4

<tbody>
</tbody>

Looking at this from August as the current month, invoices received in May have been around for 4 months and thus might have accruals in each preceding month 0-3. Invoices from July have only been around 2 months and so could only have accruals in months 0 and 1.

What is an accrual? For our purposes we will define it as a item that was received but not paid for as of the specified time period (usually because we have not received an invoice and cannot pay without one). So the June/2 cell in the table above means that we received the item in June but had not paid for it as of August (June being month 0).
Internally we're debating whether we want to show only accruals that are still there or show anything that was accrued earlier but has now been paid. So for the May column we would only show row 3 since those are still open as of August... but for now we'd like to show anything was was accruing at any given time. If an item worth $200 was received in May but not invoiced until July the $200 should show up as part of the monthly totals for rows 0 (May) and 1 (June).

So the key fields are the Invoice/Voucher $ amount, the date the item was received (accrual start date), and the date it was paid. I use the calculated column as below to tell me how many months the item was in accrual status.
Code:
Accrual Duration=IF (
    ISBLANK ( Vouchers[Accrual Start Date] ),
    BLANK (),
        IF (Vouchers[Accrual Start Date] > Vouchers[Payment Date],
        BLANK(),
            IF(ISBLANK ( Vouchers[Payment Date] ),
            DATEDIFF ( Vouchers[Accrual Start Date], TODAY (), MONTH ),
            DATEDIFF ( Vouchers[Accrual Start Date], Vouchers[Payment Date], MONTH )
        )
        )
)
Because DATEDIFF blows up if the 1st date is later than the 2nd I have the test statement. (How could we pay before we receive the item? It's rare but for things like emergency repairs we can pay in advance, and even a single row kicks out the error.)

My first difficulty is in defining the accruals. This measure gives everything that is currently accruing (I think).
Code:
Accruals:=CALCULATE (
    SUM(Vouchers[Voucher Amount]),
    FILTER(Vouchers,
    ISBLANK ( Vouchers[Payment Date] )
        && NOT ( ISBLANK ( Vouchers[Accrual Start Date] ) )
)
)
But I want to know items that were in accrual status in prior months even if they're paid currently, so I want to include vouchers that have a payment date where the payment date - the accrual date < the number of months in the selected time period as above. 'AP Calendar' is my related date table.

Code:
Accruals :=
CALCULATE (
     SUM(Vouchers[Voucher Amount]),
    FILTER (
        Vouchers,
        NOT ( ISBLANK ( Vouchers[Accrual Start Date] ) )
            && IF (
                ISBLANK ( Vouchers[Payment Date] ),
                TRUE,
                IF (
                    Vouchers[Accrual Start Date] > Vouchers[Payment Date],
                    FALSE,
                    IF (
                        Vouchers[Accrual Start Date] > MAX ( 'AP Calendar'[Date] ),
                        FALSE,
                        DATEDIFF ( Vouchers[Accrual Start Date], MAX ( 'AP Calendar'[Date] ), MONTH )
                            <= Vouchers[Accrual Duration]
                    )
                )
            )
    )
)

I put the Vouchers[Accrual Start Date] > Vouchers[Payment Date] inside the IF statement thinking that if I had it as a separate NOT() then the DATEDIFF problem would still appear. I'll ignore any items that were received after the payment date (Accrual Start Date > Payment Date). Similarly I'll ignore any accruals that start after the selected calendar date.

If the Accrual measure is correct I can then get to my final table with two measures.

Code:
Monthly Accrual:=CALCULATE (    [Accruals],
    FILTER ( Vouchers, Vouchers[Accrual Duration] <= MAX ( histogram[Bins] ) )
)

and the final measure
Code:
Net Accrual:=CALCULATE (    [Monthly Accrual],
    FILTER ( Vouchers, Vouchers[Accrual Duration] >= MAX ( Histogram[Bins] ) )
)

These return values, but as to whether they're returning the *correct* values is something else.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
I’ve literally just done a report on trade debtors and trade creditors based on a list of invoices.

My solution was like.

[Debtors]:=

VAR EndDate = MAX ( Calendar[Date] )

RETURN

CALCULATE (
[Measure],
Filter (
Invoices,
Invoices[Invoice Date] <= EndDate
&& ( Invoices[Payment Date] > EndDate || ISBLANK ( Invoices[Payment Date] ) )
)
)

In my case a blank in the payment date means the invoice is outstanding.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,123,134
Messages
5,599,914
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top