Southwest Software Meltdown

It’s rare that any kind of HR Software makes the news.   But it happened this week.  As you may have read, Southwest was responsible for the vast majority of flight cancellations after the historical pre-Christmas storm.  In the wake of the Southwest Airlines meltdown, turns out that old software is a major cause of the problem. As reported by CNN:

“On a call with employees Tuesday, Southwest Chief Operating Officer Andrew Watterson explained that the company’s outdated scheduling software quickly became the main culprit of the cancellations once the storm cleared, according to a transcript of the call that was obtained by CNN from an aviation source.”

And further:

“The process of matching up those crew members with the aircraft could not be handled by our technology,” Watterson said.

Does your company rely on software that only kinda/sorta works?  When skies are clear and when Dave in payroll and Jane in IT perform their special “year-end close dance”? Does management demand an ROI for any investment in new systems?   Because, after all, the special year end dance continues to work so why waste money.

You might want to save the story and the link.  Because you never know when a system that only kinda/sorta works will stop.   And you won’t be worrying about ROI when that happens.

Financial Risk and Your Non-Profit

I was talking to a friend/prospective client recently about the accounting knowledge that is a key part of my work.

We agreed that in too many non-profits, the board’s finance committee doesn’t perform its necessary function. The committee members look at a few reports when they meet, but they don’t really dig in. They may have a soft connection to “numbers,” but they aren’t always great at picking apart financial statements.

So, the question came up: if you want to evaluate financial risk in your non-profit, where would you start?

Read more…

Understanding the NAV Database

I recently posted a seven-part series on SQL for super users. (You’ll find the introduction here.)

In this series, I chose to use generic Northwind database (with some enhancements) for my examples. (The posts came out of presentation I made to a mixed group of Dynamics users.)

But, as you know, each kind of Dynamics software (i.e. GP, NAV, AX, CRM) has its idiosyncrasies. Therefore, I’m in the process of adapting the generic Northwind examples to specific Dynamics types. And I’m starting with NAV, as I happen to spend quite a lot of time on it.

Read more…

PDF vs. Excel—Does a PDF Really Secure Your Data?

I recently presented to a Dynamics GP user group on SSRS. It was a very interactive session with lots of questions. (Which was great. I hate when people just sit and nod.) One particular discussion was about the merits of delivering data in Excel vs. PDF. Some folks thought PDFs were more secure because people couldn’t manipulate the data as they can with Excel. Others said their users wanted data in Excel and, therefore, they got it in Excel.

From my perspective, there’s no difference. Why? Because any well-formatted PDF can be turned into Excel in about two minutes using Adobe’s own tool.

For example, let’s take the PDF below. It was created by SSRS over the Microsoft AdventureWorks database.

Can I turn this PDF into an Excel document? Well, if I cut and paste; I get strange results. But with Adobe’s Export PDF service, which they push right in Adobe Reader, I can covert from PDF to Excel (or Word or other formats) for about $25 a year.

When in Adobe, click Tools. You’ll see various options, including Export PDF. Assuming you’ve signed in and have a subscription, simply choose Excel as your desired format and then click Convert.

Abode quickly returns a “Completed” message with a link to download the file.

When you open the file in Excel, you get this:

It’s not perfect, but it’s pretty good.

There may be ways to lock PDFs. But that functionality isn’t part of the PDF creation process in either Crystal or SSRS. So that wouldn’t work either.

My point: Data honesty in your organization must be a function of people and process, not technology. Whatever format you choose, once data is released to your users you lose control of it. Instead, any number you count on must be verifiable back to or produced by a standard reporting software—and not generated from Excel or a manual process. This isn’t an easy thing to do. But you may have no option.

How does your company share data with users? Excel or PDF?

 

Finance vs. Sales—Why Your Data Marts Don’t Tie Out

In this blog post, I describe a challenge we’re having with SSAS cubes. In this case, the challenge isn’t caused by technology. It’s caused by data.

Here’s the situation: Our client has used SSAS to build several cubes for reporting, which they’re using to replace a no longer supported version of Business Objects. We’ve been asked to review the Business Objects reports and create new reports using SSRS with these cubes as a data source.

