; Series Worksheet - Excel
Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out
Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Series Worksheet - Excel

VIEWS: 53 PAGES: 9

Series Worksheet document sample

More Info
  • pg 1
									Open cow's Productive Year                                                                            4 years

Yearly cost of retaining cow in inventory                                                     ($300.00)
price of replacement heifer                                                                      ($950)
market value of open cow                                                                          $400

                                                   Reproductive age
                                                              1            2           3             4              5           6
Value of calf crop                                      352.00       368.00      384.00        400.00         400.00      400.00
Variable cost of cow                                   (300.00)     (300.00)    (300.00)      (300.00)       (300.00)    (300.00)
Fertility discount - Cow has calf                        (7.64)      (53.69)     (56.03)       (58.36)        (58.36)     (58.36)
Fertility discount - Open cow                                      FALSE       FALSE         FALSE            (88.32)   FALSE
Adjustment for no calf to open cow                                 FALSE       FALSE          (341.64)     FALSE        FALSE
Adjustment to only add one fertility discount                      FALSE       FALSE         FALSE             58.36    FALSE


EQ #1 - keep cow
 - Payment series                                      FALSE       FALSE       FALSE           (300.00)         11.68      41.64
 - Discount factor                                         0.75        0.83        0.91           1.00           1.10       1.21
 - dNCF                                                    0.00        0.00        0.00        (300.00)         10.62      34.41
   = Sum of payments                                    (145.88)
   = Salvage value                                       225.79
   = TOTAL EQ #1                                          79.91


EQ #2 - single replacement heifer
 - Payment series                                         44.36       14.31       27.97          41.64          41.64      41.64
 - Discount factor                                         1.00        1.10        1.21           1.33           1.46       1.61
 - dNCF                                                   44.36       13.01       23.12          31.28          28.44      25.86
   = Sum of payments                                     248.03
   = Salvage value                                       154.22
   = Purchase price of heifer                           (950.00)
   = TOTAL EQ #2                                        (547.76)




EQ #4 - Retain Cow
                                                                                           Second half of equation 3
                                                                                               (891.45)
perpetual series of rplacements starting now            (891.45)
discounting above line to reflect starting in ?? Yrs    (503.20)
   = Retaining open cow (EQ #1)                           79.91
   = TOTAL EQ #4                                        (423.29)



EQ #3 - Replace Cow



perpetual series of replacements starting now           (891.45)
   = Salvage value of replacing cow immediately          400.00
   = TOTAL EQ #3                                        (491.45)
required rate of return                        10%
value of calf (peak performance)              $400
salvage value at end of life                  $400
Expected lifespan                                10
# of Replacements to consider                    10


           7             8             9          10          11          12          13         14
     400.00        400.00        400.00      400.00      400.00      384.00      384.00     384.00
    (300.00)      (300.00)      (300.00)    (300.00)    (300.00)    (300.00)    (300.00)   (300.00)
     (58.36)       (58.36)       (58.36)     (58.36)     (58.36)     (56.03)     (56.03)    (56.03)
   FALSE         FALSE         FALSE       FALSE       FALSE       FALSE       FALSE       FALSE
   FALSE         FALSE         FALSE       FALSE       FALSE       FALSE       FALSE       FALSE
   FALSE         FALSE         FALSE       FALSE       FALSE       FALSE       FALSE       FALSE



      41.64          41.64         41.64      41.64    FALSE       FALSE       FALSE       FALSE
       1.33           1.46          1.61       1.77        1.95        2.14        2.36      2.59
      31.28          28.44         25.86      23.50        0.00        0.00        0.00      0.00




      41.64          41.64         41.64      41.64    FALSE       FALSE       FALSE       FALSE
       1.77           1.95          2.14       2.36        2.59        2.85        3.14      3.45
      23.50          21.37         19.43      17.66        0.00        0.00        0.00      0.00
        The Replacement Model for Open Breeding Stock

              Agricultural Economics – Extension No. 2002-03
                              November 2002

    By: Kenneth H. Burdine, Gregg Ibendahl, John D. Anderson and Les Anderson



   If you did not enable macros when you started this application, please exit
              Excel and re-start the application with macros enabled.


 The directions, "Using the Replacement Model for Open Breeding Stock" are
                              available on-line at
  http://www.uky.edu/Ag/AgEcon/pubs/software/ext2002-03_directions.pdf.


   Please select the "Output" worksheet to use the Replacement Model.


       For questions or additional information, please contact Kenny Burdine, e-
                                mail:kburdine@uky.edu.

                              University of Kentucky
                       Department of Agricultural Economics
                          400 Charles E. Barnhart Bldg.
                           Lexington, KY 40546-0276

                                Phone: 859-257-5762
                                  Fax: 859-323-1913
                           http://www.uky.edu/Ag/AgEcon/


Questions about this webpage: aecwww@lsv.uky.edu. Last update: June 3, 2003.
Fundamental Questions
1)   Open Cow's Productive Year?                                              4
2)   Annual variable cost of cow in inventory?                        ($300.00)
3)   Cost of a bred replacement heifer?                               ($950.00)
4)   What can the open cow be sold for?                                $400.00
5)   What rate of return would you require on this investment?             10%
6)   What is the value of a calf at cow's peak performance?            $400.00
7)   Value of cow at end of productive life?                           $400.00
8)   Cow's expected productive life? (8-14 years)                            10
9)   Calf discount                                                       4.00%



