Model building is the most challenging part for end users transitioning to Workday Adaptive Planning. Fortunately for most end users, their models have already been implemented by the time they get into the system by the implementation team. Although implementors like our team at QBIX can always add new models to your instance, it is valuable (and more cost-effective) to be able to build models on your own. In today’s video, we will learn all the steps involved in creating a pre-paid model as well as gain practice writing formulas on the back end.
Pre-Paid Model Sheet Setup
Before we can begin building the accounts and calculations within a model, we must begin with the sheet setup. Begin by creating a blank modeled sheet and navigating to columns and levels and add the following.
- All Levels – or the levels necessary to the model
- GL Account dimension – This is not your GL accounts but instead it is a dimension that can be linked to the GL accounts
- Supplier – Provider of the prepaid item or service
- Description – Section for notes
- Start Date – Start of contract
- End Date – End of Contract
- Pre-Paid amount – Total amount
- Renewal – Whether or not you want the contract to auto-renew
Of course, this setup can be adjusted to your needs. For example, you may want to create a distinction between the purchased date (cash-out day) and when the contract begins, or maybe some more specific controls with regard to the auto-renew. Adaptive provides the framework for this flexibility however this requires more involved formula writing.
Pre-Paid Model Formula Setup
Many of the formulas used in Adaptive are very similar to Excel in how they function but are very different to set up. The formulas used in this model are as follows.
| Name | Context | Display As | Formula |
| Ppd Amt | Contract total (Input) | Currency | 0 |
| Partial Count | Total count between start and end date (same as partial Headcount) | Number | timefraction(ROW.Start_Date, ROW.End_Date, this) |
| Term | Sums total count over the entire timespan | Number | ROW.Partial_Count[time=this-360:this+360] |
| Monthly Cost | Currency | divf(ROW.Ppd_Amt, ROW.Term) | |
| Renewal | A yes or no to delineate a renewal | Number | ROW.Renew |
| Partial Count (including Renewal) | checking for a renewal, and including no end date to carry the value forward | Number | iff (ROW.Renewal = 1, timefraction(ROW.Start_Date, blank(), this), ROW.Partial_Count ) |
| To IS – Monthly Prepaid (66xx) | This is the total that is pushed to the Income statement. | Currency | ROW.Partial_Count_IncludingRenewal * ROW.Monthly_Cost |
Final Set Up and Review
Once all formulas have been written, you must finish the model by linking it to the income statement. Begin by navigating to the general ledgers and finding the account you want to link the account to and select link. Make sure there are no hard-coded numbers in this account, they will be overridden! After selecting link, find the model and the account we created and be sure to add the dimension filter to ensure no data is double counted.
Now that we are finished with the setup, navigate back to the sheet, right click a cell, and select row details to see the calculations happening on the sheet. Additionally, navigate to the income statement and see your data flowing through as new contracts are added and contracts end.
Looking for Workday Adaptive Planning Help?
Our team is here to help! From new implementations to maintenance and health checks, our team has the tools needed to streamline your budgeting process. Contact us below or visit our website to learn more. Additional tips can be found on the Adaptive Corner.