Question: I wrote a formula to figure out a rate based on a ratio of my GL accounts that are linked but it looks like the value that the formula is pulling in from prior year comes from the budget rather than actuals, yet there are actuals in my GL accounts. How can I fix this?
In this scenario, the GL accounts are linked to a modeled sheets. Modeled sheets currently do not support the function of actuals overlay so when these linked GL accounts are referenced in other formulas for time periods that are considered to be actuals, the values are actually pulling in budget values, because the source data is not in actuals. How can this be remedied then? The answer is with custom accounts. If you were to build a set of custom accounts that mirror the linked GL accounts, then you could set up either shared formulas or master formulas in the custom accounts to refer to the GL linked accounts. This formula would be very simple reference that points to the linked GL accounts (eg: =ACCT.GL_Linked_Example). This way, both actuals and plan data in a version with actuals overlay will flow into the custom accounts. Then, as a final step, you can build your original formula for the rate using custom accounts we just built to pull in appropriate values in place of previously used GL linked accounts.
Let's look at the steps needed to accomplish this:
Create a set of custom accounts that mirror your linked accounts. you can create them manually or via a bulk upload. Navigate to NavIcon>>Modeling>>Model Management>>Custom Accounts.
Note: You can take a printable view of your linked accounts to make the creation process of custom accounts easier.
Here, you can do one of 2 things: after creating the custom accounts, you can include master formulas separately on account by account basis, or as part of the bulk import, you can include the master formulas referring to the linked GL accounts. There will be a column for master formula input on the import template.
Optional step, but you can include the custom accounts on a standard sheet to make sure the values are what you expect. A matrix report could also work.
Now, you can write any formula that you need using the custom accounts we just created above.
As a final step, you can run a report or look in a related sheet at the final formula calculation to verify that it is pulling in actuals values appropriately.