; Series Worksheet - Excel
Documents
User Generated
Resources
Learning Center
Your Federal Quarterly Tax Payments are due April 15th

# Series Worksheet - Excel

VIEWS: 53 PAGES: 9

Series Worksheet document sample

• 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.

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/

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