Financial Model, Schools, Schools and Education

10-year Financial Projections, 5-year Financial Projections, Break-Even, Cash Flow Analysis, Cash Flow Projections, Cash-on-Cash Multiple, Cost Calculation, Cost Projections, Debt Schedule, Dividends, Excel, Financial Analysis, Financial Feasibility, Financial Model, Financial Modeling, Financial Planning, Financial Projections, Financial Ratios, Financial Reporting, Financial Statements, Financing, Forecasting, Income Statement, Investor Cash Flows, Investors, IRR (Internal Rate of Return), NPV (Net Present Value), Online Courses, Payback Period, Profitability Analysis, Property Valuation, Scenario Analysis, Sensitivity Analysis, Startup Financial Models, Uses and Sources of Funds

This financial model excel template uses the top-down approach to prepare the finance plan for a university and school. To establish a monthly 10-year Financial forecast, the main assumptions user should enter are the university’s regular school and online programs, the total number of students enrolled and online courses sold, the percentage per program, how much the university tuition and online courses fees, and the direct and operating costs the university school will incur. The model also allows the calculation of break-even and the main relevant financial ratios and financial metrics, such as university plant profit margin, IRR, and NPV, as required by investors and banks to understand the finance plan of a University and School.

Why do you need this Financial Model for your University and School?

To adapt to the new normal, universities and schools can take this opportunity to increase their revenue and profit by offering two types of programs:

• Regular Programs – Degree programs such as Undergraduate, Bachelor, Postgraduate, etc.
• Online Courses – Programs which are accessible anytime and anywhere.

This financial model template allows users to calculate, understand and compare which of the two programs would result in higher profit.

Other features that make this financial model unique are the following:

• Schedule of Programs – Enrollment of students under the regular university degree programs and the first sale of online courses may occur at any month of the year. This financial model allows users to plan this circumstance flexibly and realistically by selecting the first month of the “Start of Regular Courses” and “First Sale of Online Courses.”
• Payment Schedule – Online Courses revenue is simply calculated by multiplying the courses sold by the price per course. For presentation purposes, revenue is incurred immediately. As for the university tuition fees of the Regular Courses, payment may be made monthly, semi-annually, or annually.
• Deferred Revenue and Revenue Recognition – Changes in revenue and liability are reflected.
• Cost Allocation – General direct costs and operating expenses are allocated to the two programs depending on the cost percentage assumption.
• CAPEX – Starting up a University and School may take up to two years to be completed. Using the CAPEX schedule, users may allocate the amount of CAPEX spent by spreading their percentages through the current year up to the second.
• Buy or Rent Option – Choosing the build option enables the university and school business to recognize an initial CAPEX for the property, while the rent option leads to rent operating expense to be incurred monthly.

Deciding whether to invest or not in the startup of a university requires a well-prepared and executed finance plan. Developing financial projections can provide more clarity regarding the possible university profit margins, the funding required, and the expected returns. Using a solid financial model template can be the missing piece to bring the university and school business toward financial success.

University and School Spreadsheet Template Structure

This Excel template has instructions and formulas that are simple and easy to follow, which allows users to run simulations by varying the input assumptions. The elements of this financial model template are enumerated below:

Instructions and Terms contain the cell color codes, instructions guiding the user on how to work around the workbook, and the terms and abbreviations used.

Executive Summary includes charts to understand your University and School’s financial reports better, and to answer the questions –

• How much does it cost to start a university and school?
• What would result to more profit – Regular university school programs or Online programs?

This spreadsheet includes a 10-year summary of the following:

• Projected University Financial Statements overview
• EBITDA comparison of Regular and Online Courses
• University profit per Number of Regular Students
• University profit per Number of Online Students
• Financial Ratios Forecast
• Direct Costs Breakdown
• Monthly Plan Summary at chosen year
• Levered and Unlevered Free Cash Flows calculating key financial feasibility metrics such as the Internal Rate of Return (IRR), Payback Period, Net Present Value (NPV) and expected investment multiple
• Financial Metrics, and
• Uses and Sources of Funds

In the premium version, the Executive Summary contains results of the Break-Even Analysis at chosen year, the Shareholders’ Summary, and a Sensitivity Analysis for the expected Unlevered, Levered, or Shareholder’s IRR.

Assumptions that are necessary and applicable in the finance plan for a University Financial Model are presented in this worksheet.

• General Settings
  • Currency
  • Annual Working Days and Average days per month
  • Income Tax Rate
  • Net Working Capital
