Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Problem (2000)

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula Problem (2000)

    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

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problem (2000)

    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula Problem (2000)

    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

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problem (2000)

    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.

  6. #6
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problem (2000)

    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!

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula Problem (2000)

    [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>

  8. #8
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problem (2000)

    aha! thanks for that

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •