Skip to main content

Report - Account Mapping

Updated

Introduction

This article discusses Account Mapping and how to use the report to identify missing elements or inconsistencies within the Accounting module for AUS/NZ clients. The report offers an overview of all ledgers, chart of accounts, and mappings, detailing which chart of accounts is associated with each product, payment plan, and facility booking definition.

Within this report, managers and staff can make bulk changes to chart of accounts, mapping associated with products, payment plans and facility booking definitions. 

Before you start 

To access the report, go to the PGM > Reports > All

Find the Account Mapping report by either using the search filter, or selecting the Custom tab. 

Press XLSX to generate the report.

If editing any tabs within this workbook, to make changes to your financial accounts within the software, do not change the structure of the excel. It is in a particular format for the Perfect Gym team to be able to complete bulk changes. This includes names of headings, tabs, and filters.

Instruction

All your current accounts and ledgers are listed under the Accounts & Ledgers tab. Be sure to note the AccountId, as it uniquely identifies each account. The ledger of a migrated account is determined by the ledger/category assigned to that account in the source database.

Here's a summary of the actions you can take if you use this report as a template for bulk changes to account mapping.

We can perform the following bulk actions: mappings of products, payment plans, and facility booking definitions.

  1.  Create: new mapping between items and existing accounts, and/or
  2.  Delete: existing mappings between items and existing accounts, and/or
  3.  Re-assign: a mapped item to a different account, and/or
  4.  Change split: percentage of an existing mapping

When completing the NewAccountId field for bulk mapping, carefully select the account to ensure accurate mapping into the correct ledger.

Instruction / Feature description:

  • Accounts and Ledgers
  • Transaction Types
  • Payment Plans
  • Products
  • Facility Booking
  • Over or Under Mapping Types (Transactions types)
  • Over or Under Mapping Items
  • Bulk Create Accounts
  • Bulk Create Key

Accounts & Ledgers Tab:

This tab provides an overview of the ledgers and chart of accounts within your software.  This also displays the number of products, payment plans, facility mappings, and transaction types that are mapped to each chart of account.

  • LedgerId – unique identifier of existing ledger
  • Ledger – the name of the ledger
  • AccountId - unique identifier of existing account
  • Account number – unique identifier of the chart of account (generally numerical)
  • Account name - unique name of the chart of account
  • ProductMappings – The number of products mapped to each account
  • PlanMappings - The number of payment plans mapped to each account
  • FacilityMappings - The number of facility booking definitions mapped to each account
  • TransactionTypeMappings - The number of transaction types mapped to each transaction type account

Products Tab:

This tab provides an overview of the ledgers and chart of accounts attached to the products within your software. Blank "AccountId" next to products indicate there is no mapping currently associated with the product.

If you want to assign a given unmapped product to an account, you can follow these steps:

  1. Locate the desired account on the Accounts & Ledgers tab
  2. Copy the value from the AccountId column
  3. Paste this value into the NewAccountId column on the Products tab

You can of course fill in these values using any technique in excel, but bear in mind that any value that appears in the NewAccountId column must be a valid AccountId, as reported on the first tab.

You may wish to copy and paste not just the Id, but also the AccountName and AccountNumber into the NewAccountName and NewAccountNumber fields, which exist for your convenience.
Bear in mind that these columns will be ignored in the upload, which only requires the ID's. 

  • Product Id – unique identifier of existing product
  • Product Name – List of active products
  • LedgerId – unique identifier of existing ledger
  • Ledger – the name of the ledger
  • MappingId -
  • AccountId - unique identifier of chart of account attached to product (derived from Accounts & Ledgers tab)
  • Account number – unique identifier of chart of account attached to product (derived from Accounts & Ledgers tab)
  • Account name - unique name of chart of account attached to product (derived from Accounts & Ledgers tab)
  • Split – Percentage of sale to be allocated to this chart of account (default is 100%)
  • NewAccountId – Blank column (only to be filled in if wanting to edit an existing product to a different chart of account or when mapping the product for the first time)
  • NewAccountName – Blank column (only to be filled in if wanting to edit an existing product to a different chart of account or when mapping the product for the first time)
  • NewAccountNumber – Blank column (only to be filled in if wanting to edit an existing product to a different chart of account or when mapping the product for the first time)
  • NewSplit – Blank column (only to be filled in if wanting to edit an existing product to have the income split between multiple chart of accounts)
  • IsUpdate – Blank column (only to be filled in when editing an existing product to a different chart of account)
  • ToBeDeleted – Blank column (only to be filled in when inactivating a product)

