{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Introduction","Comparison","Futures-options","LRP"],"not_rendered":{"charts":1,"images":9,"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":[[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],[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]]}
[]
[]
[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- 2.11.2023"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[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 2023 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,"2023-02-11",null,null,null,"Futures","LRP","Put"]
[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 = 91)"},null,null,null,null,null,90,null,"Number of contracts",null,1,90,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 1 for LRP)"},null,null,null,null,null,550,null,"Strike price(s), $/cwt",null,null,225.38,204]
[null,"Pounds of cattle expected to sell in cash market, lbs",null,null,null,null,null,{"$f":"H5*H6","v":49500},null,"Premium, $/cwt",null,null,{"$f":"VLOOKUP(M6,B105:D117,3,1)","v":4.0599999999999996},{"$f":"VLOOKUP(N6,B22:D34,2,1)","v":3.95}]
[null,"Expected sale date",null,null,null,null,null,"2023-10-18",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,"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"]
[null,"Commission for roundtrip (sell and buy) futures trade, $/contract",null,null,null,null,null,75,null,180,{"$f":"J12+H$15","v":216.21},{"$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":249.08878787878791},{"$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":239.52999999999997},{"$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":236.30090909090913},null,null,{"$f":"MAX(K12:P12)","v":249.08878787878791}]
[null,"Commission to buy or sell options contract, $/contract",null,null,null,null,null,40,null,{"$f":"+J12+J$21","v":185},221.21,249.03828282828283,{"$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":239.02999999999994},{"$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":236.25040404040405},null,null,{"$f":"MAX(K13:P13)","v":249.03828282828283}]
[null,{"$f":"\"Futures price -- \"&H11&\" CME feeder cattle contract, $/cwt*\"","v":"Futures price -- Oct CME feeder cattle contract, $/cwt*"},null,null,null,null,null,212.7,null,190,226.21,248.98777777777781,238.53,236.19989898989903,null,null,248.98777777777781]
[null,{"$f":"\"Expected basis (cash - \"&H11&\" CME futures), $/cwt#\"","v":"Expected basis (cash - Oct CME futures), $/cwt#"},null,null,null,null,null,36.21,null,195,231.21,248.93727272727273,238.02999999999997,236.14939393939395,null,null,248.93727272727273]
[null,"LRP expected ending value for relevant time period^",null,null,null,null,null,232.75,null,200,236.21,248.88676767676765,237.52999999999997,236.09888888888889,null,null,248.88676767676765]
[null,"End date for LRP contract",null,null,null,null,null,"2023-10-06",null,205,241.21,248.83626262626265,237.15,237.13929292929294,null,null,248.83626262626265]
[null,null,null,null,null,null,null,null,null,210,246.21,248.78575757575757,242.15,242.13929292929294,null,null,248.78575757575757]
[null,"CME feeder cattle option premiums",null,null,null,"LRP coverage levels and premiums",null,null,null,215,251.21,248.73525252525255,247.15000000000003,247.13929292929296,null,null,251.21]
[null,"Strike price","Put","Call",null,"Coverage Level",null,"LRP cost",null,220,256.20999999999998,248.68474747474744,252.14999999999998,252.13929292929288,null,null,256.20999999999998]
[null,"$/cwt","$/cwt","$/cwt",null,"$/cwt","-%-   ","$/cwt",null,5,"<= futures price increment",null,null,null,"Signifies maximum price in row   "]
[null,{"$f":"B23-2","v":202},null,null,null,231.98,{"$f":"F22/H$16","v":0.99669172932330818},6.26]
[null,204,3.95,12.8,null,{"$f":"ROUND(F22-M$113,3)","v":229.78},{"$f":"F23/H$16","v":0.98723952738990328},5.45,null,null,"Cash","Futures Hedge",{"$f":"\"LRP, ($\"&FIXED(M6,0)&\")\"","v":"LRP, ($225)"},{"$f":"\"Put, ($\"&FIXED(N6,0)&\")\"","v":"Put, ($204)"},{"$f":"\"Hedge & Call, ($\"&FIXED(O6,0)&\")\"","v":"Hedge & Call, ($0)"},{"$f":"\"Put & Call, ($\"&FIXED(P6,0)&\"/$\"&FIXED(Q6,0)&\")\"","v":"Put & Call, ($0/$0)"}]
[null,206,4.625,11.5,null,{"$f":"ROUND(F23-M$113,3)","v":227.58},0.97778732545649849,4.71]
[null,208,5.35,10.3,null,225.38,0.96833512352309348,4.0599999999999996]
[null,210,6.2,9.1750000000000007,null,223.18,0.95888292158968857,3.49]
[null,212,7.125,8.15,null,220.98,0.94943071965628356,2.74]
[null,{"$f":"EVEN(ROUND(H14,0))","v":214},8.15,7.2249999999999996,null,218.78,0.93997851772287866,2.3199999999999998]
[null,{"$f":"B28+2","v":216},9.25,6.375,null,216.58,0.93052631578947376,1.96]
[null,218,10.425000000000001,5.6,null,214.38,0.92107411385606874,1.66]
[null,220,11.7,4.9249999999999998,null,212.18,0.91162191192266384,1.39]
[null,222,13.05,4.3250000000000002,null,209.98,0.90216970998925883]
[null,224,null,null,null,207.78,0.89271750805585393]
[null,226,null,null,null,{"$f":"ROUND(F33-M$113,3)","v":205.58},0.88326530612244902]
[]
[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,"https://www.cmegroup.com/markets/agriculture/livestock/feeder-cattle.quotes.html"]
[null," ^ Website with LRP coverage levels and premiums"]
[null,"https://www.rma.usda.gov/en/Information-Tools/Livestock-Reports"]
[]
[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":205.58},{"$f":"B105/H$16","v":0.88326530612244902},{"$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":1},{"$f":"H105*J105","v":1},1.1000000000000001,2.2000000000000002]
[null,{"$f":"F33","v":207.78},0.89271750805585393,{"$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":0},{"$f":"H106*J106","v":0},1,2]
[null,{"$f":"F32","v":209.98},0.90216970998925883,{"$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":1},0,1,2]
[null,{"$f":"F31","v":212.18},0.91162191192266384,{"$f":"IF(H31=0,\"n/a\",H31)","v":1.39},null,"Heifers","Weight 2",0,null,{"$f":"IF(H$6>=L$115,1,0)","v":0},0,0.9,1.8]
[null,{"$f":"F30","v":214.38},0.92107411385606874,{"$f":"IF(H30=0,\"n/a\",H30)","v":1.66},null,"Braham","Weight 1",0,null,{"$f":"IF(H$6<L$115,1,0)","v":1},0,1,2]
[null,{"$f":"F29","v":216.58},0.93052631578947376,{"$f":"IF(H29=0,\"n/a\",H29)","v":1.96},null,"Braham","Weigh 2",0,null,{"$f":"IF(H$6>=L$115,1,0)","v":0},0,0.9,1.8]
[null,{"$f":"F28","v":218.78},0.93997851772287866,{"$f":"IF(H28=0,\"n/a\",H28)","v":2.3199999999999998},null,"Dairy","Weight 1",0,null,{"$f":"IF(H$6<L$115,1,0)","v":1},0,1,1.7]
[null,{"$f":"F27","v":220.98},0.94943071965628356,{"$f":"IF(H27=0,\"n/a\",H27)","v":2.74},null,"Dairy","Weight 2",0,null,{"$f":"IF(H$6>=L$115,1,0)","v":0},0,0.8,1.6]
[null,{"$f":"F26","v":223.18},0.95888292158968857,{"$f":"IF(H26=0,\"n/a\",H26)","v":3.49},null,"LRP adjustment factor used in formulas",null,null,null,null,null,{"$f":"SUMPRODUCT(K105:K112,L105:L112)","v":1.1000000000000001},{"$f":"SUMPRODUCT(K105:K112,M105:M112)","v":2.2000000000000002}]
[null,{"$f":"F25","v":225.38},0.96833512352309348,{"$f":"IF(H25=0,\"n/a\",H25)","v":4.0599999999999996}]
[null,{"$f":"F24","v":227.58},0.97778732545649849,{"$f":"IF(H24=0,\"n/a\",H24)","v":4.71},null,"Minimum weight for Wt2",null,null,null,null,null,600]
[null,{"$f":"F23","v":229.78},0.98723952738990328,{"$f":"IF(H23=0,\"n/a\",H23)","v":5.45},null,"Maximum weight for Wt2",null,null,null,null,null,900]
[null,{"$f":"F22","v":231.98},0.99669172932330818,{"$f":"IF(H22=0,\"n/a\",H22)","v":6.26},null,"Pounds per feeder cattle futures contract",null,null,null,null,null,50000]

{"sheet":"Futures-options","index":2,"n_rows":39,"n_cols":2,"rows_kept":39,"cut":false,"hidden":false,"merged":[[38,1,38,12],[1,1,1,12],[39,1,39,12],[2,1,2,12]]}
[]
[null,"Example screen from CME website showing prices for 2/11/2023 for feeder cattle futures contracts"]
[null,"https://www.cmegroup.com/markets/agriculture/livestock/feeder-cattle.quotes.html"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Example screen from CME website showing option premiums for 2/11/2023 for OCT 2023 feeder cattle contract"]

{"sheet":"LRP","index":3,"n_rows":76,"n_cols":16,"rows_kept":76,"cut":false,"hidden":false,"merged":[[1,7,1,14],[50,8,50,14]]}
[]
[null,"Example INPUT screen for LRP premiums",null,null,null,null,null,"https://public.rma.usda.gov/livestockreports/LRPReport.aspx"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Example OUTPUT screen for LRP premiums",null,null,null,null,null,null,"https://public.rma.usda.gov/livestockreports/LRPReport.aspx"]
[null,"State","County","Endorsement Length","Commodity","Type","Practice","Crop Year","Exp. End Value","Coverage Price","Coverage Level","Rate","Cost Per CWT","Producer Premium Per CWT","End Date","Actual End Value"]
[null,"20 Kansas","998 All Counties",34,"0801 Feeder Cattle","809 Steers Weight 1","310 Endorsement Ending in October Yr2",2023,232.75,231.98,0.99670000000000003,4.1537999999999999E-2,9.6359999999999992,6.26,"2023-10-06"]
[null,"20 Kansas","998 All Counties",34,"0801 Feeder Cattle","809 Steers Weight 1","310 Endorsement Ending in October Yr2",2023,232.75,229.78,0.98719999999999997,3.6506999999999998E-2,8.3889999999999993,5.45,"2023-10-06"]
[null,"20 Kansas","998 All Counties",34,"0801 Feeder Cattle","809 Steers Weight 1","310 Endorsement Ending in October Yr2",2023,232.75,227.58,0.9778,3.1871999999999998E-2,7.2530000000000001,4.71,"2023-10-06"]
[null,"20 Kansas","998 All Counties",34,"0801 Feeder Cattle","809 Steers Weight 1","310 Endorsement Ending in October Yr2",2023,232.75,225.38,0.96830000000000005,2.7692999999999999E-2,6.2409999999999997,4.0599999999999996,"2023-10-06"]
[null,"20 Kansas","998 All Counties",34,"0801 Feeder Cattle","809 Steers Weight 1","310 Endorsement Ending in October Yr2",2023,232.75,223.18,0.95889999999999997,2.4072E-2,5.3719999999999999,3.49,"2023-10-06"]
[null,"20 Kansas","998 All Counties",34,"0801 Feeder Cattle","809 Steers Weight 1","310 Endorsement Ending in October Yr2",2023,232.75,220.98,0.94940000000000002,2.0657999999999999E-2,4.5650000000000004,2.74,"2023-10-06"]
[null,"20 Kansas","998 All Counties",34,"0801 Feeder Cattle","809 Steers Weight 1","310 Endorsement Ending in October Yr2",2023,232.75,218.78,0.94,1.7683000000000001E-2,3.8690000000000002,2.3199999999999998,"2023-10-06"]
[null,"20 Kansas","998 All Counties",34,"0801 Feeder Cattle","809 Steers Weight 1","310 Endorsement Ending in October Yr2",2023,232.75,216.58,0.93049999999999999,1.5084999999999999E-2,3.2669999999999999,1.96,"2023-10-06"]
[null,"20 Kansas","998 All Counties",34,"0801 Feeder Cattle","809 Steers Weight 1","310 Endorsement Ending in October Yr2",2023,232.75,214.38,0.92110000000000003,1.2930000000000001E-2,2.7719999999999998,1.66,"2023-10-06"]
[null,"20 Kansas","998 All Counties",34,"0801 Feeder Cattle","809 Steers Weight 1","310 Endorsement Ending in October Yr2",2023,232.75,212.18,0.91159999999999997,1.0881999999999999E-2,2.3090000000000002,1.39,"2023-10-06"]
[]
[null,"Notes:"]
[null,"- Some endorsement lengths may not be available due to insufficient pricing or rating information."]
[null,"- For LRP Fed Cattle, Feeder Cattle and Swine, sales open from the time sales data is available (approximately 3:30 P.M. central time) until 9 A.M. central time the"]
[null,"  next day."]
[null,"- For Lamb, sales available MONDAY ONLY. Sales open from the time of official release of coverage prices and rates on Monday morning (approximately 10 A.M."]
[null,"  central time) until 7 P.M. central time Monday."]
[null,"- For Lamb, preliminary coverage prices and rates may be available for viewing Friday evening and over the weekend but are subject to change at the official"]
[null,"  release time on Monday. Coverage prices and rates may also be available for viewing following sales during the week."]
[null,"- Purchase of LRP must be made through an authorized livestock insurance agent."]
[null,"- For LRP Fed Cattle, Feeder Cattle, and Swine, coverage levels and rates shown are available only on the selected effective date until 9 a.m. the following day."]
[null,"- For LRP Lamb, coverage levels and rates shown are available only on the selected effective date until 7 p.m."]
[null,"- For the Formatted Print option, Adobe Acrobat is required. For unformatted printing, use the File->Print option from your browser menu."]
[null,"- Use landscape mode when doing unformatted printing for best results."]