Gift Cards Modelling

2 posts / 0 new
Last post
Jonathan Kress A+ 0
Gift Cards Modelling

Hi all, 

I'm trying to produce a financial model showing the impact of gift card sales which is a significant line item for a client of mine. This would need to show a big cash injection on sale date then a release into P&L as the gift cards are used / provided for as breakage. Has anyone got a solution of workaround as there doesn't appear to be an out of the box module. 


Jun Yan A+ 124

Hi Jonathan,

This would be somewhat similar to managing something like Prepayments / WIP / Deferred revenue / Unearned Income.

Two options:

  1. Segregate the Balance Sheet line item out (might be suggested as there may be GST / Sales tax implications).
  2. Build a module that passes out Revenue recognition and net movements into Other Current Liabilities and Revenue.

Option 1)

This is more complex, you'll need to know how to flow the deferral through the entire model, ie creation of a historical line item, pulling that into your module and then passing it out through to the respective P&L / BS line items. Here's are some screenshots of a Module Suite for Deferred Revenue we use.

You'll see a location for Revenue Invoices / Billing / Card Sales, which creates the initial Cash Movement

You'll then see a location for Revenue Recognition / Card Recognition, which creates the P&L recognition to pass out, which is balanced against the liability.

You'll also see we pass eliminations out alongside the recognition as we need to ensure the GST and other implications are factored in.

You can also create a separate module to link in the Amounts if you want to separately create a module to calculate Units x Prices / Granularity to drive these movements.

Below is the P&L / BS / CFS output of the assumptions, as you can see, cashflow + BS moves on the "Invoice" timing. P&L moves on the recognition timing, and BS liability amortises accordingly.

We have it done this way as it allows us to add a layer of Accounts Receivables + appropriate GST treatment into Deferred Revenue, but depending on tax obligations for this, you may be able to ignore.

Option 2)

This is the simpler option, but do be careful doing it this way, as GST may not get addressed properly. You do still need to understand how to customise and link modules with this, but much fewer steps and you should be able to build this in isolation and manage the rest via links into an existing Dynamic Template.

Screenshot below is an example of a customised module. I did some simplistic drivers with just units x price, which reflect the Value Sold.

You should also see a separate set for Value Used (recognition). You'll see at the bottom of the screenshot that the recognition is linked into a Revenue module to carry into the P&L.

You'll see in the second screenshot, I've linked the items into an Other Current Liabilities Movement module and attached the cashflow impact to Cash Receipts. Linking both is important as it nets off the P&L / BS movements, as well as ensures that a singular cash receipts line is shown for the initial inflow.

In the next set of 3, you'll see the interactions on the P&L / BS / CFS. The issue with this method is you'll see that the net cashflows on the big ticket item is correct; however the GST is based on the net of cashflows and this could be very wrong. I haven't shown in this example, but you'll need to link the sale block into GST, so you can allocate the sale to GST, but the recognition to no GST so you don't get the funny GST cashflow movements. As these are cash sales, this actually might be okay, but if there's an Accounts Receivables component to anything, then the GST impact could more complicated.

Hope that helps!