My NacuboWhy Join: Benefits of Membership

E-mail:   Password:   

 Remember Me? | Forgot password? | Need an online account?

Business Officer Magazine

Spread the Plan

Follow these tips for developing dynamic spreadsheet-based models and integrating them into your strategic planning process.

By Nathan Dickmeyer

Creating a strategic support model has become easier in many ways thanks to new technology. But the basic challenge remains: How do you develop an effective and dynamic model that will fulfill users’ needs? My work on the Stanford model provided me with invaluable experiences—I made mistakes and started over many times during that project. The design and implementation guidelines I adopted are still relevant today and can be useful in your work.

It’s important to distinguish between status quo projects, such as rolling budget details into the next fiscal year, and strategic plans, such as initiating a new program. Decision makers use strategic planning support models to build and test options while constructing budget frameworks. These models also give faculty, staff, trustees, agencies, and lenders a vehicle for providing strategic input and may give these audiences greater confidence in the managerial sophistication of the administration. Spreadsheet-based models are now favored over those written in esoteric languages because they are more open to inspection, are easier to share and maintain, and offer greater control. Such models are built by staff with broad operating responsibilities and are thus more or less “owned” by decision makers and support staff. Spreadsheet models tend to be evolutionary. They grow as new modules are added, linked, or fine-tuned. In some cases with staff turnover and model aging, these spreadsheets become encrusted and arcane, and transparency clouds. When this happens, it’s time for another upgrade.

Getting a Model in Gear

Are you ready to take your spreadsheets and planning process to the next level? Here’s how.

Identify your institution’s strategic options. Before initiating model design, understand your institution’s full set of strategic options so that you can translate them into financial parameters. Enrollment growth strategies, for example, require that the model respond to investments in new personnel and services (typically recruiting) with an anticipated return in new revenues and enrollment-sensitive expenditures. Other elements may require facilities construction and bond financing. Strategies for improved quality, which will at first appear vague, may need translation into higher faculty salary growth rates; lower average class size (which translates into faculty hiring); lower proportions of adjunct instruction (more faculty hiring); and investments in libraries, technology, and facilities. Some strategies include elements of austerity, investments in fundraising, or control of student financial aid levels.

Tip: Brainstorm to understand the broad range of possibilities. Your range of contacts may not sufficiently capture all strategic possibilities. After the model is constructed, someone might suggest something out of left field—considering the possibility of adding a school of nursing, for example. The model should be capable of simulating that option and demonstrating the high start-up costs of a new school. Spending long emergency nights getting the model to accommodate such an option accurately is less desirable than having the foresight to build in the capability ahead of time. A broadly based group, perhaps with the strategic planning team at its core, can help educate you about all the options. Brainstorming sessions in which participants are discouraged from making negative comments about new ideas can provide a good working list as preparation. A valuable and viable strategic model is one with all of the necessary structures in place, even if details cannot be set up at first.

Favor direct strategic response over complexity. If an approximation will do the trick, use it. Do not try to build a model with every department’s budget and every individual’s salary. The salary increase for the English department’s secretary is not a strategic decision; the planned rate of increase for all staff salaries is strategic. At small institutions it may be possible to carry each individual’s salary and link salary increases to a strategic growth variable. Except at the smallest institutions, however, spreadsheets can be overwhelmed with variables and the creator overwhelmed with upkeep at this level of detail.

Tip: Work with governance budget or planning committees at the level of strategy, not the level of budget detail. Coaxing the detailed budget into balance, following priorities, and financing strategic initiatives is the craft of a few technically skilled people. The role of most governance and ad hoc groups is to recommend strategies that will move the institution forward. Group members cannot be expected to venture far from political advocacy, but they can suggest directions and weigh the advantages and disadvantages of each. These planning groups are best assisted by projections with strategic levers, not micromanagement fine-tuning knobs. Institution leaders will listen more carefully when these planning groups present strategies, not resolutions on minutia.

