III. Mathematical Modeling
TID Model
The first model we built was a simple linear analysis using Quarter (measure of time) as the independent variable to estimate TID. The table below shows the results:
|
Regression Statistics |
|
|
|
|
|
|
|
|
|
Multiple
R |
0.694657411 |
|
|
|
|
|
|
|
|
R Square |
0.482548918 |
|
|
|
|
|
|
|
|
Adjusted
R Square |
0.45211062 |
|
|
|
|
|
|
|
|
Standard Error |
3873.447436 |
|
|
|
|
|
|
|
|
Observations |
19 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ANOVA |
|
|
|
|
|
|
|
|
|
|
df |
SS |
MS |
F |
Significance F |
|
|
|
|
Regression |
1 |
237857200.2 |
237857200.2 |
15.85334712 |
0.000964868 |
|
|
|
|
Residual |
17 |
255061115.6 |
15003595.04 |
|
|
|
|
|
|
Total |
18 |
492918315.8 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Coefficients |
Standard Error |
t Stat |
P-value |
Lower 95% |
Upper 95% |
Lower 95.0% |
Upper 95.0% |
|
Intercept |
14218.07018 |
1849.830412 |
7.686147922 |
6.26685E-07 |
10315.26371 |
18120.87664 |
10315.26371 |
18120.87664 |
|
Quarter |
645.9824561 |
162.2408597 |
3.981626191 |
0.000964868 |
303.683685 |
988.2812273 |
303.683685 |
988.2812273 |
TID =
646*Quarter + 14218
From this equation you can calculate the TID by multiplying the Quarter number by 646 and adding a constant of 14218. To check the validity of this equation, we must look at the P-value, which is less than .05, so this appears to be a viable model. The R-square value of .48 is adequate, but could be better. Intuitively we can look at the formula and notice that TID increases quarter over quarter, which is simplistic to say the least. We would do well to look at the other variables to see if we can do better.
Next, we performed a multiple regression analysis that was built by using Quarter, AvgPrice, and AvgAdv as the predictor variables. The table below is the result.
|
Regression Statistics |
|
|
|
|
|
|
|
|
|
Multiple
R |
0.952334882 |
|
|
|
|
|
|
|
|
R Square |
0.906941728 |
|
|
|
|
|
|
|
|
Adjusted
R Square |
0.888330073 |
|
|
|
|
|
|
|
|
Standard
Error |
1748.716231 |
|
|
|
|
|
|
|
|
Observations |
19 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ANOVA |
|
|
|
|
|
|
|
|
|
|
df |
SS |
MS |
F |
Significance F |
|
|
|
|
Regression |
3 |
447048189 |
149016063 |
48.72977468 |
5.72576E-08 |
|
|
|
|
Residual |
15 |
45870126.82 |
3058008.455 |
|
|
|
|
|
|
Total |
18 |
492918315.8 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Coefficients |
Standard Error |
t Stat |
P-value |
Lower 95% |
Upper 95% |
Lower 95.0% |
Upper 95.0% |
|
Intercept |
130249.285 |
50620.06503 |
2.573076208 |
0.021204383 |
22355.10406 |
238143.4659 |
22355.10406 |
238143.4659 |
|
Quarter |
132.2282604 |
102.9946976 |
1.283835609 |
0.218677295 |
-87.29987602 |
351.7563967 |
-87.29987602 |
351.7563967 |
|
Avg_Price |
-358.6135195 |
122.1831725 |
-2.935048355 |
0.010239797 |
-619.0409472 |
-98.1860919 |
-619.0409472 |
-98.1860919 |
|
Avg_Adv |
0.263430426 |
0.063281565 |
4.162830481 |
0.000833208 |
0.128548881 |
0.398311972 |
0.128548881 |
0.398311972 |
TID =
132.22*Quarter – 358.61*AvgPrice + .2634*AvgAdv +130249.29
In this model, we achieved a much higher R-square of .90, which indicates we have accounted for 90% of the TID in our formula. However, the Quarter’s P-value of .218 is much too high. This effectively rules out this variable, and the formula should be dropped.
Finally, we re-ran the multiple regression analysis without Quarter as a variable and came up with the following table and formula:
|
Regression Statistics |
|
|
|
|
|
|
|
|
|
Multiple
R |
0.946951041 |
|
|
|
|
|
|
|
|
R Square |
0.896716275 |
|
|
|
|
|
|
|
|
Adjusted
R Square |
0.883805809 |
|
|
|
|
|
|
|
|
Standard
Error |
1783.788804 |
|
|
|
|
|
|
|
|
Observations |
19 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ANOVA |
|
|
|
|
|
|
|
|
|
|
df |
SS |
MS |
F |
Significance F |
|
|
|
|
Regression |
2 |
442007875.9 |
221003937.9 |
69.45653998 |
1.29496E-08 |
|
|
|
|
Residual |
16 |
50910439.94 |
3181902.496 |
|
|
|
|
|
|
Total |
18 |
492918315.8 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Coefficients |
Standard Error |
t Stat |
P-value |
Lower 95% |
Upper 95% |
Lower 95.0% |
Upper 95.0% |
|
Intercept |
164336.1704 |
43962.46954 |
3.738101434 |
0.001792219 |
71139.91931 |
257532.4215 |
71139.91931 |
257532.4215 |
|
Avg_Price |
-445.1684971 |
103.9426705 |
-4.282827208 |
0.000570731 |
-665.5170653 |
-224.819929 |
-665.5170653 |
-224.819929 |
|
Avg_Adv |
0.262728595 |
0.064548343 |
4.070260894 |
0.000890413 |
0.125892252 |
0.399564938 |
0.125892252 |
0.399564938 |
TID =
.-445.17*AvgPrice + .2627*AvgAdv +164336.17
We first note that, by dropping Quarter as a variable, the R-square change was .01. Quarter had very little impact on the predictability of our formula and we are still left with a very respectable .90 R-square value. The P-values of the remaining variables are still less than .05, indicating a very low probability of a Type I error. Therefore, this is a good model to use going forward.