# predictive analytics excel template

and your sales team can accurately predict your total revenues in the next quarter. and finally, when the quarter is over, you compare how good your sales forecast was. by comparing your team’s estimations and the actual demand, you get an idea about how good your sales forecast is. you got the total sales of the quarter wholly right. let’s review each point in turn and see how you can implement a simple predictive analytics model in excel to estimate sales figures without rocket science. a good model could be the one the most accurately and consistently delivers your future sales revenues or the one that costs you the less to implement.

you get the misleading notion that your process is working, from a horrible granular forecast. furthermore, continuing with the example above, we will add the real sales per product, and we will assume that you got the total sales of the quarter right. and she right most of the time. it is called “persistence”; the hypothesis that the weather will be the same tomorrow as it was today. the first column is the sales team’s total forecast, followed by the previous quarter’s sales and a long-term sales average. using the previous quarter and a long-term average, you can create an excel sales forecast without rocket science. remember to assess the cost benefits of your model as well.

this is followed by an incredulous look when i demonstrate how we can leverage the flexible nature of excel to build predictive models for our data science and analytics projects. now you must be wondering how in the world will they build a complex statistical model that can predict these things? in this article, i am going to explain how to build a linear regression model in excel and how to analyze the result so that you can become a superstar analyst! i highly recommend going through the previous articles to become a more efficient analyst: linear regression is the first machine learning technique most of us learn. its goal is to reduce the sum of squares to produce the line of best fit like this: to perform a regression analysis in excel, we first need to enable excel’s analysis toolpak add-in. there is a shoe selling company in the town of winden. now we will see the result of regression analysis in excel.

r-squared value ranges from 0 to 1. in our case, we have the r-squared value of 0.953 which means that our line is able to explain 95% of the variance – a good sign. this table breaks down the sum of squares into its components to give details of variability within the model. the coefficient table breaks down the components 0f the regression line in the form of coefficients. it seems that an increase in running frequency decreases the sales by 24 units, but can we actually believe in this feature? an old customer of yours named aleksander walks in and we wish to predict the sales from him. in this article, we learned how to build a linear regression model in excel and how to interpret the results. the analytics toolpak consists of a lot of other analysis choices in excel.