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.
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.
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.
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.
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
The difference was a percentage in a hidden row as part of =sum total.
Row 32, the GP%?
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).