Financial Modeling
Support for strategic decisions
A financial model sounds more complicated than it is. As a simplified representation of the critical factors in a projected course of action, it can help an organization to explore, predict and control the financial repercussions of its decisions. It can be created with a pencil and a scrap of paper, or with spreadsheet software that takes into account complex interrelationships between expense and revenue lines. A financial model can offer not only comparison of key figures, but simple graphs for visual comparison. This more accessible view of the consequences of different options can bring all staff and trustees into the deliberation, not just those who are comfortable with tables of figures. For an example of a simple financial model, see the pdf version of this article.
Assumptions and variables change as a program or project unfolds. A model can provide a way to monitor the program or project over time, and to make informed decisions as critical milestones are reached. For example, projected surges in health insurance or other benefit costs, or potential changes in funding streams, make advance planning to find options for sustainability very important. Change orders in a construction project and seemingly innocent decisions made along the way in a new program can often leave an organization in an unintended and untenable position. Unforeseen eventualities can and will happen, but their consequences can be modeled before decisions are made
Some choices faced by nonprofit boards involve complex financial factors that make thorough examination and evaluation seem daunting. In discussions of adding, expanding, shrinking or dropping programs, adjusting fees, setting salaries and benefits, establishing endowment policies, or any other operating or capital budget issue, the full implications and interrelationships can be hard to envision and the possible scenarios too numerous to evaluate. These questions can be all the more difficult for trustees and staff who aren’t especially comfortable with financial issues or complex tables of numbers.
Financial modeling, a strategic and decision-support tool, can address these issues in a way that helps everyone to visualize and discuss issues. It can offer critical information about the long-term operating consequences of current decisions, and about the composite impact of alternative groupings of decisions.
Typical questions for financial modeling include:
- What are the likely net impacts of various combinations of program and service expansion—or contraction?
- What would the consequences be of different increases in salaries and benefits, combined with other costs and fees that are rising at different rates?
- How can a facility project be budgeted—including fundraising and associated cash flow, hard and soft costs, borrowing, inflation, contingencies, and maintenance endowment?
- How can an assessment of the ongoing effects of a new facility on the operating budget be combined with project costs to provide a comprehensive model for analysis?
Financial modeling can offer scenarios to help answer questions such as these, and make financial decisions more manageable, thoughtful, strategic and effective.
Simple Modeling
The simplest model is a single spreadsheet tracking relevant budget lines. By creating a series of spreadsheets showing different scenarios, an organization can see the effects of changes in assumptions.
The Simple Model in the pdf version of this article illustrates this with an example of the capital budget and fundraising issues involved in evaluating a facility project. For an all-inclusive assessment, a parallel model could be created for the operating budget issues (staff, program and facility costs and savings; changes in revenues).
Variables for a facility project might include:
- Amounts and timing of fundraising revenues
- Changes in operating expenses or revenue
- Project options, costs and timing
- Value of investments, returns and payout
- Amount, term, and interest rate of various forms of debt
Tracking and comparing the cumulative net impact using different figures for the variables will allow an organization to judge the overall financial implications, and thus the feasibility, of a set of decisions.
Complex Modeling
While a static spreadsheet comparison can be very useful, a dynamic model that responds in real time to changes in assumptions will allow staff and the board to explore options and consequences within the flow of discussion. This can turn meetings that would otherwise be lengthy, inconclusive and frustrating into crisp, focused negotiations of strategy and tactics.
When there are only a few variables, this kind of model is as easy to construct in spreadsheet software as a simple, static model. A more elaborate model will support consideration of a more complex set of interrelated issues (see the sidebar and the pdf version of this article), and therefore a more complete picture.
Increasing the number of students in a school, for example, will require various kinds of faculty and staff at certain thresholds, along with facilities and financial aid. New facilities typically involve added staff, equipment, utility costs, perhaps borrowing, and they may offer a variety of revenue streams, each with its own set of costs.
Every change to an assumption or variable will have consequences not only throughout the project, but also throughout the organization’s overall financial picture. Any and all line items can be incorporated into a financial model, along with fundraising projections, endowment earnings and payout, assumptions about inflation, borrowing costs, timelines, and options for including or excluding various possible new projects and programs, fully loaded with their individual effects on expense and revenue lines.
Using spreadsheet software to create a robust dynamic model, trustees and senior administrators can visualize the nature and extent of risks—and avoid costly, or even fatal, mistakes—by ensuring that all significant secondary effects are included in calculations. They can rest assured that they have examined the full impact of a new initiative and its potential impact on the ability of the organization to sustain existing programs.
