The following relate to financial forecasting. For a complete list of all articles on this web site please go to the Article Index page.
More Accurate Financial Forecasting
By Jeff Adams October 10, 2013
One key element of an accurate financial forecasting process that is often overlooked is persistency. Developing an accurate financial forecast is a year-round project with a substantial amount of planning, monitoring, research, and adjustment. The process should be set up to allow for frequent analyses of variances from the forecast and the underlying reasons for these variances should be determined and the underlying processes should be adjusted to mitigate these variances in future forecasts. Assumptions in the forecast should be consistent with, and may be even used as targets for, those used in other areas of the company.
For simplicity, the process below will be discussed from the perspective of an insurer or an HMO. The forecasting process for other entities, such as providers and employers, would be similar in concept, although some modifications would be necessary.
Basic Format of the Forecast Model
The presentation output for the financial forecast is often predetermined by the executives or other persons to whom the forecast will be presented. However, ancillary output and the methodology used in obtaining output is generally under the control of the individuals creating the forecast model. The design of the model, including input and ancillary output, are key to providing the flexibility necessary to perform adequate monitoring and to analyze variances from the forecast.
Substantial research needs to be done before the forecast model is designed. The developers need to fully understand the presentation output. They also need to review monthly, quarterly, and annual financial information that is produced by the company, such as monthly financial statements, quarterly financial statement submissions, and annual premium rate submissions. These monthly, quarterly, and annual reports should be used to monitor progress of forecasts produced. As such, the forecast model should be designed with this in mind, creating information, possibly in the form of ancillary output, that is easy to compare with these periodic reports. This may require expansion of the forecast model to produce these ancillary reports. An example is the use of seasonality and Days factors to break down the annual forecast by month of the year. By doing this the forecast can be more accurately measured against monthly financial statements. Simply dividing the annual forecast by 12 will produce inaccurate monthly results and make monitoring during the year difficult.
Assumptions, such as trend, should be consistent with those used in other reporting or calculations. The forecast can even be used as a benchmark or target for such assumptions. For example, individuals responsible for producing the forecast should meet with those responsible for achieving membership targets for the company and agree on membership counts to input into the forecast. The forecasted trend can use a unit cost trend that would serve as a target for those responsible to contract with providers. The trend should also be consistent with the trend used in pricing and underwriting. The forecast model should allow the user to input these assumptions in a manner n which these assumptions can be monitored.
Once the presentation output, ancillary output, and input is determined, then design of the model may begin. Presentation output should be separate from the rest of the model. For example, in Excel they may be in a separate worksheet or worksheets in the same file. Likewise, the ancillary output should be separate and the input should be separate. Once these are designed then the calculations, or guts, of the model can be begun. No calculation formulae should include any hard-coded number, all numbers should be in separate cells. Remember that this model will change often in future years so it must be designed with that in mind. Hard-coded numbers in cells are errors waiting to happen and these errs may be difficult to detect and fix.
In setting up the calculations in the model, there will probably be additional information that model designers realize will need to be added to the input or assumptions. This is to be expected. This may also require additional studies, such as the seasonality or Days study. The model may tend to be very large. As such, it may be helpful to develop formulas or worksheets that are easily copied and pasted to other cells or worksheets. This will not only save time in the initial development stage but when changes are made in the future. Balancing totals should also be calculated in the model to verify auditability of the results and underlying data.
Inputting Data Into the Model
Data to be input into the model should be consistent with data for other financial reporting used within the corporation. This is important so that all parties are being consistent with monitoring protocol. For example, if membership is obtained from a source different than the one used in the forecast, unexplainable variances may occur and reconciliation may be very difficult. Those responsible for membership targets may also not take responsibility for variances as their membership numbers may produce inconsistent results versus the forecast. Data need not be obtained from exactly the same source so long as the two sources are consistent and will be consistent in the future.
There should be minimal "other" or "miscellaneous" amounts in the data. Often "other" amounts can be set up in a more descriptive category allowing for more precise review. Substantial amounts in the "other" category can hide issues that may be developing as, by definition, the contents of an "other" category are unknown.
The process of gathering data should be automated since the forecast should be updated on a monthly basis to determine variations from the initial forecast.
The frequency of changing may depend on the type of assumption but it would not do any harm to revisit each assumption on a monthly basis. The model designers will not only need to meet with others from within the corporation to determine reasonable assumptions but also develop processes to allow for updating these assumptions when and if they change. For example, the membership assumption may change at a different frequency than the unit cost or fee schedule assumption. It is possible that in some situations membership assumptions may only change once a year and that the unit cost or fee schedule assumption changes monthly. In a situation where an assumption might change monthly, the process should assume that it will change monthly. That might mean getting a memo each month with the assumption change or verbiage indicating that the assumptions will not change that month. That way there will be no lack of communication. Forecast assumptions should never be made in a vacuum. This will lead to forecast inaccuracies and lack of other party participation in analysis of variances from forecast when they occur.
Upon Completion of the Initial Forecast
Once results are obtained for the initial forecast, all of the output, input, and assumptions should be reviewed for reasonableness. This may include review of some portions of the forecast by other parties in order to get buy-in to the process. Modifications may need to be made but the model designers need to be comfortable that proposed changes are reasonable. For example, changing an expected trend from 10% to 8% will not automatically lower actual observed costs in the future. If the model designers are not comfortable with the 8% trend then they should seek additional documentation before changing the trend.
The ancillary output of the forecast should be compared with the other financial documents to which they will be compared on an ongoing basis for consistency. For example, a forecast showing a 3% gain for the second half of the year even though there was a 3% loss in the first half may not be reasonable, although there may be situations in which it is.
Only when the forecast process and initial results have been reviewed is the forecast ready to be released.
Future Analysis and Forecast Adjustments
Analysis of variations of the forecast to future financial information is just as important in accurate forecasting as production of the initial forecast. The forecast allows for leadership to make important decisions based on projected future financial information. Analysis of variances from forecast to actual financial information allows for identification of developing issues that need attention and possible corporate action to resolve. For example, substantial excesses in observed trend over forecasted trend in a specific category may signify some type of "leakage" within that category. The analysis of variances should not be looked at as trying to create excuses for missing the forecast but as trying to find issues that need to be resolved, either via forecast process modifications or an attempt to adjust some corporate process that is causing the underlying issue.
Even if an initial budget is used as "The Forecast" or "The Budget" for the year, it is important to do frequent updates to monitor the progress of the corporation and serve as an early warning system. Additionally, the process of completing the forecast may change as variances are researched as this research may show weaknesses in the current forecast model. By following this approach your model should get stronger and more accurate each year and corporate results will improve as issues are recognized and resolved earlier.
After two years in this process, forecasts should be accurate to within 1% of premium, although there may be extenuating circumstances that cause these forecasts to be less accurate in a given year, such as in 2014 with health care reform. Since positive surprises are better than negative surprises and since it seems that most surprises are negative, the designer of the forecast may wish to include a little margin or cushion in the forecast.
More accurate forecasting and other-party buy-in to the forecasting process will allow for more accurate forecasting and also resolution of variances on a quicker basis, resulting in better company financial results.