formula problem

[cashmire]

Can anyone help me with this one. I'm trying to figure this one out. See my spreadsheet. After 5yrs my initial number of animals are going to the slaughter so need to take them out. Any suggestions?

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

 

[Oaktree]

Can you give an example (maybe hardcoded) of the results you are expecting?

 

[cashmire]

Oaktree,

what I am expecting to come out would be the following. After year 5 my initial number of animals 300 would have depreciated to about 272, taking the yearly mortality of 2% into account. So in year 6 these get taken to the slaughterhouse. After year 8 the heifers born in year 1 should be going to the slaughterhouse since they have now been reproducing for 5yrs. in year 9, the animals born in year 2 go to the slaughterhouse and so on...

 

[Oaktree]

Not too sure, but maybe:

C28 =(C27+F25)-(C22*(1-$C$15)^5)-((C27-(C22*(1-$C$15)^5))*C$15)

C29 =(C28+F26)-(C23*(1-$C$15)^5)-((C28-(C23*(1-$C$15)^5))*C$15)-(F22*(1-$C$15)^7)