A methodical approach to budget

For many accountants, next year's budget looks a lot like last year's actual amounts, adjusted for inflation and rounded to nearest hundreds, thousands, or millions. It's hard to argue against this budgeting approach. After all, no matter which budgeting method you use, it's still educated guesswork. However, for accountants who prefer to throw a few more neurons at this problem, regression-based budgeting may be a good option to consider, at least for some budget line items.

Before diving into the details, let's take a quick look at how Excel's regression tools work. In Image 1, I have exported four years' worth of QuickBooks income statements (from 2014 to 2017) into Excel. To produce a budget (or at least a starting point for a budget) for 2018, select columns B through E and drag the Fill Handle one column to the right to produce the 2018 budget in column F.

Image 1: Regression example using QuickBooks data

With this simple manoeuvre, Excel generates the values in column F using regression calculations based on each row's previous four years of actual data. You can see this simple example demonstrated at the beginning of my video (which demonstrates concepts described in this article). You can also download an Excel workbook containing the data used in this article at carltoncollins.com/regression.xlsx. Upon seeing the Fill Handle in action for the first time, accountants tend to ask, "Exactly how does it work?" The answer is that when you highlight three or more values in Excel and then drag the range's Fill Handle, Excel automatically calculates the next value(s) using linear-regression analysis, the least-squares method. Let's study this process closer by isolating the Sales line item from the example shown above. As suggested in Image 2, dragging the Fill Handle for the range of actual data (shown in blue) produces projected values (shown in red).

Image 2: Simple regression example

These regression calculations can be demonstrated visually using Excel's trendline chart. To do this, highlight the actual data cells (cells B7 through E7 in this example) and create a scatter chart by selecting Insert, Insert Scatter (X,Y) or Bubble Chart, Scatter, as pictured in blue data points in Image 3. Next, right-click any one of the blue data points, and from the resulting pop-up menu, select Add Trendline (the blue dotted line in Image 3). This resulting blue trendline represents the one true linear line that dissects the data points in such a manner that when all the distances from each data point to the trend line are squared and added together, they produce the least amount (hence, the least-squares method). To depict the process further, I have extended the linear line and added data points at intervals equivalent to the actual data intervals (as illustrated by the red dotted line, data points, and interval arrows in Image 3). The red data points correspond to the future values calculated in cells F7 through H7 in Image 2. This scatter chart and trendline visually suggest how Excel's Fill Handle tool works — and how the actual historical data are used to project the future data points.

Image 3: Excel scatter chart and trendline

To prove the Fill Handle's accuracy, these same projected numbers can also be calculated mathematically using Excel's SLOPE and INTERCEPT functions, as follows. Starting with the same sales numbers used above, we replaced the date labels in cells B5 through F5 with the numbers 1 through 4 (because these numbered data points are essential for the SLOPE and INTERCEPT formulas to work properly). In cells F8 and F9, we entered the formulas =SLOPE(B6:E6,B5:E5) and =INTERCEPT(B6:E6,B5:E5), respectively. Then, in cell F10, we calculated the projected value for period 5 by adding the Slope (cell F8) and the Intercept (cell F9) multiplied by 5 (cell F5) to derive the fifth period amount. The resulting formula =F9+F8*F5 produces the product 1,176,995, which matches the product produced by the Fill Handle method in cell F7 of Image 2.

For further proof of the Fill Handle's accuracy, we can also write the formula =FORECAST(F5,B6:E6,B5:E5) to produce the same projected 1,176,955 value, based on the same data used above. These two SLOPE/INTERCEPT and FORECAST function calculations derive the same answer as does Excel's Fill Handle, which proves the Fill Handle's accuracy, so you can have confidence in using this tool to create your own budget data.

DETAILED BUDGET EXAMPLE USING REGRESSION

Now that we've covered a basic understanding of regression using the Fill Handle tool, let's put this tool in action using the detailed sample data included in Microsoft's Dynamics GP accounting system. For this example, the budgeting process is broken into eight steps, as follows.

1. Export data to Excel. Our regression-based budgeting process starts by exporting four years' worth of monthly income statement data from Dynamics GP to Microsoft Excel (something virtually every accounting system allows users to do), as suggested in Image 4. (You might notice that Dynamics GP creates income statements in which the credit balances, such as revenue line items, are displayed as negative numbers, which is not typically how accountants display those amounts. We will compensate for this minor formatting issue by adjusting some of our later calculations accordingly.)

Image 4: Exporting income statements from Dynamics GP to Excel

2. Use regression to create the initial budget. To create the initial 12-column budget, we select the 48 data columns (columns E through AZ, or January 2014 through December 2017 in this example), and then left-click and drag the Fill Handle (located in the upper-right corner of the selection range) to the right 12 additional columns, as suggested in Image 5.

