Operating Plans and Budgets

Nearly all organizations develop operating plans and budgets. These plans include a set of projections about future performance based on a set of assumptions. While the emphasis will vary based on the nature of the organization, the operating plan should include projections of sales, margins, expenses, profits, balance sheet accounts, and cash flow. We will discuss operating plans and budgets in detail in Chapter 13.

Forecasts/Business Outlooks

Most organizations prepare forecasts or “business outlooks,” which are essentially updates to the annual operating plans and budgets. These are an increasingly important part of business projections owing to the dynamic times in which we live and operate. Assumptions made in the annual operating plan will often have to be revised throughout the year. We will discuss forecasts and business outlooks in detail in Chapter 13.

Revenue Projections

Revenue projections are typically the most important and difficult‐to‐predict variables in developing a forecast or plan for an enterprise. Projections must reflect assumptions in the company's market and overall business environment as well as pricing, cost, and product introduction and life cycle. The illustrative model at the end of this chapter presents a simple case to project revenues over a multiyear horizon. Additional models and analysis addressing revenue are included in Chapter 15.

Cash Flow and Liquidity Projections

Most treasurers and CFOs must pay very close attention to cash flow, capital requirements, and liquidity. Often, the traditional financial statement approach is not the best method for short‐term (even weekly or daily) projections. Cash flow models focus on specific drivers of short‐term cash flow. We will explore these models in Chapter 17, Capital Management and Cash Flow: Working Capital.

Strategic Plans and Long‐Range Forecasts

A strategic plan should include a set of financial projections. These projections are a financial representation of the estimated results of executing the strategic plan. The projections are typically done at a higher level than annual plans or forecasts. Owing to the difficulty in projecting financial results over an extended period of time (typically three to seven years), a number of scenarios are often included in the plan. We will explore these models in Chapter 14, Long‐Term Projections.

Capital Investment Decisions

Capital investment decisions include any expenditure with a long‐term horizon such as purchases of capital equipment, new product development, and acquisitions of businesses or companies. These decisions often require a series of projections of both investment outlays and future cash inflows. We will explore these models in Chapters 20 and 21, on capital investment decisions.

Compensation and Incentive Plans

As part of the process to design and test compensation systems, it is essential to model potential outcomes. These models illustrate incentives earned under different scenarios and help to prevent inclusion of unintended consequences. You can rest assured that executives and managers will spend a great deal of time modeling potential payouts and scenarios for their personal compensation plans!

Valuing a Business

Most sound methodologies for developing an estimated value for a business require projecting future performance and cash flows. The best valuation models will include key performance drivers, projected income statements, and balance sheet and cash flow projections. We will explore these models in Chapter 22, Business Valuation and Value Drivers.

Mergers and Acquisitions

The ability to project the results and evaluate the investment characteristics of a merger or an acquisition is simply a combination and refinement of the business valuation model and the capital investment model. Additional metrics are included to focus on critical success factors and other unique aspects of these investment decisions. We will explore these models in Chapter 23, Analysis of Mergers and Acquisitions.

Define Objective

As a first step, analysts should step back and define the objective of a large modeling project. In many cases, the model is in response to a request from the CEO, CFO, or another operating executive. Taking a few minutes of time to define the objective and review with the internal client can save a great deal of time and rework later in the process. Defining the objective will also assist in designing the model and should address the following questions:

• Who is the client or user of the model?
• What answers (output) are we seeking?
• What are the key variables that will impact the analysis?
• What is the time horizon?
• How frequently will the model be used, and by whom?

Develop Architecture of Model

Many financial models are developed without a well‐thought‐out architecture. Pressed for time, the analyst sets up a spreadsheet or other tools without developing an overall framework for the model.

Preferably, the analyst, building on the stated objective, lays out the flow of the model, including required inputs, processing worksheets, and output summaries.

The architecture should include:

• Objective: What is the purpose of the model?
• Client: Who is the primary client/user of the report?
• Frequency: How often will the model be updated?
• Flow: In complex models, it is helpful to illustrate the flow of information in a model.
• Key assumptions: Identify, up front, the major assumptions and inputs required to use the model.
• Output and presentation: Based on the objective and identification of the client, develop the output summary.

Figure 4.1 is an example of the planning architecture for a long‐term revenue projection model. This can be included as the first worksheet in an Excel model.

Documentation

One of the most common problems with financial models is that they tend to have little, if any, documentation. This presents a problem if another analyst attempts to use the model. In fact, for models that are used infrequently, even the developer of the model may waste a lot of time refreshing his or her memory (I am speaking from personal experience here!). Taking the additional time to properly document the model will save both the developer and other users substantial time in using and modifying the model in the future.

The documentation can include key inputs required and the steps required to update the model.

Identify Input Areas

For many models, it is very helpful to identify or even segregate input fields from processing or output fields. If input areas are not identified, the user must hunt and peck around the model looking for input fields. In large models, the input values may be several steps away from the final summary. A simple but effective technique is to simply shade all input areas in the model. This allows any user to quickly identify cells that can (and those that should not) be changed. Alternatively, some model developers prefer to have all key inputs and assumptions in one section of the model.

