{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Instructions","Farmer Worksheet","Schedule F","AGR-Lite Fact Sheet"],"not_rendered":{"charts":0,"images":23,"macros":1,"external_links":1,"defined_names":7,"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":"Instructions","index":0,"n_rows":58,"n_cols":12,"rows_kept":58,"cut":false,"hidden":false,"merged":[[49,14,49,15],[54,1,55,11],[32,1,32,4],[34,1,35,11],[38,1,38,3],[38,4,38,7],[39,1,42,11],[43,1,44,11],[4,1,5,8],[4,13,5,23],[7,2,7,3],[7,4,7,5],[7,13,8,13],[28,1,31,11]]}
[]
[]
[null,"KSU-Adjusted Gross Revenue-Lite (AGR-Lite)"]
[]
[null,"An Excel spreadsheet  to assess the AGR-Lite whole-farm insurance program for a farming operation."]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Version- 5.24.2016"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"INTRODUCTION"]
[null,"This worksheet is designed for and intended to assist producers by providing a simplified version of the AGR-Lite whole-farm revenue insurance program.  After entering the requested information, producers can better assess their situation and deduce whether the AGR-Lite program will fit their farming operation.  It is important to note that the results obtained in this worksheet do not completely reflect the actual performance or payouts with participation in AGR-Lite.  Directions for using the spreadsheet follow the AGR-Lite overview.  Please refer to these instructions and explanations as you enter data in the spreadsheet. For further details on AGR-Lite, click on the link below to go to USDA Risk Management Agency's site, which provides a more in depth look at the AGR-Lite program.  You may also click on the AgManager.info link for further information."]
[]
[]
[]
[null,"INSTRUCTIONS FOR THE USER:"]
[null,"Be sure to \"Enable Content\" and \"Enable Macros\" for the spreadsheet to function correctly."]
[null,"To use the Adjusted Gross Revenue-Lite farmer worksheet, click the \"BEGIN\" button below. Blue cells are for data entry. Cells with red tabs in the corner indicate a description for the cell. By placing the mouse cursor over the cell the dialogue box will appear providing a description of what each cell value reveals.   At the bottom of every page is a link to take you back to the worksheet. Keep in mind this worksheet merely provides guidance to farm managers in deciding whether this particular insurance program fits your firm.  This in no way reflects the performance or outcomes of AGR-Lite for individual farms."]
[]
[]
[null,"FOR MORE INFORMATION:"]
[null,"USDA RMA: AGR-Lite",null,null,"AgManager.info"]
[]
[]
[]
[]
[null,"Developed by:"]
[]
[]
[null,"Dr. Art Barnaby",null,null,null,null,"Dr. Rich Llewelyn"]
[null,"Agricultural Economist",null,null,null,null,"Agricultural Economist"]
[null,"    Dept. of Agricultural Economics",null,null,null,null,"Dept. of Agricultural Economics"]
[null,"Kansas State University",null,null,null,null,"Kansas State University"]
[null,"barnaby@ksu.edu",null,null,null,null,"rvl@ksu.edu"]
[null,"785-532-1515",null,null,null,null,"785-532-1504"]
[]
[null,"ACKNOWLEDGEMENTS:"]
[null,"Funding for this project provided by USDA Risk Management Agency (RMA). Partnership Agreement No. 07-IE-0831-0133-E. \"Combining AGR-Lite with Other Tools to Manage Farm Level Risk in Kansas and Colorado\". 2007-2008."]
[]
[]
[null,"Copyright 2016 AgManager.info, K-State Department of Agricultural Economics"]

{"sheet":"Farmer Worksheet","index":1,"n_rows":69,"n_cols":12,"rows_kept":69,"cut":false,"hidden":false,"merged":[[64,0,64,1],[4,2,5,6],[32,7,32,9],[0,0,0,13]]}
["Adjusted Gross Revenue-Lite (AGR-Lite) Farmer Worksheet"]
[]
["Insurance Year",2016]
[]
["Coverage Level",0.75,{"$f":"IF(B5=65%,\"By choosing the 65% coverage level farm managers must produce a minimum of one commoditiy to be eligible.  Additionally the 65% coverage level allows for a 58% RMA subsidized premium.\",IF(B5=75%, \"By choosing the 75% coverage level farm managers must produce a minimum of one commodity to be eligible.  Additionally the 75% coverage level allows for a 55% RMA subsidized premium.\",IF(B5=80%,\"By choosing the 80% coverage level you must produce a minimum of three commodities to be eligible.  Additionally the 80% coverage level allows for a 48% RMA subsidized premium.\",\" \")))","v":"By choosing the 75% coverage level farm managers must produce a minimum of one commodity to be eligible.  Additionally the 75% coverage level allows for a 55% RMA subsidized premium."}]
[]
["Payment Rate",0.9]
[]
["Base Period Years",null,"AFI (Allowable Farm Income)",null,"Indexing",null,null,"Base Period Years",null,"AE (Allowable Expenses)",null,"Indexing"]
[null,{"$f":"$B$3-6","v":2010},0,null,null,null,null,null,{"$f":"$B$3-6","v":2010},0]
[null,{"$f":"$B$3-5","v":2011},0,null,{"$f":"IF(C11=0,\"\",IF(C11/C10>1.2,1.2,IF(C11/C10<0.8,0.8,C11/C10)))","v":""},null,null,null,{"$f":"$B$3-5","v":2011},0,null,{"$f":"IF(J11=0,\"\",IF(J11/J10>1.2,1.2,IF(J11/J10<0.8,0.8,J11/J10)))","v":""}]
[null,{"$f":"$B$3-4","v":2012},0,null,{"$f":"IF(C12=0,\"\",IF(C12/C11>1.2,1.2,IF(C12/C11<0.8,0.8,C12/C11)))","v":""},null,null,null,{"$f":"$B$3-4","v":2012},0,null,{"$f":"IF(J12=0,\"\",IF(J12/J11>1.2,1.2,IF(J12/J11<0.8,0.8,J12/J11)))","v":""}]
[null,{"$f":"$B$3-3","v":2013},0,null,{"$f":"IF(C13=0,\"\",IF(C13/C12>1.2,1.2,IF(C13/C12<0.8,0.8,C13/C12)))","v":""},null,null,null,{"$f":"$B$3-3","v":2013},0,null,{"$f":"IF(J13=0,\"\",IF(J13/J12>1.2,1.2,IF(J13/J12<0.8,0.8,J13/J12)))","v":""}]
[null,{"$f":"$B$3-2","v":2014},0,null,{"$f":"IF(C14=0,\"\",IF(C14/C13>1.2,1.2,IF(C14/C13<0.8,0.8,C14/C13)))","v":""},null,null,null,{"$f":"$B$3-2","v":2014},0,null,{"$f":"IF(J14=0,\"\",IF(J14/J13>1.2,1.2,IF(J14/J13<0.8,0.8,J14/J13)))","v":""}]
["Average",null,{"$f":"AVERAGE(C10:C14)","v":0},null,{"$f":"IF(SUM(E11:E14)=0,\"\",MAX(AVERAGE(E11:E14)^4,1))","v":""},null,null,"Average",null,{"$f":"AVERAGE(J10:J14)","v":0},null,{"$f":"IF(SUM(L11:L14)=0,\"\",MAX((AVERAGE(L11:L14))^4,1))","v":""}]
[]
["Indexed Average",null,{"$f":"IF(AND(OR(C14 >C15,C13>C15),C19>C15),E15*C15,C15)","v":0},null,null,null,null,"Index Eligibility",null,{"$f":"(IF(OR((MIN(C13,C15,C19)=C15),(MIN(C14,C15,C19)=C15)),\"Index\",\"No Index\"))","v":"Index"}]
[]
["Expected AFI",null,0,null,null,null,null,"Factor Eligibility",null,{"$f":"IF(AND(C17>C21,C21>C15),\"Factor UP\",IF(C21<C15,\"Factor Down\",\"No Factor\"))","v":"No Factor"}]
[]
["Approved AGR",null,{"$f":"MIN(C19,C17)","v":0},null,null,null,null,"Indexed AE",null,{"$f":"IF(AND(MAX(C14,C13)>C15,C19>C15),L15*J15,J15)","v":0}]
[]
["AGR Liability",null,{"$f":"C21*B5*B7","v":0},null,null,null,null,"Factored AE",null,{"$f":"IF(L15<1,J15,IF(AND(C17>C21, C21>C15),J15*(C21/C15),IF(C21<C15,J15*(C21/C15),J21)))","v":0}]
[]
["AGR Eligibility",null,{"$f":"IF(OR(C10=0,C11=0,C12=0,C13=0,C14=0),\"Not Approved\",IF(AND(B5=0.65,B7=0.75,C23<=2051282),\"Approved\", IF(AND(B5=0.65, B7=0.9, C23<=1709401),\"Approved\", IF(AND(B5=0.75,B7=0.75,C23<=1777777),\"Approved\", IF(AND(B5=0.75,B7=0.9,C23<=1481481),\"Approved\", IF(AND(B5=0.8,B7=0.75,C23<=1666666),\"Approved\", IF(AND(B5=0.8,B7=0.9,C23<=1388888),\"Approved\",\"Not Approved\")))))))","v":"Not Approved"},null,null,null,null,"Approved AE",null,{"$f":"J23","v":0}]
[]
["Expected AGR Loss Inception Point",null,{"$f":"IF(C25=\"Not Approved\",\"NA\",C21*B5)","v":"NA"}]
[]
["Allowable Farm Income for Current Year",{"$f":"$B$3","v":2016},0,null,null,null,null,"Allowable Expense for Current Year",{"$f":"B3","v":2016},0]
[]
["Accrual CHANGE in Accounts Payable: Current Year",{"$f":"$B$3","v":2016},0]
[]
["Accrual CHANGE: Prepaid Expenses: Current Year",{"$f":"$B$3","v":2016},0,null,null,null,null,"SUMMARY:  AGR-LITE"]
[]
["Adjusted Loss Inception Point",null,{"$f":"IF(C25=\"Not Approved\",\"NA\",IF(J25=0,0,(C21-(MAX(0.7-(J29+C31+C33)/J25,0)*C21))*B5))","v":"NA"},null,null,null,null,{"$f":"A25","v":"AGR Eligibility"},null,{"$f":"C25","v":"Not Approved"}]
[]
["Accrual CHANGE: Accounts Receivable: Current Year",{"$f":"$B$3","v":2016},0,null,null,null,null,{"$f":"A27","v":"Expected AGR Loss Inception Point"},null,{"$f":"C27","v":"NA"}]
[]
["Accrual CHANGE: Livestock & Crop Inventory: Current Year",{"$f":"$B$3","v":2016},0,null,null,null,null,{"$f":"A35","v":"Adjusted Loss Inception Point"},null,{"$f":"C35","v":"NA"}]
[]
["Non-insured Crop Disaster  Payments:  Current Year",{"$f":"$B$3","v":2016},0,null,null,null,null,"AGR to Count in Determining Indemnity",null,{"$f":"IF(C25=\"Not Approved\", \"NA\",C29+C37+C39+C41+C43+C45)","v":"NA"}]
[]
["Net Gain: Commodity Hedges:  Current Year",{"$f":"$B$3","v":2016},0,null,null,null,null,"Potential Indemnity Payment",null,{"$f":"IF(C25=\"Not Approved\", \"NA\",(MAX(C35-J41,0)*B7))","v":"NA"}]
[]
["Gross Crop Insurance Indemnity Payment: Current Year",{"$f":"$B$3","v":2016},0]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["DO NOT DELETE"]
["CL","PR"]
[0.65,0.75]
[0.75,0.9]
[0.8]

{"sheet":"Schedule F","index":2,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"AGR-Lite Fact Sheet","index":3,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}