{"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.7.2023"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[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 2023 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. 2023)",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,294.61,{"$f":"C7*0.4","v":117.84400000000001},{"$f":"C7-40","v":254.61},{"$f":"E7*0.4","v":101.84400000000001},{"$f":"C7*1.1","v":324.07100000000003},{"$f":"G7*0.4","v":129.62840000000003},null,1,542,562,537]
[null,{"$f":"B7+1","v":2025},287.60000000000002,{"$f":"C8*0.4","v":115.04000000000002},{"$f":"E7","v":254.61},{"$f":"E8*0.4","v":101.84400000000001},{"$f":"C8*1.1","v":316.36000000000007},{"$f":"G8*0.4","v":126.54400000000004},null,2,552,{"$f":"L7","v":562},552]
[null,{"$f":"B8+1","v":2026},257.39999999999998,102.96,{"$f":"E8","v":254.61},101.84400000000001,283.14,113.256,null,3,562,{"$f":"L8","v":562},567]
[null,2027,234.49,93.796000000000006,254.61,101.84400000000001,257.93900000000002,103.17560000000002,null,4,567,562,582]
[null,2028,211.74,84.696000000000012,254.61,101.84400000000001,232.91400000000002,93.165600000000012,null,5,572,562,582]
[null,2029,208.29,83.316000000000003,254.61,101.84400000000001,229.119,91.647600000000011,null,6,572,562,577]
[null,2030,205.09,82.036000000000001,254.61,101.84400000000001,225.59900000000002,90.23960000000001,null,7,567,562,567]
[null,2031,211.56,84.624000000000009,254.61,101.84400000000001,232.71600000000001,93.086400000000012,null,8,565,562,557]
[null,2032,216.7,86.68,254.61,101.84400000000001,238.37,95.348000000000013,null,9,562,562,552]
[null,2033,226.76,90.704000000000008,254.61,101.84400000000001,249.43600000000001,99.774400000000014,null,10,559,562,547]
[null,"Average",{"$f":"AVERAGE(C7:C16)","v":235.42399999999998},94.169600000000017,254.61000000000007,101.84400000000002,258.96639999999996,103.58656000000001,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,2024,null,"Annual cow death loss",null,null,null,null,5.0000000000000001E-3]
[null,"First year for calf sales",null,null,null,null,2025,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,950,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. 2023))"},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.09,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":8.1000000000000003E-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":825},{"$f":"F33","v":950},{"$f":"G33","v":1075},{"$f":"H33","v":1200},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":2025},{"$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":290.03000000000003},{"$f":"VLOOKUP(B14,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":115.04000000000002},{"$f":"IF(B14>=G$5,E14*F14/100*N$3*C14/G$3,0)","v":1524.8037220000001},{"$f":"IF(B14>=G$5,G$6*G14/100*(N$5*C14/G$3),0)","v":207.07200000000003},{"$f":"IF(B14>=G$5,G$6*G14/100*(C14*(1-N$4-N$5))/G$3,0)","v":1166.5056000000002},{"$f":"G$7*(1+N$7)^(B14-G$4)*C14/G$3","v":978.5},0,{"$f":"H14+I14-K14-L14","v":753.37572200000022},{"$f":"1/(1+N$9)^(B14-G$4)","v":0.9174311926605504},{"$f":"SUMPRODUCT(M$14:M14,N$14:N14)+J14*N14","v":1761.3590110091745},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":849.75},{"$f":"F$33*(1+N$7)^(B14-G$4)*C14/G$3","v":978.5},{"$f":"G$33*(1+N$7)^(B14-G$4)*C14/G$3","v":1107.25},{"$f":"H$33*(1+N$7)^(B14-G$4)*C14/G$3","v":1236},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":684.35172200000011},{"$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":718.86372200000005},{"$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":753.37572200000022},{"$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":787.88772200000017},{"$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":822.39972200000011},null,{"$f":"1/(1+AJ$11)^(B14-G$4)","v":0.92506938020351526},{"$f":"SUMPRODUCT(M$14:M14,AJ$14:AJ14)+J14*AJ14","v":1776.0234246068458},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":318.79000000000008},{"$f":"G14*AM$9","v":126.54400000000004},{"$f":"IF(B14>=G$5,E14*AM14/100*N$3*C14/G$3,0)","v":1676.0065460000008},{"$f":"IF(B14>=G$5,G$6*AN14/100*(N$5*C14/G$3),0)","v":227.77920000000006},{"$f":"IF(B14>=G$5,G$6*AN14/100*(C14*(1-N$4-N$5))/G$3,0)","v":1283.1561600000005},{"$f":"AO14+AP14-K14-L14","v":925.28574600000093},{"$f":"SUMPRODUCT(AR$14:AR14,N$14:N14)+AQ14*N14","v":2026.0934917431205}]
[null,{"$f":"+B14+1","v":2026},{"$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":257.60897999999997},{"$f":"VLOOKUP(B15,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":102.96},{"$f":"IF(B15>=G$5,E15*F15/100*N$3*C15/G$3,0)","v":1183.0267403204793},{"$f":"G$6*G15/100*(N$5*C15/G$3)","v":156.60215999999997},{"$f":"IF(B15>=G$5,G$6*G15/100*(C15*(1-N$4-N$5))/G$3,0)","v":882.19216799999981},{"$f":"G$7*(1+N$7)^(B15-G$4)*C15/G$3","v":851.63747499999999},0,{"$f":"H15+I15-K15-L15","v":487.99142532047927},0.84167999326655996,{"$f":"SUMPRODUCT(M$14:M15,N$14:N15)+J15*N15","v":1844.4265047558952},null,null,627.52234999999996,739.57991249999986,851.63747499999999,963.69503750000001,1075.7526,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,461.57713758796353,475.55648145422128,487.99142532047927,498.88196918673736,508.22811305299558,null,0.85575335819011589,{"$f":"SUMPRODUCT(M$14:M15,AJ$14:AJ15)+J15*AJ15","v":1869.4640235319162},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":283.34897999999998},113.256,1301.2334437352795,172.26237600000002,970.41138480000006,621.85834473527939,{"$f":"SUMPRODUCT(AR$14:AR15,N$14:N15)+AQ15*N15","v":2189.0675807383891}]
[null,2027,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":232.42614632500002},93.796000000000006,932.04854711604935,{"$f":"G$6*G16/100*(N$5*C16/G$3)","v":120.55084002000002},679.10306544600007,741.22267636625008,0,311.37671076979939,0.77218348006106419,{"$f":"SUMPRODUCT(M$14:M16,N$14:N16)+J16*N16","v":1866.7351272622636},null,null,546.16407732250002,643.69337684437505,741.22267636625008,838.751975888125,936.28127541000003,null,{"$f":"X15*(1-$N$4-X$12)","v":80.102500000000006},75.69,71.402500000000003,67.240000000000009,63.202500000000001,null,304.23647753048556,308.77566459597074,311.37671076979939,312.14513655197084,311.18646244248555,null,0.79163122866800739,{"$f":"SUMPRODUCT(M$14:M16,AJ$14:AJ16)+J16*AJ16","v":1898.6198354134235},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":255.87514632500003},103.17560000000002,1026.0810246444753,132.60592402200004,747.01337199060015,417.46427230022516,{"$f":"SUMPRODUCT(AR$14:AR16,N$14:N16)+AQ16*N16","v":2271.4821327359314}]
[null,2028,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":207.98569390706254},84.696000000000012,721.69967583122013,91.982568389400015,518.1684685936201,645.12315637536562,0,168.55908784525457,0.7084252110651964,{"$f":"SUMPRODUCT(M$14:M17,N$14:N17)+J17*N17","v":1828.8380729805313},null,null,475.35390469763786,560.2385305365018,645.12315637536562,730.00778221422956,814.89240805309339,null,71.691737500000002,65.850300000000004,60.335112500000001,55.136800000000015,50.245987500000005,null,163.85423711372925,167.23517694831969,168.55908784525457,168.04608528466747,165.90675644669216,null,0.73231381005366081,{"$f":"SUMPRODUCT(M$14:M17,AJ$14:AJ17)+J17*AJ17","v":1863.9207146467891},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":229.15969390706255},93.165600000000026,795.17236834668995,101.18082522834003,569.98531545298215,251.23003719966437,{"$f":"SUMPRODUCT(AR$14:AR17,N$14:N17)+AQ17*N17","v":2276.4204070330102}]
[null,2029,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":202.81035595866379},83.316000000000003,608.9040955152451,76.458848344690495,430.71817900842308,561.48293915129955,0,123.88000470863608,0.64993138629834524,{"$f":"SUMPRODUCT(M$14:M18,N$14:N18)+J18*N18","v":1822.2052326315388},null,null,413.7242709535891,487.60360505244427,561.48293915129955,635.36227325015466,709.24160734900988,null,64.164105062499999,57.289761000000006,50.983170062500001,45.212176000000014,39.945560062500007,null,123.8319450022351,124.88443401938855,123.88000470863608,121.15821200502461,117.02920789229245,null,0.67744108238081491,{"$f":"SUMPRODUCT(M$14:M18,AJ$14:AJ18)+J18*AJ18","v":1860.1663830250429},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":223.6393559586638},91.647600000000011,671.43967633179864,84.104733179159552,473.78999690926554,194.06147035965864,{"$f":"SUMPRODUCT(AR$14:AR18,N$14:N18)+AQ18*N18","v":2306.6860695926448}]
[null,2030,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":198.50391629804378},82.036000000000001,511.15262856583917,63.615145709950674,358.36532083272215,488.68667609033355,0,86.081098185456312,0.5962673268792158,{"$f":"SUMPRODUCT(M$14:M19,N$14:N19)+J19*N19","v":1807.2768476537049},null,null,360.08491922445631,424.38579765739485,488.68667609033355,552.98755452327214,617.28843295621073,null,57.426874030937498,49.842092070000007,43.080778702812502,37.073984320000015,31.756720249687508,null,86.480069327284014,87.324559651324194,86.081098185456312,83.202932059261627,79.085296314687866,null,0.62668000220241893,{"$f":"SUMPRODUCT(M$14:M19,AJ$14:AJ19)+J19*AJ19","v":1846.9058764856525},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":219.01291629804379},90.23960000000001,563.96382471129334,69.976660280945751,394.20185291599444,145.25380890190559,{"$f":"SUMPRODUCT(AR$14:AR19,N$14:N19)+AQ19*N19","v":2320.4148655291347}]
[null,2031,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":204.51514682535958},84.624000000000009,447.7306015943895,55.45060749576092,312.37175555945316,425.32844853522181,0,77.852760554928636,0.54703424484331731,{"$f":"SUMPRODUCT(M$14:M20,N$14:N20)+J20*N20","v":1807.0614892465449},null,null,313.39990944700554,369.3641789911137,425.32844853522181,481.29271807933003,537.25698762343814,null,51.397052257689062,43.362620100900003,36.403258003876566,30.400667142400014,25.246592598501572,null,83.822254185655538,81.727641197181185,77.852760554928636,72.733373745993163,66.811691059857196,null,0.57972248122332926,{"$f":"SUMPRODUCT(M$14:M20,AJ$14:AJ20)+J20*AJ20","v":1848.547421152934},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":225.67114682535959},93.086400000000012,494.04594182402752,60.995668245337008,343.60893111539843,129.71316153414273,{"$f":"SUMPRODUCT(AR$14:AR20,N$14:N20)+AQ20*N20","v":2344.2885739602812}]
[null,2032,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":208.7949213415751},86.68,390.66038281348915,47.994157281433274,270.36708601874079,370.1846151826303,0,68.469924912292129,0.50186627967276809,{"$f":"SUMPRODUCT(M$14:M21,N$14:N21)+J21*N21","v":1806.2343119250966},null,null,272.76761118720123,321.47611318491579,370.1846151826303,418.89311718034469,467.60161917805925,null,46.000361770631713,37.725479487783005,30.760753013275696,24.928547056768014,20.07104111580875,null,78.4676862773008,74.162477345135244,68.469924912292129,61.970519567583381,55.114393507120894,null,0.53628351639530925,{"$f":"SUMPRODUCT(M$14:M21,AJ$14:AJ21)+J21*AJ21","v":1849.1711956628908},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":230.46492134157512},95.348000000000013,431.20548056382717,52.793573009576612,297.40379462061486,113.81443839077349,{"$f":"SUMPRODUCT(AR$14:AR21,N$14:N21)+AQ21*N21","v":2362.6992865411844}]
[null,2033,{"$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":218.12270670951273},{"$f":"VLOOKUP(B22,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":90.704000000000008},{"$f":"IF(B22>=G$5,E22*F22/100*N$3*C22/G$3,0)","v":348.16967145943499},{"$f":"G$6*G22/100*(N$5*C22/G$3)","v":42.437776021418777},239.06613825399242,{"$f":"G$7*(1+N$7)^(B22-G$4)*C22/G$3","v":322.19017982420223},0,{"$f":"H22+I22-K22-L22","v":68.417267656651518},{"$f":"1/(1+N$9)^(B22-G$4)","v":0.46042777951630098},{"$f":"SUMPRODUCT(M$14:M22,N$14:N22)+J22*N22","v":1812.1200901404511},null,null,237.40329039678062,279.79673511049145,322.19017982420223,364.58362453791307,406.9770692516239,null,41.170323784715386,32.821167154371217,25.992836296217963,20.441408586549773,15.956477687067958,null,85.960943620196815,77.459484027943915,68.417267656651518,59.367382028609086,50.68387812004147,null,0.49609946012517048,{"$f":"SUMPRODUCT(M$14:M22,AJ$14:AJ22)+J22*AJ22","v":1856.7201357249264},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":240.79870670951274},99.774400000000014,384.36533209979439,46.68155362356066,262.97275207939174,108.85670589915281,{"$f":"SUMPRODUCT(AR$14:AR22,N$14:N22)+AQ22*N22","v":2384.6429622701835}]
[null,2034,{"$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":217.38566795618664},{"$f":"VLOOKUP(B23,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":90.704000000000008},{"$f":"IF(B23>=G$5,E23*F23/100*N$3*C23/G$3,0)","v":296.01874358774239},{"$f":"G$6*G23/100*(N$5*C23/G$3)","v":35.859920738098872},202.01088682462358,{"$f":"G$7*(1+N$7)^(B23-G$4)*C23/G$3","v":280.41822300999445},0,{"$f":"H23+I23-K23-L23","v":51.460441315846822},{"$f":"1/(1+N$9)^(B23-G$4)","v":0.42241080689568894},{"$f":"SUMPRODUCT(M$14:M23,N$14:N23)+J23*N23","v":1809.1164271913665},null,null,206.62395379683801,243.52108840341623,280.41822300999445,317.31535761657267,354.21249222315083,null,36.847439787320269,28.55441542430296,21.963946670304178,16.761955040970815,12.685399761219028,null,66.278468890007332,59.131584733216698,51.460441315846822,43.833561185850698,36.624963569230204,null,0.45892642009728996,{"$f":"SUMPRODUCT(M$14:M23,AJ$14:AJ23)+J23*AJ23","v":1854.444242823723},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":240.06166795618665},99.774400000000014,326.89714092048894,39.445912811908762,222.21197550708598,85.924830722403271,{"$f":"SUMPRODUCT(AR$14:AR23,N$14:N23)+AQ23*N23","v":2393.7233189106373}]
[null,"* BOY = Beginning of year",null,null,{"$f":"AVERAGE(E14:E23)","v":601.67610261612822},{"$f":"AVERAGE(F14:F23)","v":223.81835353214046},{"$f":"AVERAGE(G14:G23)","v":91.455600000000004},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":825},{"$f":"G7","v":950},{"$f":"F33+H46","v":1075},{"$f":"G33+H46","v":1200}]
[null,"Year","Calf","NPV**","NPV**","NPV**","NPV**","NPV**"]
[null,{"$f":"B14","v":2025},{"$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":1997.597543119266},{"$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":1879.47827706422},{"$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":1761.3590110091743},{"$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":1643.2397449541286},{"$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":1525.1204788990826}]
[null,{"$f":"+B35+1","v":2026},{"$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":2269.2982537669213},{"$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":2056.8623792614085},{"$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":1844.4265047558954},{"$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":1631.9906302503823},{"$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":1419.5547557448695}]
[null,2027,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":2442.2279040987287},{"$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":2154.4815156804962},{"$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":1866.7351272622636},{"$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":1578.9887388440316},{"$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":1291.2423504257995}]
[null,2028,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":2524.599667769171},{"$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":2176.7188703748511},{"$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":1828.8380729805313},{"$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":1480.9572755862118},{"$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":1133.0764781918924}]
[null,2029,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":2613.9998234795135},{"$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":2218.1025280555264},{"$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":1822.2052326315388},{"$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":1426.3079372075517},{"$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":1030.4106417835651}]
[null,2030,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":2675.752464300067},{"$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":2241.5146559768859},{"$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":1807.2768476537049},{"$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":1373.0390393305238},{"$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":938.80123100734363}]
[null,2031,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":2736.7658497494454},{"$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":2271.9136694979952},{"$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":1807.0614892465451},{"$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":1342.2093089950949},{"$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":877.35712874364526}]
[null,2032,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":2784.8289818000503},{"$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":2295.5316468625738},{"$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":1806.2343119250968},{"$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":1316.9369769876203},{"$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":827.63964205014418}]
[null,2033,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":2829.7529992465666},{"$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":2320.9365446935089},{"$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":1812.1200901404513},{"$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":1303.3036355873937},{"$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":794.48718103433657}]
[null,2034,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":2857.9208331000887},{"$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":2333.5186301457275},{"$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":1809.1164271913663},{"$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":1284.7142242370055},{"$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":760.31202128264431}]
[null,"** Net present value if replacement is sold in this year"]
[null,"Increment to use for annual costs/cow ",null,null,null,null,null,125]
[]
[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":825},{"$f":"F33","v":950},{"$f":"G33","v":1075},{"$f":"H33","v":1200}]
[null,4,{"$f":"B51&\" calves\"","v":"4 calves"},{"$f":"VLOOKUP($B51,$C$35:$H$44,2)","v":2524.599667769171},{"$f":"VLOOKUP($B51,$C$35:$H$44,3)","v":2176.7188703748511},{"$f":"VLOOKUP($B51,$C$35:$H$44,4)","v":1828.8380729805313},{"$f":"VLOOKUP($B51,$C$35:$H$44,5)","v":1480.9572755862118},{"$f":"VLOOKUP($B51,$C$35:$H$44,6)","v":1133.0764781918924}]
[null,7,{"$f":"B52&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B52,$C$35:$H$44,2)","v":2736.7658497494454},{"$f":"VLOOKUP($B52,$C$35:$H$44,3)","v":2271.9136694979952},{"$f":"VLOOKUP($B52,$C$35:$H$44,4)","v":1807.0614892465451},{"$f":"VLOOKUP($B52,$C$35:$H$44,5)","v":1342.2093089950949},{"$f":"VLOOKUP($B52,$C$35:$H$44,6)","v":877.35712874364526}]
[null,10,{"$f":"B53&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B53,$C$35:$H$44,2)","v":2857.9208331000887},{"$f":"VLOOKUP($B53,$C$35:$H$44,3)","v":2333.5186301457275},{"$f":"VLOOKUP($B53,$C$35:$H$44,4)","v":1809.1164271913663},{"$f":"VLOOKUP($B53,$C$35:$H$44,5)","v":1284.7142242370055},{"$f":"VLOOKUP($B53,$C$35:$H$44,6)","v":760.31202128264431}]
[]
[]
[]
[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":2025},{"$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":1761.3590110091745},{"$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":1761.3590110091743},{"$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":1761.3590110091745},{"$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":1761.3590110091745},{"$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":1761.3590110091745}]
[null,{"$f":"+B61+1","v":2026},{"$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":1849.3417410722695},{"$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":1846.8841229140821},{"$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":1844.4265047558952},{"$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":1841.9688865977084},{"$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":1839.5112684395212}]
[null,2027,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":1874.3685459364738},{"$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":1870.5323094272576},{"$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":1866.7351272622636},{"$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":1862.9769994414912},{"$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":1859.2579259649406}]
[null,2028,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":1829.3382881808554},{"$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":1829.1711137624879},{"$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":1828.8380729805313},{"$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":1828.3450543610697},{"$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":1827.6979464301917}]
[null,2029,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":1820.9906263054991},{"$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":1821.7177946009081},{"$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":1822.2052326315388},{"$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":1822.4630124074033},{"$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":1822.5010839710537}]
[null,2030,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":1801.0910212050126},{"$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":1804.4464740508679},{"$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":1807.2768476537049},{"$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":1809.6161064958578},{"$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":1811.4967205450564}]
[null,2031,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":1800.7869608567394},{"$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":1804.1899446821444},{"$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":1807.0614892465449},{"$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":1809.436258877502},{"$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":1811.3473639691242}]
[null,2032,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":1799.5499802139489},{"$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":1803.1754811851167},{"$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":1806.2343119250966},{"$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":1808.7659134937883},{"$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":1810.8076401846745}]
[null,2033,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":1808.8725255525824},{"$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":1810.6074562277101},{"$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":1812.1200901404511},{"$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":1813.3946348837424},{"$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":1814.420800660663}]
[null,2034,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":1803.8334817359516},{"$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":1806.7025186984861},{"$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":1809.1164271913665},{"$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":1811.1023663735998},{"$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":1812.6860183909257}]
[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":1829.3382881808554},{"$f":"VLOOKUP($B77,$C$61:$H$70,3)","v":1829.1711137624879},{"$f":"VLOOKUP($B77,$C$61:$H$70,4)","v":1828.8380729805313},{"$f":"VLOOKUP($B77,$C$61:$H$70,5)","v":1828.3450543610697},{"$f":"VLOOKUP($B77,$C$61:$H$70,6)","v":1827.6979464301917}]
[null,7,{"$f":"B78&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B78,$C$61:$H$70,2)","v":1800.7869608567394},{"$f":"VLOOKUP($B78,$C$61:$H$70,3)","v":1804.1899446821444},{"$f":"VLOOKUP($B78,$C$61:$H$70,4)","v":1807.0614892465449},{"$f":"VLOOKUP($B78,$C$61:$H$70,5)","v":1809.436258877502},{"$f":"VLOOKUP($B78,$C$61:$H$70,6)","v":1811.3473639691242}]
[null,10,{"$f":"B79&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B79,$C$61:$H$70,2)","v":1803.8334817359516},{"$f":"VLOOKUP($B79,$C$61:$H$70,3)","v":1806.7025186984861},{"$f":"VLOOKUP($B79,$C$61:$H$70,4)","v":1809.1164271913665},{"$f":"VLOOKUP($B79,$C$61:$H$70,5)","v":1811.1023663735998},{"$f":"VLOOKUP($B79,$C$61:$H$70,6)","v":1812.6860183909257}]

{"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":2025},{"$f":"'Net present value'!D14","v":1},{"$f":"'Net present value'!O14","v":1761.3590110091745},{"$f":"'Net present value'!E35","v":1879.47827706422},{"$f":"'Net present value'!AK14","v":1776.0234246068458},{"$f":"'Net present value'!AS14","v":2026.0934917431205},null,{"$f":"E8-$D8","v":118.1192660550455},{"$f":"F8-$D8","v":14.664413597671228},{"$f":"G8-$D8","v":264.73448073394593},{"$f":"I8/$D8","v":6.706143683187496E-2},{"$f":"J8/$D8","v":8.3256244218316514E-3},{"$f":"K8/$D8","v":0.15030126117347634}]
[null,{"$f":"'Net present value'!B15","v":2026},{"$f":"'Net present value'!D15","v":2},{"$f":"'Net present value'!O15","v":1844.4265047558952},{"$f":"'Net present value'!E36","v":2056.8623792614085},{"$f":"'Net present value'!AK15","v":1869.4640235319162},{"$f":"'Net present value'!AS15","v":2189.0675807383891},null,212.43587450551331,25.037518776020988,344.64107598249393,0.11517719679138347,1.3574690404557289E-2,0.18685541283094184]
[null,{"$f":"'Net present value'!B16","v":2027},{"$f":"'Net present value'!D16","v":3},{"$f":"'Net present value'!O16","v":1866.7351272622636},{"$f":"'Net present value'!E37","v":2154.4815156804962},{"$f":"'Net present value'!AK16","v":1898.6198354134235},{"$f":"'Net present value'!AS16","v":2271.4821327359314},null,287.74638841823253,31.884708151159884,404.74700547366774,0.15414419765070725,1.7080467220821897E-2,0.21682080096026582]
[null,{"$f":"'Net present value'!B17","v":2028},{"$f":"'Net present value'!D17","v":4},{"$f":"'Net present value'!O17","v":1828.8380729805313},{"$f":"'Net present value'!E38","v":2176.7188703748511},{"$f":"'Net present value'!AK17","v":1863.9207146467891},{"$f":"'Net present value'!AS17","v":2276.4204070330102},null,347.88079739431987,35.082641666257814,447.58233405247893,0.19021957303599027,1.9183022370636793E-2,0.24473590126163325]
[null,{"$f":"'Net present value'!B18","v":2029},{"$f":"'Net present value'!D18","v":5},{"$f":"'Net present value'!O18","v":1822.2052326315388},{"$f":"'Net present value'!E39","v":2218.1025280555264},{"$f":"'Net present value'!AK18","v":1860.1663830250429},{"$f":"'Net present value'!AS18","v":2306.6860695926448},null,395.89729542398754,37.961150393504113,484.48083696110598,0.21726273656466907,2.0832532863865445E-2,0.26587610895038571]
[null,{"$f":"'Net present value'!B19","v":2030},{"$f":"'Net present value'!D19","v":6},{"$f":"'Net present value'!O19","v":1807.2768476537049},{"$f":"'Net present value'!E40","v":2241.5146559768859},{"$f":"'Net present value'!AK19","v":1846.9058764856525},{"$f":"'Net present value'!AS19","v":2320.4148655291347},null,434.23780832318107,39.629028831947608,513.1380178754298,0.24027188135947727,2.1927481051614173E-2,0.28392883942579722]
[null,{"$f":"'Net present value'!B20","v":2031},{"$f":"'Net present value'!D20","v":7},{"$f":"'Net present value'!O20","v":1807.0614892465449},{"$f":"'Net present value'!E41","v":2271.9136694979952},{"$f":"'Net present value'!AK20","v":1848.547421152934},{"$f":"'Net present value'!AS20","v":2344.2885739602812},null,464.85218025145036,41.485931906389169,537.2270847137363,0.25724203798138084,2.2957675847370723E-2,0.29729319556122763]
[null,{"$f":"'Net present value'!B21","v":2032},{"$f":"'Net present value'!D21","v":8},{"$f":"'Net present value'!O21","v":1806.2343119250966},{"$f":"'Net present value'!E42","v":2295.5316468625738},{"$f":"'Net present value'!AK21","v":1849.1711956628908},{"$f":"'Net present value'!AS21","v":2362.6992865411844},null,489.29733493747722,42.936883737794233,556.46497461608783,0.27089361092691283,2.3771491580199148E-2,0.30808017040879027]
[null,{"$f":"'Net present value'!B22","v":2033},{"$f":"'Net present value'!D22","v":9},{"$f":"'Net present value'!O22","v":1812.1200901404511},{"$f":"'Net present value'!E43","v":2320.9365446935089},{"$f":"'Net present value'!AK22","v":1856.7201357249264},{"$f":"'Net present value'!AS22","v":2384.6429622701835},null,508.81645455305784,44.600045584475311,572.52287212973238,0.2807851738532523,2.4612080527741689E-2,0.31594091100516308]
[null,{"$f":"'Net present value'!B23","v":2034},{"$f":"'Net present value'!D23","v":10},{"$f":"'Net present value'!O23","v":1809.1164271913665},{"$f":"'Net present value'!E44","v":2333.5186301457275},{"$f":"'Net present value'!AK23","v":1854.444242823723},{"$f":"'Net present value'!AS23","v":2393.7233189106373},null,524.40220295436097,45.327815632356533,584.60689171927083,0.28986647574058549,2.505522306418248E-2,0.32314498002036646]
[]
[null,"Key Inputs Varied Across Scenarios**:"]
[null,"Annual cow costs, $/year",null,{"$f":"'Net present value'!G7","v":950},{"$f":"'Net present value'!E33","v":825},{"$f":"D20","v":950},{"$f":"D20","v":950}]
[null,"Discount rate, interest rate",null,{"$f":"'Net present value'!$N9","v":0.09},{"$f":"'Net present value'!$N9","v":0.09},6.7500000000000004E-2,{"$f":"D21","v":0.09}]
[null,"Calf Price, $/cwt",{"$f":"B8","v":2025},{"$f":"'Net present value'!F14","v":290.03000000000003},{"$f":"D22","v":290.03000000000003},{"$f":"E22","v":290.03000000000003},{"$f":"'Net present value'!AM14","v":318.79000000000008}]
[null,null,2026,{"$f":"'Net present value'!F15","v":257.60897999999997},{"$f":"D23","v":257.60897999999997},257.60897999999997,{"$f":"'Net present value'!AM15","v":283.34897999999998}]
[null,null,2027,{"$f":"'Net present value'!F16","v":232.42614632500002},232.42614632500002,232.42614632500002,{"$f":"'Net present value'!AM16","v":255.87514632500003}]
[null,null,2028,{"$f":"'Net present value'!F17","v":207.98569390706254},207.98569390706254,207.98569390706254,{"$f":"'Net present value'!AM17","v":229.15969390706255}]
[null,null,2029,{"$f":"'Net present value'!F18","v":202.81035595866379},202.81035595866379,202.81035595866379,{"$f":"'Net present value'!AM18","v":223.6393559586638}]
[null,null,2030,{"$f":"'Net present value'!F19","v":198.50391629804378},198.50391629804378,198.50391629804378,{"$f":"'Net present value'!AM19","v":219.01291629804379}]
[null,null,2031,{"$f":"'Net present value'!F20","v":204.51514682535958},204.51514682535958,204.51514682535958,{"$f":"'Net present value'!AM20","v":225.67114682535959}]
[null,null,2032,{"$f":"'Net present value'!F21","v":208.7949213415751},208.7949213415751,208.7949213415751,{"$f":"'Net present value'!AM21","v":230.46492134157512}]
[null,null,2033,{"$f":"'Net present value'!F22","v":218.12270670951273},218.12270670951273,218.12270670951273,{"$f":"'Net present value'!AM22","v":240.79870670951274}]
[null,null,2034,{"$f":"'Net present value'!F23","v":217.38566795618664},217.38566795618664,217.38566795618664,{"$f":"'Net present value'!AM23","v":240.06166795618665}]
[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."]