{"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- 1.30.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. 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,2023,229.71,{"$f":"C7*0.4","v":91.884000000000015},{"$f":"C7-25","v":204.71},{"$f":"E7*0.4","v":81.884000000000015},{"$f":"C7*1.1","v":252.68100000000004},{"$f":"G7*0.4","v":101.07240000000002},null,1,542,562,537]
[null,{"$f":"B7+1","v":2024},218.19674247254491,{"$f":"C8*0.4","v":87.278696989017973},{"$f":"E7","v":204.71},{"$f":"E8*0.4","v":81.884000000000015},{"$f":"C8*1.1","v":240.01641671979942},{"$f":"G8*0.4","v":96.006566687919772},null,2,552,{"$f":"L7","v":562},552]
[null,{"$f":"B8+1","v":2025},208.35121487367937,83.340485949471756,{"$f":"E8","v":204.71},81.884000000000015,229.18633636104732,91.674534544418933,null,3,562,{"$f":"L8","v":562},567]
[null,2026,206.24409973970671,82.497639895882685,204.71,81.884000000000015,226.86850971367741,90.747403885470973,null,4,567,562,582]
[null,2027,207.9382923049626,83.175316921985043,204.71,81.884000000000015,228.73212153545887,91.492848614183558,null,5,572,562,582]
[null,2028,208.9176363380862,83.56705453523449,204.71,81.884000000000015,229.80939997189483,91.923759988757936,null,6,572,562,577]
[null,2029,210.52631642358364,84.210526569433455,204.71,81.884000000000015,231.57894806594203,92.631579226376815,null,7,567,562,567]
[null,2030,213.77775422312899,85.511101689251603,204.71,81.884000000000015,235.1555296454419,94.062211858176767,null,8,565,562,557]
[null,2031,217.73430586855505,87.09372234742203,204.71,81.884000000000015,239.50773645541057,95.803094582164235,null,9,562,562,552]
[null,2032,222.58,89.032000000000011,204.71,81.884000000000015,244.83800000000002,97.935200000000009,null,10,559,562,547]
[null,"Average",{"$f":"AVERAGE(C7:C16)","v":214.39763622442473},85.759054489769909,204.71,81.884000000000015,235.83739984686727,94.334959938746906,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,2023,null,"Annual cow death loss",null,null,null,null,5.0000000000000001E-3]
[null,"First year for calf sales",null,null,null,null,2024,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,900,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. 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":650},{"$f":"E33","v":775},{"$f":"F33","v":900},{"$f":"G33","v":1025},{"$f":"H33","v":1150},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":2024},{"$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":220.62674247254492},{"$f":"VLOOKUP(B14,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":87.278696989017973},{"$f":"IF(B14>=G$5,E14*F14/100*N$3*C14/G$3,0)","v":1159.9230358751574},{"$f":"IF(B14>=G$5,G$6*G14/100*(N$5*C14/G$3),0)","v":157.10165458023235},{"$f":"IF(B14>=G$5,G$6*G14/100*(C14*(1-N$4-N$5))/G$3,0)","v":885.00598746864227},{"$f":"G$7*(1+N$7)^(B14-G$4)*C14/G$3","v":927},0,{"$f":"H14+I14-K14-L14","v":390.02469045538987},{"$f":"1/(1+N$9)^(B14-G$4)","v":0.92592592592592582},{"$f":"SUMPRODUCT(M$14:M14,N$14:N14)+J14*N14","v":1180.5839610407704},null,null,{"$f":"D$33*(1+N$7)^(B14-G$4)*C14/G$3","v":669.5},{"$f":"E$33*(1+N$7)^(B14-G$4)*C14/G$3","v":798.25},{"$f":"F$33*(1+N$7)^(B14-G$4)*C14/G$3","v":927},{"$f":"G$33*(1+N$7)^(B14-G$4)*C14/G$3","v":1055.75},{"$f":"H$33*(1+N$7)^(B14-G$4)*C14/G$3","v":1184.5},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":337.65747226197891},{"$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":363.84108135868428},{"$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":390.02469045538987},{"$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":416.20829955209524},{"$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":442.39190864880061},null,{"$f":"1/(1+AJ$11)^(B14-G$4)","v":0.93283582089552231},{"$f":"SUMPRODUCT(M$14:M14,AJ$14:AJ14)+J14*AJ14","v":1189.3942891082388},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":242.44641671979943},{"$f":"G14*AM$9","v":96.006566687919772},{"$f":"IF(B14>=G$5,E14*AM14/100*N$3*C14/G$3,0)","v":1274.6377912626735},{"$f":"IF(B14>=G$5,G$6*AN14/100*(N$5*C14/G$3),0)","v":172.81182003825555},{"$f":"IF(B14>=G$5,G$6*AN14/100*(C14*(1-N$4-N$5))/G$3,0)","v":973.50658621550645},{"$f":"AO14+AP14-K14-L14","v":520.44961130092906},{"$f":"SUMPRODUCT(AR$14:AR14,N$14:N14)+AQ14*N14","v":1383.2927754781808}]
[null,{"$f":"+B14+1","v":2025},{"$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":208.56019487367936},{"$f":"VLOOKUP(B15,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":83.340485949471756},{"$f":"IF(B15>=G$5,E15*F15/100*N$3*C15/G$3,0)","v":957.77828669642213},{"$f":"G$6*G15/100*(N$5*C15/G$3)","v":126.76087912914653},{"$f":"IF(B15>=G$5,G$6*G15/100*(C15*(1-N$4-N$5))/G$3,0)","v":714.08628576085891},{"$f":"G$7*(1+N$7)^(B15-G$4)*C15/G$3","v":806.81444999999997},0,{"$f":"H15+I15-K15-L15","v":277.72471582556875},0.85733882030178321,{"$f":"SUMPRODUCT(M$14:M15,N$14:N15)+J15*N15","v":1211.4520467063176},null,null,582.69932499999993,694.75688750000006,806.81444999999997,918.87201249999998,1030.9295749999999,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,249.40429000831,264.18955656156027,277.72471582556875,290.009767800335,301.04471248585935,null,0.87018266874582295,{"$f":"SUMPRODUCT(M$14:M15,AJ$14:AJ15)+J15*AJ15","v":1226.8857465424207},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":229.39531636104732},91.674534544418933,1053.4601447488164,139.43696704206121,785.49491433694482,386.08266179087775,{"$f":"SUMPRODUCT(AR$14:AR15,N$14:N15)+AQ15*N15","v":1486.3367252510509}]
[null,2026,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":204.18024606470672},82.497639895882685,818.78009296038408,{"$f":"G$6*G16/100*(N$5*C16/G$3)","v":106.02967918798375},597.30052609230847,702.21095655750003,0,222.59881559086784,0.79383224102016958,{"$f":"SUMPRODUCT(M$14:M16,N$14:N16)+J16*N16","v":1250.1006846977382},null,null,507.15235751375002,604.68165703562499,702.21095655750003,799.74025607937494,897.26955560124998,null,{"$f":"X15*(1-$N$4-X$12)","v":80.102500000000006},75.69,71.402500000000003,67.240000000000009,63.202500000000001,null,210.07164782020698,217.23244913156213,222.59881559086784,226.26355704300681,228.31948333286186,null,0.81173756412856624,{"$f":"SUMPRODUCT(M$14:M16,AJ$14:AJ16)+J16*AJ16","v":1271.0433311327608},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":224.80465603867742},90.747403885470959,901.48572507324366,116.63264710678212,657.03057870153918,315.90741562252572,{"$f":"SUMPRODUCT(AR$14:AR16,N$14:N16)+AQ16*N16","v":1585.2509904483059}]
[null,2027,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":204.18398621202513},83.175316921985043,708.50794538297316,90.331057866800194,508.8649593163077,611.16930603982007,0,187.66969720995326,0.73502985279645328,{"$f":"SUMPRODUCT(M$14:M17,N$14:N17)+J17*N17","v":1287.9180355614803},null,null,441.40005436209225,526.28468020095613,611.16930603982007,696.0539318786839,780.93855771754795,null,71.691737500000002,65.850300000000004,60.335112500000001,55.136800000000015,50.245987500000005,null,187.21607874440383,188.39308513004869,187.66969720995326,185.25863868004637,181.36327601310273,null,0.75721787698560261,{"$f":"SUMPRODUCT(M$14:M17,AJ$14:AJ17)+J17*AJ17","v":1313.6205508915905},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":224.9778154425214},91.492848614183558,780.66146485361855,99.364163653480205,559.75145524793845,268.85632246727869,{"$f":"SUMPRODUCT(AR$14:AR17,N$14:N17)+AQ17*N17","v":1672.7303866188965}]
[null,2028,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":203.43799229675},83.56705453523449,610.78846840610856,76.689240353857301,432.0160539933961,531.93120551175741,0,155.54650324820841,0.68058319703375303,{"$f":"SUMPRODUCT(M$14:M18,N$14:N18)+J18*N18","v":1313.7723030867603},null,null,384.17253731404702,458.05187141290219,531.93120551175741,605.81053961061252,679.68987370946775,null,64.164105062499999,57.289761000000006,50.983170062500001,45.212176000000014,39.945560062500007,null,163.58869030483686,160.42491297144466,155.54650324820841,149.27430190022352,141.90023357469863,null,0.7063599598746293,{"$f":"SUMPRODUCT(M$14:M18,AJ$14:AJ18)+J18*AJ18","v":1343.3295710827451},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":224.32975593055863},91.923759988757951,673.51248651174876,84.358164389243044,475.21765939273581,225.93944538923438,{"$f":"SUMPRODUCT(AR$14:AR18,N$14:N18)+AQ18*N18","v":1738.4921008608471}]
[null,2029,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":203.94023272162741},84.210526569433455,525.15128149648569,65.301391072531345,367.86450304192664,462.96632471715805,0,127.48634785185902,0.63016962688310452,{"$f":"SUMPRODUCT(M$14:M19,N$14:N19)+J19*N19","v":1331.9044967739935},null,null,334.36456785128081,398.66544628421951,462.96632471715805,527.26720315009663,591.56808158303534,null,57.426874030937498,49.842092070000007,43.080778702812502,37.073984320000015,31.756720249687508,null,140.92419794865248,134.90300789831406,127.48634785185902,119.07686305869521,110.02124605232217,null,0.65891787301737803,{"$f":"SUMPRODUCT(M$14:M19,AJ$14:AJ19)+J19*AJ19","v":1364.566257587056},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":224.9928643639858},92.631579226376815,579.36234293500445,71.831530179784494,404.65095334611937,188.22754839763093,{"$f":"SUMPRODUCT(AR$14:AR19,N$14:N19)+AQ19*N19","v":1788.6809711753151}]
[null,2030,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":206.73290104848857},85.511101689251603,452.58577466065611,56.031888545811924,315.64630547474042,402.94274071757854,0,105.67492248888948,0.58349039526213387,{"$f":"SUMPRODUCT(M$14:M20,N$14:N20)+J20*N20","v":1345.9243499853499},null,null,291.01420162936233,346.97847117347044,402.94274071757854,458.90701026168676,514.87127980579487,null,51.397052257689062,43.362620100900003,36.403258003876566,30.400667142400014,25.246592598501572,null,122.83025725656648,114.75328027530526,105.67492248888948,96.048800788238793,86.241451332950021,null,0.6146621949788974,{"$f":"SUMPRODUCT(M$14:M20,AJ$14:AJ20)+J20*AJ20","v":1381.143992455404},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":228.11067647080148},94.062211858176767,499.38663219692086,61.635077400393129,347.21093602221458,158.07896887973538,{"$f":"SUMPRODUCT(AR$14:AR20,N$14:N20)+AQ20*N20","v":1828.5140372205033}]
[null,2031,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":209.82922721013017},87.09372234742203,392.59559428300054,48.223232678445463,271.65754408857612,350.70121438354442,0,90.117612577901582,0.54026888450197574,{"$f":"SUMPRODUCT(M$14:M21,N$14:N21)+J21*N21","v":1357.2036227735869},null,null,253.28421038811547,301.99271238582992,350.70121438354442,399.40971638125899,448.11821837897349,null,46.000361770631713,37.725479487783005,30.760753013275696,24.928547056768014,20.07104111580875,null,110.72596508609058,100.66472411295189,90.117612577901582,79.544769236028742,69.289086269501269,null,0.57337891322658319,{"$f":"SUMPRODUCT(M$14:M21,AJ$14:AJ21)+J21*AJ21","v":1394.5623876571224},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":231.60265779698568},95.803094582164235,433.33421318028974,53.045555946290008,298.82329849743371,135.67855474303531,{"$f":"SUMPRODUCT(AR$14:AR21,N$14:N21)+AQ21*N21","v":1860.6676224858354}]
[null,2032,{"$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":213.94270670951275},{"$f":"VLOOKUP(B22,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":89.032000000000011},{"$f":"IF(B22>=G$5,E22*F22/100*N$3*C22/G$3,0)","v":341.49751316534872},{"$f":"G$6*G22/100*(N$5*C22/G$3)","v":41.655495620247798},234.65929199406258,{"$f":"G$7*(1+N$7)^(B22-G$4)*C22/G$3","v":305.23280193871796},0,{"$f":"H22+I22-K22-L22","v":77.920206846878557},{"$f":"1/(1+N$9)^(B22-G$4)","v":0.50024896713145905},{"$f":"SUMPRODUCT(M$14:M22,N$14:N22)+J22*N22","v":1366.8030759039555},null,null,220.44591251129631,262.83935722500706,305.23280193871796,347.62624665242873,390.01969136613957,null,41.170323784715386,32.821167154371217,25.992836296217963,20.441408586549773,15.956477687067958,null,101.42576854052101,89.623480984072273,77.920206846878557,66.738194061545215,56.357465342858006,null,0.53486838920390223,{"$f":"SUMPRODUCT(M$14:M22,AJ$14:AJ22)+J22*AJ22","v":1405.9885733010294},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":236.20070670951276},97.935200000000023,377.02595797629942,45.821045182272591,258.12522119346892,117.61420121985407,{"$f":"SUMPRODUCT(AR$14:AR22,N$14:N22)+AQ22*N22","v":1887.1859503162618}]
[null,2033,{"$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":213.20566795618666},{"$f":"VLOOKUP(B23,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":89.032000000000011},{"$f":"IF(B23>=G$5,E23*F23/100*N$3*C23/G$3,0)","v":290.326747607372},{"$f":"G$6*G23/100*(N$5*C23/G$3)","v":35.198893799109392},198.28710173498291,{"$f":"G$7*(1+N$7)^(B23-G$4)*C23/G$3","v":265.65936916736314},0,{"$f":"H23+I23-K23-L23","v":59.866272239118246},{"$f":"1/(1+N$9)^(B23-G$4)","v":0.46319348808468425},{"$f":"SUMPRODUCT(M$14:M23,N$14:N23)+J23*N23","v":1368.9899692080203},null,null,191.8650999542067,228.76223456078489,265.65936916736314,302.55650377394136,339.45363838051958,null,36.847439787320269,28.55441542430296,21.963946670304178,16.761955040970815,12.685399761219028,null,80.750021006281656,70.202885569543241,59.866272239118246,50.164457819917089,41.352538117689335,null,0.49894439291408776,{"$f":"SUMPRODUCT(M$14:M23,AJ$14:AJ23)+J23*AJ23","v":1409.2809142366682},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":235.46366795618667},97.935200000000023,320.63594534208153,38.718783179020342,218.11581190848122,93.695359353738752,{"$f":"SUMPRODUCT(AR$14:AR23,N$14:N23)+AQ23*N23","v":1902.4879790643681}]
[null,"* BOY = Beginning of year",null,null,{"$f":"AVERAGE(E14:E23)","v":601.67610261612822},{"$f":"AVERAGE(F14:F23)","v":208.86398975656522},{"$f":"AVERAGE(G14:G23)","v":85.47385448976992},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":650},{"$f":"F33-H46","v":775},{"$f":"G7","v":900},{"$f":"F33+H46","v":1025},{"$f":"G33+H46","v":1150}]
[null,"Year","Calf","NPV**","NPV**","NPV**","NPV**","NPV**"]
[null,{"$f":"B14","v":2024},{"$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":1419.0098869666963},{"$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":1299.7969240037332},{"$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":1180.5839610407704},{"$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":1061.3709980778076},{"$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":942.15803511484455}]
[null,{"$f":"+B35+1","v":2025},{"$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":1642.0205695115301},{"$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":1426.7363081089238},{"$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":1211.4520467063176},{"$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":996.1677853037113},{"$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":780.88352390110504}]
[null,2026,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":1835.513012312105},{"$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":1542.8068485049214},{"$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":1250.100684697738},{"$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":957.3945208905543},{"$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":664.68835708337065}]
[null,2027,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":1998.1158312458915},{"$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":1643.0169334036859},{"$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":1287.9180355614803},{"$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":932.81913771927475},{"$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":577.72023987706871}]
[null,2028,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":2124.5321655626185},{"$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":1719.1522343246893},{"$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":1313.7723030867601},{"$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":908.39237184883086},{"$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":503.01244061090119}]
[null,2029,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":2223.7052803905335},{"$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":1777.8048885822636},{"$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":1331.9044967739937},{"$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":886.00410496572385},{"$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":440.10371315745306}]
[null,2030,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":2303.0343611155863},{"$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":1824.479355550468},{"$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":1345.9243499853501},{"$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":867.36934442023176},{"$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":388.81433885511251}]
[null,2031,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":2366.9450099839582},{"$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":1862.0743163787724},{"$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":1357.2036227735871},{"$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":852.33292916840128},{"$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":347.46223556321456}]
[null,2032,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":2418.9590169766834},{"$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":1892.8810464403191},{"$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":1366.8030759039552},{"$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":840.72510536759091},{"$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":314.64713483122614}]
[null,2033,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":2455.3269352382508},{"$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":1912.1584522231351},{"$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":1368.9899692080203},{"$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":825.82148619290456},{"$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":282.65300317778929}]
[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":650},{"$f":"E33","v":775},{"$f":"F33","v":900},{"$f":"G33","v":1025},{"$f":"H33","v":1150}]
[null,4,{"$f":"B51&\" calves\"","v":"4 calves"},{"$f":"VLOOKUP($B51,$C$35:$H$44,2)","v":1998.1158312458915},{"$f":"VLOOKUP($B51,$C$35:$H$44,3)","v":1643.0169334036859},{"$f":"VLOOKUP($B51,$C$35:$H$44,4)","v":1287.9180355614803},{"$f":"VLOOKUP($B51,$C$35:$H$44,5)","v":932.81913771927475},{"$f":"VLOOKUP($B51,$C$35:$H$44,6)","v":577.72023987706871}]
[null,7,{"$f":"B52&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B52,$C$35:$H$44,2)","v":2303.0343611155863},{"$f":"VLOOKUP($B52,$C$35:$H$44,3)","v":1824.479355550468},{"$f":"VLOOKUP($B52,$C$35:$H$44,4)","v":1345.9243499853501},{"$f":"VLOOKUP($B52,$C$35:$H$44,5)","v":867.36934442023176},{"$f":"VLOOKUP($B52,$C$35:$H$44,6)","v":388.81433885511251}]
[null,10,{"$f":"B53&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B53,$C$35:$H$44,2)","v":2455.3269352382508},{"$f":"VLOOKUP($B53,$C$35:$H$44,3)","v":1912.1584522231351},{"$f":"VLOOKUP($B53,$C$35:$H$44,4)","v":1368.9899692080203},{"$f":"VLOOKUP($B53,$C$35:$H$44,5)","v":825.82148619290456},{"$f":"VLOOKUP($B53,$C$35:$H$44,6)","v":282.65300317778929}]
[]
[]
[]
[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":2024},{"$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":1180.58396104077},{"$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":1180.5839610407702},{"$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":1180.5839610407704},{"$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":1180.5839610407702},{"$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":1180.5839610407702}]
[null,{"$f":"+B61+1","v":2025},{"$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":1213.2785606510242},{"$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":1212.3653036786704},{"$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":1211.4520467063176},{"$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":1210.538789733964},{"$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":1209.6255327616104}]
[null,2026,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":1256.6363215796719},{"$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":1253.3346732325467},{"$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":1250.1006846977382},{"$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":1246.934355975246},{"$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":1243.83568706507}]
[null,2027,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":1301.5718736774459},{"$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":1294.6088798495093},{"$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":1287.9180355614803},{"$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":1281.4934646717613},{"$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":1275.3292910387547}]
[null,2028,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":1334.1103748293435},{"$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":1323.6613064745618},{"$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":1313.7723030867603},{"$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":1304.4211816472077},{"$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":1295.5862345563303}]
[null,2029,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":1358.2806715169781},{"$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":1344.6392575330729},{"$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":1331.9044967739935},{"$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":1320.0251861403717},{"$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":1308.9522650640615}]
[null,2030,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":1378.0750297052612},{"$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":1361.3393443387563},{"$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":1345.9243499853499},{"$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":1331.733283553476},{"$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":1318.6753926555598}]
[null,2031,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":1394.9423225359865},{"$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":1375.1724248887058},{"$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":1357.2036227735869},{"$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":1340.8740182535118},{"$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":1326.0349897301637}]
[null,2032,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":1410.146996201822},{"$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":1387.2936590699953},{"$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":1366.8030759039555},{"$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":1348.4232653387116},{"$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":1331.9279002974138}]
[null,2033,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":1413.8158003813442},{"$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":1390.1367482848},{"$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":1368.9899692080203},{"$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":1350.0922098044932},{"$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":1333.1909527084224}]
[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":1301.5718736774459},{"$f":"VLOOKUP($B77,$C$61:$H$70,3)","v":1294.6088798495093},{"$f":"VLOOKUP($B77,$C$61:$H$70,4)","v":1287.9180355614803},{"$f":"VLOOKUP($B77,$C$61:$H$70,5)","v":1281.4934646717613},{"$f":"VLOOKUP($B77,$C$61:$H$70,6)","v":1275.3292910387547}]
[null,7,{"$f":"B78&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B78,$C$61:$H$70,2)","v":1378.0750297052612},{"$f":"VLOOKUP($B78,$C$61:$H$70,3)","v":1361.3393443387563},{"$f":"VLOOKUP($B78,$C$61:$H$70,4)","v":1345.9243499853499},{"$f":"VLOOKUP($B78,$C$61:$H$70,5)","v":1331.733283553476},{"$f":"VLOOKUP($B78,$C$61:$H$70,6)","v":1318.6753926555598}]
[null,10,{"$f":"B79&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B79,$C$61:$H$70,2)","v":1413.8158003813442},{"$f":"VLOOKUP($B79,$C$61:$H$70,3)","v":1390.1367482848},{"$f":"VLOOKUP($B79,$C$61:$H$70,4)","v":1368.9899692080203},{"$f":"VLOOKUP($B79,$C$61:$H$70,5)","v":1350.0922098044932},{"$f":"VLOOKUP($B79,$C$61:$H$70,6)","v":1333.1909527084224}]

{"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":2024},{"$f":"'Net present value'!D14","v":1},{"$f":"'Net present value'!O14","v":1180.5839610407704},{"$f":"'Net present value'!E35","v":1299.7969240037332},{"$f":"'Net present value'!AK14","v":1189.3942891082388},{"$f":"'Net present value'!AS14","v":1383.2927754781808},null,{"$f":"E8-$D8","v":119.21296296296282},{"$f":"F8-$D8","v":8.8103280674683901},{"$f":"G8-$D8","v":202.70881443741041},{"$f":"I8/$D8","v":0.10097796251430347},{"$f":"J8/$D8","v":7.4626865671641399E-3},{"$f":"K8/$D8","v":0.1717021585306884}]
[null,{"$f":"'Net present value'!B15","v":2025},{"$f":"'Net present value'!D15","v":2},{"$f":"'Net present value'!O15","v":1211.4520467063176},{"$f":"'Net present value'!E36","v":1426.7363081089238},{"$f":"'Net present value'!AK15","v":1226.8857465424207},{"$f":"'Net present value'!AS15","v":1486.3367252510509},null,215.28426140260626,15.433699836103187,274.88467854473333,0.17770762118725106,1.2739835537084741E-2,0.22690512537585517]
[null,{"$f":"'Net present value'!B16","v":2026},{"$f":"'Net present value'!D16","v":3},{"$f":"'Net present value'!O16","v":1250.1006846977382},{"$f":"'Net present value'!E37","v":1542.8068485049214},{"$f":"'Net present value'!AK16","v":1271.0433311327608},{"$f":"'Net present value'!AS16","v":1585.2509904483059},null,292.7061638071832,20.942646435022652,335.15030575056767,0.23414607110462995,1.6752767750132361E-2,0.26809864985523441]
[null,{"$f":"'Net present value'!B17","v":2027},{"$f":"'Net present value'!D17","v":4},{"$f":"'Net present value'!O17","v":1287.9180355614803},{"$f":"'Net present value'!E38","v":1643.0169334036859},{"$f":"'Net present value'!AK17","v":1313.6205508915905},{"$f":"'Net present value'!AS17","v":1672.7303866188965},null,355.09889784220559,25.702515330110145,384.81235105741621,0.2757154477516085,1.9956639025483391E-2,0.2987863671694399]
[null,{"$f":"'Net present value'!B18","v":2028},{"$f":"'Net present value'!D18","v":5},{"$f":"'Net present value'!O18","v":1313.7723030867603},{"$f":"'Net present value'!E39","v":1719.1522343246893},{"$f":"'Net present value'!AK18","v":1343.3295710827451},{"$f":"'Net present value'!AS18","v":1738.4921008608471},null,405.37993123792899,29.55726799598483,424.71979777408683,0.30856178828361103,2.2498014249911383E-2,0.32328265467021244]
[null,{"$f":"'Net present value'!B19","v":2029},{"$f":"'Net present value'!D19","v":6},{"$f":"'Net present value'!O19","v":1331.9044967739935},{"$f":"'Net present value'!E40","v":1777.8048885822636},{"$f":"'Net present value'!AK19","v":1364.566257587056},{"$f":"'Net present value'!AS19","v":1788.6809711753151},null,445.90039180827011,32.661760813062529,456.77647440132159,0.33478405763197411,2.4522599700032992E-2,0.34294987028550478]
[null,{"$f":"'Net present value'!B20","v":2030},{"$f":"'Net present value'!D20","v":7},{"$f":"'Net present value'!O20","v":1345.9243499853499},{"$f":"'Net present value'!E41","v":1824.479355550468},{"$f":"'Net present value'!AK20","v":1381.143992455404},{"$f":"'Net present value'!AS20","v":1828.5140372205033},null,478.55500556511811,35.219642470054168,482.58968723515341,0.35555862078751088,2.616762410936211E-2,0.35855632394228271]
[null,{"$f":"'Net present value'!B21","v":2031},{"$f":"'Net present value'!D21","v":8},{"$f":"'Net present value'!O21","v":1357.2036227735869},{"$f":"'Net present value'!E42","v":1862.0743163787724},{"$f":"'Net present value'!AK21","v":1394.5623876571224},{"$f":"'Net present value'!AS21","v":1860.6676224858354},null,504.87069360518558,37.358764883535514,503.46399971224855,0.37199332888121073,2.7526278486634886E-2,0.37095686399905675]
[null,{"$f":"'Net present value'!B22","v":2032},{"$f":"'Net present value'!D22","v":9},{"$f":"'Net present value'!O22","v":1366.8030759039555},{"$f":"'Net present value'!E43","v":1892.8810464403191},{"$f":"'Net present value'!AK22","v":1405.9885733010294},{"$f":"'Net present value'!AS22","v":1887.1859503162618},null,526.07797053636364,39.185497397073959,520.38287441230636,0.38489668322441706,2.8669453623491482E-2,0.38072995560691392]
[null,{"$f":"'Net present value'!B23","v":2033},{"$f":"'Net present value'!D23","v":10},{"$f":"'Net present value'!O23","v":1368.9899692080203},{"$f":"'Net present value'!E44","v":1912.1584522231351},{"$f":"'Net present value'!AK23","v":1409.2809142366682},{"$f":"'Net present value'!AS23","v":1902.4879790643681},null,543.16848301511482,40.290945028647911,533.49800985634784,0.39676586040242889,2.9431147002455235E-2,0.38970191298405482]
[]
[null,"Key Inputs Varied Across Scenarios**:"]
[null,"Annual cow costs, $/year",null,{"$f":"'Net present value'!G7","v":900},{"$f":"'Net present value'!E33","v":775},{"$f":"D20","v":900},{"$f":"D20","v":900}]
[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":2024},{"$f":"'Net present value'!F14","v":220.62674247254492},{"$f":"D22","v":220.62674247254492},{"$f":"E22","v":220.62674247254492},{"$f":"'Net present value'!AM14","v":242.44641671979943}]
[null,null,2025,{"$f":"'Net present value'!F15","v":208.56019487367936},{"$f":"D23","v":208.56019487367936},208.56019487367936,{"$f":"'Net present value'!AM15","v":229.39531636104732}]
[null,null,2026,{"$f":"'Net present value'!F16","v":204.18024606470672},204.18024606470672,204.18024606470672,{"$f":"'Net present value'!AM16","v":224.80465603867742}]
[null,null,2027,{"$f":"'Net present value'!F17","v":204.18398621202513},204.18398621202513,204.18398621202513,{"$f":"'Net present value'!AM17","v":224.9778154425214}]
[null,null,2028,{"$f":"'Net present value'!F18","v":203.43799229675},203.43799229675,203.43799229675,{"$f":"'Net present value'!AM18","v":224.32975593055863}]
[null,null,2029,{"$f":"'Net present value'!F19","v":203.94023272162741},203.94023272162741,203.94023272162741,{"$f":"'Net present value'!AM19","v":224.9928643639858}]
[null,null,2030,{"$f":"'Net present value'!F20","v":206.73290104848857},206.73290104848857,206.73290104848857,{"$f":"'Net present value'!AM20","v":228.11067647080148}]
[null,null,2031,{"$f":"'Net present value'!F21","v":209.82922721013017},209.82922721013017,209.82922721013017,{"$f":"'Net present value'!AM21","v":231.60265779698568}]
[null,null,2032,{"$f":"'Net present value'!F22","v":213.94270670951275},213.94270670951275,213.94270670951275,{"$f":"'Net present value'!AM22","v":236.20070670951276}]
[null,null,2033,{"$f":"'Net present value'!F23","v":213.20566795618666},213.20566795618666,213.20566795618666,{"$f":"'Net present value'!AM23","v":235.46366795618667}]
[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."]