Dial a Forecast: A Speedy Way to Examine Multiple Business Scenarios
Business forecasts typically are based on historical information that is modeled by current conditions and anticipated futures. But to make the forecasting exercise more useful for planning, accountants can tinker further with the numbers by calculating how even an unanticipated future will change various metrics. This article will describe a fast and easy way to plug multiple alternative scenarios into a "what-if" calculation so managers can adjust their strategic and tactical decisions. Say we anticipate a 2% sales growth rate. This forecast will affect performance metrics such as return on sales and earnings per share. But what happens to those metrics if the growth rate is 5%, or -1%? In a typical spreadsheet we could simply plug in alternative assumptions. But that process is very cumbersome when you need to examine many different assumptions; keeping track of the variables can become quite a headache. We'll show how to use three Excel tools--spinners, scroll bars and conditional formatting--to make forecast calculations a breeze. If you wish to follow along, download the illustration file of XYZ Company's profit and loss statement at www.aicpa.org/download/pubs/jofa/dec2006/weisel_xyz_forecast.xls. THE RIGHT SPIN XYZ'S forecast is based on these assumptions: base quarter sales, quarterly sales growth rate, sales returns and allowances rate, cost of sales rate, marketing and promotion, and general and administration (exhibit 1, below). If you want to evaluate the effect of a 5% growth rate, say, all you have to do is overwrite the original estimate in cell B18 with 0.05. [ILLUSTRATION OMITTED] Each time you want to change the assumption, you have to adjust the number in that cell. A more efficient way is to add a spinner--an Excel tool that you can rotate like a dial to change a cell's value. By default, spinners can change values by increments of only 1 unit and within a range of 0 to 30,000. Since we want to use the spinner to adjust a percentage value, we must create an index key--a way of scaling what the spinner controls. Start by typing 100 in cell D22 (any blank cell will do). Then insert the formula =D29/1000 in B18 (the cell that displays quarterly sales growth rate) and format the cell to display percentage with one decimal place (see screenshot below). Now, to load the spinner right-click in any free area in your tool-bar to elicit this menu. Click on Forms to produce this menu, below left. Now hover your cursor over the Forms menu until you find the spinner icon (see screenshot below left). Click on it and your cursor icon changes from a pointer to a cross-hair. Move your cursor to C18 and click again to attach the spinner there (see screenshot at right). At this point, your worksheet will resemble exhibit 2, left. [ILLUSTRATION OMITTED] Finally, to link the spinner to the index key and define its characteristics, right-click on the spinner to bring up the Format Control dialog box and select the Control tab (exhibit 3, above). Type 100 in the Current value box, 0 in the Minimum value box, 900 in the Maximum value box and 10 in the Incremental change box. If the completed Format Control box resembles exhibit 3, the setup is correct. Click in the Cell link box, on D22 and on OK. This configuration allows us to dial in sales growth rates between 0% and 20% in 1% increments. [ILLUSTRATION OMITTED] Variations: If you wish to hide the index key (in this case the formula in cell D22), format the text in the cell to white. You also can adjust the control options to produce different levels of sensitivity See "Spinner and Scroll Bar Format Controls," below, for several choices. LET'S GO FOR A SCROLL Spinners work well for metrics formatted as percentages and other relatively small values. However, if you wish to control values over a broad range (say from $80,000 to $120,000), the scroll bar is easier to use. …