{"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- 9.13.2016"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[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 2016 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,"FAPRI (As of Aug. '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":2017},{"$f":"150.53+100*$H$20","v":158.94999999999999},{"$f":"C7*0.44","v":69.938000000000002},{"$f":"C7-25","v":133.94999999999999},{"$f":"E7*0.44","v":58.937999999999995},{"$f":"C7*1.1","v":174.845},{"$f":"G7*0.44","v":76.931799999999996},null,1,542,562,537]
[null,{"$f":"B7+1","v":2018},{"$f":"141.38+100*$H$20","v":149.79999999999998},{"$f":"C8*0.44","v":65.911999999999992},{"$f":"E7","v":133.94999999999999},{"$f":"E8*0.44","v":58.937999999999995},{"$f":"C8*1.1","v":164.78},{"$f":"G8*0.44","v":72.503200000000007},null,2,552,{"$f":"L7","v":562},552]
[null,{"$f":"B8+1","v":2019},{"$f":"135.04+100*$H$20","v":143.45999999999998},63.122399999999992,{"$f":"E8","v":133.94999999999999},58.937999999999995,157.80599999999998,69.434639999999987,null,3,562,{"$f":"L8","v":562},567]
[null,2020,{"$f":"134.94+100*$H$20","v":143.35999999999999},63.078399999999995,133.94999999999999,58.937999999999995,157.696,69.386240000000001,null,4,567,562,582]
[null,2021,{"$f":"140.38+100*$H$20","v":148.79999999999998},65.471999999999994,133.94999999999999,58.937999999999995,163.68,72.019199999999998,null,5,572,562,582]
[null,2022,{"$f":"C11","v":148.79999999999998},65.471999999999994,133.94999999999999,58.937999999999995,163.68,72.019199999999998,null,6,572,562,577]
[null,2023,{"$f":"C12","v":148.79999999999998},65.471999999999994,133.94999999999999,58.937999999999995,163.68,72.019199999999998,null,7,567,562,567]
[null,2024,148.79999999999998,65.471999999999994,133.94999999999999,58.937999999999995,163.68,72.019199999999998,null,8,565,562,557]
[null,2025,148.79999999999998,65.471999999999994,133.94999999999999,58.937999999999995,163.68,72.019199999999998,null,9,562,562,552]
[null,2026,148.79999999999998,65.471999999999994,133.94999999999999,58.937999999999995,163.68,72.019199999999998,null,10,559,562,547]
[null,"Average",{"$f":"AVERAGE(C7:C16)","v":148.83699999999996},65.488279999999989,133.95000000000002,58.938000000000002,163.72070000000002,72.037107999999989,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,2016,null,"Annual cow death loss",null,null,null,null,5.0000000000000001E-3]
[null,"First year for calf sales",null,null,null,null,2017,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) (FAPRI (As of Aug. '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":2017},{"$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":157.51859999999999},{"$f":"VLOOKUP(B14,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":69.938000000000002},{"$f":"IF(B14>=G$5,E14*F14/100*N$3*C14/G$3,0)","v":828.13828763999993},{"$f":"IF(B14>=G$5,G$6*G14/100*(N$5*C14/G$3),0)","v":131.13374999999999},{"$f":"IF(B14>=G$5,G$6*G14/100*(C14*(1-N$4-N$5))/G$3,0)","v":738.72012499999994},{"$f":"G$7*(1+N$7)^(B14-G$4)*C14/G$3","v":808},0,{"$f":"H14+I14-K14-L14","v":151.27203763999989},{"$f":"1/(1+N$9)^(B14-G$4)","v":0.93023255813953487},{"$f":"SUMPRODUCT(M$14:M14,N$14:N14)+J14*N14","v":827.89968617674413},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":107.56078763999994},{"$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":129.41641263999998},{"$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":151.27203763999989},{"$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":173.12766263999993},{"$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":194.98328763999996},null,{"$f":"1/(1+AJ$11)^(B14-G$4)","v":0.93676814988290402},{"$f":"SUMPRODUCT(M$14:M14,AJ$14:AJ14)+J14*AJ14","v":833.71631160655727},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":173.4136},{"$f":"G14*AM$9","v":76.93180000000001},{"$f":"IF(B14>=G$5,E14*AM14/100*N$3*C14/G$3,0)","v":911.70466063999993},{"$f":"IF(B14>=G$5,G$6*AN14/100*(N$5*C14/G$3),0)","v":144.24712500000001},{"$f":"IF(B14>=G$5,G$6*AN14/100*(C14*(1-N$4-N$5))/G$3,0)","v":812.59213750000015},{"$f":"AO14+AP14-K14-L14","v":247.95178564000003},{"$f":"SUMPRODUCT(AR$14:AR14,N$14:N14)+AQ14*N14","v":986.55248664186058}]
[null,{"$f":"+B14+1","v":2018},{"$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":147.52659999999997},{"$f":"VLOOKUP(B15,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":65.911999999999992},{"$f":"IF(B15>=G$5,E15*F15/100*N$3*C15/G$3,0)","v":667.47938084879979},{"$f":"G$6*G15/100*(N$5*C15/G$3)","v":104.42932499999998},{"$f":"IF(B15>=G$5,G$6*G15/100*(C15*(1-N$4-N$5))/G$3,0)","v":588.28519749999987},{"$f":"G$7*(1+N$7)^(B15-G$4)*C15/G$3","v":689.58760000000007},0,{"$f":"H15+I15-K15-L15","v":82.321105848799675},0.86533261222282321,{"$f":"SUMPRODUCT(M$14:M15,N$14:N15)+J15*N15","v":721.01567879874483},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,50.32265220079978,66.836816524799701,82.321105848799675,96.775520172799816,110.20005949679989,null,0.87753456663503904,{"$f":"SUMPRODUCT(M$14:M15,AJ$14:AJ15)+J15*AJ15","v":730.18703862095401},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":162.50659999999999},72.503199999999993,735.25591148879994,114.87225749999999,647.11371724999992,160.54056898879981,{"$f":"SUMPRODUCT(AR$14:AR15,N$14:N15)+AQ15*N15","v":929.54241713514318}]
[null,2019,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":140.34459999999999},63.122399999999992,546.28235730597089,{"$f":"G$6*G16/100*(N$5*C16/G$3)","v":84.508071862499989},476.06213815874997,588.52853721999998,0,42.261891948470861,0.80496056950960304,{"$f":"SUMPRODUCT(M$14:M16,N$14:N16)+J16*N16","v":629.18371857967156},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,15.809632123250935,29.869168096836006,42.261891948470861,53.061775240656061,62.342789535891029,null,0.82204643244500153,{"$f":"SUMPRODUCT(M$14:M16,AJ$14:AJ16)+J16*AJ16","v":640.0328625751398},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":154.69059999999999},69.434640000000002,602.12324251218092,92.958879048750006,523.66835197462501,106.55358434093091,{"$f":"SUMPRODUCT(AR$14:AR16,N$14:N16)+AQ16*N16","v":876.87762255599409}]
[null,2020,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":139.8236},63.078399999999995,463.9865587158888,71.359544256000007,401.99209930880005,502.27968009040904,0,33.066422881479809,0.7488005297763749,{"$f":"SUMPRODUCT(M$14:M17,N$14:N17)+J17*N17","v":571.74452062525313},null,null,401.82374407232726,452.05171208136812,502.27968009040904,552.50764809944985,602.73561610849083,null,71.691737500000002,65.850300000000004,60.335112500000001,55.136800000000015,50.245987500000005,null,11.026626289008505,23.108588725768982,33.066422881479809,41.086074023987521,47.346095421138159,null,0.77006691563934571,{"$f":"SUMPRODUCT(M$14:M17,AJ$14:AJ17)+J17*AJ17","v":583.71185458528066},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":154.15960000000001},69.386240000000001,511.55872325571613,78.495498681599997,442.19130923967998,87.774541846907084,{"$f":"SUMPRODUCT(AR$14:AR17,N$14:N17)+AQ17*N17","v":852.18395777336843}]
[null,2021,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":144.84259999999998},65.471999999999994,409.72353484072414,62.586939568725001,352.5730929038175,428.67059297315956,0,43.639881436289613,0.69655863235011617,{"$f":"SUMPRODUCT(M$14:M18,N$14:N18)+J18*N18","v":546.7181912226232},null,null,342.93647437852763,385.8035336758436,428.67059297315956,471.53765227047552,514.40471156779142,null,64.164105062499999,57.289761000000006,50.983170062500001,45.212176000000014,39.945560062500007,null,28.666367357283093,37.316626387730082,43.639881436289613,47.950515532220606,50.537781123298657,null,0.72137415984950426,{"$f":"SUMPRODUCT(M$14:M18,AJ$14:AJ18)+J18*AJ18","v":559.96884004523531},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":159.7226},72.019199999999998,451.81533793201083,68.845633525597506,387.83040219419928,91.99037848444874,{"$f":"SUMPRODUCT(AR$14:AR18,N$14:N18)+AQ18*N18","v":855.29417791487128}]
[null,2022,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":144.84259999999998},65.471999999999994,346.21638694041189,52.885963935572619,297.9242635037258,365.84891757294309,0,33.25343330304139,0.64796151846522443,{"$f":"SUMPRODUCT(M$14:M19,N$14:N19)+J19*N19","v":515.72076313081823},null,null,292.67913405835446,329.26402581564878,365.84891757294309,402.43380933023735,439.01870108753172,null,57.426874030937498,49.842092070000007,43.080778702812502,37.073984320000015,31.756720249687508,null,20.827901044580869,28.274701782638715,33.25343330304139,36.202212327015275,37.507405421771523,null,0.67576033709555439,{"$f":"SUMPRODUCT(M$14:M19,AJ$14:AJ19)+J19*AJ19","v":529.42847339887305},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":159.7226},72.019199999999998,381.7839605525491,58.174560329129882,327.71668985409838,74.109603308735871,{"$f":"SUMPRODUCT(AR$14:AR19,N$14:N19)+AQ19*N19","v":845.51553845572687}]
[null,2023,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":145.2636},65.471999999999994,290.83846894714645,44.688639525558877,251.74600266064829,312.23375870262822,0,23.2933497700771,0.60275490089788319,{"$f":"SUMPRODUCT(M$14:M20,N$14:N20)+J20*N20","v":488.45862258071026},null,null,249.78700696210257,281.01038283236539,312.23375870262822,343.45713457289099,374.68051044315382,null,51.397052257689062,43.362620100900003,36.403258003876566,30.400667142400014,25.246592598501572,null,11.855753909075815,18.87444416253237,23.2933497700771,25.672448935824093,26.485440866068217,null,0.63303076074525011,{"$f":"SUMPRODUCT(M$14:M20,AJ$14:AJ20)+J20*AJ20","v":502.21144316875359},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":160.14360000000002},72.019199999999998,320.63035361703999,49.157503478114762,276.92060292671317,57.554098392526555,{"$f":"SUMPRODUCT(AR$14:AR20,N$14:N20)+AQ20*N20","v":834.77399991799575}]
[null,2024,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":145.43199999999999},65.471999999999994,245.17552919518485,37.761900399097243,212.72537224824779,266.4759013647581,0,16.461528229523992,0.56070223339337966,{"$f":"SUMPRODUCT(M$14:M21,N$14:N21)+J21*N21","v":465.22309265794581},null,null,213.18072109180648,239.8283112282823,266.4759013647581,293.1234915012339,319.7710816377097,null,46.000361770631713,37.725479487783005,30.760753013275696,24.928547056768014,20.07104111580875,null,5.79361285283386,12.470048405748628,16.461528229523992,18.440820765771548,18.954029772265812,null,0.59300305456229518,{"$f":"SUMPRODUCT(M$14:M21,AJ$14:AJ21)+J21*AJ21","v":478.75701163889107},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":160.31200000000001},72.019199999999998,270.26087406030643,41.538090439006972,233.99790947307258,45.323063134555298,{"$f":"SUMPRODUCT(AR$14:AR21,N$14:N21)+AQ21*N21","v":824.47464251903057}]
[null,2025,{"$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":145.68459999999999},{"$f":"VLOOKUP(B22,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":65.471999999999994},{"$f":"IF(B22>=G$5,E22*F22/100*N$3*C22/G$3,0)","v":206.43121433148124},{"$f":"G$6*G22/100*(N$5*C22/G$3)","v":31.908805837237171},179.75293954976939,{"$f":"G$7*(1+N$7)^(B22-G$4)*C22/G$3","v":227.4238580197528},0,{"$f":"H22+I22-K22-L22","v":10.916162148965611},{"$f":"1/(1+N$9)^(B22-G$4)","v":0.52158347292407414},{"$f":"SUMPRODUCT(M$14:M22,N$14:N22)+J22*N22","v":445.39735358225488},null,null,181.93908641580222,204.68147221777753,227.4238580197528,250.1662438217281,272.9086296237034,null,41.170323784715386,32.821167154371217,25.992836296217963,20.441408586549773,15.956477687067958,null,0.44332628397387452,7.0686325058247803,10.916162148965611,12.767051782018456,13.230602333152973,null,0.55550637429723215,{"$f":"SUMPRODUCT(M$14:M22,AJ$14:AJ22)+J22*AJ22","v":458.5281174879533},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":160.56460000000001},72.019199999999998,227.51577968191941,35.099686420960886,197.72823350474636,35.191608083127505,{"$f":"SUMPRODUCT(AR$14:AR22,N$14:N22)+AQ22*N22","v":814.75863195642535}]
[null,2026,{"$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":145.93719999999999},{"$f":"VLOOKUP(B23,Prices,IF(G$8=1,3,IF(G$8=2,5,7)))","v":65.471999999999994},{"$f":"IF(B23>=G$5,E23*F23/100*N$3*C23/G$3,0)","v":173.80406599652682},{"$f":"G$6*G23/100*(N$5*C23/G$3)","v":26.962940932465411},151.89123391955513,{"$f":"G$7*(1+N$7)^(B23-G$4)*C23/G$3","v":194.09489162695806},0,{"$f":"H23+I23-K23-L23","v":6.6721153020341717},{"$f":"1/(1+N$9)^(B23-G$4)","v":0.48519392830146441},{"$f":"SUMPRODUCT(M$14:M23,N$14:N23)+J23*N23","v":428.57516539703488},null,null,155.27591330156645,174.68540246426221,194.09489162695806,213.50438078965385,232.91386995234967,null,36.847439787320269,28.55441542430296,21.963946670304178,16.761955040970815,12.685399761219028,null,-3.8846121449699353,2.831906773664457,6.6721153020341717,8.5213721746241617,9.0443829455287954,null,0.52038067849857816,{"$f":"SUMPRODUCT(M$14:M23,AJ$14:AJ23)+J23*AJ23","v":441.18751702232902},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":160.81720000000001},72.019199999999998,191.52541807144891,29.659235025711954,167.08035731151062,27.089761470202802,{"$f":"SUMPRODUCT(AR$14:AR23,N$14:N23)+AQ23*N23","v":805.83701592033901}]
[null,"* BOY = Beginning of year",null,null,{"$f":"AVERAGE(E14:E23)","v":562},{"$f":"AVERAGE(F14:F23)","v":145.7216},{"$f":"AVERAGE(G14:G23)","v":65.488279999999989},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":2017},{"$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":978.22526757209312},{"$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":903.06247687441862},{"$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":827.89968617674424},{"$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":752.73689547906986},{"$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":677.57410478139536}]
[null,{"$f":"+B35+1","v":2018},{"$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":990.68578804698745},{"$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":855.85073342286614},{"$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":721.01567879874506},{"$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":586.18062417462397},{"$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":451.34556955050266}]
[null,2019,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":993.60228112656705},{"$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":811.39299985311936},{"$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":629.18371857967168},{"$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":446.97443730622399},{"$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":264.7651560327763}]
[null,2020,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":1011.3845412816697},{"$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":791.56453095346137},{"$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":571.74452062525324},{"$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":351.92451029704489},{"$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":132.10449996883654}]
[null,2021,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":1046.0770522730593},{"$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":796.39762174784119},{"$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":546.71819122262332},{"$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":297.03876069740545},{"$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":47.359330172187128}]
[null,2022,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":1062.4908282131391},{"$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":789.10579567197874},{"$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":515.72076313081834},{"$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":242.3357305896584},{"$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":-31.049301951502457}]
[null,2023,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":1072.8687733197862},{"$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":780.66369795024821},{"$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":488.4586225807102},{"$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":196.25354721117264},{"$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":-95.951528158365818}]
[null,2024,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":1079.5159700051686},{"$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":772.36953133155703},{"$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":465.22309265794593},{"$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":158.07665398433483},{"$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":-149.06978468927718}]
[null,2025,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":1083.414336067824},{"$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":764.40584482503937},{"$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":445.39735358225471},{"$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":126.38886233947005},{"$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":-192.61962890331506}]
[null,2026,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":1085.42688046895},{"$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":757.00102293299233},{"$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":428.57516539703465},{"$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":100.14930786107698},{"$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":-228.27654967488161}]
[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":1011.3845412816697},{"$f":"VLOOKUP($B51,$C$35:$H$44,3)","v":791.56453095346137},{"$f":"VLOOKUP($B51,$C$35:$H$44,4)","v":571.74452062525324},{"$f":"VLOOKUP($B51,$C$35:$H$44,5)","v":351.92451029704489},{"$f":"VLOOKUP($B51,$C$35:$H$44,6)","v":132.10449996883654}]
[null,7,{"$f":"B52&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B52,$C$35:$H$44,2)","v":1072.8687733197862},{"$f":"VLOOKUP($B52,$C$35:$H$44,3)","v":780.66369795024821},{"$f":"VLOOKUP($B52,$C$35:$H$44,4)","v":488.4586225807102},{"$f":"VLOOKUP($B52,$C$35:$H$44,5)","v":196.25354721117264},{"$f":"VLOOKUP($B52,$C$35:$H$44,6)","v":-95.951528158365818}]
[null,10,{"$f":"B53&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B53,$C$35:$H$44,2)","v":1085.42688046895},{"$f":"VLOOKUP($B53,$C$35:$H$44,3)","v":757.00102293299233},{"$f":"VLOOKUP($B53,$C$35:$H$44,4)","v":428.57516539703465},{"$f":"VLOOKUP($B53,$C$35:$H$44,5)","v":100.14930786107698},{"$f":"VLOOKUP($B53,$C$35:$H$44,6)","v":-228.27654967488161}]
[]
[]
[]
[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":2017},{"$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":827.89968617674413},{"$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":827.89968617674413},{"$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":827.89968617674413},{"$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":827.89968617674413},{"$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":827.89968617674413}]
[null,{"$f":"+B61+1","v":2018},{"$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":714.69118132075698},{"$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":717.85343005975096},{"$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":721.01567879874483},{"$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":724.17792753773915},{"$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":727.34017627673313}]
[null,2019,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":611.67000428286167},{"$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":620.50724373599962},{"$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":629.18371857967156},{"$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":637.69942881387828},{"$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":646.05437443861865}]
[null,2020,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":543.41926716923319},{"$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":557.81757166390673},{"$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":571.74452062525313},{"$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":585.2090390798212},{"$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":598.22005205415758}]
[null,2021,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":511.92275514084145},{"$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":529.695498665576},{"$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":546.7181912226232},{"$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":563.01554251617904},{"$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":578.61180205575329}]
[null,2022,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":470.60304245433622},{"$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":493.83317882352333},{"$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":515.72076313081823},{"$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":536.34011561724787},{"$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":555.76224636034999}]
[null,2023,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":432.11215621659471},{"$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":461.35922158360313},{"$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":488.45862258071026},{"$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":513.5732725955354},{"$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":536.8552530423791}]
[null,2024,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":397.36519206008631},{"$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":432.86279605361983},{"$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":465.22309265794581},{"$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":494.7431743590393},{"$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":521.69433431499499}]
[null,2025,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":365.96299771255201},{"$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":407.82882558780807},{"$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":445.39735358225488},{"$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":479.15172337777449},{"$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":509.52371316439275}]
[null,2026,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":337.74154117970022},{"$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":385.95899695468648},{"$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":428.57516539703488},{"$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":466.31374092024816},{"$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":499.80796466111991}]
[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":543.41926716923319},{"$f":"VLOOKUP($B77,$C$61:$H$70,3)","v":557.81757166390673},{"$f":"VLOOKUP($B77,$C$61:$H$70,4)","v":571.74452062525313},{"$f":"VLOOKUP($B77,$C$61:$H$70,5)","v":585.2090390798212},{"$f":"VLOOKUP($B77,$C$61:$H$70,6)","v":598.22005205415758}]
[null,7,{"$f":"B78&\" calves\"","v":"7 calves"},{"$f":"VLOOKUP($B78,$C$61:$H$70,2)","v":432.11215621659471},{"$f":"VLOOKUP($B78,$C$61:$H$70,3)","v":461.35922158360313},{"$f":"VLOOKUP($B78,$C$61:$H$70,4)","v":488.45862258071026},{"$f":"VLOOKUP($B78,$C$61:$H$70,5)","v":513.5732725955354},{"$f":"VLOOKUP($B78,$C$61:$H$70,6)","v":536.8552530423791}]
[null,10,{"$f":"B79&\" calves\"","v":"10 calves"},{"$f":"VLOOKUP($B79,$C$61:$H$70,2)","v":337.74154117970022},{"$f":"VLOOKUP($B79,$C$61:$H$70,3)","v":385.95899695468648},{"$f":"VLOOKUP($B79,$C$61:$H$70,4)","v":428.57516539703488},{"$f":"VLOOKUP($B79,$C$61:$H$70,5)","v":466.31374092024816},{"$f":"VLOOKUP($B79,$C$61:$H$70,6)","v":499.80796466111991}]

{"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":2017},{"$f":"'Net present value'!D14","v":1},{"$f":"'Net present value'!O14","v":827.89968617674413},{"$f":"'Net present value'!E35","v":903.06247687441862},{"$f":"'Net present value'!AK14","v":833.71631160655727},{"$f":"'Net present value'!AS14","v":986.55248664186058},null,{"$f":"E8-$D8","v":75.162790697674495},{"$f":"F8-$D8","v":5.8166254298131435},{"$f":"G8-$D8","v":158.65280046511646},{"$f":"I8/$D8","v":9.0787316329080436E-2},{"$f":"J8/$D8","v":7.0257611241217226E-3},{"$f":"K8/$D8","v":0.19163287909647361}]
[null,{"$f":"'Net present value'!B15","v":2018},{"$f":"'Net present value'!D15","v":2},{"$f":"'Net present value'!O15","v":721.01567879874483},{"$f":"'Net present value'!E36","v":855.85073342286614},{"$f":"'Net present value'!AK15","v":730.18703862095401},{"$f":"'Net present value'!AS15","v":929.54241713514318},null,134.83505462412131,9.1713598222091832,208.52673833639835,0.18700710482296939,1.2720056015271701E-2,0.28921248797781535]
[null,{"$f":"'Net present value'!B16","v":2019},{"$f":"'Net present value'!D16","v":3},{"$f":"'Net present value'!O16","v":629.18371857967156},{"$f":"'Net present value'!E37","v":811.39299985311936},{"$f":"'Net present value'!AK16","v":640.0328625751398},{"$f":"'Net present value'!AS16","v":876.87762255599409},null,182.2092812734478,10.849143995468239,247.69390397632253,0.28959630691774679,1.7243205243707281E-2,0.39367500566522978]
[null,{"$f":"'Net present value'!B17","v":2020},{"$f":"'Net present value'!D17","v":4},{"$f":"'Net present value'!O17","v":571.74452062525313},{"$f":"'Net present value'!E38","v":791.56453095346137},{"$f":"'Net present value'!AK17","v":583.71185458528066},{"$f":"'Net present value'!AS17","v":852.18395777336843},null,219.82001032820824,11.967333960027531,280.4394371481153,0.38447243899743133,2.0931261303458047E-2,0.49049781332653614]
[null,{"$f":"'Net present value'!B18","v":2021},{"$f":"'Net present value'!D18","v":5},{"$f":"'Net present value'!O18","v":546.7181912226232},{"$f":"'Net present value'!E39","v":796.39762174784119},{"$f":"'Net present value'!AK18","v":559.96884004523531},{"$f":"'Net present value'!AS18","v":855.29417791487128},null,249.67943052521798,13.250648822612106,308.57598669224808,0.45668762176517502,2.4236707384804126E-2,0.56441507095672294]
[null,{"$f":"'Net present value'!B19","v":2022},{"$f":"'Net present value'!D19","v":6},{"$f":"'Net present value'!O19","v":515.72076313081823},{"$f":"'Net present value'!E40","v":789.10579567197874},{"$f":"'Net present value'!AK19","v":529.42847339887305},{"$f":"'Net present value'!AS19","v":845.51553845572687},null,273.38503254116051,13.707710268054825,329.79477532490864,0.53010282324392943,2.6579713767657082E-2,0.63948322212742204]
[null,{"$f":"'Net present value'!B20","v":2023},{"$f":"'Net present value'!D20","v":7},{"$f":"'Net present value'!O20","v":488.45862258071026},{"$f":"'Net present value'!E41","v":780.66369795024821},{"$f":"'Net present value'!AK20","v":502.21144316875359},{"$f":"'Net present value'!AS20","v":834.77399991799575},null,292.20507536953795,13.752820588043335,346.3153773372855,0.59821868600805705,2.8155548806533544E-2,0.70899634345192097]
[null,{"$f":"'Net present value'!B21","v":2024},{"$f":"'Net present value'!D21","v":8},{"$f":"'Net present value'!O21","v":465.22309265794581},{"$f":"'Net present value'!E42","v":772.36953133155703},{"$f":"'Net present value'!AK21","v":478.75701163889107},{"$f":"'Net present value'!AS21","v":824.47464251903057},null,307.14643867361121,13.533918980945259,359.25154986108475,0.66021322569952456,2.9091245027460494E-2,0.77221349397894923]
[null,{"$f":"'Net present value'!B22","v":2025},{"$f":"'Net present value'!D22","v":9},{"$f":"'Net present value'!O22","v":445.39735358225488},{"$f":"'Net present value'!E43","v":764.40584482503937},{"$f":"'Net present value'!AK22","v":458.5281174879533},{"$f":"'Net present value'!AS22","v":814.75863195642535},null,319.00849124278449,13.130763905698416,369.36127837417047,0.71623346810899002,2.94810101588838E-2,0.82928485183726564]
[null,{"$f":"'Net present value'!B23","v":2026},{"$f":"'Net present value'!D23","v":10},{"$f":"'Net present value'!O23","v":428.57516539703488},{"$f":"'Net present value'!E44","v":757.00102293299233},{"$f":"'Net present value'!AK23","v":441.18751702232902},{"$f":"'Net present value'!AS23","v":805.83701592033901},null,328.42585753595745,12.612351625294139,377.26185052330413,0.76632031917132093,2.9428563863727317E-2,0.88026997591847456]
[]
[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":2017},{"$f":"'Net present value'!F14","v":157.51859999999999},{"$f":"D22","v":157.51859999999999},{"$f":"E22","v":157.51859999999999},{"$f":"'Net present value'!AM14","v":173.4136}]
[null,null,2018,{"$f":"'Net present value'!F15","v":147.52659999999997},{"$f":"D23","v":147.52659999999997},147.52659999999997,{"$f":"'Net present value'!AM15","v":162.50659999999999}]
[null,null,2019,{"$f":"'Net present value'!F16","v":140.34459999999999},140.34459999999999,140.34459999999999,{"$f":"'Net present value'!AM16","v":154.69059999999999}]
[null,null,2020,{"$f":"'Net present value'!F17","v":139.8236},139.8236,139.8236,{"$f":"'Net present value'!AM17","v":154.15960000000001}]
[null,null,2021,{"$f":"'Net present value'!F18","v":144.84259999999998},144.84259999999998,144.84259999999998,{"$f":"'Net present value'!AM18","v":159.7226}]
[null,null,2022,{"$f":"'Net present value'!F19","v":144.84259999999998},144.84259999999998,144.84259999999998,{"$f":"'Net present value'!AM19","v":159.7226}]
[null,null,2023,{"$f":"'Net present value'!F20","v":145.2636},145.2636,145.2636,{"$f":"'Net present value'!AM20","v":160.14360000000002}]
[null,null,2024,{"$f":"'Net present value'!F21","v":145.43199999999999},145.43199999999999,145.43199999999999,{"$f":"'Net present value'!AM21","v":160.31200000000001}]
[null,null,2025,{"$f":"'Net present value'!F22","v":145.68459999999999},145.68459999999999,145.68459999999999,{"$f":"'Net present value'!AM22","v":160.56460000000001}]
[null,null,2026,{"$f":"'Net present value'!F23","v":145.93719999999999},145.93719999999999,145.93719999999999,{"$f":"'Net present value'!AM23","v":160.81720000000001}]
[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."]