Statistical Analysis in Finance

This page addresses issues associated with data analysis for making budgets and for financial analysis and problems with the Excel FORECAST.ETS method and exponential smoothing. The page also includes spreadsheets that demonstrate how to make regressions with auto correlation adjustments and use techniques to make the analysis very flexible. In the first section I have also provided some ideas on budgeting and creating dashboards where you can work through multiple budget items and add illustrate the effects of changing budgets in a dynamic manner.

The first lesson set addresses budget issues that include making a dashboard, efficiently working with data using AVERAGEIF, INDIRECT-MATCH-INDEX and INTERPOLATE LOOKUP. The second lesson set discusses time series forecasting and in particular exponential smoothing that is used in the forecasting button provided with EXCEL 2016. The forecasting lesson set argues that you should make your own forecasts rather than using black boxes. The third lesson set is about regression analysis using country data that can be collected from the FRED database. It demonstrates how to put vast amounts of data that can be collected together in a structured manner that can then be used in statistical analysis and forecasting. The fourth lesson set demonstrates the crucial issue of AUTOCORRELATION in statistical data and the danger of applying simplistic regression.

Lesson Set 1: Dashboards, KPI, and Data Analysis in Budgeting

In the first lesson set I demonstrate a little trick that you can use to make an effective dashboard when working with multiple accounts. If you want to present alternative forecasting techniques including regression and time series analysis with many different accounts you may want to enter projected data directly from the dashboard. You may also want to include seasonal adjustments using different techniques. If you want to change the forecasting technique, the seasonal adjustment method or, most importantly, the amount of the budget, you can do this with spinner buttons. To do this requires some VBA attached to a spinner button. The VBA allows you to assign a number that depends on the account that you choose. You can do this with assigning a range name and then using the CELLS command.

The file below includes this spinner box technique and a number of other ideas that involve benchmarking to KPI’s, making presentations with bubble charts and with waterfall charts and hopefully some other ideas. Instead of using pivot tables which are very useful in budgeting, the LOOKUP function, the SUMIF function and the MATCH/INDEX/INDIRECT methods are demonstrated for budget analysis.

I have included a few videos that support the budget file below. The first video demonstrates how to construct the dashboard and add VBA to the spinner boxes as well as the other forms. The second set of videos walks through how to benchmark KPI’s using statistical analysis.

 

 

 

Lesson Set 2: Exponential Smoothing and Problems with Forecast Method in Excel

When you go to the internet and google forecasting in excel, you get a whole bunch of discussion of how you can press a couple of buttons and get some forecasts. A lot of the explanations also tell you how you can use the FORECAST.ETS function and retrieve statistics such as alpha and beta and the root mean squared stastic. These explanations also tell you how you can get confidence intervals from the FORECAST.ETS.CONFINT function. One website called real statistics does walk you through how to make your own forecast (and I used this). I originally worked on this whole forecasting business because of a question from a person in one of my classes who wanted to know how the confidence intervals are calculated.

But here is the problem, the ETS.FORECAST is a black box. None of the websites tells you in detail how to replicate the forecast and the confidence intervals in detail. This is a big problem because the forecast is made from exponential smoothing which is a very simple process. When I have tried to make my own model in the worksheet file below I have not been able to replicate the forecast parameters in excel. Maybe this is because I am just stupid (probably). The forecasts for different GDP per capita growth look good, but I think there is something very odd with the excel reported parameters from the forecasts. First, the forecasts often have a clear trend but the trend parameter — the beta — reported in the ETS.FORECAST.STATISTICS is .001. I really may be missing something here so I must be careful about my statements. Maybe they have incorrectly modelling the beta parameter, or the but I don’t think so. The really bad thing about this is that the beta parameter is by far the most important factor in making a forecast. Without the beta parameter the exponential smoothing forecast is a flat line.

The file below begins by demonstrating how the forecast works like all of the other websites (this is really no big deal). In seconds, you can make a time series forecast now in excel. You just select a series of data adjacent to a series of dates, and you get a series of forecasts along with a confidence interval. You can export the forecast to a separate sheet. The only question is what are the techniques that excel uses to do this. irst, just get some data. You can get the file for the economic variables and make forecasts for things like GDP per capita, population, life expectancy etc. The only thing you do is to select the date and then go to the DATA ribbon and then click on the forecast tab as shown below. This uses the FORECAST.ETS function.

Once you do this you can select the option to list the statistics. This is computed with the FORECAST.ETS.SEASONALITY function. An example of this output is shown below.

This is all quite boring. The real question is how are the forecasts made. To illustrate how the forecast is made using exponential smoothing, begin with a simple case without a trend. This forecast will be the same for each year of the forecast. The forecast is a weighted average of the current value and the prior forecast. The alpha is multiplied by the current value and the prior forecast is multiplied by 1-alpha as illustrated below. The illustration demonstrates that the forecast remains constant. The absolute error and the mean square error can be used to find the best value of alpha with a data table or with a macro.

It is more interesting to include a trend in the analysis. To do this you can include a beta factor. This factor is applied to the trend rather than the forecast. The trend is computed as the difference between the prior value without a trend and the current value without a trend. These are computed in a similar as the simple exponential smoothing. For me, the key is to have the correct number of columns. One column is for the exponential smooth without the trend. The next computes the trend. Then, finally the trend forecast can be computed.

The single smooth is the last forecast value without the trend plus the current vallue multiplied by the alpha as shown below.

It is more interesting to understand the trend in the analysis. To do this you can include a beta factor. This factor is applied to the trend rather than the forecast. The trend is computed as the difference between the prior value without a trend and the current value without a trend. These are computed in a similar as the simple exponential smoothing.

I demonstrate how the forecast is made for GDP per capita in many different countries in the video below. By pressing the spinner button you can see the forecast results and how the trends occur and how the variance of the forecast is affected by the variability in the historic prices and the size of the alpha and beta parameter. This is in the forecast page of the file below. In the next sheet of the file I show I have replicated the excel forecast and the alpha and beta parameters are completely different. I use either a goal seek or a solver technique to match the forecast. This replication results in very different parameters from excel and shows how the excel generated parameters do not make sense.

After showing the problems I demonstrate how exponential smoothing works. I first demonstrate the simple smoothing method and how to compute the mean absolute error, the mean square error and the root mean square error. Then I show you you can use a data table to find the alpha statistic the minimises the error. After working through the simple smoothing I move to smoothing with a trend. I think this is the most important. In this case you can make a two way data table or use the solver add in to compute the alpha and the beta. I demonstrate the effects of different alpha and beta parameters.

Finally, I have tried to replicate the forecast error. The forecast error uses some measure of standard deviation and multiplies the standard deviation by 1.96 to get a 95% confidence (2.5% of being below the forecast and 2.5% of being above the forecast). I have not been able to precisely replicate the forecast error and I cannot find any explanation as to how this is done. But you can look at the formulas that I have collected and see how the forecast error increases with higher value of alpha and beta.

 

Regression Analysis and Auto-correlation in Excel

You can probably find very good websites to perform regression analysis in excel. I believe that for economic variables it is important to understand when auto-correlation is present in the data. With auto-correlation, the variables move together, but the error term from the regression is higly correlated with the prior error, vilolating a fundamental necessity of a standard regeression. I don’t think there is an automatic way to adjust for autocorrelation, so I demonstrate how to make this adjustment in the files below. The regression analysis also makes extensive use of the OFFSET function so you can make all of the analysis very flexible.

Working with Country by Country Data Comparisons

Country by Country Data Comparisons