Accounts Payable Allocation for Property Management

DS0444

Property Management AP Allocations

Problem Definition

ACME Co. is a property management company. Bills received by the management company need to be allocated in different ways to the properties they manage. An existing software package that handles this resides outside of GP, and needs to be brought inside the software.

The properties have a hierarchical structure linking groups of them together. The relationship is:

  • Federation
    • Association
      • Condo Corporation
        • Buildings

At the bottom level, the Buildings, the system tracks the number of units within each building. Most of the allocations are calculated using a ratio of the number of units contained within some level of the entity-relationship, to a total of units. For example, if a Federation contains three Associations, an expense could be allocated to each Association by looking at the number of units within each Association vs. the total number of units in the Federation.

Design Features

Setup

The Setup window is used to specify which segment of the GL account structure will be swapped by the AP Allocations app.

Navigation: Tools >> Setup >> Purchasing >> Allocations

Field Function
Profit Center Segment User enters or selects from the Lookup the segment.

Allocation Maintenance – Option 1

Allocation Maintenance is used to create/maintain the allocation structure. The allocations are defined by a series of 1-to-many relationships as follows:

  • Federation
    • Association
      • Condo Corporation
        • Building

Cards >> Purchasing >> Allocation Maintenance

When a Federation ID is entered, the Associations scrolling window will fill automatically. The remaining windows will be empty until further selections are made.

The remaining windows display based on a selection from another window. As shown above the “NA” association is marked (the “>” indicates “marked”), which causes the Condo Corporations window to fill with the Corporations under the NA association.

Selecting the “NA” condo corporation causes the Buildings scroll to fill with the buildings which are under the “NA” condo corp. The Association and Condo fields to the left of this window show the selected Association and Condo.

Field Function
Federation ID User enters a federation ID or selects one from the Lookup. 20-character, upper case.
Name Editable. 40-character.
Associations Scroll
Association ID User enters the Association ID. 20-character, upper case.
Name Association name. 40-characters.
Segment User selects the profit center segment. The values allowed in this field/displayed in the Lookup are controlled by the Segment selected in Setup.
Federation % The Federation % calculates automatically, and is not editable. This is a ratio of the number of Units beneath the Association divided by the total number of units in the Federation.
Condo Corporations Scroll
Condo Corp User enters the condo corp ID. 20-character, upper case.
Name Condo Corp Name. 40-characters.
Valuation Valuation of the Condo Corp. This value is used when performing an Allocation By Valuation. See below for descriptions of the different allocation methods.
Federation % The Federation % calculates automatically, and is not editable. This is a ratio of the number of Units beneath the Condo Corp divided by the total number of units in the Federation.
Buildings Scroll
Association ID This display field to the left of the Buildings Scroll shows which Association ID was selected.
Condo Corp This display field to the left of the Buildings Scroll shows which Condo Corp was selected.
Building Build Number (or description). For example, 2713 or, 1101-1108 & 1201-1212. 30-characters, upper case.
Unit Count Number of doors (units) in the building. Most of the allocation methods (described below) are based on the Unit Count.
Federation % The Federation % calculates automatically, and is not editable. This is a ratio of the number of Units within the Building divided by the total number of units in the Federation.
Totals
The totals in the bottom right display various totals for the Federation.

The “v” buttons are “show detail” buttons which expand the scrolling window lines. The expanded window will show totals for the line from the “sub” windows. For example, expanding the line for an Association will show the following totals for the Condo Corps and Buildings beneath the selected Assocation:

  • Total Condo Corps
  • Total Valuation
  • Total Building Count
  • Total Unit Count

Likewise, expanding the line for a Condo Corp will show the following totals for the Buildings beneath the Condo Corp.

  • Total Building count
  • Total unit count

If entering information for a new Federation, the data entry process would be as follows:

  • Enter the Federation ID and Name.
  • Enter all of the Associations, and the information needed for each Association.
  • Select the first Association by clicking into the scrolling window line:
    • Then click into the Condo Corps scrolling window.
    • Enter one or more Condo Corps “linked” to the selected Association.
    • Select the first Condo Corp line:
      • Then click into the Buildings scroll.
      • Enter one or more buildings, and the information for each building.
  • Repeat the process by clicking into another Association.

A line in any of the scrolling windows can be deleted by selecting Edit >> Delete Row from the GP Menu Bar. If deleting a row from the Associations or Condos, all records beneath that level will also be deleted. For example, if deleting the “NA” association shown above, the “NA” Condo Corp and all Buildings would also be deleted. Before allowing a user to delete a row the system will:

  1. Present a warning message explaining that a number of records could be deleted from the Condo Corp and Buildings. For example: Deleting an Association will delete all Condos and Buildings beneath the Association. Continue? The user can click YES or NO.

Similar warnings will occur as the Condo level, and the Federation Level. Individual buildings can be deleted via Edit >> Delete Row without receiving a warning.

