DECISION SUPPORT SYSTEM
The manager of a commuter rail transportation system was recently asked by her governing board to determine which factors have a significant impact on the demand for rides in the large city served by the transportation network. The system manager has collected data on variables, thought to be possibly related to the number of weekly riders on the city’s rail system.
Objective: To develop a Decision Support System to estimate the impact of each factor on the demand for rides.
Dependent variable: Demand for Commuter rail transportation (Com_trans)
Independent variables: Year, Weekly_riders, Price_per_rider, Population, Income, Parking_rate
Descriptive Statistics for the independent variables.
|
Year |
|
Price_per_Ride |
|
Parking_Rate |
|
|
|
|
|
|
|
|
|
Mean |
14 |
Mean |
0.533333333 |
Mean |
1.07037037 |
|
Standard
Error |
1.527525232 |
Standard
Error |
0.060974984 |
Standard
Error |
0.087268589 |
|
Median |
14 |
Median |
0.4 |
Median |
1 |
|
Mode |
#N/A |
Mode |
1 |
Mode |
0.6 |
|
Standard Deviation |
7.937253933 |
Standard
Deviation |
0.316835313 |
Standard
Deviation |
0.453460892 |
|
Sample
Variance |
63 |
Sample
Variance |
0.100384615 |
Sample
Variance |
0.205626781 |
|
Kurtosis |
-1.2 |
Kurtosis |
-1.45840798 |
Kurtosis |
-0.768702646 |
|
Skewness |
3.68936E-17 |
Skewness |
0.441434598 |
Skewness |
0.668860575 |
|
Range |
26 |
Range |
0.85 |
Range |
1.5 |
|
Minimum |
1 |
Minimum |
0.15 |
Minimum |
0.5 |
|
Maximum |
27 |
Maximum |
1 |
Maximum |
2 |
|
Sum |
378 |
Sum |
14.4 |
Sum |
28.9 |
|
Count |
27 |
Count |
27 |
Count |
27 |
Performing Regression analysis with the independent variables we get the following results.
|
SUMMARY
OUTPUT |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Regression Statistics |
|
|
|
|
|
|
|
|
|
Multiple
R |
0.985554316 |
|
|
|
|
|
|
|
|
R Square |
0.97131731 |
|
|
|
|
|
|
|
|
Adjusted
R Square |
0.964488098 |
|
|
|
|
|
|
|
|
Standard
Error |
17.70163899 |
|
|
|
|
|
|
|
|
Observations |
27 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ANOVA |
|
|
|
|
|
|
|
|
|
|
df |
SS |
MS |
F |
Significance F |
|
|
|
|
Regression |
5 |
222837.0989 |
44567.41979 |
142.2297781 |
1.85736E-15 |
|
|
|
|
Residual |
21 |
6580.308482 |
313.348023 |
|
|
|
|
|
|
Total |
26 |
229417.4074 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Coefficients |
Standard Error |
t Stat |
P-value |
Lower 95% |
Upper 95% |
Lower 95.0% |
Upper 95.0% |
|
Intercept |
1115.333686 |
516.9940647 |
2.157343308 |
0.042715666 |
40.18548478 |
2190.481887 |
40.18548478 |
2190.481887 |
|
Year |
-25.72979437 |
7.615762866 |
-3.37849206 |
0.002837702 |
-41.56764301 |
-9.891945735 |
-41.56764301 |
-9.891945735 |
|
Price_per_Ride |
-185.7127795 |
43.20633862 |
-4.29827626 |
0.000318403 |
-275.5652951 |
-95.860264 |
-275.5652951 |
-95.860264 |
|
Population |
-0.044720981 |
0.300325759 |
-0.148908244 |
0.883046748 |
-0.669282697 |
0.579840734 |
-0.669282697 |
0.579840734 |
|
Income |
0.021071963 |
0.02284011 |
0.922585867 |
0.366702202 |
-0.026426655 |
0.06857058 |
-0.026426655 |
0.06857058 |
|
Parking_Rate |
306.7686067 |
44.83987552 |
6.841424137 |
9.18481E-07 |
213.5189646 |
400.0182487 |
213.5189646 |
400.0182487 |
|
|
|
|
|
|
|
|
|
|
The p-value for population is very high so we perform Regression analysis without Population.
|
SUMMARY
OUTPUT |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Regression Statistics |
|
|
|
|
|
|
|
|
|
Multiple
R |
0.985538951 |
|
|
|
|
|
|
|
|
R Square |
0.971287024 |
|
|
|
|
|
|
|
|
Adjusted
R Square |
0.966066483 |
|
|
|
|
|
|
|
|
Standard
Error |
17.30377856 |
|
|
|
|
|
|
|
|
Observations |
27 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ANOVA |
|
|
|
|
|
|
|
|
|
|
df |
SS |
MS |
F |
Significance F |
|
|
|
|
Regression |
4 |
222830.1509 |
55707.53771 |
186.0510243 |
1.27778E-16 |
|
|
|
|
Residual |
22 |
6587.256556 |
299.4207526 |
|
|
|
|
|
|
Total |
26 |
229417.4074 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Coefficients |
Standard Error |
t Stat |
P-value |
Lower 95% |
Upper 95% |
Lower 95.0% |
Upper 95.0% |
|
Intercept |
1038.880533 |
59.28395455 |
17.52380625 |
2.06924E-14 |
915.9330046 |
1161.828062 |
915.9330046 |
1161.828062 |
|
Year |
-24.98578351 |
5.618463564 |
-4.447084729 |
0.000202412 |
-36.63777629 |
-13.33379074 |
-36.63777629 |
-13.33379074 |
|
Price_per_Ride |
-183.6614195 |
40.03088865 |
-4.587992564 |
0.00014349 |
-266.6804904 |
-100.6423485 |
-266.6804904 |
-100.6423485 |
|
Income |
0.019994213 |
0.02117613 |
0.944186386 |
0.355329153 |
-0.023922439 |
0.063910865 |
-0.023922439 |
0.063910865 |
|
Parking_Rate |
302.8152415 |
35.32319456 |
8.57270259 |
1.8384E-08 |
229.559341 |
376.0711419 |
229.559341 |
376.0711419 |
|
|
|
|
|
|
|
|
|
|
We now perform a Regression analysis without income to get the final equation
|
R Square |
0.970123511 |
|
|
|
|
|
|
|
|
Adjusted
R Square |
0.966226578 |
|
|
|
|
|
|
|
|
Standard
Error |
17.26291142 |
|
|
|
|
|
|
|
|
Observations |
27 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ANOVA |
|
|
|
|
|
|
|
|
|
|
df |
SS |
MS |
F |
Significance F |
|
|
|
|
Regression |
3 |
222563.2209 |
74187.74029 |
248.9453731 |
1.13974E-17 |
|
|
|
|
Residual |
23 |
6854.186544 |
298.0081106 |
|
|
|
|
|
|
Total |
26 |
229417.4074 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Coefficients |
Standard Error |
t Stat |
P-value |
Lower 95% |
Upper 95% |
Lower 95.0% |
Upper 95.0% |
|
Intercept |
1093.745026 |
11.72313045 |
93.29803427 |
3.6017E-31 |
1069.493916 |
1117.996136 |
1069.493916 |
1117.996136 |
|
Year |
-19.96809666 |
1.819255798 |
-10.97596978 |
1.28259E-10 |
-23.73150889 |
-16.20468443 |
-23.73150889 |
-16.20468443 |
|
Price_per_Ride |
-182.7136966 |
39.92378948 |
-4.576561971 |
0.000133734 |
-265.3022352 |
-100.1251581 |
-265.3022352 |
-100.1251581 |
|
Parking_Rate |
288.7855386 |
31.97017381 |
9.032967424 |
5.02369E-09 |
222.6502852 |
354.9207919 |
222.6502852 |
354.9207919 |
A negative coefficient for the variable would include redundancy. This is a good model to estimate the demand
Number of riders=1093.75-19.97Year-182.7Price_per_ride+288.8Parking_rate
Sensitivity or What-if?
Analysis is used to find out how sensitive the dependent variable is to
each/combination of the independent variables. From the above DSS we can
conclude the following sensitivity relationship between the independent
variables and the dependent variable.
a. 1% increases in Year decreases
the demand by 20 and a 1% decrease in Year increases the contract sales by 20.
b. 1% increase in Price_per_rider
decreases the demand by 2, whereas a 1% decrease in Price_per_rider increases
the demand by 2.
c. 1% increase in Parking_rate increases the demand by
3,whereas a 1% decrease in Parking_rate decreases the decreases the demand by
3.
This shows that demand is more
sensitive with respect to the year and then it is more sensitive with respect
to parking_rate. The Price_per_rider is the lease sensitive out of the 3
variables. So this shows that the manager of the commuter rail transportation
system should decrease the variable of year and increases Parking_rate if he
wants to make a significant impact on the demand for rides in the large city
served by the transportation network.
This Analysis is used to determine how to change the independent variables in order to get the desired result (of the output variable). This can be done using the results of Sensitivity Analysis. Let’s take the case when the manager of the rail commuting system decides to keep the demand at 1050.To obtain this he would have to adjust the parking _rate to value of 1.16 which would then give him the desired result he wanted to achieve. He can also alter other variables and get the desired output according to the circumstances. So in this way by altering the independent variables you can get any desired output u want.
Scenario Analysis
We can analyze various possible scenarios. The best case scenario will
be when Price_rider is at its minimum value and that would be 0.15 and the
worst case would be when it is at its maximum value and that would be
1.considering the variable Parking _rate the best case scenario would be when
the parking rate would be at its maximum and that would be 0.5 and the worst
case would be at 0.2. The expected scenario is when the values of all the
independent variables equal their respective average values. Some of the other
possible scenarios can be when the values of the independent variables are
somewhere between their max and min. value.
|
|
|
|
|
|
|
|
Scenario Summary |
|
|
|
||
|
|
|
Current Values: |
scenario1 |
s2 |
s3 |
|
Changing Cells: |
|
|
|
|
|
|
|
$C$3 |
14 |
17 |
19 |
20 |
|
|
$C$4 |
0.533 |
0.65 |
0.7 |
0.55 |
|
|
$C$5 |
1.16 |
1.13 |
1.15 |
1.06 |
|
Result Cells: |
|
|
|
|
|
|
|
$F$3 |
1051.77037 |
961.8199 |
918.52 |
899.9663 |
|
Notes: Current Values column represents values of
changing cells at |
|||||
|
time
Scenario Summary Report was created.
Changing cells for each |
|||||
|
scenario
are highlighted in gray. |
|
|
|
||
|
|
|
|
|
|
|
The potential
limitation of this DSS would be the possibility that it fails to consider any
other possible variables that may have significant effect on the demand. If any such variables are found out, then
they have to be incorporated in the DSS.