How do I resolve complex iteration problems in Excel?

W

William Sporborg

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
 
R

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
 
W

William Sporborg

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
 
R

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
 
W

William Sporborg

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
 
R

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
 
W

William Sporborg

Sadly, Roger, that is not the case!

William

Roger Govier said:
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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top