[Mail_treas] "what if" enrollment changes

Mon Oct 17 07:46:11 EDT 2016

Ladies and Gentlemen,

               Many of you now are getting updated enrollment figures.  Attached is a tool that some of you may find beneficial.  I realize that ODE already has a tool to help you project your state aid and it is very useful.  I believe that many of you, like myself, want to know what the formulas are and where the data comes from that determines your funding.  I believe that this tool gives you more of that information.  Along with the template I have attached an example district that is completed.

               In order for this to work you need to enter a small amount of data from the reports selected below from the most current Foundation Funding Report.

Payment Date  (Select date for the July No.1 to June No. 2 payment from the drop down list):
Choose Oct  #1 or most recent report
Choose your District
Choose three reports only:
School Finance payment report Detail
State Share Index
Transportation

Step One.  Cells highlighted in green need to have data entered or remain as a 0.00. Click on the state share index tab. Enter your IRN into cell B11.  Your district name will appear in cell F11.  Enter the total ADM (E1) and the formula ADM (E2) into cells E14 and E15.  All of the #N/A will now populate and you will see your state share index in cell G49.  It probably will not match perfectly to the state share you have on your SFPR but will come very close.  Scroll down further and you will see the data used to calculate your targeted assistance.  Again these numbers may or not be exactly what is on your SFPR but should be very close.

Step two.  Click on the SFPR tab.  All of the cells highlighted in green need to be entered from your school finance report detail.  Once you finish entering the third grade guarantee numbers in m1 and m2 you will see that the formula amounts have all populated from A-Opportunity Grant down to F3-Intervention Specialist Funding.  Transportation funding will not be calculated.

Step three.  Click on the Transportation tab.  From the SFPR Transportation worksheet enter the riders, miles and miles driven in their appropriate cells in column C.  Lastly enter your district square mileage driven in cell C40.

Step four. Go back to the SFPR tab. Scroll down to G117 and enter the funding guarantee base from the SFPR detail worksheet in that cell.

Step five.  Enter Q1 through Q9 from your SFPR detail worksheet in cells G121 to G129.

In column K I have put notes in some of the cells to show what those lines are used for.

The results should be within a couple of hundred dollars of your SFPR.  The biggest difference will be in gifted funding (F2 and F3) and Transportation (G3).  If your results are not close, double check the data you entered.  If you did not find a mistake e-mail me the spreadsheet and I will take a look at it.

If you are doing this after the Oct#1 you may have to change the amounts in the cells that are formatted in blue.  These amounts may change at any time when ODE gets updated info.

Look at the Youngstown example.  I chose Youngstown because they are a high state share index district so enrollment changes have a great impact on them.  Column G reflects the Oct #1.  As you can see they are a formula district.  Column H cells H19 to H52 can be used to change the enrollment numbers.  In this example I have chosen to show their enrollment going down by 200 students.  I did this because over the past two years Youngstown has lost an average of 200 students.  They lost 140 from FY14 to FY15 and 260 from FY15 to FY16.  I have also reduced enrollment by about 2% on other lines.  The effect of those changes show up in column J.  The funding differences show up below in J69 down to J132
You can use this to show your Supt, administrative team, board members or just make yourself crazy seeing what happens when you lose or gain students.  You can also use it to help determine how close you are to coming off the guarantee or cap.  It is not just the opportunity grant ($6,000 x state share index) but possibly special ed funding, targeted assistance, K-3 funding, etc that get effected and this is an easy way to show those audiences.

In Youngstown's case if they had enrollment changes as I have shown they will see a reduction of $2.3 million and be within $41,000 of the guarantee.  If they lose a like amount of students as they lost from FY15 to FY16 they will be on the guarantee. If they do not lose any students, they will receive what is on the Oct #1 and if they gain students they will gain revenue.

Please use carefully.  If you are having trouble with it or results are not what you expect please give me a call or e-mail your spreadsheet with your question.  If you choose to use it, I hope you find it useful.

Jack Pierson
Area Coordinator Region 7
614-403-3127
John.Pierson at education.ohio.gov<mailto:John.Pierson at education.ohio.gov>

Ohio Department of Education

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listserv.mveca.org/pipermail/mail_treas/attachments/20161017/27a76d66/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: FY17 Youngstown what if Oct #1.xlsx
Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Size: 1318960 bytes
Desc: FY17 Youngstown what if Oct #1.xlsx
URL: <http://listserv.mveca.org/pipermail/mail_treas/attachments/20161017/27a76d66/attachment.xlsx>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: FY17 state funding what if template.xlsx
Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Size: 1317006 bytes
Desc: FY17 state funding what if template.xlsx
URL: <http://listserv.mveca.org/pipermail/mail_treas/attachments/20161017/27a76d66/attachment-0001.xlsx>