CertiPro

Menu
Contact Us
Home  »  Resources  »  Blog • Sage ERP   »   Analytical Accounting in Sage X3
Hero Background: 7 Reasons Why Magento eCommerce is the Best Choice

Blog / Analytical Accounting in Sage X3

BlogSage ERP

Analytical Accounting in Sage X3

July 13, 2021

What is Analytical Accounting in Sage X3?

In Sage X3, the term, analytical accounting refers to the use of analytical dimensions as part of the accounting structure. This allows users to record the details of financial transactions in greater detail than would be possible with just straight account numbers in a chart-of-accounts.

The term chart-of-accounts in Sage X3 also has a specific meaning that may differ somewhat from the use of that term in other accounting software. In Sage X3, a general ledger account is what would typically be referred to as the natural account in other accounting software. Most commonly, the Sage X3 G/L account consists of accounts identified by a fixed number of numeric digits. However, this is not a requirement of the X3 system. In Sage X3, a G/L account can consist of up to 15 characters, including a mixture of numeric, alpha, and limited special characters (for example, a “-“ dash character). In certain industries, long account numbers of up to nine digits may be more common, but normally, the G/L accounts are five or six digits in length.

In many other accounting software packages, the term G/L Account refers to a fully-qualified account number that may consist of a number of different components, G/L segments, or sub-accounts. For example, you may have a three-part accounting structure consisting of a five-digit natural G/L account, two-digit division code, and a three-digit department code. In many accounting software packages, this would be represented by a fully-qualified account number #####-##-### where the natural account comes first, the division 2nd, and the department code 3rd.

This sort of fixed fully-qualified account structure with a natural account and a set number of G/L segments may get unwieldy if you have numerous types of segments. Imagine a situation where you need to record accounting information by division, department, product line, and customer market segment. A fully-qualified account structure might have a format like #####-##-###-#####-### or something similar depending on the maximum digits assigned to each segment. Aside from being huge, the other problem is that many of these G/L segments are not relevant to every account.  For example, perhaps the product line segment is only needed for reporting on profit margin and applies only to your revenue and cost-of-goods accounts, yet it appears as a segment in every account, usually with a placeholder of zeroes or similar code to signify that it doesn’t apply on a particular account.

Another drawback is that the addition of a G/L segment, such as a department, requires the expansion of the fully-qualified account numbering as a multiplicity of possible combinations.  For example, you add a new product line and therefore need to create a new set of G/L accounts for revenue and expense which reference the segment.

Instead of using a fixed account format as is typical in other financial accounting systems, Sage X3 instead uses the concept of analytical dimensions to provide the same sort of sub-account functionality without the burdens of trying to incorporate these codes into your actual G/L account numbers.  In Sage X3, you define the chart-of-accounts, which consists of the natural account number of five digits, six digits, or whatever number of digits you feel are necessary to encompass the types of accounts needed for financial reporting.  Next, each of the segments, such as division, department, product line, and so forth are defined as analytical dimensions, known as analytical dimension types, or simply, as dimension types.

So, for example, the Department number is a dimension type whereas the actual department numbers that you assign are called the dimension codes or values or sometimes just dimensions as long as the context makes it clear whether you are referring to a dimension type or a dimension code itself.

So, let us imagine that you have set up your chart-of-accounts and assigned the accounts needed for all the various types of assets, liabilities, equity, sales, cost-of-sales, expense, and other income and expense.  You might have defined an account 62300 as Office Supplies expense.  You would not create a separate office supplies expense account for each department, just the single G/L account 62300, and allow the Department dimension to take care of breaking out the expense across the various departments that have these expenses.  So, in addition to defining the G/L account 62300 itself, you would tell the system that it uses the Department dimension to subdivide postings of these expenses by department.

