Accounting and rounding errors in Financial Statements

[mrvegas]

Has anyone figured out how to get "whole dollar" financial statements to come out right using excel for consolidations?

I use Excel for consolidation spreadsheets. There are tabs for the Balance Sheet and the Income Statement. These tabs "pull" account balances from trial balances that have dollars and cents. Most of the line items on these tabs have several individual accounts that roll up (are combined) to arrive at summary amounts, e.g. the line "cash" on the Balance Sheet consists of several individual accounts ... checking $100.75 + savings $1,000.33 + money market $15,000.46 are all combined to be total cash of $16,101.54. This amount would need to be rounded up to $16,102 as our audited statements are in whole dollars.

Where this gets complicated is I have five separate companies that are each in their own column on the Balance Sheet and Income Statement tabs. It never fails that when I take the dollars and cents of the individual accounts and combine them into whole dollar amounts that the results are out of balance by a dollar here, two dollars there, etc.

 

[Gerald Higgins]

Hi, welcome to the board.

You may want to do some kind of rounding somewhere.

For example

=round(a1,0)

and

=rounddown(a1,0)

and so on.

 

[Andrew Poulsom]

Welcome to MrExcel.

There isn't a generic way, as it depends on your layout. I find the best way is to round cumulatives and deduct the previous rounded cumulative. So, for example, if you had tangible assets in A2, inventories in A3 and cash in A4 you would use a formula like:

=ROUND(SUM(Sheet2:Sheet6!A$2:A2),0)-SUM(A$1:A1)

in A2 on the summary sheet, copied down.

That way the total at the bottom of the balance sheet will always equal the total of the underlying date rounded to zero decimal places.

 

[mrvegas]

Rounding helps but does not totally solve it

 

[Gerald Higgins]

Rounding helps but does not totally solve it

OK, so what do you still need help with exactly ?

 

[mrvegas]

Welcome to MrExcel.

There isn't a generic way, as it depends on your layout. I find the best way is to round cumulatives and deduct the previous rounded cumulative. So, for example, if you had tangible assets in A2, inventories in A3 and cash in A4 you would use a formula like:

=ROUND(SUM(Sheet2:Sheet6!A$2:A2),0)-SUM(A$1:A1)

in A2 on the summary sheet, copied down.

That way the total at the bottom of the balance sheet will always equal the total of the underlying date rounded to zero decimal places.

My individual companies are in separate rows, not separate sheets, will this approach still work under that setup?

 

[Andrew Poulsom]

Yes, eg:

=ROUND(SUM(Sheet2!A$2:A2),0)-SUM(A$1:A1)

 

[mrvegas]

Yes, eg:

=ROUND(SUM(Sheet2!A$2:A2),0)-SUM(A$1:A1)

Do you find that when you have multiple companies and net income feeding into the balance sheet tab from a separate tab that there is sometimes a need to PLUG a dollar somewhere to get it all to balance out?

 

[Andrew Poulsom]

That depends on the layout of your data and what formulas you are using. It should be possible to eliminate rounding differences with formulas, but you may not get the answer you want for individual numbers because of the way the rounding is distributed.

 

[mrvegas]

My source data is on trial balance tabs, one for each company. What I have done is created "defined names" for account categories like cash, receivables, inventories, etc. So cells A1:A12 on tab 1 might be the cash accounts and named "cash." The same cells in tab 2 are defined as "cash2."

Do you think this naming convention can work under your general approach?