# PETROL CASE – MULTIPLE REGRESSION ANALYSIS Harvard Case Solution & Analysis

## PETROL CASE – MULTIPLE REGRESSION ANALYSIS Case Solution

Problem Diagnosis

This case focuses on the estimation of a model of predicting the demand for a branch of a company which specializes in supplying and selling the distribution products. The opening of the branch would take place in one of the emerging markets called Taraland located in one of the antipodes. Therefore, the Board of directors of the company believe that the study of the future demand of this particular branch and the range of the variables effecting the demand is highly important before a final investment decision is made. The investment is also a huge one as significant resources would be required for establishing the service stations, pipelines and ports. Therefore, this case focuses on the creation of the regression model in order to estimate the future demand of petrol with reasonable accuracy.

Analysis

Before the construction of the regression models, the data for Domestic Private Consumption and Bus Fares needs to be deflated using the Retail Price Index for each of the respective years.

Adjustment of Nominal DPC & Bus Fares

The current data for domestic private consumption and the Bus Fares C-Euros/Km is inflation adjusted. Therefore, inflation needs to be taken out by deflating these figures using the retail price index values for each of the year. The complete real data set for all the six explanatory variables and the dependent variable which is the petrol consumption in thousands of tones is shown in the Real Data sheet in excel.

Multiple Regression Model 1

The first regression model has been estimated by using the data for the 20 years collected by Pepe Lumbrera from the Taraland Statistical Institute. The variable which has been taken as dependent variable is Petrol Consumption and the independent or the exploratory variables are price of petrol, bus fares, population, DPC, number of tourists and number of petrol vehicles. The regression statistics for this multiple regression model are shown below:

Regression Statistics

Multiple R 71.37%
R Square 50.94%
Standard Error 24516.551
Observations 20

First of all, if we look at the value of Multiple R which is the correlation between the petrol consumption and all the six independent variables then the relationship between then is 71.37%. This is a positive and moderately strong correlation. Moreover, if we look at the adjusted R square value which shows the percentage explanation of petrol as a result of all the six explanatory variables then this variation is 28.29%. However, in order to determine that whether this dependency is significant or not, the ANOVA table needs to be analyzed which is shown below.

ANOVA
df SS MS F Significance F
Regression 6 8112392350.2621 1352065391.7104 2.2495 10.39%
Residual 13 7813796437.7379 601061264.4414
Total 19 15926188788.0000

If we look at the above ANOVA table, then it could be seen that the significant value for the F test is 10.39%. This p value is higher than the level of significance of 5%, therefore, it could be said that this multiple regression model is not significant or fitted. In order to analyze the significance of the effect of each of the six exploratory variables individually on petrol consumption, the coefficients table needs to be interpreted as shown below.
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 2102761.206 905069.764 2.323 0.037 147476.856 4058045.555 147476.856 4058045.555
PRICE OF PETROL 10.156 5.272 1.926 0.076 -1.235 21.546 -1.235 21.546
BUS FARES -203.919 106.410 -1.916 0.078 -433.804 25.966 -433.804 25.966
DPC -14.585 7.832 -1.862 0.085 -31.504 2.334 -31.504 2.334
POPULATION -72.324 32.055 -2.256 0.042 -141.575 -3.072 -141.575 -3.072
TOURISTS 25.157 19.235 1.308 0.214 -16.398 66.711 -16.398 66.711
PETROL VEH. 71.836 35.230 2.039 0.062 -4.274 147.946 -4.274 147.946...........

This is just a sample partial case solution. Please place the order on the website to order your own originally done case solution.