Question

Based on this information, in which city should you open a second office in?  

Based on the MLR output, what variable(s) is/are significant?

Based on the descriptive statistics, for the significant predictors, what city/cities has the best potential? What city or cities fall above or below the median and/or the mean? What city or cities are in the upper 3^rd quartile? Or the bottom quartile? These are some things to consider not necessarily questions you need to answer in your Executive Summary. But they are questions to help guide you along in your analysis.

City	Cost of Living Index	Rent (in City Centre)	Monthly Pubic Trans Pass	Loaf of Bread	Milk	Bottle of Wine (mid-range)	Coffee
Mumbai	31.74	$1,642.68	$7.66	$0.41	$2.93	$10.73	$1.63
Prague	50.95	$1,240.48	$25.01	$0.92	$3.14	$5.46	$2.17
Warsaw	45.45	$1,060.06	$30.09	$0.69	$2.68	$6.84	$1.98
Athens	63.06	$569.12	$35.31	$0.80	$5.35	$8.24	$2.88
Rome	78.19	$2,354.10	$41.20	$1.38	$6.82	$7.06	$1.51
Seoul	83.45	$2,370.81	$50.53	$2.44	$7.90	$17.57	$1.79
Brussels	82.2	$1,734.75	$57.68	$1.66	$4.17	$8.24	$1.51
Madrid	66.75	$1,795.10	$64.27	$1.04	$3.63	$5.89	$1.58
Vancouver	74.06	$2,937.27	$74.28	$2.28	$7.12	$14.38	$1.47
Paris	89.94	$2,701.61	$85.92	$1.56	$4.68	$8.24	$1.51
Tokyo	92.94	$2,197.03	$88.77	$1.77	$6.46	$17.75	$1.49
Berlin	71.65	$1,695.77	$95.34	$1.24	$3.52	$5.89	$1.71
Amsterdam	85.9	$2,823.28	$105.93	$1.33	$4.34	$7.06	$1.71
New York	100	$5,877.45	$121.00	$2.93	$3.98	$15.00	$0.84
Sydney	90.78	$3,777.72	$124.55	$1.94	$4.43	$14.01	$2.26
Dublin	87.93	$3,025.83	$144.78	$1.37	$4.31	$14.12	$2.06
London	88.33	$4,069.99	$173.81	$1.23	$4.63	$10.53	$1.90

Answer 1

ased on the given data, our objective here, is to choose a city to open a second office. Since, the Cost of living index is a single measure that can best characterize a city, we may test how the rest of the variables relate to it. Running a multiple regression model by regressing Cost of living index on possible predictors - Rent, Transport, Bread, Milk, Wine and Coffee we may identify the significant predictors and through them, the best city.

Using Excel,

From the p-values of the test for significance of predictors obtained from the output, we find that the only significant predictors are Monthly Public Trans Pass (p-value = 0.003<0.05) and Loaf of Bread (p-value = 0.033<0.05) along with the intercept at 5% level .

The regression equation can be expressed as:

Expected Cost of Living Index = 35.640 + 0.3 (Monthly Public Trans Pass) + 16.595 (Loaf of Bread)

The descriptive statistics for the response variable and the significant predictors are given by:

To find out what city or cities fall above or below the median and/or the mean, in the upper 3^rdquartile or the bottom quartile, we may use the excel function "IF":

Similarly using the function for obtaining the cities that lie above or below the median, in the 1st or last quartile, we may arrive at the following conclusions:

Since, the slope of both predictors are positive, both are positively related to Cost of living. Cities which satisfies the required condition for both the variables is chosen.

- It is clear that cities namely Mumbai, Prague, Warsaw and Athens lie in the bottom quartile (For both the predictors) - Sydney lies above the 3rd quartile (In terms of both the predictors); Dublin, London, Seoul and Vancouver based on one of the predictor   

The cities marked in Green and Red lie above and below the median respectively (In terms of both significant predictors).

Compiling all the three results, we find that Sydney marks its place in the criterion - Greater than median and third quartile and hence is the next best city to open an office after New York. Cities Tokyo & Paris also lies above the median, hence might be the next choice in rank.

The above conclusions are also supported by the Cost of living index for the three cities.