While my client has some technical challenges and room for optimization, my technical folks are handling that aspect of the project well. But the project also has a larger challenge: The client has two different SSAS cubes, one built from sales data from the order processing system and one built from general ledger data. And the two cubes don’t always agree. This is a problem. Only the sales cube can give information by product. And only the financial cube can give true profitability.

This isn’t a technical problem. It’s a problem common to every ERP system: What an order processing system considers “profit” isn’t what gets reported on the financial statement. There can be multiple reasons for this:

1. Shipping Goods vs. Recognizing Revenue

Just because you’ve shipped goods DOES NOT mean you can recognize revenue. This company is a manufacturer. In general, manufacturers and wholesalers don’t have complex revenue recognition issues, so when the order system ships something, the company can recognize revenue.

But sometimes a manufacturer, because of supply chain issues, needs to ship something before the customer wants it. And per GAAP, you can’t recognize revenue for something that someone doesn’t want. So, you need to make an adjustment. And often, this adjustment is made only in the general ledger so the order system knows nothing about it.

2. Allocating Costs to Inventory

A regular order processing system can only report profit by product based on costs allocated to inventory. But for a whole series of reasons, not all costs get allocated back to inventory within the sub-ledger but still hit the general ledger.

Let’s take freight for example. Depending on how the inventory was originally “made,” all freight costs may not have hit inventory. This may be acceptable if freight is fairly consistent over the range of products sold. However, if freight varies, and it’s not properly added to inventory, then you can have a big discrepancy between what comes out of the order system and the general ledger.

3. Costs Beyond COGS

Even if your order processing system has good inventory costs and you can report accurate gross profit, you can be sure that’s not everything you’re spending on every product. Marketing costs, whether consumer coupons or special promotions to wholesalers, are often assigned to specific products. But even if you code this data to a product, brand or line of business, it’s probably only in the AP system and, therefore, not in either of the cubes!

Fortunately, you can solve these problems. Sometimes, you can tie ledger transactions back to the subsystem, so you can at least reconcile profit coming from the order system with what is based on ledger-only adjustments. Other times, you can find ways to code product or customer information in the general ledger.

But whenever you’re building a new data mart, you need to consider these issues before you start. You don’t want to be surprised that the numbers don’t agree after you’ve built everything.

Understanding Currency Accounting: A Few Additional Pointers

So far in this series, we’ve covered the basics of exchange and revaluation and revaluation and translation as it relates to accounts and controls. In this final post on currency accounting, we’ll provide a few additional pointers to help your currency accounting run smoothly.

Watch Your Rates

Everything depends on having good rates in your system. To be good, a rate needs to be updated frequently and accurately.

1. Update rates frequently

When you first start with multiple currencies, it’s often not a crucial part of your business. So, some businesses decide to update rates only monthly. If rates are stable, this isn’t much of an issue. If rates swing, it becomes a problem.

Let’s take an example. Say ACME only sets rates once a month. They send out weekly invoices to their U.K. Customer. At the end of the month, they revalue these invoices. For the entire month, they used a rate of 1 GBP = 1.5 USD. At month end, they revalue to a rate of 1 GBP = 1.75 USD.

Invoice Date Invoice
Amount (GBP)
Rate Financial Statement Amount (USD)
August 1st 10,000 1.5 15,000
August 8th 10,000 1.5 15,000
August 15th 10,000 1.5 15,000
August 22nd 10,000 1.5 15,000
August 29th 10,000 1.5 15,000
Total for Month 50,000 1.5 75,000
Revaluation 50,000 1.75 87,500
Currency Gain 12,500

At month end, none of these invoices have been paid. And the rate for the month changed dramatically. They revalue the amounts involved at month end and now the 50,000 of GBP invoices are worth 87,500 USD. So they need to book an entry of 12,500 USD to currency gain/loss. A significant number.

Now, let’s say ACME has a policy of changing rates as the month goes on (and to make things simple, we’ll assume the rate changes in a linear fashion). Here’s what those invoice postings may now look like:

Invoice Date Invoice
Amount (GBP)
Rate Financial Statement Amount (USD)
August 1st 10,000 1.5 15,000
August 8th 10,000 1.55 15,500
August 15th 10,000 1.60 16,000
August 22nd 10,000 1.65 16,500
August 29th 10,000 1.70 17,000
Total for Month 50,000 1.6 80,000
Revaluation 50,000 1.75 87,500
Currency Gain 7,500