Let us further imagine that, after using this accounting structure for over a year, you need to create a new department.  You have purchased your own delivery vehicles and now have expenses associated with fleet management.  You want to add a logistics department to track these expenses separately.  In Sage X3, all you need to do for this is create the new department code, and that’s it.  As soon as you have crated the new department code, you can specify it on any postings to any of the G/L accounts previously defined to use the Department dimension type.  This can help insulate the chart-of-accounts from changes that can be handled by dimension types, such as new divisions, new departments, new product lines, and so forth.  By proper use of analytical dimensions, the actual chart-of-accounts remains more compact and easier to manage, subject to less frequent changes, and the analytical dimensions provide for expansion of the attributes that you want to track and report on.

A Working Example of Analytical Accounting Structure:

Take the 1st example dimension type of Division as mentioned above.  We could assign an analytical dimension type DIV for recording the division on financial transactions as two numeric digits.  It could, in fact, be any 15-character combination, but most often is a simple numeric code, a division number.  You would then indicate to Sage X3 which natural accounts can utilize that dimension when posting financial transactions.  Let us further assume that the division dimension is going to be utilized to track the profitability of various operating divisions of the company, meaning that the Division dimension is only applicable to the P&L accounts used for statements of net income, but would not apply to balance sheet accounts, such as cash, receivables, and so forth.  What you would do then is indicate that the Division dimension is used on the P&L accounts and not used on the balance sheet accounts.

You would use a similar design technique for all other dimensions needed to track the other attributes of financial transactions in the ledger.  Perhaps, analytical dimensions for Department, Product Line, Customer Group.  In each case, you would assign a dimension type code, which is a short 3-character mnemonic to represent the dimension type, and a list of valid dimension code values, such as the list of division code, the list of department codes, and so forth.  You might then settle on a structure as follows:

  • G/L Account: five-digit numeric.  In the US, accounts are generally groups by their leading digit into (1) assets, (2) liabilities, (3) equity, (4) revenue, (5) cost-of-sales, (6,7,8) expenses, and (9) other income and expense.  Of course, many sub-groupings of G/L accounts are possible.  In many cases, when migrating to Sage X3 from another accounting package, the existing chart-of-accounts can be utilized as-is as a starting point.

  • Division (DIV) is a two-digit numeric code representing each operating division. Perhaps, these divisions are physical locations of various operations for which profitability needs to be tracked.  For example, 10=New York, 20=Los Angeles, 30=Chicago, 40=Seattle, and so forth.

  • Department (DPT) is a three-digit numeric code representing departments by functional area, such as Accounting, Marketing, Administration, Sales, Information Technology, and so forth.

  • Product Line (PRD) is a five-digit code that corresponds to various product categories assigned to inventory to facilitate margin reporting by those product lines. Perhaps, rather than a straight numeric code, it is decided that the product line dimension will be an alpha code that is an abbreviation or mnemonic for the product line, such as “RM” for raw materials, “SFG” for semi-finished goods, “TOY” for sale of toys, “GARD” for sale of gardening supplies, etc.

  • Customer Market Segment (MKT) is a five-digit code that corresponds to the customer categories. In this scenario, we have divided up the customer base into various customer groups based on the nature of their business.  Perhaps codes such as “RET” for retail, “WHSL” for wholesale, “MFG” for manufacturing, etc.

There is nothing intrinsic to Sage X3 in having divisions, departments, or product lines.  None of the examples above are some sort of hard-wired or built-in structure in Sage X3.  The choice of dimensions and codes that you implement in Sage X3 is entirely up to you.  You decide what dimension types and dimension codes you need and how to use them in financial reporting.

Notice in the example above that the Product Line and Customer Market Segment are not fixed-size numeric values.  It is possible to define a dimension type to be either alpha, numeric, or both, varying in length, and having embedded “-“ dash characters.  It is possible to have other special characters, but that is discouraged as it can easily get unwieldy in practice.

