Rounding in excel preparing forecasts

Help please to find difference on my monthly balance sheet forecasts

Didn't find your answer?

Hi Peeps

I'm preparing a 3 year BS, p and l and cash flow projection. I've used formulaes, percentages etc etc.

I've got pence difference in my first month, and it accumulates monthly to about £10 at the end of the first year.

I'm assuming it's rounding somewhere but could do with some help looking please. I've used the round function where appropriate but the difference remains. Is the precision as displayed any good?

Thanks

 

 

Replies (14)

Please login or register to join the discussion.

Routemaster image
By tom123
16th Feb 2017 16:53

Would displaying the formulae help?

If I am using a lot of rounding, I tend to create the final column differently - ie being the total required to zero out the line.

Thanks (1)
Replying to tom123:
avatar
By tugwilson
16th Feb 2017 17:00

Hi Tom

I think I know what you mean. I've sort of found where I think the problem is further down. Basically it's a crap formula :)

Thanks (1)
avatar
By tugwilson
16th Feb 2017 16:58

Peeps

I think part of the problem is in my closing stock calculation formula on the trading account. I've asked excel (long winded, granted), to calculate closing stock as gp% of sales , less other cost of sales to equal closing stock. This is creating a recurring decimal place issue, and I don't know how to round the formula. Could someone help me to round my formula or improve it please? Here it is (cringe) =-SUM(F16*F32)-SUM(F19:F25)+F16 where f16 is turnover, f32 is gp% f19:25.

Thanks

Thanks (1)
Avatar
By I'msorryIhaven'taclue
16th Feb 2017 17:02

If you're want to show the pennies, 2 d.p. =round(x+y,2)
If you're working in round pounds, 0 d.p. =round(x+y,0)

You haven't said whether the difference is in the P&L, BS, or Cash-flow statement. But if you've linked P&L loan interest to B.S. bank overdraft that can easily cause a sort of compound interest effect that can go on ad infinitum, increasing first one and then the other, and create the effect you describe.

Do you have any circular references on your worksheet?

LATE EDIT: Ahha, your post got there before mine; so it looks like stock and sales rather than bank loan interest and bank balance are the culprits.

Thanks (1)
Replying to I'msorryIhaven'taclue:
avatar
By tugwilson
17th Feb 2017 08:58

Many thanks

Thanks (0)
Danny Kent
By Viciuno
16th Feb 2017 17:03

I would go to "tools" then "options" then tick the box that says "precision as displayed"

This will stop excell carrying forward all the .0000001 that accumulate and throw out your balances.

Tou will get a pop up that says the worksheet will never be correct or something to that ilk, but I have never been in a position after where it doesn't work.

Edit: Seem I'm too late to the party and more detailed answers above!

Thanks (2)
Avatar
By I'msorryIhaven'taclue
16th Feb 2017 17:22

I'd suspect cell F32, the gp%. I'll assume there are no circular referencing issues flagged on your worksheet:
Firstly, try rounding F32 to 2 d.p. viz
=-SUM(F16*ROUND(F32,2))-SUM(F19:F25)+F16
Or however many d.p. you want for F32 eg for zero d.p.
=-SUM(F16*ROUND(F32,0))-SUM(F19:F25)+F16
Secondly, for belt and bracers, try rounding the entire formula to zero d.p. (I'm assuming you want the results to be in rounded pounds)
=ROUND(-SUM(F16*ROUND(F32,2))-SUM(F19:F25)+F16,0)
Or however many d.p. you want for F32 eg for zero d.p.
=ROUND(-SUM(F16*ROUND(F32,0))-SUM(F19:F25)+F16,0)

Thanks (1)
avatar
By adam.arca
16th Feb 2017 19:50

Not exactly answering the question asked, but why are you including pence? That just seems a bit above and beyond, especially for a forecast.

Also, personally, I wouldn't perform a calculation in any cell which forms part of the forecast to avoid the sort of problem you're having. Think about performing the calculations elsewhere in standalone cells and then link the forecast cells affected to the individual calculation cells. I try to do that as one positive and one negative cell linked to every calculation so I'm sort of mimicking double entry but that's just a personal preference.

Thanks (1)
Replying to adam.arca:
avatar
By tugwilson
17th Feb 2017 08:58

Hi Adam

Using pence to try and nail it bang on. Was in pound only to start, but it accumulates by high pence each month.

Thanks for your welcome comments

Thanks (0)
avatar
By paulwakefield1
16th Feb 2017 22:02

If you must use "Precision as displayed", use it with the greatest of care and understand exactly what it does. It is very easy to lose the integrity of the spreadsheet model especially if anybody else is going to use it.

Bright ideas such as "I'll set the number format of the worksheet to zero dp" whilst forgetting that there are GP% and VAT % settings on the same sheet (now neatly set to 1 or 0) is a classic. DAMHIK.

Thanks (1)
avatar
By tugwilson
17th Feb 2017 09:12

Hi Guys

Thanks for everything. I've learned loads. Especially not to include workings and formula in the main statements. The difference was a percentage in a hidden row as part of =sum total. Lesson learned.

Thanks all again

Thanks (0)
Replying to tugwilson:
Avatar
By I'msorryIhaven'taclue
17th Feb 2017 10:57

tugwilson wrote:

Hi Guys

The difference was a percentage in a hidden row as part of =sum total.

Row 32, the GP%?

Thanks (0)
Replying to I'msorryIhaven'taclue:
avatar
By tugwilson
17th Feb 2017 16:33

R0w f20 (hidden). Doh!

Thanks

Thanks (0)
Replying to tugwilson:
Avatar
By I'msorryIhaven'taclue
18th Feb 2017 10:22

In the f19:F25 total; I didn't see that (well neither did you, come to think of it. That's why I dislike hidden rows; I try to put calcs at the bottom of each "page", below the bottom line in a non-print area).

Thanks (1)