• Operational Assumptions
  • Starting month of Regular Courses
  • University Programs for Regular Courses
  • Number of Regular Courses Students per year
  • Regular Students Enrolled per University Program
  • University Tuition Fees for Regular Courses
  • Deferred Revenue Assumptions for University tuition fees
  • Regular Students Capacity
  • School Area Utilization
  • First month Sale of Online Courses
  • Number of Online Courses Students
  • Online Courses Purchased per student
  • University Online Courses Sold per Program
  • Online Courses Fees
  • Other Income
  • Price and Cost Inflation rates
  • Salaries and Wages of Full-Time Equivalent Staff
  • Direct Costs – Direct Salaries and Wages, Supplies Cost, Online Courses Cost and Others
  • Operating Expenses – Utilities, Other Expenses, and Indirect Salaries and Wages
  • School Building Option – Buy or Rent
  • Uses and Sources of Funds
  • Capital Expenditures, CAPEX Schedule and Maintenance per year
  • Depreciation Periods
  • Financial Assumptions such as Interest Rates, Bank Covenants, and Dividend Policy
  • Exit and Valuation assumptions such as the Investment Period, Discount Rate, and Exit Valuation Multiples

CAPEX (Capital Expenditures) sheet contains computations of total CAPEX based on the CAPEX Schedules assumption and annual maintenance percentage. This sheet also shows the Depreciation schedule of the Fixed Assets and a yearly summary of Gross Fixed Assets, Depreciation, and Net Fixed Assets.

Special features of this worksheet contain:

• Calculated Drawdown CAPEX, which is to be linked to the Debt Schedule, where it could be used as a suggested assumption in which funds are borrowed under the loan agreement.
• Maintenance CAPEX will only begin the year after the Initial CAPEX drawdown is at 100%

The Monthly worksheet contains a detailed calculation of the 10-year monthly assessment of the financial feasibility of the finance plan of a university and school.

This monthly report presents the operational forecasts of:

• Regular and Online Courses
• Other Income
• Revenue Breakdown
• Deferred Revenue
• Inflation Rates
• Salaries and Wages
• Direct Costs
• Operating Expenses
• Cost Allocation by Business Segment
• Monthly University Financial Statements – Income Statement, Balance Sheet, Cash Flow Statement
• Monthly Unlevered and Levered Free Cash Flow Statement

The Yearly worksheet contains a comprehensive and detailed summary of the 10-year monthly forecast of the University:

• Financial Statements – Income Statement, Balance Sheet, Cash Flow Statement
• Financial Ratios
• Standardized Income Statement
• Regular Courses Summary
• Online Courses Summary
• Projected Enterprise Value
• Unlevered and Levered Free Cash Flow
• Financial Metrics, and
• Equity Funding and Divided Policy Schedule

The Yearly worksheet also shows calculations of University and School Profit Margins such as Gross Profit, EBITDA, EBIT, EBT, and Net Income/Loss Margin.

The Debt Schedule is specially prepared for funding purposes to be presented to banks and investors. It contains financial ratios the banks and investors want to see when evaluating a project’s feasibility. The debt schedule comes with two debt facility options and adjustments for drawdowns and repayments.

Break-Even Analysis (only in Premium Version) shows the calculations of break-even at selected year at an EBITDA level of the following required:

• Regular Courses Student capacity
• Average University and School tuition fees
• Quantity of Online Courses to be sold and the
• Average Online Courses fees

Shareholders Cash Flow (only in Premium Version) is prepared to show the calculations of the Equity Multiple and Investors’ IRR, which helps determine if their funding and equity stake percentages would result to profit on their end. The user has the option to select which Cash Flow – Levered Cash Flow or Cash In/Out, will be used to compute investment and proceed.

Prepare your own Next University and School Financial Model now!

This University and School Excel spreadsheet template contains a great set of useful tools that allow users to understand better if university programs, both regular school programs, and online programs, are profitable by testing various scenarios and assessing how it affects your project’s viability. You can then decide whether to invest in a university and school based on the financial analysis and key metrics.

This financial model template comes pre-filled with an example forecast of a university and school finance plan. The forecast can easily be changed by updating the marked assumptions in blue or light blue.

The models, current version at 2.2, are available in two fully editable Excel versions:
• Premium Version (Excel file, fully editable) – 10-Year financial projection containing all features
• Basic Version (Excel file, fully editable) – 5-Year financial projection containing all features except for the Break-Even Analysis, Shareholder’s Cash Flow, and Sensitivity Analysis

Both versions are also available in PDF Demo Preview (which outlines the details of the model structure).

File types:
.xlsx (MS Excel)
.pdf (Adobe Acrobat Reader)