The result is shown in Figure 3 and shows that there is a direct association between inventory and revenue 3 months later. We can replace these labels by selecting Chart Tools|Design > Data|Select Data and editing these labels on the dialog box that appears. The two line charts are labeled Series1 and Series2. Next select Insert > Charts|Insert Line Chart and add the secondary axis as described above. To create this chart, highlight range B4:B18 and while holding down the Ctrl key highlight range C7:C21. However, if instead, we create a chart with inventory lagging 3 months behind revenue the situation is quite different.
Correlation formula in excel series#
No demonstrable relationship between the inventory and revenue time series is discernible in the chart shown in Figure 1.
On the bottom of the dialog box that appears click on the Secondary Axis checkbox corresponding to the Revenue line chart as shown in Figure 2. This is done by clicking anywhere on the revenue line (in red) on the chart and selecting Chart Tools|Design > Type|Change Chart Type. Since the inventory and revenue time series have a different scale, we need to add a secondary vertical axis. We create the chart on the right side of Figure 1 by highlighting range A3:C21 and selecting Insert > Charts|Insert Line Chart. The analysis of a leading indicator can be carried out using cross-correlation, as explained in the following example.Įxample 1: Evaluate inventory as a leading indicator of a company’s revenues based on the data on the left side of Figure 1. For a company marketing spend may be a leading indicator of revenues with a lag of a few months. There is a lag between a lower CCI and the onset of a recession. When looking for leading indicators, especially when doing financial analysis, instead of evaluating the correlation between two time series, it is often beneficial to investigate the correlation between one time series and the other with a time lag.įor example, the consumer confidence index (CCI) is considered by many economists to be a leading indicator of a gross domestic product (GDP) rise or fall.
0 kommentar(er)
