In Workday Adaptive Planning, there are 3 different types of formulas: Master formulas, Shared formulas, and Ad-hoc formulas. Each formula type uses the same logic, and all formula types can leverage the Formula Assistant tool for easy breezy creation, however; the main difference for each formula type is in how it shows up on the sheet.
Master Formulas
Master formulas are used to calculate an account’s value based on a formula. What this means on a sheet is that users will not be able to adjust this value – unless they adjust the values of the accounts used in the formula. Creating a Master formula will overwrite all previously input data from the account to apply the new formula across all versions.
As an example, my account ‘6340 Telephone’ should be planned using the number of full-time heads multiplied by a stored assumption for telephone cost our employees receive as a stipend each month. Using a master formula, users will see a grey line in their income statement where that formula is written and will not be able to change that number, unless they increase the FTE numbers on the personnel sheet or adjust stored the phone stipend assumption.
To add a master formula, navigate to the account settings of the desired account. Once there, input the desired Master Formula in the Data Type section of the options panel. You have the ability to override the formula by version, which means each version could use a different formula. Budgets could differ from Forecasts if you adjust the Formula Version Selector and change the Override Formula Setting to ‘Override Formula’.
Now, on my Income Statement, my account 6340 Telephone will be greyed out with blue triangles indicating a formula is present on this account. The biggest take-away is that this formula cannot be changed unless the values referenced in the formula are adjusted.
Shared Formulas
First, let us navigate to where shared formulas are created.
Navigation Menu > Formulas
Within Shared Formulas, you have the ability to apply different formulas to accounts by version and by level. Shared Formulas differ from Master Formulas in the flexibility of applying different formulas by your level structure as well as flexibility in overwriting the formula – more on that later.
To apply a Shared Formula, let us first select the account we want to which we want to apply the formula. Once again, we will utilize our 6340 Telephone account. In the drop-down menu in the top ribbon, select your account.
Next, select the level to which you want the shared formula to apply. All checkboxes will submit the formula you submit first. If you want different formulas to different levels those will need to be submitted one at a time or mass imported. I am going to only select the level – “Sales – North” to illustrate that it will only be applied to one level.
It is important to note that these shared formulas will only be applied to the single version you select when creating the formula. Conversely, Master Formulas, when present, need to apply to all versions (even if they differ from version to version for a given account).
I will be applying this formula to the Working Budget by selecting that version in the upper right-hand corner version drop down.
Input your formula into the Formula pane on the right-hand side of the window. You may leverage the formula assistant to aid in the formula creation. Syntax for the formulas is uniform across the Workday Adaptive Planning platform.
The option to Preserve or Remove user edits is used in the sheet. This will determine if users can over-write the formula or if the formula will be mandatory and users will not be able to overwrite the formula.
If I return to the Income Statement, this time after applying a shared formula to my 6540 Telephone account, you will notice that there is still a blue triangle in the bottom right corner – indicating a formula is used. But in this instance, it is not greyed out. What this means is that users can utilize the shared formula as a reference & change the value by adjusting the FTE account or the Phone Stipend amount, or they can simply overwrite the value.
In March and April, I have overwritten the formula with the value of 300. These months are no longer driven by the formula as indicated by no blue triangle in the bottom right corner. This gives users the flexibility to use driver-based planning to see budgets and forecasts, but also the ability to manually adjust where they see fit.
If users decide they want to return to their shared formula, all they have to do is delete their input values & save the sheet. The shared formula values will return. Below, I returned the value for April, but left March as an overwritten value.
Lastly, if I were to navigate to a different level, for example “Sales – South”, there would be no shared formula on the telephone account as I only checked the box to apply the formula to a single level – “Sales – North”. This gives users the flexibility to apply formulas both by version as well as level. Additionally, it gives users the ability to seed forecasts and budgets using driver-based planning and other formulas while allowing them to overwrite those formulas.
Ad-hoc Formulas
Ad-hoc formulas allow for the greatest flexibility. It is important to remember with greater flexibility comes a greater need for responsibility. Ad-hoc formulas allow users to write their own formulas on the fly within the sheet. This means any given month users can right-click > formula assistant and write their formulas. Any formula entered applies specifically to the account/level/version combination selected and only that combination.
Ad-hoc formulas are best used for one-off driver-based formulas or easy calculation needs. Anywhere where large plugs should be applied without going to each intersection to insert the formula are needed, ad-hoc formulas are not the answer.
Key take-aways and differences
Master Formulas: Applied at the account and version intersection. These formulas cannot vary by level and cannot be changed by users on the planning input sheet. This type of formula must be set up by an administrative user who has access to editing the account settings.
Shared Formulas: Applied at the account, version, and level intersections. These formulas can be changed on the input sheet from what is plugged and returned to the shared formula, if needed. This type of formula must be set up by an administrative user who has access to the shared formula module.
Ad-hoc Formulas: Applied individually at specific intersections one at a time. These are the most flexible formulas but also the formulas that require the most maintenance and upkeep.
Each formula type has a time and a place for best execution. My hope is by using the guide above you will be able to determine which formula type is best for your business needs and will eliminate manual work while helping drive faster business decisions.
