Question

The Condé Nast Traveler Gold List provides ratings for the top 20 small cruise ships. The data shown below are the scores each ship received based upon the results from Condé Nast Traveler's annual Readers' Choice Survey. Each score represents the percentage of respondents who rated a ship as excellent or very good on several criteria, including Shore Excursions and Food/Dining. An overall score was also reported and used to rank the ships. The highest ranked ship, the Seabourn Odyssey, has an overall score of 94.3, the highest component of which is 97.9 for Food/Dining.

Ship	Overall	Shore Excursions	Food/Dining
Seabourn Odyssey	94.3	90.7	97.9
Seabourn Pride	92.9	84.0	96.7
National Geographic Endeavor	93.0	99.9	88.5
Seabourn Sojourn	91.5	94.9	96.9
Paul Gauguin	90.7	87.9	91.4
Seabourn Legend	90.5	82.3	99.0
Seabourn Spirit	90.2	86.3	92.1
Silver Explorer	90.1	92.7	88.7
Silver Spirit	89.3	85.8	90.9
Seven Seas Navigator	89.4	83.4	90.4
Silver Whisperer	89.1	82.0	88.7
National Geographic Explorer	88.9	93.0	89.6
Silver Cloud	88.8	78.5	91.3
Celebrity Xpedition	87.2	91.8	73.7
Silver Shadow	87.2	75.0	89.6
Silver Wind	86.8	78.1	91.8
SeaDream II	86.3	77.3	91.0
Wind Star	85.9	76.6	91.6
Wind Surf	86.0	72.4	89.2
Wind Spirit	85.1	77.5	92.0

a. Determine an estimated regression equation that can be used to predict the overall score given the score for Shore Excursions (to 3 decimals).

    

b. Consider the addition of the independent variable Food/Dining. Develop the estimated regression equation that can be used to predict the overall score given the scores for Shore Excursions and Food/Dining (to 3 decimals).

       

c. Predict the overall score for a cruise ship with a Shore Excursions score of  and a Food/Dining Score of 90 .

(to 2 decimals)

Answer 1

Solution:

Install analysis tool pak n excel

Go to

Data>Data analysis>Regression

You will get

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.699191
R Square	0.488868
Adjusted R Square	0.460472
Standard Error	1.888295
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	1	61.38618	61.38618	17.21595	0.000603
Residual	18	64.18182	3.565657
Total	19	125.568
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	69.03602	4.868419	14.18038	3.29E-11	58.80785	79.26419
Shore excursions	0.23814	0.057394	4.149211	0.000603	0.117559	0.35872

Regression equation is

Overall score=69.036+0.238*Shore excursions

Solution-b:

Now include one more variable Food/Dining

You will get

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.862463068
R Square	0.743842543
Adjusted R Square	0.713706372
Standard Error	1.37552513
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	2	93.40282048	46.70141	24.68272	9.38E-06
Residual	17	32.16517952	1.892069
Total	19	125.568
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	44.27984767	6.985356627	6.338953	7.41E-06	29.54203	59.01766
Shore excursions	0.258219897	0.042092524	6.134579	1.1E-05	0.169412	0.347027
Food/Dining	0.253259527	0.061566776	4.113575	0.000725	0.123365	0.383154

Regression equation is

overall score=44.280+0.258*Shore excursions+0.253*Food/Dining

Solution-c:

we have

Regression equation is

overall score=44.27984767+0.258219897*shore excursions+0.253259527food/dining

Given shore excursions=Food/Dining=90,substitute in regression equation above

Regression equation is

overall score=44.27984767+0.258219897*90+0.253259527*90

=90.31

90.31