Just by keeping rates up to date, the currency gain has changed dramatically.

2. Enter rates correctly

Almost all accounting systems allow you to specify default rates for your foreign currency transactions. They also let you override those rates on a transaction-by-transaction basis. But here’s the rub: some, but not all, accounting systems check the rates in your system to ensure they’re within an acceptable range. So, for example, if the system expects a rate of 1 GBP = 1.5 USD, the system will accept entries of 1.55 to 1.45 but will report an error if the user tries to override the rate with 1 GBP = 2 USD.

If you don’t have this type of control in your system, you can end up with major problems. You have a couple options:

1) Modify your system to check rates. This can be difficult.

2) Create exception reports to highlight cases where rates vary more than expected.

Start with the Foreign Currency

Accountants under month end pressure sometimes push to do their consolidated analysis before they’ve fully understood the local currency statement (in this case GBP). This doesn’t work. If you have legal entities operating in multiple currencies, you need to make sure the local currency is good before you do your revaluation.

I hope you’ve found this series of posts on currency accounting helpful. If you need help managing currency accounting challenges with your ERP system, feel free to contact us.

 

Understanding Currency Accounting: Exchange and Revaluation

When we started our series on complex accounting challenges, we explained that our data consultants need to educate our clients in what we do before we can explain how we can do it for them. This is particularly true with foreign currency accounting. In Europe, it’s rare that to find a company of any significant size that doesn’t face currency issues. As a result, most European accountants have at least a basic understanding of what’s involved. In the U.S., that just isn’t the case. Given the size of our market, many companies expand extensively without ever trading internationally. And if they do trade internationally, often their trading partners use U.S. dollars. So, they operate internationally and still have no currency exposure.

But that’s not always the case. And indeed, with opportunities in developing worlds often outpacing those in the U.S. domestic market, it’s happening more and more.

So, in this series of blog posts, we want to explain the basics of currency accounting as it relates to accounting systems. We’ll discuss exchange, revaluation and translation. We won’t get into elements of currency that don’t relate to the systems we deal with – so we won’t talk about hedging currency risk and such.

Please note: The ISO (International Organization for Standards) assigns a three-letter code to all currencies. In this post, we’ll reference each currency by its full name and ISO code in the first instance. For all subsequent instances, we’ll reference the currency only by its ISO code.

Exchange on a Cash Basis

Let’s start with the basics. ACME Widgets starts selling its products abroad. Overseas clients pay them in Euros (EUR) and British Pound Sterling (GBP). For simplicity, let’s assume the client pays COD. The customer pays 10,000 GBP. Looking up the exchange rate, we find 1 GBP = 1.5 USD. So, when we record the information on our financial statement, we’ll show a credit to sales for 15,000 USD and a credit to cash for 15,000 USD. That gives us the following trial balance:

ACME Widgets
Debit Credit
Cash 15,000 USD
Sales 15,000 USD

Exchange and Revaluation of Accounts Receivable

Of course, that example is too simple. Few companies large enough to have currency risk operate on a cash basis.

So, let’s use a more realistic example. ACME sells the same goods for 10,000 GBP on April 15th. Again, the exchange rate is 1 GBP = 1 USD. Therefore, we have the following trial balance:

ACME Widgets
Trial Balance
Acct # Description Debit Credit
11000 Account Receivable 15,000 USD
40000 Sales 15,000 USD

Now, it’s month end. We still show 15,000 USD receivable on our books. But we need to ask – is that receivable still worth 15,000 USD? Indeed, if we look at the exchange rate on April 30th, we now find that 1 GBP = 1.6 USD. If they paid us 10,000 GBP, we’d receive 16,000 USD. This is revaluation. Revaluation is simply setting the value of a foreign currency asset to its current value if the asset were liquidated at this moment.

At month end, therefore, we need to book new entry. The entry affects two accounts.

Journal Entry
Acct # Description Debit Credit
11000 Accounts Receivable 1,000 USD
90000 Currency Gain Loss/Unrealized 1,000 USD

