How do I resolve complex iteration problems in Excel?

[Guest]

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

 

[Roger Govier]

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 Tools>Options>Calculation 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

 

[Guest]

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]

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

 

[Guest]

Hi Roger

Thank you for your continuing interest - I will elaborate on "dodgy"
answers. Cell G38 is "Dividends proposed" as a creditor in the bs, and
comprises the bf creditor+dividend proposed, from the P&L, less dividend
paid, from the cashflow. The formula is =g26+f38+g50. g26 equals 400, f38
equals 347 and g50 equals minus 347, so I would expect g38 to read 400, but
it reads 748!!!

Look forward to hearing from you!

Many thanks again

William

 

[Roger Govier]

Hi William

It sounds as though F38 has been included twice in the formula, and is a non
integer value, but the display is set to zero places of decimal.

I know you said the formula is g26+f38+g50 and that is what you intend, but
is that what is actually in the cell?? Before now, I have been "blind" to
entering a value twice in a formula!!!

Regards

Roger Govier

 

[Guest]

Sadly, Roger, that is not the case!

William

Hi William

It sounds as though F38 has been included twice in the formula, and is a non
integer value, but the display is set to zero places of decimal.

I know you said the formula is g26+f38+g50 and that is what you intend, but
is that what is actually in the cell?? Before now, I have been "blind" to
entering a value twice in a formula!!!

Regards

Roger Govier

 

[Roger Govier]

Hi William

Can't think of anything else.
If you want to send me a copy of the file I will take a look and see if I
can spot anything else.

Remove "nospam" from my address to send.

Regards

Roger Govier