Identify Key Assumptions and Drivers

Models typically require making many estimates and assumptions. Not all inputs to a model have equal importance. Key assumptions and drivers in the model should be identified and highlighted. These critical inputs should be identified, documented, tested, reviewed, and varied in sensitivity and scenario analysis.

Incorporate Historical/Actual Results

In most cases, it is essential to incorporate actual results into the model. This serves two purposes. First, the historical information validates the model by replicating the actual results. Second, the historical information provides a baseline to compare and evaluate the projections used in the model.

For models that project annual results, three to four years of history should be included. For quarterly analysis, eight quarters generally provide sufficient historical perspective. Monthly or weekly analysis should generally include at least the same periods from the previous year.

Protect Formulas

For models that will be used by several people, it is important to protect the cells with formulas to prevent inadvertent changes. For the record, I have often wished I had protected some of my own models from myself – from inadvertently keying over a formula.

Ownership and Buy‐In

Most models should not be viewed as a finance exercise or product of finance. While it is okay for finance to be the developer/facilitator, the inputs and major assumptions must be understood and agreed to by the appropriate managers. Generally, it is preferable to obtain key inputs from the operating manager responsible for achieving the projected results. For example, the sales forecast should be provided by the senior sales and marketing executive. If finance develops the major assumptions and inputs without buy‐in of the cognizant manager, finance often owns the projected results and reduces the accountability of the responsible manager.

Robust and Flexible

The model must be flexible so that changes to key assumptions can easily be made and reflected throughout the model. For example, changing interest rates, currency rates, or sales projections in one input field should ripple throughout the model, resulting in revised outcomes. Too often, models are fragmented and require multiple manual entries to effect a single change.

In addition, contrary to the wildest dreams of the analyst, most models are not one and done. Typically, the client will want to change assumptions or run additional scenarios.

Review for Accuracy and Reasonableness

There is no quicker way to destroy personal credibility than to produce reports and analysis that contain errors. The analyst and FP&A team should implement measures to identify and correct any errors. The following review techniques can reduce errors and improve the overall quality of the analysis:

• Independent Review. It can be very difficult to effectively review an analysis that we have personally prepared. Where possible, the analysis should be reviewed by an analyst or a manager who was not directly involved in the process.
• Review of Key Inputs, Assumptions, and Flow of Analysis. The reviewer should perform a mini‐audit of the model, to “tick and tie” key numbers to ensure they flow through the analysis, from detail worksheets to summaries.
• Big Picture/Client Perspective. Review the analysis through the lens of the client. Does it address the objective and issue at hand? Is the presentation clear? Are the results of the analysis/model sensible? In too many cases, the answer (output) is nonsensical because of some minor error in the model; this could easily be detected (and most assuredly will be discovered by the client!). What additional questions does the analysis raise? What actions should be taken or recommended?

Sensitivity and Scenario Analysis

The model should also facilitate making changes to critical assumptions to create various scenarios and perform sensitivity analysis. The initial output of the model may be described as the “base” projection. Other versions of the model should be summarized and presented to the client since they add tremendous value in understanding the impact of assumptions and the overall dynamics of the situation or decision.

A sensitivity analysis will flex one or more sensitive assumptions. For example, what is the impact on total revenues if unit volumes are 10% under the base forecast? What is the result if unit volumes are 10% above?

A scenario analysis will attempt to address what‐ifs. For example, what if a new competitor successfully introduces a product that competes directly with one of our key products? This scenario will likely result in reduced average sales prices and unit sales for this product. A model could easily accommodate estimating and presenting this and other scenarios. For additional examples of scenario analysis, refer to Chapter 21, Capital Investment Decisions: Advanced Topics.

Output and Presentation Summary

Nearly all analysis, spreadsheets, and models make poor presentation documents. Completing the analysis or model is just half of the job. Developing an effective way to summarize and present the results of the model is as important as the model itself.

For complex models, it is best to incorporate an output or presentation summary. By integrating the presentation summary into the model, it is updated any time the model is run or changed. I prefer a one‐page summary highlighting key assumptions, a graphic presentation of results, and a sensitivity and scenario analysis. This summary should be the lead presentation summary, with most spreadsheets and tables as supporting schedules. The model, or even one output from the model, represents a small part of the potential value of a model. The real value is in increasing the understanding of the dynamics of a particular investment, projection, or decision and communicating this to the pertinent managers. We cover this topic in greater detail in Chapter 6, Communicating and Presenting Financial Information.

Establish a Portfolio of Models

In most organizations, the models are often stored by the individual who developed or uses the model. Many organizations have found it useful to develop an index of models and store them in a shared drive or cloud. This will serve to eliminate duplication of similar models. It will also encourage the sharing of best practices and techniques across the organization. A partial list of the models included in this book (Table 4.1) highlights the usefulness of this practice. More on building the analytical capability of the organization is presented in Chapter 5.