Hi William

I had rather assumed monthly or shorter time intervals. Annual variation in
Cash would be quite material!!!

Not quite sure what you now mean by "dodgy" answers. Can you give some
examples of what you mean?


Regards

Roger Govier


William Sporborg wrote:
Roger

Thank you for that. I had already ticked the Options/Calculation box, but
the numbers still shimmer like a mirage! Unfortunately, the forecasts are
annual, so the opening and closing balances are very different, and the
interest is quite material.

That said, I have changed the basis of calculation of interest, as you
suggest, with workings ouside the cashflow, but I am still getting "dodgy"
answers eg one can see that the summing of cells is wrong, just by eye! Very
frustrating, and any further thoughts gratefully received!

William

"Roger Govier" wrote:


Hi William

This is obviously forecasting, as opposed to actual as you are calculating
the interest. Your problem arises as you are trying to include the cell with
the interest calculation within the cashflow itself.

Under ToolsOptionsCalculation you can click on the iterations box and set
the maximum number of iterations, and, the amount of change.

Personally, I do not like circular references.
Since the actual interest charged by the bank will be different anyway, as
it depends upon when funds actually cleared, in my experience there is
usually only a small difference if you base the interest calculation on the
final balance of the previous period. This will then prevent circular
references.

Regards

Roger Govier


William Sporborg wrote:

I have a p&l account, a balance sheet and a cashflow, running down the page.
Bank interest in the P&L is calculated with reference to the average cash
balances in the balance sheet, and cash balnces are determined by the cash
flow. Dividends are paid, depending on profitability and cash balances.
Unfortunately, the spreadsheet iterates, so instead of trending towards the
right answere through iteration - the "calculation" tab on the tools/options
sheet is crossed - it gives a wrong answer.

Is there any way I can reorder my workings, or any changes I can make to
prevent this.

Many thanks