{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["CALC","Award Table $6500"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":1,"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":"CALC","index":0,"n_rows":26,"n_cols":9,"rows_kept":26,"cut":false,"hidden":false,"merged":[[25,1,25,4],[11,1,11,3],[24,1,24,2],[20,1,20,2],[13,1,14,2],[13,4,14,4],[15,1,15,3],[17,1,17,3],[18,1,18,3],[19,1,19,3],[22,1,22,3],[23,1,23,3],[8,1,8,3],[9,1,9,3],[10,1,10,3],[1,1,1,4],[2,2,2,4],[7,1,7,4],[3,2,3,4],[4,2,5,4],[6,3,6,4]]}
[]
[null,"Minnesota Postsecondary Child Care Grant Calculator 2022-2023 REVISED 11/14/22  "]
[null,"Student's Name:"]
[null,"Student's SSN:"]
[null,"Child's Name and Term Calculated:"]
[null,"(An award calculation must be completed for each child per term.)"]
[null,"Date and time of Calculation:",null,"TO RESET FORM, PRESS CTRL+SHIFT+T"]
[null,"This spreadsheet can be used to calculate the term award for the Postsecondary Child Care Grant.  The school is responsible for entering the correct values in all of the yellow boxes.  The award for the term will be the lesser of the student's net actual child care costs or the maximum annual award for the student's expected family contribution (EFC) after it has been adjusted for term length and enrollment level.   The actual child care costs entered on this spreadsheet should represent net costs after other child care assistance (Basic Sliding Fee Program, court-ordered child care payments from the child's other parent, Postsecondary Child Care Grant received by the child's other parent, etc.) has been subtracted from gross child care charges.  Because program policy caps actual child care charges at $5 per hour for home day care or $10 per hour for center day care and no more than 40 hours per week, this spreadsheet will cap those variables accordingly in determining the award based on what the user enters in cell C23."]
[null,"Number of Terms/Payment Periods in School's Academic Year = Enter '2' for semester, '3' for quarter"]
[null,"Number of Weeks in Term for Which Student is Purchasing Child Care Services"]
[null,"EFC as reported on the FAFSA or the Minnesota Dream Act Application"]
[null,"Maximum Full-Time Annual Award from MN Office of Higher Education Chart for this student's EFC",null,null,{"$f":"VLOOKUP(\"Y\",Table16[[#All],[EFC Range based on Student EFC]:[Less than Half Time Award]],2,FALSE)","v":6500}]
[null,"Maximum Full-Time Annual Award per Statutory Language Inflated by 10% for Infant Care Adjustment","Enter a 'Y' in this yellow box if the provider is charging a more expensive rate for infant care and the school requested permission from OHE on the annual program participation form to increase the maximum award by 10% for higher infant care costs.",null,{"$f":"IF(D13=\"Y\",ROUND(E12*1.1,0),E12)","v":6500}]
[null,"Number of Credits for Which Student is Enrolled for Term: When credits have a fraction round down when entering, i.e. 12.5 enter 12. ",null,"Undergraduate",null,null,null,null,"Undergraduate"]
[null,null,null," Ed Level ^",null,null,null,null,"Graduate"]
[null,"Enrollment Level",null,null,{"$f":"IF(D14=\"Undergraduate\",VLOOKUP(IF(D14=\"Undergraduate\",(IF(IF(D14=\"Undergraduate\",ROUNDDOWN($E$14,0)/12,ROUNDDOWN($E$14,0)/6)>1,1,IF(D14=\"Undergraduate\",ROUNDDOWN($E$14,0)/12,ROUNDDOWN($E$14,0)/6)))*12,(IF(IF(D14=\"Undergraduate\",ROUNDDOWN($E$14,0)/12,ROUNDDOWN($E$14,0)/6)>1,1,IF(D14=\"Undergraduate\",ROUNDDOWN($E$14,0)/12,ROUNDDOWN($E$14,0)/6)))*6),'Award Table $6500'!$B$15:$D$26,3,FALSE),VLOOKUP(IF(D14=\"Undergraduate\",(IF(IF(D14=\"Undergraduate\",ROUNDDOWN($E$14,0)/12,ROUNDDOWN($E$14,0)/6)>1,1,IF(D14=\"Undergraduate\",ROUNDDOWN($E$14,0)/12,ROUNDDOWN($E$14,0)/6)))*12,(IF(IF(D14=\"Undergraduate\",ROUNDDOWN($E$14,0)/12,ROUNDDOWN($E$14,0)/6)>1,1,IF(D14=\"Undergraduate\",ROUNDDOWN($E$14,0)/12,ROUNDDOWN($E$14,0)/6)))*6),'Award Table $6500'!$B$27:$D$33,3,FALSE))","v":{"$error":"#N/A"}}]
[null,null,null,null,{"$f":"IF(D14=\"Undergraduate\",VLOOKUP(IF(D14=\"Undergraduate\",(IF(IF(D14=\"Undergraduate\",$E$14/12,$E$14/6)>1,1,IF(D14=\"Undergraduate\",$E$14/12,$E$14/6)))*12,(IF(IF(D14=\"Undergraduate\",$E$14/12,$E$14/6)>1,1,IF(D14=\"Undergraduate\",$E$14/12,$E$14/6)))*6),'Award Table $6500'!$B$15:$D$26,2,FALSE),VLOOKUP(IF(D14=\"Undergraduate\",(IF(IF(D14=\"Undergraduate\",$E$14/12,$E$14/6)>1,1,IF(D14=\"Undergraduate\",$E$14/12,$E$14/6)))*12,(IF(IF(D14=\"Undergraduate\",$E$14/12,$E$14/6)>1,1,IF(D14=\"Undergraduate\",$E$14/12,$E$14/6)))*6),'Award Table $6500'!$B$27:$D$33,2,FALSE))","v":{"$error":"#N/A"}}]
[null,"Maximum Annual Award Adjusted for Student's Enrollment Level:",null,null,{"$f":"ROUND(IF($E$12=$E$13,VLOOKUP(\"Y\",'Award Table $6500'!$C$3:$G$11,CALC!$E$17,FALSE),VLOOKUP(\"Y\",'Award Table $6500'!$C$3:$G$11,CALC!$E$17,FALSE)*1.1),0)","v":{"$error":"#REF!"}}]
[null,"Maximum Term Award Adjusted for Student's Enrollment Level (Annual Award/Number of Terms):",null,null,{"$f":"ROUND(IF($E$12=$E$13,VLOOKUP(\"Y\",'Award Table $6500'!$C$3:$G$11,CALC!$E$17,FALSE),VLOOKUP(\"Y\",'Award Table $6500'!$C$3:$G$11,CALC!$E$17,FALSE)*1.1)/$E$9,0)","v":{"$error":"#REF!"}}]
[null,"Actual Child Care Costs Per Week: (Be sure to subtract other child care assistance from gross charges)"]
[null,"Reported Total Hours of Child Care per Week; If charged flat weekly rate enter 40 hours, if charged by hour enter actual hours per week. ",null,{"$f":"IF(E20/E21>$C$22,\"Hourly Rate Will Be Capped\",IF(E20/E21<$C$22,\"Hourly Rate OK\",IF(E20/E21=$C$22,\"Hourly Rate OK\")))","v":{"$error":"#DIV/0!"}}]
[null,"Hourly Cap for Provider Charges:  (Enter $5 for home day care; $10 for center care)",null,"Eligible Weekly Expense if more than 40 hours:",{"$f":"IF(E21<=40,E20,E20/E21*40)","v":0}]
[null,"Actual Costs for Term (hourly rate capped at cell C22)",null,null,{"$f":"IF(E20/E21 > $C$22,E21*$C$22*E10,E20*E10)","v":{"$error":"#DIV/0!"}}]
[null,"Actual Costs for Term (weekly hours capped at 40)",null,null,{"$f":"IF(E22<E20,E22*E10,IF(E22=E20,E20*E10))","v":0}]
[null,"Child Care Term Award:",null,null,{"$f":"MINA(E19,E23,E24)","v":{"$error":"#REF!"}}]
[null,"Users of this spreadsheet are responsible for the accuracy of all calculations!"]

{"sheet":"Award Table $6500","index":1,"n_rows":32,"n_cols":7,"rows_kept":32,"cut":false,"hidden":false,"merged":[]}
["Postsecondary Child Care Grant Award Table - Annual Award Amount Per Child"]
["EFC_start","EFC_end","EFC Range based on Student EFC","Full-Time Award","3 Quarter Time Award","Half Time Award","Less than Half Time Award"]
[0,6206,{"$f":"IF(CALC!$E$11>=Table16[[#This Row],[EFC_start]],IF(CALC!$E$11<=Table16[[#This Row],[EFC_end]],\"Y\",\"N\"),\"N\")","v":"Y"},6500,4875,3250,1625]
[6207,6999,{"$f":"IF(CALC!$E$11>=Table16[[#This Row],[EFC_start]],IF(CALC!$E$11<=Table16[[#This Row],[EFC_end]],\"Y\",\"N\"),\"N\")","v":"N"},6347,4760,3174,1587]
[7000,7999,{"$f":"IF(CALC!$E$11>=Table16[[#This Row],[EFC_start]],IF(CALC!$E$11<=Table16[[#This Row],[EFC_end]],\"Y\",\"N\"),\"N\")","v":"N"},5347,4010,2674,1337]
[8000,8999,{"$f":"IF(CALC!$E$11>=Table16[[#This Row],[EFC_start]],IF(CALC!$E$11<=Table16[[#This Row],[EFC_end]],\"Y\",\"N\"),\"N\")","v":"N"},4347,3260,2174,1087]
[9000,9999,{"$f":"IF(CALC!$E$11>=Table16[[#This Row],[EFC_start]],IF(CALC!$E$11<=Table16[[#This Row],[EFC_end]],\"Y\",\"N\"),\"N\")","v":"N"},3347,2510,1674,837]
[10000,10999,{"$f":"IF(CALC!$E$11>=Table16[[#This Row],[EFC_start]],IF(CALC!$E$11<=Table16[[#This Row],[EFC_end]],\"Y\",\"N\"),\"N\")","v":"N"},2347,1760,1174,587]
[11000,11999,{"$f":"IF(CALC!$E$11>=Table16[[#This Row],[EFC_start]],IF(CALC!$E$11<=Table16[[#This Row],[EFC_end]],\"Y\",\"N\"),\"N\")","v":"N"},1347,1010,674,337]
[12000,12412,{"$f":"IF(CALC!$E$11>=Table16[[#This Row],[EFC_start]],IF(CALC!$E$11<=Table16[[#This Row],[EFC_end]],\"Y\",\"N\"),\"N\")","v":"N"},655,491,328,164]
[12413,99999,{"$f":"IF(CALC!$E$11>=Table16[[#This Row],[EFC_start]],IF(CALC!$E$11<=Table16[[#This Row],[EFC_end]],\"Y\",\"N\"),\"N\")","v":"N"},0,0,0,0]
[]
["Enrollment Status - This table is used to make sure the formulas pull the right value from the award chart above (should be hidden in final spreadsheet)"]
["Level","Student Enrollment Status","Lookup Column","Report Field"]
["Undergraduate",1,5,"Less than Half Time"]
["Undergraduate",2,5,"Less than Half Time"]
["Undergraduate",3,5,"Less than Half Time"]
["Undergraduate",4,5,"Less than Half Time"]
["Undergraduate",5,5,"Less than Half Time"]
["Undergraduate",6,4,"Half Time"]
["Undergraduate",7,4,"Half Time"]
["Undergraduate",8,4,"Half Time"]
["Undergraduate",9,3,"3 Quarter Time"]
["Undergraduate",10,3,"3 Quarter Time"]
["Undergraduate",11,3,"3 Quarter Time"]
["Undergraduate",12,2,"Full-Time"]
["Graduate",1,5,"Less than Half Time"]
["Graduate",2,5,"Less than Half Time"]
["Graduate",3,4,"Half Time"]
["Graduate",4,4,"Half Time"]
["Graduate",5,3,"3 Quarter Time"]
["Graduate",6,2,"Full-Time"]