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…

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.

 

“But That Account Always Nets to Zero” – The Importance of Including Everything on Financial Statements

Over the years, I’ve rewritten many financial statements. Unlike other reports, financial statements represent special challenges in that, most often, they specify particular ranges of data.

Let me explain. When we build a sales report, we generally start with all the data we want and then sort and select from there. We may select all invoices and matching customer data. We may sort those invoices by region, salesperson, store, or line of business. We may allow the user to select one or several regions, salespeople or lines of business. But no matter how we sort or select, we can always run the report for everything and confirm we’re getting the full picture.

Financial statements are different. With the exception of a very basic trial balance where you can select all the natural balance accounts, a financial statement is built line by line. For example, the first line may be sales – 40000-42000. The next line may be intercompany sales 420001-44000, and then COGS 50000-52000, and so on. The report builder has to make sure to include every account on the report if he/she wants to figure out net income, for example.

If all your account ranges are nice and continuous, it’s not hard to make sure you’ve included everything. But account ranges have a tendency not to stay neat and tidy. Indeed, today I had to adjust logic on a legacy report so that advertising, which we had set to a tidy 60000-69999, is now 60000-69999 except for 66600 and 66750, which we had to add to a different line. And I’m not even touching on the challenge of insuring that other parts of your account string are considered (such as legal entity, profit center or department).

Another problem is that people accidentally leave accounts off statements. At Red Three, we’ve designed ways to ensure this doesn’t happen. (For example, by using attributes in Lawson and using hierarchies in Oracle EBS – a topic for a future post).

But sometimes, people purposely leave accounts off reports because they assume a certain range will always net to zero. We see this sometimes with intercompany eliminations or WIP (work in progress) costs that wind up in inventory. Generally, this isn’t a problem until someone makes a mistake. But then the report won’t reveal why it doesn’t net to zero.

This is why, when building a financial report that excludes certain accounts because they “always” zero out, it’s important to have backup in case someone makes a mistake. Yes, you’ll have reports that print nothing but zeros for months in a row. But when someone makes a mistake, and you’re trying to close on a tight deadline, you’ll be happy to have that report.

 

Loved the Training, But I Remember Nothing

Over the years, I’ve gotten pretty good reviews of my training. People say I communicate clearly, understand the material and am even somewhat entertaining. (Go figure.) But I also know that even though most people like my training, they often don’t remember much. I think it’s because I’ve been following the standard methodology of software training, which means covering as many features as possible. This satisfies the goal of completeness – but not necessarily of learning.

But of late, I’ve changed tack. I think a better approach is to cover a few key points several times. Don’t just create one parameter, create multiple. Don’t immediately move on to the next concept, repeat the basic one several times. Most people won’t “get it” the first time. And even if they do, they won’t remember what they’ve learned unless it’s repeated.

I’ve been thinking about this while preparing new Crystal reports training sessions, which I’ll be delivering soon. The question arises – do I recommend one of the many “complete” prepackaged training materials available, or do I recommend using just a few short “cheat sheet” pages. These cheat sheets aren’t nearly as comprehensive as the prepackaged materials – but they’ll help us get down a few key points.

Of course, changing training methodology is only a starting point in helping people learn software. For example, I’ve posted before about how training is wasted if people don’t use what they’ve learned shortly after learning it and over an extended period of time.

What’s your experience? If you’ve taken software training, did you like it? Do you remember any of it?

 

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.

Understanding VAT

As discussed in an earlier post, we’re creating a series of posts to cover some of the complex system accounting challenges we commonly run into. In this first blog post of the series, we’ll review the basics of VAT, especially as it compares to sales and use tax.

When U.S. companies expand internationally, they find themselves having to meet a new set of reporting and regulatory requirements. They wonder how (and if) their enterprise software solution can meet them. But before addressing these questions, it helps to understand the requirement itself.

VAT vs. Sales and Use Tax

VAT (or value added tax) is different from sales and use tax. Sales/Use tax in the United States is only charged to the final consumer of the material or service involved. Wholesalers who buy in bulk do not pay sales tax nor do the retail stores that buy from them. Retail stores at the end of the process charge sales tax to the final consumer. Thus, a 5% sales tax rate would look something like this:

Unit cost Tax Total invoice cost
Wholesaler buys from manufacturer $5.00 $0.00 $5.00
Retailer buys from wholesaler $7.50 $0.00 $7.50
Consumer buys from retailer $10.00 $.50 $10.50

VAT is different. With VAT, each entity charges VAT to the next entity at every step in the process. Thus, a 20% VAT rate would look something like this:

Unit cost Tax Total invoice cost
Wholesaler buys from manufacturer £5.00 £1.00 £6.00
Retailer buys from wholesaler £7.50 £1.50 £9.00
Consumer buys from retailer £10.00 £2.00 £12.00

As an American looking at this example, you might ask whether the retailer’s end price should be higher to compensate for all that VAT. Why is it just the difference between 5% and 20%? Did the government take a bigger piece all the way through?

No, the government isn’t getting a bigger piece. VAT taxes the value a company adds to a product as it passes through the supply chain. The total tax the consumer pays is only £2.00, not £4.50. Wholesalers, retailers and other parties in the supply chain give the government the difference between the VAT they were charged and the VAT they charged to consumers. Let’s explain:

1) When a company files a VAT return, it reports all the VAT it was charged by vendors and all the VAT it charged to customers. Then it pays the difference.

2) The company pays VAT owing to the government periodically via a VAT return.

Let’s say our retailer bought 100 widgets. Its (simplified) VAT return would look like this:

VAT paid £150.00
VAT charged £200.00
Net VAT owed £50.00

The wholesaler then files its return for the same 100 widgets:

VAT paid £100.00
VAT charged £150.00
Net VAT owed £50.00

And the manufacturer (assuming that it has dug the stuff up from the ground) files its return.

VAT paid £0.00
VAT charged £100.00
Net VAT owed £100.00

The total VAT paid is £200.00, but unlike sales tax, it’s paid in stages.

VAT may be calculated on a cash or accrual basis depending on the country and the size of the business. Lawson’s standard report handles it on an accrual basis, as we’ll discuss in our next blog post.

 

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