Do not elaborate your submodels without a strategic reason. Many model developers find it tempting to try to model all subsystems perfectly. For subsystems that are not necessary for illustrating the impact of strategic options, keep it simple. Careful modeling of all investment pools, each with its own return rate and rules of reinvestment, is unnecessary if an institution has little dependence on its endowment and does not expect endowment support to swell. Creating a detailed investment submodel takes development time from more important subsystems. Given the uncertainty of the environment, simple approximations are often the most honest.

Tip: Try using a straight-line growth model first. Pay heed to institutional goals and plans. Careful modeling of the impact of enrollment change is not needed when an institution has dismissed growth as an acceptable alternative. Therefore, keep in mind what areas will have strategic impact. If the area is low in impact and does not figure in the testing of strategic alternatives, then greater accuracy does not add to decision making.

Define financial feasibility and infeasibility. If the members of the planning committee or the president’s cabinet do not share your understanding of what makes an alternative financially infeasible, little progress can be made in building a reasonable strategy. Few institutions can fully close the gap that stands between their budgets and financial equilibrium within the planning horizon. Thus, you will likely need to construct a compromise definition of feasibility. Generally, the definition falls between the highest possible standard and the lowest—“no worse off.” Your institution’s definition of financial feasibility might include budget balance, steady endowment spending power, minimum gains in salaries against inflation, and a minimum level of deferred maintenance reduction through the planning horizon. Financial projection models are ineffective when the campus community doesn’t understand why certain alternatives are financially unreasonable.

Tip: Put a music professor on the advisory committee. Music professors are quantitative and structured thinkers and carry the cachet of a liberal arts sensibility. They seem to grasp financial concepts like “movement toward financial equilibrium” quickly and can help convey these concepts and build trust.

Study and incorporate marginal costs and revenues. Every proposed change in future enrollments, tuition price, salaries, faculty numbers, and staff counts can have a complex marginal effect on revenues and expenditures. Additional students will mean more tuition revenue, but these extra students may also require financial aid and may increase academic support and student services costs. The institution may choose to maintain student-to-faculty ratios and levels of in-person services, and an increase in enrollment will put pressure on such costs. A good projection model will react to strategic changes with a full set of marginal revenue and cost increments.

Tip: Work with service area heads to determine the impact of strategic marginal changes. The marginal impact of one additional student is usually viewed as zero by a service area head. The possible impact of 300 additional students, however, is easier to gauge. The marginal cost is that “possible impact” divided by 300. Customer-service employees with multiple incumbents in a single title (e.g., clerks) are likely to require increased numbers in proportion to the enrollment increase. Single-title incumbents (e.g., library director) will not vary. Building a good understanding of marginal costs and revenues requires a research project, but the information is invaluable for decision making and modeling.

Try out model assumptions on real people. Models generally run on two kinds of assumptions: environmental and construction. How your institution will fare in the future depends to a certain extent on events wholly or partially out of your control, such as inflation, state support, giving rates, and investment returns. A hidden environmental assumption may turn out to be unreasonable and lead to loss of trust.

To keep it dynamic, build assumptions into the model. Marginal costs may be determined, for example, assuming no more than a 25 percent change in a particular factor, such as enrollments. Investment returns may be determined from historic averages without normal random variations.

Tip: Use models to explore worst-case scenarios. This will help you keep assumptions reasonable while preparing your institution for the volatile future. And remember, board members, business faculty, and economics faculty are good sounding boards for testing assumptions.

Design the model for mediated use. Models may be constructed to operate in one of three ways: closed, public, or mediated. Closed models are only run within the budget, finance, or planning offices and are less effective for a strategic options search than those where the results can be directly demonstrated. However, making a model hands-on or for public use can be challenging. In the early 1990s, Rice University and the University of Denver developed sophisticated models for public use. At both institutions, engineers built macros into the spreadsheet to produce custom dropdown menus for user-friendly manipulation. However, because planning is a sporadic activity, the cost of developing amateur-user-proof interfaces is difficult to justify. Most institutions build mediated models—those that can be publicly demonstrated by an expert user.

