Forecast Financial Data Using MS Excel 2016

With the New release of Excel 2016  Microsoft has provided handy way to do the forecasting using the user data. Within few clicks you will be able to do the forecasting without knowing the thorough knowledge about the Data Mining or Predictive Analytics. Just to have some domain knowledge about the data you are going to play around will be enough.

From this blog post I will be showing a sample data forecast analysis I've done using MS Excel 2016 Forecast Feature.

1. Created a Test Data Set for do the prediction. I used vTimeSeries  view in AdventureWorksDW2014 sample database. (This view returns the Time Series data Sales data which change over the time. This can be useful to do the Financial forecast on sales data)

2. In this view return the sales amount correspond to the reporting date. I've made a small change to the query in-order to improve the readability. I sort the data-set using reporting date.

3. Open Microsoft Excel 2016 and Create a New work sheet and go to the Data Tab.

4. Copy the Sales Amount data along with Reporting date in to the work sheet.(You can either copy or export to the Excel). Once you prepared the data-set in the Excel workbook the data would looks like here. 

5. Select the data-set and go to Forecast and select Forecast sheet button as below.

6. Once you clicked the Forecast button it will open a Forecast Worksheet wizard like below. You can select the cart type (either Line chart or Bar chat) and do the configuration using the option tab as you want.

In the line chat you would noticed there are two regions called Lower Confidence Bound, Upper Confidence Bound. This means under the confidence interval you have been given the chat will show the maximum and minimum sales amounts will be for particular dates.  

7. This is the bar chat for the given data-set. The confidence levels also have been shown using gray bars here.

 8. In the Option tab you can configure the way you want to do the prediction. It allows to give Forecast start, Confidence Interval, Seasonality , how to fill missing values ...etc.

9. Once you click OK button it will create a worksheet with the forecast Sales values and the chart.

You would you noticed , the forecast End default set by October of 2014(One year ahead). This because financial data analysis done usually for few quarters or short term ahead. Because, there may be many variables that could impact to change the business. So, as a best practice normally do not do the predictions for long period. But of course you can change the configurations if you want. 

For more on Time Series : http://www.itl.nist.gov/div898/handbook/pmc/section4/pmc4.htm