The result is that Excel uses linear-regression analysis on a line-by-line basis to project the 12 monthly future values for each row. (Keep in mind that this is just a starting point. More work is needed to further refine this initial budget projection.)

Image 5: The initial regression-based projections

3. Seasonalise the budget projections. As illustrated in the trendline chart shown in Image 3, this linear-regression method produces results that follow a straight-line path, resulting in no data seasonality. Because most companies experience seasonal variations in revenue and expense line items, the budget projections should therefore be seasonalised by summing the monthly projected 2018 budget values and redistributing them using 2017's seasonality. For example, if the supplies expenses for January, February, and March of 2017 represent 5.2%, 6.8%, and 9.4%, respectively, of total supplies for 2017, then those same ratios would be used to distribute the 2018 total supplies budget to January, February, and March of 2018 (and so on for each month).

Hint: It is essential to seasonalise your budget to produce meaningful monthly actual-to-budget comparison results; simply dividing an annual budget by 12 to produce monthly budget amounts would likely result in monthly actual-to-budget comparisons of little to no use to companies with revenue and expenses that fluctuate each month.

4. Insert known values, where applicable. For certain line items, future amounts are known values based on executed contracts, and therefore they are easy to project. For example, companies that rent office space typically know precisely how much their rent will cost based on their lease agreements. In this case, it makes sense to replace the regression-based budget projections (which are educated estimates) with the more accurate contract values. Other examples of common contract expenses include insurance expenses, employee salaries, internet fees, website hosting fees, and leased trucks and automobiles. In addition, a company's fixed-asset depreciation schedule is likely to produce more accurate depreciation expense amounts for budgeting purposes than would the regression method. At this juncture, any contract values (or known values) should be inserted over the regression-based values. Further, those updated line items should be documented using an embedded comment and perhaps even different background colour highlights to make it easy for reviewers and readers to understand each line item's origins.

Hint: Using the Split or Freeze Panes tools on the View tab enables you to view the account descriptions on the left side of a large workbook, along with the data columns to the far right side of the workbook.

5. Test line item data for regression suitability. For regression to produce meaningful results, the underlying data must follow a mathematical trend; data that do not follow a trend will most likely produce unreliable projected values. To help you calculate the suitability of data for regression purposes, Excel provides the PEARSON function. (This function is named after mathematician Karl Pearson, who developed systems for measuring the amount of skew in a data set.) To illustrate a simple example using the Pearson function, Image 6 depicts the Pearson scores for three sets of data.

Image 6: Simple Pearson example

Our next step is to highlight the Pearson scores and apply conditional formatting to make the subpar scores easier to identify, as follows. Start by selecting the Pearson scores in column BZ, and then from the Home tab, select Conditional Formatting, Highlight Cell Rules, Less Than, enter .60 (or 60%) in the Format cells that are LESS THAN box, and press OK. As a result, those rows of data with inadequate Pearson scores are highlighted in translucent red (or pink, you could say) in Image 7.

6. Replace budget line item amounts that have inadequate Pearson scores. Once the Pearson scores have been calculated for each line item, you should replace the regression-based projections for those line items with subpar Pearson scores (less than 60% in this example) and insert new values using an alternative projection method, such as using an appropriate inflation index.

In addition, it is wise to document and colour-code this alternative projection method. We then repeat this process for each line item as necessary and, upon completion, we have our initial 2018 rounded, seasonalised budget based on a combination of regression, inflation, and contract-pricing projection methods. Note that at this juncture you should expect your budget projections to be based on a variety of projection methods. For example, your 200-line-item expense budget might end up consisting of 30 line items based on contract values, 40 line items based on inflated 2017 values, and the remaining 130 line items based on regression calculations.

7. Forecast balance sheet. No budget would be complete without including a cash flow forecast. To project cash flow for 2018, we would need to forecast the 2018 balance sheet line items. Many balance sheet line items are relatively easy to project. As examples, land is usually a fixed amount, property and equipment values can be easily projected based on depreciation schedules, and loans payable amounts can be easily projected based on amortisation schedules. However, forecasting other line items, such as accounts receivable, inventory, and accounts payable balances, is trickier, though it can be done using historical financial statement ratios, as follows.

Let's assume that we have calculated the average number of days in accounts receivable, inventory, and accounts payable from our 2017 actual financials as 42, 62, and 28 days, respectively. Let's further assume that these amounts remain consistent enough throughout the year for us to rely on them for projection purposes. In this case, we can then use our 2018 budgeted income statement, along with these number-of-days ratio calculations to project balance sheet line items, as follows:

a. Accounts receivable. To project accounts receivable balances for 2018, we divide total budgeted net sales for January 2018 ($2,006,400 in this example) by 365 days, and multiply by 42 days to derive $230,873.42. Next, we apply rounding to produce a January 2018 accounts receivable budget value of $230,900 in this example. (Because Dynamics GP displays the sales amounts as negative values, we must adjust our formula by multiplying the product by —1 to produce a positive value.) We then copy this formula across to calculate the budgeted accounts receivable values for each month in 2018, as shown in Image 8.

Image 8: Balance sheet projections

b. Inventory. To project inventory balances for 2018, we divide total budgeted net cost of goods sold for January ($288,300 in this example) by 365 days, and multiply by 62 days to derive $48,971.51. Next, we apply rounding to produce a January 2018 inventory budget value of $49,000 and then copy this formula across to calculate the budgeted inventory values for each month in 2018, as shown in Image 8.

c. Accounts payable. To project accounts payable for 2018, we divide total budgeted net expenses for those January line items typically paid on account ($1,483,150 in this example) by 365 days, and multiply by 28 days to derive $113,776. (For consistency, make sure the line items selected are the same line items used to calculate the days in payables.) Next, we apply rounding to produce a January 2018 accounts payable budget value of $113,800 and then copy this formula across to calculate the budgeted accounts payable values for each month in 2018, as pictured in Image 8.

Next, we populate the remaining budgeted balance sheet line items, and then calculate the change in each balance sheet line item for each month. Finally, we can calculate January 2018's projected ending cash balance, as follows. Starting with January 2018's beginning cash balance ($234,600 in this example), we add net income/(loss) (—$77,600 in this example), add back depreciation and amortisation ($35,400 in this example), and add or subtract the month-to-month changes in the applicable balance sheet line items (a net amount of —$34,700 in this example) to project an ending cash balance of $157,700 for January 2018. This $157,700 ending cash balance becomes the beginning cash balance for February, and the calculations are repeated for each additional month to create ending cash balances for each month in 2018, as shown in Image 8.

At this point, we have created an initial preliminary budget for 2018. While this may seem to be a completed budget to you, to management it's merely a starting point that most certainly requires further review and refinements before finalising. For example, management may want to add new line items for purchasing new furnishings, equipment, or a new accounting system. The bosses may also want to expand or tighten the marketing budget, increase sales expectations, or implement an employee training initiative. In other words, further refinements should be expected.

8. Use the budget. There is little point in going through the time and effort to create a budget if you're not going to follow it or periodically compare it to actual results. To properly utilise a budget, management should:

• Consult the budget. Refer to the budget throughout the year when approving significant expenditures, and ensure that spending does not exceed budgeted amounts, unless the president, board, or management approves such overages.
• Compare budget values to actual values. This should be done on a monthly basis. Specifically, income statement and balance sheet line items should be compared to budgeted values, cash balances should be compared to cash flow forecasts, and key financial ratios should be compared to expected financial ratios.
• Flag significant deviations from the budget. For example, management should flag those items that vary by more than 10%, or by more than $10,000, for further scrutiny (or some other threshold that you deem appropriate). You should analyse those significant deviations in detail and document reasons for those variances.
• Take corrective measures, as necessary. In case errors are discovered, management should enter adjusting journal entries to correct those errors or communicate with vendors to resolve duplicate bills or payments. If fraud is detected, management should take steps to identify and possibly remove the fraudsters, recover monies, and prevent future frauds. If significant variances are found to be caused by flawed budget projections, then management should learn from the past budgeting mistakes and employ more reliable methods for projecting those same line items when preparing future budgets.

Regardless of which budgeting methods you use, even the best budgets are just educated estimates derived from the best available information at hand, with experience and reason applied. Despite this "iffy" nature of the budgeting process, most accountants believe it's better to embrace budgeting because budgets represent more than mere restrictions on spending; well-prepared budgets can serve a much greater purpose. Proper budgets represent thoughtful plans of action to help guide organisations toward their financial goals. Budgets also represent the first line of defence against fraud, because it's virtually impossible to perpetrate a fraud for long periods when the proper budgeting and review process is employed (short of massive collusion). Hopefully, this insight into Excel's built-in regression tools will help you tackle your next budget more efficiently.

Tip: The RSQUARE function

Excel offers the RSQUARE function (RSQ), which is an updated version of the PEARSON function that uses squared correlation coefficients rather than average correlation coefficients; some accountants prefer to use the RSQUARE function because they believe it produces a more reliable score than does the PEARSON function. My advice is to consider using both scores to judge the suitability of your data for regression.

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant and continuing professional education instructor in the US. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.