Tip: Build it for a good show. Mediated models that involve the audience are especially effective. When presenting the impact of strategies, an interactive model allows members to participate while watching the institution’s simulated financial fortunes shift.

Use graphics to dramatize the impact of options. Graphics help an audience quickly grasp the financial impact of strategic changes (see figure 1). Graph comprehension, however, diminishes as data elements are added. The best designs allow easy selection of five or fewer variables to graph at once. Surpluses/deficits, net assets, cash, investments, revenues, expenditures, enrollments, and financial assessment ratios should all be available for graphing.

figure 1

Graphics are useful for conveying the financial impact of strategic moves. Here, one variable was changed: Faculty salary growth was increased from 3 percent (left) to 8 percent (right). The resulting impact is easy to identify: A surplus of $10 million in 2010 goes to a deficit of $4.7 million and cash drops from $24 million in the bank to $10 million overdrawn in 2010.

Tip: Design the graphs so that users can control the scale of each variable. This will allow the same graph to display small things (like ratios) and big things (like investment levels) simultaneously. Learn to use the indirect spreadsheet function, which lets the user type in a reference and have it translated into a cell with the value of the referenced variable. The user can then easily select variables to be plotted.

Project balance-sheet items. Unless your institution has limited control over balance-sheet items (i.e., cash levels, investments, debt, and construction), the model will need to include projections that go beyond activities. Project financial position and cash flow to highlight strategies that could result in dangerously low cash levels for the institution. Balance-sheet items are essential to the calculation of most financial assessment ratios and are necessary for convincing lenders, agencies, and accrediting groups that the planning is sound. Boards gain confidence in a model when the statements that normally appear in a financial report can be examined in a financial projection (see Figure 2 Direct Statement of Cash Flows (Includes Actual, Budgeted and Projected Expenditures).

Tip: Project cash as a residual. Cash levels depend on every other item in the projection: revenues, collections, expenses, payables, bonds, and capital purchases. Unless policies or processes are to be changed, receivables can be calculated as a direct function of revenues. Likewise, payables can be calculated as a direct function of expenses. To capture cash response correctly, “sweep” rules (e.g., maximum cash balances before transfers to investments) and cash shortfall coverage rules (e.g., designation of investment funds to draw down during cash shortages) will need to be simulated.

Relate the model to displays that are already familiar to the board of trustees. You will be forced to backpedal if you demonstrate financial ideas in a format that the board has never seen. Getting trustees to comprehend any new framework takes time. If you use a familiar framework, you can avoid introducing new definitions and cross-referencing the new display to an old one.

Tip: Begin projected financial statements with the same lines, wording, and figures as those on the audited financial statements. This way you don’t introduce new terms and you don’t need a translation table from your format to the financial statements (see Figure 3 Statement of Financial Position Projection). Note: 2005 calculations match the audited financial statement

Teach people about financial subsystems. In strategic planning, the end product is less important than the process—bringing people together to share values, find direction, understand limitations, and develop forward-thinking approaches. This process requires basic trust, and trust is difficult to develop when rumors undermine the understanding of endowments, student financial aid, and bond financing.

Tip: Use every opportunity to educate, but never try to teach more than one concept at a time. Online newsletters with a “CFO Corner” and presentations to trustees are good opportunities to improve financial system understanding. Each lesson, however, must be limited to a single concept.

Portray choices in trade-off rather than values terms. Dialogues that focus on planning for the future often devolve into a series of single-value assertions: “We must increase funding for the library,” or, “You must not raise tuition.” Models can be useful tools for changing the language of the debate toward a strategy that optimizes benefits by establishing multiple variables through trade-offs. Different audiences will have different assumptions. For example, a financial person advocating a particular trade-off may be perceived as pressing for a value, like higher reserves for the university (a perceived CFO value). Meanwhile, from a student’s perspective, the notion that tuition growth can be traded off against faculty salary growth is often viewed as a preference for faculty salaries and as a lack of appreciation for the student situation. Planners can combat such false assumptions by attempting to retain all values in the model. Meanwhile, establish a primary mission: to teach all camps the financial leverage implied in each choice. Consider a graph that illustrates the following: a 4 percent tuition growth rate is required to support a 5 percent salary increase. To balance the values of multiple constituencies, the financial leverage associated with each of their prime values must be outlined.

Tip: Begin public presentations by demonstrating the financial leverage of each variable. Pick a single desired financial outcome configuration and show the amount of each leverage variable above a base state required to reach that configuration. Do this before working toward creative solutions involving compromises among multiple variables.

Start with principles, not solutions. People often champion particular solutions before problems have even been defined. Models can guide the search for solutions and should not be presented before the group has defined the principles that solutions must satisfy. Planning teams will find that it is much easier to develop compromises when it’s clear that all proposals are guided by a set of principles. A principle that insists that solutions be responsive to the needs of the region, for example, as part of a set of guiding principles, excludes strategies that ignore these needs. These strategies may have other merits and strong backers, but support dwindles when it is clear that the ideas fall outside the group’s principles.

Tip: Facilitate task force processes using flipchart management. Inevitably, when a group develops principles, a member will slip into the more concrete, “I think what we should do is…” A strong facilitator will not jump to the model to show that the idea is infeasible, but instead will thank the person for the idea, flip the flipchart ahead and write the idea down on a page “to be discussed later,” then flip back to the pages on principles.

Don’t cheat. It’s tempting to make a preferred solution look more attractive by adding a smidge to a formula or propping a factor up a bit. Remember, discerning experts will view the model. I was pleased when working with a lender to find that its analysts were capable of auditing formulas, not just of getting an overview of the model. Audiences have an uncanny ability to notice small aberrations. If you are found out, you will lose all credibility.

Tip: Find a tester to certify the model. Bank analysts were helpful to me because they found errors in formulas. Once the formulas were corrected, the analysts assured the lending decision makers that the model worked as our institution had indicated.

Don’t surprise. A surprising result too often signals an error somewhere in the development phase. If you forge ahead and present this new result to the board before the president has seen it, you risk presidential ire, even if the projection is correct. A surprising result can make the modeler feel clever, but that feeling is a trap. The new result must be carefully audited and then presented to backers before going public.

Tip: Clear any changes in a presentation with the boss. You may have to use the old, incorrect run again, particularly if you have not had time to work with other individuals who might be embarrassed by a new result.


Interested in learning more about strategic planning? Consider these sources:

  • The Strategic Attitude: Integrating Strategic Planning Into Daily University Worklife, by Nathan Dickmeyer (NACUBO, 2004)
  • NACUBO’s Integrated Planning and Budgeting program, May 7–9 in Denver. For details, visit

Don’t hesitate to send for help. A model is an invention and a wheel. Your invention is always within your level of comprehension and tailored to your needs. Nevertheless, others have already done the trial-and-error work and much of the testing that may be required to ensure the successful evolution of your model.

Tip: Turn to others for assistance. Outside expertise—including the experiences of other institutions—may help you avoid hours of spreadsheet training and eons of evolutionary time with existing models.

The Payoff

Models assist in increasing the sophistication of strategic planning, while at the same time, creative planning pushes the limits of each new model. Work to understand the dynamics that force strategic plans and models to become more robust in each of their iterations. Strategic planning is essential for budgeting for the unknown. By taking the time to develop and implement a sound model, you will benefit in the short- and long-term by establishing a dynamic tool that can be integrated into planning discussions for years to come.

Nathan Dickmeyer is founder, Dickmeyer Consulting, LLC, Upper Grandview, New York.