{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Beef Repl","Prices and weights","Net present value","Sensitivity Table"],"not_rendered":{"charts":5,"images":3,"macros":1,"external_links":2,"defined_names":8,"comments":2,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Beef Repl","index":0,"n_rows":49,"n_cols":12,"rows_kept":49,"cut":false,"hidden":false,"merged":[[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.17.2017"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[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 2017 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 Dec. '16)",null,"Flat at $25 under Scen 1",null,"Scenario 1 +10%",null,null,null,"Low peak","Constant","High peak"]
[null,"Year","Calf","Cull cow","Calf","Cull cow","Calf","Cull cow",null,"Calf","Lbs/hd","Lbs/hd","Lbs/hd"]
[null,{"$f":"'Net present value'!G5","v":2018},172.03,{"$f":"C7*0.44","v":75.693200000000004},{"$f":"C7-25","v":147.03},{"$f":"E7*0.44","v":64.693200000000004},{"$f":"C7*1.1","v":189.233},{"$f":"G7*0.44","v":83.262520000000009},null,1,542,562,537]
[null,{"$f":"B7+1","v":2019},169.23,{"$f":"C8*0.44","v":74.461199999999991},{"$f":"E7","v":147.03},{"$f":"E8*0.44","v":64.693200000000004},{"$f":"C8*1.1","v":186.15299999999999},{"$f":"G8*0.44","v":81.907319999999999},null,2,552,{"$f":"L7","v":562},552]
[null,{"$f":"B8+1","v":2020},156.15,68.706000000000003,{"$f":"E8","v":147.03},64.693200000000004,171.76500000000001,75.576600000000013,null,3,562,{"$f":"L8","v":562},567]
[null,2021,152.81,67.236400000000003,147.03,64.693200000000004,168.09100000000001,73.960040000000006,null,4,567,562,582]
[null,2022,145.33000000000001,63.945200000000007,147.03,64.693200000000004,159.86300000000003,70.339720000000014,null,5,572,562,582]
[null,2023,145.1,63.844000000000001,147.03,64.693200000000004,159.61000000000001,70.228400000000008,null,6,572,562,577]
[null,2024,141.66,62.330399999999997,147.03,64.693200000000004,155.82600000000002,68.563440000000014,null,7,567,562,567]
[null,2025,140.56,61.846400000000003,147.03,64.693200000000004,154.61600000000001,68.031040000000004,null,8,565,562,557]
[null,2026,138.81,61.0764,147.03,64.693200000000004,152.691,67.184039999999996,null,9,562,562,552]
[null,2027,{"$f":"C15","v":138.81},61.0764,147.03,64.693200000000004,152.691,67.184039999999996,null,10,559,562,547]
[null,"Average",{"$f":"AVERAGE(C7:C16)","v":150.04900000000001},66.021560000000008,147.03,64.693200000000019,165.05390000000003,72.623716000000002,null,"Average",{"$f":"AVERAGE(K7:K16)","v":562},{"$f":"AVERAGE(L7:L16)","v":562},{"$f":"AVERAGE(M7:M16)","v":562}]
[]
[null,"Average weight for calf price (average of steer and heifer), lbs/head",null,null,null,null,null,525]
[null,"Price slide (change in price for weight deviation from base), $/lb",null,null,null,null,null,8.4199999999999997E-2]

{"sheet":"Net present value","index":2,"n_rows":79,"n_cols":45,"rows_kept":79,"cut":false,"hidden":false,"merged":[[25,1,26,14],[11,8,11,9],[11,41,11,42]]}
[]
[null,"Input Assumptions"]
[null,"Number of replacements purchased",null,null,null,null,100,null,"Percent marketable calves (1 - death loss)",null,null,null,null,0.97]
[null,"Year of purchase",null,null,null,null,2017,null,"Annual cow death loss",null,null,null,null,5.0000000000000001E-3]
[null,"First year for calf sales",null,null,null,null,2018,null,"Annual cull rate",null,null,null,null,0.15]
[null,"Cull cow weight, lbs/hd",null,null,null,null,1250]
[null,"Annual cow costs, $/year",null,null,null,null,800,null,"Annual inflation rate on costs",null,null,null,null,0.01]
[null,{"$f":"\"Price scenario to use (1-3) (\"&IF(G8=1,'Prices and weights'!C5,IF(G8=2,'Prices and weights'!E5,'Prices and weights'!G5))&\")\"","v":"Price scenario to use (1-3) (USDA (As of Dec. '16))"},null,null,null,null,1,null,"Annual increase in average weaning weight",null,null,null,null,0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Sensitivity to discount rate",null,null,"Sensitivity to calf/cull prices"]
[null,"Weaning weight scenario to use (1-3)",null,null,null,null,1,null,"Discount rate (interest rate)",null,null,null,null,7.4999999999999997E-2,null,null,null,"Values for sensitivity analysis down below",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0.9,"<= % of base",null,1.1000000000000001,"<= % of base"]
[]
[null,"Net Present Value Analysis",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Annual cow costs, $/year",null,null,null,null,null,"Annual Cull Rate",null,null,null,null,null,"Annual Cull Rate",null,null,null,null,null,{"$f":"N9*AJ9","v":6.7500000000000004E-2}]
[null,null,"Cows at",null,null,"Prices, $/cwt",null,"Calf","Cull Income",null,null,"Cost","Net","Discount",null,null,null,{"$f":"D33","v":640},{"$f":"E33","v":720},{"$f":"F33","v":800},{"$f":"G33","v":880},{"$f":"H33","v":960},null,{"$f":"Y12-H72","v":0.1},{"$f":"Z12-H72","v":0.125},{"$f":"N5","v":0.15},{"$f":"Z12+H72","v":0.17499999999999999},{"$f":"AA12+H72","v":0.19999999999999998},null,{"$f":"X12","v":0.1},{"$f":"Y12","v":0.125},{"$f":"Z12","v":0.15},{"$f":"AA12","v":0.17499999999999999},{"$f":"AB12","v":0.19999999999999998},null,"Discount",null,null,"Prices, $/cwt",null,"Calf","Cull Income",null,"Net"]
[null,"Year","BOY*","Calf","Calf wt","Calf","Cull","Income","Annual","Age","Cost","Adj.","Income","factor","NPV**",null,null,"Cost","Cost","Cost","Cost","Cost",null,"Cows at Beginning of Year (BOY)",null,null,null,null,null,"Net Income",null,null,null,null,null,"factor","NPV**",null,"Calf","Cull","Income","Annual","Age","Income","NPV**"]
[null,{"$f":"G5","v":2018},{"$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":170.5986},{"$f":"VLOOKUP(B14,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":75.693200000000004},{"$f":"IF(B14>=G$5,E14*F14/100*N$3*C14/G$3,0)","v":896.90507963999994},{"$f":"IF(B14>=G$5,G$6*G14/100*(N$5*C14/G$3),0)","v":141.92474999999999},{"$f":"IF(B14>=G$5,G$6*G14/100*(C14*(1-N$4-N$5))/G$3,0)","v":799.50942499999985},{"$f":"G$7*(1+N$7)^(B14-G$4)*C14/G$3","v":808},0,{"$f":"H14+I14-K14-L14","v":230.82982963999984},{"$f":"1/(1+N$9)^(B14-G$4)","v":0.93023255813953487},{"$f":"SUMPRODUCT(M$14:M14,N$14:N14)+J14*N14","v":958.4551205953486},null,null,{"$f":"D$33*(1+N$7)^(B14-G$4)*C14/G$3","v":646.4},{"$f":"E$33*(1+N$7)^(B14-G$4)*C14/G$3","v":727.2},{"$f":"F$33*(1+N$7)^(B14-G$4)*C14/G$3","v":808},{"$f":"G$33*(1+N$7)^(B14-G$4)*C14/G$3","v":888.8},{"$f":"H$33*(1+N$7)^(B14-G$4)*C14/G$3","v":969.6},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":183.52157963999991},{"$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":207.17570463999994},{"$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":230.82982963999984},{"$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":254.48395463999987},{"$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":278.13807963999989},null,{"$f":"1/(1+AJ$11)^(B14-G$4)","v":0.93676814988290402},{"$f":"SUMPRODUCT(M$14:M14,AJ$14:AJ14)+J14*AJ14","v":965.18899732084287},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":187.80160000000001},{"$f":"G14*AM$9","v":83.262520000000009},{"$f":"IF(B14>=G$5,E14*AM14/100*N$3*C14/G$3,0)","v":987.34813183999995},{"$f":"IF(B14>=G$5,G$6*AN14/100*(N$5*C14/G$3),0)","v":156.11722499999999},{"$f":"IF(B14>=G$5,G$6*AN14/100*(C14*(1-N$4-N$5))/G$3,0)","v":879.46036749999996},{"$f":"AO14+AP14-K14-L14","v":335.46535683999991},{"$f":"SUMPRODUCT(AR$14:AR14,N$14:N14)+AQ14*N14","v":1130.1634645023255}]
[null,{"$f":"+B14+1","v":2019},{"$f":"C14*(1-N$4-N$5)","v":84.5},{"$f":"IF(D14>0,D14+1,IF(G$5=B15,1,0))","v":2},552,{"$f":"VLOOKUP(B15,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E15)*'Prices and weights'!H$20","v":166.95659999999998},{"$f":"VLOOKUP(B15,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":74.461199999999991},{"$f":"IF(B15>=G$5,E15*F15/100*N$3*C15/G$3,0)","v":755.38979408879982},{"$f":"G$6*G15/100*(N$5*C15/G$3)","v":117.97446374999998},{"$f":"IF(B15>=G$5,G$6*G15/100*(C15*(1-N$4-N$5))/G$3,0)","v":664.58947912499991},{"$f":"G$7*(1+N$7)^(B15-G$4)*C15/G$3","v":689.58760000000007},0,{"$f":"H15+I15-K15-L15","v":183.77665783879979},0.86533261222282321,{"$f":"SUMPRODUCT(M$14:M15,N$14:N15)+J15*N15","v":948.8443083412003},null,null,551.67007999999998,620.62883999999997,689.58760000000007,758.54635999999994,827.50512000000003,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,152.99928254079975,168.96969831479964,183.77665783879979,197.42016111279975,209.90020813679985,null,0.87753456663503904,{"$f":"SUMPRODUCT(M$14:M15,AJ$14:AJ15)+J15*AJ15","v":960.70464279801865},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":183.87959999999998},81.907319999999999,831.95736605279978,129.77191012500001,731.04842703750012,272.1416761777997,{"$f":"SUMPRODUCT(AR$14:AR15,N$14:N15)+AQ15*N15","v":1180.153909631844}]
[null,2020,71.402500000000003,{"$f":"IF(D15>0,D15+1,IF(G$5=B16,1,0))","v":3},562,{"$f":"VLOOKUP(B16,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E16)*'Prices and weights'!H$20","v":153.03460000000001},68.706000000000003,595.67736868662098,{"$f":"G$6*G16/100*(N$5*C16/G$3)","v":91.983378093750019},518.17302992812506,588.52853721999998,0,99.132209560371052,0.80496056950960304,{"$f":"SUMPRODUCT(M$14:M16,N$14:N16)+J16*N16","v":870.65973545408042},null,null,470.82282977599994,529.67568349800001,588.52853721999998,647.38139094200005,706.23424466400002,null,{"$f":"X15*(1-$N$4-X$12)","v":80.102500000000006},75.69,71.402500000000003,67.240000000000009,63.202500000000001,null,76.813906633400961,88.833677069736041,99.132209560371052,107.79001894905605,114.88762007954108,null,0.82204643244500153,{"$f":"SUMPRODUCT(M$14:M16,AJ$14:AJ16)+J16*AJ16","v":884.95797209498107},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":168.64960000000002},75.576600000000013,656.45775503089612,101.18171590312502,569.99033292093759,169.11093371402114,{"$f":"SUMPRODUCT(AR$14:AR16,N$14:N16)+AQ16*N16","v":1142.5012411178434}]
[null,2021,60.335112500000001,4,567,{"$f":"VLOOKUP(B17,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E17)*'Prices and weights'!H$20","v":149.27360000000002},67.236400000000003,495.34516327095065,76.063420464281251,428.49060194878439,502.27968009040904,0,69.128903644822856,0.7488005297763749,{"$f":"SUMPRODUCT(M$14:M17,N$14:N17)+J17*N17","v":826.16862759417108},null,null,401.82374407232726,452.05171208136812,502.27968009040904,552.50764809944985,602.73561610849083,null,71.691737500000002,65.850300000000004,60.335112500000001,55.136800000000015,50.245987500000005,null,52.013907424276567,61.611871543558891,69.128903644822856,74.75794134622754,78.684043000306247,null,0.77006691563934571,{"$f":"SUMPRODUCT(M$14:M17,AJ$14:AJ17)+J17*AJ17","v":842.19599928779417},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":164.55460000000002},73.960040000000006,546.05318826628422,83.669762510709376,471.33966214366279,127.44327068658453,{"$f":"SUMPRODUCT(AR$14:AR17,N$14:N17)+AQ17*N17","v":1132.0504754391779}]
[null,2022,50.983170062500001,5,572,{"$f":"VLOOKUP(B18,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E18)*'Prices and weights'!H$20","v":141.37260000000001},63.945200000000007,399.90777161983954,61.127418867760795,344.35112628838584,428.67059297315956,0,32.364597514440788,0.69655863235011617,{"$f":"SUMPRODUCT(M$14:M18,N$14:N18)+J18*N18","v":767.7192272076046},null,null,342.93647437852763,385.8035336758436,428.67059297315956,471.53765227047552,514.40471156779142,null,64.164105062499999,57.289761000000006,50.983170062500001,45.212176000000014,39.945560062500007,null,15.08831362981698,24.919939632219666,32.364597514440788,37.73580863409228,41.322361188571278,null,0.72137415984950426,{"$f":"SUMPRODUCT(M$14:M18,AJ$14:AJ18)+J18*AJ18","v":783.98255182500907},null,{"$f":"VLOOKUP(B18,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))*AM$9+('Prices and weights'!H$19-E18)*'Prices and weights'!H$20","v":155.90560000000002},70.339720000000014,441.0179983890377,67.240160754536873,378.78623891722441,79.587566170415016,{"$f":"SUMPRODUCT(AR$14:AR18,N$14:N18)+AQ18*N18","v":1098.3953174983506}]
[null,2023,43.080778702812502,6,572,{"$f":"VLOOKUP(B19,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E19)*'Prices and weights'!H$20","v":141.14259999999999},63.844000000000001,337.37229941588862,51.570923165669271,290.51620049993693,365.84891757294309,0,23.094305008614811,0.64796151846522443,{"$f":"SUMPRODUCT(M$14:M19,N$14:N19)+J19*N19","v":731.06601698791223},null,null,292.67913405835446,329.26402581564878,365.84891757294309,402.43380933023735,439.01870108753172,null,57.426874030937498,49.842092070000007,43.080778702812502,37.073984320000015,31.756720249687508,null,7.8700552467410034,16.774720610884231,23.094305008614811,27.270967160179111,29.695544402174789,null,0.67576033709555439,{"$f":"SUMPRODUCT(M$14:M19,AJ$14:AJ19)+J19*AJ19","v":747.50208832466024},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":155.65260000000001},70.228400000000008,372.05546427557346,56.728015482236209,319.56782054993067,62.934562184866593,{"$f":"SUMPRODUCT(AR$14:AR19,N$14:N19)+AQ19*N19","v":1082.3953176985301}]
[null,2024,36.403258003876566,7,567,{"$f":"VLOOKUP(B20,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E20)*'Prices and weights'!H$20","v":138.12360000000001},62.330399999999997,276.54316944828622,42.544305612840525,239.66625495233492,312.23375870262822,0,6.8537163584985024,0.60275490089788319,{"$f":"SUMPRODUCT(M$14:M20,N$14:N20)+J20*N20","v":691.41381945003582},null,null,249.78700696210257,281.01038283236539,312.23375870262822,343.45713457289099,374.68051044315382,null,51.397052257689062,43.362620100900003,36.403258003876566,30.400667142400014,25.246592598501572,null,-10.34586377972505,-0.2823097561865211,6.8537163584985024,11.645114162073355,14.588416830621441,null,0.63303076074525011,{"$f":"SUMPRODUCT(M$14:M20,AJ$14:AJ20)+J20*AJ20","v":707.23748772089732},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":152.28960000000004},68.56344,304.90552416829377,46.798736174124578,263.63288044756843,39.470501639790143,{"$f":"SUMPRODUCT(AR$14:AR20,N$14:N20)+AQ20*N20","v":1058.0247164742536}]
[null,2025,30.760753013275696,8,565,{"$f":"VLOOKUP(B21,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E21)*'Prices and weights'!H$20","v":137.19200000000001},61.846400000000003,231.28418230751012,35.670784409254765,200.94541883880186,266.4759013647581,0,0.47906535200678491,0.56070223339337966,{"$f":"SUMPRODUCT(M$14:M21,N$14:N21)+J21*N21","v":659.8929678435702},null,null,213.18072109180648,239.8283112282823,266.4759013647581,293.1234915012339,319.7710816377097,null,46.000361770631713,37.725479487783005,30.760753013275696,24.928547056768014,20.07104111580875,null,-17.064574115768437,-6.703670010656424,0.47906535200678491,5.2061753619828721,8.0708449207093054,null,0.59300305456229518,{"$f":"SUMPRODUCT(M$14:M21,AJ$14:AJ21)+J21*AJ21","v":674.96671041223999},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":151.24800000000002},68.031040000000004,254.98039248386416,39.237862850180242,221.039960722682,27.742353969286285,{"$f":"SUMPRODUCT(AR$14:AR21,N$14:N21)+AQ21*N21","v":1038.6115052232171}]
[null,2026,{"$f":"C21*(1-N$4-N$5)","v":25.992836296217963},{"$f":"IF(D21>0,D21+1,IF(G$5=B22,1,0))","v":9},{"$f":"VLOOKUP(D22,weights,IF(G$9=1,2,IF(G$9=2,3,4)))*(1+N$8)^(B22-G$5)","v":562},{"$f":"VLOOKUP(B22,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E22)*'Prices and weights'!H$20","v":135.69460000000001},{"$f":"VLOOKUP(B22,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":61.0764},{"$f":"IF(B22>=G$5,E22*F22/100*N$3*C22/G$3,0)","v":192.27564928773955},{"$f":"G$6*G22/100*(N$5*C22/G$3)","v":29.76654125179363},167.68484905177075,{"$f":"G$7*(1+N$7)^(B22-G$4)*C22/G$3","v":227.4238580197528},0,{"$f":"H22+I22-K22-L22","v":-5.3816674802196189},{"$f":"1/(1+N$9)^(B22-G$4)","v":0.52158347292407414},{"$f":"SUMPRODUCT(M$14:M22,N$14:N22)+J22*N22","v":631.87707982120219},null,null,181.93908641580222,204.68147221777753,227.4238580197528,250.1662438217281,272.9086296237034,null,41.170323784715386,32.821167154371217,25.992836296217963,20.441408586549773,15.956477687067958,null,-24.23992387406895,-13.05980521226769,-5.3816674802196189,-0.3307523562553456,2.7873292680392012,null,0.55550637429723215,{"$f":"SUMPRODUCT(M$14:M22,AJ$14:AJ22)+J22*AJ22","v":645.9659151722484},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":149.57560000000001},67.18404000000001,211.9446581338035,32.743195376972999,184.45333395694786,17.263995491023707,{"$f":"SUMPRODUCT(AR$14:AR22,N$14:N22)+AQ22*N22","v":1019.8863308192671}]
[null,2027,{"$f":"C22*(1-N$4-N$5)","v":21.963946670304178},{"$f":"IF(D22>0,D22+1,IF(G$5=B23,1,0))","v":10},{"$f":"VLOOKUP(D23,weights,IF(G$9=1,2,IF(G$9=2,3,4)))*(1+N$8)^(B23-G$5)","v":559},{"$f":"VLOOKUP(B23,Prices,IF(G$8=1,2,IF(G$8=2,4,6)))+('Prices and weights'!H$19-E23)*'Prices and weights'!H$20","v":135.94720000000001},{"$f":"VLOOKUP(B23,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":61.0764},{"$f":"IF(B23>=G$5,E23*F23/100*N$3*C23/G$3,0)","v":161.90646470429084},{"$f":"G$6*G23/100*(N$5*C23/G$3)","v":25.15272735776562},141.69369744874629,{"$f":"G$7*(1+N$7)^(B23-G$4)*C23/G$3","v":194.09489162695806},0,{"$f":"H23+I23-K23-L23","v":-7.0356995649015914},{"$f":"1/(1+N$9)^(B23-G$4)","v":0.48519392830146441},{"$f":"SUMPRODUCT(M$14:M23,N$14:N23)+J23*N23","v":609.75067686650334},null,null,155.27591330156645,174.68540246426221,194.09489162695806,213.50438078965385,232.91386995234967,null,36.847439787320269,28.55441542430296,21.963946670304178,16.761955040970815,12.685399761219028,null,-25.869002212686951,-14.596823785540181,-7.0356995649015914,-2.1700987068802817,0.77885845755518801,null,0.52038067849857816,{"$f":"SUMPRODUCT(M$14:M23,AJ$14:AJ23)+J23*AJ23","v":622.88933295497577},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":149.82820000000001},67.18404000000001,178.43805664998931,27.668000093542183,155.86306719362096,12.011165116573437,{"$f":"SUMPRODUCT(AR$14:AR23,N$14:N23)+AQ23*N23","v":1005.1300785367539}]
[null,"* BOY = Beginning of year",null,null,{"$f":"AVERAGE(E14:E23)","v":562},{"$f":"AVERAGE(F14:F23)","v":146.93360000000001},{"$f":"AVERAGE(G14:G23)","v":66.021560000000008},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":640},{"$f":"F33-H46","v":720},{"$f":"G7","v":800},{"$f":"F33+H46","v":880},{"$f":"G33+H46","v":960}]
[null,"Year","Calf","NPV**","NPV**","NPV**","NPV**","NPV**"]
[null,{"$f":"B14","v":2018},{"$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":1108.7807019906975},{"$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":1033.6179112930231},{"$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":958.4551205953486},{"$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":883.29232989767422},{"$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":808.12953919999973}]
[null,{"$f":"+B35+1","v":2019},{"$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":1218.5144175894425},{"$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":1083.6793629653212},{"$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":948.84430834120008},{"$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":814.00925371707899},{"$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":679.17419909295768}]
[null,2020,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":1235.0782980009758},{"$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":1052.8690167275281},{"$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":870.65973545408042},{"$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":688.45045418063273},{"$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":506.24117290718505}]
[null,2021,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":1265.8086482505876},{"$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":1045.9886379223792},{"$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":826.16862759417108},{"$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":606.34861726596273},{"$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":386.52860693775438}]
[null,2022,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":1267.0780882580405},{"$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":1017.3986577328224},{"$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":767.71922720760449},{"$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":518.03979668238662},{"$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":268.3603661571683}]
[null,2023,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":1277.8360820702328},{"$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":1004.4510495290724},{"$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":731.06601698791201},{"$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":457.68098444675206},{"$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":184.29595190559121}]
[null,2024,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":1275.8239701891116},{"$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":983.6188948195736},{"$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":691.41381945003559},{"$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":399.20874408049804},{"$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":107.00366871095957}]
[null,2025,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":1274.1858451907929},{"$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":967.03940651718131},{"$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":659.8929678435702},{"$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":352.7465291699591},{"$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":45.600090496347093}]
[null,2026,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":1269.8940623067715},{"$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":950.88557106398684},{"$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":631.87707982120219},{"$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":312.86858857841753},{"$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":-6.1399026643675825}]
[null,2027,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":1266.6023919384188},{"$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":938.17653440246113},{"$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":609.75067686650345},{"$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":281.32481933054578},{"$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":-47.101038205412806}]
[null,"** Net present value if replacement is sold in this year"]
[null,"Increment to use for annual costs/cow ",null,null,null,null,null,{"$f":"G7*0.1","v":80}]
[]
[null,"Values to include in chart (Calf number)"]
[null,null,null,"Annual cow costs, $/year"]
[null,"Calves",null,{"$f":"D33","v":640},{"$f":"E33","v":720},{"$f":"F33","v":800},{"$f":"G33","v":880},{"$f":"H33","v":960}]
[null,4,{"$f":"B51&\" calves\"","v":"4 calves"},{"$f":"VLOOKUP($B51,$C$35:$H$44,2)","v":1265.8086482505876},{"$f":"VLOOKUP($B51,$C$35:$H$44,3)","v":1045.9886379223792},{"$f":"VLOOKUP($B51,$C$35:$H$44,4)","v":826.16862759417108},{"$f":"VLOOKUP($B51,$C$35:$H$44,5)","v":606.34861726596273},{"$f":"VLOOKUP($B51,$C$35:$H$44,6)","v":386.52860693775438}]
[null,7,{"$f":"B52&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B52,$C$35:$H$44,2)","v":1275.8239701891116},{"$f":"VLOOKUP($B52,$C$35:$H$44,3)","v":983.6188948195736},{"$f":"VLOOKUP($B52,$C$35:$H$44,4)","v":691.41381945003559},{"$f":"VLOOKUP($B52,$C$35:$H$44,5)","v":399.20874408049804},{"$f":"VLOOKUP($B52,$C$35:$H$44,6)","v":107.00366871095957}]
[null,10,{"$f":"B53&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B53,$C$35:$H$44,2)","v":1266.6023919384188},{"$f":"VLOOKUP($B53,$C$35:$H$44,3)","v":938.17653440246113},{"$f":"VLOOKUP($B53,$C$35:$H$44,4)","v":609.75067686650345},{"$f":"VLOOKUP($B53,$C$35:$H$44,5)","v":281.32481933054578},{"$f":"VLOOKUP($B53,$C$35:$H$44,6)","v":-47.101038205412806}]
[]
[]
[]
[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":2018},{"$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":958.45512059534872},{"$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":958.45512059534883},{"$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":958.4551205953486},{"$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":958.4551205953486},{"$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":958.4551205953486}]
[null,{"$f":"+B61+1","v":2019},{"$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":948.27562122557038},{"$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":948.55996478338511},{"$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":948.8443083412003},{"$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":949.12865189901538},{"$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":949.41299545683046}]
[null,2020,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":860.56469024009323},{"$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":865.68064932767845},{"$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":870.65973545408042},{"$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":875.50194861929913},{"$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":880.20728882333458}]
[null,2021,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":807.69920787645628},{"$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":817.12264257982702},{"$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":826.16862759417108},{"$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":834.8440760438524},{"$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":843.15590105323383}]
[null,2022,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":734.1385904678832},{"$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":751.44308254786483},{"$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":767.7192272076046},{"$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":783.01080294348753},{"$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":797.36051345971055}]
[null,2023,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":685.27969447076111},{"$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":709.03733377917536},{"$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":731.06601698791223},{"$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":751.4681850549714},{"$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":770.34183417284407}]
[null,2024,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":629.29552764785183},{"$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":661.80466087342415},{"$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":691.41381945003582},{"$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":718.35430116344446},{"$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":742.84201872315373}]
[null,2025,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":582.15849636028747},{"$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":623.14698431381714},{"$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":659.8929678435702},{"$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":692.80976939590096},{"$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":722.27502192936151}]
[null,2026,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":537.78383990292173},{"$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":587.7713087514669},{"$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":631.87707982120219},{"$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":670.77738295473443},{"$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":705.07663361121729}]
[null,2027,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":500.66385810631863},{"$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":559.00569065477873},{"$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":609.75067686650334},{"$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":653.89144814928773},{"$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":692.29740631789741}]
[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":807.69920787645628},{"$f":"VLOOKUP($B77,$C$61:$H$70,3)","v":817.12264257982702},{"$f":"VLOOKUP($B77,$C$61:$H$70,4)","v":826.16862759417108},{"$f":"VLOOKUP($B77,$C$61:$H$70,5)","v":834.8440760438524},{"$f":"VLOOKUP($B77,$C$61:$H$70,6)","v":843.15590105323383}]
[null,7,{"$f":"B78&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B78,$C$61:$H$70,2)","v":629.29552764785183},{"$f":"VLOOKUP($B78,$C$61:$H$70,3)","v":661.80466087342415},{"$f":"VLOOKUP($B78,$C$61:$H$70,4)","v":691.41381945003582},{"$f":"VLOOKUP($B78,$C$61:$H$70,5)","v":718.35430116344446},{"$f":"VLOOKUP($B78,$C$61:$H$70,6)","v":742.84201872315373}]
[null,10,{"$f":"B79&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B79,$C$61:$H$70,2)","v":500.66385810631863},{"$f":"VLOOKUP($B79,$C$61:$H$70,3)","v":559.00569065477873},{"$f":"VLOOKUP($B79,$C$61:$H$70,4)","v":609.75067686650334},{"$f":"VLOOKUP($B79,$C$61:$H$70,5)","v":653.89144814928773},{"$f":"VLOOKUP($B79,$C$61:$H$70,6)","v":692.29740631789741}]

{"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":2018},{"$f":"'Net present value'!D14","v":1},{"$f":"'Net present value'!O14","v":958.4551205953486},{"$f":"'Net present value'!E35","v":1033.6179112930231},{"$f":"'Net present value'!AK14","v":965.18899732084287},{"$f":"'Net present value'!AS14","v":1130.1634645023255},null,{"$f":"E8-$D8","v":75.162790697674495},{"$f":"F8-$D8","v":6.7338767254942695},{"$f":"G8-$D8","v":171.70834390697689},{"$f":"I8/$D8","v":7.8420772222476939E-2},{"$f":"J8/$D8","v":7.0257611241217981E-3},{"$f":"K8/$D8","v":0.17915115712493612}]
[null,{"$f":"'Net present value'!B15","v":2019},{"$f":"'Net present value'!D15","v":2},{"$f":"'Net present value'!O15","v":948.8443083412003},{"$f":"'Net present value'!E36","v":1083.6793629653212},{"$f":"'Net present value'!AK15","v":960.70464279801865},{"$f":"'Net present value'!AS15","v":1180.153909631844},null,134.83505462412086,11.86033445681835,231.30960129064374,0.14210450907361585,1.2499768774028863E-2,0.24378035390761474]
[null,{"$f":"'Net present value'!B16","v":2020},{"$f":"'Net present value'!D16","v":3},{"$f":"'Net present value'!O16","v":870.65973545408042},{"$f":"'Net present value'!E37","v":1052.8690167275281},{"$f":"'Net present value'!AK16","v":884.95797209498107},{"$f":"'Net present value'!AS16","v":1142.5012411178434},null,182.20928127344769,14.298236640900654,271.84150566376297,0.2092772570657801,1.642230145562389E-2,0.31222473555870578]
[null,{"$f":"'Net present value'!B17","v":2021},{"$f":"'Net present value'!D17","v":4},{"$f":"'Net present value'!O17","v":826.16862759417108},{"$f":"'Net present value'!E38","v":1045.9886379223792},{"$f":"'Net present value'!AK17","v":842.19599928779417},{"$f":"'Net present value'!AS17","v":1132.0504754391779},null,219.82001032820813,16.027371693623081,305.88184784500686,0.26607160207514885,1.9399637263273106E-2,0.370241422426974]
[null,{"$f":"'Net present value'!B18","v":2022},{"$f":"'Net present value'!D18","v":5},{"$f":"'Net present value'!O18","v":767.7192272076046},{"$f":"'Net present value'!E39","v":1017.3986577328224},{"$f":"'Net present value'!AK18","v":783.98255182500907},{"$f":"'Net present value'!AS18","v":1098.3953174983506},null,249.67943052521775,16.263324617404464,330.67609029074595,0.32522232305339943,2.1183948559629573E-2,0.43072529457611902]
[null,{"$f":"'Net present value'!B19","v":2023},{"$f":"'Net present value'!D19","v":6},{"$f":"'Net present value'!O19","v":731.06601698791223},{"$f":"'Net present value'!E40","v":1004.4510495290724},{"$f":"'Net present value'!AK19","v":747.50208832466024},{"$f":"'Net present value'!AS19","v":1082.3953176985301},null,273.38503254116017,16.436071336748,351.32930071061787,0.37395396063893427,2.2482335322419673E-2,0.48057123781808464]
[null,{"$f":"'Net present value'!B20","v":2024},{"$f":"'Net present value'!D20","v":7},{"$f":"'Net present value'!O20","v":691.41381945003582},{"$f":"'Net present value'!E41","v":983.6188948195736},{"$f":"'Net present value'!AK20","v":707.23748772089732},{"$f":"'Net present value'!AS20","v":1058.0247164742536},null,292.20507536953778,15.823668270861504,366.61089702421782,0.42261966300003007,2.2885958923193003E-2,0.53023368453327613]
[null,{"$f":"'Net present value'!B21","v":2025},{"$f":"'Net present value'!D21","v":8},{"$f":"'Net present value'!O21","v":659.8929678435702},{"$f":"'Net present value'!E42","v":967.03940651718131},{"$f":"'Net present value'!AK21","v":674.96671041223999},{"$f":"'Net present value'!AS21","v":1038.6115052232171},null,307.1464386736111,15.073742568669786,378.71853737964693,0.46544887374284183,2.2842708292419727E-2,0.57390903651730296]
[null,{"$f":"'Net present value'!B22","v":2026},{"$f":"'Net present value'!D22","v":9},{"$f":"'Net present value'!O22","v":631.87707982120219},{"$f":"'Net present value'!E43","v":950.88557106398684},{"$f":"'Net present value'!AK22","v":645.9659151722484},{"$f":"'Net present value'!AS22","v":1019.8863308192671},null,319.00849124278466,14.088835351046214,388.00925099806489,0.50485846287232361,2.2296797590811226E-2,0.61405811887947748]
[null,{"$f":"'Net present value'!B23","v":2027},{"$f":"'Net present value'!D23","v":10},{"$f":"'Net present value'!O23","v":609.75067686650334},{"$f":"'Net present value'!E44","v":938.17653440246113},{"$f":"'Net present value'!AK23","v":622.88933295497577},{"$f":"'Net present value'!AS23","v":1005.1300785367539},null,328.42585753595779,13.13865608847243,395.37940167025056,0.53862319468628028,2.1547587541832218E-2,0.64842798322438522]
[]
[null,"Key Inputs Varied Across Scenarios**:"]
[null,"Annual cow costs, $/year",null,{"$f":"'Net present value'!G7","v":800},{"$f":"'Net present value'!E33","v":720},{"$f":"D20","v":800},{"$f":"D20","v":800}]
[null,"Discount rate, interest rate",null,{"$f":"'Net present value'!$N9","v":7.4999999999999997E-2},{"$f":"'Net present value'!$N9","v":7.4999999999999997E-2},6.7500000000000004E-2,{"$f":"D21","v":7.4999999999999997E-2}]
[null,"Calf Price, $/cwt",{"$f":"B8","v":2018},{"$f":"'Net present value'!F14","v":170.5986},{"$f":"D22","v":170.5986},{"$f":"E22","v":170.5986},{"$f":"'Net present value'!AM14","v":187.80160000000001}]
[null,null,2019,{"$f":"'Net present value'!F15","v":166.95659999999998},{"$f":"D23","v":166.95659999999998},166.95659999999998,{"$f":"'Net present value'!AM15","v":183.87959999999998}]
[null,null,2020,{"$f":"'Net present value'!F16","v":153.03460000000001},153.03460000000001,153.03460000000001,{"$f":"'Net present value'!AM16","v":168.64960000000002}]
[null,null,2021,{"$f":"'Net present value'!F17","v":149.27360000000002},149.27360000000002,149.27360000000002,{"$f":"'Net present value'!AM17","v":164.55460000000002}]
[null,null,2022,{"$f":"'Net present value'!F18","v":141.37260000000001},141.37260000000001,141.37260000000001,{"$f":"'Net present value'!AM18","v":155.90560000000002}]
[null,null,2023,{"$f":"'Net present value'!F19","v":141.14259999999999},141.14259999999999,141.14259999999999,{"$f":"'Net present value'!AM19","v":155.65260000000001}]
[null,null,2024,{"$f":"'Net present value'!F20","v":138.12360000000001},138.12360000000001,138.12360000000001,{"$f":"'Net present value'!AM20","v":152.28960000000004}]
[null,null,2025,{"$f":"'Net present value'!F21","v":137.19200000000001},137.19200000000001,137.19200000000001,{"$f":"'Net present value'!AM21","v":151.24800000000002}]
[null,null,2026,{"$f":"'Net present value'!F22","v":135.69460000000001},135.69460000000001,135.69460000000001,{"$f":"'Net present value'!AM22","v":149.57560000000001}]
[null,null,2027,{"$f":"'Net present value'!F23","v":135.94720000000001},135.94720000000001,135.94720000000001,{"$f":"'Net present value'!AM23","v":149.82820000000001}]
[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."]