{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Budget Overhead for FY21","table"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":15,"comments":0,"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":"Budget Overhead for FY21","index":0,"n_rows":55,"n_cols":13,"rows_kept":55,"cut":false,"hidden":false,"merged":[]}
["Budgeting Overhead for FY22",null,null,null,null,"Enter in the BLUE and GREEN cells ONLY"]
[]
[null,null,"Purpose:","This spreadsheet calculates the amount you need",null,null,null,"Per New Overhead Assessment Memo 01/28/2021:"]
[null,null,null,"to budget for overhead, based on the amount of",null,null,null,"Overhead Rate Schedule:",null,"FY13","FY22",null,"<-Select FY from drop down. "]
[null,null,null,"anticipated expenses subject to the overhead assessment."]
[null,null,null,null,null,null,null,null,"Admin & Facility Rate",0.08,{"$f":"IF($K$4=table!C$3,VLOOKUP($I6,table!$B$4:$G$8,2,FALSE),IF($K$4=table!D$3,VLOOKUP($I6,table!$B$4:$G$8,3,FALSE),IF($K$4=table!E$3,VLOOKUP($I6,table!$B$4:$G$8,4,FALSE),IF($K$4=table!F$3,VLOOKUP($I6,table!$B$4:$G$8,5,FALSE), VLOOKUP($I6,table!$B$4:$G$8,6,FALSE)))))","v":0.124}]
[null,null,"How to Use:","Input the revenue budget amount in the blue cell(s) and the budgeted expenses",null,null,null,null,"Admin Only",0.08,{"$f":"IF($K$4=table!C$3,VLOOKUP($I7,table!$B$4:$G$8,2,FALSE),IF($K$4=table!D$3,VLOOKUP($I7,table!$B$4:$G$8,3,FALSE),IF($K$4=table!E$3,VLOOKUP($I7,table!$B$4:$G$8,4,FALSE),IF($K$4=table!F$3,VLOOKUP($I7,table!$B$4:$G$8,5,FALSE), VLOOKUP($I7,table!$B$4:$G$8,6,FALSE)))))","v":6.6000000000000003E-2}]
[null,null,null,"NOT subject to overhead in the green cell(s) for the appropriate rate type(s).",null,null,null,null,"Facility Only",7.0000000000000007E-2,{"$f":"IF($K$4=table!C$3,VLOOKUP($I8,table!$B$4:$G$8,2,FALSE),IF($K$4=table!D$3,VLOOKUP($I8,table!$B$4:$G$8,3,FALSE),IF($K$4=table!E$3,VLOOKUP($I8,table!$B$4:$G$8,4,FALSE),IF($K$4=table!F$3,VLOOKUP($I8,table!$B$4:$G$8,5,FALSE), VLOOKUP($I8,table!$B$4:$G$8,6,FALSE)))))","v":5.8000000000000003E-2}]
[null,null,null,null,null,null,null,null,"Research Service Centers – Internal Customers",null,{"$f":"IF($K$4=table!C$3,VLOOKUP($I9,table!$B$4:$G$8,2,FALSE),IF($K$4=table!D$3,VLOOKUP($I9,table!$B$4:$G$8,3,FALSE),IF($K$4=table!E$3,VLOOKUP($I9,table!$B$4:$G$8,4,FALSE),IF($K$4=table!F$3,VLOOKUP($I9,table!$B$4:$G$8,5,FALSE), VLOOKUP($I9,table!$B$4:$G$8,6,FALSE)))))","v":0}]
[null,null,null,null,null,null,null,null,"Research Service Centers – External Customers","TBD",{"$f":"IF($K$4=table!C$3,VLOOKUP($I10,table!$B$4:$G$8,2,FALSE),IF($K$4=table!D$3,VLOOKUP($I10,table!$B$4:$G$8,3,FALSE),IF($K$4=table!E$3,VLOOKUP($I10,table!$B$4:$G$8,4,FALSE),IF($K$4=table!F$3,VLOOKUP($I10,table!$B$4:$G$8,5,FALSE), VLOOKUP($I10,table!$B$4:$G$8,6,FALSE)))))","v":0.113}]
[]
[null,null,null,null,null,null,null,null,"(1) Internal revenue: acct codes 09xxx"]
[null,null,null,null,null,null,null,null,"(2) External revenue: acct codes 01xxx thru 08xxx"]
[null,null,"Facilities and Administration"]
[]
[null,null,null,0,"<< Enter revenue budget here. Include budgeted internal sales reimbursements (79xxx accounts)."]
[null,null,null,0,"<< Enter expenses NOT subject to Overhead (Depreciation + COGS + Fund Transfers)"]
[null,null,null,{"$f":"(D16-D17)*K6/(K201+K6)","v":0},"Overhead Budget"]
[null,null,null,{"$f":"D16-D17-D18","v":0},"Amount remaining to budget for other expenses"]
[]
[]
[null,null,"Research Service Center External Revenue"]
[]
[null,null,null,0,"<< Enter external revenue budget here for Research Service Center funds"]
[null,null,null,0,"<< Enter expenses NOT subject to Overhead (Depreciation + COGS + Fund Transfers)"]
[null,null,null,{"$f":"(D24-D25)*K8/(1+K8)","v":0},"Overhead Budget"]
[null,null,null,{"$f":"D24-D25-D26","v":0},"Amount remaining to budget for other expenses"]
[]
[]
[null,null,"Non-Rsch Service Centers"]
[]
[null,null,null,0,"<< Enter revenue budget here for Service Center funds"]
[null,null,null,0,"<< Enter expenses NOT subject to Overhead (Depreciation + COGS + Fund Transfers)"]
[null,null,null,{"$f":"(D32-D33)*K9/(1+K9)","v":0},"Overhead Budget"]
[null,null,null,{"$f":"D32-D33-D34","v":0},"Amount remaining to budget for other expenses"]
[]
[]
[null,null,"Admin OH Only"]
[]
[null,null,null,0,"<< Enter revenue budget here. Include budgeted internal sales reimbursements (79xxx accounts)."]
[null,null,null,0,"<< Enter expenses NOT subject to Overhead (Depreciation + COGS + Fund Transfers)"]
[null,null,null,{"$f":"G54","v":0},"Overhead Budget"]
[null,null,null,{"$f":"D40-D41-D42","v":0},"Amount remaining to budget for other expenses"]
[]
[]
[]
[null,null,"OH RATE SLIDING SCALE CALCULATION - OVER $5M"]
[null,null,"MINIMUM","MAXIMUM","AMOUNT","RATE","OH BUDGET"]
[null,null,0,"$              5,000,000",{"$f":"IF((D40-D41)<D49,(D40-D41),D49)","v":0},{"$f":"IF($K$4=table!C$3,VLOOKUP($D49,table!$B$13:$G$17,3,FALSE),IF($K$4=table!D$3,VLOOKUP($D49,table!$B$13:$G$17,4,FALSE),IF($K$4=table!E$3,VLOOKUP($D49,table!$B$13:$G$17,5,FALSE),IF($K$4=table!F$3,VLOOKUP($D49,table!$B$13:$G$17,6,FALSE), VLOOKUP($D49,table!$B$13:$G$17,6,FALSE)))))","v":6.6000000000000003E-2},{"$f":"E49*(F49/(1+F49))","v":0},"<-rates will adjust per selected FY in cell K4"]
[null,null,{"$f":"D49","v":"$              5,000,000"},"$            10,000,000",{"$f":"IF((D40-D41)<=D49,0,IF(AND((D40-D41)>D49,(D40-D41)<=D50),((D40-D41)-D49),D49))","v":0},{"$f":"IF($K$4=table!C$3,VLOOKUP($D50,table!$B$13:$G$17,3,FALSE),IF($K$4=table!D$3,VLOOKUP($D50,table!$B$13:$G$17,4,FALSE),IF($K$4=table!E$3,VLOOKUP($D50,table!$B$13:$G$17,5,FALSE),IF($K$4=table!F$3,VLOOKUP($D50,table!$B$13:$G$17,6,FALSE), VLOOKUP($D50,table!$B$13:$G$17,6,FALSE)))))","v":5.9700000000000003E-2},{"$f":"E50*(F50/(1+F50))","v":0}]
[null,null,{"$f":"D50","v":"$            10,000,000"},"$            20,000,000",{"$f":"IF((D40-D41)<=D50,0,IF(AND((D40-D41)>D50,(D40-D41)<=D51),((D40-D41)-D50),D50))","v":0},{"$f":"IF($K$4=table!C$3,VLOOKUP($D51,table!$B$13:$G$17,3,FALSE),IF($K$4=table!D$3,VLOOKUP($D51,table!$B$13:$G$17,4,FALSE),IF($K$4=table!E$3,VLOOKUP($D51,table!$B$13:$G$17,5,FALSE),IF($K$4=table!F$3,VLOOKUP($D51,table!$B$13:$G$17,6,FALSE), VLOOKUP($D51,table!$B$13:$G$17,6,FALSE)))))","v":5.4399999999999997E-2},0]
[null,null,"$            20,000,000","$            30,000,000",{"$f":"IF((D40-D41)<=D51,0,IF(AND((D40-D41)>D51,(D40-D41)<=D52),((D40-D41)-SUM(E49:E51)),E51))","v":0},{"$f":"IF($K$4=table!C$3,VLOOKUP($D52,table!$B$13:$G$17,3,FALSE),IF($K$4=table!D$3,VLOOKUP($D52,table!$B$13:$G$17,4,FALSE),IF($K$4=table!E$3,VLOOKUP($D52,table!$B$13:$G$17,5,FALSE),IF($K$4=table!F$3,VLOOKUP($D52,table!$B$13:$G$17,6,FALSE), VLOOKUP($D52,table!$B$13:$G$17,6,FALSE)))))","v":4.8000000000000001E-2},0]
[null,null,30000000,"Above $30,000,000",{"$f":"IF((D40-D41)<C53,0,((D40-D41)-C53))","v":0},{"$f":"IF($K$4=table!C$3,VLOOKUP($D53,table!$B$13:$G$17,3,FALSE),IF($K$4=table!D$3,VLOOKUP($D53,table!$B$13:$G$17,4,FALSE),IF($K$4=table!E$3,VLOOKUP($D53,table!$B$13:$G$17,5,FALSE),IF($K$4=table!F$3,VLOOKUP($D53,table!$B$13:$G$17,6,FALSE), VLOOKUP($D53,table!$B$13:$G$17,6,FALSE)))))","v":4.2599999999999999E-2},0]
[null,null,null,null,{"$f":"SUM(E49:E53)","v":0},null,{"$f":"SUM(G49:G53)","v":0}]
[null,null,null,null,{"$f":"E54-(D40-D41)","v":0}]

