{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Intro","Sell","Selling price figure","Buy","Purchase price figure"],"not_rendered":{"charts":4,"images":4,"macros":0,"external_links":3,"defined_names":27,"comments":2,"pivot_tables":0},"unopened":[{"sheet":"Selling price figure","index":2,"reason":"not_a_worksheet"},{"sheet":"Purchase price figure","index":4,"reason":"not_a_worksheet"}],"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":"Intro","index":0,"n_rows":55,"n_cols":12,"rows_kept":55,"cut":false,"hidden":false,"merged":[[52,1,53,11],[54,1,55,11],[30,1,30,4],[32,1,36,11],[42,1,43,11],[47,15,47,16],[48,1,48,3],[48,6,48,8],[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-Buy/Sell (Calf-Yearling)"]
[]
[null,"An Excel spreadsheet for calculating breakeven cattle prices between the calf and yearling stages"]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Version- 10.13.2015"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"INTRODUCTION"]
[null,"A spreadsheet program to calculate the maximum price that can be paid for calves given an expected yearling selling price and cost of gain.  Additionally, this program also calculates the breakeven yearling selling price needed given a calf purchase price and an estimated cost of gain."]
[]
[null,"INSTRUCTIONS FOR THE USER:"]
[null,"Be sure to \"Enable Content\" and \"Enable Macros\" for the spreadsheet to function correctly."]
[null,"In the Sell and Buy sheets all blue numbers are inputs and all black numbers are calculated from these inputs.  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)."]
[]
[]
[]
[]
[]
[]
[null,"FOR MORE INFORMATION:"]
[null,"Links to supporting materials:"]
[null,null,"www.agmanager.info/livestock/marketing/",null,null,null,null,null,"www.BeefBasis.com"]
[null,"Updated and Expanded by:"]
[]
[null,"Glynn T. Tonsor, Ph.D.",null,null,null,null,null,"Brian Highfill"]
[null,"Agricultural Economist",null,null,null,null,null,"Graduate Research Assistant"]
[null,"Department of Agricultural Economics",null,null,null,null,null,"Kansas State University"]
[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"]
[]
[null,"Copyright 2016 AgManager.info, K-State Department of Agricultural Economics"]

{"sheet":"Sell","index":1,"n_rows":44,"n_cols":27,"rows_kept":44,"cut":false,"hidden":false,"merged":[[16,2,18,2],[16,3,18,3],[16,4,18,4],[29,2,31,2],[16,7,16,13],[18,7,18,13],[31,7,31,13],[29,7,29,13],[16,5,18,5],[29,3,31,3],[29,4,31,4],[29,5,31,5]]}
[]
[null,"Breakeven Selling Price Worksheet",null,null,null,null,null,"(Blue values in shaded cells are inputs to be entered)"]
[]
[null,"Purchase weight (lbs)",null,null,null,null,null,525]
[null,"Purchase price ($/cwt)",null,null,null,null,null,205,null,null,null,null,null,{"$f":"IF(H5<1,\"   Enter purchase price as $/Cwt.!\",\" \")","v":" "}]
[null,"Purchase date (mm/dd/yy)",null,null,null,null,null,{"$f":"NOW()","v":"2016-07-28T15:37:08.290"}]
[null,"Average Daily Gain (pay-to-pay)",null,null,null,null,null,2.15,null,null,"Increments to use in tables below"]
[null,"Feeding cost of gain ($/cwt)",null,null,null,null,null,85,null,null,"Selling Weight (lbs)",null,null,25]
[null,"Interest rate on feeder and feeding cost of gain",null,null,null,null,null,7.0000000000000007E-2,null,null,"Purchase Price ($/cwt) ",null,null,5]
[null,"Percent death loss*",null,null,null,null,null,1.7999999999999999E-2,null,null,"Feeding Cost of Gain ($/cwt)",null,null,5]
[null,"Costs per head (trucking, vaccines, backgrounding, etc.)**",null,null,null,null,null,20]
[null,"Desired profit per head",null,null,null,null,null,0]
[null,"* Enter ONLY if death loss is NOT included in feeding cost of gain, otherwise enter zero."]
[null,"** Do not enter any costs included in feeding cost of gain."]
[]
[]
[null,null,"Expected sale date","Relevant futures price","Expected basis*","Expected cash price",null,"Purchase Price ($/cwt)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Selling","Adj."]
[null,"Selling",null,null,null,null,null,{"$f":"I18-$N9","v":190},{"$f":"J18-$N9","v":195},{"$f":"K18-$N9","v":200},{"$f":"H5","v":205},{"$f":"K18+$N9","v":210},{"$f":"L18+$N9","v":215},{"$f":"M18+$N9","v":220},null,null,null,null,null,null,null,null,null,"Days","Gain","Weight","Gain"]
[null,"Weight1",null,null,null,null,null,"Breakeven Selling Price ($/cwt)2"]
[null,{"$f":"H4+75","v":600},{"$f":"H$6+X20","v":"2016-09-01T12:49:41.778"},176.27500000000001,8.1,{"$f":"D20+E20","v":184.375},null,{"$f":"IF($B20<=$H$4,\"    N/A\",((H$18/100*$H$4)*(1+$H$9*$X20/365)+(($H$8/100*$AA20)+$H$11)*(1+$H$9/2*$X20/365)+$H$12)/$Z20*100)","v":183.12843539017163},187.61343424499651,192.09843309982145,196.5834319546463,201.06843080947118,205.55342966429606,210.03842851912094,null,null,null,null,null,null,null,null,null,{"$f":"(B20-$H$4)/$H$7","v":34.883720930232556},{"$f":"B20-H$4","v":75},{"$f":"B20*(1-H$10)","v":589.20000000000005},{"$f":"Z20-H$4","v":64.200000000000045}]
[null,{"$f":"B20+N$8","v":625},{"$f":"H$6+X21","v":"2016-09-13T03:53:52.941"},176.27500000000001,5.91,{"$f":"D21+E21","v":182.185},null,179.59444255924902,183.90957917896461,188.22471579868019,192.53985241839572,196.85498903811131,201.17012565782687,205.48526227754246,null,null,null,null,null,null,null,null,null,46.511627906976749,100,613.75,88.75]
[null,650,"2016-09-24T18:58:04.104",176.27500000000001,4.13,180.405,null,176.33958574656506,180.49792645694896,184.65626716733286,188.81460787771672,192.97294858810062,197.13128929848452,201.28963000886841,null,null,null,null,null,null,null,null,null,58.139534883720934,125,638.29999999999995,113.29999999999995]
[null,675,"2016-10-06T10:02:15.267",176.27500000000001,2.71,178.98500000000001,null,173.33284983898218,177.34600915183677,181.35916846469132,185.37232777754591,189.38548709040043,193.39864640325499,197.41180571610957,null,null,null,null,null,null,null,null,null,69.767441860465112,150,662.85,137.85000000000002]
[null,700,"2016-10-18T01:06:26.430",176.27500000000001,1.1000000000000001,177.375,null,170.547650453811,174.42599846895973,178.30434648410852,182.18269449925722,186.06104251440598,189.93939052955474,193.81773854470353,null,null,null,null,null,null,null,null,null,81.395348837209312,175,687.4,162.39999999999998]
[null,725,"2016-10-29T16:10:37.592",175.35,-0.56999999999999995,174.78,null,167.96107001976742,171.71390406808666,175.46673811640585,179.21957216472507,182.97240621304428,186.72524026136347,190.47807430968265,null,null,null,null,null,null,null,null,null,93.023255813953497,200,711.94999999999993,186.94999999999993]
[null,750,"2016-11-10T07:14:48.755",175.35,-1.89,173.46,null,165.55324664173884,169.18893432101711,172.82462200029542,176.46030967957375,180.09599735885203,183.73168503813034,187.36737271740861,null,null,null,null,null,null,null,null,null,104.65116279069768,225,736.5,211.5]
[null,null,null,null,null,null,1,"Enter the minimum selling (pay) weight you want to consider."]
[null,null,null,null,null,null,2,{"$f":"\"Based on a feeding cost of gain of $\"&LEFT(H8,6)&\"/cwt.\"","v":"Based on a feeding cost of gain of $85/cwt."}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Adj."]
[null,null,"Expected sale date","Relevant futures price","Expected basis","Expected cash price",null,"Feeding Cost of Gain ($/cwt)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Selling","Adj."]
[null,"Selling",null,null,null,null,null,{"$f":"I31-$N10","v":70},{"$f":"J31-$N10","v":75},{"$f":"K31-$N10","v":80},{"$f":"H8","v":85},{"$f":"K31+$N10","v":90},{"$f":"L31+$N10","v":95},{"$f":"M31+$N10","v":100},null,null,null,null,null,null,null,null,null,"Days","Gain","Weight","Gain"]
[null,"Weight",null,null,null,null,null,"Breakeven Selling Price ($/cwt)1"]
[null,{"$f":"B20","v":600},{"$f":"H$6+X33","v":"2016-09-01T12:49:41.778"},{"$f":"D20","v":176.27500000000001},8.1,{"$f":"D33+E33","v":184.375},null,{"$f":"IF($B33<=$H$4,\"    N/A\",(($H$5/100*$H$4)*(1+$H$9*$X33/365)+((H$31/100*$AA33)+$H$11)*(1+$H$9/2*$X33/365)+$H$12)/$Z33*100)","v":194.94354524587959},195.49017414880183,196.03680305172409,196.5834319546463,197.13006085756854,197.67668976049077,198.22331866341304,null,null,null,null,null,null,null,null,null,{"$f":"X20","v":34.883720930232556},{"$f":"B33-H$4","v":75},{"$f":"B33*(1-H$10)","v":589.20000000000005},{"$f":"Z33-H$4","v":64.200000000000045}]
[null,625,"2016-09-13T03:53:52.941",176.27500000000001,5.91,{"$f":"D34+E34","v":182.185},null,190.36113567632484,191.08737459034845,191.81361350437209,192.53985241839572,193.26609133241934,193.99233024644295,194.71856916046659,null,null,null,null,null,null,null,null,null,46.511627906976749,100,613.75,88.75]
[null,650,"2016-09-24T18:58:04.104",176.27500000000001,4.13,180.405,null,186.13722302246674,187.0296846408834,187.92214625930006,188.81460787771672,189.70706949613344,190.5995311145501,191.49199273296676,null,null,null,null,null,null,null,null,null,58.139534883720934,125,638.29999999999995,113.29999999999995]
[null,675,"2016-10-06T10:02:15.267",176.27500000000001,2.71,178.98500000000001,null,182.23197429367229,183.27875878829681,184.32554328292136,185.37232777754591,186.41911227217039,187.46589676679491,188.51268126141946,null,null,null,null,null,null,null,null,null,69.767441860465112,150,662.85,137.85000000000002]
[null,700,"2016-10-18T01:06:26.430",176.27500000000001,1.1000000000000001,177.375,null,178.61124692654184,179.80172945078033,180.99221197501879,182.18269449925722,183.37317702349569,184.56365954773418,185.75414207197264,null,null,null,null,null,null,null,null,null,81.395348837209312,175,687.4,162.39999999999998]
[null,725,"2016-10-29T16:10:37.592",175.35,-0.56999999999999995,174.78,null,175.24560767676533,176.57026250608524,177.89491733540515,179.21957216472507,180.54422699404498,181.86888182336486,183.1935366526848,null,null,null,null,null,null,null,null,null,93.023255813953497,200,711.94999999999993,186.94999999999993]
[null,750,"2016-11-10T07:14:48.755",175.35,-1.89,173.46,null,172.10954773260733,173.55980171492948,175.01005569725163,176.46030967957375,177.91056366189585,179.360817644218,180.81107162654013,null,null,null,null,null,null,null,null,null,104.65116279069768,225,736.5,211.5]
[null,null,null,null,null,null,1,{"$f":"\"Based on a purchase price of $\"&LEFT(H5,5)&\"/cwt.\"","v":"Based on a purchase price of $205/cwt."}]
[]
[null,null,null,null,null,null,null,"Breakeven Selling Price is below Expected cash price (expect positive returns)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Footnotes for \"Buy\" sheet because they will not work in that sheet for some reason"]
[null,null,null,null,null,null,null,"Breakeven Selling Price is above Expected cash price (expect negative returns)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"Based on a feeding cost of gain of $\"&LEFT(Buy!H8,6)&\"/cwt.\"","v":"Based on a feeding cost of gain of $85/cwt."}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"Based on a selling price of $\"&LEFT(Buy!H5,5)&\"/cwt.\"","v":"Based on a selling price of $175/cwt."}]

