0.15 Fraction of Net Cash Income

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

A B C D E F

2016 HWK 5.xlsx

All Costs and Values are for December 1, 2015 Assets

Cash On Hand Jan 1, 2016 15,000

Land Value 4,127,193

Buildings fences etc 576,420 Machinery Value 195,000

Operator Withdrawals

Fixed Management Draw 50,000

Bonus if NCI > 0 0.15 Fraction of Net Cash Income

Cash Overhead Costs for the Ranch

Labor costs 5,275 Fixed Costs 41,066

Tax Depreciation Deduction 13,688

Fract Year Operating Loan 0.66

Operating Interest rate 0.0672

Interest Rate Cash Reserves 0.003 Disocunt rate 0.120

Land Loan

Interest rate 0.0546

No. of Years 30

Year started loan 1999

Amount borrowed 250,000

Machinery Loan

Interest rate 0.0672

No. of Years 5

Year started loan 2014

Amount borrowed 54,600

Cows and Bull Herd Fractions and Numbers

Number of Cows 275

Cows Culled each Year 0.1

Cows Die each year 0.015

No. Replacement Heifers 36

Number of Bulls 12

Bulls Culled each year 0.2

Bulls die each year 0.035

Calving Percentage Steer Weaning Weight

Minimun fraction 0.75 550

Middle fraction 0.9 575

Maximum fraction 0.95 610

Fractions of Animals Die, Retained and Culled

Calves die each year 0.015

Heifers retained 0.35 for replacements

Replacements Culled 0.1

Sale Weights for Cattle

Heifers to Steers 0.90 Ratio

Culled Cows 1100 Pounds

Culled Bulls 1800 Pounds

Culled Replacements 900 Pounds

Production Cash Costs $/Cow

Vet, Medicine&Supplies 22.44

Marketing & Transportation 46.19

Check off 1

Salt & Minerals 41.56

Other Costs 2.55

68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134

A B C D E F

Other Cash Costs

Property Taxes 0.00125 $tax/$Land Value

Cash Rent for Pasture 15,500

Feed fed in tons/cow

Soybean Meal Products 0.10

Hay 0.90

Cattle Price Relationships

Culled Bulls/Culled Cow 1.24 Ratio

Heifer Calves/Steer Calves 0.9788 Ratio

Replacement/Steer Calves 0.873 Ratio

Inflation Rates 2015 2016 2017 2018 2019

PPI 0.0152 0.0087 0.0084 0.0144 0.0163

Land Value 0.0237 -0.0322 -0.0172 -0.0082 -0.008

BldgValue -0.065 -0.065 -0.065 -0.065 -0.065

Machinery Value -0.075 -0.075 -0.075 -0.075 -0.075

CPI 237.0147 239.8889 246.0403 252.6371 259.0523

% Change in CPI 0.012126674 0.025642704 0.026811868 0.02539295

LONG-TERM Interest Rate 0.081 0.0857 0.0899 0.0957 0.1002

Intermediate Term Interest 0.0656 0.0694 0.0728 0.0775 0.0811

Soybean Meal Hay Steer Price Cull Cow Price

Stochastic Prices for 2016 426.02 185.83 2.01 0.92

=STOCh!B110 =STOCh!C110 =STOCh!D110 =STOCh!E110

Culled Bull Heifers Replacement

1.14 1.97 1.76

=B77*E93 =D93*B78 =B79*D93

2016

Cows 275 =B37

Cull Cows 27 =INT(B103*$B$38)

Cows Die 4 =INT(B103*$B$39)

Cows Needed 31 =SUM(B104:B105)

Repalcement Raised 33 =B138

Surplus Replacements 2 =IF(B107>B106,B107-B106,0)

Repalcements Bought 0 =IF(B107<B106,B106-B107,0)

Sale Weight Culled Cows 1100 =B57

Price Culled Cows 0.92 =E93

Receipts Cull Cows 27,352 =B111*B112*B104

Calving Fraction 0.880052886 =STOCh!O23

Calves Born 242 =INT(B115*B103)

Calves Die 3 =INT(B116*$B$51)

Heifer Calves 119 =INT((B116-B117)/2)

Heifers Retained 41 =INT(B119*$B$52)

Heifers Sold 78 =B119-B120

Heifer Weaning Wt. 519.30 =B129*$B$56

