The following data (stored in Credit) are monthly credit card charges (in millions of dollars) for a popular credit card issued by a large bank (the mane of which is not disclosed at its request):
Copy the data and paste it into the Excel sheet.
To construct the time series plot, select the data and go to Insert -> Line -> Line with marker.
The following time series plot will be created.
From the data, it can be observed that the credit card charges exhibit a rising trend over a one-year cycle. In the starting of the year the charges decline continuously in the month of January and February, reaching their lowest-in-the-year value in the month of February. After February, the charges pick up and continue to rise but dip once in September every year. In the month of December, the charges are highest.
It can be observed that the overall charges are increasing every year, i.e., the charges in January 2009 are higher than those in January 2008, which in turn is higher than that in January 2007. Similar observation is made for all the months. Thus the data is depicting an upward sloping trend line overall, despite monthly fluctuations.
Step 1: Select the data and go to Insert -> Pivot Table
Step 2: In the Pivot Table dialog box, select the data range as A1 to C25.
Step 3: On clicking on Ok, the following screen appears:
Step 4: in the right panel, drag the option Month to the section of Row Labels. Similarly drag the option of Year. Drag the option Credit Card Charges to section Values. This creates the pivot table as shown below:
Step 5: In the Pivot table tools, go to Options -> Field Settings.
In the field settings tool box, under Summarize by, select the option ‘Average’ and under show values as, select the option of ‘% of total’. The following pivot table appears.
Step 6: For the purpose of pasting the monthly average seasonal index, unselect the option of years from the right panel. Copy the resultant pivot table to Sheet 1.
Step 7: Paste the seasonal index against the corresponding month.
Step 8: De-seasonalize the data by dividing the credit card charges with the corresponding seasonal index.
Step 9: insert a column to the left of credit card charge. In this column, number the observations.
Step 10: Use the function FORECAST
Go to Formula -> More Functions -> Statistical -> FORECAST. In the FORECAST dialog box, enter the following: X: C2 Known y’s: F2 to F25 Known x’s : C2 to C25
The following is the final output:
On the basis of the forecast, the value of credit card charge for February 2009 is 39.24. The actual value is 39.6. Therefore, it can be concluded that even though the charge was very high in December 2008, but, the charge in February is expected to be low because of the presence of seasonality.
Step 1: Copy and paste data on excel and take log (base 10) of credit charge.
Step 2: Click on data and then click data analysis tool pack and select regression. For x values select cells C1 to C25 and for x values select cells D1 to D25. Regression summary is obtained.
The following values can be obtained by converting the intercept value as 10^intercept and taking the Month coefficient from regression summary Y=35.38270251* 10^ (0.0080*t)
The monthly compound growth rate is 0.065524102 or 6.55%. This means that on an average every month the charges grow by 6.55%
The January multiplier is 0.8523 or 85.23% every January (see Pivot table output). It mean that in the month of January, the charges are only 85.23% of the total annual average credit card charges.
Taking t= 25 Y=35.38270251* 10^ (0.0080*t) =35.38270251* 10^ (0.0080*25) =56.07
Taking t= 26 Y=35.38270251* 10^ (0.0080*t) =35.38270251* 10^ (0.0080*26) =57.11
Time-series financial forecasting can help banks in analyzing potential portfolio decisions over a time horizon. Banks can conduct prediction of the future external economic environment as well as their own internal financial variables. Bank’s current financial position depends heavily on the previous decisions of deposits and funding as well as acquiring funds out of several investment opportunities of varying returns and time horizons. These decisions will also be affected by overall external economic environment as well as future expectations of financial and economic variables as well as expected targets for meeting management objectives. All these financial planning decisions can be optimized based on time-series forecasting of relevant economic and financial variables.