It does so because it uses the COUNT function, which counts only numbers. The NumRows cell returns the number of rows in the table. But when the text is displayed in the chart, this character causes the year text to wrap to a second line below the month text. Excel doesn’t show this character in column E. The CHAR(13) section of this formula returns the carriage-return character. Here’s the first formula, for the cell shown: The DateText column contains formulas that return text to be displayed in the chart. The Date, Data1, and Data2 columns contain the values shown. My workbook contains two relevant worksheets: Data and Report. So, with that warning, let’s set up the analysis. But the reality is that you must properly interpret what analyses like this tell you.Įven so, the analysis definitely can give you additional facts on which to base your decisions. This might be because when sales rise or fall, the Marketing Department decides to spend more or less on advertising.īoth interpretations are supported by high correlations. Here, we see that sales performance could be a leading indicator of ad spending three months later.
However, that interpretation isn’t the only one you could make, as this figure illustrates: That is, ad spending could be a good leading indicator of sales performance two months later.
That is, the correlation has been calculated with sales (Data2) shifted two months ahead of ad spending (Data1). The version with the highest correlation has a shift of +2 months. Here, the table shows the correlations associated with eleven different time shifts.
This figure shows the analysis from that perspective… It could be that what you spend on advertising is a leading indicator of what your sales will be several months later. However, that’s not the end of the story. That is, as the chart illustrates, your advertising and sales values are negatively correlated to a significant degree. If you want to be more precise in your analysis, you could use Excel’s CORREL function to learn that Data1 and Data2 have a correlation coefficient of -.50. …when you spend less for advertising, your sales rise significantly. …when you spend more for advertising, your sales fall significantly, and, And suppose that the red Data2 line shows the amount of your sales.Īt first glance, it looks like you REALLY need to change your ad strategy, because… Suppose, for example, that the blue Data1 line in this chart shows what you spend in advertising.
( You can download the example workbook here.) And then you use those leading indicators as the basis of your forecast.īut when you try to make it all work, that simple idea can become a huge challenge. To improve your forecasts of sales or other measures, you simply need to find leading indicators…measures that are highly correlated with your key measures, but with a time lag.
0 kommentar(er)
