limited recurring batch

[cashmire]

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
	B	C	D	E	F	G
20					Calving
21	Year	Heifers	Bulls	Pregnant	Heifers	Bulls
22	1	300	25	235	112	112
23	2	294	25	230	109	109
24	3	288	24	226	107	107
25	4	394	24	309	147	147
26	5	496	23	389	185	185
27	6	593	23	465	221	221
28	7
29	8
30	9
31	10
variables

 

[Harvey]

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]

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]

Harvey,

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

-Peter

 

[cashmire]

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.

 

[fairwinds]

This is how I guessed your constants:

Book1
	B	C	D	E	F	G	H	I
8		300						95%
9		25						95%
10
11
12
13		80%
14		50%
15		2%
16
17
18
19
20					Calving
21	Year	Heifers	Bulls	Pregnant	Heifers	Bulls
22	1	300	25	235	112	112
23	2	294	25	230	109	109
24	3	288	24	226	107	107
25	4	394	24	309	147	147
26	5	196	23	153	73	73
27	6	187	23	147	70	70
28	7	221	22	173	82	82
29	8	182	22	143	68	68
30	9	101	21	79	38	38
31	10	109	21	85	40	40
Sheet2

 

[cashmire]

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]

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]

Harvey,

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

-Peter