The gain or loss hits the income statement. We consider it unrealized because the customer hasn’t paid yet – this is just our best guess of how much we’ll gain or lose, but we aren’t sure. Generally, we make this a reversing entry so when we do get paid we can book a realized gain or loss entry.

Our trial balance now looks like this:

ACME Widgets
Trial Balance
Acct # Description Debit Credit
11000 Account Receivable 16,000 USD
40000 Sales 15,000 USD
90000 Currency Gain/Loss Unrealized 1,000 USD

The journal entry reverses at month end. Which makes the next entry easier to calculate.

Continuing with our example, let’s say it’s now May 15th and the customer pays us. We receive 10,000 GBP. Once again, we check the exchange rate. Now, 1 GBP = 1.55 USD. So, the payment is worth 15,500 USD, meaning we have a final realized gain of 500 USD. We include that as part of our entry reflecting the cash receipt. Because we’ve reversed our unrealized gain/loss entry, we can simply book the 500 USD amount. We’ve finished revaluation for this entry, and we’re back to simple exchange.

Journal Entry
Acct # Description Debit Credit
10000 Cash 15,500 USD
11000 Accounts Receivable 15,000 USD
90001 Currency Gain Loss/Realized 500 USD

And our trial balance looks like this:

ACME Widgets
Trial Balance
Acct # Description Debit Credit
10000 Cash 15,500
40000 Sales 15,000 USD
90001 Currency Gain/Loss Unrealized 500 USD

In our next blog post, we’ll cover translation and aspects of revaluation that relate to accounts and controls.

Let us know if you’re interested in Financially Aware Business Intelligence using tools like Jet Reports and Dynamics NAV Consulting.

Understanding Currency Accounting: Revaluation and Translation

Continuing our previous post on currency accounting, we’ll now move onto translation and revaluation as it relates to accounts and controls.

Revaluation doesn’t just impact accounts payable and receivable. It also impacts foreign currency bank accounts and/or intercompany payables and receivables. The challenges with these accounts are often more system-based than conceptual. Most accounting systems that can handle foreign currency can track the currency and initial rate of payables and receivables. Because most systems require complete information for transactions these transactions are generally entered with complete information (you can’t print an invoice/cut a check if you don’t specify the currency correctly), revaluation is usually fairly smooth.

Bank accounts can be more of a challenge. Accountants unaccustomed to working with different currencies often take short cuts when doing account reconciliations. They reconcile their financial statements, but they don’t always reconcile the foreign currency balance. Some systems have the ability to insist that currency is always entered correctly but too often these controls are not properly set up. Here are a few tips to help avoid problems:

1) If you have to revalue and you have accounts with transactions in multiple currencies, set up sub accounts for each currency involved.

2) Setup system controls as tightly as possible. Make your end users enter the proper currency information with every transaction.

Balance Sheet or Income Statement?

In our example above, we treated the gain/loss as an income statement item. This will be the case for most accounts you revalue. The general rule (and, again, please check with your accountants) is that any asset or liability that you expect to settle within a set amount of time (such as payables and receivables) should be revalued to the income statement. If you have liabilities or assets like intercompany payables/receivables that you don’t expect to settle quickly, the revaluation should hit the equity section of your balance sheet. The ability to determine the appropriate account is often not allowed through software packages. Often, we can work around these shortcomings with reports.

Translation

So far what we’ve covered applies to companies that do business in other currencies but the company itself (and all its legal entities) still operates in a single currency. When you have legal entities in multiple jurisdictions with multiple currencies, you need to perform currency translation. Currency translation is the process by which we take foreign entities and “translate” their financial statements into the currency of the parent entity.

Let’s take an example of ACME Manufacturing. ACME has entities in the U.S. (reporting in USD) and entities in the U.K. (reporting in GBP). The UK/GBP entity must close its books in GBP and then “translate” them to USD for consolidation purposes. (See our series on consolidation for more information.)

To do this, you have to take the financial statements and apply the appropriate translation rate to each account. The difference is then posted to the equity section of the balance sheet as “foreign currency translation.”

Generally, rates are determined as follows:

  • Income statement accounts are translated at the average rate for the period.
  • Liability and asset accounts (with the exception of fixed assets) are translated at the ending rate for the period.
  • Fixed asset accounts are translated at the original historical rate at which they were acquired. This means that in most accounting systems, the translated rate is calculated when the entry is created and is never “retranslated” again.
  • Equity accounts are generally not revalued.