The next step in our example, is to identify to which G/L accounts to these dimension types apply?  So let’s take a stab at that:

  • Division: We want to report comprehensive profitability by division.  So, in the case of division, we assign this dimension type to all P&L accounts: revenue, cost-of-sales, and expense.  Let us assume further that certain asset accounts are assigned to particular divisions, such as fixed-assets and capital leases.  We would then also assign the division dimension to those balance sheet accounts

  • Department: We will be using department as a means of budgeting and tracking expenses, so we assign this dimension type to just the expense accounts.

  • Product Line: This dimension is for reporting sales margin on various groups of products.  The accounts involved in sales margin, sales and cost-of-sales, will be assigned this dimension type.

  • Customer Market Segment: Let us assume that this dimension type is only required for tracking sales, and so we assign it to just the sales accounts.

Now consider how a sale on account transaction would be posted utilizing the above accounting structure of accounts and dimension types.

  • The debit for the sale would go to the A/R account for the customer with no dimensions specified.

  •  The credit to sales would go to the Sales account along with:
    • Division dimension depending on which division gets credit for the sale.
    • Product Line dimension depending on the product sold,
    • Customer Market Segment depending on the customer to whom the sale was made.
    • No Department dimension would be specified.
  • The credit posting to the inventory account for the cost of the product shipped with no dimensions specified.
    • The debit posting to cost-of-goods sold for the cost of the product shipped:
    • Division dimension depending on the division getting credit for the sale.
    • Product line dimension depending on the product that was shipped.
    • No dimension specified for Customer Market Segment.
    • No dimension specified for Department.

Mandatory versus Optional:

When you define an analytical dimension type, you can indicate whether that particular dimension type is mandatory or optional.  This is controlled at the company level.  If you have a multi-company organizational structure, each company can have its own set of dimensions, and the same dimension type may be mandatory in one company yet optional in another.

If a dimension type is specified as mandatory, then any time you are entering a financial transaction to the ledger that is posting to an account that is set up to use that dimension type, for example, the office supplies account with the department dimension, then the system will require the user to specify a department dimension when entering the transaction.

The assignment of dimension types, especially mandatory dimension types, requires some careful thought concerning the purpose of the account and the suitability of the dimension types assigned to it.  Once you assign a mandatory dimension type to a G/L account, the system will not permit you to post to that account without assigning a dimension value, such as department, on the posting line.  If there will be situations where amounts need to be posted into that account where the dimension value is not known, or even where a department assignment may not be meaningful, you may want to provide for a catch-all or default dimension value.  For example, if your department codes are all two-digit numbers, then perhaps, the department “00” is reserved to mean unassigned or corporate-wide.

Accounts versus dimensions:

When you are designing your accounting structure of accounts and dimensions in Sage X3, you may find that the choice of creating analytical dimensions or separate G/L accounts is not so obvious.  There really is no hard-and-fast rule about which method to use for breaking out different types of postings for financial reporting.  However, there are some general guidelines or rules-of-thumbs that can help:

  • Focus on the P&L Accounts:  As a general rule, analytical dimensions are more useful to assign to income statement accounts.  Notice in the example above, all of the analytical dimension types were focused on income statement, profit margin, or expense reporting.  Only in the case of division were some balance sheet accounts involved, and only then because fixed assets and capital leases were assigned to specific divisions, so this information would be useful.  However, we did not assign any dimensions to the cash accounts because cash and cash equivalents are not assigned to each division.  That is, it is not meaningful to ask which division does a particular bank account “belong” to.  So, the rule of thumb is to focus on the P&L accounts for use of dimensions and keep the use of dimensions on balance sheet accounts fairly limited.
  • Consider Organizational Changes:  Using dimensions is more fruitful when the attribute being tracked is both numerous and tends to change over time.  Remember that one of the big benefits of using dimensions is that you can easily assign more of them without touching your chart-of-accounts.  Things like divisions, departments, product lines, and other types of variable characteristics may come-and-go, and accommodating these changes is the best purpose for using dimensions.
  • Try Not to Have Too Many Dimension Types:  Try not to go overboard with the number of dimension types.  It is a very unusual business that would need, say, nine dimension types.  Even though Sage X3 is much more flexible at handling this using the dimension concept than a typical accounting package would be, a profuse number of dimension types will nevertheless add complexity to your accounting process.  Always consider the cost-versus-benefit of utilizing additional dimension types in your accounting structure.

