epicor advanced allocations - sage partner portal · epicor advanced allocations user guide 2 note:...

Epicor Advanced Allocations

User Guide

Version 7.4a

Copyright

Program copyright © 2011 Sage Software, Inc. This work and the computer programs to which it relates are the property of, and embody trade secrets and confidential information proprietary to, Sage Software, Inc., and may not be reproduced, copied, disclosed, transferred, adapted or modified without the express written approval of Sage Software, Inc. Manual copyright © Sage Software, Inc. Printed in U.S.A. All rights reserved. This document may not, in whole or in any part, be copied, photocopied, reproduced, translated, transmitted or reduced to any electronic medium or machine-readable form without prior consent, in writing, from Sage Software, Inc.

Trademarks Epicor is a trademark of Epicor Software Corporation.

Windows, Internet Explorer, and Excel are registered trademarks of Microsoft Corporation.

Other company or product names mentioned may be trademarks or registered trademarks of their respective holders.

Warranty Sage Software, Inc. makes no representations or warranties with respect to the contents of this manual and specifically disclaims any implied warranties of merchantability or fitness for a particular purpose. Any statements made by a dealer or any party other than Sage Software, Inc. are not warranties and you cannot rely on them in deciding to license the software. We welcome user comments and reserve the right to revise this publication and/or make improvements or changes to the products or programs described in this publication at any time, without notice.

Limitation of Liability Neither Sage Software, Inc. nor anyone else who has been involved in the creation, production, or delivery of this software or its accompanying documentation shall be liable for any indirect, incidental, special exemplary or consequential damages, including, but not limited to any loss of anticipated profit or benefits, resulting from the use of the software or its documentation or arising out of any breach of warranty. Sage Software, Inc.‟s cumulative liability to you or any other party for damages for any cause whatsoever will be limited to the price you paid for the product that caused the damages.

iii

Table Of Contents Chapter 1 - Introducing Allocations ............................................................................................................................ 1

Prerequisite Knowledge ............................................................................................................................................ 1

Sources of Information .............................................................................................................................................. 1

Conventions Used in this Guide .............................................................................................................................. 1

Getting Around in Advanced Allocations .............................................................................................................. 2

Toolbar ..................................................................................................................................................................... 2

Windows, Dialog Boxes, and Forms .................................................................................................................... 2

Right Mouse Click .................................................................................................................................................. 2

Cursor Movement .................................................................................................................................................. 2

Spin Buttons ............................................................................................................................................................ 2

Form Maintenance ................................................................................................................................................. 2

Tabbed Forms ......................................................................................................................................................... 2

Table Forms ............................................................................................................................................................ 3

Record Lookups ..................................................................................................................................................... 3

User, Company, and Record Stamp Display ...................................................................................................... 3

Lookup Windows ................................................................................................................................................... 4

Technical Support .................................................................................................................................................. 5

Online Help ............................................................................................................................................................. 5

Using this Guide ........................................................................................................................................................ 6

Chapter 2 - Basic Concepts ........................................................................................................................................... 7

Introduction ................................................................................................................................................................ 7

Definitions ................................................................................................................................................................... 7

Types of Allocations .................................................................................................................................................. 7

Defining an Amount Allocation ............................................................................................................................... 7

How Amount Allocations Are Processed ........................................................................................................... 8

Defining a Percentage Allocation............................................................................................................................. 8

How Percentage Allocations Are Processed ....................................................................................................... 9

Other Options for Percentage Allocations ............................................................................................................ 10

Offset Accounts .................................................................................................................................................... 10

Rounding Accounts ............................................................................................................................................. 10

Source Pools .......................................................................................................................................................... 10

Epicor Advanced Allocations User Guide

iv

Transaction Allocations ....................................................................................................................................... 11

Processing Segment Ranges .................................................................................................................................... 11

Filters and Variables ................................................................................................................................................ 12

Processing Steps ....................................................................................................................................................... 13

Chapter 3 - Formula Concepts.................................................................................................................................... 15

What are Formulas? ............................................................................................................................................. 15

Global vs. Local Formulas ................................................................................................................................... 15

Formula Maintenance .............................................................................................................................................. 15

Creating a New Global Formula ........................................................................................................................ 15

Creating a Local Formula .................................................................................................................................... 16

Applying a Global or Local Formula ................................................................................................................. 17

Editing a Global Formula .................................................................................................................................... 17

Saving a Local Formula as Global Formula or Vice Versa .............................................................................. 17

Creating Formula Lines ........................................................................................................................................... 18

Accounts Formula Type .......................................................................................................................................... 18

Periods and Years................................................................................................................................................. 19

Company ............................................................................................................................................................... 20

Balance Types ....................................................................................................................................................... 20

Valuations ............................................................................................................................................................. 20

Currency ................................................................................................................................................................ 21

Account Values ..................................................................................................................................................... 22

Reference Codes ................................................................................................................................................... 22

Constants Formula Type ......................................................................................................................................... 22

Plan Sheet Values Formula Type ........................................................................................................................... 22

Completing the PS-Acct # and PS-Row ID Formula Lines ............................................................................. 23

Processing PS-Acct # and PS-Row ID Formula Lines ..................................................................................... 23

Completing the Published Data Retrieval Formula Line ................................................................................ 24

Processing Published Data Retrieval Formula Lines ....................................................................................... 24

Printing the Formula Definition ............................................................................................................................. 25

ERP Configurations ................................................................................................................................................. 25

Chapter 4 - Database Queries ..................................................................................................................................... 27

Setting up Database Queries in Formulas ............................................................................................................. 28

Table Of Contents

v

Microsoft SQL Server Data Source Definition .................................................................................................. 30

Microsoft Access Data Source Definition .......................................................................................................... 31

Microsoft Excel Data Source Definition ............................................................................................................ 31

ODBC Data Source Definition ............................................................................................................................ 32

OLE-DB Data Source Definition ......................................................................................................................... 33

Setting up Linked Server Queries in Formulas .................................................................................................... 33

SQL Variables ........................................................................................................................................................... 36

Date Processing .................................................................................................................................................... 37

Using User-Defined Query Parameters ................................................................................................................ 38

Chapter 5 - Setting up Allocations ............................................................................................................................. 41

Amount Allocations ................................................................................................................................................. 41

Step-by-Step Instructions .................................................................................................................................... 41

How Amount Allocations Are Processed ......................................................................................................... 46

Percentage Allocations ............................................................................................................................................ 48

Step-by-Step Instructions .................................................................................................................................... 48

How Percentage Allocations Are Processed ..................................................................................................... 56

Maximum Allocated Amount ............................................................................................................................ 61

Advanced Options ................................................................................................................................................... 61

Source Pools .......................................................................................................................................................... 61

Transaction Allocations ....................................................................................................................................... 62

Calculating Allocations Using Amounts Method ............................................................................................ 64

Plan Sheet Values in Formulas ........................................................................................................................... 65

Using Temporary Accounts to Compute Totals .............................................................................................. 66

Chapter 6 - Reference Code Processing..................................................................................................................... 67

Overview ................................................................................................................................................................... 67

Reference Codes in Formulas ................................................................................................................................. 67

Reference Codes in Amount Transactions ............................................................................................................ 68

Offset Reference Codes........................................................................................................................................ 68

Recipient Reference Codes .................................................................................................................................. 68

Reference Codes in Percentage Transactions ....................................................................................................... 69

Source Reference Codes ...................................................................................................................................... 69

Offset Reference Codes........................................................................................................................................ 69


vi

Rounding Reference Codes ................................................................................................................................. 69

Recipient Reference Codes .................................................................................................................................. 70

Processing Reference Code Transactions .............................................................................................................. 70

Based On Amounts .............................................................................................................................................. 70

Based On Percentages .......................................................................................................................................... 71

Chapter 7 - Intercompany Processing ....................................................................................................................... 73

Overview ................................................................................................................................................................... 73

Multicompany Processing in Formulas ................................................................................................................ 73

Intercompany Transactions .................................................................................................................................... 73

Source Company .................................................................................................................................................. 73

Rounding Companies .......................................................................................................................................... 74

Recipient Companies ........................................................................................................................................... 74

Index .............................................................................................................................................................................. 79

1

Chapter 1 - Introducing Allocations

Introducing Epicor Advanced Allocations Welcome to Epicor Advanced Allocations, the perfect companion to your financial system. Advanced Allocations enhances and simplifies the process of creating, calculating, and processing allocations and accruals. While Advanced Allocations is independent of your accounting system, it is optimized to integrate smoothly with it. The user interface is intuitive and flexible, making it easily adaptive to your business‟s ever-changing needs.

Advanced Allocations can compute virtually any allocation. However, Advanced Allocations is more than an allocation processor: it is a complete transaction generator capable of automating the majority of off-line entries made throughout the course of the normal accounting cycle. By automating these processes, Advanced Allocations saves resources and accelerates the closing cycle. Accuracy in fiscal analysis and control is improved through advanced metrics such as activity-based calculations.

Prerequisite Knowledge

This guide explains how to set up, maintain, and process transactions using Advanced Allocations. However, it cannot teach accounting, and therefore assumes an understanding of basic accounting principles.

Chapter 4 covers database queries, which are used to import values from many sources. You will need a basic knowledge of SQL syntax and a detailed understanding of the database tables to be accessed before using the information in this chapter.

Sources of Information

We have provided a number of tools to aid you in understanding Advanced Allocations. Your individual learning style and basic computer knowledge will dictate how extensively you need to use these aids.

This Advanced Allocations User Guide discusses basic concepts and provides detailed examples for accruals and allocations.

The Advanced Allocations System Management Guide contains set up and maintenance procedures.

The Online Help provides context-sensitive help to understanding and applying allocations.

Conventions Used in this Guide

Names of screen objects such as windows, tabs, fields, field selection options, and buttons are printed in bold type.

Names of individual keys to press are printed in bold type.

Information that appears exactly as it is stated, or that you are instructed to enter exactly, is printed in bold type.

Simple emphasis is printed in italic type.

Important terms are introduced in sans-serif type.

Keystroke combinations are separated by a (+) sign. For example, “Shift+F4” means to press the Shift key and hold it down while pressing the F4 key.

Menus and menu items are separated by a (>) sign. For example, “Select File > Open” means to select the Open command from the File menu. “Select File > New > Sheet” means to select Sheet from the File menu‟s New submenu.

“Click” means to press and then immediately release your mouse button without moving the mouse.

Special visual aids are used throughout this guide such as Notes and Tips. These appear as text in the format displayed below:


2

Note: A note indicates additional information that may help you avoid problems or that should be considered in using the described features.

Tip: A tip indicates additional information that may help you to perform a specific task.

Examples illustrate specific concepts:

Example:

This is example text.

This is example detail.

Getting Around in Advanced Allocations

Toolbar

Each main window has a set of toolbar icons displayed below the menus. The icons available depend on the window open or the node that is highlighted on the desktop interface. A list of toolbar icons and their actions is listed in Appendix B.

Windows, Dialog Boxes, and Forms

All three of these terms are used in this manual. Generically, any independent part of the user interface is displayed in some kind of window. Dialog boxes are windows that ask for specific information from you and then close when you click Ok, Close, or Cancel. The term form is sometimes used to indicate windows that display financial information in a tabbed or table format. Tabbed and Table forms are explained later in this chapter.

Right Mouse Click

Clicking the right mouse button on the relevant object can access most menu options.

Cursor Movement

Press the Tab key to move the cursor to the next field in a window. Press Shift + Tab to move the cursor back to the previous field. To edit a field, simply begin typing in the field or press F2.

Spin Buttons

Click the up or down arrow to change the value in increments of one. In fields with separate sub-fields, as with dates, the arrows adjust only the highlighted portion of the field.

Form Maintenance

You can edit any maintenance form from another entry form by clicking the Maintenance icon . The relevant form displays, allowing you to build the maintenance record “on the fly”. For example, you can build formulas from the Allocation Maintenance form.

Tabbed Forms

Some forms contain overlapping index tabs. To access a particular tab, click the tab title. The selected tab moves to the front. In this example, note that the Percentage Options tab is selected, and is therefore displayed “in front” of the Main and Source Account tabs.


3

Table Forms

Some forms consist of rows and columns. You can effectively hide or resize rows and columns by moving

the cursor to the row or column boundary and “dragging” the boundary when the drag icon displays.

These changes are saved for future sessions, unless you reduce the column width to zero (i.e. hide the column completely).

Record Lookups

Record lookups locate specific records in the database and are activated by clicking the Open icon or by pressing Ctrl + O.

User, Company, and Record Stamp Display

The Advanced Allocations desktop interface always displays the current company, server, and user ID in the status bar at the bottom of the window. In addition, the company is displayed as part of the title at the top of the form.


4

Each form displays in the status bar, the user ID, date and time the displayed record was originally created and last updated.

Lookup Windows

Advanced Allocations provides several types of lookup windows to assist in selection of field values. A field

with a lookup window has a button with three dots to the right of it. As lookup windows are context sensitive, you can type a value in the field and click the lookup button. The starting value closest to the entered value is listed first. There are several types of lookup windows:

Database lookups are used for items such as chart of accounts, budget, and statistical codes.


5

Date lookups are used for fields with date values. The date lookup displays a calendar to select the values. Click the down arrow to display a list of months. Click the year arrows to scroll the year. Click Ok to return with the value to the original field. Click Cancel to exit without changing the date.

File lookups are also available.

Technical Support

Selecting Help > Technical Support from any of the menus displays hyper links and telephone numbers for Advanced Allocations technical support. You may contact technical support by clicking on the links to the websites or by telephone.

Online Help

You can access Online Help from anywhere in the program by pressing the F1 key. You may also select various help options from the Help menu. Help is context sensitive to the form. You can then select detailed help about any specific field in the form.


6

Online Help Options

Help Topics Provides access to the Contents, Index, and Search features.

Contents Lists the table of contents in chapter order. This is the same as Contents from the Help menu. Each option can be further selected to “drill down” to the appropriate subjects.

Index Searches the online help by keyword. The search is context sensitive so the more information that is supplied, the narrower the returned results are.

