{"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":0,"external_links":1,"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":[[48,1,49,11],[30,1,30,4],[32,1,36,11],[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]]}
[]
[]
[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- 12.3.2024"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[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 2024 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. 2024)",null,"Flat at $40 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,2024,315.96791458800902,{"$f":"C7*0.4","v":126.38716583520362},{"$f":"C7-40","v":275.96791458800902},{"$f":"E7*0.4","v":110.38716583520362},{"$f":"C7*1.1","v":347.56470604680993},{"$f":"G7*0.4","v":139.02588241872397},null,1,542,562,537]
[null,{"$f":"B7+1","v":2025},326.36,{"$f":"C8*0.4","v":130.54400000000001},{"$f":"E7","v":275.96791458800902},{"$f":"E8*0.4","v":110.38716583520362},{"$f":"C8*1.1","v":358.99600000000004},{"$f":"G8*0.4","v":143.59840000000003},null,2,552,{"$f":"L7","v":562},552]
[null,{"$f":"B8+1","v":2026},318.37,127.34800000000001,{"$f":"E8","v":275.96791458800902},110.38716583520362,350.20700000000005,140.08280000000002,null,3,562,{"$f":"L8","v":562},567]
[null,2027,292.98,117.19200000000001,275.96791458800902,110.38716583520362,322.27800000000002,128.91120000000001,null,4,567,562,582]
[null,2028,270.66000000000003,108.26400000000001,275.96791458800902,110.38716583520362,297.72600000000006,119.09040000000003,null,5,572,562,582]
[null,2029,248.41,99.364000000000004,275.96791458800902,110.38716583520362,273.25100000000003,109.30040000000002,null,6,572,562,577]
[null,2030,231.78,92.712000000000003,275.96791458800902,110.38716583520362,254.95800000000003,101.98320000000001,null,7,567,562,567]
[null,2031,233.06,93.224000000000004,275.96791458800902,110.38716583520362,256.36600000000004,102.54640000000002,null,8,565,562,557]
[null,2032,239.08,95.632000000000005,275.96791458800902,110.38716583520362,262.98800000000006,105.19520000000003,null,9,562,562,552]
[null,2033,246.84,98.736000000000004,275.96791458800902,110.38716583520362,271.524,108.6096,null,10,559,562,547]
[null,"Average",{"$f":"AVERAGE(C7:C16)","v":272.35079145880093},{"$f":"AVERAGE(D7:D16)","v":108.94031658352037},275.96791458800902,110.3871658352036,299.58587060468102,119.83434824187243,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,562]
[null,"Price slide (change in price for weight deviation from base), $/lb",null,null,null,null,null,0.1215]

{"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,2025,null,"Annual cow death loss",null,null,null,null,5.0000000000000001E-3]
[null,"First year for calf sales",null,null,null,null,2026,null,"Annual cull rate",null,null,null,null,0.15]
[null,"Cull cow weight, lbs/hd",null,null,null,null,1200]
[null,"Annual cash cow costs, $/year",null,null,null,null,1000,null,"Annual inflation rate on costs",null,null,null,null,0.03]
[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. 2024))"},null,null,null,null,1,null,"Annual increase in average weaning weight",null,null,null,null,1.4999999999999999E-2,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,0.08,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":7.2000000000000008E-2}]
[null,null,"Cows at",null,null,"Prices, $/cwt",null,"Calf","Cull Income",null,null,"Cost","Net","Discount",null,null,null,{"$f":"D33","v":700},{"$f":"E33","v":850},{"$f":"F33","v":1000},{"$f":"G33","v":1150},{"$f":"H33","v":1300},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","Cash 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":2026},{"$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":320.8},{"$f":"VLOOKUP(B14,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":127.34800000000001},{"$f":"IF(B14>=G$5,E14*F14/100*N$3*C14/G$3,0)","v":1686.5739199999998},{"$f":"IF(B14>=G$5,G$6*G14/100*(N$5*C14/G$3),0)","v":229.22640000000001},{"$f":"IF(B14>=G$5,G$6*G14/100*(C14*(1-N$4-N$5))/G$3,0)","v":1291.3087200000002},{"$f":"G$7*(1+N$7)^(B14-G$4)*C14/G$3","v":1030},0,{"$f":"H14+I14-K14-L14","v":885.80031999999983},{"$f":"1/(1+N$9)^(B14-G$4)","v":0.92592592592592582},{"$f":"SUMPRODUCT(M$14:M14,N$14:N14)+J14*N14","v":2015.8417037037036},null,null,{"$f":"D$33*(1+N$7)^(B14-G$4)*C14/G$3","v":721},{"$f":"E$33*(1+N$7)^(B14-G$4)*C14/G$3","v":875.5},{"$f":"F$33*(1+N$7)^(B14-G$4)*C14/G$3","v":1030},{"$f":"G$33*(1+N$7)^(B14-G$4)*C14/G$3","v":1184.5},{"$f":"H$33*(1+N$7)^(B14-G$4)*C14/G$3","v":1339},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":809.3915199999999},{"$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":847.59591999999975},{"$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":885.80031999999983},{"$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":924.00471999999991},{"$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":962.20911999999998},null,{"$f":"1/(1+AJ$11)^(B14-G$4)","v":0.93283582089552231},{"$f":"SUMPRODUCT(M$14:M14,AJ$14:AJ14)+J14*AJ14","v":2030.8852985074625},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":352.63700000000006},{"$f":"G14*AM$9","v":140.08280000000002},{"$f":"IF(B14>=G$5,E14*AM14/100*N$3*C14/G$3,0)","v":1853.9537638000004},{"$f":"IF(B14>=G$5,G$6*AN14/100*(N$5*C14/G$3),0)","v":252.14904000000001},{"$f":"IF(B14>=G$5,G$6*AN14/100*(C14*(1-N$4-N$5))/G$3,0)","v":1420.4395920000002},{"$f":"AO14+AP14-K14-L14","v":1076.1028038000004},{"$f":"SUMPRODUCT(AR$14:AR14,N$14:N14)+AQ14*N14","v":2311.6133294444448}]
[null,{"$f":"+B14+1","v":2027},{"$f":"C14*(1-N$4-N$5)","v":84.5},{"$f":"IF(D14>0,D14+1,IF(G$5=B15,1,0))","v":2},560.28,{"$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":293.18898000000002},{"$f":"VLOOKUP(B15,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":117.19200000000001},{"$f":"IF(B15>=G$5,E15*F15/100*N$3*C15/G$3,0)","v":1346.4220203320795},{"$f":"G$6*G15/100*(N$5*C15/G$3)","v":178.24903200000003},{"$f":"IF(B15>=G$5,G$6*G15/100*(C15*(1-N$4-N$5))/G$3,0)","v":1004.1362136000004},{"$f":"G$7*(1+N$7)^(B15-G$4)*C15/G$3","v":896.46049999999991},0,{"$f":"H15+I15-K15-L15","v":628.21055233207949},0.85733882030178321,{"$f":"SUMPRODUCT(M$14:M15,N$14:N15)+J15*N15","v":2219.6597321091217},null,null,627.52234999999996,761.99142500000005,896.46049999999991,1030.9295749999999,1165.3986499999999,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,602.45067036356352,616.20955134782196,628.21055233207949,638.4536733163377,646.93891430059557,null,0.87018266874582295,{"$f":"SUMPRODUCT(M$14:M15,AJ$14:AJ15)+J15*AJ15","v":2246.7461337541067},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":322.48698000000002},128.91120000000001,1480.9682517480394,196.07393520000002,1104.5498349600002,780.58168694803953,{"$f":"SUMPRODUCT(AR$14:AR15,N$14:N15)+AQ15*N15","v":2612.5879201063444}]
[null,2028,71.402500000000003,{"$f":"IF(D15>0,D15+1,IF(G$5=B16,1,0))","v":3},578.98644999999988,{"$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":268.59614632500006},108.26400000000001,1077.093312871654,{"$f":"G$6*G16/100*(N$5*C16/G$3)","v":139.14576468000004},783.854474364,780.23439617500014,0,436.00468137665382,0.79383224102016958,{"$f":"SUMPRODUCT(M$14:M16,N$14:N16)+J16*N16","v":2327.1383026495732},null,null,546.16407732250002,663.19923674874997,780.23439617500014,897.26955560124998,1014.3047150275,null,{"$f":"X15*(1-$N$4-X$12)","v":80.102500000000006},75.69,71.402500000000003,67.240000000000009,63.202500000000001,null,437.09613974599642,437.60191186528107,436.00468137665382,432.42624528011493,426.98840057566406,null,0.81173756412856624,{"$f":"SUMPRODUCT(M$14:M16,AJ$14:AJ16)+J16*AJ16","v":2363.1697032801803},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":295.66214632500009},119.09040000000002,1185.6302669756403,153.06034114800002,862.2399218004,558.45621194864009,{"$f":"SUMPRODUCT(AR$14:AR16,N$14:N16)+AQ16*N16","v":2793.4088632998992}]
[null,2029,60.335112500000001,4,592.89963862499974,{"$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":244.65569390706253},99.364000000000004,848.94269248099238,107.9124861321,607.90700521082999,679.07700671091106,0,277.77817190218127,0.73502985279645328,{"$f":"SUMPRODUCT(M$14:M17,N$14:N17)+J17*N17","v":2355.8943939888891},null,null,475.35390469763786,577.21545570427452,679.07700671091106,780.93855771754795,882.8001087241845,null,71.691737500000002,65.850300000000004,60.335112500000001,55.136800000000015,50.245987500000005,null,287.32172453736757,283.54021917645548,277.77817190218127,270.28138387517299,261.28447780605904,null,0.75721787698560261,{"$f":"SUMPRODUCT(M$14:M17,AJ$14:AJ17)+J17*AJ17","v":2397.5422311198013},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":269.49669390706259},109.30040000000001,935.13968666143978,118.70373474531,668.69770573191306,374.76641469583865,{"$f":"SUMPRODUCT(AR$14:AR17,N$14:N17)+AQ17*N17","v":2875.9122927164226}]
[null,2030,50.983170062500001,5,607.09995095749969,{"$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":226.3003559586638},92.712000000000003,679.4288827532713,85.081529931021009,479.29261861141839,591.03467279084157,0,173.47573989345074,0.68058319703375303,{"$f":"SUMPRODUCT(M$14:M18,N$14:N18)+J18*N18","v":2353.3277737885592},null,null,413.7242709535891,502.37947187221528,591.03467279084157,679.68987370946775,768.34507462809404,null,64.164105062499999,57.289761000000006,50.983170062500001,45.212176000000014,39.945560062500007,null,182.63259909324984,179.00025140445393,173.47573989345074,166.41444338704537,158.13967691899899,null,0.7063599598746293,{"$f":"SUMPRODUCT(M$14:M18,AJ$14:AJ18)+J18*AJ18","v":2398.3136107503078},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":249.47835595866383},101.98320000000001,749.01694229362761,93.58968292412311,527.2218804725602,251.57195242690909,{"$f":"SUMPRODUCT(AR$14:AR18,N$14:N18)+AQ18*N18","v":2914.4335131318603}]
[null,2031,43.080778702812502,6,616.20645022186204,{"$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":226.47391629804378},93.224000000000004,583.17608929958305,72.290925248237869,407.23887889840671,514.40702746350905,0,141.05998708431184,0.63016962688310452,{"$f":"SUMPRODUCT(M$14:M19,N$14:N19)+J19*N19","v":2372.6505628960795},null,null,360.08491922445631,437.2459733439826,514.40702746350905,591.56808158303534,668.72913570256162,null,57.426874030937498,49.842092070000007,43.080778702812502,37.073984320000015,31.756720249687508,null,155.91222718670258,149.25921099676259,141.05998708431184,131.76041284418994,121.74439744946108,null,0.65891787301737803,{"$f":"SUMPRODUCT(M$14:M19,AJ$14:AJ19)+J19*AJ19","v":2421.0444184509138},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":249.77991629804382},102.54640000000001,643.1896315184116,79.520017773061653,447.96276678824739,208.30262182796423,{"$f":"SUMPRODUCT(AR$14:AR19,N$14:N19)+AQ19*N19","v":2969.1736752542715}]
[null,2032,36.403258003876566,7,619.98233065547674,{"$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":232.03514682535959},95.632000000000005,507.97819864107305,62.663694649681034,353.00547985986981,447.71415635286507,0,122.92773693788899,0.58349039526213387,{"$f":"SUMPRODUCT(M$14:M20,N$14:N20)+J20*N20","v":2393.7234513160797},null,null,313.39990944700554,380.55703289993539,447.71415635286507,514.87127980579487,582.02840325872467,null,51.397052257689062,43.362620100900003,36.403258003876566,30.400667142400014,25.246592598501572,null,144.06806452784531,133.9877809465886,122.92773693788899,111.37981752288471,99.739829595814626,null,0.6146621949788974,{"$f":"SUMPRODUCT(M$14:M20,AJ$14:AJ20)+J20*AJ20","v":2445.245598257472},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":255.94314682535963},105.19520000000001,560.31829857537969,68.930064114649142,388.30602784585682,181.53420633716371,{"$f":"SUMPRODUCT(AR$14:AR20,N$14:N20)+AQ20*N20","v":3019.3774491295162}]
[null,2033,30.760753013275696,8,627.06237578950515,{"$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":238.93492134157512},98.736000000000004,447.05305684187812,54.669486771338214,307.97144214520523,389.668015981716,0,112.05452763150032,0.54026888450197574,{"$f":"SUMPRODUCT(M$14:M21,N$14:N21)+J21*N21","v":2414.6751064960667},null,null,272.76761118720123,331.21781358445861,389.668015981716,448.11821837897349,506.56842077623094,null,46.000361770631713,37.725479487783005,30.760753013275696,24.928547056768014,20.07104111580875,null,140.31765577361955,126.25087607682048,112.05452763150032,98.193143796153493,85.004709380763103,null,0.57337891322658319,{"$f":"SUMPRODUCT(M$14:M21,AJ$14:AJ21)+J21*AJ21","v":2469.1005092447344},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":263.61892134157512},108.60960000000001,493.23742199505506,60.136435448472035,338.76858635972582,163.70584146181108,{"$f":"SUMPRODUCT(AR$14:AR21,N$14:N21)+AQ21*N21","v":3064.2759100689782}]
[null,2034,{"$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":633.08883366656175},{"$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":238.20270670951274},{"$f":"VLOOKUP(B22,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":98.736000000000004},{"$f":"IF(B22>=G$5,E22*F22/100*N$3*C22/G$3,0)","v":380.22157063294071},{"$f":"G$6*G22/100*(N$5*C22/G$3)","v":46.195716321780786},260.23586861269843,{"$f":"G$7*(1+N$7)^(B22-G$4)*C22/G$3","v":339.14755770968657},0,{"$f":"H22+I22-K22-L22","v":87.26972924503491},{"$f":"1/(1+N$9)^(B22-G$4)","v":0.50024896713145905},{"$f":"SUMPRODUCT(M$14:M22,N$14:N22)+J22*N22","v":2422.1270353905434},null,null,237.40329039678062,288.27542405323362,339.14755770968657,390.01969136613957,440.89182502259263,null,41.170323784715386,32.821167154371217,25.992836296217963,20.441408586549773,15.956477687067958,null,113.83747338780722,100.47363768887703,87.26972924503491,74.685979731452903,63.0259984338621,null,0.53486838920390223,{"$f":"SUMPRODUCT(M$14:M22,AJ$14:AJ22)+J22*AJ22","v":2478.3859378081984},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":262.88670670951274},108.60960000000001,419.62242119065064,50.815287953958872,286.25945547396827,131.29015143492296,{"$f":"SUMPRODUCT(AR$14:AR22,N$14:N22)+AQ22*N22","v":3090.1285433944176}]
[null,2035,{"$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":639.15499624537745},{"$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":237.46566795618665},{"$f":"VLOOKUP(B23,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":98.736000000000004},{"$f":"IF(B23>=G$5,E23*F23/100*N$3*C23/G$3,0)","v":323.36211183794313},{"$f":"G$6*G23/100*(N$5*C23/G$3)","v":39.035380291904765},219.89930897773013,{"$f":"G$7*(1+N$7)^(B23-G$4)*C23/G$3","v":295.1770768526257},0,{"$f":"H23+I23-K23-L23","v":67.220415277222173},{"$f":"1/(1+N$9)^(B23-G$4)","v":0.46319348808468425},{"$f":"SUMPRODUCT(M$14:M23,N$14:N23)+J23*N23","v":2424.9362974820469},null,null,206.62395379683801,250.90051532473183,295.1770768526257,339.45363838051958,383.7301999084134,null,36.847439787320269,28.55441542430296,21.963946670304178,16.761955040970815,12.685399761219028,null,90.942168064282441,78.932425548416745,67.220415277222173,56.264802747238804,46.338559115187962,null,0.49894439291408776,{"$f":"SUMPRODUCT(M$14:M23,AJ$14:AJ23)+J23*AJ23","v":2482.4507744622933},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":262.14966795618665},108.60960000000001,356.97484599570976,42.938918321095251,241.88923987550319,104.73668746417928,{"$f":"SUMPRODUCT(AR$14:AR23,N$14:N23)+AQ23*N23","v":3107.482418807007}]
[null,"* BOY = Beginning of year",null,null,{"$f":"AVERAGE(E14:E23)","v":601.67610261612822},{"$f":"AVERAGE(F14:F23)","v":252.66535353214044},{"$f":"AVERAGE(G14:G23)","v":102.99440000000001},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":700},{"$f":"F33-H46","v":850},{"$f":"G7","v":1000},{"$f":"F33+H46","v":1150},{"$f":"G33+H46","v":1300}]
[null,"Year","Calf","NPV**","NPV**","NPV**","NPV**","NPV**"]
[null,{"$f":"B14","v":2026},{"$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":2301.9528148148147},{"$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":2158.897259259259},{"$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":2015.8417037037038},{"$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":1872.7861481481482},{"$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":1729.7305925925925}]
[null,{"$f":"+B35+1","v":2027},{"$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":2736.3419594753768},{"$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":2478.000845792249},{"$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":2219.6597321091217},{"$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":1961.3186184259939},{"$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":1702.9775047428661}]
[null,2028,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":3029.633095786814},{"$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":2678.3856992181936},{"$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":2327.1383026495732},{"$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":1975.8909060809528},{"$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":1624.643509512332}]
[null,2029,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":3208.1317488101827},{"$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":2782.0130713995359},{"$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":2355.8943939888886},{"$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":1929.7757165782418},{"$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":1503.6570391675941}]
[null,2030,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":3326.2396087595898},{"$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":2839.783691274075},{"$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":2353.3277737885592},{"$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":1866.8718563030438},{"$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":1380.4159388175276}]
[null,2031,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":3442.8115032359278},{"$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":2907.7310330660043},{"$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":2372.6505628960795},{"$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":1837.5700927261551},{"$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":1302.4896225562297}]
[null,2032,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":3542.2554646723643},{"$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":2967.9894579942224},{"$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":2393.7234513160797},{"$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":1819.4574446379374},{"$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":1245.1914379597947}]
[null,2033,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":3626.3647711485132},{"$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":3020.5199388222904},{"$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":2414.6751064960667},{"$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":1808.8302741698435},{"$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":1202.9854418436198}]
[null,2034,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":3684.7141646778177},{"$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":3053.420600034181},{"$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":2422.1270353905429},{"$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":1790.8334707469057},{"$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":1159.5399061032676}]
[null,2035,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":3728.540656718324},{"$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":3076.7384771001862},{"$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":2424.9362974820469},{"$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":1773.1341178639086},{"$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":1121.3319382457694}]
[null,"** Net present value if replacement is sold in this year"]
[null,"Increment to use for annual costs/cow ",null,null,null,null,null,150]
[]
[null,"Values to include in chart (Calf number)"]
[null,null,null,"Annual cow costs, $/year"]
[null,"Calves",null,{"$f":"D33","v":700},{"$f":"E33","v":850},{"$f":"F33","v":1000},{"$f":"G33","v":1150},{"$f":"H33","v":1300}]
[null,4,{"$f":"B51&\" calves\"","v":"4 calves"},{"$f":"VLOOKUP($B51,$C$35:$H$44,2)","v":3208.1317488101827},{"$f":"VLOOKUP($B51,$C$35:$H$44,3)","v":2782.0130713995359},{"$f":"VLOOKUP($B51,$C$35:$H$44,4)","v":2355.8943939888886},{"$f":"VLOOKUP($B51,$C$35:$H$44,5)","v":1929.7757165782418},{"$f":"VLOOKUP($B51,$C$35:$H$44,6)","v":1503.6570391675941}]
[null,7,{"$f":"B52&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B52,$C$35:$H$44,2)","v":3542.2554646723643},{"$f":"VLOOKUP($B52,$C$35:$H$44,3)","v":2967.9894579942224},{"$f":"VLOOKUP($B52,$C$35:$H$44,4)","v":2393.7234513160797},{"$f":"VLOOKUP($B52,$C$35:$H$44,5)","v":1819.4574446379374},{"$f":"VLOOKUP($B52,$C$35:$H$44,6)","v":1245.1914379597947}]
[null,10,{"$f":"B53&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B53,$C$35:$H$44,2)","v":3728.540656718324},{"$f":"VLOOKUP($B53,$C$35:$H$44,3)","v":3076.7384771001862},{"$f":"VLOOKUP($B53,$C$35:$H$44,4)","v":2424.9362974820469},{"$f":"VLOOKUP($B53,$C$35:$H$44,5)","v":1773.1341178639086},{"$f":"VLOOKUP($B53,$C$35:$H$44,6)","v":1121.3319382457694}]
[]
[]
[]
[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":2026},{"$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":2015.8417037037036},{"$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":2015.8417037037034},{"$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":2015.8417037037036},{"$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":2015.8417037037034},{"$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":2015.8417037037036}]
[null,{"$f":"+B61+1","v":2027},{"$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":2231.7199704763061},{"$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":2225.6898512927141},{"$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":2219.6597321091217},{"$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":2213.6296129255297},{"$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":2207.5994937419373}]
[null,2028,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":2352.2942108280658},{"$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":2339.6221786510951},{"$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":2327.1383026495732},{"$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":2314.8425828235022},{"$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":2302.7350191728801}]
[null,2029,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":2386.4629405511482},{"$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":2371.0068427887704},{"$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":2355.8943939888891},{"$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":2341.1211259679703},{"$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":2326.6825705424767}]
[null,2030,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":2383.2327591405447},{"$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":2368.1227330389365},{"$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":2353.3277737885592},{"$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":2338.8450320318689},{"$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":2324.6716112152276}]
[null,2031,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":2408.9901249407021},{"$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":2390.4781375312737},{"$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":2372.6505628960795},{"$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":2355.4736260814961},{"$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":2338.9152815157177}]
[null,2032,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":2438.7425264215767},{"$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":2415.5796175935202},{"$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":2393.7234513160797},{"$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":2373.0717725583067},{"$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":2353.5298698241809}]
[null,2033,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":2470.0741299605725},{"$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":2441.2750626103102},{"$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":2414.6751064960667},{"$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":2390.0510162668747},{"$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":2367.2005862848696}]
[null,2034,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":2481.8773176508248},{"$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":2450.6846168844504},{"$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":2422.1270353905434},{"$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":2395.9113972408463},{"$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":2371.775175059071}]
[null,2035,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":2486.5902281908711},{"$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":2454.3368219275903},{"$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":2424.9362974820469},{"$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":2398.0553072394873},{"$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":2373.3976800304995}]
[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":2386.4629405511482},{"$f":"VLOOKUP($B77,$C$61:$H$70,3)","v":2371.0068427887704},{"$f":"VLOOKUP($B77,$C$61:$H$70,4)","v":2355.8943939888891},{"$f":"VLOOKUP($B77,$C$61:$H$70,5)","v":2341.1211259679703},{"$f":"VLOOKUP($B77,$C$61:$H$70,6)","v":2326.6825705424767}]
[null,7,{"$f":"B78&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B78,$C$61:$H$70,2)","v":2438.7425264215767},{"$f":"VLOOKUP($B78,$C$61:$H$70,3)","v":2415.5796175935202},{"$f":"VLOOKUP($B78,$C$61:$H$70,4)","v":2393.7234513160797},{"$f":"VLOOKUP($B78,$C$61:$H$70,5)","v":2373.0717725583067},{"$f":"VLOOKUP($B78,$C$61:$H$70,6)","v":2353.5298698241809}]
[null,10,{"$f":"B79&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B79,$C$61:$H$70,2)","v":2486.5902281908711},{"$f":"VLOOKUP($B79,$C$61:$H$70,3)","v":2454.3368219275903},{"$f":"VLOOKUP($B79,$C$61:$H$70,4)","v":2424.9362974820469},{"$f":"VLOOKUP($B79,$C$61:$H$70,5)","v":2398.0553072394873},{"$f":"VLOOKUP($B79,$C$61:$H$70,6)","v":2373.3976800304995}]

{"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":2026},{"$f":"'Net present value'!D14","v":1},{"$f":"'Net present value'!O14","v":2015.8417037037036},{"$f":"'Net present value'!E35","v":2158.897259259259},{"$f":"'Net present value'!AK14","v":2030.8852985074625},{"$f":"'Net present value'!AS14","v":2311.6133294444448},null,{"$f":"E8-$D8","v":143.05555555555543},{"$f":"F8-$D8","v":15.043594803758879},{"$f":"G8-$D8","v":295.77162574074123},{"$f":"I8/$D8","v":7.0965669225276778E-2},{"$f":"J8/$D8","v":7.4626865671641278E-3},{"$f":"K8/$D8","v":0.14672363668105504}]
[null,{"$f":"'Net present value'!B15","v":2027},{"$f":"'Net present value'!D15","v":2},{"$f":"'Net present value'!O15","v":2219.6597321091217},{"$f":"'Net present value'!E36","v":2478.000845792249},{"$f":"'Net present value'!AK15","v":2246.7461337541067},{"$f":"'Net present value'!AS15","v":2612.5879201063444},null,258.34111368312733,27.086401644985017,392.92818799722272,0.11638771021793123,1.2202952215224225E-2,0.17702181208822587]
[null,{"$f":"'Net present value'!B16","v":2028},{"$f":"'Net present value'!D16","v":3},{"$f":"'Net present value'!O16","v":2327.1383026495732},{"$f":"'Net present value'!E37","v":2678.3856992181936},{"$f":"'Net present value'!AK16","v":2363.1697032801803},{"$f":"'Net present value'!AS16","v":2793.4088632998992},null,351.24739656862039,36.031400630607095,466.27056065032593,0.15093533382554281,1.5483136773428288E-2,0.20036220456663517]
[null,{"$f":"'Net present value'!B17","v":2029},{"$f":"'Net present value'!D17","v":4},{"$f":"'Net present value'!O17","v":2355.8943939888891},{"$f":"'Net present value'!E38","v":2782.0130713995359},{"$f":"'Net present value'!AK17","v":2397.5422311198013},{"$f":"'Net present value'!AS17","v":2875.9122927164226},null,426.1186774106468,41.647837130912194,520.01789872753352,0.18087342051405061,1.7678142635415863E-2,0.22073056417739667]
[null,{"$f":"'Net present value'!B18","v":2030},{"$f":"'Net present value'!D18","v":5},{"$f":"'Net present value'!O18","v":2353.3277737885592},{"$f":"'Net present value'!E39","v":2839.783691274075},{"$f":"'Net present value'!AK18","v":2398.3136107503078},{"$f":"'Net present value'!AS18","v":2914.4335131318603},null,486.45591748551578,44.985836961748646,561.10573934330114,0.20670980171299447,1.9115839902457427E-2,0.23843076412598152]
[null,{"$f":"'Net present value'!B19","v":2031},{"$f":"'Net present value'!D19","v":6},{"$f":"'Net present value'!O19","v":2372.6505628960795},{"$f":"'Net present value'!E40","v":2907.7310330660043},{"$f":"'Net present value'!AK19","v":2421.0444184509138},{"$f":"'Net present value'!AS19","v":2969.1736752542715},null,535.08047016992487,48.393855554834317,596.52311235819207,0.22552013285799685,2.0396537236298432E-2,0.25141633651694179]
[null,{"$f":"'Net present value'!B20","v":2032},{"$f":"'Net present value'!D20","v":7},{"$f":"'Net present value'!O20","v":2393.7234513160797},{"$f":"'Net present value'!E41","v":2967.9894579942224},{"$f":"'Net present value'!AK20","v":2445.245598257472},{"$f":"'Net present value'!AS20","v":3019.3774491295162},null,574.26600667814273,51.522146941392293,625.65399781343649,0.23990490896615846,2.1523851016735117E-2,0.26137271516032862]
[null,{"$f":"'Net present value'!B21","v":2033},{"$f":"'Net present value'!D21","v":8},{"$f":"'Net present value'!O21","v":2414.6751064960667},{"$f":"'Net present value'!E42","v":3020.5199388222904},{"$f":"'Net present value'!AK21","v":2469.1005092447344},{"$f":"'Net present value'!AS21","v":3064.2759100689782},null,605.8448323262237,54.425402748667693,649.60080357291145,0.2509011794987876,2.2539430916503029E-2,0.26902203191863217]
[null,{"$f":"'Net present value'!B22","v":2034},{"$f":"'Net present value'!D22","v":9},{"$f":"'Net present value'!O22","v":2422.1270353905434},{"$f":"'Net present value'!E43","v":3053.420600034181},{"$f":"'Net present value'!AK22","v":2478.3859378081984},{"$f":"'Net present value'!AS22","v":3090.1285433944176},null,631.29356464363764,56.258902417655008,668.00150800387428,0.26063602586469953,2.3227065135575695E-2,0.27579127694108158]
[null,{"$f":"'Net present value'!B23","v":2035},{"$f":"'Net present value'!D23","v":10},{"$f":"'Net present value'!O23","v":2424.9362974820469},{"$f":"'Net present value'!E44","v":3076.7384771001862},{"$f":"'Net present value'!AK23","v":2482.4507744622933},{"$f":"'Net present value'!AS23","v":3107.482418807007},null,651.80217961813923,57.5144769802464,682.54612132496004,0.26879146487062094,2.3717933143219905E-2,0.28146971202240967]
[]
[null,"Key Inputs Varied Across Scenarios**:"]
[null,"Annual cow costs, $/year",null,{"$f":"'Net present value'!G7","v":1000},{"$f":"'Net present value'!E33","v":850},{"$f":"D20","v":1000},{"$f":"D20","v":1000}]
[null,"Discount rate, interest rate",null,{"$f":"'Net present value'!$N9","v":0.08},{"$f":"'Net present value'!$N9","v":0.08},6.7500000000000004E-2,{"$f":"D21","v":0.08}]
[null,"Calf Price, $/cwt",{"$f":"B8","v":2026},{"$f":"'Net present value'!F14","v":320.8},{"$f":"D22","v":320.8},{"$f":"E22","v":320.8},{"$f":"'Net present value'!AM14","v":352.63700000000006}]
[null,null,2027,{"$f":"'Net present value'!F15","v":293.18898000000002},{"$f":"D23","v":293.18898000000002},293.18898000000002,{"$f":"'Net present value'!AM15","v":322.48698000000002}]
[null,null,2028,{"$f":"'Net present value'!F16","v":268.59614632500006},268.59614632500006,268.59614632500006,{"$f":"'Net present value'!AM16","v":295.66214632500009}]
[null,null,2029,{"$f":"'Net present value'!F17","v":244.65569390706253},244.65569390706253,244.65569390706253,{"$f":"'Net present value'!AM17","v":269.49669390706259}]
[null,null,2030,{"$f":"'Net present value'!F18","v":226.3003559586638},226.3003559586638,226.3003559586638,{"$f":"'Net present value'!AM18","v":249.47835595866383}]
[null,null,2031,{"$f":"'Net present value'!F19","v":226.47391629804378},226.47391629804378,226.47391629804378,{"$f":"'Net present value'!AM19","v":249.77991629804382}]
[null,null,2032,{"$f":"'Net present value'!F20","v":232.03514682535959},232.03514682535959,232.03514682535959,{"$f":"'Net present value'!AM20","v":255.94314682535963}]
[null,null,2033,{"$f":"'Net present value'!F21","v":238.93492134157512},238.93492134157512,238.93492134157512,{"$f":"'Net present value'!AM21","v":263.61892134157512}]
[null,null,2034,{"$f":"'Net present value'!F22","v":238.20270670951274},238.20270670951274,238.20270670951274,{"$f":"'Net present value'!AM22","v":262.88670670951274}]
[null,null,2035,{"$f":"'Net present value'!F23","v":237.46566795618665},237.46566795618665,237.46566795618665,{"$f":"'Net present value'!AM23","v":262.14966795618665}]
[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 settings in the NET PRESENT VALUE tab."]