Use case:
Complex allocations with  InfoFlex

This use case describes how to use the InfoFlex process to allocate monies among departments within a business, to determine the true costs of operating any given department.

The business problem

Allocations let you factor in the opportunity cost of running a department with respect to the costs incurred by other departments. In other words, after applying allocations you can see how much the department actually costs to operate.

For example, in the following table, the Human Resources department may seem inexpensive and the IT department costly; however, by adding the portion of the IT resources consumed by the HR department, a more accurate picture emerges:

Department	Expenses Before Allocation	Allocated Expense (A)	Computers (Cost Driver) (B)	Allocation Expense*	Expenses with Allocation
Toronto	10,000	3,996	20	360	10,360
Montreal	12,000		30	540	12,540
New York	15,000		60	1,080	16,080
Los Angeles	19,000		100	1,800	20,800
Corporate	3,000		5	90	3,090
Information Technology	5,000		3	54	1,058**
Human Resources	1,500		4	72	1,572
Total	65,500	3,996	222	3,996	65,500
Allocation Expense = (Total A / Total B) x Dept. B; ($18 per computer) (A) is made up of all “Outside Expenses” (5,000 - 3,996 + 3,996/222 3)

The example above is a simple calculation that can be done in Excel. However, if using a more realistic example, the complexities of using Excel become apparent. Furthermore, Journal Entries for every department must be done every month to assign the allocations. Restatement and Journal Entries to accounts after the allocation has been calculated would require that the Journal Entries to the allocations be altered. This can be time consuming.

Also, with Excel there is no audibility, and there is no audit log for when exactly the allocations were last calculated.

The solution

Using the InfoFlex process, you can easily define monthly, quarterly, or annual allocations that move data from one part of the cube to another. As well, InfoFlex several types of spreading, and can accommodate multiple allocations in a one step.

 Use Case: For a more detailed description of using InfoFlex, see Basic allocations with InfoFlex.

The benefits

Allocations are consistently performed through logical sequential steps, avoiding inconsistent results as well as human error.

To continue the above example, when managers add computers to their departments, or the IT Department adds expenses to its Outside Expenses account, the InfoFlex process can be rerun to automatically factor in these changes. There is no need to go back to an Excel workbook to recalculate the difference.

When creating new budgets or as actual results come in, the InfoFlex process can be run to automatically calculate the new allocations and expenses being incurred by every department.

Allocations are rerun for all departments and for all values simultaneously, removing the need to make adjusting Journal Entries. This saves the Finance Department time and increases the accuracy of reporting to management.

How it's done

There are two major steps involved in calculating allocations: the first is to actually allocate the amounts to the various departments; the second is to subtract (in this example) the allocated amount from the Information Technology Department. FP&A Plus can perform these types of complex allocations automatically on Actual, as well as Budgeted, data.

The common dimensions used for this example are Time and Version. The Account being allocated is the Outside Services account to the Allocated Overheads account, and the Organization being allocated is the Information Technology Department to Total Organization based on the Number of Computers in each department.

InfoFlex divides the Outside Expenses by the total Number of Computers for the entire organization then multiplies that number by the number of computers in each department. InfoFlex can also create a contra entry for Information Technology’s Outside Expenses account in order to reduce their expenses by the total amount being allocated.

The final result of the overhead allocation is:

	Expenses Less Allocations (A)	Allocated Expenses (B)	Contra Account to Allocation (C)	Total Expenses with Allocations (A) + (B) + (C) (+)
Toronto	10,000	360	0	10,360
Montreal	12,000	540	0	12,540
New York	15,000	1,080	0	16,080
Los Angeles	19,000	1,800	00	20,800
Corporate	3,000	90	0	3,090
Information Technology	5,000	54	-3,996	1,058
Human Resources	1,500	72	0	1,572
Total Organization (+)	65,500	3,996	-3,996	65,500

Working in conjunction with the Audit Log, the Finance Department can determine exactly when an allocation was run and by whom. This ensures full control and audit over how data is entered. Because the Finance Department sets up the drivers, they cannot be modified by users, who must follow these rules every time the allocations are run.

Next steps