Model User Guide
Construction of the Model:
The model contains 10 tabs; each is briefly described below:
Input Tab - Users input their data and assumptions into this tab. The information entered here populates onto the Base School Model Tab.
Worksheet Tab - This tab contains worksheets to help users with the data needed for some sections in the Input Tab. This tab is optional, however, will guide a user in how to best populate the Input Tab
Dashboard Tab - This tab lays out the data for the four exemplar schools (shown in the Exemplar Tabs) into the base school model. Data such as fundraising per student or average administrative salaries may be interesting metrics to compare with the exemplars.
Base School Model Tab - This tab calculates the base year school financial results and projects results for the following three years based upon the data entered into the Input Tab.
Assumptions by State Tab - This tab is used to update the data based on the state that the User selects. This tab should not be deleted or changed and is shown for informational purposes.
Data for Dashboard Tab - This tab contains certain data that is populated into the Dashboard Tab based upon the state that the User’s school operates in. This tab should not be deleted or changed and is shown for informational purposes.
Exemplar Tabs - There are 4 Exemplar Tabs with the data of 4 actual schools. These are useful for reference as Users evaluate the data in their models.
More detailed instructions relative to the Input, Worksheets, Dashboard, and Base School Model Tabs are below:
Input and Worksheets Tabs:
The Input Tab requires the user to populate the key information to build the financial model. Cells in green are calculated or populated based on the selected State (see below) and cells in yellow should be manually completed. The Worksheet Tab contains a number of worksheets that can be used as a guide to populate some of the yellow cells on the input tab.
Below is further instruction for completing certain inputs and worksheets in their respective tabs. Some of the data inputs are more self-explanatory in the model and not covered below.
Operating State: Update the State where the school is primarily located. The State selected will populate salary, cost of living adjustments, teacher ratio information, and state-specific public funding available. This information will also populate the State-specific data in the Dashboard Tab.
Tuition and Aid Chart: This chart contains the school enrollment and tuition information by grade level. Worksheets 1 and 2 in the Worksheet Tab are useful to collate the data to populate the inputs in this chart.
The average financial aid per student is intended to show financial aid from sources other than public funding. Worksheet 2 is a tool for calculating the amounts to incorporate in L11-L15.
Annual Fundraising: This input should include all of the philanthropic revenue for the school. Worksheet 3 is a tool to assist the user in completing this cell.
Average Administrator Salary: This input should include all administrative staff. Worksheet 4 provides a tool to calculate this amount.
Cost for Non-Professional Staff: This input should include pay for facilities, cleaning, administrative support, and other non-professional staff at the school. Worksheet 5 provides a tool for calculating this line item.
Facilities and Admin Cost/Student: This input is intended to calculate the total costs of non-salary facilities and administration costs per student. Worksheet 6 provides a tool to calculate this input.
Capital Expenditures: This input is for capital expenditures. These expenditures could be deferred or capital maintenance (ie. replacing or rebuilding the old boiler or repaving the school parking lot) or could be program and structure investments (ie. science lab renovation or updating the lighting in the theater). Worksheet 7 provides a tool to calculate this input.
Growth Rate for Tuition and Similar: This input should be the rate at which you expect tuition and similar revenues to grow annually.
Growth Rate for State/Federal Funds: This input should be the rate at which you expect state or federal funds to grow annually. Generally, these rates will grow at more modest rates than tuition.
Growth Rate for Other Items (See Base Model): There are certain items in the Base Model where specific growth rates may make sense. The User should populate these in the blue cells in the Base School Model Tab.
The Assumptions Varied by State section is populated by the State that is selected at the top of the Input Tab.
Dashboard Tab:
The Dashboard Tab provides side-by-side data for the User’s school as well as the exemplars. The yellow boxes should be populated directly into this tab.
Base School Model:
The Base School Model Tab creates the base year financial results together with 3 years of forecasted results based upon the information input into the Input Tab and the growth assumptions in the Blue Boxes in column F of the Base School Model Tab.
Users need to look at the base year financial results and ensure that they make sense based on what the User knows about the school budget or forecast for that year. The base year model will not calculate exact results, but the results should be similar to the school’s actuals. For example, the teacher salary line will likely differ from the school’s forecast to the extent that the school’s average teacher salaries are different from the state average teacher salary.