Naturally, you’ll want to review your setup to ensure you conform to standard GAAP. Our goal here is simply to explain your options.

To illustrate, let’s start with some basic trial balances for ACME Widgets and its U.K. affiliate. To make things easy, I’ve set it up so there are no intercompany accounts to eliminate. Debit balances are positive numbers, credit balances are negative numbers.

ACME US
(In USD)
ACME UK
(In GBP)
Rate Type Rate ACME UK

(In USD)

US and UK (USD)
Cash 50,000 25,000 Period End 1.6 40,000 90,000
Accounts Receivable 25,000 10,000 Period End 1.6 16,000 41,000
Fixed Assets 30,000 10,000 Historical 1.5 15,000 45,000
Accounts Payable -30,000 -15,000 Period End 1.6 -24,000 -54,000
Stockholder Equity -40,000 -12,000 Historical 1.5 -18,000 -58,000
Currency Translation 1,600 1,600
Sales -100,000 -50,000 Per Average 1.7 -85,000 -185,000
COGS 50,000 25,000 Per Average 1.7 42,500 92,500
SG & A 10,000 5,000 Per Average 1.7 8,500 18,500
Occupancy Cost 5,000 2,000 Per Average 1.7 3,400 8,400

After all the translations are performed, the offsetting balance is posted to currency translation. It resides in the equity section of the balance sheet.

In our next blog post, we provide a few additional pointers to help your currency accounting run smoothly.  And let us know if you’re looking for BI Consulting or a Microsoft SSRS Consultant.

Consolidation: Challenges and Solutions

Consolidation is a basic accounting concept that’s simple in theory, but complex in the real world. In this post, we’ll cover the basics of consolidation, some of the challenges that emerge and possible solutions.

Understanding Consolidation

In the context of financial accounting, consolidation is the aggregation of the financial statements of two or more companies under the same ownership into a consolidated financial statement. To really grasp consolidation, you need to understand that in the outside world, no one cares about money that’s traded back and forth between different companies under the same ownership. In the outside world, the only revenue that counts is revenue coming from a real customer. That’s what consolidation is all about – putting together financial statements that eliminate all the internal back and forth and focus only on “real” customer revenue.

Let’s explain how this works with an example of a growing company.

ACME makes and sells widgets to wholesalers around the country. Focusing on the income statement, let’s say (for absolute simplicity) that ACME sells 1000 widgets at a price of $5.00 each and a cost of $2.00 each. Its income statement would look like this:

Manufacturing
Wholesale Sales $5000
COGS $-2000
Gross Profit $3000

Everyone’s happy. The company makes money. And any system can handle the accounting.

As ACME grows, it decides to open retail stores. And these retail stores are set up as a different legal entity. This creates more complexity because the manufacturing entity must “sell” widgets to the new retail entity. Let’s assume ACME sells 1000 widgets to its wholesale customers and another 500 widgets through its retail channel. Let’s also assume that the manufacturer charges the retail division the same price it charges outside customers. The retailer then charges its customer $7.00 per widget for a total of $3500. We now we have two income statements, one for manufacturing and one for wholesale:

Manufacturing Retail
Wholesale Sales $5000
Retail Sales $3500
Interco. Sales $2500
COGS $-3000
Interco COGS $-2500
Gross Profit $4500 $1000

Now it’s time to consolidate the income statements. If we add all revenue together, we’d have a total of $11,000. That’s not right. We only sold 1500 widgets and the total price out the door was $8500. So, we have to make journal entries to “eliminate” the intercompany entries while preserving the original statements for the manufacturing and retail group. Elimination simply means backing out all intercompany activity transactions.

So, we set up an additional “elimination statement” either through Excel, by creating a dummy company in the accounting system, or with special consolidation software. Our numbers now look something like this:

Manufacturing Wholesale Elimination Consolidated
Wholesale Sales $5000 $5000
Retail Sales $3500 $3500
Interco. Sales $2500 $-2500 0
COGS $-3000 $3000
Interco COGS $-2500 $2500 0
Gross Profit $4500 $1000 0 $5500

