Build a Prepaids model (step by step)

Modeling can feel daunting at first, but once you've debugged or built a few simple models, you'll start to realize just how powerful Adaptive can be.

In this article, you will learn how to build a simple Prepaids model, step by step!


Some background first...

My background in software engineering and object-oriented design has given me the foundation to create robust, well-structured models. Having started as a user and consumer of Adaptive, I always prioritize the user experience when developing models. My focus is on building models that are:

  • Dynamic: Flexible and adaptable to changing requirements.

  • Easy to use: Intuitive and user-friendly interfaces.

  • Easy to update: Minimizing complexity for future adjustments.

  • Easy to understand: Clear logic and well-documented structures (AND EASY TO READ FORMULAS) --> check this article!

To further enhance the user experience, I make it a point to provide detailed, easy-to-read documentation so that end users know what to do and understand the reasoning behind the model’s design.

As you can see, "ease of use" is my ultimate goal, even when tackling complex financial models such as sales commissions, ARR, COGS models, or balance sheet forecasts.

Unfortunately, I’ve also encountered poorly designed models—ones that are neither dynamic nor maintainable, or that are so convoluted that no one understands what’s going on anymore. These experiences have reinforced my commitment to building models that are not only functional but also efficient and user-friendly.


Prepaids Model: step by step guide!

If you watch my video, please note that there is one formula logic, I would like to update: the calculation of the contract's term (duration in months). 
The formula I wrote in the video may not work in all cases: if your plan version does not have enough past periods in it, it won't work.
You can find the new logic written right below the video (in the Instructions section).



Step by step instructions

Sheet setup: blank modeled sheet.

Hamburger Menu > Modeling > Level Assigned Sheets > New Sheet

Note: I like to keep my sheets's names short and in CAPITAL letters. 
It makes it easier to read in formulas.

Here are the elements you will need to add to your sheet, under Columns and Levels

- Levels (required), select all or the levels you will budget on.

- GL Account Dimension (a dimension that will contain the list of the GL accounts you want to budget prepaids in) --> your output calculation will be then linked to the equivalent GL account.

- Supplier as a Dimension

- Description or Notes field, free text input for your notes and comments...

- Stard Date: date type, code: Start_Date

- End Date: date type, code: End_Date

- Prepaid Amount: your total contract's amount: Initial Balance type (a modeled account will be automatically created), code: Prepaid_Amount

- Renewal: checkbox --> whether or not you want the contract to automatically renew after the end date (so you don't need to maintain it constantly).


This is a basic setup that you can adjust for your own needs of course! For example, you can add later on the cash impact of your prepaids for your Balance Sheet forecast!


Formulas for the modeled accounts

Under Modeled Accounts, you will build 7 formulas:
  • Prepaid_Amount
    • This the initial balance you created as a column in the steps above
      • Since it's an initial balance type, an account is automatically created!
    • Display as: Currency
    • Formula: 0 (leave as is)
  • Partial_Count
    • This formula calculates counts either a partial amount or full 1, for the months that are between your start and end dates (works just like a partial headcount)
    • Display as: Number
    • Formula: timefraction(Row.Start_Date, Row.End_Date, this)
  • Term
    • The number of months/periods between your start and end date.
    • Please note that the formula I show in the video may not work in all cases so I am  providing an alternative below (which is better in any case!)
    • Display as: Number
    • Formula: this.version.positionof (ROW.End_Date.month) - this.version.positionof (ROW.Start_Date.month) + 1 
    • Old formula: ROW.Partial_Count[time=this-360:this+360] (may not work if your plan version does not carry enough past periods)
  • Monthly_Cost
    • Calculates what is the monthly cost of your contract (this is not the output to the P&L)
      • It simply divides your total contract amount by the term (# of months)
    • Display as: Currency
    • Formula: divf(ROW.Prepaid_Amount, ROW.Term)
  • Renewal
    • Not required really, but it simply displays a 0 or 1 (no renewal / yes renewal)
    • Display as: Number
    • Formula: Row.Rewew
  • Partial_Count_Including_Renewal
    • If there is a renewal, the count will go on and will ignore the end date.
    • Display as: Number
    • Formula: 
                                iff (ROW.Renewal = 1,
                                        timefraction(ROW.Start_Date, blank(), this),
                                        ROW.Partial_Count
                                )
  • To_IS_Monthly_Prepaid
    • This is the final monthly expense that will be linked to your P&L's GL accounts
      • A blend of the Monthly_Cost and the Partial_Count_Including_Renewal!
    • Display as: Currency
    • Formula: Row.Partial_Count_Including_Renewal * ROW.Monthly_Cost

Don't forget to link your output (To_IS_Monthly_Prepaid) to the GL accounts filtering on the equivalent GL_Account Dimension!


Comments

Post a Comment

Popular posts from this blog

Adaptive REST API Calls for Platform Customers with User Sync Enabled

How to Make Adaptive REST API Calls for Platform Customers with User Sync Enabled If you’re a platform customer (HCM + FINS + Adaptive) or using Workday HCM or Financials alongside Adaptive and want to create custom scripts leveraging Adaptive’s REST API (XML), you’ve likely encountered a key requirement: providing Adaptive user credentials (username and password). For standalone customers or those not using user sync, this typically involves creating an integration user in Adaptive. Easy peasy.  But what if you’re using user sync? How do you supply credentials for API calls in the XML input? In this article, I’ll guide you through the steps to generate the necessary credentials when user sync is enabled. I’ll also share helpful links to official documentation to ensure a smooth integration process. 1. Overview The process allows Workday-synchronized users to make API calls to Adaptive Planning using Workday credentials . The key steps involve: Generating an OAuth token using Work...
Read more

Introduction to Adaptive's API

To integrate data in Workday Adaptive Planning, customers typically use the integration module to connect with ERPs, SFTPs, AWS S3, Snowflake, SalesForce, Excel, and Google Sheets, etc.  You can also use third party platforms such as Tray.ai , Boomi , Matilion etc. which leverage Adaptive's APIs in a user friendly setup.  And lastly, you can write your very own scripts (C#, Python, Powershell...) to make these API calls.  Most  common Adaptive's API use cases Import Data from ERPs Actual summaries Actual transactions Assets, amortization, depreciation Transactional details by Supplier Payroll details and taxes Import Sales data from CRMs or datalakes Pipeline, churn, opportunities and customer list Billings and bookings ARR and MRR Import Headcount data from an HRIS Current headcount Terminated Hired, not started Wages and compensation plans Benefits Other Demographics Import Other data: Exchange Rates Weather Import Meta data: currencies, attributes, dimensions such...
Read more

What is OAuth 2.0 and how to set it up in Adaptive?

Image
Uh Oh... OAuth? Was ist das? Adaptive can connect with other applications's via REST APIs using the OAuth 2.0 authentication.  I will explain what OAUTH is so you will no longer ask: "What is this?" (Was ist das?) and feel like someone is talking a foreign language to you!   What is OAUTH 2 really? How does it work and what do you need to do in Adaptive? Let me give you 2 sets of explanations: a real life illustration to demistify OAuth 2.0 and a more technical summary. And lastly high level steps on the setup in Adaptive. If you have no idea what OAUTH is, continue reading, otherwise jump to the 2nd section.  In this example: imagine you need to connect to Microsoft Sharepoint and you want to bring CSV files  into Adaptive. 1) Illustrational explanation for total newbs: OAuth Access vs Hotel Access! OAuth 2.0 can sound overwhelming at first, but it's essentially a way for applications to securely talk to each other and access data. Let me break it down into sim...
Read more