Decision Rules
NPV of Replacement Heifer                                             ($491.45)
NPV of Keeping Cow                                                    ($423.29)


NPV of Replacement Decision                                            ($68.15)            Retain Open Cow

             Table 1. Sensitivity Analysis of Variable Costs and Cow Productive Age
                                              Open Cow's Productive Year
                               ($68.15)     2          3          4         5         6             7
             Variable        -240     ($167)      ($142)    ($102)      ($58)      ($9)          $45
             Costs           -270     ($153)      ($127)      ($85)     ($39)     $12            $68
             per             -300     ($139)      ($111)      ($68)     ($21)     $32            $91
             Year            -330     ($125)       ($96)      ($51)      ($2)     $53           $113
                             -360     ($111)       ($80)      ($34)      $17      $73           $136
                             -390      ($97)       ($65)      ($17)      $35      $94           $158

             Note: Shaded areas represent combinations of variable costs and open cow productive age where retaining the ope



             Table 2. Sensitivity Analysis of Variable Costs and Bred Replacment Heifer Cost
                                              Cost of Bred Replacement Heifer
                               ($68.15) -700        -750      -800       -850      -900       -950
             Variable        -240       $75         $40        $4       ($31)     ($67)    ($102)
             Costs           -270       $92         $57       $21       ($14)     ($50)      ($85)
             per             -300      $109         $74       $38         $3      ($33)      ($68)
             Year            -330      $126         $91       $55        $20      ($16)      ($51)
                             -360      $143        $107       $72        $37        $1       ($34)
                             -390      $160        $124       $89        $54       $18       ($17)

             Note: Shaded areas represent combinations of variable costs and open cow productive age where retaining the ope



             Table 3. Sensitivity Analysis of Bred Replacement Heifer Cost and Cow Productive Age
                                              Open Cow's Productive Year
                  ($68.15)        2        3        4            5          6          7
Cost of         -700           $78      $87     $109         $134       $161       $192
Bred            -750           $35      $47      $74         $103       $135       $171
Heifer          -800           ($9)      $8      $38          $72       $110       $151
                -850          ($52)    ($32)      $3          $41        $84       $131
                -900          ($95)    ($71)    ($33)         $10        $58       $111
                -950         ($139)   ($111)    ($68)        ($21)       $32        $91

Note: Shaded areas represent combinations of variable costs and open cow productive age where retaining the ope
 Retain Open Cow




ductive age where retaining the open cow is optimal




ductive age where retaining the open cow is optimal



oductive Age
ductive age where retaining the open cow is optimal
                                                             Calving Rates for Pregnant Cows by Age
Cow age (year)                             2.5         3.5        4.5      5.5     6.5     7.5
Pregnant to no calf                       2.17        2.78       3.23     3.53    3.68    3.68
Pregnant to live newborn calf            97.83       97.22      96.77    96.47   96.32   96.32


                                                 Estimated Fertility of Open Cows with Calf by Age
Cow age (year)                               3           4          5        6       7       8
Newborn calf at side to pregnant         81.95       80.80      79.33    77.52   75.39   72.94
Newborn calf at side to open             14.59       14.59      14.59    14.59   14.59   14.59
Newborn calf at side to cull (unsound)    1.40        1.86       2.65     3.77    5.21    6.98
Newborn calf at side to cow died          2.06        2.75       3.43     4.12    4.81    5.49


                                            Estimated Fertility of Open Cows with No Calf by Age
Cow age (year)                               3           4          5        6       7       8
Open to pregnant                         70.99       69.26      67.03    64.41   61.52   58.49
Open to open                             25.09       24.09      23.08    22.08   21.08   20.08
Open to cull (unsound)                    3.32        5.58       8.21    11.09   14.10   17.13
Open to cow dies                          0.60        1.07       1.68     2.42    3.29    4.30



Calf to calf                             79.67       78.19      76.53    74.67   72.62   70.37
calf to cull                              1.40        1.86       2.65     3.77    5.21    6.98
calf to die                               2.06        2.75       3.43     4.12    4.81    5.49
Open to calf                             69.02       67.02      64.66    62.04   59.26   56.43
Open to cull                              3.32        5.58       8.21    11.09   14.10   17.13
Open to die                               0.60        1.07       1.68     2.42    3.29    4.30
nt Cows by Age
                8.5     9.5    10.5    11.5    12.5    13.5
               3.52    3.22    2.76    2.15    1.39    0.48
              96.48   96.78   97.24   97.85   98.61   99.52


with Calf by Age
                  9      10      11      12      13
              70.15   67.04   63.59   59.83   55.73
              14.59   14.59   14.59   14.59   14.59
               9.08   11.51   14.26   17.35   20.76
               6.18    6.87    7.55    8.24    8.93


 h No Calf by Age
                  9      10      11      12      13
              55.44   52.49   49.75   47.36   45.43
              19.07   18.07   17.07   16.07   15.07
              20.04   22.72   25.05   26.90   28.15
               5.44    6.71    8.12    9.67   11.35



              67.89   65.19   62.22   59.00   55.46
               9.08   11.51   14.26   17.35   20.76
               6.18    6.87    7.55    8.24    8.93
              53.65   51.04   48.68   46.70   45.21
              20.04   22.72   25.05   26.90   28.15
               5.44    6.71    8.12    9.67   11.35

								
To top