Is it Time to Cancel the Allocation Workbook?

• May 26, 2023
• Malcolm E. Coate

Are your allocations currently being calculated using a massive multi-tab excel workbook?  Does the task of maintaining the formulas seem more time consuming than manually splitting costs?  Is the allocation process holding report deadlines hostage?  Sage Intacct Dynamic Allocations may be a solution.

Dynamic Allocations can be used in a wide variety of scenarios, for example:

• Fringe and Indirect Cost Assignments
• Restricted Revenue Releases
• Multi-Dimension Splits
• Tiered Allocations

If this excites you, you are only 7 clicks (and a paid subscription) away from adding this functionality to your Sage Intacct environment and beginning the journey of allocation automation. 

This “application” must first be turned on under company > admin > subscriptions:

You then confirm this has been added to your subscription.

Configuration is then finished with a save!

Strong internal controls allow you to control who can create / modify the allocation setup of account allocation definitions (the meat of the process), generating allocations (running them), setting up automated allocation timelines, manage groups of multiple allocations, and viewing the allocation log to troubleshoot issues and re-process allocation batches.

Once permissions are updated, you’ll have new tasks within the General Ledger module.  The All tab will reflect normal processing, while the Setup tab is for creating and maintaining each process.

       

Our first focus will be on the account allocation definitions.  These are what we use to build the logic of the allocations and ensures the system can find what amounts we want to allocate, how we want to allocate the percentage splits, and where we want the allocated entries to post.  If we can clearly define the what, the how, and the where, we can automate the process.

Rationale

The setup will start with an id and description to organize the allocations and to make it easy to duplicate as the organization expands.  It is best practice to also clearly layout the methodology of how and why we are allocating a certain way for auditor understanding and to clarify the process for new staff.  You can even upload supporting documents to tie the old way to the newly automated approach.

Dimension treatment

Next, we have to instruct Intacct how to treat our dimensions (standard and user defined) on the allocation entry.  The most important setting is do we want the allocation to allocate across entities (make sure to double check all potential inter-entity account mapping) or keep it within a single entity.  We can then specify how other dimension values will impact the entry as follows:

• Preserve values: the dimension value coded on the original entry will carry forward onto the allocated entry (no need to lose valuable insights on allocated reports)
• Allocation focus: this will be the dimension (or dimensions) that will be changed based on the allocation
• Not considered: values on allocated entry will default as blank (creates smaller, more efficient entries if vendor details aren’t needed on allocated reports). Be careful about what dimension requirements you have on each posting account as the allocation entries will abide by the same constraints, although you can also override a generic value upon posting.
• Per dimension value: only applicable to Project and Employee and would be the same treatment as preserve values; but creates a separate journal entry for each project or employee. Filtering will be restricted on the basis and must also be tagged on project-by-project basis.  Source pool filtering can be done to limit which projects are analyzed for allocations. 

Let’s take a look at an example. In the above screenshot, we would be allocating to multiple departments and posting one entry per project while maintaining the original payroll import codes for location, class, and site.

Source pool

The Source pool is the What of the allocation definition.  This boils down to how would you run a general ledger report to pull the amount you want to allocate (thus there is also no way to allocate costs not coded to a dimension value).  The period is the frequency at which you will be running allocations to ensure the proper amounts get allocated.  These include most system reporting periods (most common are monthly, quarterly and yearly) as well as any custom reporting period. 

We can pull from accrual or cash books as well as user defined in the case of tiered allocations.  Tiered allocations reflect a step-down approach of multiple allocation phases where each phase must be completed and posted to a user defined book (more on that later) before the next phase can be run.  We can also customize the percentage total amount to allocate where we don’t always have to allocate 100% of costs.  Examples include the need to allocate 50% based on driver A and 50% based on driver B or Fringe/Indirect Allocations where each grant needs a different percent of related costs assigned. 

The source must be pooled using account groups, thus even if you only need to allocate a particular salary account, an account group containing only salaries must be used.  This is especially common for clients allocating many expense accounts and wishing to preserve the original natural account coding in the allocation entry.  Dimension filters are optional but can be used to narrow down applicable balances to allocate such as if a To Be Allocated project is used for period end allocations.  It is important to note that when using parent-child dimension hierarchies filtering for the parent will only include values coded direct to the parent (yes, entities work the same way) thus to truly capture everything a dimension group should be used. 

True-up options are particularly important to understand your allocation needs and how often they are run as each will have vastly different effects and can lead to overstated balances.

