Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I resolve complex iteration problems in Excel?
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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" wrote: 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 |
#6
|
|||
|
|||
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 William Sporborg wrote: 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" wrote: 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 |
#7
|
|||
|
|||
Sadly, Roger, that is not the case!
William "Roger Govier" wrote: 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 William Sporborg wrote: 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" wrote: 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 |
#8
|
|||
|
|||
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 William Sporborg wrote: Sadly, Roger, that is not the case! William "Roger Govier" wrote: 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 William Sporborg wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix operations with complex numbers | Excel Discussion (Misc queries) |