# limited recurring batch

#### cashmire

##### New Member
Hi,

I could use some help on this one. I'm working on the headcount forecast for the breeding operation. In short, they start with 300 animals which get impregnated. The offspring (50% male, 50% female) get returned into the breeding programme after 2yrs (female only). After 5yrs the original batch get send to the slaughter. I'm running into problems in year 5. How to run a formula that automates the elimination of the original batch while continue to add the new additions? Here is my preliminary layout...
Book1
BCDEFG
20Calving
21YearHeifersBullsPregnantHeifersBulls
22130025235112112
23229425230109109
24328824226107107
25439424309147147
26549623389185185
27659323465221221
287
298
309
3110
variables

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Harvey

##### Well-known Member
the problem is that you have never stored the "original" herd. You have to add somewhere what is the number of animals of the original herd.
Also, what is in C15 and C13?

#### fairwinds

##### MrExcel MVP
Hi,

As I see you posted this before and had no luck, I'll make a guess:

Keep your layout but:

C26: =(C25+F23)-(C25*C\$15)-C22

C27: =(C26+F24)-(C26*C\$15)-F22 then dragged down with the rest of the formulas.

#### cashmire

##### New Member
Harvey,

in the cells are some constants which I was unable to post. Mainly % for mortality, etc.

-Peter

#### cashmire

##### New Member
Fairwinds,

thanks for your feedback. I tried it and unfortunately it didn't bring the results I was looking for. Problem is that I have problems with the constants which I am unable to paste for some funny reason.

#### cashmire

##### New Member
Fairwinds,

you were on the mark with your assumptions but the problem that I have is the following that I have to take into account a mortality rate of 2% each year, thus my initial batch of 300 reduces and after 5 years is not 300 anymore. I also have a mortality rate for the calfs. cell values:
C13 "calving ratio" 80%
C14 "Calf mortality" 5%
C15 "mortality" 2%

-Peter

#### Harvey

##### Well-known Member
make it:

=(C25+F23)-(C25*C\$15)-(C22*(1-((1-C\$15)^5)))

this subtracts the mortality of 2% for those 5 periods (or survival of 98%^5)

#### cashmire

##### New Member
Harvey,

thanks very much. I had no experience working with the "power to" formula but this seems to work fine. Appreciate your help!

-Peter

Replies
1
Views
10K

1,182,044
Messages
5,933,384
Members
436,891
Latest member
DanDan

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back