Pounds Sold 40,505 =B122*B121

Price Heifers 1.97 =C97

Receipts Heifers 79,871 =B123*B124

Bull Calves 119 =B119

Steer Calves Sold 119 =B127

Steer Weaning Weight 577 =STOCh!AB23 Pounds Sold 68,662 =B129*B128

Steer Price 2.01 =D93

Receipts for Steers 138,327 =B130*B131

Replacement Heifers

135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201

A B C D E F

Number Jan 1 36 =B40

No. Culled 3 =INT($B$53*B135)

Surplus Heifers Sold 2

No. to Enter Herd 33 =B135-B136

Sale Weight 900 =B59

Price 1.76 =D97

Receipts for Cull Replacements 7,914 =B140*B139*(B136+B137)

Bulls

No. Jan 1 12 =B41

No Die 0 =INT(B144*$B$43)

No. Culled 2 =INT(B144*$B$42)

Price for culls 1.14 =B97

Sale Weight 1800 =B58

Receipts 4,111 =B148*B146*B147

Value Cattle Herd Dec 31 2015 2016

Cows 301,774 278,583 =B103*$B$111*B112 Replacements 64,072 64,898 =B120*B139*B140 Bulls 26,720 24,666 =B144*B148*B147 Total 392,565 368,147 =SUM(C152:C154)

Feed Costs

No. of Cows 275 =B103

Quantity Hay Fed to Cows 247.5 =B158*$B$74

Price Hay 185.83 =C93

Cost of Hay 45,992 =B159*B160

Soybean Meal fed 27.5 =B158*$B$73

Price Soybean Meal 426.02 =B93

Cost of Soybeam Meal 11,716 =B164*B163

Inflate Costs of Production and Multiply by Number of Cows Where Appropriate

PPI 0.0087 =C82

Labor costs 5,320.89 =(1+B168)*$B$14 Fixed Costs 41,423.02 =(1+B168)*$B$15

Cash Rent 15,500.00 =B70

Vet, Medicine&Supplies 6,224.69 =(1+B168)*$B$62*B158 Marketing & Transportation 12,812.76 =$B$63*(1+B168)*B158 Check off 277.39 =B64*(1+B168)*B158 Salt & Minerals 11,528.43 =B65*(1+$B$168)*B158 Other Costs 707.35 =$B$66*(1+B168)*B158 Proprty Taxes 4,993 =B69*B181

Update Value of Fixed Assets

Land 3,994,297 =$B$5*(1+C83)

Buildings 538,953 =$B$6*(1+C84) Machinery 180,375 =B7*(1+C85)

Income Statement 2016 2016

Receipts

Cows 27,352 =B113

Heifers 79,871 =B125

Steers 138,327 =B132

Replacement Heifers 7,914 =B141

Bulls 4,111 =B149

Total 257,576 =SUM(B188:B192)

Expenses

Hay Costs 45,992 =B161 Soybean Meal Costs 11,716 =B165

Labor costs 5,321 =B169

Fixed Costs 41,423 =B170

Cash Rent 15,500 =B171

Vet, Medicine&Supplies 6,225 =B172

202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268

A B C D E F

Marketing & Transportation 12,813 =B173

Check off 277 =B174

Salt & Minerals 11,528 =B175 Other Costs 707 =B176 Propery Taxes 4,993 =B177 Interest Land Loan 8,545 =Loans!D29 Interest Mach Loan 2,343 =Loans!M14

Operating Loan 7,424 =SUM(B196:B208)*$B$20*$B$19

Total 174,807 =SUM(B196:B209)

Net Cash Income 82,769 =B193-B210

Cash Flow Statement 2016 =B186

Beginning Cash 15,000 =B4 Net Cash Income 82,769 =B212 Interest Earned on Reserves 1,008 =B215*$B$20 Cash Inflows 98,777 =SUM(B215:B217)

Principal Payment Land 8,580 =Loans!E29 Principal Payments Mach 10,874 =Loans!N14 Income Taxes 12,270 =Taxes!B28 Management Draw 50,606 =$B$10*(1+C87) Management Bonus 12,415 =IF(B212>0,B212*$B$11,0) Total Outflows 94,746 =SUM(B220:B224)

Ending Cash Dec 31 4,031 =B218-B225

Balance Sheet 2016 =B247

Assets

Land 3,994,297 =B181

Buildings 538,953 =B182

Machinery 180,375 =B183

Cattle 368,147 =C155

Cash 4,031 =IF(B227>0,B227,0) Total Assets 5,085,803 =SUM(B232:B236)

Debts

Land 147,925 =Loans!F29

Machinery 23,989 =Loans!O14 Cash Flow Deficit - =IF(B227<0,-B227,0)

Total 171,914 =SUM(B240:B242)

Net Worth 4,913,890 =B237-B243

Beginning Net Worth Jan 1 2016 =B214

Cash On Hand Jan 1, 2016 15000 =B4

Land Value 4127193 =B5

Buildings fences etc 576420 =B6

Machinery Value 195000 =B7

Cattle 392,565 =B155

Land Debt 156,505 =Loans!B29

Machinery Debt 34,863 =Loans!K14

Beg NW 5,114,810 =SUM(B248:B252)-SUM(B253:B254)

Net Present Value

Discount rate 0.120 =B22

Discount Factor 0.89 =1/(1+$B$258)^1

NPV (671,140) =-B255+(SUM(B223:B224)*B259)+(B245*B259) PV Ending Net Worth 4,387,401 =B245*B259

KOV

NPV (671,140) =B260

Change RNW 0.86 =B261/B255

NCI 82,769 =B212

End Cash 4,031 =B227

269

A B C D E F

P(Neg EC) 0 =IF(B227<0,1,0)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

A B C D E F G

$/ton $/ton $/Pound $/Pound Pounds/Head Soybean Meal Hay Steer Price Cull Cow Price Steer Weights

2001 159.97 96.5 0.9534 0.4439 530

2002 173.19 92.4 0.8634 0.3923 590

2003 244.22 85.5 0.9521 0.4662 715

2004 174.45 92 1.1179 0.5235 625

2005 166.12 98.2 1.2011 0.5436 465

2006 195.95 110 1.1768 0.4756 705

2007 320.42 127 1.1547 0.5212 610

2008 315.87 152 1.0762 0.5492 505

2009 296.89 108 1.0189 0.4701 640

2010 329.56 114 1.154 0.5676 590

2011 375.35 178 1.4125 0.6982 665

2012 446.49 191 1.5819 0.7668 480

2013 467.31 176 1.588 0.7638 690

2014 351.47 172 2.2507 1.0409 560

2015 283.87 148.6 2.2652 0.9976 600

Simple Regression

Soybean Meal Hay Steer Price Cull Cow Price Steer Weights Intercept -35656.24 -13634 -163.5871 -77.0189 562.1429 Slope 17.899893 6.854286 0.082124 0.038662 0.017857 R-Square 0.6395869 0.697853 0.708014 0.760742 1.02E-06 F-Ratio 23.069718 30.02548 31.52265 41.33462 1.33E-05 Prob(F) 0.0003449 0.000106 8.42E-05 2.24E-05 0.997144 S.E. 3.7267416 1.250885 0.014627 0.006014 4.894208 T-Test 4.8030947 5.479551 5.614504 6.4292 0.003649 Prob(T) 0.0002809 8.11E-05 6.38E-05 1.58E-05 0.99714

Projected Prices for 2016

Soybean Meal Hay Steer Price Cull Cow Price Steer Weights 2016 429.94114 184.2476 1.974778 0.923997 598.1429

MVEMP as Deviations from Trend

Output for Empirical Distributions with 15 Observations as Precent Deviations from Trend Unsorted Deviations from Trend

Obs. Soybean Meal Hay Steer Price Cull Cow Price Steer Weights

1 -1.47275 15.06667 0.210481 0.099835 -67.875

2 -6.152643 4.112381 0.038357 0.009573 -7.89286

3 46.977464 -9.6419 0.044933 0.044811 117.0893

4 -40.69243 -9.99619 0.128609 0.063449 27.07143

5 -66.92232 -10.6505 0.129685 0.044886 -132.946

6 -54.99221 -5.70476 0.023261 -0.06178 107.0357

7 51.577893 4.440952 -0.080963 -0.05484 12.01786

8 29.128 22.58667 -0.241587 -0.0655 -93

9 -7.751893 -28.2676 -0.381011 -0.18326 41.98214

10 7.0182143 -29.1219 -0.328035 -0.12442 -8.03571