Adding chart of accounts to products (mapping):

  1. Go to the Products tab
  2. Filter AccountId column to blanks only. This will bring up all products that do NOT have any mapping attached
  3. Enter the account id into the column NewAccountId (you can find this in the AccountId column of the Accounts & Ledgers tab)
  4. Repeat the above steps for all products with no mapping associated

Editing chart of accounts to products (mapping):

  1. Go to the Products tab
  2. Filter AccountId column to everything except blanks. This will bring up all products that have mapping attached
  3. Enter the different account id into the column NewAccountId (you can find this in the AccountId column of the Accounts & Ledgers tab)
  4. Enter a 1 in the column IsUpdate
  5. Repeat the above steps for all products that are to have a different chart of account

Inactivating a product (no longer sold in club):

  1. Go to the Products tab
  2. Enter a 1 in the column ToBeDeleted
  3. Repeat the above steps for all products that are to be inactivated

Creating products with splits:

  1. Go to the Products tab
  2. Enter a number between 0 and 100 in the column NewSplit
  3. Duplicate the row and enter a number between 0 and 100 in the column NewSplit
  4. Check to make sure the product totals 100% between the newly made splits/rows
  5. Repeat the above steps for any products that you want the income to be split between accounts

Hint: The columns NewAccountName and New AccountNumber will be ignored in the upload, as the upload only requires the NewAccountId to be populated.

Payment Plans Tab:

This tab provides an overview of the ledgers and chart of accounts attached to the payment plans within your software. Blank rows next to payment plans indicate there is no mapping currently associated with the payment plan.

  • PaymentPlanId – unique identifier of existing payment plan
  • Payment plan – List of active payment plans
  • LedgerId – unique identifier of the existing ledger
  • Ledger – the name of the ledger
  • MappingId -
  • AccountId - unique identifier of chart of account attached to payment plan (derived from Accounts & Ledgers tab)
  • Account number – unique identifier of chart of account attached to payment plan (derived from Accounts & Ledgers tab)
  • Account name - unique name of chart of account attached to payment plan (derived from Accounts & Ledgers tab)
  • Split – Percentage of sale to be allocated to this chart of account (default is 100%)
  • NewAccountId – Blank column (only to be filled in if wanting to edit an existing payment plan to a different chart of account or when mapping the payment plan for the first time)
  • NewAccountName – Blank column (only to be filled in if wanting to edit an existing payment plan to a different chart of account or when mapping the payment plan for the first time)
  • NewAccountNumber – Blank column (only to be filled in if wanting to edit an existing payment plan to a different chart of account or when mapping the payment plan for the first time)
  • NewSplit – Blank column (only to be filled in if wanting to edit an existing payment plan to have the income split between multiple chart of accounts)
  • IsUpdate – Blank column (only to be filled in when editing an existing payment plan to a different chart of account)
  • ToBeDeleted – Blank column (only to be filled in when inactivating a payment plan)

Follow the instructions under Products for adding/editing/inactivating/splitting payment plans. 

Facility Bookings Tab:

This tab provides an overview of the ledgers and chart of accounts attached to the facility booking definitions within your software. Blank rows next to facility booking definitions indicate there is no mapping currently associated with the facility booking definition.

  • FacilityBookingDefinitionId – unique identifier of existing facility booking definition
  • FacilityBookingDefinition – List of active facility booking definitions
  • LedgerId – unique identifier of existing ledger
  • Ledger – the name of the ledger
  • MappingId -
  • AccountId - unique identifier of chart of account attached to facility booking definition (derived from Accounts & Ledgers tab)
  • Account number – unique identifier of chart of account attached to facility booking definition (derived from Accounts & Ledgers tab)
  • Account name - unique name of chart of account attached to facility booking definition (derived from Accounts & Ledgers tab)
  • Split – Percentage of sale to be allocated to this chart of account (default is 100%)
  • NewAccountId – Blank column (only to be filled in if wanting to edit an existing facility booking definition to a different chart of account or when mapping the facility booking definition for the first time)
  • NewAccountName – Blank column (only to be filled in if wanting to edit an existing facility booking definition to a different chart of account or when mapping the facility booking definition for the first time)
  • NewAccountNumber – Blank column (only to be filled in if wanting to edit an existing facility booking definition to a different chart of account or when mapping the facility booking definition for the first time)
  • NewSplit – Blank column (only to be filled in if wanting to edit an existing facility booking definition to have the income split between multiple chart of accounts)
  • IsUpdate – Blank column (only to be filled in when editing an existing facility booking definition to a different chart of account)
  • ToBeDeleted – Blank column (only to be filled in when inactivating a facility booking definition)

Follow the instructions under Products for adding/editing/inactivating/splitting facility bookings. 

Transactions Types Tab:

