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

## Please login or register to join the discussion.

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.

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 :)

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

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.

Many thanks

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!

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)

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.

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

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.

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

Row 32, the GP%?

R0w f20 (hidden). Doh!

Thanks

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).

## Please login or register to join the discussion.