{"sheet":"table","index":1,"n_rows":17,"n_cols":7,"rows_kept":17,"cut":false,"hidden":false,"merged":[[10,1,10,5]]}
[]
[]
[null,"Max Rate","FY19","FY20","FY21","FY22","FY23"]
[null,"Admin & Facility Rate",0.11,0.115,0.12,0.124,0.127]
[null,"Admin Only",0.06,6.5000000000000002E-2,6.5000000000000002E-2,6.6000000000000003E-2,6.6000000000000003E-2]
[null,"Facility Only",0.05,0.05,5.5E-2,5.8000000000000003E-2,6.0999999999999999E-2]
[null,"Research Service Centers – Internal Customers",0,0,0,0,0]
[null,"Research Service Centers – External Customers",0.108,0.11,0.11,0.113,0.113]
[]
[]
[null,"Structured Rate Schedule"]
[null,"Base Amounts - Up to","Overhead total","FY19","FY20","FY21","FY22"]
[null,"$              5,000,000","$        330,000",0.06,6.5000000000000002E-2,6.6000000000000003E-2,6.6000000000000003E-2]
[null,"$            10,000,000","$        629,000",5.6000000000000001E-2,5.8799999999999998E-2,5.9700000000000003E-2,5.9700000000000003E-2]
[null,"$            20,000,000","$     1,174,000",5.0999999999999997E-2,5.3600000000000002E-2,5.4399999999999997E-2,5.4399999999999997E-2]
[null,"$            30,000,000","$     1,657,000",4.4999999999999998E-2,4.7300000000000002E-2,4.8000000000000001E-2,4.8000000000000001E-2]
[null,"Above $30,000,000",null,0.04,4.2000000000000003E-2,4.2599999999999999E-2,4.2599999999999999E-2]