Search Searches the online help for any occurrence of a word or characters.

Back Moves back to last help window accessed.

Print Prints the current help window.

<< Moves back one help topic.

>> Moves forward one help topic.

Glossary Displays a Glossary of Terms. Selections by alphabetic character can be displayed.

Using this Guide

This guide contains step-by-step instructions to create, produce, maintain, and process allocations. It is intended to help you get the most out of the Advanced Allocations system. Since Advanced Allocations is an extremely powerful tool, we recommend you spend time with the system in advance of using it in a “live” environment. Specific examples are noted in this guide.

Note: Because Advanced Allocations is flexible and easily customized, some features described in this manual may not be available on your system, or may not be available with the configuration you are using. Illustrations of windows, dialog boxes, and other screen elements are intended to be typical, and may differ from what you see.

Chapter 2 introduces basic allocation concepts and definitions and discusses the various transaction-generating options in Advanced Allocations.

Chapter 3 introduces formulas and associated detailed components.

Chapter 4 discusses the use of database queries in formulas.

Chapter 5 discusses step-by-step instructions for creating and maintaining allocations.

Chapter 6 discusses the process and options of generating allocations using reference codes.

Chapter 7 discusses additional steps and options associated with intercompany allocations.

Chapter 8 discusses the process and options of generating multicurrency allocations.

Chapter 9 introduces allocation batches.

Chapters 10 - 11 provide detailed steps for processing, printing, posting, and purging allocations.

The appendices provide additional information on the desktop interface, toolbar icons and actions, errors and warnings, basic SQL concepts, and a glossary of terms used in this guide.

7

Chapter 2 - Basic Concepts

Chapter 2

Basic Concepts

Introduction

This chapter discusses general principles used in the Advanced Allocations system. For purposes of this guide, the term “allocations” is used generically to describe the various types of entries that can be automated through Advanced Allocations.

Most of the Advanced Allocations system revolves around the definition of transaction calculations and the database sources of these calculations. Advanced Allocations provides a number of means to compute these entries.

Definitions

Formulas are mathematical expressions that calculate various amounts.

Recipient Accounts are the accounts designated to receive the results of a calculation.

Source and Offset Accounts are accounts designated to offset the recipient accounts. In a percentage allocation, the source account may be replaced by an offset account as the account to be charged with the transaction.

Rounding Accounts are used to designate which accounts will receive any rounding differences arising from the transaction calculation.

Reference Codes are an extension of a general ledger account and provide a means to further allocate transaction calculation amounts.

Types of Allocations

Two types of allocations can be calculated:

Amounts: an entry is computed based on the specified formula.

Percentages: a redistribution of source values based on calculated basis percentages.

Defining an Amount Allocation

When creating a transaction for an amount allocation, there are a number of things to consider before defining the transaction. We recommend that you review and understand the concepts listed below before creating any transactions.

1. How is the entry to be computed? It is best to sketch out mathematically how the entry will be computed.

2. What accounts will receive the calculated value? (These are the recipient accounts.)

3. Will reference codes be included in the transaction generated?

4. How do the recipient accounts and reference codes correlate with the calculations? In other words, are there multiple calculations based on a change in a specific account segment or reference value?

5. What accounts are to be charged with the entry? (These are the offset accounts.)

6. Is the calculation dependent on a previous allocation? In other words, do you want the results of any previous allocation to be included in the calculation? Using allocation batches, the results of previous allocations can be included in the calculation.

7. In which fiscal periods should the calculation process? Period definitions need not be contiguous, making quarterly, semi-annual, and annual calculations possible.


8

8. Should the allocation include the results of unposted transactions from the general ledger or just closed balances? The option to include unposted transactions can be applied to either ranges of allocations or allocation batches.

9. Should the allocation be automatically reversed once it has been posted? The option to flag allocations for automatic reversal is available for amount or percentage allocations only.

10. Should the allocation generate a detailed audit trail? This processing parameter may apply to either ranges of allocation or allocation batches.

How Amount Allocations Are Processed

The following steps are executed when Advanced Allocations processes an allocation based on amounts:

1. First, each segment range of the recipient account ranges is evaluated separately, followed by the reference code. This process creates a list of valid recipient company, currency, account, and reference code combinations. Each recipient value is then used as a source for any filtered positions in the formula or offset accounts, reference codes, companies or currency.

2. The offset account, reference code, and company for each recipient account are determined using filtered positions, if any from the recipient account. The currency specified for the recipient account is used for the offset account as well.

3. The transaction value for each offset-recipient combination is computed using filtered positions, if any, from the recipient values.

4. A debit-credit transaction for each offset and recipient combination is created.

Example 1:

An interest accrual is calculated based on 7.5% per annum rate of the average daily balance of the loan payable account.

A formula is established to create the entry as shown below.

The formula is assigned to the recipient account (Interest Expense) in the allocation form as shown below.

The other side of this entry is an offset account of Accrued Interest as shown below.

The recipient account is the recipient of the calculation. If the amount calculated is negative, the recipient account is credited. If the amount calculated is positive, the recipient account is debited. The offset account is always the offsetting entry. In this case, the normal balance of the note payable on line 1 of the formula is a credit. It is multiplied by a negative interest rate. The result of two negatives is a positive number, which is then divided by 12 for a monthly accrual. The positive number is therefore a debit to the recipient account.

Defining a Percentage Allocation

When creating a transaction for a percentage allocation, there are a number of things to consider before defining the transaction. We recommend that you review and understand the concepts listed below before creating any transactions.

1. What is to be allocated? The amounts to be allocated are referred to as the source values. Advanced Allocations requires users to identify which account balances make up the source values. You should also know how the source values are related for example:


9

All accounts out of particular department or location

All or a range of reference codes

A range of specific main accounts

A pool of accounts or other values

The second part of this step is determining what portion of the account is to be allocated. There are three aspects to this:

Which balance to use? The beginning balance, net change, ending balance, average daily balance or a pool formula can be allocated.

What percentage of this balance is to be allocated? This is specified in terms of a fixed percentage (usually 100%).

Is there a maximum amount to be allocated?

2. How are the balances to be allocated? This is referred to as the basis. Formulas are used to compute basis values. Balances can typically be allocated any number of ways. Some examples include:

Actual balances

Budgeted values

Statistical criteria such as head-count or square footage

3. Where are the basis values coming from? If basis values come from account balances, you will need to know what accounts those represent. As with source account balances, you should determine the relationship between the accounts. For example, sales balances might be within a range of account numbers. If the basis values are statistical, what are the statistical account numbers? Where are the values stored? Advanced Allocations can read these values from many sources, including spreadsheets.

4. Where is the allocation to be distributed? The distribution accounts are referred to as recipient

accounts. You will need to define how the recipient accounts are related and if the distribution should go to the reference code.

5. How are the basis calculations related to the recipient accounts? By definition, there must be a correlation between the recipient accounts and the basis calculation; otherwise, there would be no way to distribute the allocation.

6. Where is the allocation to be charged or offset? Advanced Allocations can charge to the source accounts as the source balances or to separate offset accounts. Using offset accounts allows you to keep balances in the source accounts for financial analysis.

7. How are the source accounts related to the recipient accounts? This relationship is not required, but allows you to define multiple source accounts in a single allocation.

8. How are rounding differences to be handled? The rounding difference can be left in the source account, charged to the offset account, distributed to recipient accounts, or charged to completely separate rounding accounts.

9. Is the allocation dependent on a previous allocation? In other words, do you want the results of previous allocations to be included in the calculation? The results of previous allocations can be included in the calculation using allocation batches.

10. In which fiscal periods should the allocation process? Period definitions need not be contiguous, making quarterly, semi-annual, and annual calculations possible.

11. Should the allocation include the results of unposted transactions from the general ledger or just closed balances? The option to include unposted transactions can be applied to either ranges of allocations or allocation batches.

12. Should the allocation be automatically reversed once it has been posted? The option to flag allocations for automatic reversal is available for amount or percentage allocations only.

13. Should the allocation generate a detailed audit trail? This processing parameter can apply to either ranges of allocations or allocation batches.

How Percentage Allocations Are Processed


10

Advanced Allocations executes the following when processing an allocation based on percentages:

1. First, each segment range of the recipient account ranges is evaluated separately, followed by reference codes. This process creates a list of valid recipient company, currency, account, and reference code combinations. Each recipient value is then used as a source for any filtered positions in the formula or source accounts, reference codes, companies, or currencies.

2. The basis value for each recipient account processed is determined using filtered positions, if any from the recipient account.

3. The source account, reference code, company and currency for each recipient account are determined using filtered positions, if any from the recipient account.

4. The offset account, if applicable, for each recipient account is determined using filtered positions, if any, from the source account.

5. The rounding account, if applicable, for each recipient account is determined using filtered positions, if any, from the source account.

6. The weighted basis percentage of the source value is computed by subtotaling the percentages when the source account breaks. Just as in the other calculations, the calculated value is received by the recipient account. The other side of the entry can be distributed to either the offset account, if one is specified, or to the source account if the offset account field is left blank.

7. The individual allocated amount is calculated based upon the weighted basis percentage times the source value.

8. A transaction is created, charging each source or offset account (if applicable) and distributing the allocated amount to each recipient account.

9. If a maximum allocated amount is specified, then the calculated amount is compared to the specified maximum. If the calculated amount is greater than the designated maximum amount, the source account is adjusted down to the maximum amount and the remainder is applied to the rounding account. If no rounding account is specified, then the first recipient account is automatically adjusted to accommodate the maximum.

10. Any rounding differences are evaluated. If no rounding information is provided, the source transaction changes to match the total of the recipient transactions. If a rounding account filter exists, the source account number is processed through the filter to determine the rounding account number. A transaction is generated to the resultant rounding account for the difference. If the rounding account number is the same as the source, offset, or recipient account, the transaction is merged into these accounts rather than creating an additional transaction.

Other Options for Percentage Allocations

Offset Accounts

In a percentage allocation, offset accounts are used to charge a different account or series of accounts other than the source accounts. The offset account is simply a logical substitution for the source account in the debit-credit transaction.

Rounding Accounts

Rounding accounts provide the capability to specify the account(s) to charge with a remainder for percentage calculations. In this type of calculation, a percentage is distributed out of account balances and in some cases a rounding difference will exist. The rounding account determines the account to charge with the remainder. If the rounding account is blank, the system will leave the difference in the source account.

Source Pools

Source pools provide the capability to summarize a series of values. They are not restricted to account balances, but to enterprise-wide information. Since source pools calculate the value to be allocated, the source account is not directly involved in the computation of the entry. Thus, the source account becomes the account charged with the entry to offset the recipient account.

Maximum Allocated Amount

The Maximum Allocated Amount option allows the ability to specify a maximum amount to be allocated from the source account. If the calculated source amount is greater than the maximum amount defined, then


11

the source amount is adjusted down to the maximum amount and the percentages will be calculated from the maximum amount. If no rounding account is specified, and the rounding amount increases the source amount higher than the maximum, then the first recipient account in the range is adjusted to only allow for the maximum amount to be applied to the source account. For this reason, it is recommended that a rounding account be specified when using the Maximum Allocated Amount option.

Example:

IT costs are to be allocated to each department and location on the basis of head count. However, based on the estimated budget for the year, those costs are not to exceed $60,000.

By entering $60,000 in the Maximum Allocated Amount field, the source account will not exceed that amount and the remainder will be charged to a designated rounding account.

The allocation would result in the following:

Month Calculated Amount Allocated Amount

January $56,783 $56,783

February $72,369 $60,000

March $65,021 $60,000

April $15,438 $15,438

Transaction Allocations

Transaction allocations provide the capability to allocate detailed transactions to one or more recipient accounts using the Advanced Allocations formula engine. Transaction allocations process identically to percentage allocations. The exception is that each document is allocated individually, instead of an account balance. All of the details of the original transaction are assigned to the created allocation transaction.

Documents processed in transaction allocations can be restricted to a particular date range. Like other percentage allocations, transaction allocations can be included in allocation batches.

Processing Segment Ranges

A range of accounts is always processed based upon each segment range. This applies to both formula accounts and recipient accounts.

Example:

Suppose that your chart of accounts includes the following: 6000-100-100-00

6000-200-100-00

6000-100-200-00

6000-200-200-00

7000-100-100-00

7000-200-100-00

7000-100-200-00

7000-200-200-00


12

These are the recipient account ranges and the resultant accounts from this list:

Results in

6000-100-100-00

7000-100-100-00

Results in

6000-100-100-00

6000-200-100-00

7000-100-100-00

7000-200-100-00

Results in

6000-100-100-00

6000-200-100-00

6000-100-200-00

6000-200-200-00

Filters and Variables

Filters and variables are used to make various allocation's results dependent on changes in a corresponding parameter. When used with formulas, this results in separate calculations for different account combinations.

Filters are identified with a question mark (?) and should not be confused with a wildcard designation. A wildcard indicates that all values will be used in that position. Filters are filled by parameters passed from other values in the Advanced Allocations system. Reference code filters and Account filters are completed on a position-by-position basis as illustrated by the following example:

Filter Examples:

Parameter Account: 1200-100-300-12

Account Filter: ????-000-000-00

Resulting Number: 1200-000-000-00


Account Filter: 5000-???-???-??



Account Filter: 1100-?00-000-00



13

Processing Steps

We recommend the following steps to complete an allocation:

1. Define the formula to compute the basis, amounts, or source pools for the allocation (see Chapters 3 - 4).

2. Complete the Allocation Maintenance form and assign formulas (see Chapters 5 - 8).

3. Test the allocation to verify that it is calculating the correct values (see Chapter 5).

4. If necessary, define allocation batches by assigning allocations in the proper sequences (Optional - see Chapter 9).

5. Process the allocation or allocation batch (see Chapter 10).

6. Preview the Allocation Report for errors and results (see Chapter 11).

7. Post or purge the allocations (see Chapter 11).

You may skip step 1 and use an override value if you are performing a fixed calculation. For additional information regarding reference code, intercompany and multicurrency allocations, see Chapters 6, 7 and 8 respectively.