This tab provides an overview of the ledgers and chart of accounts attached to the transaction types within your software.  You can change the chart of accounts attached to transaction types in the software.

  • LedgerId – unique identifier of existing ledger
  • Ledger – the name of the ledger
  • Transaction type – transaction type in each ledger
  • MappingId –
  • Split – Percentage of sale to be allocated to this chart of account (default is 100%)
  • AccountId - unique identifier of chart of account attached to transaction type (derived from Accounts & Ledgers tab)
  • Account number – unique identifier of chart of account attached to transaction type (derived from Accounts & Ledgers tab)
  • Account name - unique name of chart of account attached to transaction type (derived from Accounts & Ledgers tab)

Editing chart of accounts to transaction types (mapping):

  1. Open PGM > Accounting > Chart of accounts
  2. In Name field, type the transaction type (E.g. AdminFee)
  3. Select Edit, and change the name and/or number
  4. Select Save

Over or Under-Mapped Types Tab:

This tab provides an overview of any products, payment plans, facility bookings that have mapping (chart of accounts) that does not equal 100% in the software.  If any items do not match 100%, they need to be edited in PGM > Accounting > mapping to total 100%.

Bulk Create Accounts Tab:

This tab generates a blank worksheet. This tab is where you can create new charts of accounts. The Bulk Create Account Key tab provides instructions on what each column means.

  1. Locate Bulk Create Accounts tab.
  2. Enter Account number and Account name into the correct columns.
  3. If you want accounts created into a new ledger, leave LedgerId blank, and specify the new ledger name in LedgerName

  4. If you want accounts created into existing ledgers, fill LedgerId and LedgerName with existing values (you can find these in the LedgerId column of the Accounts & Ledgers tab)

  5. Confirm that the data entered fits within the limits specified on the Bulk Create Accounts Key tab and make sure you read the Meaning column to be sure you've used the correct format

  6. Raise a case via Get Support module and upload file to be generated

Hint: All accounts must exist in a ledger, so you must either specify a LedgerName (if creating new ledgers), or LedgerId (if creating accounts in existing ledgers). If LedgerId is specified, the LedgerName field will be disregarded by the system, so feel free to copy across the ledger name from Accounts & Ledgers tab, if it helps

Bulk Edit Accounts Tab:

This tab generates the list of all the chart of accounts.  This tab is where you can edit charts of accounts numbers and names. The Bulk Edit Accounts Key tab provides instructions on what each column means.  

  1. Locate "Bulk Edit Accounts" tab
  2. If you want to delete an account, put a 1 under ToBeDeleted; the account will be removed, and all other columns will be ignored
  3. To edit an account, put a 1 in the column IsUpdate (E.g. to change the account number or rename the account) and fill in the NewAccountNumber and/or NewAccountName
  4. Confirm that the data entered fits within the limits specified on the Bulk Edit Accounts Key tab data and make sure you read the Meaning column to be sure you've used the correct format
  5. Raise a case via Get Support module and upload file to be generated

Hint: Rows that do not have a 1 in either columns; ToBeDeleted or IsUpdate, will remain the same.  Deleting an account will automatically remove all mappings of items associated with the deleted accounts.  To move an account from one ledger to another, you need to use a combination of bulk edit (delete) and bulk create (create within different ledger).

Hints:

  • Any additional fields created in excel will not affect the outcome, but you might find it helpful to add other information to the sheet for your own purposes
  • Any highlighting or formatting applied to the sheet will be disregarded

  • Here's a brief guide to each action, using products as the example:

  1. Create new mapping: Add a new line to the Products tab with IsUpdate set to 0 or left blank, and include at least ProductId and NewAccountId. (ProductId and NewAccountId must already exist; you can find them on the Products and Accounts & Ledgers tabs, respectively). You may add other fields for visual aid, such as NewAccountName, but these will be ignored by the system. If not specified, NewSplit will default to 100. Note: All accounts already exist within a ledger, so you don't need to specify the ledger here.
  2. Delete existing mapping: Enter a 1 in the ToBeDeleted column on the Products tab. If ToBeDeleted is 1, all other fields will be ignored.
  3. Re-assign existing mapping to a different account: Enter a 1 in the IsUpdate field on the Products tab and fill the NewAccountId column with an ID from the Accounts & Ledgers tab.
  4. Change percent split of an existing mapping: Enter a 1 in the IsUpdate field on the Products tab and specify an integer between 1 and 100 in the NewSplit field. Note that the total percent split should not exceed 100 for a given item in a given ledger. For example, a 50-50 split between two accounts in the same ledger.
  • If you want to delete a ledger, use the user interface (typically, there are tens to hundreds of accounts grouped into only a handful of ledgers).
  • You can generate this report again at any time, which may be useful for keeping track of your mappings as new items are added.

Related articles