• Activity Delta: Will only allocate amounts posted since prior allocation run. For this method to work properly and prevent duplication of allocation entries, you must include the allocation user defined book as well as the target account in the source pool.
• Auto-reverse prior post: Reverses original entry on date of new posting and re-allocates full amount.
• None: Cleanest way to tie out allocations without a lot of reversals within the allocation book. Does require deleting the prior posted batch before re-running the allocation batch, or you will have doubly allocated.

In this example, we would only be allocating 100% of the monthly costs coded to accounts contained in the salary and wages group within the primary entity/ sublocations and the shared department.  Since no other dimension filters are selected, it will pull all such values.

Basis

The basis calculates the How, and can be based on financial data (cash, accrual, or user defined) as well as statistical data.  If you wish to allocate based on budgeted information, we recommend setting up a user defined book to track these values.  Statistics can be entered as percentages if you predetermine rates at the start of each year or timely counts in the case of allocations based on full time equivalents or square footage. 

Whether you are allocating based on financials or stats you have the option to determine if the percentages are to be based on time period activity or ending balances.  The time period is also selected on the basis and does not need to match the source pool period; thus, you can allocate monthly expenses using a smoothed year to date percentage.  Dimension filtering can be used to precisely target desired percentages and you even have the option to automatically ignore any value that would be negative.

In this example, are percentages are pulled from a monthly stat entry.  Since we are using a monthly-activity rates we would need to ensure the stat entry has been posted prior to running our allocations, or an error will occur (stay tuned for troubleshooting tips).

Target entry

In this final Where section, we define the final posting details.  This must be posted to a user defined book and journal.  If multiple distinct types of allocations are being setup with different reporting requirements, we would often setup multiple sets of books to make it easier to view and control.  Even though the initial allocated entries post to a user defined book, you can still very efficiently duplicate them to the cash/accrual book after posting. 

A single account must be specified to post the allocated entry to, while you would always be able to reverse the amounts out of the source pool account.  If you run allocations through a single allocated cost account you generally only need a single account allocation definition, but if you desire to allocate line by line (preserve original expense) we would want to setup a definition per applicable gl account to allow for individual destinations. 

Finally, as needed we can override the dimension codes on either the destination side or its corresponding reversal.  To use overrides, the dimension treatment must be set as “Not considered”, but the target destination overrides do not need to match the reversal overrides.  Leaving reversal overrides blank will reverse based on how original transactions were coded.

As a bonus, if you are using Project/Grant billing, these allocated entries can be flagged as billable; but you will need to be careful around incorporating the customer and item dimension to the dimension treatment / overrides.

In this example, we are running both sides of the allocated entry through the 5121 gl account and posting it to the allocation book.

After completing the five sections, we are now ready to allocate.  However, based on the allocation volume, we may want to create an account allocation group.  This will allow us to run multiple at the same time.  We need to give the group an id, name, and determine error processing.  Error processing has two options:

• Stop if a group member in the sequence fails: should be used when sequence of allocations is important.
• Skip and continue if a member in the sequence fails: should be used for large volumes of allocations or when sequence is irrelevant.

To run our allocations, we will use “generate allocation“, then enter:

• Desired posting date of the allocated entry
• As of date to drive time period of our source pool and basis

Allocation run type: individual or group

Allocation log

Immediately after generating the allocation, we are taken to the Allocation log where we can follow the progress in real time or wait for a summarized email describing the successes and/or failures.  After the allocations are processed, we can view the successful journal entries or run reports on the allocation book.  Allocations using the “per dimension value” treatment will have a go between log to show the specific status per project.  Final entries will have a hyperlink description to view the Automated allocation snapshot which shows how the allocation was setup and allow you to download reports showing how the source and basis amounts were calculated and the final split percentages.  This page is where users can delete allocation postings if they are needed to be re-run.

Error troubleshooting

• Source balance not found: no amount to allocate was found, this could be normal based on detail of allocations and activity in that month.
• Basis balance not found: no basis amount was found, check filter options or posted statistics.
• Transactions do not balance: could be caused by inter-entity account mapping issues or other dimension balancing requirements.
• Periods are closed: books do need to be open to post the allocations.

Other tips and tricks

• Recurring allocations can be scheduled to run automatically for future or past periods. For example, if allocations are only processed once a year, you can still efficiently post on a monthly (or even daily) basis.
• When setting up allocations, imports can be used to mass create account groups, allocation definitions, and allocation groups!

What’s next?

Join our complimentary Sage Intacct webinar on Tuesday, May 30^th for an in-depth look at the set up and use of dynamic allocations.

Tags: Month-End Allocations, General Ledger | No Comments »