Iterative Calculation

[Bill]

I want to do an interative caluclation regarding cuts to
be made in service people, i.e. layoffs, to reduce costs.
This in turn reduces income as less people are able to
service customers. This creates more of a loss that
requires further cuts in staff, and so on. How do I do
it? I can do a series of columns so that A caluclates the
first cut in staff, col. B reduces revenue based on cuts
in staff calculated in col. A and gives a new cut figure,
col. C reduces revenue based on cuts in staff in col B.
and so on. But that is messy, and the problem is it
eventually starts to earn money as the other sources of
funds aren't affected by staff.

Thank you for your assistance. (please note I posted
before (Dec 1)and received no answer)

 

[bdcrisp]

I don't mean to be rude, but this doesn't sound like an excel question.

 

[Bill]

I want Excel to recalculate two cells that are related to
each other, i.e. the sum is negative, so I have to reduce
one expense cell value, but reducing that one causes the
revenue cell value to go down, creating a negative sum.
It has to be repeated and there has to be an elegant way
to do it in Excel. Why isn't it an Excel question.

-----Original Message-----

I don't mean to be rude, but this doesn't sound like an

excel question.

 

[Stephen Bullen]

Hi Bill,

I want to do an interative caluclation regarding cuts to
be made in service people, i.e. layoffs, to reduce costs.
This in turn reduces income as less people are able to
service customers. This creates more of a loss that
requires further cuts in staff, and so on. How do I do
it? I can do a series of columns so that A caluclates the
first cut in staff, col. B reduces revenue based on cuts
in staff calculated in col. A and gives a new cut figure,
col. C reduces revenue based on cuts in staff in col B.
and so on. But that is messy, and the problem is it
eventually starts to earn money as the other sources of
funds aren't affected by staff.

The usual way to set this up is the following:

1. Put a zero in A1, which we'll use as a switch to provide starting
values and turn iteration on/off
2. Enter your starting staff figure in A2
3. In A3 enter the redundant-looking formula =IF(A1=0,A2,A2)
4. In A4 and below enter the remaining formulae to work out the
revenues and redundancies and finally (say in A10) the resultant staff
figure.
5. Modify the formula in A3 to be =IF(A1=0,A2,A10)
6. Click on Tools > Options > Calculation and enable iterations
7. Change the zero in A1 to a 1 to see the formulas iterate to a
solution (or to zero if you can't afford all the redundancy cheques
<g>)

The key to it is to use the on/off switch to control the iteration.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[Charles Williams]

Hi Bill,

have you tried using circular references and Iteration? (Tools--> options-->
calculation)


Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

 

[bdcrisp]

I understand now what you are asking, but where is the negative sum
coming from? what is being summed to give you a negative value?

 

[Mike A]

From the original post:

...
But that is messy, and the problem is it
eventually starts to earn money as the other sources of
funds aren't affected by staff.
...

Did you consider that maybe the numbers are right? And at some point
your cuts will pay off and you will profit from your difficult
decisions?

Just wondering....



Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers