{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Introduction","Comparison","Futures-options","LRP"],"not_rendered":{"charts":1,"images":13,"macros":1,"external_links":1,"defined_names":4,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Introduction","index":0,"n_rows":55,"n_cols":12,"rows_kept":55,"cut":false,"hidden":false,"merged":[[48,1,48,3],[48,6,48,8],[52,1,53,11],[54,1,55,11],[30,1,30,4],[32,1,36,11],[41,2,41,4],[41,8,41,11],[42,1,43,11],[47,15,47,16],[4,1,5,8],[4,13,5,23],[7,2,7,3],[7,4,7,5],[7,13,8,13],[28,1,29,11]]}
[]
[]
[null,"KSU-Feeder Cattle Risk Management Tool"]
[]
[null,"An Excel spreadsheet for evaluating feeder cattle risk management strategies"]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Version- 12.14.2020"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"INTRODUCTION"]
[null,"This spreadsheet was developed as a decision-aid tool for producers interested in managing feeder cattle price risk. It can be used to compare the Expected Net Selling Price using alternative risk management tools available for pricing feeder cattle."]
[]
[null,"INSTRUCTIONS FOR THE USER:"]
[null,"Be sure to \"Enable Content\" and \"Enable Macros\" for the spreadsheet to function correctly."]
[null,"In the Comparison sheet all blue numbers are input by the user.  All black numbers are calculated using data provided by the user.  The spreadsheet automatically recalculates every time an additional input is entered.  Thus, it is important to wait until all data have been entered and reviewed before interpreting any of the calculated results (i.e., black numbers). Most of the input cells (i.e., blue number) have a red diamond in the upper right hand corner of the cell.  By moving your mouse cursor over this diamond, a brief description of the input will be displayed on the screen.  "]
[]
[]
[]
[]
[]
[]
[null,"FOR MORE INFORMATION:"]
[null,"To learn more about managing price risk and using LRP insurance visit www.AgManager.info or click on the following links:"]
[null,null,"LRP Insurance Information",null,null,null,null,null,"Price Risk Management Information"]
[null,"Updated by:"]
[]
[null,"Glynn T. Tonsor, Ph.D."]
[null,"Agricultural Economist"]
[null,"Department of Agricultural Economics"]
[null,"Kansas State University"]
[null,"gtonsor@k-state.edu"]
[null,"785-532-1518"]
[]
[null,"ACKNOWLEDGEMENTS:"]
[null,"This spreadsheet was originally developed by Kevin Dhuyvetter, Ph.D., Agricultural Economist, Kanasas State University.  The authors gratefully acknowledge funding provided by USDA's Risk Management Agency (RMA) for initial development of this feeder cattle risk management tool."]
[]
[null,"Copyright 2020 AgManager.info, K-State Department of Agricultural Economics"]

