Formula Problem (2000)

[Chance1234]

I am trying to work out how many cows there are each year of a breeding age.

Now i have something liket this table

YEAR No of C Births BreedP C
1 1
2 2
3 1
4 2
5 2
6 1
7 2
8 2
9 1
10 2
11 2
12 1
13 2
14 2
15 1

No of C births is bsed on gestation periods etc etc, what i wnat to do is say im starting with a population of 100 cows of breeding age i have each year. in the first year 64 cows are born, now for the next year i can expect 128 cows born as my newly born cows wont be of breeding age yet. as we move down through the years in two years time my cows born in year one are going to be old enough.

what formula can i use to work out what my breeding population is for each year ?
constraints are
cow must be 2 or older and less that 9 years old.


ps i already know the formula for working out the populatioon grow , just need to work out what population to base that on

[sdckapr]

I don't thinnk you provide us with enough info (maybe it is "understood" by you, but this chemist knows nothing about cow breeding)

What is the age distribution of the 100? If some of them are 9 they will no longer be of breeding age next year.
Why did only 64% give birth (Should we always assume that 64% of breeding population will give birth)
What is gestation period?
Do we assume that they all get impregnated and give birth on same day or is there a distribution

Steve

[Chance1234]

the 100 cows have all been bought at the start of their breeding life span.

gestation period is number of days a cow is pregnant which is 220

the 64% is derived from the following

the farmer has the ideal breeding ratio which is 1 to 3 males per female, 95 % of those females will fall pregant and there will be a 10% failure ratio

pesume that they all give birth on the same day

[sdckapr]

If you have 100 cows and 95% get pregnant you have 95 pregnancies. If 10% fail you have 85-86 births. If 50% born are female you only get 43 cows.

How does that get 64%? Or do you get 75% females born (on average) instead of 50%?

How does the 1-3 males/female work into the equation (or is that just the ration that gives the 95% an 10%)?

Steve

[Chance1234]

100 cows with a 1 to 3 male to female ratio,
so only 75 cows can get pregnant of which 95 % of them do
and with a 10 % failure rate you end up with 64.

[Chance1234]

ps at this stage im ignoring the male/female split in the births. basically another part of the model im working on is the farmer will ensure the 1 to 3 ratio by selling off extra males born. but thats another problem!

[sdckapr]

[oops]Ahh, I was thinking 1-3 Males / 1 female not 1 male / 3 females (which makes more sense)

How about a simulation?
Check out the attached table for setup:
B1-B2 has the percentages (95% pregnant, 90% successfull if pregnant)
B3 has female births (equal male and female)
B4 is calc:
=B1*B2*B3
So 43% of the females will birth females( so 75 females give 64 total = 32 male and 32 female)

A9-A19 is the years
B8-L8 gives the Ages of the females
B9-L9 is filled in for the start (75 females at 2 years old) no other females.
B10 has the formula:
<pre>=INT($B$4*SUM(D9:K9)+0.5)</pre>

Which calcs the number of births based on the percentage in B4 and the number of females of breeding age
This is copied to B11:B19 (and beyond)

C10 has:
<pre>=B9</pre>

Which increments the cows 1 year and "moves them over in age"
C10 is copied to C10:K19

Since you only cared about "breeding age and younger" I made a col for >9. In col L10 enter:
<pre>=K9+L9</pre>


This adds to the current >9 the number of 9 yr olds. Copyu it to L11:L19

You can create a similar table for males if desired and watch the F/M ratio change. You will also have to account somewhere for sales, deaths, etc.

You could just keep the similated values and add the true values over them year by year to you have the "real" and the projections.

Steve


<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center>G</td><td align=center>H</td><td align=center>I</td><td align=center>J</td><td align=center>K</td><td align=center>L</td><td align=center valign=bottom>1</td><td align=right valign=bottom>Get Pregnant</td><td align=right valign=bottom>0.95</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>2</td><td align=right valign=bottom>Successful Pregnancy</td><td align=right valign=bottom>0.9</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>3</td><td align=right valign=bottom>Females Born</td><td align=right valign=bottom>0.5</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>4</td><td align=right valign=bottom>Overall Born/year</td><td align=right valign=bottom>0.4275</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>5</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>6</td><td align=right valign=bottom></td><td valign=bottom>Females</td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td align=center valign=bottom>7</td><td align=right valign=bottom></td><td valign=bottom> Age of Cows</td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td valign=bottom></td><td align=center valign=bottom>8</td><td align=right valign=bottom>Year</td><td align=center valign=bottom>0</td><td align=center valign=bottom>1</td><td align=center valign=bottom>2</td><td align=center valign=bottom>3</td><td align=center valign=bottom>4</td><td align=center valign=bottom>5</td><td align=center valign=bottom>6</td><td align=center valign=bottom>7</td><td align=center valign=bottom>8</td><td align=center valign=bottom>9</td><td align=center valign=bottom>>9</td><td align=center valign=bottom>9</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>75</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=center valign=bottom>10</td><td align=right valign=bottom>1</td><td align=right valign=bottom>32</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>75</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=center valign=bottom>11</td><td align=right valign=bottom>2</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>75</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=center valign=bottom>12</td><td align=right valign=bottom>3</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>75</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=center valign=bottom>13</td><td align=right valign=bottom>4</td><td align=right valign=bottom>46</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>75</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=center valign=bottom>14</td><td align=right valign=bottom>5</td><td align=right valign=bottom>59</td><td align=right valign=bottom>46</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>75</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=center valign=bottom>15</td><td align=right valign=bottom>6</td><td align=right valign=bottom>73</td><td align=right valign=bottom>59</td><td align=right valign=bottom>46</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>75</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=center valign=bottom>16</td><td align=right valign=bottom>7</td><td align=right valign=bottom>93</td><td align=right valign=bottom>73</td><td align=right valign=bottom>59</td><td align=right valign=bottom>46</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>75</td><td align=right valign=bottom>0</td><td align=center valign=bottom>17</td><td align=right valign=bottom>8</td><td align=right valign=bottom>118</td><td align=right valign=bottom>93</td><td align=right valign=bottom>73</td><td align=right valign=bottom>59</td><td align=right valign=bottom>46</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>75</td><td align=center valign=bottom>18</td><td align=right valign=bottom>9</td><td align=right valign=bottom>117</td><td align=right valign=bottom>118</td><td align=right valign=bottom>93</td><td align=right valign=bottom>73</td><td align=right valign=bottom>59</td><td align=right valign=bottom>46</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>0</td><td align=right valign=bottom>75</td><td align=center valign=bottom>19</td><td align=right valign=bottom>10</td><td align=right valign=bottom>157</td><td align=right valign=bottom>117</td><td align=right valign=bottom>118</td><td align=right valign=bottom>93</td><td align=right valign=bottom>73</td><td align=right valign=bottom>59</td><td align=right valign=bottom>46</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>32</td><td align=right valign=bottom>75</td></table>

[Chance1234]

aha! thanks for that