Building Financial Models with Microsoft® Excel®: A Guide for Business Professionals, Second Edition

Chapter 10. Sensitivity Analysis

After working through all of the steps in Chapters 2 through 9, I now have a financial model for Napavale that will generate consolidated projected financial statements (Balance Sheet, Income Statement, and Statement of Cash Flows) and free cash flows based on a series of assumptions and inputs (found in Napavale's Assumptions and Dashboard worksheet). Chapters 10 through 14 will cover ways in which I can evaluate and analyze Napavale as a business.

This chapter addresses an analytical technique known as "sensitivity analysis" in which I will evaluate the extent to which changes in assumptions and inputs affect various outputs of interest, such as revenues, net income, and free cash flows. In other words, a sensitivity analysis determines how sensitive an output variable is (or output variables are) to an input variable (or input variables). I will use the term "revenues" in place of "sales" (in dollars—not unit sales) throughout this chapter and Chapter 11 to avoid confusion. While revenues and sales may represent different meanings in some cases, I will use these terms interchangeably.

While there are a variety of ways to build sensitivity analyses in Microsoft Excel, one of the more efficient techniques is through the use of a tool known as a "data table." Data tables are well-suited to sensitivity analyses in that they enable the direct evaluation of how different values for an input variable would affect values for an output variable. To build ...