Thus far we’ve dealt only with the income statement, but the same logic applies to balance sheets.

A few additional things to note:

We recommend keeping separate accounts for intercompany and external company transactions. This makes elimination easy. Too often, intercompany and external transactions are mixed together, either because systems are inadequate or not set up appropriately.

As a concept, consolidations aren’t that hard. And in many companies, even mid-size ones, no one pays much attention to them. Often, outside accountants create the consolidated financial statement and only the CFO Controller and/or the bank looks at it. Often, business leaders look only at their individual statements to go about their business.

Yet, in practice, consolidation can quickly get complicated.

How Consolidation Gets Complex

At the end of the day, consolidation is really about addition – adding in balancing entries. But things can get complicated quickly. Here are some of the complexities we see regularly:

1. Scaling Up to Multiple Systems
On purpose, we’ve used a simplified example. In reality, it’s rare to have such a simple situation. As companies grow, structures get complex, and multiple levels of consolidation must occur. While some people stick with an Excel solution as long as possible, it just isn’t trustworthy. Often, when people “upgrade” from Excel to a real system, they discover that what they thought was working, wasn’t.

2. Setting Up Intercompany Costs
In our example, widgets were sold at the same price to outside wholesalers and the company-owned retailer. But life is rarely so simple. There are all kinds of reasons management may want different intercompany prices. Some we’ve heard include:

  • The effort it takes to sell to a related company is much less then the effort it takes to sell to an outside party. The price should reflect that.
  • We can’t let those guys know our costs. If the sales guys in division X knew the cost, they’d undercut our prices.
  • Everyone here gets paid based on margin. We have to set reasonable margins around the organization to keep compensation in check.

If you’re operating within one country, you have a fair amount of leverage (with some restrictions – talk to your tax guys) on allocating your profit. When your entities are in different countries, things get more complicated. Every government in the world wants to collect more tax. Any costs you set between companies need to be justified. If your manufacturing plant in Mexico charges too little to the U.S., the Mexican government is going to want to know why.

In these situations, you often need to maintain two sets of books – one for tax and one for management.

3. Currency Issues
Currency issues (the subject of an upcoming post in this series) are complex even when you aren’t consolidating. When you are consolidating, they’re even worse. If manufacturing sells to retail, what currency do you use for that transaction? How do you track the value over time? This topic deserves its own post. Stay tuned.

4. How Much Money Do Your Divisions Make?
Companies go through consolidation because outsiders don’t care about all your inter-company back and forth. They only care about the net revenue you earn from customers, not coworkers.
While simple eliminations can create a consolidated view, they don’t help you determine how much money each division really made. To figure that out, it’s not enough to eliminate entries, you also need to allocate costs. Let’s demonstrate with our earlier example:

We assumed a $2.00 cost per widget. Manufacturing charged the retail division $5.00 per widget. Is this fair? Should manufacturing get all the credit? Or should it be split differently? Which divisions should assume which portion of the costs?

5. Partial Ownership and Joint Ventures
So far in our examples, we’ve pretended that all our companies are owned fully by the same entity. This isn’t always the case. How do you consolidate with partial ownership or complex joint ventures?

Let’s take a look at handling these complexities.

Solving Consolidation Challenges

If you’ve been following our blog for awhile, you probably know we believe in implementing the simplest solution possible to get the job done. We apply this approach to consolidation as well. We’ve identified four different ways to solve consolidation challenges.

1. Outside Accountants
For mid-sized companies with two or three entities, the most common approach is to let outside accountants deal with it. When a company has to answer to its bank and a few owners, a consolidated statement is generally not all that important – it’s something they have to produce once a year at most. And while auditors who follow strict rules of independence shouldn’t be doing your consolidation for you, smaller accounting firms generally handle such things in the normal course of business.

2. Excel
When all else fails, use Excel – the accountant’s Swiss Army knife. Again, this can work to a degree. If you only have a few entities and don’t have to consolidate statements very often, then Excel is fine. Generally companies start using Excel when their outside accountants stop doing consolidations for them, and consolidation remains an occasional chore rather than a key part of the financial close.

3. The General Ledger
Any General Ledger that can support a mid-sized company will have the ability to create multiple legal entities. We often use this ability to get elimination entries into systems and out of spreadsheets. Database driven ledgers are preferable to spreadsheets because they are far better at ensuring data consistency.