15

Chapter 3 - Formula Concepts

Chapter 3

Formula Concepts

What are Formulas?

Formulas can be used to calculate transaction amounts, basis values, and source pools. Formulas are essentially mathematical expressions, consisting of combinations of account balances, constants, and database queries. Here are some examples of how you might use formulas:

Calculate basis using sales accounts

Compute interest accrual from average daily balance of debt

Calculate basis using number of purchase orders by product line

Calculate source pool of general and administrative expenses

You can create an unlimited number of formulas with each formula being unlimited in length. By incorporating filters and variables with each process, a single formula may be regenerated many times. But the overall result of each formula is a single value. That value may consist of many component values, but the result is a single value. This makes formulas a powerful yet easy to use tool for allocation calculations.

Global vs. Local Formulas

Formulas are classified into two general categories, global and local.

Global formulas are created independently and can be retrieved and used by anyone in the system.

Local formulas are attached to an individual allocation and cannot be copied by other users. If you delete the allocation, all local formulas attached to it are also deleted.

Local formulas can be saved as global formulas or vice-versa. Local formulas and global formulas can even share the same key identification.

Note: The system always defaults to the local formula when the key values are the same.

Formula Maintenance

Creating a New Global Formula

1. Highlight the Global Formulas node in the Tasks pane.

2. Select File > New (Ctrl +N) or click the new icon.

3. Complete the Main tab.

4. Complete the Formula tab.

5. Click the Save icon to save the formula.


16

Formula Main Tab Fields

Formula Key Type a unique key of up to 16 alphanumeric characters to identify this formula.

Description Type a maximum 40-character description of the formula.

Include Previous Allocations

Check this box to include the results of any previous allocations in the formula calculation.

Note: A user must be given access to global formula maintenance in order to create, edit or delete global formulas. If a user has not been granted permission, global formulas can only be opened as read-only. See the Advanced Allocations System Management Guide for more information on maintaining user permissions.

Creating a Local Formula

1. Click the Maintenance icon on the formula fields on the Percentage Options tab or Recipient

Accounts tab in the Allocation Maintenance form. This is virtually the same as the Global Formula

form with the addition of the Globe icon . If the globe is indented, the formula is a global formula. If the globe is not indented, it is a local formula.

2. Complete the Main tab.

3. Complete the Formula tab.

4. Click the Save icon .

5. Close the form (Ctrl+F4). This message displays:


17

6. Click Yes to apply the formula to the allocation.

7. If the formula is used to compute a source pool, optionally complete Query Parameter 1 and Query Parameter 2 on the Percentage Options tab. These options are used in database queries and are discussed in Chapter 4.

Applying a Global or Local Formula

You can apply a global or local formula to an allocation at any time.

1. There are several ways to access the Look Up Formula list from the Allocation Maintenance window:

Percentage Options tab: Click the Lookup icon next to the Pool Formula field.

Recipient Account tab: With the cursor in the Formula field, click the toolbar Lookup icon .

The Lookup Formula window displays.

2. Select whether to browse local or global formulas.

3. Select the formula to apply.

4. Click Ok.

5. If the formula is used to compute a source pool, optionally complete the Query Parameter 1 and Query

Parameter 2 fields. These options are used in database queries as discussed in Chapter 4.

Tip: A single allocation can have many local formulas.

Editing a Global Formula

1. Highlight the Global Formulas node in the Tasks pane.

2. In the list pane, highlight the global formula to be edited.

3. Double-click on the formula or select File > Open (Ctrl + O) from the menu.

Saving a Local Formula as Global Formula or Vice Versa

1. Retrieve the existing formula (global or local).

2. Click the Globe icon .

3. Click the Save icon .

4. The following message displays:


18

Note: Global formulas may only be saved as local formulas if they are accessed through an allocation.

Creating Formula Lines

The next step in creating a formula is to complete the detail lines. A formula can consist of multiple formula lines joined together by mathematical operands and parentheses.

Each formula line is identified as one of the following line types:

Accounts

Constants

Database queries

Plan Sheet - Account Numbers (if Active Planner is also installed)

Plan Sheet - Row ID (if Active Planner is also installed)

Plan Sheet Table Lookups (if Active Planner is also installed)

Database queries are discussed in Chapter 4. The other options are discussed in this chapter. Determining the appropriate procedure to complete the line depends on the line type selected.

These mathematical operands can be used to join lines together:

Addition (+)

Subtraction (-)

Multiplication (*)

Division (/)

Lines joined by these operands form the equivalent of a left-to-right mathematical expression. The expression is evaluated from top-to-bottom, and according to normal algebraic precedence. Multiplication and division operands are evaluated first, then addition and subtraction operands. Parentheses can be used at the beginning or end of a line to change the order of precedence.

Tip: Nested parentheses are not supported. To mimic nested parentheses, use a constant and operand combination such as “0+” or “1*”.

Each line independently computes a value based on the selection criteria. All formula lines combine to produce a single value at a time. However, the same formula could be computed repeatedly with different values by using filters and variables, which are discussed later in this chapter.

The Check Formula button at the bottom of the form ensures the validity of the formula expression. This option checks the formula to ensure that:

All required fields are completed

If multiple rows exist, that operands are selected

Each open parenthesis is followed by a close parenthesis

Note: Clicking the Check Formula button does not ensure that the data typed is correctly or that the formula

will be useful. It only ensures that the formula is mathematically valid.

Accounts Formula Type

Steps to processing Accounts formula line type are as follows:


19

1. Select the Year and the Periods to process.

2. For intercompany processing, select the Company.

3. Select the Balance Type to process (actual, budget, or statistical).

4. Select a Balance Code, if applicable.

5. Select the balance Valuation.

6. If a multicurrency calculation, select the Currency (home, or natural).

7. Select the Accounts to process.

8. Select a reference type (Ref Type) and corresponding Value, if applicable.

Note: Selecting Accounts summarizes the total of all of account balances resulting from segment ranges.

Periods and Years

Advanced Allocations can read account balances from any period or range of periods for the current and up to ten prior years. The periods specified could either be fixed period numbers or relative to the allocation period being processed.

Year

You can select from the current year or up to ten prior years. The year is based upon the allocation period being processed.

Fixed Periods

You can specify the fixed periods that you want to process. These periods correlate to your company‟s fiscal calendar to determine the balances to process.

Tip: You cannot use period 0 when using fixed period numbers. To obtain a beginning balance sheet

balance, you can use period 1 and specify the valuation as Beginning.

Note: The Thru Period must always be higher than the From Period for the formula to retrieve balances

properly.

Relative to the Current Column

You may also select a period range relative to the allocation period being processed. A range of periods can be positive or negative. Positive period numbers work forward in the fiscal calendar. Negative period numbers work backward in the fiscal calendar.

Tip: You can span years with relative periods.

Note: The Thru Period must always be higher than the From Period to retrieve balances properly. Negative

numbers would always have a value lower than period 0.

Example 1:

Suppose you are computing a 15% bonus accrual for quarterly profit. You would compute the accrual using this formula:

The From Period value of -2 signifies that the calculation is to start with account balances two months prior to the processing period. The Thru Period of 0 signifies that the ending period is to be the current processing period. Note also the calculation is multiplied by a negative value to obtain a positive calculation


20

(since the normal balance of these accounts would be a credit if the company earned a profit) and thus a debit value.

You might also use the relative periods to allocate based on a moving average of account balances.

Example 2:

Assuming the same example, except the bonus is a seasonal bonus paid for profit earned during periods 7 to 10 of the fiscal calendar. The formula is rewritten as follows:

In this case, fixed periods are used regardless of the processing period.

Company

You can restrict a balance to a specific company. You can either specify the company by using the lookup window or by typing the value. You can also use filters (?) and the system will complete the filter from the appropriate parameter, as discussed in Chapter 7.

Note: Advanced Allocations uses the fiscal calendar of the specified company.

Balance Types

Balance type options include:

Actual

Budget

Statistical

You will also need to specify a balance code to use for any balance types that are configured with codes. You can use the lookup window to select from a list of balance codes.

Valuations

Valuation determines what account balance to use in calculating the value. Options include:

Average Daily Balance

Beginning Balance

Net Change

Ending Balance

Selecting Average Daily Balance calculates the average daily ending balance for the periods selected, based on the historical transactions for the account. You can select whether to exclude Saturdays or Sundays from the calculation. To change this option, select Setup > Options.


21

Tip: Average Daily Balance is a powerful option. For example, you might calculate average daily debt balance to calculate interest on debt accounts. Note that you can calculate the average daily balance for a range of periods, not just the current fiscal period, allowing more precise basis calculations.

Tip: Selecting Average Daily Balance calculates the average daily ending balance. To calculate average daily activity, you could specify a formula with net change valuation and divide by a constant representing the number of business days in the periods covered.

Example:

The following is an account balance and its activity for the month of June.

Beginning Balance 30,000

6/5 Transaction 10,000

6/27 Transaction 15,000

The average daily balance is computed as follows if Saturdays and Sundays are included:

30,000 * 30/30 = 30,000

10,000 * 26/30 = 8,666

15,000 * 4/30 = 1,995

40,661

The average daily balance would be computed as follows, if Saturdays and Sundays were excluded (based on the 2002 calendar):

30,000 * 20/20 = 30,000

10,000 * 18/20 = 9,000

15,000 * 2/20 = 1,500

40,500

Currency

You can specify on a formula line to restrict a balance to a specific currency key, if currency balances are available. You can also specify whether to use the natural currency value, or the converted home equivalent.

Note: Advanced Allocations will default to the home currency and the home amount.


22

Multicurrency Options

Specific Currency Type a specific currency key. You may select from a list of valid currencies in the lookup window.

??????? Type a complete filter. The filter will be completed from the appropriate parameter, as discussed in Chapter 8.

* Type this value to return all currency balances for this line.

Tip: By using multicurrency in formulas, you could convert all currencies to the home equivalent for a basis calculation.

Account Values

For the Accounts formula line type, you type the account number in the Parameter 1 for the beginning account and Parameter 2 for the ending account. Advanced Allocations reads the account format and properly displays the account with segment separators. Options are:

Type a valid Account Number. You can select from a list of valid accounts using the lookup window.

Filter any portion of the field. The value will be derived from the appropriate parameter.

Note: Formula account ranges are based on the individual segment ranges.

Reference Codes

Reference groups or codes may also be specified in a formula. Once you have chosen the reference type, you can specify the reference code value.

Reference Code Options

Ref Type Select Group or Code from the drop down list.

Group represents a group of reference codes.

Code represents only a specific reference code.

Value Type or select a valid reference type value.

Group values will incorporate the balance of all reference codes within the group.

Code values will use only the amount applicable for the specified reference code.

Note: The use of reference codes is only available when the Accounts type and the Net Change valuation

options are selected.

Constants Formula Type

Constants are fixed values used in the formula. The constant value is completed in the Parameter 1 column. Constants can range from -99,999,999 to 999,999,999 and can include as many as five decimal places.

Tip: You would normally only use constants if the value is relatively fixed. If the value is variable, you might

use another line type such as Accounts or Database Queries.

Plan Sheet Values Formula Type


23

This section assumes that Active Planner is installed and requires a basic understanding of plan sheets. One of the most powerful components to a formula is the ability to use values from Active Planner plan sheets. There are a number of ways to use this option:

Use statistical factors from plan sheets for basis calculations

Use plan sheets as “scratch pads” to work up accruals

Query cross-company, cross-plan, and cross-group data

Within a formula, three formula line types are available to reference plan sheet values.

Plan sheet account numbers

Plan sheet row IDs

Plan sheet table lookups

To access plan sheet values based on account numbers, the range of accounts is evaluated based on the Account Number column and the periods are based on the configured periods for each column. For plan sheet row IDs, the row ID defined in the formula is matched to the row ID in the plan sheet and the periods are based on the configured periods for each column.

The PS-Acct# and PS-Row ID options retrieve values from one specific plan sheet. The Plan sheet table lookup option can retrieve plan sheet values that cross companies, plans, sheets, groups, and dates.

Completing the PS-Acct # and PS-Row ID Formula Lines

1. Select the formula line type of PS-Acct# to retrieve a plan sheet value by account number or PS-Row

ID for a row ID match.

2. Select the Year and Periods to process.

3. Complete the Balance Code field. The balance code is defined as the combination of the plan, plan sheet, and column group containing the value to be retrieved. This is expressed in the form of a Plan.Sheet.Group key identification. Use the lookup window to select from a list of the plan sheets and groups available.

4. Type the appropriate parameter, based on the formula line type.

For plan sheet values by account number, enter the beginning and ending account number to use in the Parameter 1 and Parameter 2 fields. You can use a filter in these columns and the system will substitute the appropriate parameter for the filters as the formula processes.

For plan sheet values by row IDs, type the required row ID in the Parameter 1 field.

Processing PS-Acct # and PS-Row ID Formula Lines

Advanced Allocations obtains the necessary variables to process the plan sheet as follows:

1. Determines the year to process based on the processing period of the allocation. For example, if you are processing an allocation for a 2002 period and you want to retrieve values from a column in a plan sheet with a column date of 2001, you would specify a year of Prior 1.

2. Determines the periods to process. This can either be fixed periods for the year or relative periods. Relative periods, just as in any other calculation, are substituted from the processing period of the allocation. For example, if a column has a range of periods of 1 to 1 (assuming a calendar year), then the equivalent periods will be constructed for the calculation.

3. Determines the account ranges to process. If you specify a filter in an account range, the system uses the filtered positions from the appropriate parameter as discussed in Chapter 5.

Once the variables are determined, the system can calculate the plan sheet values. The system processes the calculated values based upon the following criteria:

Advanced Allocations looks for an exact match in the plan sheet being read. For example, if you are calculating a value for a period range of 4 to 6, the system looks for an exact match of periods 4 to 6 in the plan sheet group being referenced.


24

