David Churchward FCMA, CGMA, systems and development director at telecommunications company Azzurri Communications, continues our series exploring the potential of Excel add on program PowerPivot.
It’s a concept engrained in management accountants everywhere from an early stage - matching costs and revenues. But it is essential to apply those costs and revenues to the correct accounting period. I’ve seen many extravagant spreadsheets that do this very well and now we have the concept of being able to do this in PowerPivot.
The benefits of doing this are obvious once PowerPivot is engrained in the way you do things. You can create a measure once and then simply maintain a list of valid transactions that require prepaying or deferring and PowerPivot will do the rest. Taken to the fullest extent, your model becomes self-reconciling.
The Dataset
The dataset that I’m using looks like this:
The Prepayments table contains a list of transactions that require prepayment / deferral. The critical elements are the nominal accounts and the start and end dates. Nominal accounts determine which P&L accounts and balance sheet accounts are affected by the prepayment transaction. The start and end dates tell us the timeframe to which the transaction relates.
Start and end dates tables are sequential lists of dates covering the timeframe of any likely prepayment. Details on how to create these tables can be found at this link.
Days or Months?
It’s not unusual to see prepayments run on a monthly basis. For example, an invoice covering a full year would see 1/12th allocated to each month in that year. However, whilst this isn’t necessarily materially wrong, it isn’t entirely accurate either. What happens when an invoice charge period starts and ends in the middle of a month?
Calculating prepayments on the basis of days is more accurate, but ultimately, PowerPivot can handle both approaches (and a combination of the two if appropriate). In this post, I’ll explain how to calculate the Days approach but you can get a full overview of both approaches by following this link.
Days Prepayment
I’ll jump straight into the DAX expression and then explain what it’s doing:
This looks complicated, but the underlying equation can be summarised as follows:
Deferral Value = Invoice Value * (Number of future days / Total days on Invoice)
For example, if you have an invoice for £30 relating to the period 20th June 2012 to 19 July 2012 and you’re working on the June month end, your equation would be:
= 30 * (19 / 30)
which evaluates to £19.
But first we evaluate when to evaluate!
The first part of our DAX expression is as follows:
There’s two things happening here:
- IF(COUNTROWS(VALUES(Months[MonthEndDate))=1 is used to ensure that our measure only evaluates when we have one MonthEndDate on our report. PowerPivot is an aggregation model so it will inherently try to run each measure at every possible aggregation level. Obviously, we want our model to run a separate evaluation for each MonthEndDate so it’s important that we only have one.
- The second “IF” statement is used to evaluate which transactions should be included in the calculation. This is based on the following criteria:
a. LASTDATE(VALUES(Prepayments[Prepay_Start]))<LASTDATE(VALUES(Months[MonthEndDate])) determines that the start date of the prepayment transaction is less than the date being evaluated on our report
b. LASTDATE(VALUES(Prepayments[Prepay_End]))>LASTDATE(VALUES(Months[MonthEndDate])) determines that the end date of the prepayment transaction is after the date being evaluated.
The Prepayment calculation
Once we’ve determined when our calculation needs to run, we then tell PowerPivot what we want it to do. Let’s refer back to the basic formula that we want to execute:
Deferral Value = Invoice Value * (Number of future days / Total days on Invoice)
Translating this into PowerPivot as follows:
- Invoice Value =
This simply takes the PandL_Value from our Prepayment table to use as the value in our equation.
- Number of future days
This counts the number of rows in our Days table that fit between the MonthEndDate being evaluated and the Prepay_End date on the transaction.
- Total days on Invoice
This counts the number of rows in our Days table that fit between the Prepay_Start date on the transaction being evaluated and the Prepay_End date on the transaction.
SUMX finishes it off!
I’m sure you’ve noticed that our deferral value is wrapped in a SUMX function. This is because we need the deferral value equation to run for each transaction independently. SUMX is an iterative function so it runs the equation for each record in the prescribed table and then sums the results.
For more detail see Prepayments & Deferred Revenue
Links
CIMA professional development