Even if all your companies don’t use the same GL system, you can still make it work by writing an interface between ledgers. We’ve done this for many clients because it’s generally easier (and more cost effective) than buying new software just for consolidation

4. Multiple Sets of Book Within One System
As we mentioned in our example, sometimes you need to keep multiple sets of books – one for management purposes and one for tax purposes. You might even need a third set for GAP purposes.

While you can use your standard ledger to get most of the way there, you might want to find out if your system has the ability to have multiple ledgers within one system. While you still won’t be generating your consolidation automatically, you’ll at least meet management and statutory needs with one system.

5. Real Consolidation Software
When you grow beyond a certain size, your ledger (even if it can support multiple sets of books) isn’t enough. That’s when it’s time to check out software designed to handle consolidations. Some examples are SAP Planning and Consolidation or BPC or Oracle Hyperion Financial Management software.

One final cautionary note: Software isn’t a panacea. A great team can get the job done with just okay software, while perfect software won’t save a mediocre team. To get the desired results, you really need to nail down the non-software side. Different divisions have to talk to one another. They need to agree how they’re going to make entries and do it in a timely manner. They need to agree to a close schedule so month end doesn’t turn into a mad house with people scrambling to find matching entries. They need to use their systems to the fullest, so it’s easy to segregate internal and external company transactions.

If you have these kinds of processes in place, then your software will do the job. If not, gold plated software won’t be enough.

Solving Consolidation Challenges

In our previous posts on consolidation, we’ve reviewed consolidation basics and complexities. In this post, we’ll cover system solutions to consolidation challenges.

If you’ve been following our blog for awhile, you probably know we believe in implementing the simplest solution possible to get the job done. We apply this approach to consolidation as well. We’ve identified four different ways to solve consolidation challenges.

1. Outside Accountants
For mid-sized companies with two or three entities, the most common approach is to let outside accountants deal with it. When a company has to answer to its bank and a few owners, a consolidated statement is generally not all that important – it’s something they have to produce once a year at most. And while auditors who follow strict rules of independence shouldn’t be doing your consolidation for you, smaller accounting firms generally handle such things in the normal course of business.

2. Excel
When all else fails, use Excel – the accountant’s Swiss Army knife. Again, this can work to a degree. If you only have a few entities and don’t have to consolidate statements very often, then Excel is fine. Generally companies start using Excel when their outside accountants stop doing consolidations for them, and consolidation remains an occasional chore rather than a key part of the financial close.

3. The General Ledger
Any General Ledger that can support a mid-sized company will have the ability to create multiple legal entities. We often use this ability to get elimination entries into systems and out of spreadsheets. Database driven ledgers are preferable to spreadsheets because they are far better at ensuring data consistency.

Even if all your companies don’t use the same GL system, you can still make it work by writing an interface between ledgers. We’ve done this for many clients because it’s generally easier (and more cost effective) than buying new software just for consolidation

4. Multiple Sets of Book Within One System
As we mentioned in our example, sometimes you need to keep multiple sets of books – one for management purposes and one for tax purposes. You might even need a third set for GAP purposes.

While you can use your standard ledger to get most of the way there, you might want to find out if your system has the ability to have multiple ledgers within one system. While you still won’t be generating your consolidation automatically, you’ll at least meet management and statutory needs with one system.

5. Real Consolidation Software
When you grow beyond a certain size, your ledger (even if it can support multiple sets of books) isn’t enough. That’s when it’s time to check out software designed to handle consolidations. Some examples are SAP Planning and Consolidation or BPC or Oracle Hyperion Financial Management software.

One final cautionary note: Software isn’t a panacea. A great team can get the job done with just okay software, while perfect software won’t save a mediocre team. To get the desired results, you really need to nail down the non-software side. Different divisions have to talk to one another. They need to agree how they’re going to make entries and do it in a timely manner. They need to agree to a close schedule so month end doesn’t turn into a mad house with people scrambling to find matching entries. They need to use their systems to the fullest, so it’s easy to segregate internal and external company transactions.

If you have these kinds of processes in place, then your software will do the job. If not, gold plated software won’t be enough.

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult