Magazine article The CPA Journal

Forecasting: Using Algebra to Make More Efficient Spreadsheets

Magazine article The CPA Journal

Forecasting: Using Algebra to Make More Efficient Spreadsheets

Article excerpt

Computing budgeted or forecasted volumes for business planning has become relatively easy with the advent of spreadsheet programs. Unfortunately, the ease of using spreadsheet programs can cause users to ignore forecasting methods that may be more efficient than creating large spreadsheet models. Forecasting the growth of business volumes subject to both a population growth factor and an attrition factor is a common real-life example that can require an excessively large spreadsheet if not planned properly. The increased volume associated with the passage of time defines a mathematical series that is often simplified by using a few simple algebraic operations. Many reallife problems are solvable using series analysis methods. One such example is described below.

Case Study

A few years ago, a client asked the author for help to forecast patient visits for a development-stage outpatient clinic company. The client proposed several alternate growth models, including a simple growth and attrition scenario. Assuming a simple growth and attrition model, each clinic would attract 80 patient visits per month for five years, with a 5% attrition rate beginning with the second month. Thus, during the first month, 80 patient visits are assumed, the second-month visits are 80 + 80x .95, and the third-month visits are 80 + 80x .95 + 8Ox .95 ? .95. The obvious problem is how to forecast the patient visits by month with an economy of effort. One method is to use a spreadsheet model, which results in a lapsing schedule. For a 60-month forecast, this lapsing schedule spreadsheet requires more than 60 columns and lines. An abbreviated spreadsheet lapsing schedule showing only the first four months of the visits' forecast is shown in Exhibit I.

Exhibit 1 shows that patient visits can be calculated as the sum of a series involving the assumed number of patients visiting the clinic each month and an assumed attrition factor that increases by the power of (n - 1). For example, the sum (S) of the total visits for 60 months is equal to 80 + 80 (.95) + 80 (.952 ) + 80 (.953) + . . .+ 80 (.9559). While this format is descriptive of how the visits can be calculated, it does not simplify the calculation as much as one would like. In addition, the number of spreadsheet rows and columns increases dramatically for the required 60 months of data.

Exhibit 2 shows a Screenshot of the Excel formulas used to derive Exhibit 1. Columns A, B, and G are input and columns C, D, E, and F are output.

The spreadsheet, below can be simplified significantly by performing a few calculations, making use of nothing more than multiplication, subtraction, and division of a few factors learned in elementary algebra. For example, use the symbol (S) to represent the unknown value of the desired sum of visits at the end of 60 months. Next, write the series sum (S) as shown on line 1 of Exhibit 3 and multiply S by .95, as shown on line 2 of Exhibit 3, and then subtract this new sum from the original sum. After the few additional steps shown in Exhibit 3, a simplified model will result on line 6 that allows one to quickly compute patient visits by month.

Steps 1 through 5 above are the classic steps used to find the sum of a convergent infinite series. Mathematician Karl Friedrich Gauss (1777-1855) used a summing method to simplify the summing of a sequence of digits, providing the equation: Sum of digits = n(n + l)/2, familiar to accountants and used in the sum of the years' digits depreciation method and for the Rule of 78s interest recognition method. Further development of summing techniques lead to the classic method exemplified above, which is often useful to find the sum of a convergent infinite series. …

Search by... Author
Show... All Results Primary Sources Peer-reviewed