The system looks for various other calculations that will construct the balance requested. Using the same example, individual net change columns of 4, 5, and 6 could be added together to get a column value of periods 4 to 6. The system also uses combinations of ending balance columns and beginning balance columns to achieve the result. For example, ending balance at the end of period 6 minus the beginning balance at the beginning of period 4.

Refer to Chapter 5 for examples of using plan sheets in formulas.

Completing the Published Data Retrieval Formula Line

1. Select the formula line type of Pub Data to retrieve cross-company, cross-plan, cross-sheet and cross-group plan sheet.

2. Select the Year and Periods to process.

3. In the Parameter 1 field, click the Pub Data button.

4. Complete the Published Data Retrieval form.

You can select a specific company, plan, dimension set, sheet, group, and account range, or you can use variables and filters to expand the result set.

Processing Published Data Retrieval Formula Lines

Advanced Allocations obtains the necessary variables to process the plan sheet as follows:

1. Determines the year to process based on the processing period of the allocation. For example, if you are processing an allocation for a 2002 period and you want to retrieve values from a column in a plan sheet with a column date of 2001, you would specify a year of Prior 1.

2. Determines the periods to process. This can either be fixed periods for the year or relative periods. Relative periods, just as in any other calculation, are substituted from the processing period of the allocation. For example, if a column has a range of periods of 1 to 1 (assuming a calendar year), then the equivalent periods will be constructed for the calculation.

3. Determines the account ranges to process. If you specify a filter in an account range, the system uses the filtered positions from the appropriate parameter as discussed in Chapter 5.

Once the variables are determined, the system evaluates each published plan sheet on a sheet-by-sheet basis to calculate the plan sheet values based on the selected criteria. The system processes the calculated values based upon the following criteria:

First, the system looks for an exact match for the selected valuation type.


25

Next, Advanced Allocations looks for an exact period match. For example, if you are calculating a value for a period range of 4 to 6, the system looks for an exact match of periods 4 to 6 in the plan sheet group being referenced.

If an exact period match is not found, then the system looks for various other calculations that will construct the balance requested. Using the same example, individual net change columns of 4, 5, and 6 could be added together to get a column value of periods 4 to 6.

Note: If single monthly columns exist in a sheet with quarterly columns, then all of the column values will be added together. To avoid returning duplicate values, quarterly columns should be applied to a different group than the monthly columns. Then the option to publish the group associated with quarterly columns should be disabled.

Printing the Formula Definition

1. Highlight the global or local formula you wish to print.

2. Select File > Print Definition or click the print icon.

3. The Output Location form displays. Select an output option.

4. The formula definition output may be viewed on the screen, printed or saved.

ERP Configurations

ERP Configurations allow you to import and export global configurations from your Advanced Allocations system including global formulas. The definitions are stored in the ERPCustom.MDB file located in the directory where Advanced Allocations was installed. You can copy this file to other Advanced Allocations systems and use it as a basis for importing or exporting definitions. In some installations, the file may have been already installed but can be edited.

Note: In many cases, it may be necessary to change database connection or file path information once the configurations are imported.

1. From the main menu, select Setup > ERP Configurations.


26

2. Type a new data source code or select a list of existing data source codes. Click the Remove Data

Source button to delete a data source code and the associated custom configurations.

3. If a new data source code is defined, enter a description for the data source code. If an existing data source code is selected, the description will be shown.

4. Configurations already available are displayed in the right hand section of the screen. You may export these configurations by using the Copy button. Click the Select All button to select all of the configurations for copying. You can also restrict the display to a particular type of configuration by selecting a configuration type from Type of Configuration to View. This will restrict both the available system configurations and the exported system configurations.

Tip: Click the Type heading in the Configurations to be Imported pane, then Select All and Copy. This will copy the single dimensions first, followed by tiered dimensions.

5. To delete any exported configurations, highlight the configuration to delete and click the Delete button.

6. Click the Close button to close the window. Any changes are automatically saved to the ERPCustom.MDB file.

27

Chapter 4 - Database Queries

Chapter 4

Database Queries

Database queries are used in formulas to calculate and deploy values from other data sources. Advanced Allocations can retrieve and manipulate values from many sources either through a direct connection or by implementing linked servers. Linked servers allow SQL Server to connect to other data sources through the OLE-DB provider and, in essence, accesses the data sources as if they were databases on the SQL Server. These connection definitions are created in a centralized location using the Admin Tool. For additional information on creating linked server definitions, see the System Management Guide.

The following data sources may be accessed through Advanced Allocations:

Microsoft SQL Server

Microsoft Access

Microsoft Excel

ODBC sources

OLE-DB sources

dBase

FoxPro

HTML

Paradox

Text files

Advanced Allocations uses the Microsoft Jet database engine to natively access all of the above databases with the exception of SQL Server (which uses Transact SQL). This is the same engine that is used in Microsoft Access. The Microsoft Jet query engine is designed to accept user requests for information or actions in the form of SQL statements. Microsoft Jet parses, analyzes, and optimizes these queries, and returns the resulting information.

Microsoft Jet database engine is generally ANSI Level 1 compliant. However, certain ANSI SQL features are not implemented in Jet SQL. Conversely, Jet SQL includes keywords and features not supported in ANSI SQL. These differences are discussed in more detail in Appendix D.

Advanced Allocations can process a complete SQL query, but only returns the value found in the first row-column intersection. Thus, SQL expressions such as GROUP BY and ORDER BY are accepted but generally not used in Advanced Allocations database queries. In addition, multiple columns can be selected, but only the first column value will be returned to the formula.

In addition to the databases natively supported with Jet Database SQL, Advanced Allocations can communicate with any ODBC compliant database. ODBC databases can be queried using the Jet SQL engine or using the ODBC driver supported SQL dialect. You should note that each ODBC driver might have differences in the SQL query dialect supported. Consult your individual ODBC driver for these specific differences.

Database queries can be implemented for any line in a formula. Some examples of host system data you may want to query may be:

Spreadsheet statistical factors

Member data from fund software

Marketing data from a marketing database


28

Setting up Database Queries in Formulas

1. Select Database as the formula type.

2. Click the Database Query button in the Parameter 1 field. The Database Query form displays.

3. In the Data Source Type field, select the data source to query against.

4. Complete the connection information based on the data source selected.

5. Optionally, you may click the Test button in the Test Connection section to verify that the connection information has been defined correctly.

6. Next, click the Query Builder tab to build the SQL query.

The SQL query may be completed by:

Selecting items from the Tables, Fields, Key Words, Operations and Variables list boxes to complete the SQL statement. Double-click on an item to add it to the Query field.

Clicking the Advanced button and manually typing the SQL statement.

Note: If you switch from the Advanced mode back to the query builder box, you will lose any changes that were made.

7. Once you have created the SQL statement, you may test the query. Select the desired test query options and then click the Test Query button.

Test Query Options

Show Parsed Query Check this option to show the query with the SQL variables translated before processing.

Execute Query Check this option to process the query.


29

Show Entire Query Result

Check this option to show the entire result of the query. When actually using the query in an allocation, Advanced Allocations will only return the first value.

a. Input the values of the desired test SQL variables. Any variables not required for the test can be left blank.

b. Click Ok to process the test query or Cancel to return to the Query Builder screen. If the test query is processed, and Show Parsed Query was selected, the following window will display, showing the SQL statement.

c. Click Ok to continue. If Execute Query was selected, all results will display.

d. Click Ok to continue. If Show Entire Query is selected, the single value that will be returned by the query is displayed.

Note: Only the first row and column value will be retrieved when a query is processed.

8. On the Query Builder screen, click Ok to complete and save the query definition.


30

Microsoft SQL Server Data Source Definition

SQL Connection Options

Use Logon Connection Information

Check this option to use the server that you are currently connected to. This option also uses the Database Query Authority (DQA) assigned to the logged on user.

Server This field is enabled when the Use Logon Connection

Information option is deselected. Type the server name that contains the database you wish to query.

Login ID This field is enabled when the Use Logon Connection

Information option is deselected. Type a valid user ID to connect to the specified server.

Password This field is enabled when the Use Logon Connection

Information option is deselected. Type the corresponding password for the login ID specified.

Database Type the name of the database you wish to query. If this field is left blank, then the current company database is used.


31

Microsoft Access Data Source Definition

Access Connection Options

Database Filename Type or browse to the path that contains the source file.

Login ID For secure Access databases, type the correct login name. To access unsecured Access databases, you MUST use the login name Admin.

Password For secure Access databases, type the password associated with the designated login ID. For unsecured Access databases, leave this field blank.

Microsoft Excel Data Source Definition

Excel Connection Options

Database Filename Type or browse to the path that contains the source file.

Login ID For secure Excel files, type the correct login name; otherwise leave the field blank.

Password For secure Excel files, type the password associated with the designated login ID; otherwise leave the field blank.


32

ODBC Data Source Definition

ODBC Connection Options

Data Source Name Type the name of the data source that you wish to query.

User ID Type a valid user ID to connect to the specified data source, if required. You may also opt to complete this information at the time of plan sheet calculation processing.

Password Type the corresponding password for the user ID specified. You may also opt to complete this information at the time of plan sheet calculation processing.

Retype Password Retype the user ID password for verification. You may also opt to complete this information at the time of plan sheet calculation processing.

Other Parameters Complete this field if any additional driver-specific information is required to access the data source. The key parameter and valid values, specified in the key=value format, vary for each driver. See your ODBC driver documentation for additional information.

Send SQL directly to ODBC

Select this option to pass the SQL query directly to the ODBC driver without interpreting it as a Microsoft Jet SQL query. Selecting this option allows you to take advantage of any ODBC driver-specific SQL syntax. Consult your ODBC driver manual for a listing of supported SQL syntax.

Manage Data Sources

Click this button to view existing or to create new ODBC connections.

Learn Connection Click this button to view a list of existing ODBC configurations and to select the data source to be queried.


33

OLE-DB Data Source Definition

OLE-DB Connection Options

Provider Select from the drop-down list, the data source provider to be used to access the desired data.

Server Complete this field with the information pertaining to the location of the data source, i.e. SQL server name, Access file path, Oracle server name, etc.

Login ID For secure data sources, type the corresponding password for the user ID specified.

Password Retype the user ID password for verification.

Database Type the name of the database to be queried.

Other Parameters Complete this field if any additional provider-specific information is required to access the data source.

Connection Timeout

If desired, type a maximum number of seconds available to connect to the data source.

Manage Data Links Click this button to view existing or to create new OLE-DB connections based on the provider selected.

Learn Data Link Click this button to view a list of existing OLE-DB configurations and to select the data source you wish to query.

Setting up Linked Server Queries in Formulas

Prior to creating a formula using linked servers, you must define a linked server configuration for the data source(s) you wish to query. For information on defining linked servers, see the System Management Guide.

The process of using linked server queries in formulas is as follows:

1. Select Linked Server as the formula type.

2. Click the Linked Server button in the Parameter 1 field. The Linked Server Query form displays.


34

3. Select the Query User that will be used to access the data source. If the Specified User option is selected, type the corresponding User ID and Password in the designated fields.

Query User Options

Default Query User Select this option to use the query login ID defined in User

Maintenance for the current user logged into Advanced Allocations. Depending on the options set, this login ID may use the Database Query Authority (DQA) defined using the Admin Tool, or it may use another login ID. For more information on setting the default query user, see the System Management Guide.

Specified User Select this option to indicate a specific user ID and password to access the data source that may be different than the query user defined in User Maintenance. If this option is selected, type the corresponding user ID and password.

4. In the Tables section, click the New button to select a table from the data source to be queried.

5. A table lookup form displays. Expand through the previously defined linked server definitions and highlight the desired table.

6. Click Ok to add the table to the Data Source tab.

7. The Alias Maintenance form displays with the selected table name. At this point, you can define an alias for the table or accept the existing table name as the alias.


35

8. At this point, you may click the Test button in the Test Connection section to verify that the Query

User can connect to the defined data source.

9. Next, click the Query Builder tab to build the SQL query.

The SQL query may be completed by:

Selecting items from the Tables, Fields, Key Words, Operations and Variables list boxes to complete the SQL statement. Double-click on an item to add it to the Query field.

Clicking the Advanced button and manually typing the SQL statement.

Note: If you switch from the Advanced mode back to the query builder box, you will lose any changes that were made.

10. Once you have created the SQL statement, you may test the query. Select the desired test query options and then click the Test Query button.

Test Query Options

Show Parsed Query Check this option to show the query with the SQL variables translated before processing.

Execute Query Check this option to process the query.

Show Entire Query Result

Check this option to show the entire result of the query. When actually using the query in an allocation, Advanced Allocations will only return the first value.

a. Input the values of the desired test SQL variables. Any variables not required for the test can be left blank.

b. Click Ok to process the test query or Cancel to return to the Query Builder screen. If the test query is processed, and Show Parsed Query was selected, the following window will display, showing the SQL statement.


36

c. Click Ok to continue. If Execute Query was selected, all results will display.

d. Click Ok to continue. If Show Entire Query is selected, the single value that will be returned by the query is displayed.

Note: Only the first row and column value will be retrieved when a query is processed.

11. On the Query Builder screen, click Ok to complete and save the query definition.

SQL Variables

Advanced Allocations SQL variables are values derived at the time of processing similar to filters. SQL variables begin with an ampersand (&) and are evaluated at the time plan sheet rows are calculated.

For example, the SQL variable &ACCT& uses the equivalent parameters of account and currency filters. The &PEREND& variable uses the processing period-end dates as it is processed. Two user-defined query parameters are provided in the Allocations Maintenance form for source pools that can be used as SQL variables. Variables are converted or pre-processed before running the SQL query against the database.

Note: Although a database query can return multiple values, only the first value in the first column is returned. However, by using the above SQL variables, the same query can be processed multiple times and return multiple values.

Tip: You can be quite creative in using database queries with other tables in your system. For example, you might have a sales territory that mirrors a segment in your chart of accounts. You could therefore construct a query to calculate a sales basis for territory using the &ACCTSEG& variable as a restriction to the territory.

SQL Variables

&& This variable is evaluated as a single ampersand (&). This allows you to use an ampersand in your query without the Advanced Allocations pre-processor attempting to evaluate it as another variable.

