Financial Planning Models and Sensitivity Analysis

Learning Objective 4 Use computer-based financial planning models for sensitivity analysis

Financial planning models are mathematical representations of the relationships among operating activities, financing activities, and other factors that affect the master budget. Computer-based enterprise resource planning (ERP) systems store vast quantities of information about the materials, machines and equipment, labor, power, maintenance, and setups needed to produce different products. Budgeting tools within ERP systems simplify budgeting, reduce the need to re-input data, and reduce the time required to prepare budgets. Managers identify sales quantities for different products and the software quickly computes the budgeted costs for manufacturing these products. ERP systems also help managers budget for operating costs. Many service companies, such as banks, hospitals, airlines, and restaurants, also use ERP systems to manage their operations. The Concepts in Action: P.F. Chang’s and Internet-Based Budgeting is an example of a service company using a software platform to coordinate and manage its budgets across multiple restaurants.

As they prepare budgets, managers do not focus only on what they can achieve. They also identify the risks they face such as a potential decline in demand for the company’s products, the entry of a new competitor, or an increase in the prices of different inputs. Managers use sensitivity analysis to evaluate these risks. Sensitivity analysis is a “what-if” technique that examines how a result will change if the original predicted data are not achieved or if an underlying assumption changes.

To see how sensitivity analysis works, we consider two scenarios identified as possibly affecting Stylistic Furniture’s budget model for 2020. Either of the two scenarios could happen, but not both together.

SCENARIO 1: A 3% decrease in the selling price of the Casual table and a 3% decrease in the selling price of the Deluxe table.

SCENARIO 2: A 5% increase in the price per board foot of red oak and a 5% increase in the price per square foot of granite.

Exhibit 6-4 presents the budgeted operating income for the two scenarios.

In the case of Scenario 1, note that a change in the selling price per table affects revenues (Schedule 1) as well as variable marketing costs (sales commissions, Schedule 8). The Problem for Self-Study at the end of the chapter shows the revised schedules for Scenario 1. Similarly, a change in the price of direct materials affects the direct material usage budget (Schedule 3A), the unit cost of ending finished-goods inventory (Schedule 6A), the ending finished-goods inventories budget (Schedule 6B), and the cost of goods sold budget (Schedule 7).

Exhibit 6-4 shows that operating income decreases substantially if selling prices decrease by 3%, but declines much less if direct materials prices increase by 5%. The sensitivity analysis prompts Stylistic’s managers to put in place contingency plans. For example, if selling prices decline in 2020, Stylistic may need to reduce costs even more than planned. More generally, when the success or viability of a venture is highly dependent on attaining a certain income target, managers should frequently update their budgets as uncertainty is resolved. These updated budgets can help managers adjust expenditure levels as circumstances change.

Earlier in this chapter we described a rolling budget as a budget that is always available for a specified future period. Rolling budgets are constantly updated to reflect the latest cost and revenue information and make managers responsive to changing conditions and market needs.

Instructors and students who, at this point, want to explore the cash budget and the budgeted balance sheet for the Stylistic Furniture example can skip ahead to Appendix: The Cash Budget.