The different allocation methods will be calculated as follows:

  • Unit – allocates a percentage of the amount to each Association in the system based on a ratio of units within each Association to the total number of units in the system. $Amount * (ttl Assn Units / ttl Units for all Federations).
  • Federation – allocates the amount to the Associations within the selected Federation, based on a ratio of the units within each Association to the total number of units in the Federation. $Amount * (ttl Assn Units / ttl Federation Units).
  • Association – allocates 100% to the selected Association.
  • Reserve – works the same as Association, but Reserve Cash Accounts are used to pay instead of cash. The user must select the default Reserve Accounts to use on the AP Distributions window prior to performing the Allocation (see next section).
  • Valuation – allocates to all Associations in the system based on valuation. $Amount * (Condo Corp Valuation / Total Valuation of all Condo Corps).

With the exception of Valuation Allocation, all of the above methods allocate based on ratios calculated using the Buildings table. The Buildings table will be similar to this example:

FedID AssnID CondoID BuildingID UnitCount
NA NA NA 2612 16
NA NA NA 2613 12
NA NA NA 2614 16
NA NA1 NA1 3212 16
ABACO ABACO ABACO A 1601 48
ABACO ABACO ABACO B 1602 40
ABACO ABACO ABACO C 1603 48
….

Example calculations using just the information above for a $1000 AP Tranasction.

Unit Allocation

Sum of Unit Count by Association:

  • NA = 16 + 12 + 16 = 44.
  • NA1 = 16
  • ABACO = 48 + 40 + 48 = 136
  • Total Units = 44 + 16 + 136 = 196
  • Allocation for NA = $1000 * (44/196)
  • Allocation for NA1 = $1000 * (16/196)
  • Allocation for ABACO = $1000 * (136/196)

Federation Allocation

Sum of Unit Count by Association within the selected Federation (NA).

  • NA = 16 + 12 + 16 = 44.
  • NA1 = 16
  • Total Units = 44 + 16 = 60
  • Allocation for NA = $1000 * (44/60)
  • Allocation for NA1 = $1000 * (16/60)

Association

$1000 allocated to the selected Association (i.e. NA).

Reserve

$1000 allocated to the selected Association (i.e. NA). User will have selected default reserve accounts to use for the allocation.

Valuation

The Valuations are stored in the Condo Corps table (not shown here). The Valuation Allocation is similar to a Unit Allocation in that the amount is allocated to every Association, but the ratios are calculated using the Valuation Amounts from the Condo Corps table.

Allocation Maintenance – Option 2

ACME Co. has emphasized that the entity relationships do not change. They have been the same for years, and are expected to be the same for the foreseeable future. Given that, the sophisticated Maintenance window proposed above may be overkill, or even not needed.

Since the primary objective is to facilitate automatically creating the allocations for an AP transaction inside GP, all that is really needed is to have the “allocation logic” presented in the next section (AP Allocations). That “logic” needs the underlying entity relationship data. If the data itself does not change often (or ever), the Maintenance window adds little or no value.

An alternate approach is to import the data (entity relationships, unit count, valuation, etc) from the existing system into the new GP tables created for this customization. The AP Allocations window (next section) will use that to create the allocations.

In the unlikely event that changes to the data are needed, those changes would need to be made from the “backend”, directly on the SQL Server.

Everything in the previous section pertaining to HOW the allocations will be created still apply to Option 2. What is removed is the ability to create new Federations, or change anything in an existing Federation from inside GP.

This estimate does not include time to migrate/import the data. We will provide documentation on the structure of the tables, and assistance in understanding what data is expected in the tables, however the actual task of importing and validating the correctness of the data will be Complete Business Solution’s responsibility.

AP Allocations

After entering an AP transaction (below), the user will select Additional >> AP Allocations.

The AP Allocations window will open:

Field Function
Allocation Method The allocation method selection will control whether the Federation and Association fields are enabled. Options will include:

  • Unit (Federation and Association disabled)
  • Federation (Federation enabled, Association disabled)
  • Association (Federation and Association enabled)
  • Reserve (Federation and Association enabled)
  • Valuation (Federation and Association disabled)
Federation The drop-down list will display all available Federations. When a selection is made the field to the right will display the Name of the federation.
Association The drop-down list will display all Associations within the Federation. When a selection is made the field to the right will display the Name of the association.
CREATE Will prompt the user to confirm they are ready to allocate the transaction. If so, the software will split the existing “default” purchases allocation (PURCH) and expense allocation (PAY). If there are other distributions (i.e. taxes), they will NOT be redistributed by the AP Allocation routine. Only amounts coded with the distribution type of PURCH and PAY will be allocated.

Since the app creates the allocations using the existing “default” distribution accounts, the user must have selected the correct accounts if the default accounts are not appropriate.

If necessary, the user can click DEFAULT on the Distributions window to delete the Allocations and rebuild the distributions using default accounts.

If no accounts are found in the Distributions table the user will see a warning and be required to select default distribution accounts.

AP Allocations

The segment of the account swapped to create the AP Allocations depends on Setup. If the 2nd segment is selected (2-xxx-130000-051) the second segment will be replaced, and a distribution created, for each entity within the selected Allocation Method.

For example, if allocating by Federation for NASSAU the 2-xxx-130000-051 account would result in three distributions:

  • 2-040-130000-051
  • 2-050-130000-051
  • 2-060-130000-051

The original distribution created by default by GP will be removed.