Skip to main content

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 planner 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 sheet's name 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!


Labels:

Comments

Post a Comment

Popular posts from this blog

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 and transaction details (by supplier, employee...) Assets, amortization, depreciation Payroll details and taxes Import Sales data from CRMs or datalakes Pipeline, churn, opportunities and customers  Billings, bookings, revenue, ARR Import Headcount data from an HRIS Current headcount and terminations Hired, not started Wages and compensation plans Benefits and other demographics Import Other data: Exchange Rates Weather Meta data: currencies, attributes, dimensions suc...
Post a Comment
Read more

What are Metric accounts and when to use them?

 I am often asked: "What are Metric accounts in Adaptive and what is their use case?" Before we jump into the weeds, you can create "global" metric accounts, called standard metric accounts  and within cube sheets.  To create a standard metric account, go to:  Menu > Modeling > Metric Accounts Adaptive will come with a couple of pre-built Metric accounts, such as Gross Margin % What are Metric Accounts? A metric account in Adaptive is a calculated account , meaning it carries a formula .  It does   not carry any data entry .  It can not be a rollup account,  meaning it can not have any "children accounts" rolling up to a parent. Metric accounts are typically used to calculate ratios , this is why they can only be either a number or a percent ! But... there is more subtility in them! Metric accounts are different from the other calculated accounts (custom, modeled, cube etc.) in the sense that their formula is also computed at the level an...
Post a Comment
Read more

One (perfect) Headcount Planning Process in Workday Adaptive for FP&A

FP&A: The Perfect Headcount Planning Process in Workday Adaptive for non Platform customers: Streamlining integration, reconciliation and budgeting This article is for companies that are not on the Workday Platform  (HCM, FINS, Adaptive) and are missing out on the integrated cycle of hiring, planning, reconciliation, and system-wide alignment that Workday offers. I sometimes hear complaints (luckily not too much) about Workday HCM, Adaptive or FINS... but the truth is, no other tool on the market currently offers a complete solution that spans the entire hiring process—from budgeting and requesting to hiring, reconciling, and publishing plans across systems. Workday is the only one with this capability right now!  I’m not paid by Workday to write this… But after working with several HRIS, ATS, and ERP systems, I can confidently say that Workday stands out for its customizable workflows, overall ease of use, and seamless communication across all three systems -- assuming yo...
Post a Comment
Read more