How to Set Up Analytical Accounting in Sage X3

Once you have thought through the design of your chart-of-accounts and analytical dimension structure, you can then proceed to enter them into Sage X3.  It is usually a good practice to prepare your listings of your chart-of-accounts and dimensions using Excel or similar software that lends itself to sharing and collaboration.  Once you have what you feel is a good starting point, it is time to start entering these values into Sage X3 and get your pilot configuration ready for testing.

How to Create an Analytical Dimension Types

Users can create up to 20 dimension types. These dimensions are shared across all the companies included in a Folder within your Sage X3 installation.

Click “Dimension types” under Setup > Organizational structure

As shown above, the entry in the Dim type format field determines the format of the values for the created dimension type code. There is a maximum length of 15, meaning that the values cannot be longer than 15 characters.   After entering the dimension length (a value of 1-15), enter the number sign (#) if the values will be numeric only, the letter “A” if the values include alpha characters only, and the letter “C” for values that are alphanumeric, for example, 10A represents a maximum length of 10.   Access codes can be used to limit user access to the selected record. Setting an access code will limit record access to users who have the access code. There are also different access codes for read-only and write/edit access that allows users to modify the record.

To automatically create a dimension value during the posting process using the automatic journal, select the “Automatic creation” checkbox. If a user checks the box and enters a value for a dimension type that does not exist, the system will automatically create the value if it meets the relevant criteria.   If the value does not meet the format criteria, the system will not automatically create the value. Instead, it will display an error message. If you do not want this value to be used during the new fiscal year, please select the “No Carry-Forward” checkbox.   Selecting the “No Carry-Forward” checkbox will reset the dimension value to zero. If the checkbox is not selected, the value will be carried over into the new fiscal year.   You can make changes to postings marked as final at the journal entry for a dimension by selecting the “Change Final Entries” checkbox. Additionally, the journal parameters and settings also determine whether modifications can be made.   The “Entity” and “Envelope” checkbox are used in the Operating Budgets module. When defining operational budgets, users will only be able to use dimension codes when either of these checkboxes are selected.   A dimension can be used to either track Project Management costs or project cost types. One dimension cannot be used to track both.

At the account core module level, tracking project management costs analytically requires meeting the following conditions:

  • Everything must be tracked in one ledger
  • The ledger must be Analytical
  • The ledge must only have one dimension type, which tracks Project management costs
  • If the dimension can track project management cost types, select the “Cost Type” checkbox. Items that fall with the “Cost Type” checkbox include things like labor costs, raw materials, and overheads.
  • A dimension can be used to either track Project Management costs or project cost types, but not both

Sage X3 Tips and Tricks

This is one in our ongoing series of Sage X3 Tips and Tricks. We’re previously covered other topics including:

Statistical Groups in Sage X3 - Person Feeding Box into Equipment

How to Create Statistical Groups in Sage X3

Sage X3 keyboard shortcuts

Top Sage X3 Keyboard Shortcuts

Sage X3 Price List - Woman on Laptop

Fourteen Steps to Increase the Number of Lines in a Sage X3 Price List

Sage X3 keyboard shortcuts

How to Replace a Failed Part on a Work Order in Sage X3

Sage X3 keyboard shortcuts

How to Close Stock Period in Sage X3

Sage X3 keyboard shortcuts

How to Close Stock Period in Sage X3

Sage X3 stock quantities SQL query

Create a Detailed Stock Quantities Query in Sage X3

About Us

CertiPro is your full-service technology partner. We offer full implementation and support for Sage X3 Distribution, Management, and Financial. We also provide a two-way Sage X3 Magento integration to handle your B2B or B2C eCommerce needs. Contact us and see what the CertiPro Difference can do for your business.        

Send us a message

Please fill out the form below and one of our associates will contact you shortly.



    Training and optimization available for current Sage software

    CONTACT US