1. Modeling
  2. Build Models
  3. Lists and Roll-ups
  4. Numbered Lists
  5. Examples and Numbered Lists

The following examples show you how numbered lists can be used effectively in many different areas of a business resulting in less memory usage, reduced sparsity, faster model calculation and increased usability.

Note: The hash character prefix to the name of a numbered list is a convention to allow numbered lists to be quickly identified — Good Practice: Naming Conventions provides further information.

Project planning

#Resources>#Projects>Organizational Hierarchy

Numbered lists allow for a person to appear several times in a single list, once for each project they are working on. They may be working on more than one project, or several projects in the course of a year.

Typically, a composite hierarchy would be used. In the example, #Resources is a numbered list with a parent hierarchy of #Projects, which is, in itself, another numbered list. #Projects has a parent hierarchy of Organization.

In the example above, note that display names are repeated within the numbered lists, even though the hidden ID of each job is different.

Sales performance planning

#Customer Accounts>Reps

Suppose you need to assign customer accounts or territories to sales reps, but the same account can be assigned to more than one sales rep. Using a numbered list with sales reps as the parent hierarchy, you can repeat the same account many times in a list. Only the valid combinations of customer account and Rep are contained in the numbered list. This helps remove sparsity making the model more usable, run faster and use less memory.

In this example, display names can be repeated within the same subtotals. For more information about display names, see Working with Numbered Lists.

Product Sales Planning

#Products or Services>Reps>Organization

If you have thousands of products and services but each profit center is selling a small subset of the products and services you offer, a numbered list of selected products/services rolling up into sales reps lets you assign the correct products to each rep.

Sparsity - Customer by Product


Suppose you have 2,000 customers and 2,000 products — if you have these as dimensions in a module, that's four million cells before you've even added the other dimensions. However, if each customer bought a handful of products, you could take advantage of this sparsity and merge the products and customers into a single list. If, on average, you sell five products per customer, this will cut relative module size down from four million cells to just 12,000 (three percent of its original size).

Promotions Planning

#Products>#Promotional Activities>Organization

You can assign products to promotional activities. Each activity relates to a limited set of products, though occasionally a promotion affects several hundred products.

CapEx Planning

#Itemized Assets>Organization

A numbered list could be used to plan fixed asset purchases, particularly in the case of individual large-sum expenditures where each item needs to be listed separately. Import using asset codes from your fixed asset register.

Budget adjustments or journals

#Journal Number>Organization

Each cost-center requires several budget adjustments throughout the year, entered as a journal with the balance sheet or P&L account affected, the offset account, the amount, details and month. The number of journals varies between organizational units. A numbered list allows you to enter any number of adjustments per cost-center, but with zero sparsity. Each entity can have the journals set independently, so rows can be added, deleted or moved without affecting other organizational entities.

Transactional data

#Numbers>Normal Hierarchy

Without numbered lists, you might see lists with numbers 1 to 1,000 as one dimension and a normal hierarchy as another dimension. Typically, this would be for transactional data or for itemized changes such as journal or budget adjustments. The problem with this method is that you end up creating 1000+ items in the list to cater for the worst case. If only one of your organizational units required 1000 adjustments, then the list size would be set to 1000, even though most organizational units required two or three adjustments. This creates massive sparsity and wasteful memory usage.

If you delete or re-order the transactions for a single hierarchy item, it affects all the other items in the hierarchy too. With numbered lists, each transaction is independent and you end up with zero sparsity. The model will calculate faster, use less memory and be much more usable.