{"sheet":"Comparison","index":1,"n_rows":117,"n_cols":17,"rows_kept":117,"cut":false,"hidden":false,"merged":[[11,1,11,6],[36,1,36,5],[37,1,37,3],[41,1,41,5],[39,1,39,7],[3,1,3,6],[8,1,8,6],[18,1,18,3],[18,5,18,7],[4,1,4,6],[5,1,5,6],[6,1,6,6],[14,1,14,6],[15,1,15,6],[16,1,16,6],[13,1,13,6],[12,1,12,6],[7,1,7,6],[10,1,10,6]]}
[]
[null,"Comparison of Alternative Risk Management Strategies for Pricing Feeder Cattle"]
[]
[null,"Current date",null,null,null,null,null,"2020-12-14",null,null,null,"Futures","LRP","Put","Call","Put","Call"]
[null,{"$f":"\"Number of head (number of head to match futures contract = \"&FIXED(L117/H6,0)&\")\"","v":"Number of head (number of head to match futures contract = 83)"},null,null,null,null,null,83,null,"Number of contracts",null,1,83,1,1,1,1]
[null,{"$f":"\"Expected weight at time of sale, lbs/head -- \"&IF(H6<L115,\"(use weight 1 for LRP)\",\"(use weight 2 for LRP)\")","v":"Expected weight at time of sale, lbs/head -- (use weight 2 for LRP)"},null,null,null,null,null,600,null,"Strike price(s), $/cwt",null,null,140.16,152,150,146,146]
[null,"Pounds of cattle expected to sell in cash market, lbs",null,null,null,null,null,{"$f":"H5*H6","v":49800},null,"Premium, $/cwt",null,null,{"$f":"VLOOKUP(M6,B105:D117,3,1)","v":6.0910000000000002},{"$f":"VLOOKUP(N6,B22:D34,2,1)","v":11.75},{"$f":"VLOOKUP(O6,B22:D34,3,1)","v":7.5},{"$f":"VLOOKUP(P6,B22:D34,2,1)","v":8.7249999999999996},{"$f":"IF(Q6=0,0,VLOOKUP(Q6,B22:D34,3,1))","v":9.5500000000000007}]
[null,"Expected sale date",null,null,null,null,null,"2021-10-13",null,null,null,null,{"$f":"IF(M5>H5,\"Cannot buy LRP for more cattle than owned!\",\"\")","v":""},null,""]
[null,"Feeder cattle type",null,null,null,null,null,"Steers",null,null,"Expected Net Selling Prices"]
[null,null,null,null,null,null,null,null,null,"Futures",null,null,null,null,"Hedge"]
[null,"CME futures contract for hedging (Jan, Mar, Apr, May, Aug, Sep, Oct, Nov)",null,null,null,null,null,"Oct",null,"price","Cash","Hedge","LRP","Put","&Call","Put&Call"]
[null,"Commission for roundtrip (sell and buy) futures trade, $/contract",null,null,null,null,null,60,null,125,{"$f":"J12+H$15","v":138.09},{"$f":"IF(L$5=0,\"n/a\",(K12/100*H$7+((H$14-J12)*L$117/100-H$12)*L$5)/H$7*100)","v":160.78578313253013},{"$f":"IF(OR(M$5=0,M$5>H$5),\"n/a\",(K12/100*H$7-M$7*H$6/100*M$5+IF(M$6>J12*L$113,(M$6-J12*L$113)*H$6/100*M$5,0))/H$7*100)","v":147.15899999999999},{"$f":"IF(N$5=0,\"n/a\",(K12/100*H$7-(N$7*L$117/100+H$13)*N$5+IF(N$6>J12,((N$6-J12)*L$117/100-H$13)*N$5))/H$7*100)","v":153.28076305220887},{"$f":"IF(O$5=0,\"n/a\",(K12/100*H$7+((H$14-J12)*L$117/100-H$12)*L$5-(O$7*L$117/100+H$13)*O$5+IF(J12>O$6,((J12-O$6)*L$117/100-H$13)*O$5,0))/H$7*100)","v":153.19542168674701},{"$f":"IF(SUM(P$5:Q$5)=0,\"n/a\",(K12/100*H$7-(P$7*L$117/100+H$13)*P$5+(Q$7*L$117/100-H$13)*Q$5+IF(P$6>J12,((P$6-J12)*L$117/100-H$13)*P$5)-IF(Q$6<J12,((J12-Q$6)*L$117/100+H$13)*Q$5))/H$7*100)","v":159.82192771084337},{"$f":"MAX(K12:P12)","v":160.78578313253013}]
[null,"Commission to buy or sell options contract, $/contract",null,null,null,null,null,30,null,{"$f":"+J12+J$21","v":130},143.09,{"$f":"IF(L$5=0,\"n/a\",(K13/100*H$7+((H$14-J13)*L$117/100-H$12)*L$5)/H$7*100)","v":160.76570281124501},{"$f":"IF(OR(M$5=0,M$5>H$5),\"n/a\",(K13/100*H$7-M$7*H$6/100*M$5+IF(M$6>J13*L$113,(M$6-J13*L$113)*H$6/100*M$5,0))/H$7*100)","v":147.15899999999999},{"$f":"IF(N$5=0,\"n/a\",(K13/100*H$7-(N$7*L$117/100+H$13)*N$5+IF(N$6>J13,((N$6-J13)*L$117/100-H$13)*N$5))/H$7*100)","v":153.26068273092369},{"$f":"IF(O$5=0,\"n/a\",(K13/100*H$7+((H$14-J13)*L$117/100-H$12)*L$5-(O$7*L$117/100+H$13)*O$5+IF(J13>O$6,((J13-O$6)*L$117/100-H$13)*O$5,0))/H$7*100)","v":153.17534136546186},{"$f":"IF(SUM(P$5:Q$5)=0,\"n/a\",(K13/100*H$7-(P$7*L$117/100+H$13)*P$5+(Q$7*L$117/100-H$13)*Q$5+IF(P$6>J13,((P$6-J13)*L$117/100-H$13)*P$5)-IF(Q$6<J13,((J13-Q$6)*L$117/100+H$13)*Q$5))/H$7*100)","v":159.80184738955825},{"$f":"MAX(K13:P13)","v":160.76570281124501}]
[null,{"$f":"\"Futures price -- \"&H11&\" CME feeder cattle contract, $/cwt*\"","v":"Futures price -- Oct CME feeder cattle contract, $/cwt*"},null,null,null,null,null,147.72499999999999,null,135,148.09,{"$f":"IF(L$5=0,\"n/a\",(K14/100*H$7+((H$14-J14)*L$117/100-H$12)*L$5)/H$7*100)","v":160.74562248995986},{"$f":"IF(OR(M$5=0,M$5>H$5),\"n/a\",(K14/100*H$7-M$7*H$6/100*M$5+IF(M$6>J14*L$113,(M$6-J14*L$113)*H$6/100*M$5,0))/H$7*100)","v":147.15899999999999},153.24060240963857,153.15526104417671,159.78176706827313,160.74562248995986]
[null,{"$f":"\"Expected basis (cash - \"&H11&\" CME futures), $/cwt#\"","v":"Expected basis (cash - Oct CME futures), $/cwt#"},null,null,null,null,null,13.09,null,140,153.09,160.72554216867468,{"$f":"IF(OR(M$5=0,M$5>H$5),\"n/a\",(K15/100*H$7-M$7*H$6/100*M$5+IF(M$6>J15*L$113,(M$6-J15*L$113)*H$6/100*M$5,0))/H$7*100)","v":147.15899999999996},153.22052208835339,153.13518072289156,159.76168674698795,160.72554216867468]
[null,"LRP expected ending value for relevant time period^",null,null,null,null,null,147.46299999999999,null,145,158.09,160.70546184738953,151.999,153.20044176706827,153.11510040160641,159.7416064257028,160.70546184738953]
[null,"End date for LRP contract",null,null,null,null,null,"2021-11-05",null,150,163.09,160.68538152610444,156.99900000000002,153.18036144578315,153.09502008032129,159.72152610441768,163.09]
[null,null,null,null,null,null,null,null,null,155,168.09,160.66530120481929,161.99900000000002,156.23257028112451,158.03477911646587,159.70144578313256,168.09]
[null,"CME feeder cattle option premiums",null,null,null,"LRP coverage levels and premiums",null,null,null,160,173.09,160.64522088353414,166.999,161.23257028112451,163.03477911646587,159.68136546184741,173.09]
[null,"Strike price","Put","Call",null,"Coverage Level",null,"LRP cost",null,165,178.09,160.62514056224899,171.999,166.23257028112448,168.03477911646584,159.66128514056223,178.09]
[null,"$/cwt","$/cwt","$/cwt",null,"$/cwt","-%-   ","$/cwt",null,5,"<= futures price increment",null,null,null,null,null,"Signifies maximum price in row   "]
[null,{"$f":"B23-2","v":136},null,null,null,146.16,{"$f":"F22/H$16","v":0.99116388517797682},9.39]
[null,138,5.625,14.45,null,{"$f":"ROUND(F22-M$113,3)","v":144.16},{"$f":"F23/H$16","v":0.97760116096919225},8.2370000000000001,null,null,"Cash","Futures Hedge",{"$f":"\"LRP, ($\"&FIXED(M6,0)&\")\"","v":"LRP, ($140)"},{"$f":"\"Put, ($\"&FIXED(N6,0)&\")\"","v":"Put, ($152)"},{"$f":"\"Hedge & Call, ($\"&FIXED(O6,0)&\")\"","v":"Hedge & Call, ($150)"},{"$f":"\"Put & Call, ($\"&FIXED(P6,0)&\"/$\"&FIXED(Q6,0)&\")\"","v":"Put & Call, ($146/$146)"}]
[null,140,6.3,13.125,null,{"$f":"ROUND(F23-M$113,3)","v":142.16},0.96403843676040768,7.1159999999999997]
[null,142,7.0750000000000002,11.9,null,140.16,0.95047571255162311,6.0910000000000002]
[null,144,7.875,10.7,null,138.16,0.93691298834283854,5.1749999999999998]
[null,146,8.7249999999999996,9.5500000000000007,null,136.16,0.92335026413405397,4.4109999999999996]
[null,{"$f":"EVEN(ROUND(H14,0))","v":148},9.6750000000000007,8.5,null,134.16,0.9097875399252694,3.702]
[null,{"$f":"B28+2","v":150},10.675000000000001,7.5,null,132.16,0.89622481571648482,3.1389999999999998]
[null,152,11.75,6.5750000000000002,null,130.16,0.88266209150770025,2.64]
[null,154,12.925000000000001,5.75,null,128.16,0.86909936729891568,2.2250000000000001]
[null,156,14.15,4.9749999999999996,null,126.16,0.85553664309013111]
[null,158,null,null,null,124.16,0.84197391888134654]
[null,160,null,null,null,{"$f":"ROUND(F33-M$113,3)","v":122.16},0.82841119467256197]
[]
[null," # Websites with information on feeder cattle basis"]
[null,"http://www.agmanager.info/livestock/marketing/graphs/"]
[null,"http://www.beefbasis.com/"]
[null," * Website with futures prices and option premium quotes"]
[null,"http://www.cmegroup.com/trading/agricultural/livestock/feeder-cattle_quotes_globex_options.html"]
[null," ^ Website with LRP coverage levels and premiums"]
[null,"https://public.rma.usda.gov/livestockreports/main.aspx "]
[]
[null,"FeederCattleRiskMgmtTool.xls"]
[null,null,null,null,null,"Updated by:","Glynn T. Tonsor, PhD"]
[null,null,null,null,null,"Developed by:","Kevin C. Dhuyvetter, PhD"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Data/information used in formulas above -- do not delete!"]
[]
[null,"LRP coverage level",null,"LRP cost",null,null,null,null,null,null,null,"Price","Price"]
[null,"$/cwt","-%-   ","$/cwt",null,"Feeder cattle type",null,"Type",null,"Weight","Factor","Adj.","Inc."]
[null,{"$f":"F34","v":122.16},{"$f":"B105/H$16","v":0.82841119467256197},{"$f":"IF(H34=0,\"n/a\",H34)","v":"n/a"},null,"Steers","Weight 1",{"$f":"IF(H$9=F105,1,0)","v":1},null,{"$f":"IF(H$6<L$115,1,0)","v":0},{"$f":"H105*J105","v":0},1.1000000000000001,2.2000000000000002]
[null,{"$f":"F33","v":124.16},0.84197391888134654,{"$f":"IF(H33=0,\"n/a\",H33)","v":"n/a"},null,"Steers","Weight 2",1,null,{"$f":"IF(H$6>=L$115,1,0)","v":1},{"$f":"H106*J106","v":1},1,2]
[null,{"$f":"F32","v":126.16},0.85553664309013111,{"$f":"IF(H32=0,\"n/a\",H32)","v":"n/a"},null,"Heifers","Weight 1",0,null,{"$f":"IF(H$6<L$115,1,0)","v":0},0,1,2]
[null,{"$f":"F31","v":128.16},0.86909936729891568,{"$f":"IF(H31=0,\"n/a\",H31)","v":2.2250000000000001},null,"Heifers","Weight 2",0,null,{"$f":"IF(H$6>=L$115,1,0)","v":1},0,0.9,1.8]
[null,{"$f":"F30","v":130.16},0.88266209150770025,{"$f":"IF(H30=0,\"n/a\",H30)","v":2.64},null,"Braham","Weight 1",0,null,{"$f":"IF(H$6<L$115,1,0)","v":0},0,1,2]
[null,{"$f":"F29","v":132.16},0.89622481571648482,{"$f":"IF(H29=0,\"n/a\",H29)","v":3.1389999999999998},null,"Braham","Weigh 2",0,null,{"$f":"IF(H$6>=L$115,1,0)","v":1},0,0.9,1.8]
[null,{"$f":"F28","v":134.16},0.9097875399252694,{"$f":"IF(H28=0,\"n/a\",H28)","v":3.702},null,"Dairy","Weight 1",0,null,{"$f":"IF(H$6<L$115,1,0)","v":0},0,1,1.7]
[null,{"$f":"F27","v":136.16},0.92335026413405397,{"$f":"IF(H27=0,\"n/a\",H27)","v":4.4109999999999996},null,"Dairy","Weight 2",0,null,{"$f":"IF(H$6>=L$115,1,0)","v":1},0,0.8,1.6]
[null,{"$f":"F26","v":138.16},0.93691298834283854,{"$f":"IF(H26=0,\"n/a\",H26)","v":5.1749999999999998},null,"LRP adjustment factor used in formulas",null,null,null,null,null,{"$f":"SUMPRODUCT(K105:K112,L105:L112)","v":1},{"$f":"SUMPRODUCT(K105:K112,M105:M112)","v":2}]
[null,{"$f":"F25","v":140.16},0.95047571255162311,{"$f":"IF(H25=0,\"n/a\",H25)","v":6.0910000000000002}]
[null,{"$f":"F24","v":142.16},0.96403843676040768,{"$f":"IF(H24=0,\"n/a\",H24)","v":7.1159999999999997},null,"Minimum weight for Wt2",null,null,null,null,null,600]
[null,{"$f":"F23","v":144.16},0.97760116096919225,{"$f":"IF(H23=0,\"n/a\",H23)","v":8.2370000000000001},null,"Maximum weight for Wt2",null,null,null,null,null,900]
[null,{"$f":"F22","v":146.16},0.99116388517797682,{"$f":"IF(H22=0,\"n/a\",H22)","v":9.39},null,"Pounds per feeder cattle futures contract",null,null,null,null,null,50000]

{"sheet":"Futures-options","index":2,"n_rows":49,"n_cols":2,"rows_kept":49,"cut":false,"hidden":false,"merged":[[47,1,47,12],[1,1,1,12],[48,1,48,12],[2,1,2,12]]}
[]
[null,"Example screen from CME website showing prices for 7/3/2019 for feeder cattle futures contracts"]
[null,"http://www.cmegroup.com/trading/agricultural/livestock/feeder-cattle.html"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Example screen from CME website showing option premiums for 7/3/2019 for OCT 2019 feeder cattle contract"]
[null,"http://www.cmegroup.com/trading/agricultural/livestock/feeder-cattle_quotes_globex_options.html"]

{"sheet":"LRP","index":3,"n_rows":41,"n_cols":9,"rows_kept":41,"cut":false,"hidden":false,"merged":[[1,7,1,14],[40,8,40,14]]}
[]
[null,"Example INPUT screen for LRP premiums",null,null,null,null,null,"https://public.rma.usda.gov/livestockreports/main.aspx"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Example OUTPUT screen for LRP premiums",null,null,null,null,null,null,"https://public.rma.usda.gov/livestockreports/main.aspx"]