47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92

A B C D E F G

11 34.908321 28.02381 -0.151658 -0.03249 66.94643 12 88.148429 34.16952 -0.064382 -0.00255 -118.071 13 91.068536 12.31524 -0.140406 -0.04421 91.91071 14 -42.67136 1.460952 0.44017 0.194227 -38.1071 15 -128.1713 -28.7933 0.372546 0.112265 1.875 Mean 286.742 129.4133 1.317787 0.6147 598 Slope 17.899893 6.854286 0.082124 0.038662 0.017857 Intercept 143.54286 74.57905 0.660795 0.305403 597.8571 Autocorrelation Coefficient 0.3801798 0.217748 0.640986 0.52491 -0.49573

Unsorted Deviations from Trend as a Percent of Predicted

Obs. Soybean Meal Hay Steer Price Cull Cow Price Steer Weights 1 -0.009122 0.185018 0.283316 0.290163 -0.11353 2 -0.034307 0.046579 0.046491 0.025012 -0.0132 3 0.2381711 -0.10134 0.049531 0.10634 0.195831 4 -0.189142 -0.09801 0.130001 0.137916 0.045275 5 -0.287168 -0.09785 0.121041 0.090004 -0.22234 6 -0.219143 -0.0493 0.020165 -0.11496 0.179 7 0.191852 0.036235 -0.065522 -0.0952 0.020097 8 0.1015826 0.174531 -0.183328 -0.10656 -0.15552 9 -0.025446 -0.20744 -0.272168 -0.28049 0.070202 10 0.0217591 -0.20348 -0.221341 -0.1798 -0.01344 11 0.1025383 0.186855 -0.096958 -0.04446 0.111941 12 0.24599 0.217876 -0.039108 -0.00331 -0.19742 13 0.2420481 0.075238 -0.081235 -0.05472 0.153674 14 -0.108264 0.008567 0.243116 0.2294 -0.06371 15 -0.311064 -0.16231 0.196838 0.126805 0.003135

Correlation Matrix

Soybean Meal Hay Steer Price Cull Cow Price Steer Weights Soybean Meal 1 0.623173 -0.576935 -0.3989 0.102011

Hay 1 -0.054948 0.107441 -0.25962

Steer Price 1 0.95403 -0.16883

Cull Cow Price 1 -0.25069

Steer Weights 1

Sorted Deviations from Trend as a Percent of Predicted

F(x) Soybean Meal Hay Steer Price Cull Cow Price Steer Weights

0 -0.311095 -0.20746 -0.272195 -0.28052 -0.22236

0.033333 -0.311064 -0.20744 -0.272168 -0.28049 -0.22234

0.1 -0.287168 -0.20348 -0.221341 -0.1798 -0.19742

0.166667 -0.219143 -0.16231 -0.183328 -0.11496 -0.15552

0.233333 -0.189142 -0.10134 -0.096958 -0.10656 -0.11353

0.3 -0.108264 -0.09801 -0.081235 -0.0952 -0.06371

0.366667 -0.034307 -0.09785 -0.065522 -0.05472 -0.01344

0.433333 -0.025446 -0.0493 -0.039108 -0.04446 -0.0132

93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110

A B C D E F G

0.5 -0.009122 0.008567 0.020165 -0.00331 0.003135 0.566667 0.0217591 0.036235 0.046491 0.025012 0.020097 0.633333 0.1015826 0.046579 0.049531 0.090004 0.045275 0.7 0.1025383 0.075238 0.121041 0.10634 0.070202 0.766667 0.191852 0.174531 0.130001 0.126805 0.111941 0.833333 0.2381711 0.185018 0.196838 0.137916 0.153674 0.9 0.2420481 0.186855 0.243116 0.2294 0.179 0.966667 0.24599 0.217876 0.283316 0.290163 0.195831 1 0.2460146 0.217897 0.283344 0.290192 0.19585

MV EMP as Percent of Trend

CUSD 0.5 0.5 0.5 0.5 0.5

Stoch Dev -0.009122 0.008567 0.020165 -0.00331 0.003135 Y-Hat 2016 429.94114 184.2476 1.974778 0.923997 598.1429

Soybean Meal Hay Steer Price Cull Cow Price Steer Weights

Stoch 2016 426.01904 185.826 2.0146 0.920936 600.0179