{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Intro","Comparison","Bull carrying cost details","AI cost details","Notes"],"not_rendered":{"charts":0,"images":3,"macros":1,"external_links":2,"defined_names":6,"comments":1,"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":"Intro","index":0,"n_rows":57,"n_cols":12,"rows_kept":57,"cut":false,"hidden":false,"merged":[[56,1,57,11],[30,1,30,4],[32,1,36,11],[40,1,41,11],[45,15,45,16],[46,1,46,3],[50,1,50,5],[28,1,29,11],[4,1,5,8],[4,13,5,23],[7,2,7,3],[7,4,7,5],[7,13,8,13]]}
[]
[]
[null,"KSU-Bull vs AI Breeding Costs"]
[]
[null,"An Excel spreadsheet to evaluate the economic costs and benefits of natural (bull) breeding versus artificial insemination (AI) in beef herds"]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Version- 11.11.2015"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[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 Comparison, Bull carrying cost details, and AI cost details tab all blue numbers are inputs and all black numbers are calculated from these inputs (green values are \"pulled\" from another tab).  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,"Dustin L. Pendell, Ph.D.",null,null,null,null,null,"Sandy Johnson, Ph.D."]
[null,"Agricultural Economist",null,null,null,null,null,"Livestock Specialist"]
[null,"Department of Agricultural Economics",null,null,null,null,null,"Northwest Research and Extension Center"]
[null,"Kansas State University",null,null,null,null,null,"Kansas State University"]
[null,"dpendell@k-state.edu",null,null,null,null,null,"sandyj@ksu.edu"]
[null,"785-532-3525",null,null,null,null,null,"785-462-6281"]
[]
[null,"ACKNOWLEDGEMENTS:"]
[null,"Originally developed by:"]
[]
[null,"Kevin C. Dhuyvetter, Ph.D.",null,null,null,null,null,"L. Curt Vogel, DVM"]
[null,"Former Extension Agricultural Economist",null,null,null,null,null,"Associate Veterinarian"]
[null,"Department of Agricultural Economics",null,null,null,null,null,"Western Edge Veterinary Center"]
[null,"Kansas State University"]
[null,"Copyright 2016 AgManager.info, K-State Department of Agricultural Economics"]

{"sheet":"Comparison","index":1,"n_rows":129,"n_cols":9,"rows_kept":129,"cut":false,"hidden":false,"merged":[[5,1,5,7],[35,1,35,7],[7,1,7,3],[8,1,8,3],[9,1,9,3],[10,1,10,3],[11,1,11,3],[12,1,12,3],[13,1,13,3],[14,1,14,3],[15,1,15,3],[16,1,16,3],[19,1,19,3],[20,1,20,3],[21,1,21,3],[22,1,22,3],[23,1,23,3],[26,1,26,3],[27,1,27,3],[37,1,37,3],[36,1,36,3],[39,1,39,3],[28,1,28,3],[29,1,29,3],[31,1,31,3],[32,1,32,3],[33,1,33,3]]}
[]
[null,"Comparison of Bull vs. AI Breeding Costs"]
[]
[null,null,null,null,null,"100% bull",null,"AI plus"]
[null,null,null,null,null,"power",null,"Clean-up bulls"]
[null,"Cost"]
[null,"Bull Costs"]
[null,"Purchase price of bull, $/hd",null,null,null,5200,null,5200]
[null,"Weight of bull at salvage, lbs/hd",null,null,null,1800,null,1800]
[null,"Salvage sale price, $/lb",null,null,null,1.1000000000000001,null,{"$f":"F10","v":1.1000000000000001}]
[null,"Salvage value, $/hd",null,null,null,{"$f":"F10*F9","v":1980.0000000000002},null,{"$f":"H10*H9","v":1980.0000000000002}]
[null,"Breeding seasons, years",null,null,null,4,null,{"$f":"F12","v":4}]
[null,"Annual bull death loss, %",null,null,null,0.01,null,{"$f":"F13","v":0.01}]
[null,"Interest rate, %",null,null,null,7.0000000000000007E-2,null,{"$f":"F14","v":7.0000000000000007E-2}]
[null,"Annual carrying cost, $/hd",null,null,null,{"$f":"'Bull carrying cost details'!G32","v":772.33797500000003},null,{"$f":"'Bull carrying cost details'!G32","v":772.33797500000003}]
[null,"Annual bull ownership costs, $/hd",null,null,null,{"$f":"(F8-F11*(1-F13)*1/(1+F14)^F12)*(F14/(1-(1+F14)^-F12))","v":1093.6940523786002},null,{"$f":"(H8-H11*(1-H13)*1/(1+H14)^H12)*(H14/(1-(1+H14)^-H12))","v":1093.6940523786002}]
[null,"Total annual bull cost, $/hd",null,null,null,{"$f":"+F15+F16","v":1866.0320273786001},null,{"$f":"+H15+H16","v":1866.0320273786001}]
[]
[null,"Artificial Insemination Costs"]
[null,"Expected AI pregnancy rate, %",null,null,null,"n/a",null,0.55000000000000004]
[null,"Synchonization program costs, $/hd",null,null,null,"n/a",null,{"$f":"'AI cost details'!G11","v":20.5}]
[null,"Semen cost, $/hd",null,null,null,"n/a",null,{"$f":"'AI cost details'!G13","v":28}]
[null,"Labor and other costs, $/hd",null,null,null,"n/a",null,{"$f":"'AI cost details'!G19","v":12.5}]
[null,"Total A.I. program cost, $/hd",null,null,null,"n/a",null,{"$f":"SUM(H21:H23)","v":61}]
[]
[null,"Annual Bull Cost/Female Exposed"]
[null,"Number of females bred",null,null,null,100,null,{"$f":"F27","v":100}]
[null,"Cow-to-bull ratio (cows per bull)",null,null,null,25,null,25]
[null,"Number of bulls needed",null,null,null,4,null,2]
[null,"Cost per female exposed",null,null,null,{"$f":"F29*F17/F27","v":74.641281095144009},null,{"$f":"H29*H17/H27","v":37.320640547572005}]
[]
[null,"Total Breeding Cost per Female Exposed",null,null,null,{"$f":"F30","v":74.641281095144009},null,{"$f":"H30+H24","v":98.320640547571998}]
[null,"Final Pregnancy rate, %",null,null,null,0.92,null,0.92]
[null,"Total Breeding Cost per Female Bred",null,null,null,{"$f":"F32/F33","v":81.131827277330444},null,{"$f":"H32/H33","v":106.87026146475216}]
[]
[null,"Benefit"]
[null,"Added Value of A.I. Sired Calf at Weaning, $/hd",null,null,null,"n/a",null,50]
[null,"(due to age, genetics, gender, etc.)"]
[]
[null,"Total Cost per Female Bred",null,null,null,{"$f":"F34","v":81.131827277330444},null,{"$f":"H34-H37*H20","v":79.370261464752161}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Inputs for formula and intermediate calculations"]
[null,"Single bull = 1 vs. battery of bulls = 0",null,null,null,1,null,1]
[null,{"$f":"\" (battery implies \"&FIXED(1/F12*100,1)&\"% of bulls are bought and sold every year)\"","v":" (battery implies 25.0% of bulls are bought and sold every year)"}]
[null,"Calculated number of bulls (unrounded)",null,null,null,{"$f":"F27/F28","v":4},null,{"$f":"(H27*(1-H20))/H28","v":1.7999999999999998}]
[null,"Portion of bull",null,null,null,{"$f":"F98-INT(F98)","v":0},null,{"$f":"H98-INT(H98)","v":0.79999999999999982}]
[null,"\"Trigger\" to round number of bulls up",null,null,null,0.33,null,{"$f":"F100","v":0.33}]
[null,"Decimal places to round to",null,null,null,0,null,{"$f":"F101","v":0}]
[]
[null,"100% Bull Power -- Calculations for \"battery\" of bulls"]
[null,"End of yr"]
[null,0,{"$f":"IF(B105=F$12,F$11*(1-F$13),0)","v":0},{"$f":"F8","v":5200},{"$f":"D106*(1-F$13)","v":4351.05},0,0,{"$f":"IF(B105<F$12,1/F$12,0)","v":0.25},{"$f":"1/(1+F$14)^B105","v":1}]
[null,{"$f":"+B105+1","v":1},0,{"$f":"IF(B106<=F$12,D105-(D$105-MAX(C$105:C$115)/(1-F$13))/F$12,0)","v":4395},3554.1,{"$f":"D$105*H106","v":1300},{"$f":"IF(B106<=F$12,F$11*(1-F$13),0)","v":1960.2000000000003},0.25,{"$f":"1/(1+F$14)^B106","v":0.93457943925233644}]
[null,{"$f":"+B106+1","v":2},0,3590,2757.15,1300,1960.2000000000003,0.25,0.87343872827321156]
[null,3,0,2785,1960.2,1300,1960.2000000000003,0.25,0.81629787689085187]
[null,4,1960.2000000000003,1980,0,0,1960.2000000000003,0,0.7628952120475252]
[null,{"$f":"+B109+1","v":5},0,0,0,0,0,0,0.71298617948366838]
[null,6,0,0,0,0,0,0,0.66634222381651254]
[null,7,0,0,0,0,0,0,0.62274974188459109]
[null,8,0,0,0,0,0,0,0.5820091045650384]
[null,9,0,0,0,0,0,0,0.54393374258414806]
[null,10,0,0,{"$f":"D117*(1-F$13)","v":0},0,0,0,0.5083492921347178]
[]
[null,"AI + Clean-up Bulls -- Calculations for \"battery\" of bulls"]
[null,"End of yr"]
[null,0,{"$f":"IF(B119=H$12,H$11*(1-H$13),0)","v":0},{"$f":"H8","v":5200},{"$f":"D120*(1-H$13)","v":4351.05},0,0,{"$f":"IF(B119<H$12,1/H$12,0)","v":0.25},{"$f":"1/(1+H$14)^B119","v":1}]
[null,{"$f":"+B119+1","v":1},0,{"$f":"IF(B120<=H$12,D119-(D$119-MAX(C$119:C$129)/(1-H$13))/H$12,0)","v":4395},3554.1,{"$f":"D$119*H120","v":1300},{"$f":"IF(B120<=H$12,H$11*(1-H$13),0)","v":1960.2000000000003},0.25,0.93457943925233644]
[null,2,0,3590,2757.15,1300,1960.2000000000003,0.25,0.87343872827321156]
[null,3,0,2785,1960.2,1300,1960.2000000000003,0.25,0.81629787689085187]
[null,4,1960.2000000000003,1980,0,0,1960.2000000000003,0,0.7628952120475252]
[null,5,0,0,0,0,0,0,0.71298617948366838]
[null,6,0,0,0,0,0,0,0.66634222381651254]
[null,7,0,0,0,0,0,0,0.62274974188459109]
[null,8,0,0,0,0,0,0,0.5820091045650384]
[null,9,0,0,0,0,0,0,0.54393374258414806]
[null,10,0,0,0,0,0,0,0.5083492921347178]

{"sheet":"Bull carrying cost details","index":2,"n_rows":42,"n_cols":7,"rows_kept":42,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Estimate of Annual Bull Carrying Costs"]
[]
[null,"Feed Costs, $/head"]
[null,"Summer pasture",10,"acres",18,"$/ac",{"$f":"C5*E5","v":180}]
[]
[null,"Other feed",null,"Days","Lbs/day","$/ton"]
[null,"Crop residue",null,60,36,30,{"$f":"D8*E8*F8/2000","v":32.4}]
[null,"Hay",null,120,40,100,{"$f":"D9*E9*F9/2000","v":240}]
[null,"Grain/concentrate",null,0,0,0,{"$f":"D10*E10*F10/2000","v":0}]
[null,"Protein",null,30,1,350,{"$f":"D11*E11*F11/2000","v":5.25}]
[null,"Salt and mineral",null,365,0.2,550,{"$f":"D12*E12*F12/2000","v":20.074999999999999}]
[null,"Sub-total",null,null,null,null,{"$f":"SUM(G8:G12)","v":297.72499999999997}]
[null,"Total feed",null,null,null,null,{"$f":"G5+G13","v":477.72499999999997}]
[]
[null,"Non-feed Operating Costs, $/head"]
[null,"Labor",5,"hrs/yr",15,"$/hr",{"$f":"C17*E17","v":75}]
[null,"Vet, drugs, and supplies",null,null,null,null,12]
[null,"Breeding soundness exam, Trichomanisis test",null,null,null,null,50]
[null,"Utilities, fuel and oil",null,null,null,null,40]
[null,"Facility and equipment repairs",null,null,null,null,35]
[null,"Miscellaneous",null,null,null,null,10]
[null,"Interest on feed and non-feed operating costs",null,null,null,null,{"$f":"SUM(G14:G22)/2*Comparison!F14","v":24.490375}]
[null,"Total non-feed variable costs",null,null,null,null,{"$f":"SUM(G17:G23)","v":246.490375}]
[]
[null,"Facility and Equipment Costs, $/head (see assumptions below)"]
[null,"Depreciation",null,null,null,null,{"$f":"D38*(1-D40)/D39+F38*(1-F40)/F39","v":32.186}]
[null,"Interest",null,null,null,null,{"$f":"D38*D41+F38*F41","v":15.9366}]
[null,"Insurance and taxes",null,null,null,null,{"$f":"D38*D42+F38*F42","v":1.2731750000000002}]
[null,"Total facility and equipment costs",null,null,null,null,{"$f":"SUM(G27:G28)","v":48.122599999999998}]
[]
[null,"Total Annual Cost, $/head",null,null,null,null,{"$f":"G14+G24+G30","v":772.33797500000003}]
[]
[]
[]
[null,"Facilities and Equipment Assumptions"]
[null,null,null,"Facilities",null,"Equipment"]
[null,"Investment, $/hd",null,40.61,null,225]
[null,"Useful life, years",null,10,null,8]
[null,"Salvage value, %",null,0,null,0]
[null,"Interest rate, %",null,0.06,null,0.06]
[null,"Insurance and tax rate, %",null,1.7500000000000002E-2,null,2.5000000000000001E-3]

{"sheet":"AI cost details","index":3,"n_rows":21,"n_cols":7,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Estimate of Artificial Insemination Costs"]
[]
[null,null,null,"Units","$/unit"]
[null,"Synchronization products"]
[null,"CIDR",null,1,11.5,null,{"$f":"D6*E6","v":11.5}]
[null,"GnRH",null,2,3,null,{"$f":"D7*E7","v":6}]
[null,"Prostaglandin",null,1,3,null,{"$f":"D8*E8","v":3}]
[null,"Other",null,0,0,null,{"$f":"D9*E9","v":0}]
[null,"Other",null,0,0,null,{"$f":"D10*E10","v":0}]
[null,"Sub-total",null,null,null,null,{"$f":"SUM(G6:G10)","v":20.5}]
[]
[null,"Semen",null,1,28,null,{"$f":"D13*E13","v":28}]
[]
[null,"AI Technician Fee",null,1,8,null,{"$f":"D15*E15","v":8}]
[null,"Chute Charge",null,3,1.5,null,{"$f":"D16*E16","v":4.5}]
[null,"Other",null,0,0,null,{"$f":"D17*E17","v":0}]
[null,"Other",null,0,0,null,{"$f":"D18*E18","v":0}]
[null,"Sub-total",null,null,null,null,{"$f":"SUM(G15:G18)","v":12.5}]
[]
[null,"Total Cost, $/head",null,null,null,null,{"$f":"G11+G13+G19","v":61}]

{"sheet":"Notes","index":4,"n_rows":2,"n_cols":2,"rows_kept":2,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Notes -- this tab left blank intentionally for notes"]