{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Beef Repl","Prices and weights","Net present value","Sensitivity Table"],"not_rendered":{"charts":5,"images":3,"macros":1,"external_links":2,"defined_names":8,"comments":2,"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":"Beef Repl","index":0,"n_rows":49,"n_cols":12,"rows_kept":49,"cut":false,"hidden":false,"merged":[[46,15,46,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],[40,1,40,4],[40,7,40,11],[46,1,46,3],[46,6,46,8],[48,1,49,11],[30,1,30,4],[32,1,36,11]]}
[]
[]
[null,"KSU-Beef Replacements"]
[]
[null,"An Excel spreadsheet program to evaluate the economic value of purchasing beef replacement females."]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Version- 1.12.2016"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"INTRODUCTION"]
[null,"A spreadsheet program to calculate the net present value of purchasing replacment heifers given expectations for calf selling price, weaning weights, cull cow price, and a target rate of return on investment."]
[]
[null,"INSTRUCTIONS FOR THE USER:"]
[null,"Be sure to \"Enable Content\" and \"Enable Macros\" for the spreadsheet to function correctly."]
[null,"In the Price and weights and Net Present Value tabs all blue numbers are inputs and all black numbers are calculated from these inputs.  Several 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,"Updated by:",null,null,null,null,null,"Originally developed by:"]
[]
[null,"Glynn T. Tonsor, Ph.D.",null,null,null,null,null,"Kevin C. Dhuyvetter, Ph.D."]
[null,"Agricultural Economist",null,null,null,null,null,"Former Extension Agricultural Economist"]
[null,"Department of Agricultural Economics",null,null,null,null,null,"Department of Agricultural Economics"]
[null,"Kansas State University",null,null,null,null,null,"Kansas State University"]
[null,"gtonsor@k-state.edu"]
[null,"785-532-1518"]
[null,"Copyright 2016 AgManager.info, K-State Department of Agricultural Economics"]

{"sheet":"Prices and weights","index":1,"n_rows":20,"n_cols":13,"rows_kept":20,"cut":false,"hidden":false,"merged":[[4,6,4,7],[1,1,1,7],[1,9,1,12],[4,2,4,3],[4,4,4,5]]}
[]
[null,"Calf (average of steer and heifer) and Cull Cow Price Scenarios ($/cwt)",null,null,null,null,null,null,null,"Weaning Weight (steer and heifer) Scenarios"]
[]
[null,null,"Scenario 1 (S1)",null,"Scenario 2 (S2)",null,"Scenario 3 (S3)",null,null,null,"Scenario1","Scenario2","Scenario3"]
[null,null,"USDA (As of Nov. '15)",null,"Flat at $25 under Scen 1",null,"Scenario 1 +10%",null,null,null,"Low peak","Constant","High peak"]
[null,"Year","Calf","Cull cow","Calf","Cull cow","Calf","Cull cow",null,"Calf","Lbs/hd","Lbs/hd","Lbs/hd"]
[null,{"$f":"'Net present value'!G5","v":2016},205,{"$f":"C7*0.44","v":90.2},{"$f":"C7-25","v":180},{"$f":"E7*0.44","v":79.2},{"$f":"C7*1.1","v":225.50000000000003},{"$f":"G7*0.44","v":99.220000000000013},null,1,542,562,537]
[null,{"$f":"B7+1","v":2017},169.56893725981922,{"$f":"C8*0.44","v":74.610332394320451},{"$f":"E7","v":180},{"$f":"E8*0.44","v":79.2},{"$f":"C8*1.1","v":186.52583098580115},{"$f":"G8*0.44","v":82.071365633752507},null,2,552,{"$f":"L7","v":562},552]
[null,{"$f":"B8+1","v":2018},158.20226671441031,69.608997354340545,{"$f":"E8","v":180},79.2,174.02249338585136,76.569897089774599,null,3,562,{"$f":"L8","v":562},567]
[null,2019,151.58755984477699,66.698526331701871,180,79.2,166.74631582925471,73.368378964872079,null,4,567,562,582]
[null,2020,144.81308624673179,63.717757948561989,180,79.2,159.29439487140499,70.089533743418201,null,5,572,562,582]
[null,2021,143.0534196178078,62.943504631835431,180,79.2,157.35876157958859,69.237855095018986,null,6,572,562,577]
[null,2022,140.24507849224455,61.707834536587598,180,79.2,154.26958634146902,67.878617990246369,null,7,567,562,567]
[null,2023,138.6668814307524,61.013427829531054,180,79.2,152.53356957382766,67.114770612484179,null,8,565,562,557]
[null,2024,139.44683790220549,61.356608676970417,180,79.2,153.39152169242604,67.492269544667451,null,9,562,562,552]
[null,2025,140.26,61.714399999999998,180,79.2,154.286,67.885840000000002,null,10,559,562,547]
[null,"Average",{"$f":"AVERAGE(C7:C16)","v":153.08440675087485},67.357138970384923,180,79.200000000000017,168.39284742596237,74.092852867423446,null,"Average",{"$f":"AVERAGE(K7:K16)","v":562},{"$f":"AVERAGE(L7:L16)","v":562},{"$f":"AVERAGE(M7:M16)","v":562}]
[]
[null,"Average weight for calf price (average of steer and heifer), lbs/head",null,null,null,null,null,525]
[null,"Price slide (change in price for weight deviation from base), $/lb",null,null,null,null,null,8.4199999999999997E-2]

{"sheet":"Net present value","index":2,"n_rows":79,"n_cols":45,"rows_kept":79,"cut":false,"hidden":false,"merged":[[25,1,26,14],[11,8,11,9],[11,41,11,42]]}
[]
[null,"Input Assumptions"]
[null,"Number of replacements purchased",null,null,null,null,100,null,"Percent marketable calves (1 - death loss)",null,null,null,null,0.97]
[null,"Year of purchase",null,null,null,null,2016,null,"Annual cow death loss",null,null,null,null,5.0000000000000001E-3]
[null,"First year for calf sales",null,null,null,null,2016,null,"Annual cull rate",null,null,null,null,0.15]
[null,"Cull cow weight, lbs/hd",null,null,null,null,1250]
[null,"Annual cow costs, $/year",null,null,null,null,700,null,"Annual inflation rate on costs",null,null,null,null,0.01]
[null,{"$f":"\"Price scenario to use (1-3) (\"&IF(G8=1,'Prices and weights'!C5,IF(G8=2,'Prices and weights'!E5,'Prices and weights'!G5))&\")\"","v":"Price scenario to use (1-3) (USDA (As of Nov. '15))"},null,null,null,null,1,null,"Annual increase in average weaning weight",null,null,null,null,0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Sensitivity to discount rate",null,null,"Sensitivity to calf/cull prices"]
[null,"Weaning weight scenario to use (1-3)",null,null,null,null,1,null,"Discount rate (interest rate)",null,null,null,null,7.4999999999999997E-2,null,null,null,"Values for sensitivity analysis down below",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0.9,"<= % of base",null,1.1000000000000001,"<= % of base"]
[]
[null,"Net Present Value Analysis",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Annual cow costs, $/year",null,null,null,null,null,"Annual Cull Rate",null,null,null,null,null,"Annual Cull Rate",null,null,null,null,null,{"$f":"N9*AJ9","v":6.7500000000000004E-2}]
[null,null,"Cows at",null,null,"Prices, $/cwt",null,"Calf","Cull Income",null,null,"Cost","Net","Discount",null,null,null,{"$f":"D33","v":560},{"$f":"E33","v":630},{"$f":"F33","v":700},{"$f":"G33","v":770},{"$f":"H33","v":840},null,{"$f":"Y12-H72","v":0.1},{"$f":"Z12-H72","v":0.125},{"$f":"N5","v":0.15},{"$f":"Z12+H72","v":0.17499999999999999},{"$f":"AA12+H72","v":0.19999999999999998},null,{"$f":"X12","v":0.1},{"$f":"Y12","v":0.125},{"$f":"Z12","v":0.15},{"$f":"AA12","v":0.17499999999999999},{"$f":"AB12","v":0.19999999999999998},null,"Discount",null,null,"Prices, $/cwt",null,"Calf","Cull Income",null,"Net"]
[null,"Year","BOY*","Calf","Calf wt","Calf","Cull","Income","Annual","Age","Cost","Adj.","Income","factor","NPV**",null,null,"Cost","Cost","Cost","Cost","Cost",null,"Cows at Beginning of Year (BOY)",null,null,null,null,null,"Net Income",null,null,null,null,null,"factor","NPV**",null,"Calf","Cull","Income","Annual","Age","Income","NPV**"]
[null,{"$f":"G5","v":2016},{"$f":"G3","v":100},{"$f":"IF(G5=B14,1,0)","v":1},{"$f":"VLOOKUP(D14,weights,IF(G$9=1,2,IF(G$9=2,3,4)))*(1+N$8)^(B14-G$5)","v":542},{"$f":"VLOOKUP(B14,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E14)*'Prices and weights'!H$20","v":203.5686},{"$f":"VLOOKUP(B14,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":90.2},{"$f":"IF(B14>=G$5,E14*F14/100*N$3*C14/G$3,0)","v":1070.2415576400001},{"$f":"IF(B14>=G$5,G$6*G14/100*(N$5*C14/G$3),0)","v":169.125},{"$f":"IF(B14>=G$5,G$6*G14/100*(C14*(1-N$4-N$5))/G$3,0)","v":952.73749999999995},{"$f":"G$7*(1+N$7)^(B14-G$4)*C14/G$3","v":700},0,{"$f":"H14+I14-K14-L14","v":539.36655764000011},{"$f":"1/(1+N$9)^(B14-G$4)","v":1},{"$f":"SUMPRODUCT(M$14:M14,N$14:N14)+J14*N14","v":1492.1040576400001},null,null,{"$f":"D$33*(1+N$7)^(B14-G$4)*C14/G$3","v":560},{"$f":"E$33*(1+N$7)^(B14-G$4)*C14/G$3","v":630},{"$f":"F$33*(1+N$7)^(B14-G$4)*C14/G$3","v":700},{"$f":"G$33*(1+N$7)^(B14-G$4)*C14/G$3","v":770},{"$f":"H$33*(1+N$7)^(B14-G$4)*C14/G$3","v":840},null,{"$f":"$G3","v":100},{"$f":"$G3","v":100},{"$f":"$G3","v":100},{"$f":"$G3","v":100},{"$f":"$G3","v":100},null,{"$f":"IF(B14>=G$5,E14*F14/100*N$3*X14/G$3,0)+IF(B14>=G$5,G$6*G14/100*(AD$12*X14/G$3),0)-G$7*(1+N$7)^(B14-G$4)*X14/G$3-L14","v":482.99155764000011},{"$f":"IF(B14>=G$5,E14*F14/100*N$3*Y14/G$3,0)+IF(B14>=G$5,G$6*G14/100*(AE$12*Y14/G$3),0)-G$7*(1+N$7)^(B14-G$4)*Y14/G$3-L14","v":511.17905764000011},{"$f":"IF(B14>=G$5,E14*F14/100*N$3*Z14/G$3,0)+IF(B14>=G$5,G$6*G14/100*(AF$12*Z14/G$3),0)-G$7*(1+N$7)^(B14-G$4)*Z14/G$3-L14","v":539.36655764000011},{"$f":"IF(B14>=G$5,E14*F14/100*N$3*AA14/G$3,0)+IF(B14>=G$5,G$6*G14/100*(AG$12*AA14/G$3),0)-G$7*(1+N$7)^(B14-G$4)*AA14/G$3-L14","v":567.55405764000011},{"$f":"IF(B14>=G$5,E14*F14/100*N$3*AB14/G$3,0)+IF(B14>=G$5,G$6*G14/100*(AH$12*AB14/G$3),0)-G$7*(1+N$7)^(B14-G$4)*AB14/G$3-L14","v":595.74155764000011},null,{"$f":"1/(1+AJ$11)^(B14-G$4)","v":1},{"$f":"SUMPRODUCT(M$14:M14,AJ$14:AJ14)+J14*AJ14","v":1492.1040576400001},null,{"$f":"VLOOKUP(B14,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))*AM$9+('Prices and weights'!H$19-E14)*'Prices and weights'!H$20","v":224.06860000000003},{"$f":"G14*AM$9","v":99.220000000000013},{"$f":"IF(B14>=G$5,E14*AM14/100*N$3*C14/G$3,0)","v":1178.0182576400002},{"$f":"IF(B14>=G$5,G$6*AN14/100*(N$5*C14/G$3),0)","v":186.03750000000002},{"$f":"IF(B14>=G$5,G$6*AN14/100*(C14*(1-N$4-N$5))/G$3,0)","v":1048.0112500000002},{"$f":"AO14+AP14-K14-L14","v":664.05575764000014},{"$f":"SUMPRODUCT(AR$14:AR14,N$14:N14)+AQ14*N14","v":1712.0670076400004}]
[null,{"$f":"+B14+1","v":2017},{"$f":"C14*(1-N$4-N$5)","v":84.5},{"$f":"IF(D14>0,D14+1,IF(G$5=B15,1,0))","v":2},552,{"$f":"VLOOKUP(B15,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E15)*'Prices and weights'!H$20","v":167.29553725981921},{"$f":"VLOOKUP(B15,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":74.610332394320451},{"$f":"IF(B15>=G$5,E15*F15/100*N$3*C15/G$3,0)","v":756.9233048748597},{"$f":"G$6*G15/100*(N$5*C15/G$3)","v":118.21074538725146},{"$f":"IF(B15>=G$5,G$6*G15/100*(C15*(1-N$4-N$5))/G$3,0)","v":665.92053234818331},{"$f":"G$7*(1+N$7)^(B15-G$4)*C15/G$3","v":597.41499999999996},0,{"$f":"H15+I15-K15-L15","v":277.71905026211118},0.93023255813953487,{"$f":"SUMPRODUCT(M$14:M15,N$14:N15)+J15*N15","v":1417.1708205332973},null,null,477.93200000000002,537.67349999999999,597.41499999999996,657.15650000000005,716.89800000000002,null,{"$f":"X14*(1-$N$4-X$12)","v":89.5},{"$f":"Y14*(1-$N$4-Y$12)","v":87},{"$f":"Z14*(1-$N$4-Z$12)","v":84.5},{"$f":"AA14*(1-$N$4-AA$12)","v":82},{"$f":"AB14*(1-$N$4-AB$12)","v":79.5},null,252.41697547620458,265.65090609098843,277.71905026211118,288.62140798957262,298.35797927337273,null,0.93676814988290402,{"$f":"SUMPRODUCT(M$14:M15,AJ$14:AJ15)+J15*AJ15","v":1423.3380635981214},null,{"$f":"VLOOKUP(B15,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))*AM$9+('Prices and weights'!H$19-E15)*'Prices and weights'!H$20","v":184.25243098580114},82.071365633752507,833.64422791746563,130.03181992597663,732.51258558300162,366.26104784344227,{"$f":"SUMPRODUCT(AR$14:AR15,N$14:N15)+AQ15*N15","v":1686.1707654785528}]
[null,2018,71.402500000000003,{"$f":"IF(D15>0,D15+1,IF(G$5=B16,1,0))","v":3},562,{"$f":"VLOOKUP(B16,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E16)*'Prices and weights'!H$20","v":155.08686671441032},69.608997354340545,603.66568529138283,{"$f":"G$6*G16/100*(N$5*C16/G$3)","v":93.192308129874405},524.98333579829239,509.86383175000003,0,186.99416167125719,0.86533261222282321,{"$f":"SUMPRODUCT(M$14:M16,N$14:N16)+J16*N16","v":1413.8072079385997},null,null,407.8910654,458.87744857500002,509.86383175000003,560.85021492500005,611.83659810000006,null,{"$f":"X15*(1-$N$4-X$12)","v":80.102500000000006},75.69,71.402500000000003,67.240000000000009,63.202500000000001,null,174.92927485658709,181.75788144447438,186.99416167125719,190.71968858070971,193.01603521660661,null,0.87753456663503904,{"$f":"SUMPRODUCT(M$14:M16,AJ$14:AJ16)+J16*AJ16","v":1424.3097832371159},null,{"$f":"VLOOKUP(B16,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))*AM$9+('Prices and weights'!H$19-E16)*'Prices and weights'!H$20","v":170.90709338585137},76.569897089774599,665.244903296134,102.51153894286185,577.48166937812164,257.89261048899584,{"$f":"SUMPRODUCT(AR$14:AR16,N$14:N16)+AQ16*N16","v":1727.6403169034447}]
[null,2019,60.335112500000001,4,567,{"$f":"VLOOKUP(B17,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E17)*'Prices and weights'!H$20","v":148.051159844777},66.698526331701871,491.28865349107059,75.454932933889594,425.06278886091138,435.14328720703747,0,131.60029921792272,0.80496056950960304,{"$f":"SUMPRODUCT(M$14:M17,N$14:N17)+J17*N17","v":1407.6138430037245},null,null,348.11462976562996,391.62895848633372,435.14328720703747,478.65761592774123,522.17194464844499,null,71.691737500000002,65.850300000000004,60.335112500000001,55.136800000000015,50.245987500000005,null,126.48503845574055,129.90441547438718,131.60029921792272,131.75429098520885,130.54017584155258,null,0.82204643244500153,{"$f":"SUMPRODUCT(M$14:M17,AJ$14:AJ17)+J17*AJ17","v":1421.2216647957673},null,{"$f":"VLOOKUP(B17,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))*AM$9+('Prices and weights'!H$19-E17)*'Prices and weights'!H$20","v":163.20991582925473},73.368378964872065,541.59102750841612,83.000426227278552,467.56906774700258,189.44816652865723,{"$f":"SUMPRODUCT(AR$14:AR17,N$14:N17)+AQ17*N17","v":1756.7995625098374}]
[null,2020,50.983170062500001,5,572,{"$f":"VLOOKUP(B18,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E18)*'Prices and weights'!H$20","v":140.85568624673178},63.717757948561989,398.44555173289439,60.909999177989008,343.12632870267146,371.3730384668462,0,87.982512444037184,0.7488005297763749,{"$f":"SUMPRODUCT(M$14:M18,N$14:N18)+J18*N18","v":1388.2695870468397},null,null,297.09843077347693,334.23573462016151,371.3730384668462,408.51034231353077,445.64764616021546,null,64.164105062499999,57.289761000000006,50.983170062500001,45.212176000000014,39.945560062500007,null,85.17661752418735,87.458480031666625,87.982512444037184,87.025968882189886,84.842483120815757,null,0.77006691563934571,{"$f":"SUMPRODUCT(M$14:M18,AJ$14:AJ18)+J18*AJ18","v":1403.7829712542309},null,{"$f":"VLOOKUP(B18,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))*AM$9+('Prices and weights'!H$19-E18)*'Prices and weights'!H$20","v":155.33699487140498},70.089533743418187,439.409556513398,67.000999095787904,377.43896157293852,135.03751714233971,{"$f":"SUMPRODUCT(AR$14:AR18,N$14:N18)+AQ18*N18","v":1764.1675582110674}]
[null,2021,43.080778702812502,6,572,{"$f":"VLOOKUP(B19,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E19)*'Prices and weights'!H$20","v":139.0960196178078},62.943504631835431,332.48037076019131,50.843534884191669,286.4185798476131,316.94831967952985,0,66.375585964853144,0.69655863235011617,{"$f":"SUMPRODUCT(M$14:M19,N$14:N19)+J19*N19","v":1377.0782319734883},null,null,253.55865574362386,285.25348771157689,316.94831967952985,348.6431516474828,380.3379836154358,null,57.426874030937498,49.842092070000007,43.080778702812502,37.073984320000015,31.756720249687508,null,65.887403656748631,66.989040803827947,66.375585964853144,64.413169539317778,61.421333732678022,null,0.72137415984950426,{"$f":"SUMPRODUCT(M$14:M19,AJ$14:AJ19)+J19*AJ19","v":1394.049332598287},null,{"$f":"VLOOKUP(B19,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))*AM$9+('Prices and weights'!H$19-E19)*'Prices and weights'!H$20","v":153.40136157958858},69.237855095018986,366.67434275430634,55.927888372610845,315.06043783237448,105.65391144738732,{"$f":"SUMPRODUCT(AR$14:AR19,N$14:N19)+AQ19*N19","v":1774.5932755713864}]
[null,2022,36.403258003876566,7,567,{"$f":"VLOOKUP(B20,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E20)*'Prices and weights'!H$20","v":136.70867849224456},61.707834536587598,273.71029455742593,42.119366653048566,237.27243214550685,270.49954343049484,0,45.330117779979673,0.64796151846522443,{"$f":"SUMPRODUCT(M$14:M20,N$14:N20)+J20*N20","v":1360.686475087035},null,null,216.39963474439585,243.44958908744536,270.49954343049484,297.54949777354432,324.5994521165938,null,51.397052257689062,43.362620100900003,36.403258003876566,30.400667142400014,25.246592598501572,null,44.178207050526112,45.634147921621832,45.330117779979673,43.717935803900133,41.174552178977137,null,0.67576033709555439,{"$f":"SUMPRODUCT(M$14:M20,AJ$14:AJ20)+J20*AJ20","v":1378.405964597137},null,{"$f":"VLOOKUP(B20,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))*AM$9+('Prices and weights'!H$19-E20)*'Prices and weights'!H$20","v":150.73318634146904},67.878617990246369,301.78936178834738,46.331303318353427,260.99967536005761,77.621121676205973,{"$f":"SUMPRODUCT(AR$14:AR20,N$14:N20)+AQ20*N20","v":1774.5484537187606}]
[null,2023,30.760753013275696,8,565,{"$f":"VLOOKUP(B21,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E21)*'Prices and weights'!H$20","v":135.2988814307524},61.013427829531054,228.09268148895222,35.190355949203628,198.23900518051374,230.85783534075571,0,32.42520209740016,0.60275490089788319,{"$f":"SUMPRODUCT(M$14:M21,N$14:N21)+J21*N21","v":1345.9770510625833},null,null,184.68626827260456,207.77205180668014,230.85783534075571,253.94361887483126,277.02940240890689,null,46.000361770631713,37.725479487783005,30.760753013275696,24.928547056768014,20.07104111580875,null,30.94792006733951,32.573783903317917,32.42520209740016,31.030468703707953,28.810844058823278,null,0.63303076074525011,{"$f":"SUMPRODUCT(M$14:M21,AJ$14:AJ21)+J21*AJ21","v":1364.0842044768451},null,{"$f":"VLOOKUP(B21,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))*AM$9+('Prices and weights'!H$19-E21)*'Prices and weights'!H$20","v":149.16556957382767},67.114770612484165,251.46974158345049,38.709391544123996,218.06290569856515,59.321297786818747,{"$f":"SUMPRODUCT(AR$14:AR21,N$14:N21)+AQ21*N21","v":1772.625395835998}]
[null,2024,{"$f":"C21*(1-N$4-N$5)","v":25.992836296217963},{"$f":"IF(D21>0,D21+1,IF(G$5=B22,1,0))","v":9},{"$f":"VLOOKUP(D22,weights,IF(G$9=1,2,IF(G$9=2,3,4)))*(1+N$8)^(B22-G$5)","v":562},{"$f":"VLOOKUP(B22,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E22)*'Prices and weights'!H$20","v":136.33143790220549},{"$f":"VLOOKUP(B22,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":61.356608676970417},{"$f":"IF(B22>=G$5,E22*F22/100*N$3*C22/G$3,0)","v":193.17803170485561},{"$f":"G$6*G22/100*(N$5*C22/G$3)","v":29.903105344342471},168.45416010646258,{"$f":"G$7*(1+N$7)^(B22-G$4)*C22/G$3","v":197.02561957156803},0,{"$f":"H22+I22-K22-L22","v":26.055517477630048},{"$f":"1/(1+N$9)^(B22-G$4)","v":0.56070223339337966},{"$f":"SUMPRODUCT(M$14:M22,N$14:N22)+J22*N22","v":1335.5495297789348},null,null,157.62049565725439,177.3230576144112,197.02561957156803,216.7281815287248,236.43074348588161,null,41.170323784715386,32.821167154371217,25.992836296217963,20.441408586549773,15.956477687067958,null,25.481658625189596,26.607203746005212,26.055517477630048,24.410126076863293,22.11392738265593,null,0.59300305456229518,{"$f":"SUMPRODUCT(M$14:M22,AJ$14:AJ22)+J22*AJ22","v":1353.937649167337},null,{"$f":"VLOOKUP(B22,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))*AM$9+('Prices and weights'!H$19-E22)*'Prices and weights'!H$20","v":150.27612169242605},67.492269544667465,212.93727879263122,32.893415878776722,185.29957611710887,48.805075099839911,{"$f":"SUMPRODUCT(AR$14:AR22,N$14:N22)+AQ22*N22","v":1772.4499115072763}]
[null,2025,{"$f":"C22*(1-N$4-N$5)","v":21.963946670304178},{"$f":"IF(D22>0,D22+1,IF(G$5=B23,1,0))","v":10},{"$f":"VLOOKUP(D23,weights,IF(G$9=1,2,IF(G$9=2,3,4)))*(1+N$8)^(B23-G$5)","v":559},{"$f":"VLOOKUP(B23,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E23)*'Prices and weights'!H$20","v":137.3972},{"$f":"VLOOKUP(B23,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":61.714399999999998},{"$f":"IF(B23>=G$5,E23*F23/100*N$3*C23/G$3,0)","v":163.63334377073147},{"$f":"G$6*G23/100*(N$5*C23/G$3)","v":25.415471069809129},143.17382035992475,{"$f":"G$7*(1+N$7)^(B23-G$4)*C23/G$3","v":168.15151502335473},0,{"$f":"H23+I23-K23-L23","v":20.897299817185882},{"$f":"1/(1+N$9)^(B23-G$4)","v":0.52158347292407414},{"$f":"SUMPRODUCT(M$14:M23,N$14:N23)+J23*N23","v":1326.6736906513556},null,null,134.52121201868377,151.33636352101925,168.15151502335473,184.96666652569019,201.78181802802567,null,36.847439787320269,28.55441542430296,21.963946670304178,16.761955040970815,12.685399761219028,null,20.845387684656544,21.660778950416159,20.897299817185882,19.180604025314494,16.962300897778533,null,0.55550637429723215,{"$f":"SUMPRODUCT(M$14:M23,AJ$14:AJ23)+J23*AJ23","v":1345.1863707669509},null,{"$f":"VLOOKUP(B23,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))*AM$9+('Prices and weights'!H$19-E23)*'Prices and weights'!H$20","v":151.42320000000001},67.885840000000002,180.33762362307408,27.957018176790044,157.49120239591721,40.143126776509376,{"$f":"SUMPRODUCT(AR$14:AR23,N$14:N23)+AQ23*N23","v":1771.6348251103407}]
[null,"* BOY = Beginning of year",null,null,{"$f":"AVERAGE(E14:E23)","v":562},{"$f":"AVERAGE(F14:F23)","v":149.96900675087485},{"$f":"AVERAGE(G14:G23)","v":67.357138970384923},null,null,null,null,null,null,null,"** Net present value if replacement is sold in this year"]
[]
[null,"The Net Present Value (NPV) reflects the amount that could be paid for a replacement such that the rate of return from the investment would be exactly equal to the discount rate given all of the assumptions used in the analysis.  If you have a targeted rate of return for investing in replacements, enter that as the discount rate."]
[]
[]
[]
[]
[null,"Sensitivity Analysis of NPV to varying annual cow costs"]
[null,null,null,"Annual cow costs, $/year"]
[null,null,null,{"$f":"E33-H46","v":560},{"$f":"F33-H46","v":630},{"$f":"G7","v":700},{"$f":"F33+H46","v":770},{"$f":"G33+H46","v":840}]
[null,"Year","Calf","NPV**","NPV**","NPV**","NPV**","NPV**"]
[null,{"$f":"B14","v":2016},{"$f":"D14","v":1},{"$f":"SUMPRODUCT(M$14:M14,N$14:N14)+J14*N14+SUMPRODUCT(K$14:K14,N$14:N14)-SUMPRODUCT(R$14:R14,N$14:N14)","v":1632.1040576400001},{"$f":"SUMPRODUCT(M$14:M14,N$14:N14)+J14*N14+SUMPRODUCT(K$14:K14,N$14:N14)-SUMPRODUCT(S$14:S14,N$14:N14)","v":1562.1040576400001},{"$f":"SUMPRODUCT(M$14:M14,N$14:N14)+J14*N14+SUMPRODUCT(K$14:K14,N$14:N14)-SUMPRODUCT(T$14:T14,N$14:N14)","v":1492.1040576400001},{"$f":"SUMPRODUCT(M$14:M14,N$14:N14)+J14*N14+SUMPRODUCT(K$14:K14,N$14:N14)-SUMPRODUCT(U$14:U14,N$14:N14)","v":1422.1040576400001},{"$f":"SUMPRODUCT(M$14:M14,N$14:N14)+J14*N14+SUMPRODUCT(K$14:K14,N$14:N14)-SUMPRODUCT(V$14:V14,N$14:N14)","v":1352.1040576400001}]
[null,{"$f":"+B35+1","v":2017},{"$f":"D15","v":2},{"$f":"SUMPRODUCT(M$14:M15,N$14:N15)+J15*N15+SUMPRODUCT(K$14:K15,N$14:N15)-SUMPRODUCT(R$14:R15,N$14:N15)","v":1668.3177972774829},{"$f":"SUMPRODUCT(M$14:M15,N$14:N15)+J15*N15+SUMPRODUCT(K$14:K15,N$14:N15)-SUMPRODUCT(S$14:S15,N$14:N15)","v":1542.74430890539},{"$f":"SUMPRODUCT(M$14:M15,N$14:N15)+J15*N15+SUMPRODUCT(K$14:K15,N$14:N15)-SUMPRODUCT(T$14:T15,N$14:N15)","v":1417.170820533297},{"$f":"SUMPRODUCT(M$14:M15,N$14:N15)+J15*N15+SUMPRODUCT(K$14:K15,N$14:N15)-SUMPRODUCT(U$14:U15,N$14:N15)","v":1291.5973321612039},{"$f":"SUMPRODUCT(M$14:M15,N$14:N15)+J15*N15+SUMPRODUCT(K$14:K15,N$14:N15)-SUMPRODUCT(V$14:V15,N$14:N15)","v":1166.0238437891107}]
[null,2018,3,{"$f":"SUMPRODUCT(M$14:M16,N$14:N16)+J16*N16+SUMPRODUCT(K$14:K16,N$14:N16)-SUMPRODUCT(R$14:R16,N$14:N16)","v":1753.1945449640189},{"$f":"SUMPRODUCT(M$14:M16,N$14:N16)+J16*N16+SUMPRODUCT(K$14:K16,N$14:N16)-SUMPRODUCT(S$14:S16,N$14:N16)","v":1583.5008764513091},{"$f":"SUMPRODUCT(M$14:M16,N$14:N16)+J16*N16+SUMPRODUCT(K$14:K16,N$14:N16)-SUMPRODUCT(T$14:T16,N$14:N16)","v":1413.8072079385997},{"$f":"SUMPRODUCT(M$14:M16,N$14:N16)+J16*N16+SUMPRODUCT(K$14:K16,N$14:N16)-SUMPRODUCT(U$14:U16,N$14:N16)","v":1244.1135394258899},{"$f":"SUMPRODUCT(M$14:M16,N$14:N16)+J16*N16+SUMPRODUCT(K$14:K16,N$14:N16)-SUMPRODUCT(V$14:V16,N$14:N16)","v":1074.4198709131801}]
[null,2019,4,{"$f":"SUMPRODUCT(M$14:M17,N$14:N17)+J17*N17+SUMPRODUCT(K$14:K17,N$14:N17)-SUMPRODUCT(R$14:R17,N$14:N17)","v":1817.0558176868353},{"$f":"SUMPRODUCT(M$14:M17,N$14:N17)+J17*N17+SUMPRODUCT(K$14:K17,N$14:N17)-SUMPRODUCT(S$14:S17,N$14:N17)","v":1612.3348303452797},{"$f":"SUMPRODUCT(M$14:M17,N$14:N17)+J17*N17+SUMPRODUCT(K$14:K17,N$14:N17)-SUMPRODUCT(T$14:T17,N$14:N17)","v":1407.6138430037245},{"$f":"SUMPRODUCT(M$14:M17,N$14:N17)+J17*N17+SUMPRODUCT(K$14:K17,N$14:N17)-SUMPRODUCT(U$14:U17,N$14:N17)","v":1202.892855662169},{"$f":"SUMPRODUCT(M$14:M17,N$14:N17)+J17*N17+SUMPRODUCT(K$14:K17,N$14:N17)-SUMPRODUCT(V$14:V17,N$14:N17)","v":998.17186832061361}]
[null,2020,5,{"$f":"SUMPRODUCT(M$14:M18,N$14:N18)+J18*N18+SUMPRODUCT(K$14:K18,N$14:N18)-SUMPRODUCT(R$14:R18,N$14:N18)","v":1853.328427319678},{"$f":"SUMPRODUCT(M$14:M18,N$14:N18)+J18*N18+SUMPRODUCT(K$14:K18,N$14:N18)-SUMPRODUCT(S$14:S18,N$14:N18)","v":1620.7990071832587},{"$f":"SUMPRODUCT(M$14:M18,N$14:N18)+J18*N18+SUMPRODUCT(K$14:K18,N$14:N18)-SUMPRODUCT(T$14:T18,N$14:N18)","v":1388.2695870468397},{"$f":"SUMPRODUCT(M$14:M18,N$14:N18)+J18*N18+SUMPRODUCT(K$14:K18,N$14:N18)-SUMPRODUCT(U$14:U18,N$14:N18)","v":1155.7401669104206},{"$f":"SUMPRODUCT(M$14:M18,N$14:N18)+J18*N18+SUMPRODUCT(K$14:K18,N$14:N18)-SUMPRODUCT(V$14:V18,N$14:N18)","v":923.21074677400156}]
[null,2021,6,{"$f":"SUMPRODUCT(M$14:M19,N$14:N19)+J19*N19+SUMPRODUCT(K$14:K19,N$14:N19)-SUMPRODUCT(R$14:R19,N$14:N19)","v":1886.2916898626549},{"$f":"SUMPRODUCT(M$14:M19,N$14:N19)+J19*N19+SUMPRODUCT(K$14:K19,N$14:N19)-SUMPRODUCT(S$14:S19,N$14:N19)","v":1631.6849609180717},{"$f":"SUMPRODUCT(M$14:M19,N$14:N19)+J19*N19+SUMPRODUCT(K$14:K19,N$14:N19)-SUMPRODUCT(T$14:T19,N$14:N19)","v":1377.0782319734885},{"$f":"SUMPRODUCT(M$14:M19,N$14:N19)+J19*N19+SUMPRODUCT(K$14:K19,N$14:N19)-SUMPRODUCT(U$14:U19,N$14:N19)","v":1122.4715030289053},{"$f":"SUMPRODUCT(M$14:M19,N$14:N19)+J19*N19+SUMPRODUCT(K$14:K19,N$14:N19)-SUMPRODUCT(V$14:V19,N$14:N19)","v":867.86477408432211}]
[null,2022,7,{"$f":"SUMPRODUCT(M$14:M20,N$14:N20)+J20*N20+SUMPRODUCT(K$14:K20,N$14:N20)-SUMPRODUCT(R$14:R20,N$14:N20)","v":1904.9545919572761},{"$f":"SUMPRODUCT(M$14:M20,N$14:N20)+J20*N20+SUMPRODUCT(K$14:K20,N$14:N20)-SUMPRODUCT(S$14:S20,N$14:N20)","v":1632.8205335221555},{"$f":"SUMPRODUCT(M$14:M20,N$14:N20)+J20*N20+SUMPRODUCT(K$14:K20,N$14:N20)-SUMPRODUCT(T$14:T20,N$14:N20)","v":1360.686475087035},{"$f":"SUMPRODUCT(M$14:M20,N$14:N20)+J20*N20+SUMPRODUCT(K$14:K20,N$14:N20)-SUMPRODUCT(U$14:U20,N$14:N20)","v":1088.5524166519144},{"$f":"SUMPRODUCT(M$14:M20,N$14:N20)+J20*N20+SUMPRODUCT(K$14:K20,N$14:N20)-SUMPRODUCT(V$14:V20,N$14:N20)","v":816.41835821679388}]
[null,2023,8,{"$f":"SUMPRODUCT(M$14:M21,N$14:N21)+J21*N21+SUMPRODUCT(K$14:K21,N$14:N21)-SUMPRODUCT(R$14:R21,N$14:N21)","v":1918.0753062652875},{"$f":"SUMPRODUCT(M$14:M21,N$14:N21)+J21*N21+SUMPRODUCT(K$14:K21,N$14:N21)-SUMPRODUCT(S$14:S21,N$14:N21)","v":1632.0261786639353},{"$f":"SUMPRODUCT(M$14:M21,N$14:N21)+J21*N21+SUMPRODUCT(K$14:K21,N$14:N21)-SUMPRODUCT(T$14:T21,N$14:N21)","v":1345.9770510625831},{"$f":"SUMPRODUCT(M$14:M21,N$14:N21)+J21*N21+SUMPRODUCT(K$14:K21,N$14:N21)-SUMPRODUCT(U$14:U21,N$14:N21)","v":1059.9279234612309},{"$f":"SUMPRODUCT(M$14:M21,N$14:N21)+J21*N21+SUMPRODUCT(K$14:K21,N$14:N21)-SUMPRODUCT(V$14:V21,N$14:N21)","v":773.87879585987866}]
[null,2024,9,{"$f":"SUMPRODUCT(M$14:M22,N$14:N22)+J22*N22+SUMPRODUCT(K$14:K22,N$14:N22)-SUMPRODUCT(R$14:R22,N$14:N22)","v":1929.7423259675375},{"$f":"SUMPRODUCT(M$14:M22,N$14:N22)+J22*N22+SUMPRODUCT(K$14:K22,N$14:N22)-SUMPRODUCT(S$14:S22,N$14:N22)","v":1632.6459278732364},{"$f":"SUMPRODUCT(M$14:M22,N$14:N22)+J22*N22+SUMPRODUCT(K$14:K22,N$14:N22)-SUMPRODUCT(T$14:T22,N$14:N22)","v":1335.5495297789348},{"$f":"SUMPRODUCT(M$14:M22,N$14:N22)+J22*N22+SUMPRODUCT(K$14:K22,N$14:N22)-SUMPRODUCT(U$14:U22,N$14:N22)","v":1038.4531316846333},{"$f":"SUMPRODUCT(M$14:M22,N$14:N22)+J22*N22+SUMPRODUCT(K$14:K22,N$14:N22)-SUMPRODUCT(V$14:V22,N$14:N22)","v":741.35673359033171}]
[null,2025,10,{"$f":"SUMPRODUCT(M$14:M23,N$14:N23)+J23*N23+SUMPRODUCT(K$14:K23,N$14:N23)-SUMPRODUCT(R$14:R23,N$14:N23)","v":1938.4074970766237},{"$f":"SUMPRODUCT(M$14:M23,N$14:N23)+J23*N23+SUMPRODUCT(K$14:K23,N$14:N23)-SUMPRODUCT(S$14:S23,N$14:N23)","v":1632.5405938639901},{"$f":"SUMPRODUCT(M$14:M23,N$14:N23)+J23*N23+SUMPRODUCT(K$14:K23,N$14:N23)-SUMPRODUCT(T$14:T23,N$14:N23)","v":1326.6736906513561},{"$f":"SUMPRODUCT(M$14:M23,N$14:N23)+J23*N23+SUMPRODUCT(K$14:K23,N$14:N23)-SUMPRODUCT(U$14:U23,N$14:N23)","v":1020.8067874387216},{"$f":"SUMPRODUCT(M$14:M23,N$14:N23)+J23*N23+SUMPRODUCT(K$14:K23,N$14:N23)-SUMPRODUCT(V$14:V23,N$14:N23)","v":714.93988422608754}]
[null,"** Net present value if replacement is sold in this year"]
[null,"Increment to use for annual costs/cow ",null,null,null,null,null,{"$f":"G7*0.1","v":70}]
[]
[null,"Values to include in chart (Calf number)"]
[null,null,null,"Annual cow costs, $/year"]
[null,"Calves",null,{"$f":"D33","v":560},{"$f":"E33","v":630},{"$f":"F33","v":700},{"$f":"G33","v":770},{"$f":"H33","v":840}]
[null,4,{"$f":"B51&\" calves\"","v":"4 calves"},{"$f":"VLOOKUP($B51,$C$35:$H$44,2)","v":1817.0558176868353},{"$f":"VLOOKUP($B51,$C$35:$H$44,3)","v":1612.3348303452797},{"$f":"VLOOKUP($B51,$C$35:$H$44,4)","v":1407.6138430037245},{"$f":"VLOOKUP($B51,$C$35:$H$44,5)","v":1202.892855662169},{"$f":"VLOOKUP($B51,$C$35:$H$44,6)","v":998.17186832061361}]
[null,7,{"$f":"B52&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B52,$C$35:$H$44,2)","v":1904.9545919572761},{"$f":"VLOOKUP($B52,$C$35:$H$44,3)","v":1632.8205335221555},{"$f":"VLOOKUP($B52,$C$35:$H$44,4)","v":1360.686475087035},{"$f":"VLOOKUP($B52,$C$35:$H$44,5)","v":1088.5524166519144},{"$f":"VLOOKUP($B52,$C$35:$H$44,6)","v":816.41835821679388}]
[null,10,{"$f":"B53&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B53,$C$35:$H$44,2)","v":1938.4074970766237},{"$f":"VLOOKUP($B53,$C$35:$H$44,3)","v":1632.5405938639901},{"$f":"VLOOKUP($B53,$C$35:$H$44,4)","v":1326.6736906513561},{"$f":"VLOOKUP($B53,$C$35:$H$44,5)","v":1020.8067874387216},{"$f":"VLOOKUP($B53,$C$35:$H$44,6)","v":714.93988422608754}]
[]
[]
[]
[null,"Sensitivity Analysis of NPV to varying annual cow cull rates"]
[null,null,null,"Annual cow cull rate, %"]
[null,null,null,{"$f":"E59-H72","v":0.1},{"$f":"F59-H72","v":0.125},{"$f":"N5","v":0.15},{"$f":"F59+H72","v":0.17499999999999999},{"$f":"G59+H72","v":0.19999999999999998}]
[null,"Year","Calf","NPV**","NPV**","NPV**","NPV**","NPV**"]
[null,{"$f":"B14","v":2016},{"$f":"D14","v":1},{"$f":"SUMPRODUCT(AD$14:AD14,N$14:N14)+IF(B14>=G$5,G$6*G14/100*(X14*(1-N$4-X$12))/G$3,0)*N14","v":1492.1040576400001},{"$f":"SUMPRODUCT(AE$14:AE14,N$14:N14)+IF(B14>=G$5,G$6*G14/100*(Y14*(1-N$4-Y$12))/G$3,0)*N14","v":1492.1040576400001},{"$f":"SUMPRODUCT(AF$14:AF14,N$14:N14)+IF(B14>=G$5,G$6*G14/100*(Z14*(1-N$4-Z$12))/G$3,0)*N14","v":1492.1040576400001},{"$f":"SUMPRODUCT(AG$14:AG14,N$14:N14)+IF(B14>=G$5,G$6*G14/100*(AA14*(1-N$4-AA$12))/G$3,0)*N14","v":1492.1040576400001},{"$f":"SUMPRODUCT(AH$14:AH14,N$14:N14)+IF(B14>=G$5,G$6*G14/100*(AB14*(1-N$4-AB$12))/G$3,0)*N14","v":1492.1040576400001}]
[null,{"$f":"+B61+1","v":2017},{"$f":"D15","v":2},{"$f":"SUMPRODUCT(AD$14:AD15,N$14:N15)+IF(B15>=G$5,G$6*G15/100*(X15*(1-N$4-X$12))/G$3,0)*N15","v":1412.736901177871},{"$f":"SUMPRODUCT(AE$14:AE15,N$14:N15)+IF(B15>=G$5,G$6*G15/100*(Y15*(1-N$4-Y$12))/G$3,0)*N15","v":1414.953860855584},{"$f":"SUMPRODUCT(AF$14:AF15,N$14:N15)+IF(B15>=G$5,G$6*G15/100*(Z15*(1-N$4-Z$12))/G$3,0)*N15","v":1417.1708205332973},{"$f":"SUMPRODUCT(AG$14:AG15,N$14:N15)+IF(B15>=G$5,G$6*G15/100*(AA15*(1-N$4-AA$12))/G$3,0)*N15","v":1419.3877802110105},{"$f":"SUMPRODUCT(AH$14:AH15,N$14:N15)+IF(B15>=G$5,G$6*G15/100*(AB15*(1-N$4-AB$12))/G$3,0)*N15","v":1421.6047398887233}]
[null,2018,3,{"$f":"SUMPRODUCT(AD$14:AD16,N$14:N16)+IF(B16>=G$5,G$6*G16/100*(X16*(1-N$4-X$12))/G$3,0)*N16","v":1408.9634509784137},{"$f":"SUMPRODUCT(AE$14:AE16,N$14:N16)+IF(B16>=G$5,G$6*G16/100*(Y16*(1-N$4-Y$12))/G$3,0)*N16","v":1411.388273694173},{"$f":"SUMPRODUCT(AF$14:AF16,N$14:N16)+IF(B16>=G$5,G$6*G16/100*(Z16*(1-N$4-Z$12))/G$3,0)*N16","v":1413.8072079385997},{"$f":"SUMPRODUCT(AG$14:AG16,N$14:N16)+IF(B16>=G$5,G$6*G16/100*(AA16*(1-N$4-AA$12))/G$3,0)*N16","v":1416.2202537116937},{"$f":"SUMPRODUCT(AH$14:AH16,N$14:N16)+IF(B16>=G$5,G$6*G16/100*(AB16*(1-N$4-AB$12))/G$3,0)*N16","v":1418.6274110134545}]
[null,2019,4,{"$f":"SUMPRODUCT(AD$14:AD17,N$14:N17)+IF(B17>=G$5,G$6*G17/100*(X17*(1-N$4-X$12))/G$3,0)*N17","v":1401.6043349553388},{"$f":"SUMPRODUCT(AE$14:AE17,N$14:N17)+IF(B17>=G$5,G$6*G17/100*(Y17*(1-N$4-Y$12))/G$3,0)*N17","v":1404.6287779035415},{"$f":"SUMPRODUCT(AF$14:AF17,N$14:N17)+IF(B17>=G$5,G$6*G17/100*(Z17*(1-N$4-Z$12))/G$3,0)*N17","v":1407.6138430037245},{"$f":"SUMPRODUCT(AG$14:AG17,N$14:N17)+IF(B17>=G$5,G$6*G17/100*(AA17*(1-N$4-AA$12))/G$3,0)*N17","v":1410.5604925942973},{"$f":"SUMPRODUCT(AH$14:AH17,N$14:N17)+IF(B17>=G$5,G$6*G17/100*(AB17*(1-N$4-AB$12))/G$3,0)*N17","v":1413.4696890136706}]
[null,2020,5,{"$f":"SUMPRODUCT(AD$14:AD18,N$14:N18)+IF(B18>=G$5,G$6*G18/100*(X18*(1-N$4-X$12))/G$3,0)*N18","v":1377.2589113566912},{"$f":"SUMPRODUCT(AE$14:AE18,N$14:N18)+IF(B18>=G$5,G$6*G18/100*(Y18*(1-N$4-Y$12))/G$3,0)*N18","v":1382.8916478048404},{"$f":"SUMPRODUCT(AF$14:AF18,N$14:N18)+IF(B18>=G$5,G$6*G18/100*(Z18*(1-N$4-Z$12))/G$3,0)*N18","v":1388.2695870468397},{"$f":"SUMPRODUCT(AG$14:AG18,N$14:N18)+IF(B18>=G$5,G$6*G18/100*(AA18*(1-N$4-AA$12))/G$3,0)*N18","v":1393.4058922852237},{"$f":"SUMPRODUCT(AH$14:AH18,N$14:N18)+IF(B18>=G$5,G$6*G18/100*(AB18*(1-N$4-AB$12))/G$3,0)*N18","v":1398.3133710122038}]
[null,2021,6,{"$f":"SUMPRODUCT(AD$14:AD19,N$14:N19)+IF(B19>=G$5,G$6*G19/100*(X19*(1-N$4-X$12))/G$3,0)*N19","v":1362.3407841225871},{"$f":"SUMPRODUCT(AE$14:AE19,N$14:N19)+IF(B19>=G$5,G$6*G19/100*(Y19*(1-N$4-Y$12))/G$3,0)*N19","v":1369.9438653656705},{"$f":"SUMPRODUCT(AF$14:AF19,N$14:N19)+IF(B19>=G$5,G$6*G19/100*(Z19*(1-N$4-Z$12))/G$3,0)*N19","v":1377.0782319734883},{"$f":"SUMPRODUCT(AG$14:AG19,N$14:N19)+IF(B19>=G$5,G$6*G19/100*(AA19*(1-N$4-AA$12))/G$3,0)*N19","v":1383.7749586066293},{"$f":"SUMPRODUCT(AH$14:AH19,N$14:N19)+IF(B19>=G$5,G$6*G19/100*(AB19*(1-N$4-AB$12))/G$3,0)*N19","v":1390.0637352586111}]
[null,2022,7,{"$f":"SUMPRODUCT(AD$14:AD20,N$14:N20)+IF(B20>=G$5,G$6*G20/100*(X20*(1-N$4-X$12))/G$3,0)*N20","v":1339.1975812477604},{"$f":"SUMPRODUCT(AE$14:AE20,N$14:N20)+IF(B20>=G$5,G$6*G20/100*(Y20*(1-N$4-Y$12))/G$3,0)*N20","v":1350.4184282034832},{"$f":"SUMPRODUCT(AF$14:AF20,N$14:N20)+IF(B20>=G$5,G$6*G20/100*(Z20*(1-N$4-Z$12))/G$3,0)*N20","v":1360.686475087035},{"$f":"SUMPRODUCT(AG$14:AG20,N$14:N20)+IF(B20>=G$5,G$6*G20/100*(AA20*(1-N$4-AA$12))/G$3,0)*N20","v":1370.0860644716279},{"$f":"SUMPRODUCT(AH$14:AH20,N$14:N20)+IF(B20>=G$5,G$6*G20/100*(AB20*(1-N$4-AB$12))/G$3,0)*N20","v":1378.6956316662925}]
[null,2023,8,{"$f":"SUMPRODUCT(AD$14:AD21,N$14:N21)+IF(B21>=G$5,G$6*G21/100*(X21*(1-N$4-X$12))/G$3,0)*N21","v":1317.2007586644388},{"$f":"SUMPRODUCT(AE$14:AE21,N$14:N21)+IF(B21>=G$5,G$6*G21/100*(Y21*(1-N$4-Y$12))/G$3,0)*N21","v":1332.378555315064},{"$f":"SUMPRODUCT(AF$14:AF21,N$14:N21)+IF(B21>=G$5,G$6*G21/100*(Z21*(1-N$4-Z$12))/G$3,0)*N21","v":1345.9770510625833},{"$f":"SUMPRODUCT(AG$14:AG21,N$14:N21)+IF(B21>=G$5,G$6*G21/100*(AA21*(1-N$4-AA$12))/G$3,0)*N21","v":1358.1655315505627},{"$f":"SUMPRODUCT(AH$14:AH21,N$14:N21)+IF(B21>=G$5,G$6*G21/100*(AB21*(1-N$4-AB$12))/G$3,0)*N21","v":1369.0978999639112}]
[null,2024,9,{"$f":"SUMPRODUCT(AD$14:AD22,N$14:N22)+IF(B22>=G$5,G$6*G22/100*(X22*(1-N$4-X$12))/G$3,0)*N22","v":1300.6844992371332},{"$f":"SUMPRODUCT(AE$14:AE22,N$14:N22)+IF(B22>=G$5,G$6*G22/100*(Y22*(1-N$4-Y$12))/G$3,0)*N22","v":1319.2117190700255},{"$f":"SUMPRODUCT(AF$14:AF22,N$14:N22)+IF(B22>=G$5,G$6*G22/100*(Z22*(1-N$4-Z$12))/G$3,0)*N22","v":1335.5495297789348},{"$f":"SUMPRODUCT(AG$14:AG22,N$14:N22)+IF(B22>=G$5,G$6*G22/100*(AA22*(1-N$4-AA$12))/G$3,0)*N22","v":1349.9650712131497},{"$f":"SUMPRODUCT(AH$14:AH22,N$14:N22)+IF(B22>=G$5,G$6*G22/100*(AB22*(1-N$4-AB$12))/G$3,0)*N22","v":1362.6966550671325}]
[null,2025,10,{"$f":"SUMPRODUCT(AD$14:AD23,N$14:N23)+IF(B23>=G$5,G$6*G23/100*(X23*(1-N$4-X$12))/G$3,0)*N23","v":1285.7940994048665},{"$f":"SUMPRODUCT(AE$14:AE23,N$14:N23)+IF(B23>=G$5,G$6*G23/100*(Y23*(1-N$4-Y$12))/G$3,0)*N23","v":1307.6726090233399},{"$f":"SUMPRODUCT(AF$14:AF23,N$14:N23)+IF(B23>=G$5,G$6*G23/100*(Z23*(1-N$4-Z$12))/G$3,0)*N23","v":1326.6736906513556},{"$f":"SUMPRODUCT(AG$14:AG23,N$14:N23)+IF(B23>=G$5,G$6*G23/100*(AA23*(1-N$4-AA$12))/G$3,0)*N23","v":1343.1914062320507},{"$f":"SUMPRODUCT(AH$14:AH23,N$14:N23)+IF(B23>=G$5,G$6*G23/100*(AB23*(1-N$4-AB$12))/G$3,0)*N23","v":1357.5703647314417}]
[null,"** Net present value if replacement is sold in this year"]
[null,"Increment to use for cow cull rate",null,null,null,null,null,2.5000000000000001E-2]
[]
[null,"Values to include in chart (Calf number)"]
[null,null,null,"Annual cow cull rate, %"]
[null,"Calves",null,{"$f":"D59","v":0.1},{"$f":"E59","v":0.125},{"$f":"F59","v":0.15},{"$f":"G59","v":0.17499999999999999},{"$f":"H59","v":0.19999999999999998}]
[null,4,{"$f":"B77&\" calves\"","v":"4 calves"},{"$f":"VLOOKUP($B77,$C$61:$H$70,2)","v":1401.6043349553388},{"$f":"VLOOKUP($B77,$C$61:$H$70,3)","v":1404.6287779035415},{"$f":"VLOOKUP($B77,$C$61:$H$70,4)","v":1407.6138430037245},{"$f":"VLOOKUP($B77,$C$61:$H$70,5)","v":1410.5604925942973},{"$f":"VLOOKUP($B77,$C$61:$H$70,6)","v":1413.4696890136706}]
[null,7,{"$f":"B78&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B78,$C$61:$H$70,2)","v":1339.1975812477604},{"$f":"VLOOKUP($B78,$C$61:$H$70,3)","v":1350.4184282034832},{"$f":"VLOOKUP($B78,$C$61:$H$70,4)","v":1360.686475087035},{"$f":"VLOOKUP($B78,$C$61:$H$70,5)","v":1370.0860644716279},{"$f":"VLOOKUP($B78,$C$61:$H$70,6)","v":1378.6956316662925}]
[null,10,{"$f":"B79&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B79,$C$61:$H$70,2)","v":1285.7940994048665},{"$f":"VLOOKUP($B79,$C$61:$H$70,3)","v":1307.6726090233399},{"$f":"VLOOKUP($B79,$C$61:$H$70,4)","v":1326.6736906513556},{"$f":"VLOOKUP($B79,$C$61:$H$70,5)","v":1343.1914062320507},{"$f":"VLOOKUP($B79,$C$61:$H$70,6)","v":1357.5703647314417}]

{"sheet":"Sensitivity Table","index":3,"n_rows":35,"n_cols":14,"rows_kept":35,"cut":false,"hidden":false,"merged":[[4,1,4,6],[34,1,35,6],[31,1,32,6]]}
[]
[null,"Sensitivity of results in \"Net present value\" tab"]
[null,"Range NPV values are pulled from",null,"O14:O23","E35:E44","AK14:AK23","AS14:AS23"]
[]
[null,"Table 1. Net Present Value of Beef Replacements, Sensitivity Analysis Summary*",null,null,null,null,null,null,"Marginal Impacts (relative to Base Case):"]
[null,null,"Number",null,"10% Lower","10% Lower","10% Higher",null,"Costs","Discount","Prices","Costs","Discount","Prices"]
[null,"Year","of Calves","Base Case","Cow Costs/yr","Discount Rate","Calf Prices",null,"$/head","$/head","$/head","%","%","%"]
[null,{"$f":"'Net present value'!B14","v":2016},{"$f":"'Net present value'!D14","v":1},{"$f":"'Net present value'!O14","v":1492.1040576400001},{"$f":"'Net present value'!E35","v":1562.1040576400001},{"$f":"'Net present value'!AK14","v":1492.1040576400001},{"$f":"'Net present value'!AS14","v":1712.0670076400004},null,{"$f":"E8-$D8","v":70},{"$f":"F8-$D8","v":0},{"$f":"G8-$D8","v":219.96295000000032},{"$f":"I8/$D8","v":4.6913618149873632E-2},{"$f":"J8/$D8","v":0},{"$f":"K8/$D8","v":0.14741796919171088}]
[null,{"$f":"'Net present value'!B15","v":2017},{"$f":"'Net present value'!D15","v":2},{"$f":"'Net present value'!O15","v":1417.1708205332973},{"$f":"'Net present value'!E36","v":1542.74430890539},{"$f":"'Net present value'!AK15","v":1423.3380635981214},{"$f":"'Net present value'!AS15","v":1686.1707654785528},null,125.5734883720927,6.1672430648241061,268.99994494525549,8.8608576011209417E-2,4.351799356483577E-3,0.18981476406918102]
[null,{"$f":"'Net present value'!B16","v":2018},{"$f":"'Net present value'!D16","v":3},{"$f":"'Net present value'!O16","v":1413.8072079385997},{"$f":"'Net present value'!E37","v":1583.5008764513091},{"$f":"'Net present value'!AK16","v":1424.3097832371159},{"$f":"'Net present value'!AS16","v":1727.6403169034447},null,169.69366851270934,10.502575298516149,313.833108964845,0.12002603152669665,7.4285767108440608E-3,0.22197730157454004]
[null,{"$f":"'Net present value'!B17","v":2019},{"$f":"'Net present value'!D17","v":4},{"$f":"'Net present value'!O17","v":1407.6138430037245},{"$f":"'Net present value'!E38","v":1612.3348303452797},{"$f":"'Net present value'!AK17","v":1421.2216647957673},{"$f":"'Net present value'!AS17","v":1756.7995625098374},null,204.7209873415552,13.607821792042841,349.18571950611295,0.14543831631031603,9.6672975046941442E-3,0.24806925652349457]
[null,{"$f":"'Net present value'!B18","v":2020},{"$f":"'Net present value'!D18","v":5},{"$f":"'Net present value'!O18","v":1388.2695870468397},{"$f":"'Net present value'!E39","v":1620.7990071832587},{"$f":"'Net present value'!AK18","v":1403.7829712542309},{"$f":"'Net present value'!AS18","v":1764.1675582110674},null,232.52942013641905,15.513384207391255,375.89797116422778,0.16749586845812939,1.1174619362217461E-2,0.27076727364160369]
[null,{"$f":"'Net present value'!B19","v":2021},{"$f":"'Net present value'!D19","v":6},{"$f":"'Net present value'!O19","v":1377.0782319734883},{"$f":"'Net present value'!E40","v":1631.6849609180717},{"$f":"'Net present value'!AK19","v":1394.049332598287},{"$f":"'Net present value'!AS19","v":1774.5932755713864},null,254.60672894458344,16.971100624798737,397.51504359789806,0.18488908112337743,1.2323991644597769E-2,0.28866554881796364]
[null,{"$f":"'Net present value'!B20","v":2022},{"$f":"'Net present value'!D20","v":7},{"$f":"'Net present value'!O20","v":1360.686475087035},{"$f":"'Net present value'!E41","v":1632.8205335221555},{"$f":"'Net present value'!AK20","v":1378.405964597137},{"$f":"'Net present value'!AS20","v":1774.5484537187606},null,272.13405843512055,17.719489510101994,413.86197863172561,0.19999762136072805,1.3022463171737327E-2,0.30415675191101854]
[null,{"$f":"'Net present value'!B21","v":2023},{"$f":"'Net present value'!D21","v":8},{"$f":"'Net present value'!O21","v":1345.9770510625833},{"$f":"'Net present value'!E42","v":1632.0261786639353},{"$f":"'Net present value'!AK21","v":1364.0842044768451},{"$f":"'Net present value'!AS21","v":1772.625395835998},null,286.04912760135198,18.107153414261802,426.64834477341469,0.21252154884478167,1.3452795053204724E-2,0.31698040054739168]
[null,{"$f":"'Net present value'!B22","v":2024},{"$f":"'Net present value'!D22","v":9},{"$f":"'Net present value'!O22","v":1335.5495297789348},{"$f":"'Net present value'!E43","v":1632.6459278732364},{"$f":"'Net present value'!AK22","v":1353.937649167337},{"$f":"'Net present value'!AS22","v":1772.4499115072763},null,297.09639809430155,18.388119388402174,436.90038172834147,0.22245254965832534,1.376820475646893E-2,0.32713154547002005]
[null,{"$f":"'Net present value'!B23","v":2025},{"$f":"'Net present value'!D23","v":10},{"$f":"'Net present value'!O23","v":1326.6736906513556},{"$f":"'Net present value'!E44","v":1632.5405938639901},{"$f":"'Net present value'!AK23","v":1345.1863707669509},{"$f":"'Net present value'!AS23","v":1771.6348251103407},null,305.8669032126345,18.512680115595231,444.96113445898504,0.23055172147301975,1.3954207614161761E-2,0.33539606430313917]
[]
[null,"Key Inputs Varied Across Scenarios**:"]
[null,"Annual cow costs, $/year",null,{"$f":"'Net present value'!G7","v":700},{"$f":"'Net present value'!E33","v":630},{"$f":"D20","v":700},{"$f":"D20","v":700}]
[null,"Discount rate, interest rate",null,{"$f":"'Net present value'!$N9","v":7.4999999999999997E-2},{"$f":"'Net present value'!$N9","v":7.4999999999999997E-2},6.7500000000000004E-2,{"$f":"D21","v":7.4999999999999997E-2}]
[null,"Calf Price, $/cwt",{"$f":"B8","v":2016},{"$f":"'Net present value'!F14","v":203.5686},{"$f":"D22","v":203.5686},{"$f":"E22","v":203.5686},{"$f":"'Net present value'!AM14","v":224.06860000000003}]
[null,null,2017,{"$f":"'Net present value'!F15","v":167.29553725981921},{"$f":"D23","v":167.29553725981921},167.29553725981921,{"$f":"'Net present value'!AM15","v":184.25243098580114}]
[null,null,2018,{"$f":"'Net present value'!F16","v":155.08686671441032},155.08686671441032,155.08686671441032,{"$f":"'Net present value'!AM16","v":170.90709338585137}]
[null,null,2019,{"$f":"'Net present value'!F17","v":148.051159844777},148.051159844777,148.051159844777,{"$f":"'Net present value'!AM17","v":163.20991582925473}]
[null,null,2020,{"$f":"'Net present value'!F18","v":140.85568624673178},140.85568624673178,140.85568624673178,{"$f":"'Net present value'!AM18","v":155.33699487140498}]
[null,null,2021,{"$f":"'Net present value'!F19","v":139.0960196178078},139.0960196178078,139.0960196178078,{"$f":"'Net present value'!AM19","v":153.40136157958858}]
[null,null,2022,{"$f":"'Net present value'!F20","v":136.70867849224456},136.70867849224456,136.70867849224456,{"$f":"'Net present value'!AM20","v":150.73318634146904}]
[null,null,2023,{"$f":"'Net present value'!F21","v":135.2988814307524},135.2988814307524,135.2988814307524,{"$f":"'Net present value'!AM21","v":149.16556957382767}]
[null,null,2024,{"$f":"'Net present value'!F22","v":136.33143790220549},136.33143790220549,136.33143790220549,{"$f":"'Net present value'!AM22","v":150.27612169242605}]
[null,null,2025,{"$f":"'Net present value'!F23","v":137.3972},137.3972,137.3972,{"$f":"'Net present value'!AM23","v":151.42320000000001}]
[null," * NPV is Net Present Value of a replacement expected to produce the number of saleable calves listed in the \"Number of Calves\" column before a cow is culled for age-related reasons."]
[]
[]
[null," ** Annual cow cost, discount rate, and calf price assumptions were varied with all other inputs set at their default settings as of 11-1-13."]