{"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- 8.8.2022"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[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 2022 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 Feb. 2022)",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,2022,180.01,{"$f":"C7*0.4","v":72.004000000000005},{"$f":"C7-25","v":155.01},{"$f":"E7*0.4","v":62.003999999999998},{"$f":"C7*1.1","v":198.011},{"$f":"G7*0.4","v":79.204400000000007},null,1,542,562,537]
[null,{"$f":"B7+1","v":2023},188.66213796448181,{"$f":"C8*0.4","v":75.464855185792729},{"$f":"E7","v":155.01},{"$f":"E8*0.4","v":62.003999999999998},{"$f":"C8*1.1","v":207.52835176093001},{"$f":"G8*0.4","v":83.011340704372003},null,2,552,{"$f":"L7","v":562},552]
[null,{"$f":"B8+1","v":2024},189.42017105602514,75.768068422410053,{"$f":"E8","v":155.01},62.003999999999998,208.36218816162767,83.34487526465108,null,3,562,{"$f":"L8","v":562},567]
[null,2025,191.87862319525024,76.751449278100097,155.01,62.003999999999998,211.06648551477528,84.426594205910121,null,4,567,562,582]
[null,2026,192.56869986848722,77.027479947394895,155.01,62.003999999999998,211.82556985533594,84.730227942134377,null,5,572,562,582]
[null,2027,193.06493065761691,77.225972263046771,155.01,62.003999999999998,212.37142372337863,84.948569489351456,null,6,572,562,577]
[null,2028,193.87306636899507,77.549226547598039,155.01,62.003999999999998,213.26037300589459,85.304149202357848,null,7,567,562,567]
[null,2029,195.22846078729503,78.091384314918017,155.01,62.003999999999998,214.75130686602455,85.90052274640982,null,8,565,562,557]
[null,2030,196.94622925053534,78.778491700214147,155.01,62.003999999999998,216.64085217558889,86.656340870235567,null,9,562,562,552]
[null,2031,199.3136079567592,79.725443182703685,155.01,62.003999999999998,219.24496875243514,87.697987500974065,null,10,559,562,547]
[null,"Average",{"$f":"AVERAGE(C7:C16)","v":192.0965927105446},76.838637084217837,155.01,62.004000000000005,211.30625198159905,84.522500792639633,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,2022,null,"Annual cow death loss",null,null,null,null,5.0000000000000001E-3]
[null,"First year for calf sales",null,null,null,null,2023,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,817,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 Feb. 2022))"},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":653.59999999999991},{"$f":"E33","v":735.3},{"$f":"F33","v":817},{"$f":"G33","v":898.7},{"$f":"H33","v":980.40000000000009},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":2023},{"$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":191.09213796448182},{"$f":"VLOOKUP(B14,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":75.464855185792729},{"$f":"IF(B14>=G$5,E14*F14/100*N$3*C14/G$3,0)","v":1004.6478061344668},{"$f":"IF(B14>=G$5,G$6*G14/100*(N$5*C14/G$3),0)","v":135.83673933442691},{"$f":"IF(B14>=G$5,G$6*G14/100*(C14*(1-N$4-N$5))/G$3,0)","v":765.21363158393831},{"$f":"G$7*(1+N$7)^(B14-G$4)*C14/G$3","v":841.51},0,{"$f":"H14+I14-K14-L14","v":298.97454546889367},{"$f":"1/(1+N$9)^(B14-G$4)","v":0.92592592592592582},{"$f":"SUMPRODUCT(M$14:M14,N$14:N14)+J14*N14","v":985.35942319706646},null,null,{"$f":"D$33*(1+N$7)^(B14-G$4)*C14/G$3","v":673.20800000000008},{"$f":"E$33*(1+N$7)^(B14-G$4)*C14/G$3","v":757.35899999999992},{"$f":"F$33*(1+N$7)^(B14-G$4)*C14/G$3","v":841.51},{"$f":"G$33*(1+N$7)^(B14-G$4)*C14/G$3","v":925.66100000000006},{"$f":"H$33*(1+N$7)^(B14-G$4)*C14/G$3","v":1009.8120000000001},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":253.69563235741816},{"$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":276.33508891315591},{"$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":298.97454546889367},{"$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":321.61400202463142},{"$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":344.2534585803694},null,{"$f":"1/(1+AJ$11)^(B14-G$4)","v":0.93283582089552231},{"$f":"SUMPRODUCT(M$14:M14,AJ$14:AJ14)+J14*AJ14","v":992.71285172838793},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":209.95835176093001},{"$f":"G14*AM$9","v":83.011340704372003},{"$f":"IF(B14>=G$5,E14*AM14/100*N$3*C14/G$3,0)","v":1103.8350385479134},{"$f":"IF(B14>=G$5,G$6*AN14/100*(N$5*C14/G$3),0)","v":149.42041326786961},{"$f":"IF(B14>=G$5,G$6*AN14/100*(C14*(1-N$4-N$5))/G$3,0)","v":841.73499474233211},{"$f":"AO14+AP14-K14-L14","v":411.7454518157831},{"$f":"SUMPRODUCT(AR$14:AR14,N$14:N14)+AQ14*N14","v":1160.6300431093657}]
[null,{"$f":"+B14+1","v":2024},{"$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":189.62915105602514},{"$f":"VLOOKUP(B15,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":75.768068422410053},{"$f":"IF(B15>=G$5,E15*F15/100*N$3*C15/G$3,0)","v":870.84059120745428},{"$f":"G$6*G15/100*(N$5*C15/G$3)","v":115.2432320704857},{"$f":"IF(B15>=G$5,G$6*G15/100*(C15*(1-N$4-N$5))/G$3,0)","v":649.20354066373613},{"$f":"G$7*(1+N$7)^(B15-G$4)*C15/G$3","v":732.40822849999995},0,{"$f":"H15+I15-K15-L15","v":253.67559477793998},0.85733882030178321,{"$f":"SUMPRODUCT(M$14:M15,N$14:N15)+J15*N15","v":1050.9016156962286},null,null,585.92658279999989,659.16740564999998,732.40822849999995,805.64905135000004,878.88987420000001,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,227.9985322586524,241.40532403146449,253.67559477793998,264.80934449807944,274.80657319188288,null,0.87018266874582295,{"$f":"SUMPRODUCT(M$14:M15,AJ$14:AJ15)+J15*AJ15","v":1064.5639411829002},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":208.57116816162767},83.344875264651066,957.82867971095186,126.76755527753429,714.12389473010978,352.18800648848628,{"$f":"SUMPRODUCT(AR$14:AR15,N$14:N15)+AQ15*N15","v":1295.4363761828204}]
[null,2025,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":189.81476952025025},76.751449278100097,761.17331440472117,{"$f":"G$6*G16/100*(N$5*C16/G$3)","v":98.644416427431764},555.69687920786555,637.45150167497513,0,222.36622915717783,0.79383224102016958,{"$f":"SUMPRODUCT(M$14:M16,N$14:N16)+J16*N16","v":1111.9657989763818},null,null,509.96120133997994,573.70635150747751,637.45150167497513,701.19665184247265,764.94180200997016,null,{"$f":"X15*(1-$N$4-X$12)","v":80.102500000000006},75.69,71.402500000000003,67.240000000000009,63.202500000000001,null,212.57242741878861,218.29068410314414,222.36622915717783,224.88540796132816,225.9345658960325,null,0.81173756412856624,{"$f":"SUMPRODUCT(M$14:M16,AJ$14:AJ16)+J16*AJ16","v":1131.2213239314337},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":209.00263183977529},84.426594205910106,838.11826866201454,108.50885807017495,611.26656712865213,309.17562505721435,{"$f":"SUMPRODUCT(AR$14:AR16,N$14:N16)+AQ16*N16","v":1413.8669268780172}]
[null,2026,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":188.81439377554975},77.027479947394895,655.17624900188082,83.654310027916182,471.25261315726112,554.8059144828145,0,184.02464454698247,0.73502985279645328,{"$f":"SUMPRODUCT(M$14:M17,N$14:N17)+J17*N17","v":1152.4840462981012},null,null,443.8447315862515,499.32532303453297,554.8059144828145,610.28650593109592,665.76709737937756,null,71.691737500000002,65.850300000000004,60.335112500000001,55.136800000000015,50.245987500000005,null,185.5294278043832,185.62935165721763,184.02464454698247,180.91071786735466,176.47431742051629,null,0.75721787698560261,{"$f":"SUMPRODUCT(M$14:M17,AJ$14:AJ17)+J17*AJ17","v":1176.3289467251498},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":208.07126376239847},84.730227942134391,721.996598834417,92.019741030707806,518.37787447298729,259.21042538231029,{"$f":"SUMPRODUCT(AR$14:AR17,N$14:N17)+AQ17*N17","v":1500.1744316124571}]
[null,2027,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":187.58528661628071},77.225972263046771,563.19337707949524,70.8700477883188,399.23460254086257,482.87532767011749,0,151.18809719769661,0.68058319703375303,{"$f":"SUMPRODUCT(M$14:M18,N$14:N18)+J18*N18","v":1180.7077481271938},null,null,386.30026213609398,434.58779490310576,482.87532767011749,531.16286043712932,579.45039320414116,null,64.164105062499999,57.289761000000006,50.983170062500001,45.212176000000014,39.945560062500007,null,160.54470286437117,156.61721168123393,151.18809719769661,144.54915931790873,136.96561288046303,null,0.7063599598746293,{"$f":"SUMPRODUCT(M$14:M18,AJ$14:AJ18)+J18*AJ18","v":1208.2845995677048},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":206.89177968204243},84.948569489351456,621.15788605247417,77.957052567150669,439.15806279494876,216.23961094950732,{"$f":"SUMPRODUCT(AR$14:AR18,N$14:N18)+AQ18*N18","v":1565.2038629711253}]
[null,2028,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":187.28698266703884},77.549226547598039,482.26873943728378,60.135859214484171,338.76534024159417,420.27054143768686,0,122.13405721408111,0.63016962688310452,{"$f":"SUMPRODUCT(M$14:M19,N$14:N19)+J19*N19","v":1199.4401872887242},null,null,336.21643315014944,378.24348729391807,420.27054143768686,462.29759558145554,504.32464972522428,null,57.426874030937498,49.842092070000007,43.080778702812502,37.073984320000015,31.756720249687508,null,136.08479599867337,129.70674132458515,122.13405721408111,113.72996465136356,104.80659302231061,null,0.65891787301737803,{"$f":"SUMPRODUCT(M$14:M19,AJ$14:AJ19)+J19*AJ19","v":1229.9761123828623},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":206.67428930393837},85.304149202357848,532.19154666988243,66.149445135932595,372.6418742657537,178.07045036812821,{"$f":"SUMPRODUCT(AR$14:AR19,N$14:N19)+AQ19*N19","v":1613.3624447254499}]
[null,2029,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":188.18360761265461},78.091384314918017,411.97711345343157,51.170054599725134,288.25797424511819,365.78246574029077,0,97.364702312865916,0.58349039526213387,{"$f":"SUMPRODUCT(M$14:M20,N$14:N20)+J20*N20","v":1210.9676871946112},null,null,292.62597259223259,329.20421916626168,365.78246574029077,402.36071231431981,438.93895888834891,null,51.397052257689062,43.362620100900003,36.403258003876566,30.400667142400014,25.246592598501572,null,113.38532855929748,105.81957593451608,97.364702312865916,88.432172902773402,79.354165123548455,null,0.6146621949788974,{"$f":"SUMPRODUCT(M$14:M20,AJ$14:AJ20)+J20*AJ20","v":1243.7852557456306},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":207.70645369138413},85.90052274640982,454.71710486897382,56.287060059697644,317.08377166962998,145.22169918838068,{"$f":"SUMPRODUCT(AR$14:AR20,N$14:N20)+AQ20*N20","v":1648.2856557811667}]
[null,2030,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":189.04115059211045},78.778491700214147,353.70059665867535,43.61914306707618,245.72117261119581,318.35876905706203,0,78.960970668689527,0.54026888450197574,{"$f":"SUMPRODUCT(M$14:M21,N$14:N21)+J21*N21","v":1218.1875872323994},null,null,254.68701524564958,286.52289215135579,318.35876905706203,350.19464596276828,382.03052286847446,null,46.000361770631713,37.725479487783005,30.760753013275696,24.928547056768014,20.07104111580875,null,96.337079096780656,87.923128224068478,78.960970668689527,69.881554875743632,61.008132430006555,null,0.57337891322658319,{"$f":"SUMPRODUCT(M$14:M21,AJ$14:AJ21)+J21*AJ21","v":1252.7698710338543},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":208.73577351716401},86.656340870235567,390.54971579353219,47.981057373783806,270.29328987231543,120.17200411025397,{"$f":"SUMPRODUCT(AR$14:AR21,N$14:N21)+AQ21*N21","v":1674.2265693351449}]
[null,2031,{"$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":190.67631466627194},{"$f":"VLOOKUP(B22,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":79.725443182703685},{"$f":"IF(B22>=G$5,E22*F22/100*N$3*C22/G$3,0)","v":304.35946277186218},{"$f":"G$6*G22/100*(N$5*C22/G$3)","v":37.301227079245976},210.13024587975235,{"$f":"G$7*(1+N$7)^(B22-G$4)*C22/G$3","v":277.08355464881396},0,{"$f":"H22+I22-K22-L22","v":64.577135202294187},{"$f":"1/(1+N$9)^(B22-G$4)","v":0.50024896713145905},{"$f":"SUMPRODUCT(M$14:M22,N$14:N22)+J22*N22","v":1222.8541670569132},null,null,221.66684371905112,249.37519918393252,277.08355464881396,304.79191011369534,332.50026557857677,null,41.170323784715386,32.821167154371217,25.992836296217963,20.441408586549773,15.956477687067958,null,82.590462314171248,73.691534037468045,64.577135202294187,55.674155503656266,47.275423715843488,null,0.53486838920390223,{"$f":"SUMPRODUCT(M$14:M22,AJ$14:AJ22)+J22*AJ22","v":1258.8108265470619},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":210.60767546194788},87.697987500974065,336.17410254346424,41.031349787170583,231.1432704677276,100.12189768182088,{"$f":"SUMPRODUCT(AR$14:AR22,N$14:N22)+AQ22*N22","v":1693.9105733409006}]
[null,2032,{"$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":189.93927591294585},{"$f":"VLOOKUP(B23,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":79.725443182703685},{"$f":"IF(B23>=G$5,E23*F23/100*N$3*C23/G$3,0)","v":258.64440072032647},{"$f":"G$6*G23/100*(N$5*C23/G$3)","v":31.519536881962857},177.56005776839072,{"$f":"G$7*(1+N$7)^(B23-G$4)*C23/G$3","v":241.15967178859523},0,{"$f":"H23+I23-K23-L23","v":49.004265813694076},{"$f":"1/(1+N$9)^(B23-G$4)","v":0.46319348808468425},{"$f":"SUMPRODUCT(M$14:M23,N$14:N23)+J23*N23","v":1222.6798479080205},null,null,192.92773743087611,217.04370460973564,241.15967178859523,265.27563896745471,289.39160614631425,null,36.847439787320269,28.55441542430296,21.963946670304178,16.761955040970815,12.685399761219028,null,64.585098300315451,56.878871725941679,49.004265813694076,41.407045709556087,34.370781432364936,null,0.49894439291408776,{"$f":"SUMPRODUCT(M$14:M23,AJ$14:AJ23)+J23*AJ23","v":1259.4617992959929},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":209.87063670862179},87.697987500974065,285.78536376633156,34.671490570159143,195.31606354522981,79.297182547895488,{"$f":"SUMPRODUCT(AR$14:AR23,N$14:N23)+AQ23*N23","v":1705.4804583621649}]
[null,"* BOY = Beginning of year",null,null,{"$f":"AVERAGE(E14:E23)","v":601.67610261612822},{"$f":"AVERAGE(F14:F23)","v":189.20630703836096},{"$f":"AVERAGE(G14:G23)","v":77.610781402488215},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":653.59999999999991},{"$f":"F33-H46","v":735.3},{"$f":"G7","v":817},{"$f":"F33+H46","v":898.7},{"$f":"G33+H46","v":980.40000000000009}]
[null,"Year","Calf","NPV**","NPV**","NPV**","NPV**","NPV**"]
[null,{"$f":"B14","v":2023},{"$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":1141.1946083822515},{"$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":1063.2770157896591},{"$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":985.35942319706646},{"$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":907.44183060447381},{"$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":829.52423801188127}]
[null,{"$f":"+B35+1","v":2024},{"$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":1332.3212022017158},{"$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":1191.6114089489724},{"$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":1050.9016156962289},{"$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":910.19182244348531},{"$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":769.48202919074174}]
[null,2025,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":1494.5912963051323},{"$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":1303.2785476407571},{"$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":1111.9657989763816},{"$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":920.6530503120066},{"$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":729.34030164763089}]
[null,2026,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":1616.6693255574332},{"$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":1384.5766859277674},{"$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":1152.4840462981015},{"$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":920.39140666843605},{"$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":688.29876703876971}]
[null,2027,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":1710.6203942414149},{"$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":1445.6640711843042},{"$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":1180.7077481271936},{"$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":915.75142507008286},{"$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":650.79510201297171}]
[null,2028,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":1782.3211794604949},{"$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":1490.8806833746098},{"$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":1199.4401872887242},{"$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":907.99969120283868},{"$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":616.55919511695265}]
[null,2029,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":1836.5347904693344},{"$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":1523.7512388319733},{"$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":1210.9676871946117},{"$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":898.1841355572501},{"$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":585.40058391988805}]
[null,2030,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":1878.1545579130989},{"$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":1548.1710725727498},{"$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":1218.1875872323999},{"$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":888.2041018920504},{"$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":558.2206165517}]
[null,2031,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":1910.543290142049},{"$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":1566.6987285994815},{"$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":1222.8541670569134},{"$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":879.00960551434582},{"$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":535.16504397177687}]
[null,2032,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":1932.7096889053792},{"$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":1577.6947684066999},{"$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":1222.6798479080203},{"$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":867.66492740934109},{"$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":512.65000691066052}]
[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":81.7}]
[]
[null,"Values to include in chart (Calf number)"]
[null,null,null,"Annual cow costs, $/year"]
[null,"Calves",null,{"$f":"D33","v":653.59999999999991},{"$f":"E33","v":735.3},{"$f":"F33","v":817},{"$f":"G33","v":898.7},{"$f":"H33","v":980.40000000000009}]
[null,4,{"$f":"B51&\" calves\"","v":"4 calves"},{"$f":"VLOOKUP($B51,$C$35:$H$44,2)","v":1616.6693255574332},{"$f":"VLOOKUP($B51,$C$35:$H$44,3)","v":1384.5766859277674},{"$f":"VLOOKUP($B51,$C$35:$H$44,4)","v":1152.4840462981015},{"$f":"VLOOKUP($B51,$C$35:$H$44,5)","v":920.39140666843605},{"$f":"VLOOKUP($B51,$C$35:$H$44,6)","v":688.29876703876971}]
[null,7,{"$f":"B52&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B52,$C$35:$H$44,2)","v":1836.5347904693344},{"$f":"VLOOKUP($B52,$C$35:$H$44,3)","v":1523.7512388319733},{"$f":"VLOOKUP($B52,$C$35:$H$44,4)","v":1210.9676871946117},{"$f":"VLOOKUP($B52,$C$35:$H$44,5)","v":898.1841355572501},{"$f":"VLOOKUP($B52,$C$35:$H$44,6)","v":585.40058391988805}]
[null,10,{"$f":"B53&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B53,$C$35:$H$44,2)","v":1932.7096889053792},{"$f":"VLOOKUP($B53,$C$35:$H$44,3)","v":1577.6947684066999},{"$f":"VLOOKUP($B53,$C$35:$H$44,4)","v":1222.6798479080203},{"$f":"VLOOKUP($B53,$C$35:$H$44,5)","v":867.66492740934109},{"$f":"VLOOKUP($B53,$C$35:$H$44,6)","v":512.65000691066052}]
[]
[]
[]
[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":2023},{"$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":985.35942319706669},{"$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":985.35942319706646},{"$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":985.35942319706646},{"$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":985.35942319706646},{"$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":985.35942319706658}]
[null,{"$f":"+B61+1","v":2024},{"$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":1054.7798519387827},{"$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":1052.8407338175057},{"$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":1050.9016156962286},{"$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":1048.9624975749516},{"$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":1047.0233794536748}]
[null,2025,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":1123.2843684640929},{"$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":1117.5716330503731},{"$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":1111.9657989763818},{"$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":1106.4668662421204},{"$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":1101.0748348475881}]
[null,2026,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":1171.4291954244934},{"$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":1161.7936230501336},{"$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":1152.4840462981012},{"$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":1143.4941693556793},{"$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":1134.8176964101485}]
[null,2027,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":1206.9497126303581},{"$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":1193.5085817383003},{"$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":1180.7077481271938},{"$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":1168.5231146508454},{"$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":1156.9311031523739}]
[null,2028,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":1231.9201392663836},{"$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":1215.180983804787},{"$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":1199.4401872887242},{"$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":1184.643671814883},{"$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":1170.7396007691589}]
[null,2029,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":1248.1955922766685},{"$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":1228.9122437672579},{"$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":1210.9676871946112},{"$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":1194.2703840060169},{"$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":1178.7342174535281}]
[null,2030,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":1258.9924025349453},{"$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":1237.7668446498137},{"$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":1218.1875872323994},{"$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":1200.1213986986511},{"$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":1183.4451200262949}]
[null,2031,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":1266.3838468603346},{"$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":1243.659337285635},{"$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":1222.8541670569132},{"$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":1203.79131235515},{"$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":1186.3098392126196}]
[null,2032,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":1266.0914033191525},{"$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":1243.4327121954993},{"$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":1222.6798479080205},{"$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":1203.6582793542791},{"$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":1186.2091602170397}]
[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":1171.4291954244934},{"$f":"VLOOKUP($B77,$C$61:$H$70,3)","v":1161.7936230501336},{"$f":"VLOOKUP($B77,$C$61:$H$70,4)","v":1152.4840462981012},{"$f":"VLOOKUP($B77,$C$61:$H$70,5)","v":1143.4941693556793},{"$f":"VLOOKUP($B77,$C$61:$H$70,6)","v":1134.8176964101485}]
[null,7,{"$f":"B78&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B78,$C$61:$H$70,2)","v":1248.1955922766685},{"$f":"VLOOKUP($B78,$C$61:$H$70,3)","v":1228.9122437672579},{"$f":"VLOOKUP($B78,$C$61:$H$70,4)","v":1210.9676871946112},{"$f":"VLOOKUP($B78,$C$61:$H$70,5)","v":1194.2703840060169},{"$f":"VLOOKUP($B78,$C$61:$H$70,6)","v":1178.7342174535281}]
[null,10,{"$f":"B79&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B79,$C$61:$H$70,2)","v":1266.0914033191525},{"$f":"VLOOKUP($B79,$C$61:$H$70,3)","v":1243.4327121954993},{"$f":"VLOOKUP($B79,$C$61:$H$70,4)","v":1222.6798479080205},{"$f":"VLOOKUP($B79,$C$61:$H$70,5)","v":1203.6582793542791},{"$f":"VLOOKUP($B79,$C$61:$H$70,6)","v":1186.2091602170397}]

{"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":2023},{"$f":"'Net present value'!D14","v":1},{"$f":"'Net present value'!O14","v":985.35942319706646},{"$f":"'Net present value'!E35","v":1063.2770157896591},{"$f":"'Net present value'!AK14","v":992.71285172838793},{"$f":"'Net present value'!AS14","v":1160.6300431093657},null,{"$f":"E8-$D8","v":77.917592592592655},{"$f":"F8-$D8","v":7.3534285313214696},{"$f":"G8-$D8","v":175.27061991229925},{"$f":"I8/$D8","v":7.9075300604305038E-2},{"$f":"J8/$D8","v":7.4626865671642579E-3},{"$f":"K8/$D8","v":0.17787480972539102}]
[null,{"$f":"'Net present value'!B15","v":2024},{"$f":"'Net present value'!D15","v":2},{"$f":"'Net present value'!O15","v":1050.9016156962286},{"$f":"'Net present value'!E36","v":1191.6114089489724},{"$f":"'Net present value'!AK15","v":1064.5639411829002},{"$f":"'Net present value'!AS15","v":1295.4363761828204},null,140.70979325274379,13.662325486671534,244.53476048659172,0.13389435428693552,1.3000575203816925E-2,0.23269044107862177]
[null,{"$f":"'Net present value'!B16","v":2025},{"$f":"'Net present value'!D16","v":3},{"$f":"'Net present value'!O16","v":1111.9657989763818},{"$f":"'Net present value'!E37","v":1303.2785476407571},{"$f":"'Net present value'!AK16","v":1131.2213239314337},{"$f":"'Net present value'!AS16","v":1413.8669268780172},null,191.31274866437525,19.255524955051897,301.90112790163539,0.17204913032441094,1.7316652160325016E-2,0.27150217046203212]
[null,{"$f":"'Net present value'!B17","v":2026},{"$f":"'Net present value'!D17","v":4},{"$f":"'Net present value'!O17","v":1152.4840462981012},{"$f":"'Net present value'!E38","v":1384.5766859277674},{"$f":"'Net present value'!AK17","v":1176.3289467251498},{"$f":"'Net present value'!AS17","v":1500.1744316124571},null,232.09263962966611,23.844900427048515,347.69038531435581,0.20138468760168249,2.0690004780231724E-2,0.30168780768043907]
[null,{"$f":"'Net present value'!B18","v":2027},{"$f":"'Net present value'!D18","v":5},{"$f":"'Net present value'!O18","v":1180.7077481271938},{"$f":"'Net present value'!E39","v":1445.6640711843042},{"$f":"'Net present value'!AK18","v":1208.2845995677048},{"$f":"'Net present value'!AS18","v":1565.2038629711253},null,264.95632305711047,27.576851440511064,384.49611484393154,0.22440466193042005,2.3356204347987644E-2,0.32564884532502536]
[null,{"$f":"'Net present value'!B19","v":2028},{"$f":"'Net present value'!D19","v":6},{"$f":"'Net present value'!O19","v":1199.4401872887242},{"$f":"'Net present value'!E40","v":1490.8806833746098},{"$f":"'Net present value'!AK19","v":1229.9761123828623},{"$f":"'Net present value'!AS19","v":1613.3624447254499},null,291.44049608588557,30.535925094138065,413.92225743672566,0.24298043301739999,2.5458480896127907E-2,0.34509620556601212]
[null,{"$f":"'Net present value'!B20","v":2029},{"$f":"'Net present value'!D20","v":7},{"$f":"'Net present value'!O20","v":1210.9676871946112},{"$f":"'Net present value'!E41","v":1523.7512388319733},{"$f":"'Net present value'!AK20","v":1243.7852557456306},{"$f":"'Net present value'!AS20","v":1648.2856557811667},null,312.78355163736205,32.817568551019349,437.31796858655548,0.25829223599018747,2.7100284258654482E-2,0.36113099730981951]
[null,{"$f":"'Net present value'!B21","v":2030},{"$f":"'Net present value'!D21","v":8},{"$f":"'Net present value'!O21","v":1218.1875872323994},{"$f":"'Net present value'!E42","v":1548.1710725727498},{"$f":"'Net present value'!AK21","v":1252.7698710338543},{"$f":"'Net present value'!AS21","v":1674.2265693351449},null,329.9834853403504,34.582283801454878,456.03898210274542,0.27088068274446953,2.8388307485567452E-2,0.37435858555973339]
[null,{"$f":"'Net present value'!B22","v":2031},{"$f":"'Net present value'!D22","v":9},{"$f":"'Net present value'!O22","v":1222.8541670569132},{"$f":"'Net present value'!E43","v":1566.6987285994815},{"$f":"'Net present value'!AK22","v":1258.8108265470619},{"$f":"'Net present value'!AS22","v":1693.9105733409006},null,343.84456154256827,35.956659490148695,471.05640628398737,0.28118198457802313,2.9403881884531555E-2,0.3852106154388758]
[null,{"$f":"'Net present value'!B23","v":2032},{"$f":"'Net present value'!D23","v":10},{"$f":"'Net present value'!O23","v":1222.6798479080205},{"$f":"'Net present value'!E44","v":1577.6947684066999},{"$f":"'Net present value'!AK23","v":1259.4617992959929},{"$f":"'Net present value'!AS23","v":1705.4804583621649},null,355.01492049867943,36.781951387972413,482.80061045414436,0.29035803698417251,3.0083060132957584E-2,0.39487083334219175]
[]
[null,"Key Inputs Varied Across Scenarios**:"]
[null,"Annual cow costs, $/year",null,{"$f":"'Net present value'!G7","v":817},{"$f":"'Net present value'!E33","v":735.3},{"$f":"D20","v":817},{"$f":"D20","v":817}]
[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":2023},{"$f":"'Net present value'!F14","v":191.09213796448182},{"$f":"D22","v":191.09213796448182},{"$f":"E22","v":191.09213796448182},{"$f":"'Net present value'!AM14","v":209.95835176093001}]
[null,null,2024,{"$f":"'Net present value'!F15","v":189.62915105602514},{"$f":"D23","v":189.62915105602514},189.62915105602514,{"$f":"'Net present value'!AM15","v":208.57116816162767}]
[null,null,2025,{"$f":"'Net present value'!F16","v":189.81476952025025},189.81476952025025,189.81476952025025,{"$f":"'Net present value'!AM16","v":209.00263183977529}]
[null,null,2026,{"$f":"'Net present value'!F17","v":188.81439377554975},188.81439377554975,188.81439377554975,{"$f":"'Net present value'!AM17","v":208.07126376239847}]
[null,null,2027,{"$f":"'Net present value'!F18","v":187.58528661628071},187.58528661628071,187.58528661628071,{"$f":"'Net present value'!AM18","v":206.89177968204243}]
[null,null,2028,{"$f":"'Net present value'!F19","v":187.28698266703884},187.28698266703884,187.28698266703884,{"$f":"'Net present value'!AM19","v":206.67428930393837}]
[null,null,2029,{"$f":"'Net present value'!F20","v":188.18360761265461},188.18360761265461,188.18360761265461,{"$f":"'Net present value'!AM20","v":207.70645369138413}]
[null,null,2030,{"$f":"'Net present value'!F21","v":189.04115059211045},189.04115059211045,189.04115059211045,{"$f":"'Net present value'!AM21","v":208.73577351716401}]
[null,null,2031,{"$f":"'Net present value'!F22","v":190.67631466627194},190.67631466627194,190.67631466627194,{"$f":"'Net present value'!AM22","v":210.60767546194788}]
[null,null,2032,{"$f":"'Net present value'!F23","v":189.93927591294585},189.93927591294585,189.93927591294585,{"$f":"'Net present value'!AM23","v":209.87063670862179}]
[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."]