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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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:

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

1,152,253
Messages
5,769,051
Members
425,512
Latest member
wannabe_excel_wiz

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.

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

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