{"sheet":"Buy","index":3,"n_rows":43,"n_cols":26,"rows_kept":43,"cut":false,"hidden":false,"merged":[[16,7,16,13],[29,7,29,13],[31,7,31,13],[18,7,18,13],[29,2,31,2],[16,2,18,2],[29,3,31,3],[29,4,31,4],[29,5,31,5],[16,3,18,3],[16,4,18,4],[16,5,18,5]]}
[]
[null,"Breakeven Buying Price Worksheet",null,null,null,null,null,"(Blue values in shaded cells are inputs to be entered)"]
[]
[null,"Selling weight after shrink (pay-weight)",null,null,null,null,null,725]
[null,"Expected selling price ($/cwt)",null,null,null,null,null,175,null,null,null,null,null,{"$f":"IF(H5<1,\"   Enter selling price as $/Cwt.!\",\" \")","v":" "}]
[null,"Purchase date (mm/dd/yy)",null,null,null,null,null,{"$f":"NOW()","v":"2016-07-28T15:37:08.290"}]
[null,"Average Daily Gain (pay-to-pay)",null,null,null,null,null,2.15,null,null,"Increments to use in tables below"]
[null,"Feeding cost of gain ($/cwt)",null,null,null,null,null,85,null,null,"Purchase Weight (lbs)",null,null,25]
[null,"Interest rate on feeder",null,null,null,null,null,7.0000000000000007E-2,null,null,"Selling Price ($/cwt) ",null,null,5]
[null,"Percent death loss*",null,null,null,null,null,1.7999999999999999E-2,null,null,"Feeding Cost of Gain ($/cwt)",null,null,5]
[null,"Costs per head (trucking, vaccines, backgrounding, etc.)**",null,null,null,null,null,20]
[null,"Desired profit per head",null,null,null,null,null,0]
[null,"* Enter ONLY if death loss is NOT included in feeding cost of gain, otherwise enter zero."]
[null,"** Do not enter any costs included in feeding cost of gain."]
[]
[]
[null,null,"Expected sale date","Relevant futures price","Expected basis*","Expected cash price",null,"Selling Price ($/cwt)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Adj."]
[null,"Purchase",null,null,null,null,null,{"$f":"I18-$N9","v":160},{"$f":"J18-$N9","v":165},{"$f":"K18-$N9","v":170},{"$f":"H5","v":175},{"$f":"K18+$N9","v":180},{"$f":"L18+$N9","v":185},{"$f":"M18+$N9","v":190},null,null,null,null,null,null,null,null,null,"Days","Gain","Gain"]
[null,"Weight1",null,null,null,null,null,"Breakeven Purchase Price ($/cwt)2"]
[null,{"$f":"H4-125","v":600},{"$f":"H$6+X20","v":"2016-09-24T18:58:04.104"},176.27500000000001,9.0299999999999994,{"$f":"D20+E20","v":185.30500000000001},null,{"$f":"IF($B20>=$H$4,\"    N/A\",(H$18*($H$4*(1-$H$10))/100-(($H$8/100*$Z20)+$H$11)*(1+$H$9/2*$X20/365)-$H$12)/($B20/100*(1+$H$9*$X20/365)))","v":168.67270695494645},174.54020078502415,180.40769461510189,186.27518844517959,192.1426822752573,198.01017610533506,203.87766993541274,null,null,null,null,null,null,null,null,null,{"$f":"($H$4-B20)/$H$7","v":58.139534883720934},{"$f":"H$4-B20","v":125},{"$f":"H$4*(1-H$10)-B20","v":111.94999999999993}]
[null,{"$f":"B20-N$8","v":575},"2016-10-06T10:02:15.267",176.27500000000001,12.52,{"$f":"D21+E21","v":188.79500000000002},null,{"$f":"IF($B21>=$H$4,\"    N/A\",(H$18*($H$4*(1-$H$10))/100-(($H$8/100*$Z21)+$H$11)*(1+$H$9/2*$X21/365)-$H$12)/($B21/100*(1+$H$9*$X21/365)))","v":171.92569905553353},178.03482812350504,184.14395719147655,190.25308625944808,196.36221532741956,202.47134439539116,208.58047346336264,null,null,null,null,null,null,null,null,null,69.767441860465112,150,136.94999999999993]
[null,550,"2016-10-18T01:06:26.430",176.27500000000001,16.690000000000001,192.965,null,175.48466857248772,181.85746157465491,188.23025457682215,194.60304757898939,200.97584058115658,207.34863358332385,213.72142658549103,null,null,null,null,null,null,null,null,null,81.395348837209312,175,161.94999999999993]
[null,525,"2016-10-29T16:10:37.592",175.35,21.29,196.64,null,179.39325598032642,186.05488814367686,192.71652030702734,199.37815247037784,206.03978463372829,212.70141679707882,219.36304896042927,null,null,null,null,null,null,null,null,null,93.023255813953497,200,186.94999999999993]
[null,500,"2016-11-10T07:14:48.755",175.35,26.58,201.93,null,183.70383049656462,190.68325288881942,197.66267528107429,204.64209767332915,211.62152006558395,218.60094245783887,225.58036485009367,null,null,null,null,null,null,null,null,null,104.65116279069768,225,211.94999999999993]
[null,475,"2016-11-21T22:18:59.918",175.35,18.739999999999998,194.09,null,188.47978711190564,195.81052155942984,203.14125600695408,210.47199045447834,217.80272490200255,225.13345934952685,232.46419379705105,null,null,null,null,null,null,null,null,null,116.27906976744187,250,236.94999999999993]
[null,{"$f":"B25-N$8","v":450},"2016-12-03T13:23:11.081",175.35,37.99,213.34,null,193.79860929726365,201.51976411346041,209.24091892965723,216.96207374585404,224.68322856205077,232.40438337824764,240.1255381944444,null,null,null,null,null,null,null,null,null,127.90697674418605,275,261.94999999999993]
[null,null,null,null,null,null,1,"Enter the maximum purchase weight you are willing to consider."]
[null,null,null,null,null,null,2,{"$f":"Sell!X43","v":"Based on a feeding cost of gain of $85/cwt."}]
[]
[null,null,"Expected sale date","Relevant futures price","Expected basis","Expected cash price",null,"Feeding Cost of Gain ($/cwt)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Adj."]
[null,"Purchase",null,null,null,null,null,{"$f":"I31-$N10","v":70},{"$f":"J31-$N10","v":75},{"$f":"K31-$N10","v":80},{"$f":"H8","v":85},{"$f":"K31+$N10","v":90},{"$f":"L31+$N10","v":95},{"$f":"M31+$N10","v":100},null,null,null,null,null,null,null,null,null,"Days","Gain","Gain"]
[null,"Weight",null,null,null,null,null,"Breakeven Purchase Price ($/cwt)1"]
[null,{"$f":"B20","v":600},{"$f":"H$6+X33","v":"2016-09-24T18:58:04.104"},{"$f":"D20","v":176.27500000000001},{"$f":"E20","v":9.0299999999999994},{"$f":"D33+E33","v":185.30500000000001},null,{"$f":"IF($B33>=$H$4,\"    N/A\",($H$5*($H$4*(1-$H$10))/100-((H$31/100*$Z33)+$H$11)*(1+$H$9/2*$X33/365)-$H$12)/($B33/100*(1+$H$9*$X33/365)))","v":189.05850740390673},188.13073441766437,187.20296143142198,186.27518844517959,185.34741545893721,184.41964247269482,183.49186948645243,null,null,null,null,null,null,null,null,null,{"$f":"X20","v":58.139534883720934},{"$f":"H$4-B33","v":125},{"$f":"H$4*(1-H$10)-B33","v":111.94999999999993}]
[null,575,"2016-10-06T10:02:15.267",{"$f":"D21","v":176.27500000000001},12.52,{"$f":"D34+E34","v":188.79500000000002},null,193.80210967292209,192.61910186843076,191.43609406393941,190.25308625944808,189.07007845495673,187.8870706504654,186.70406284597405,null,null,null,null,null,null,null,null,null,69.767441860465112,150,136.94999999999993]
[null,550,"2016-10-18T01:06:26.430",176.27500000000001,16.690000000000001,192.965,null,198.98592220828104,197.52496399851714,196.06400578875326,194.60304757898939,193.14208936922546,191.68113115946159,190.22017294969771,null,null,null,null,null,null,null,null,null,81.395348837209312,175,161.94999999999993]
[null,525,"2016-10-29T16:10:37.592",175.35,21.29,196.64,null,204.67277040017885,202.90789775691184,201.14302511364485,199.37815247037784,197.61327982711083,195.84840718384382,194.08353454057684,null,null,null,null,null,null,null,null,null,93.023255813953497,200,186.94999999999993]
[null,500,"2016-11-10T07:14:48.755",175.35,26.58,201.93,null,210.93804528419733,208.83939608057463,206.7407468769519,204.64209767332915,202.54344846970645,200.44479926608369,198.34615006246094,null,null,null,null,null,null,null,null,null,104.65116279069768,225,211.94999999999993]
[null,475,"2016-11-21T22:18:59.918",175.35,18.739999999999998,194.09,null,217.87301028357743,215.40600367387771,212.93899706417801,210.47199045447834,208.00498384477865,205.53797723507896,203.07097062537926,null,null,null,null,null,null,null,null,null,116.27906976744187,250,236.94999999999993]
[null,450,"2016-12-03T13:23:11.081",175.35,37.99,213.34,null,225.58921019900492,222.71349804795466,219.83778589690434,216.96207374585404,214.08636159480375,211.21064944375343,208.33493729270313,null,null,null,null,null,null,null,null,null,127.90697674418605,275,261.94999999999993]
[null,null,null,null,null,null,1,{"$f":"Sell!X44","v":"Based on a selling price of $175/cwt."}]
[]
[null,null,null,null,null,null,null,"Breakeven Buying Price is above Expected cash price (expect positive returns)"]
[null,null,null,null,null,null,null,"Breakeven Buying Price is below Expected cash price (expect negative returns)"]