How to predict quarterly revenue using R?

Question

Trying to predict '16 revenue prediction fucntion in R. I'm quite unfamiliar with this method and have read that Arima for univariate, box-Jenkins for multivariate for predictions.

I have set of data as follows and I want to make prediction for the next quarter's revenue in 2016.

From this data, how do I interprete for 2016's revenue? Could someone please help me?

The data is as follows:

   year   month   clicks   displays  sales       rev
 1: 2013     1  3350822 1146916151 129646  12792716
 2: 2013     2  2774135  984286445 126227   9301925
 3: 2013     3  2892579  967930719 151966  13284856
 4: 2013     4  3296395 1147754247 165168  12655752
 5: 2013     5  3171190 1159809788 182977  15089209
 6: 2013     6  2606784 1015161899 145694   8893754
 7: 2013     7  2970039 1199375184 156814  11012823
 8: 2013     8  2925406 1284866259 150373  12202659
 9: 2013     9  2713304 1262218999 146155  11559574
10: 2013    10  3187140 1569619026 162097  25022638
11: 2013    11  3262731 1649279161 173665  11621704
12: 2013    12  3283399 1708595223 177966  17825133
13: 2014     1  4851276 2491151738 198338  49065208
14: 2014     2  4395304 1811290343 185689  15528556
15: 2014     3  4908698 1935532238 222345  17335326
16: 2014     4  4616648 1652841814 238402  15354234
17: 2014     5  4404517 1613752345 271847  18522974
18: 2014     6  3876896 1326242243 268091  22565322
19: 2014     7  4571233 1237162599 309268  22023250
20: 2014     8  4781473 1076301082 306972  31286438
21: 2014     9  4682077 1133978339 289326  35281811
22: 2014    10  5982788 1464096951 339983  59081082
23: 2014    11  6219104 1379860921 331986  72156570
24: 2014    12  6691648 1611674163 386073  59094580
25: 2015     1  8411187 2061361446 379481  97190760
26: 2015     2  7471402 1667345575 359188  98833961
27: 2015     3  8811576 1909563162 454111 102437201
28: 2015     4  9061911 2050283355 440551  93614359
29: 2015     5  9728254 2006776762 521781  73863899
30: 2015     6 10379345 1857331372 628497  68028506
31: 2015     7 11232551 2174549198 519711  77408783
32: 2015     8 11718022 2270961526 530628  83112348
33: 2015     9 10998938 2106945271 534276  71477968
34: 2015    10 11623937 2288945105 559432  73709860
35: 2015    11 13676241 2806167731 631772  86201956
36: 2015    12 14400905 2835507200 687981  84602342

And here is the method I used

model <- lm(formula = rev ~ clicks + displays + sales, data = dt)
summary(model)
library(ztable)
ztable(model)
fitted.rev <- predict(model)
fitted.rev
predicted.rev <- predict(model, newdata = dt, interval='prediction')
predicted.rev

and this gave me results of..

Call:
lm(formula = rev ~ clicks + displays + sales, data = dt)

Residuals:
      Min        1Q    Median        3Q       Max 
-24865953  -9575883  -6019452   3352007  43548517 

Coefficients:
              Estimate Std. Error t value Pr(>|t|)  
(Intercept) -8.346e+05  1.379e+07  -0.061   0.9521  
clicks       9.324e+00  5.022e+00   1.857   0.0726 .
displays    -5.500e-03  1.202e-02  -0.458   0.6503  
sales       -1.218e+01  8.761e+01  -0.139   0.8903  
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 17310000 on 32 degrees of freedom
Multiple R-squared:  0.7461,    Adjusted R-squared:  0.7223 
F-statistic: 31.34 on 3 and 32 DF,  p-value: 1.205e-09


> fitted.rev
        1         2         3         4         5         6         7 
 89474032  20126857  28047573  72481469  65293117  18401932  33061303 
        8         9        10        11        12        13        14 
 45521066  18081290 103557431  28283437  18962076  18216028  34090332 
       15        16        17        18        19        20        21 
 15742753  17544524  24754831  18276135  88146928  31217320  42756874 
       22        23        24        25        26        27        28 
 18352402  31582121  30218036  78074250  22522450  16113880  21577581 
       29        30        31        32        33        34        35 
 67018610 109468295  85610574  83626641  47993385  27924932  55285443 
       36 
 61634125 