&ACCT& Account number without any formatting characters.


37

&ACCTSEG (segno)& Account segment value for the segment number indicated as the parameter. No formatting characters are included. For example, &ACCTSEG(2)& for account 6000-100 returns 100.

&AMASK(‘mask’)& Account number after it has been passed through the indicated mask without any formatting characters. You should not place any formatting characters in the mask of this function. For example, &AMASK(„6100??????‟)& used with an account 2000-100-100 yields 6100100100.

&CURRENCY& Currency key

&FACCT& Account number with account format separators.

&FAMASK(mask)& Account number after it has been passed through the indicated format mask. This is similar to the &AMASK& variable, but has the account formatting characters in it. You should not place any formatting characters in the mask of this function. For example, &FAMASK(„6100??????‟)& used with an account 2000-100-100 yields 6100-100-100.

&PARM(parmno)& User-definable parameters defined in the Allocation

Maintenance form for source pools. This option is explained in more detail later in this chapter.

&PERNO(From)& Beginning period number from the processing period used in the allocation.

&PERNO(Thru)& Ending period number from the processing period used in the allocation. This is predominantly used in Active Planner.

&PERBEGIN(Format[,Delimiter])& Period beginning date based on the allocation processing period. Options for Format and Delimiter are explained later in this section.

&PEREND(Format[,Delimiter])& Period ending date based on the allocation processing period. Options for Format and Delimiter are explained later in this section.

&REFCODE& Reference code

&REFGROUP& Reference group

Date Processing

The &PERBEGIN& and &PEREND& SQL variables are flexible enough to handle multiple date variations that may be dictated by the database that you are processing. The various options available with the &PERBEGIN& and &PEREND& SQL variables are shown below. The date examples assume a 06/30/2003 period ending date:


38

SQL Variable Date Formats

Format Description

-not specified Default based on the database:

SQL Server - SQL Server Jet SQL - CVDate ODBC with Jet SQL - CVDate ODBC with ODBC Driver Syntax - Control

Julian Julian Long Integer Representation e.g. &PEREND(Julian)& = 731396 Default Delimiter: none

CVDate CVDate(Control Panel Date) with CVDate function e.g. &PEREND(CVDate)& = CVDate(“06/30/03”) Default Delimiter: none

Control Control Panel Date Format e.g. &PEREND(Control)& = 06/30/03 Default Delimiter: none

‘quoted format’ Date formatted as specified e.g. &PEREND(„YYMMDD‟)& = „030630‟ Default Delimiter: „

SQLServer SQL Server Generic Date format (YYYYMMDD) e.g. &PEREND(SQLServer)& = „20030630‟ Default Delimiter: „

Valid Delimiters

Delimiter Description

-not specified Default based on format above

none No delimiter

