please write which excel formula should be written to which cell.

Question 1 A hotel manager thinks that the monthly electricity bill is positively correlated with the ratio of average

number of guests to the hotels square-meter (person/square-meter):

1. a) Calculate the correlation coefficient between the electricity bill and the person/square-meter. Also calculate the square of the correlation.

2. b) Perform regression analysis using Excels Data Analysis toolbox. Place the regression output table in cell C12.

3. c) Estimate the person/square-meter for the given electricity bill of hotel.

4. d) Write IF functions to return yes or no as the conclusion of the hypothesis test that the

   regression coefficient of Electricity Charge is equal to 0, first using the p-value and then using the confidence interval limits

Question 2

A fast food restaurant recently added a new sandwich to its menu. To estimate the popularity of this sandwich, a random sample of 40 customers who ordered the sandwich were surveyed. Each of these customers was asked to rate the sandwich on a scale of 1 to 10, 10 being the best. The manager wants to estimate the mean satisfaction rating over the entire population of customers, by finding a 95% confidence interval. You can find the data in Q2 sheet of the Excel file, to do the analysis fort his manager.

Question 3 (30 points)

An automotive brand wants to measure the effect of advertisements on the wholesale of the company. They have collected the data on money spent on the advertisements and the amount of sales of the company on that year.

a) Calculate the correlation coefficient between sales and investment on advertisements. Also calculate the square of the correlation.

b) Perform regression analysis using Excels Data Analysis tools. Place the summary output in cell C16.

c) Write the equation of sales as the response variable with respect to intercept and coefficient of advertisement investments.

d) Using your regression equation, predict the amount of sales, if the company 64 million euros in advertisement.

E13 fox A B D E 1 Year 2 3 4 5 6 7 8 1 2 3 4 5 Electricity Bill Person/Square-Meter 56.50 59.40 63.00 65.60 89.00 8.37 8.93 9.15 9.79 11.20 a) Corr(Electricity Bill, Person/Square-Meter) Correlation squared: b) 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40. c) Electricity Bill 65.00 Person/Square-Meter d) alpha Yes/No? Yes/No? 0.05 Using p-value Using confidence interval limits Q1 Q2 Q3 + - C14 fx Correlation squared: A B C D 1 2 Year Sale(Million Euro) Advertisements (Million euro) 651 3 1 2 762 4 5 23 26 30 3 6 4 34 43 7 5 8 6 856 1063 1190 1298 1421 1440 1518 9 7 48 52 57 10 8 11 9 58 12 13 a) Corr Correlation squared: 14 15 b) 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 c) 37 38 39 d) 40 Q1 Q2 Q3 + F18 A B C D E F Customer 1 2 3 4 1 5 Sample size Sample mean Sample Std Dev Std Error of mean Confidence level Degrees of freedom t multiple Lower limit Upper limit 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Satisfaction 7 5 5 6 8 7 6 7 10 7 9 5 5 8 8 6 7 8 7 5 5 5 5 5 2 5 8 7 6 6 4 6 6 5 6 7 8 9 5 4 Q1 Q2 Q3 +