> predicted.rev
         fit       lwr       upr
1   89474032  51552302 127395762
2   20126857 -16113641  56367356
3   28047573  -9070539  65165686
4   72481469  35677009 109285930
5   65293117  29200488 101385746
6   18401932 -18534160  55338024
7   33061303  -3683547  69806154
8   45521066   9257703  81784428
9   18081290 -19631664  55794244
10 103557431  65138554 141976308
11  28283437 -13363684  69930559
12  18962076 -18197579  56121731
13  18216028 -19093121  55525177
14  34090332  -3134954  71315618
15  15742753 -20525739  52011246
16  17544524 -18719720  53808769
17  24754831 -12547164  62056827
18  18276135 -18304301  54856571
19  88146928  51169057 125124799
20  31217320  -6146119  68580760
21  42756874   6629129  78884619
22  18352402 -17953601  54658406
23  31582121  -5403699  68567942
24  30218036  -5863079  66299151
25  78074250  37301367 118847132
26  22522450 -15644903  60689804
27  16113880 -20596371  52824131
28  21577581 -15028145  58183307
29  67018610  30912042 103125178
30 109468295  70892682 148043909
31  85610574  48182208 123038940
32  83626641  46758500 120494782
33  47993385  11516444  84470325
34  27924932  -9029553  64879416
35  55285443  19011896  91558991
36  61634125  25133900  98134349

I basically don't understand what this presents. What I would like to know is whether this approach is correct way of forecasting the revenue, and how can I visualize this into plot.

I would really appreciate someone's help on this!

Answer 1

There are many issues to be discussed. I would recommend you to review a textbook or other resources about Econometrics. I will give some guidance on what to study there.

1) Diagnostic of the model: the linear econometric model rely on assumptions that should be checked in order to assess the quality of the estimates. You should for example check whether the residuals are homoscedastic (constant variance throughout time) and whether they are independent (absence of serial correlation).

The plot of the residuals of your model, plot(residuals(model)), reveals a pattern that is not captured by the model. This may lead to violation of the assumptions mentioned above.

The package lmtest implements some of the test statistics that you may use to make a diagnostic of the model.

2) Model selection: Except for the variable clicks, the remaining explanatory variables are not significant at the 5% significance level ($p$-values greater than 0.05). Maybe you should reconsider the choice of your model.

3) The test statistic for the significance of all the regressors except the intercept concludes that the regressors are jointly significant, $F=31.34$ with $p$-value close to zero. This result is in contrast with the fact that the variables are individually not significant. As discussed for example here, this may be caused by the presence of multicollinearity across the regressors; this would be a violation of the assumption of uncorrelated regressors.

3) We usually refer to the fitted values; i.e., the values estimated for the in-sample observations as predictions; while the term forecasts refers to out-of-sample or future forecasts. You mention forecasting in your question, but the values that you obtain by means of predict are the fitted values (in-sample). You can display them as follows:

plot(cbind(dt[,"rev"], a), plot.type="single", type="n")
lines(dt[,"rev"])
pred <- ts(predicted.rev[,"fit"])
tsp(pred) <- tsp(dt)
lines(pred, col="blue")

It may be illuminating to check that the output from predict can be obtained in this other ways:

# matrix of regressors multiplied by the vector of estimated coefficients
cbind(1, dt[,c("clicks", "displays", "sales")]) %*% coef(model)
# dependent variable minus the residuals of the model
dt[,"rev"] - residuals(model)

If you are interested in forecasts you need to know future values of the regressors variables, then you can obtain the forecasts as follows: matrix_of_future_regressor_variables %*% coef(model).

4) Dynamic model: It may happen that changes in the explanatory variable have an effect on the dependent variable only after a few periods; i.e., a change in the variable clicks at time $t$ may not have an on rev at the very time $t$, but for example $t+1$. The context of the data may suggest you whether this may be possible. You can conduct a cross-correlation analysis in order to check this guess. My comment to this post gives some links to other posts or sites discussing this. You may also be interested in the package dynlm.

5) The regression model that you use can be useful in order to get a better understanding of the observed data and the relationships across the variables (or the absence of relationship). You can use it to test your theory or hypothesis. However, if you are interested only in getting short-term forecasts (out-of-sample) for the dependent variable, the you may consider using a time series model, for example ARIMA or structural time series models.