[,{,<,( Open and close of specified character

‘quoted string’ Quoted characters are used as the delimiter

Using User-Defined Query Parameters

Up to two special query parameters from the Allocation Maintenance form can be passed into pool formulas.

The query parameters can be any text value up to 60 characters in length. For example:

Salesperson name for a commission accrual


39

Branch code for a liability accrual

Tip: The query parameter is used exactly as it is typed. If you are using a query parameter as a “Where” condition in a SQL statement, and the field is a text field, you must surround the value in quotes.

Tip: You can use whole SQL clauses such as a “Where” clause in these parameters.

41

Chapter 5 - Setting up Allocations

Chapter 5

Setting up Allocations

Amount Allocations

This type of calculation computes a transaction amount using formulas. The formula is calculated for each designated recipient account with the other side of this entry designated as the offset account.

Step-by-Step Instructions

The procedure to set up this kind of entry is as follows:

1. Highlight the Allocations node in the Tasks pane.

2. Select File > New or click the new allocation icon.

3. Complete the Main tab, selecting Based On: Amounts.

Main Tab Fields

Allocation Key Type a unique key with up to 16 characters to identify the allocation.

Description Type a maximum 40-character description of the allocation. This description is also used as the transaction description when posting, unless you are using transaction allocations.

Based On: Amounts This option assigns the method Advanced Allocations uses to process formulas.

Amounts -- each recipient formula value is calculated as an individual transaction amount. This option is typically used when executing either an accrual calculation or a mixture of an accrual and an allocation calculation.


42

Automatic Reversal Optionally, check this box to have the general ledger create a reversing entry for the allocation once it has been posted. The reversing entry is created in the general ledger, not in Advanced Allocations, and must be processed from within the general ledger.

Note: This option is only available for amount-based and percentage-based allocations, and not for transaction-based allocations.

Test Allocation Click this button to verify that the allocation definition will return the correct values. The configuration screen displays allowing you to select processing criteria. An allocation report will display on the screen with all of the defined information.

Note: This option is for testing purposes only. Therefore, the configuration cannot be saved and the allocation transaction

will not be saved or posted.

Effective Dates Optionally complete the beginning and ending effective dates for this allocation. If the Effective Dates boxes are not checked, this allocation will be effective for all dates.

Note: Do not confuse effective dates with configured periods. Effective dates are always evaluated first to determine if the allocation is available for processing. Only then does processing of configured periods occur.

4. Complete the Offset Account tab.

Offset Account Tab Fields

Company Code Enter a company to be used for the offset transaction. Options include:

Accept the default of current processing company.

Enter a valid company key. You may select from a list of valid companies using the lookup window.

Filter the entire field. The value is derived from the recipient company as it is processed.

Account Enter an account to be used for the offset account. Options are as follows:

Enter a valid account number. You may select from a list of valid accounts using the lookup window.

Filter any portion of the field. The value will be derived from the recipient account as it is processed.


43

Skip Invalid Filtered Offset Accounts

If filtering is applied to the offset account, the filtered account segments are resolved by the recipient account. This substitution may produce offset account numbers that do not exist in the general ledger. Usually, if invalid offset accounts are found, an error occurs and the allocation is not processed and cannot be posted. However, if the Skip Invalid Filtered

Offset Accounts option is selected, then the invalid combinations are ignored and the allocation can be processed and then posted.

Reference Code Enter a reference code to be used for the offset transaction. Options include:

Accept the default of no assigned reference code. The entire offset amount will be charged to the designated account number with no reference code.

Enter a valid reference code or group. You may select from a list of valid codes using the lookup window. The offset amount will be charged only to the designated account – reference code combination.

Filter the entire field. The value is derived from the recipient reference code as it is processed.

5. Complete the Recipient Accounts tab. The Recipient Accounts tab is used to define all of the recipient accounts that will receive a calculated amount.

You can have multiple recipient account ranges, each with a different formula key. Advanced Allocations evaluates each recipient account range based on the range of each of the account segment values. The resulting accounts, currency, and companies are used as sources for any filtered values in the formulas or source fields. See the examples detailed later in this section for more information on using recipient account ranges and filters.

Note: Each Allocation is restricted to one currency in the recipient tab. If the allocation is saved with more than one currency defined on multiple recipient lines a warning will be provided to allow the user correct this unintentional mistake.

Recipient Accounts Tab Fields

Type Select Account to designate a single recipient account for the row or Range to designate a range of recipient accounts, based on the account segments.

Company Enter the company key for the account(s). The default is the current company. A lookup window is available to select from a list of companies.

From Recipient Type the beginning account number to be processed. A lookup window is available to select from a list of accounts.

Thru Recipient Type the ending account number to be processed. A lookup window is available to select from a list of accounts.


44

Ref Type Enter the type of reference code, either group or code, for the account(s). The default is no reference type assigned. A drop down list is available to select between no reference type, group, or code.

Note: Since the recipient account is used to evaluate filters in the offset reference code, no portion of the reference code may be filtered.

Value Enter a value that corresponds to the reference code or group for the account(s). The default is no value assigned. A lookup window is available to select from a list of valid values.

Crncy Enter the currency to use for the account(s). The default is the current company's home currency. A lookup window is available to select from a list of valid currencies.

Home Natural This field is used by Advanced Allocations to interpret the formula value returned when calculating amounts.

Home – converts the formula amount to the natural currency, based on the defined exchange rates.

Natural – converts the formula amount to the equivalent home currency, based on the defined exchange rates.

Formula Select the formula key to use for this recipient account(s). A lookup window is available to select from a list of valid formulas. You can also create or edit the formula by clicking

the Maintenance icon .

Override Type the value to override any formula calculation. This is used primarily if the allocation uses fixed amounts. This value is then used in place of any formula calculation.

The value of the override determines the exact amount of the transaction. The override field must be blank, which is the default, if a formula is to be used.

Note: If you use the override value, a formula key must be assigned. The formula need not contain any formula lines.

6. Complete the Periods tab.


45

This tab defines the eligible periods that can be processed for this allocation but does not actually process the allocation (see Chapter 10 for details on processing). You may select individual periods to process for each allocation. By default, all periods are selected. Click Select All to select all periods or click Unselect

All to deselect all periods.

The periods available are automatically displayed from the current year fiscal calendar. The periods selected automatically “roll over” from year to year unless the number of fiscal periods in your year is changed. Advanced Allocations only processes for those periods selected.

If an allocation is not processed for a configured period, a warning is reported in the Allocation Report.

Note: The periods are selected based on the period number. Advanced Allocations processes any year based upon this period number. The current year fiscal dates are displayed for information purposes only.

7. Optionally, test the Allocation definition by clicking the Test Allocation button. This option allows the current allocation to be run without creating a configuration.

Next, the Process Allocations configuration form displays, allowing you to select the specific processing options to be used to test the allocation definition. These options are described in detail in Chapter 10. Select the desired options and click Ok.

The allocation is processed and the journal entry can be viewed on the Allocation Report.

Note: Although the report shows the actual journal entries, the system does not generate or store these journal entries. Consequently, they are not listed in the Processed Allocations list or displayed on the History tab of the allocation. This feature is intended as a tool to allow for quick verification of

the allocation definition without creating a processing configuration.

8. Review the History tab.


46

This tab displays the processing history of this allocation but does not actually process the allocation. See Chapter 10 for details on processing.

History Tab Fields

Type Indicates if the allocation was processed by Allocation or by Batch.

Run By Identifies the user that processed the allocation.

Date & Time Specifies the date and time that the allocation was processed.

Status Indicates the status of the allocation.

Pending designates that the allocation has been processed, but not yet posted.

Posted specifies that the allocation has been processed and posted to the general ledger or the resulting transaction table.

Purged indicates that the posted allocation was purged.

How Amount Allocations Are Processed

The following steps are executed when Advanced Allocations processes an allocation based on amounts:

1. First, each segment range of the recipient account ranges is evaluated separately, followed by the reference code. This process creates a list of valid recipient company, currency, account, and reference code combinations. Each recipient value is then used as a source for any filtered positions in the formula or offset accounts, reference codes, companies or currency.

2. The offset account, reference code, and company for each recipient account are determined using filtered positions, if any from the recipient account. The currency specified for the recipient account is used for the offset account as well.

3. The transaction value for each offset-recipient combination is computed using filtered positions, if any, from the recipient values.

4. A debit-credit transaction for each offset and recipient combination is created.

Example 1:

An interest accrual is calculated based on 7.5% per annum rate of the average daily balance of the loan payable account.

A formula is established to create the entry as shown below.


47

The formula is assigned to the recipient account (Interest Expense) in the allocation form as shown below.

The other side of this entry is an offset account of Accrued Interest as shown below.

The recipient account is the recipient of the calculation. If the amount calculated is negative, the recipient account is credited. If the amount calculated is positive, the recipient account is debited. The offset account is always the offsetting entry. In this case, the normal balance of the note payable on line 1 of the formula is a credit. It is multiplied by a negative interest rate. The result of two negatives is a positive number, which is then divided by 12 for a monthly accrual. The positive number is therefore a debit to the recipient account.

Filters in Formulas

Filters may also be used with formulas. Filters in formulas receive parameters from the following sources for populating the filtered values:

Formula Filters

Filter Parameter

Accounts Recipient Accounts

Reference Type Values Recipient Reference Type

Companies Recipient Companies

Currency Recipient Currency

Example 2:

Assume the same interest accrual as Example 1, except the interest accrual is to be computed by location (segment 2 of the account number).

The formula is revised to filter the note payable account in the second segment (the location segment) and is shown below.

The recipient accounts now consist of a range of interest expense accounts for all locations.

The formula is computed separately for each new recipient account as shown below.


48

Filters in Offset Accounts

Filters may also be used with offset accounts. In this case, by assigning a filter to the offset account, a relationship with the recipient account is established. Offset values receive parameters from the recipient accounts as follows.

Offset Filters

Filter Parameter


Reference Codes Recipient Reference Codes


Example 3:

Assume the same interest accrual as Example 2 except that accrued interest is also credited by location. The formula and recipient accounts are the same. The offset account is changed to reflect a filter in the second segment as shown below.

The revised account distribution is shown as follows and the formula calculation.

Percentage Allocations

This type of allocation is traditionally used to reallocate source accounts to recipient accounts. The formula is computed as a percentage of the source account. The general calculation is expressed as follows:

formula value * source values

Thus, instead of the formula computing an amount by itself, it computes a weighted basis percentage of the source value. Advanced Allocations subtotals the percentages where the source account breaks. Just as in the other calculations, the calculated value will be received by the recipient account.




3. Complete the Main tab, selecting Based On: Percentages.


49

Main Tab Fields

Allocation Key Type a unique key with up to 16 characters to identify the allocation.

Allocation Description Type a maximum 40-character description of the allocation. This description is also used as the transaction description when posting, unless you are using transaction allocations.

Based On: Percentages This option assigns the method Advanced Allocations uses to process formulas.

Percentages - each recipient formula value is computed as a weighted percentage of the total of all the recipient formula values. The proportional results are multiplied by a source value. You typically select this option when you are performing a percentage allocation.

Automatic Reversal Optionally, check this box to have the general ledger create a reversing entry for the allocation once it has been posted. The reversing entry is created in the general ledger, not in Advanced Allocations, and must be processed from within the general ledger.

Note: This option is only available for amount-based and percentage-based allocations, and not for transaction-based

allocations.

Test Allocation Click this button to verify that the allocation definition will return the correct values. The configuration screen displays allowing you to select processing criteria. An allocation report will display on the screen with all of the defined information.

Note: This option is for testing purposes only. Therefore, the configuration cannot be saved and the allocation transaction will not be saved or posted.

Effective Dates Optionally, complete the beginning and ending effective dates for this allocation. If the Effective Dates boxes are not checked, this allocation will be effective for all dates.

Note: Do not confuse effective dates with configured periods. Effective dates are always evaluated first to determine if the allocation is available for processing. Only then does processing of configured periods occur.


50

4. Complete the Source Account tab.

Source Account Tab Fields

Company Code Enter the company to be used for the source transaction. Options are to:

Accept the default of current processing company.


Filter the entire field. The value is derived from the recipient company as it is processed.

Account Enter the account to be used for the source account, which will be used as the source value to calculate the allocated amounts (unless a pool formula is specified in the Percentage

Options tab). Options are:


Filter any portion of the field. The value will be derived from the recipient account as it is processed.

Skip Invalid Filtered Source Accounts

If filtering is applied to the source account, the filtered account segments are resolved by the recipient account. This substitution may produce source account numbers that do not exist in the general ledger. Usually, if invalid source accounts are found, an error occurs and the allocation is not processed and cannot be posted. However, if the Skip

Invalid Filtered Source Accounts option is selected, then the invalid combinations are ignored and the allocation can be processed and then posted.

Reference Code Enter a source reference code to be used as the source value to calculate the allocated amounts. Options include:

Accept the default of no assigned reference code. The entire source amount, regardless of reference codes, will be used as the source value to calculate the allocated amounts.

Enter a valid reference code or group. You may select from a list of valid codes using the lookup window. This source amount will be used as the source value to calculate the allocated amounts.

Filter the entire field. The value is derived from the recipient reference code as it is processed.

Note: You may only select the valuation methods of Net Change or Pool Formula to use reference codes in the

source account.

5. Complete the Percentage Options tab. The Percentage Options tab is used to define the source values in the case of percentage allocations.


51

Percentage Options Tab Fields

Source Account Percent Enter the percentage of the source account or source pool to be allocated. The default is 100%.


This option allows the ability to specify a maximum amount to be allocated to the source account. If the allocation amount calculated is greater than the maximum amount allowed, the maximum amount is applied to the source account and the remainder is applied to the rounding account. If no rounding account is specified, the first recipient account in the range is adjusted to only allow for the maximum amount to be applied to the source account. For this reason, it is recommended that a rounding account be specified when using this option.

Source Valuation Currency

Enter the currency to be used to restrict the balances of the source values computations. Options are:

Accept the default, the home currency.

Enter a valid currency key. You may select from a list of valid currency keys using the lookup window.

Filter the entire field to derive the value from the source currency as it is processed.

Type an * to include all currencies in the source values.

Offset Company Enter the company to be used for the offset. The use of an offset is optional and can be used to charge the offset to a different company rather than the source company. If this field is left blank, then the source company is charged for the allocation. Options are:

Accept the default of the processing company.


Filter the entire field. The value is derived from the source company as it is processed.


52

Offset Account Enter the offset account to be charged for this allocation. Offset accounts are optional and can be used to charge a different account or series of accounts, rather than the source account. If you leave the field blank, the source account is charged for the allocation. Options are:


Filter any portion of the field to derive the value from the source account as it is processed.

Offset Account Reference Code

Enter an offset account reference code to be charged for this allocation. Offset account reference codes are optional and can be used to charge a different reference code or series of reference codes, rather than the reference code of the source account. If you leave the field blank, the source account is charged for the allocation. Options are:

Enter a valid reference code. You may select from a list of valid codes using the lookup window.

Filter the entire field to derive the value from the source account reference code as it is processed.

Note: In order to use filters in the Offset Account Reference Code, filters must be present in the Offset Account.

Valuation Method Select the appropriate valuation method of the source value. The valuation method may be a restriction of the source account balances or a specified pool formula. Valid options are:

Beginning Balance

Net Change

Ending Balance

Average Daily Balance

Pool Formula

Selecting Average Daily Balance calculates the average daily ending balance for the processing period based on the historical transactions for the account. You can select whether to exclude Saturdays or Sundays from the calculation.

Note: Do not confuse the valuation method presented here with the valuation method in the formulas. This valuation determines which source account balance to allocate. The formula determines how the account will be allocated (if a percentage allocation).

Valuation methods are not applicable to transaction allocations because an individual document amount is allocated.

Pool Formula Complete the formula key for the pool formula calculation. If a pool formula is assigned, it is used in place of the individual source account value. Options are to:

Use the lookup window to select from a list of formulas.

Use the Maintenance icon to create or edit a formula.


53

Query Parameters Complete any query parameters that are to be used with database queries in a pool formula.

Include Previous Allocations

Check this box to include the results of previous allocations processed in an allocation batch.

Rounding Company Enter the company to be used for any rounding difference. The use of rounding is optional. The rounding account determines which account to charge with the remainder if a rounding difference exists, which may happen in this type of transaction. Options are:

Accept the default of the processing company.


Filter the entire field. The value is derived from the source company as it is processed.

Rounding Account Enter the filter for the account to be charged with any rounding difference. The rounding account determines which account to charge with the remainder of an account balance after a percentage is distributed. Rounding accounts are optional. Options are:

Leave the field blank. The rounding difference remains in the source account.


Filter any portion of the field. The value is derived from the source account as it is processed.

If the rounding account is the same as any of the source, offset, or recipient accounts, a separate rounding transaction is not generated. If the account is different than any of these accounts, a separate rounding transaction is generated.

Rounding Account Reference Code

Enter a reference code for the account to be charged with any rounding difference. Rounding account reference codes are optional. Options are:

Leave the field blank. The entire rounding amount is distributed to the rounding account.

Enter a valid reference code. You may select from a list of valid codes using the lookup window.

Filter the entire field. The value is derived from the source account reference code as it is processed.

Note: In order to use filters in the Rounding Account Reference Code, filters must be present in the Rounding Account.

6. Complete the Recipient Accounts tab. The Recipient Accounts tab is used to define all of the recipient accounts that will receive a source value distribution.

Note: Each Allocation is restricted to one currency in the recipient tab. If the allocation is saved with more than one currency defined on multiple recipient lines a warning will be provided to allow the user correct this

unintentional mistake.


54

You can have multiple recipient account ranges, each with a different formula key. Advanced Allocations evaluates each recipient account range based on the range of each of the account segment values. The resulting accounts, currency, and companies are used as sources for any filtered values in the formulas or source fields. See Chapter 2 and the examples in this section for more information on using recipient account ranges and filters.

Recipient Accounts Tab Fields

Type Select Account or Range.

Company Enter the company key for the account(s). The default is the current company. A lookup window is available to select from a list of companies.

From Recipient Enter the beginning account number to be processed. A lookup window is available to select from a list of accounts.

Thru Recipient Enter the ending account number to be processed. A lookup window is available to select from a list of accounts.

Ref Type Enter a reference code or group for the account(s). The default is no reference code assigned. A lookup window is available to select from a list of valid codes or groups.

Note: Since the recipient account is used to evaluate filters in the source account, no portion of the reference code may be filtered.

Value Enter a value that corresponds to the reference code or group for the account(s). The default is no value assigned. A lookup window is available to select from a list of valid values.

Crncy Enter the currency to use for the account or range of accounts. The default is the current company's home currency. A lookup window is available to select from a list of valid currencies.

Home Natural This field does not impact the allocation, therefore Home is assumed as the currency type. The Natural value will be calculated based on the defined conversion rates.

Formula Select the formula key to use for the recipient account(s). This value defaults to the default formula key specified in the Allocations Main tab. A lookup window is available to select from a list of valid formulas. You can also edit the formula by

clicking the Maintenance icon .

Override Type the value to override any formula calculation. This is used primarily if the allocation uses fixed basis amounts or percentages. This value is then used in place of any formula calculation.

The value of the override determines the proportional value to the total of all of the overrides assigned to the recipient accounts. For example, two override values of 20 and 30 would receive 40% (20/50) and 60% (30/50) of the source values respectively.



55

This tab defines the eligible periods that can be processed for this allocation, but it does not actually process the allocation (see Chapter 10 for details on processing). You may select individual periods to process for each allocation. By default, all periods are selected. Click Select All to select all periods or click Unselect All to deselect all periods.

The periods available are automatically displayed from the current year fiscal calendar. The periods selected automatically “roll over” from year to year unless the number of fiscal periods in your year is changed. Advanced Allocations only processes for the periods selected.

If an allocation is not processed for a configured period, a warning is reported in the Allocation Report.

Note: The periods are selected based on the period number. Advanced Allocations processes any year based upon this period number. The current year fiscal dates are displayed for information purposes only.

8. Optionally, test the Allocation definition by clicking the Test Allocation button. This option allows the current allocation to be run without creating a configuration.

Next, the Process Allocations configuration form displays, allowing you to select the specific processing options to be used to test the allocation definition. These options are described in detail in Chapter 10. Select the desired options and click Ok.

The allocation is processed and the journal entry can be viewed on the Allocation Report.


56

Note: Although the report shows the actual journal entries, the system does not generate or store these journal entries. Consequently, they are not listed in the Processed Allocations list or displayed on the History tab of the allocation. This feature is intended as a tool to allow for quick verification of

the allocation definition without creating a processing configuration.


This tab displays the processing history of this allocation but does not actually process the allocation (see Chapter 10 for details on processing).

History Tab Fields

Type Indicates if the allocation was processed by Allocation or by Batch.

Run By Identifies the user that processed the allocation.

Date & Time Specifies the date and time that the allocation was processed.

Status Indicates the status of the allocation.

Pending designates that the allocation has been processed, but not yet posted.

Posted specifies that the allocation has been processed and posted to the general ledger or resulting transaction table.

Purged indicates that the posted allocation was purged.

How Percentage Allocations Are Processed

Advanced Allocations executes the following when processing an allocation based on percentages:

1. First, each segment range of the recipient account ranges is evaluated separately, followed by reference codes. This process creates a list of valid recipient company, currency, account, and reference code combinations. Each recipient value is then used as a source for any filtered positions in the formula or source accounts, reference codes, companies, or currencies.

2. The basis value for each recipient account processed is determined using filtered positions, if any from the recipient account.

3. The source account, reference code, company and currency for each recipient account are determined using filtered positions, if any from the recipient account.

4. The offset account, if applicable, for each recipient account is determined using filtered positions, if any, from the source account.

5. The rounding account, if applicable, for each recipient account is determined using filtered positions, if any, from the source account.


57

6. The weighted basis percentage of the source value is computed by subtotaling the percentages when the source account breaks. Just as in the other calculations, the calculated value is received by the recipient account. The other side of the entry can be distributed to either the offset account, if one is specified, or to the source account if the offset account field is left blank.

7. The individual allocated amount is calculated based upon the weighted basis percentage times the source value.

8. A transaction is created, charging each source or offset account (if applicable) and distributing the allocated amount to each recipient account.

9. If a maximum allocated amount is specified, then the calculated amount is compared to the specified maximum. If the calculated amount is greater than the designated maximum amount, the source account is adjusted down to the maximum amount and the remainder is applied to the rounding account. If no rounding account is specified, then the first recipient account is automatically adjusted to accommodate the maximum.

10. Any rounding differences are evaluated. If no rounding information is provided, the source transaction changes to match the total of the recipient transactions. If a rounding account filter exists, the source account number is processed through the filter to determine the rounding account number. A transaction is generated to the resultant rounding account for the difference. If the rounding account number is the same as the source, offset, or recipient account, the transaction is merged into these accounts rather than creating an additional transaction.

Filters in Formulas

Note that percentage allocations usually have filters in the formula. This is because the recipient account receives a weighted portion of the formula result. Thus, the formula recalculates with the changed filters or variables, arriving at a weighted percentage.

Formula Filters

Filter Parameter


Reference Type Values Recipient Reference Type


Currency Recipient Currency

Example 1:

Operating costs for a single account 7000-000-100-00 are to be allocated to each location (second segment) on the basis of head count in each location. The recipient accounts are as follows:

7000-000-100-00

7000-100-100-00

7000-200-100-00

7000-300-100-00

Head count is maintained in statistical account 9900 and separated by location.

The formula to compute the basis is as follows:

The recipient accounts are shown as a range of accounts as shown below. Note that the Thru Recipient field

is set as 999 to pick up all possible values in the segment range.

The source account is a single account consisting of the account 7000-000-100-00.

The head count is not viewed as an amount to itself, but a proportional value to the total of all of the head count values as shown below.


58

Each proportion is in turn multiplied by the individual source account.

Filters in Source Accounts

If you wanted to have multiple source accounts, you would assign a filter to the portion of the source account number that corresponds to the recipient account.

Source Filters

Filter Parameter




Example 2:

Assume the same facts as Example 1, except you want to allocate from each location (segment 2) to departments (segment 3) on the basis of head count. Thus, we have multiple source accounts. The source/recipient account relationship is shown below:

The formula to compute basis must now be weighted on the basis of both location and department as shown below:

The recipient accounts are also adjusted to go across location as shown below:

In this example, there are multiple source accounts that vary by location. Thus, we assign a filter to vary the source account location as the recipient account changes as shown below:

Filters in Offset Accounts

Filters may also be used with offset accounts. In this case, by assigning a filter to the offset account, a relationship with the recipient account is established. Offset values receive parameters from the recipient accounts as follows.


59

Offset Filters

Filter Parameter




Example 3:

Assume the same interest accrual as Example 2 except that accrued interest is also credited by location. The formula and recipient accounts are the same. The offset account is changed to reflect a filter in the second segment as shown below.

The revised account distribution is shown as follows and the formula calculation.

Filters in Rounding

You may place a filter in the rounding account. These values are filled from the source account.

Note: In order to use filters in the Rounding Account Reference Code, filters must be present in the Rounding Account.

Rounding Filters

Filter Parameter

Rounding Company Source Company

Rounding Account Source Account

Rounding Account Reference Code Source Account Reference Code

If the rounding account is determined to be the same account as either the source, offset, or recipient account, the system merges the rounding difference into the existing transaction.

Example 4:

Use the same facts as Example 2 except that we want to charge any rounding to account 9999 for the same location and department as the source account. The rounding account is shown as follows:


60

The rounding account is therefore charged.

Skip Invalid Filtered Source Accounts

When a source account contains filters, it is possible for an invalid source account combination to be generated during processing. Typically, this occurs when recipient ranges are utilized to resolve the filters. By default, if an invalid source account is returned, the system will generate an error and the resulting journal entries cannot be posted to the general ledger, even if valid source account combinations exist along with the invalid combinations.

To bypass invalid source account combinations and to allow valid combinations to be posted, select the Skip Invalid Filtered Source Accounts option. Enabling this option is particularly useful when a large range of account numbers is defined and specifying an exact recipient range is not practical.

Note: If reference codes are defined, this option skips invalid account-reference code combinations as well.

The following example shows the resulting transactions when the Skip Invalid Filtered Source Accounts

option is enabled and disabled.

Example:

Suppose your chart of accounts includes the following accounts:

1000-000-000-00 4000-000-000-00

2000-000-000-00 4000-100-000-00

3000-000-000-00 4000-200-000-00

The recipient range is set from 4000-000-000-00 to 4000-200-000-00. The source account 1000-???-000-00 is filtered creating multiple source accounts for each location.

Processing the allocation would produce the following results:

Source Acct. Recipient Acct Message

1000-000-000-00 4000-000-000-00 (no error)

1000-100-000-00 4000-100-000-00 Error, Invalid Source

1000-200-000-00 4000-200-000-00 Error, Invalid Source

Since errors were encountered during processing, the system prevents the journal entries from posting to the general ledger, even the valid journal entry.

By selecting the Skip Invalid Filtered Source Accounts option, the system ignores invalid source combinations and bypasses posting restriction.


61

Therefore, the allocation would produce the following results:

Source Acct. Recipient Acct Message

1000-000-000-00 4000-000-000-00 (no error)

The invalid source accounts were skipped and the journal entry can be posted to the general ledger.


The Maximum Allocated Amount option allows the ability to specify a maximum amount to be allocated from the source account. If the calculated source amount is greater than the maximum amount defined, then the source amount is adjusted down to the maximum amount and the percentages will be calculated from the maximum amount. If no rounding account is specified, and the rounding amount increases the source amount higher than the maximum, then the first recipient account in the range is adjusted to only allow for the maximum amount to be applied to the source account. For this reason, it is recommended that a rounding account be specified when using the Maximum Allocated Amount option.

Advanced Options

Source Pools

Many times, you will find it necessary to summarize a group of values that comprise a source pool. Source pools are not restricted to account balances, but may also include enterprise-wide information. Source pools typically are charged to an offset account.

1. In the Percentage Options tab, select Valuation Method: Pool Formula.

2. Complete the formula to compute the source pool.

The source account is still charged with the entry to offset the recipient account. Since the source account is not directly involved in the computation of the entry, you can make this account whatever you wish. The offset account can still be used, but is redundant.

Filters in Source Pools

The source pool is computed by combining every resultant in the source account. Thus, by using a filter in the source account, multiple source pools can be processed from a single allocation.

Source Pool Filters

Filter Source Pools

Accounts Source Accounts

Reference Code Source Reference Code

Companies Source Companies

Currencies Source Currencies

Example 1:

Assume the same facts as Example 2 in the percentage allocation section, except the operating costs are to be computed as a single source pool and charged to the single offset account of 7900-000-100-00. The formula to compute the source pool is shown below:


62

The Percentage Options tab is shown below with Pool Formula selected.

The Source Account tab shows a single source account.

Note: In this type of calculation, the source pool is computed instead of the individual source account. However, the source account is charged with the other side of the entry from the recipient account. The use of reference codes in the source account is not supported in source pool calculations.

Transaction Allocations

Transaction allocations allow you to allocate detailed transactions to one or more recipient accounts using the Advanced Allocations formula engine. For example, advertising invoices charged to a control account could be allocated to retail stores based upon each store‟s sales. This eliminates the need to perform manual document splitting in accounts payable. Procedurally, this provides the accounts payable clerk with the ability to charge any documents to be split to a control account. Then Advanced Allocations can allocate these transactions according to the defined basis.

Another application of transaction allocations is reclassifying transactions when a restructuring of the chart of accounts occurs. For example, travel expenses are split into more finite categories and you want to historically restructure the existing documents. All of the original documents are preserved, but a balancing entry is created between the original account and the new accounts.

Transaction allocations are processed the same way as percentage allocations, except that each document is allocated instead of a balance. All of the details of the original transaction are assigned to the generated allocation transaction. This procedure provides more visibility into supporting documents when distributing financial statements and viewing the accompanying detail.

Several options in percentage allocations are not available to transaction allocations. For instance:

Only the source company can be the processing company, however, multiple recipient companies can be designated.

Valuation methods are not applicable to transaction allocations since you are not allocating a balance.

Transaction allocations can also be used in an allocation batch. See Chapter 9 for the processing rules for transaction allocations and allocation batches.

In addition, documents processed in transaction allocations can be restricted to a particular date range.


63


The procedure for setting up transaction allocations is identical to those for percentage allocations except for selecting Based On: Transactions in the Allocations Maintenance window. To set up this kind of entry:



3. Complete the Main tab, selecting Based On: Transactions.

4. Complete the Source Account tab.

5. Complete the Percentage Options tab.

6. Complete the Recipient Accounts tab.

Note: Unlike in Percentage Allocations, the reference code on the Source tab determines which

transactions will be allocated.


8. Optionally, test the allocation by clicking the Test Allocation button on the Main tab.


Example:

Suppose you need to allocate several detailed advertising invoices from an account 6500-000-200-00 to each location (segment 2) on the basis of each location‟s sales. The three advertising invoices are as follows:

Doc# Description Amount

12345 Jones & Way PR $1000.00

98765 Metropolis Newspaper $1200.00

88999 Daily Planet Ads $2800.00

TOTALS $5000.00

Formulas computed the following basis calculations for each location.

Locn %

000 50

100 20

200 10

300 20

If the allocations were generated using the standard Based On: Percentage option, the following entries would be created.


64

Note: The description is assigned based on the description assigned in the window. The document number is assigned based on that assigned in the Options window (Setup > Options).

If the allocations were generated using the Based On: Transactions option, each document would be processed as a separate transaction.

Thus, the transaction allocation produces a more detailed transaction even though the resulting totals are the same.

Note: Although you can use offset accounts with transaction allocations, caution is advised to avoid unintended consequences. Suppose, for example, that your advertising expenses for a specific date are allocated in detail, and the remaining balance is allocated in total. In this case, if the transactions are allocated and an offset is used, the original account balance will not be affected. Therefore, when the percentage allocation is processed for the balance of the account, the value of the transactions will be

allocated a second time, as part of the total account balance.

Calculating Allocations Using Amounts Method

The formula engine provides additional possibilities for computing entries. To reiterate, the two methods to compute transactions are as follows:

Formula computes transaction amounts

Formula computes basis percentage times source values (or formula generated source pools)

However, consider the nature of the percentage method. It can be expressed mathematically as follows:

source value * (basis value /sum basis values)

Formulas calculating transaction amounts can also emulate this type of calculation using multiplication and division operands.


65

Example:

A previous example can be restated in a single formula. Assume you want to allocate from each location (segment 2) to departments (segment 3) on the basis of head count. Thus, we have multiple source accounts. The source/recipient account relationship is shown below:

Note: If this type of calculation could be done for any allocation, then why define any allocation using percentages? Allocations based on percentages process more efficiently since the source value is only calculated when the source account or source pool change. Allocations based on amounts recalculate every time the recipient account changes. In most cases, the source values do not change; therefore, the same amounts are calculated repeatedly.

In addition, percentage calculations have a built-in means to deal with rounding differences. However, you may find it necessary or useful to use this kind of approach.

Plan Sheet Values in Formulas

This section assumes that Active Planner is installed and requires a basic understanding of plan sheets. Plan sheet values are evaluated in the same manner that a set of account balances is evaluated. The account number is read from the plan sheet Account Number column and the periods based on the configured periods for each column.

Example 1:

Suppose an allocation is based on budgeted departmental head count. The head count is maintained in a plan sheet (plan key is Hd_Cnt, sheet is Dept_Months) with the values displayed as follows for the first six months of the year.

The columns are assigned to the Group Key:Months and the Valuations:Ending Balance.

The formula to retrieve the departmental head count for each period as it is processed is as follows:


66

The selected plan sheet and column group is noted in the Bal Code field. The Current Period +/- 0 value in the From and Thru Period fields are used to represent the allocation processing period. Selecting Ending

Balance in the Valuation field uses the ending balance at the end of any periods found in the column group. The filters in the Parameter 1 and Parameter 2 fields return the appropriate positions from the plan sheet Account Number column.

If you wanted the formula to calculate the increase in head count for the period being processed from the beginning of the year, you would:

Change the Valuation field to Net Change.

Add a column in the plan sheet showing the head count at the beginning of period 1. Set its Valuation field to Beginning Balance and its period to Absolute Period 1.

The system computes the change based upon the period processed, less the beginning balance head count.

Example 2:

Plan sheets can also be used in allocations to construct accruals. Consider the use of the following plan sheet to calculate a prepaid insurance calculation.

The following formula is used to retrieve the prepaid insurance calculation. Note that the calculation also subtracts the current ending balance of the prepaid insurance account.

For more information on plan sheets, see the Epicor Advanced Active Planner User Guide.

Using Temporary Accounts to Compute Totals

The final advanced option available is the use of temporary accounts to compute and hold values that are subsequently allocated. This procedure may be used when you have multiple source pools and the source pools do not fluctuate according to source accounts.

1. Create any temporary accounts to hold source values.

2. Create an allocation(s) using amounts to compute the source pools, and store the results in the temporary accounts defined as recipient account(s).

Note: You can manipulate the debit-credit result desired using a multiplication operand and constants. For a recipient credit, make sure the formula is negative (using a constant of -1). For a recipient debit, make sure the formula is positive.

3. Create an allocation(s) using percentages to allocate the temporary accounts from step 2. The temporary accounts should be defined as the source account. On the Percentage Options tab, check the Include Previous Allocations box.

4. Include the allocations in steps 2 and 3 as part of the same allocation batch. Allocations from step 2 must precede the allocations from step 3. For more information on allocation batches, see Chapter 9.

67

Chapter 6 - Reference Code Processing

Chapter 6

Reference Code Processing

Overview

Advanced Allocations can process reference codes in a number of ways:

Formulas can retrieve balances based on reference groups, either in total or by individual code in the group.

Formulas can retrieve balances based on individual reference codes.

Filters provide the ability to have multiple source and recipient reference codes in a single allocation transaction.

Reference code groups can be applied to recipient accounts allowing for allocations to process the reference codes within the group.

Note: Advanced Allocations assumes there are no common Reference Code and Reference Type names.

In the event your data has a "same name" condition, Advanced Allocations will retrieve balances from the

transactions that have the reference code attached.

Reference Codes in Formulas

When retrieving actual balances, you can specify a reference group or code in each formula line. For the Ref

Type, you may either choose to apply reference groups, which contain all defined reference codes for that group, or to apply a specific reference code. This determines which specific reference code(s) and related balances are included in the formula line.

Formulas Reference Code Value Options

Ref Type Select Group or Code from the drop down list.

Group represents a group of reference codes.

Code represents a specific reference code.

Specific Value Enter a specific reference group or code value. You may select from a list of valid reference groups or codes in the lookup window.


68

? Enter a complete filter. If the formula is applied to a recipient range, the recipient reference code will be used to evaluate the filter. If the formula is used in a source pool, the source reference code value will be used.

See the next sections for more information about filters and how they are evaluated.

Note: You may not enter a partial filter for the reference code value. You must type all question marks for the complete

length of the key.

Reference Codes in Amount Transactions

Offset Reference Codes

The offset account reference code dictates the reference code that is charged to the transaction. Choosing a reference group is not an option in the offset account. However, multiple reference codes may be used if filters are used.

Offset Account Reference Code Options

Specific Value Enter a specific reference code value. You may select from a list of valid reference codes in the lookup window.

? Enter a complete filter. This value is derived from the recipient reference code value.

Note: You may not enter a partial filter for the reference code value. You must type all question marks for the complete length of the key.

Recipient Reference Codes

You must specify a Ref Type of either Group or Code and a corresponding Value for each range of recipient accounts. Group should be used to include all reference codes in the group as valid recipient reference codes. Use Code to specify a specific recipient reference code. Since the recipient reference group or code serves as the source for any filtered source or offset reference codes and formula reference groups or codes, you must enter a valid reference group or code or use the lookup window to select from a list of valid values for each recipient range line.


69

Reference Codes in Percentage Transactions

Source Reference Codes

The reference code designated on the Source Account tab dictates the balance to be allocated and/or the reference code to charge to the transaction. To charge the transaction with a different reference code, an offset can be designated on the Percentage Options tab, as described in the next section. Choosing a reference group is not an option in the source account. However, multiple reference codes may be allocated if filters are used.

Source Account Reference Code Options


? Enter a complete filter. This value is derived from the recipient reference code value.


Offset Reference Codes

The offset reference code designated on the Percentage Options tab dictates the reference code charge to the transaction. To select an offset reference code, an offset account must be designated. Choosing a reference group is not an option in the offset account. However, multiple reference codes may be used if filters are applied.

Offset Account Reference Code Options


? Enter a complete filter. This value is derived from the source reference code value.


Rounding Reference Codes


70

You may specify a rounding reference code to charge the rounding difference. In most cases, this will be the same as the source reference code. As with source reference codes, reference groups may not be specified, however filters may be used. If you filter the field, then the value will be retrieved from the source reference code.

Rounding Account Reference Code Options


? Enter a complete filter. This value is derived from the source reference code value.


Recipient Reference Codes

You must specify a Ref Type of either Group or Code and a corresponding Value for each range of recipient accounts. Group should be used to include all reference codes in the group as valid recipient reference codes. Use Code to specify the recipient reference code. Since the recipient reference group or code serves as the source for any filtered source reference codes and formula reference groups or codes, you must enter a valid reference group or code or use the lookup window to select from a list of valid values for each recipient range line.

Processing Reference Code Transactions

Based On Amounts

Reference codes are essentially an extension of an account number and are established in the general ledger. Advanced Allocations processes amount allocations containing reference codes in the same manner as general amount allocations, with the exception that now reference codes are used to determine the valid recipient company, currency, account, and reference code combinations. If a reference group is selected, each reference code within the group can be a valid recipient reference code.

Example 1:

Reference codes have been established to designate projects in a reference group called Design. A bonus accrual is based on 5% of sales revenue of each project.

The following formula is used to calculate the bonus. Note that the reference code value is filtered, allowing for the calculation of the bonus for each recipient reference code.

The formula is assigned to the recipient accounts in the allocation shown below:


71

Based on the reference group and the recipient accounts, a sample list of recipient account, reference code combinations are as follows:

6000-300-900-00 CL-001

6000-300-900-00 CL-002

6000-300-900-00 GP-001

6000-300-900-00 GP-002

6000-300-900-00 MP-001

6000-300-900-00 MP-002

6000-300-900-00 MP-003

The other side of this entry is an offset account of Accrued Payroll.

A bonus accrual amount is calculated from the formula and applied to each of the recipient account, reference code combinations. The resulting allocation contains the one offset account and 7 recipient accounts.

Based On Percentages

Reference codes are essentially an extension of an account number and are established in the general ledger. Advanced Allocations processes percentage allocations containing reference codes in the same manner as general percentage allocations, with the exception that now reference codes are used to determine the valid recipient company, currency, account, and reference code combinations.

Example 1:

In the Design reference group, reference codes have been established to designate projects. Within each location, salaries are to be allocated to each project, based on the revenue of each project.

The formula to compute the basis is shown below. Note that the reference code value has been filtered.

The recipient accounts are a range of accounts as shown below. Note that the reference group of Design has been assigned. By assigning the group, each reference code within the group can be a valid recipient code.

Based on this recipient range, a sample list of valid recipient account, reference code combinations would be as follows:

6000-100-100-00 CL-001

6000-100-100-00 GP-002

6000-200-100-00 MP-001

6000-200-100-00 MP-002

6000-300-100-00 MP-003

Based on activity, the basis is calculated as shown below:


72

Each proportion is in turn multiplied by the individual source account.

73

Chapter 7 - Intercompany Processing

Chapter 7

Intercompany Processing

Overview

Advanced Allocations can process across multiple companies in a number of ways:

Formulas can retrieve balances across companies. Different account masks and fiscal periods are also accommodated.

Intercompany transactions can be created.

Filters provide the ability to have multiple source and recipient companies in a single allocation transaction.

Multicompany Processing in Formulas

A formula can be used to create cross-company basis or pool calculations, or transaction amounts. A company code can be designated for each formula line with an Accounts type

Formulas Company Key Options

-default- Current processing company.

Specific Company Enter a specific company key. You may select from a list of valid companies in the lookup window.

? Enter a complete filter. This value is derived from the source company if used in a source pool or from the recipient company if used in any other calculation.

Note: You may not enter a partial filter for the company code. You must type all question marks for the complete length of the key.

If you change the company, the account mask changes to reflect the account structure of the selected company. Likewise, the fiscal calendar of the selected company is used for the selected periods.

Intercompany Transactions

Source Company

You must designate a company to be charged for the source account transaction. If you are processing a percentage allocation, the source company also determines the company used in processing source values.


74

Source Company Key Options

-default- Current processing company.

Specific Company Enter a specific company key. You may select from a list of valid companies in the lookup window.

? Enter a complete filter. This value is derived from each recipient company processed.

Note: You may not type a partial filter for the company code. You must type all question marks for the complete length of the key.

Rounding Companies

You must specify a rounding company to charge the rounding difference to. In most cases, this will be the same as the source company. The options available for the rounding company are also the same as for the source company. If you filter the field, then the value will be retrieved from the source company.

Recipient Companies

You must specify a recipient company for each range of recipient accounts. The field defaults to the current processing company. Each company serves as the source for any filtered source and formula companies. You must enter a valid company code for each recipient range line. You may use the lookup window to select from a list of valid companies.

Note: The source and recipient account balances and transactions are processed based on a unique company, account, and currency combination. By using filters and specifying multiple companies in the recipient accounts, having multiple source companies is possible. This unique company, account, and currency combination results in a separate detail transaction. Furthermore, if you are allocating a percentage of source values, Advanced Allocations allocates each unique source company, account, and currency balance.

Example 1:

Assume a percentage allocation. The following are source accounts to allocate:

Company Source Account Recipient Account

HOLDCO 6000-000-000-00 6000-100-000-00

6000-200-000-00

DDIST 6000-000-000-00 6000-100-000-00

6000-200-000-00

The source company is filtered and two recipient lines are used, as shown below.

Chapter 7 - Intercompany Processing

75

Advanced Allocations evaluates separate source balances to allocate from each of the two companies. It creates an allocation with two source accounts and four recipient accounts. Note that in this example, the source account and recipient account are to the same company.

Example 2:

Assume the same facts as the previous example except now there is only one source company and a different recipient company:

Company Source Account Co Recipient

HOLDCO 6000-000-000-00 DDIST 6000-100-000-00

DDIST 6000-200-000-00

In this example, you would not use filters, but specify the source company as HOLDCO and the recipient ranges as follows:

One source transaction detail line is generated with HOLDCO as the source company and two recipient account transaction detail lines are generated with DDIST as the recipient company.

79

Index A

Account Values ...................................................... 22

Accounts

Rounding ............................................................. 10

Accounts .................................................................. 10

Accounts Formula Type ........................................ 18

Advanced Options ................................................. 61

Allocation

Types ...................................................................... 7

Allocation .................................................................. 2

Allocation .................................................................. 7

Allocations User Guide .......................................... 1

Allocations Using Amounts Method

Calculating .......................................................... 64

Allocations Using Amounts Method ................... 64

Amount Allocations

Defining ................................................................. 7

Amount Allocations ................................................. 7

Amount Allocations ............................................... 41

Amount Allocations Are Processed ................. 8, 46

Amount Transactions ............................................ 68

Amt Trans ............................................................... 68

Applying

Global .................................................................. 17

Applying ................................................................. 17

Around

Getting ................................................................... 2

Around ...................................................................... 2

B

Balance Types#IX_budget .................................... 20

Based On Amounts ................................................ 70

Based On Percentages ........................................... 71

C

Calculating

Allocations Using Amounts Method .............. 64

Calculating ............................................................. 64

Chapter 1#Introducing_Allocations ..................... 1

Chapter 2#Basic_Concepts ..................................... 7

Chapter 3#Chapter_3 ............................................ 15

Chapter 4#Chapter_4 ............................................ 27

Chapter 5#Setting_up_Allocations ..................... 41

Chapter 6#Chapter_6 ............................................ 67

Chapter 7#Chapter_8 ............................................ 73

Companies

Rounding ............................................................ 74

Companies .......................................................... 3, 20

Companies .............................................................. 74

Completing

PS-Acct ................................................................ 23

Published Data Retrieval Formula Line ......... 24

Completing ............................................................. 23

Completing ............................................................. 24

Compute Totals

Using Temporary Accounts ............................. 66

Compute Totals ..................................................... 66

Constants Formula Type ...................................... 22

Conventions Used ................................................... 1

Creating

Formula Lines#IX_check_formula .................. 18

Local Formula .................................................... 16

New Global Formula#IX_including_previous.......................................................................... 15

Creating .................................................................. 15


80

Creating ................................................................... 16

Creating ................................................................... 18

Currency#IX_home_or_natural ........................... 21

Current Column

Relative ................................................................ 19

Current Column ..................................................... 19

Cursor Movement .................................................... 2

D

Database Queries ................................................... 28

Date Processing ...................................................... 37

Defining

Amount Allocation .............................................. 7

Percentage Allocation#IX_Allocation_Batches 8

Defining ..................................................................... 7

Defining ..................................................................... 8

Definitions ................................................................. 7

Dialog Boxes ............................................................. 2

E

Editing

Global Formula ................................................... 17

Editing ..................................................................... 17

ERP Configurations ............................................... 25

F

Filters ......................................... 12, 47, 57, 58, 59, 61

Fixed Periods .......................................................... 19

Form Maintenance ................................................... 2

Forms ......................................................................... 2

Formula Lines#IX_check_formula

Creating ............................................................... 18

Formula Lines#IX_check_formula....................... 18

Formula Maintenance ............................................ 15

FormulaDefinition

Printing ............................................................... 25

FormulaDefinition ................................................. 25

Formulas .............................. 15, 28, 33, 47, 65, 67, 73

Formulas1 ............................................................... 57

G

Getting

Around .................................................................. 2

Getting ...................................................................... 2

Global

Applying ............................................................. 17

Global ...................................................................... 17

Global Formula

Editing ................................................................. 17

Global Formula ...................................................... 17

Global Formulaor Vice Versa ............................... 17

Global vs ................................................................. 15

Guide ..................................................................... 1, 6

I

Information

Sources .................................................................. 1

Information .............................................................. 1

Intercompany Transactions .................................. 73

Introduction ............................................................. 7

L

Linked Server Queries .......................................... 33

Local Formula

Creating .............................................................. 16

Saving .................................................................. 17

Local Formula ........................................................ 15

Local Formula ........................................................ 16

Local Formula ........................................................ 17

Local Formula ........................................................ 17

Index

81

Lookup Windows .................................................... 4

M

Maximum Allocated Amount .............................. 10

Microsoft Access Data Source Definition ............ 30

Microsoft Excel Data Source Definition .............. 31

Microsoft SQL Server Data Source Definition .... 29

Multicompany Processing .................................... 73

N

New Global Formula#IX_including_previous

Creating ............................................................... 15

New Global Formula#IX_including_previous ... 15

O

ODBC Data Source Definition .............................. 32

Offset Accounts ...................................................... 10

Offset Reference Codes ......................................... 68

Offset Reference Codes Percent Trans ................ 69

OLE-DB Data Source Definition........................... 32

Online Help#IX_Glossary_of_Terms .................... 5

Other Options

Percentage Allocations ...................................... 10

Other Options ......................................................... 10

Overview ................................................................. 67

Overview Ch ........................................................... 73

P

Percentage Allocation#IX_Allocation_Batches

Defining ................................................................. 8

Percentage Allocation#IX_Allocation_Batches .... 8

Percentage Allocations

Other Options ..................................................... 10

Percentage Allocations .......................................... 10

Percentage Allocations .......................................... 48

Percentage Allocations Are Processed ............ 9, 56

Percentage Transactions ....................................... 68

Periods .................................................................... 19

Plan Sheet Values .................................................. 65

Plan Sheet Values Formula Type......................... 22

Prerequisite Knowledge#IX_syntax ..................... 1

Printing

FormulaDefinition ............................................. 25

Printing ................................................................... 25

Processing

PS-Acct ................................................................ 23

Published Data Retrieval Formula Lines ........ 24

Reference Code Transactions ........................... 70

Steps .................................................................... 13

Processing ............................................................... 13

Processing ............................................................... 23

Processing ............................................................... 24

Processing ............................................................... 70

ProcessingSegment Ranges .................................. 11

PS-Acct

Completing ......................................................... 23

Processing ........................................................... 23

PS-Acct .................................................................... 23

PS-Acct .................................................................... 23

PS-Row ID Formula Lines .................................... 23

Published Data Retrieval Formula Lines

Completing ......................................................... 24

Processing ........................................................... 24

Published Data Retrieval Formula Lines............ 24

Published Data Retrieval Formula Lines............ 24

R

Recipient Companies ............................................ 74

Recipient Reference Codes ................................... 68


82

Record Lookups ....................................................... 3

Record Stamp Display ............................................. 3

Reference Code Transactions

Processing ........................................................... 70

Reference Code Transactions ............................... 70

Reference Codes

Rounding ............................................................. 69

Reference Codes ......................................... 22, 67, 68

Reference Codes ..................................................... 69

Relative

Current Column ................................................. 19

Relative .................................................................... 19

Right Mouse Click .................................................... 2

Rounding

Accounts .............................................................. 10

Companies .......................................................... 74

Reference Codes ................................................. 69

Rounding................................................................. 10

Rounding................................................................. 59

Rounding................................................................. 69

Rounding................................................................. 74

S

Saving

Local Formula ..................................................... 17

Saving ...................................................................... 17

Skip Invalid Filtered Source Accounts ................ 60

Source Accounts ..................................................... 58

Source Company .................................................... 73

Source Pools ............................................................ 61

Source Reference Codes ........................................ 69

Sources

Information ........................................................... 1

Sources ...................................................................... 1

Spin Buttons ............................................................. 2

SQL Variables ........................................................ 36

Step-by-Step Instructions Amounts#IX_type .... 41

Step-by-Step Instructions Percentages#Source_Valuation_Currency ..... 48

Step-by-Step Instructions Transactions .............. 63

Steps

Processing ........................................................... 13

Steps ........................................................................ 13

T

Tabbed Forms .......................................................... 2

Table Forms .............................................................. 3

Technical Support.................................................... 5

Transaction Allocations ........................................ 11

Types

Allocations ............................................................ 7

Types ......................................................................... 7

U

User ........................................................................... 3

Using ......................................................................... 6

Using Temporary Accounts

Compute Totals ................................................. 66

Using Temporary Accounts ................................. 66

Using User-Defined Query Parameters .............. 38

V

Valuations#IX_ending_balance_1 ....................... 20

Variables ................................................................. 12

W

Windows .................................................................. 2

Y

Year ......................................................................... 19