Streaming services spending analysis

Instructions REMEMBER: You will be submitting 2 files – an Excel file with your work AND a Word file with your report. You will receive a zero (0) for the assignment if you don’t submit both parts! Your report should contain the Excel tables, graphics, and statistics you are asked to create by using copy/paste functionality from Excel to Word. You can also use “snipping tool” as an alternative for copying from Excel to Word. Use the Excel file M07 Business Application – Streaming ServicesDownload M07 Business Application – Streaming Services Part 1 A random sample of 25 customers of streaming services was surveyed and asked how much they spend on streaming services monthly. Other information gathered is the customer age, monthly household income, and family size. Your job is to do an analysis to find the best way to estimate the amount spent on streaming services based on the data you have. Here are your instructions to guide your work: Create a correlation matrix of all the variables from the file. Which variable has the strongest linear correlation with the amount spent on streaming services? Explain this in your report. Create a simple linear regression equation which can estimate the amount spent (this is the y variable) and your chosen variable from the previous step (this is the x variable). State your equation and include Excels Analysis Toolpak Regression output in your report. Interpret the slope of the model using appropriate units of measure. For example, the amount spent is in dollars per month. Test the slope of the model. Report the 95% confidence interval estimate of the slope and explain what it represents. Find the Coefficient of Determination, R2, for your regression model and interpret the meaning. Estimate the average amount spent on streaming services for customers with a profile below. Keep in mind your regression in this part has YOUR selected x variable, so you will only use one of the values listed below to estimate the cost of monthly streaming service. 40 years of age, with a household income of $2200, and with a family size of 3. Part 2 Heres a thought: Could you find a better regression model based on your data? How about considering a multiple regression model which predicts the average amount spent on streaming services using ALL the data. Use Excels Analysis Toolpak to create your multiple regression model output. State the multiple regression model clearly by using the names of your variables in your equation. COST = _________ + _________(AGE) + _________(INCOME) + ____________(FAMILY SIZE) Be sure to include the regression output in your report. Test each of the slopes using the t-test. List these and write your conclusion for each. List each slope and give the value, test statistic, p-value, and conclusion of your test. Coefficient Test Statistic p-value Conclusion Age Income Family Size 3. Test the overall module using the F-Test statistic and related p-value. What is your conclusion from this test? 4. Report the Multiple Coefficient of Determination, R2, and interpret the meaning. 5. Use this model to estimate the average amount spent on streaming services for the profile: 50 years of age, with a household income of $3000, and with a family size of 4 6. Decide which model is best, the simple linear from Part 1 or the multiple regression from Part 2. Justify your choice. Submit a Word document of your written report. Use complete sentences and thoughts. Consider writing a paragraph for each part of this assignment. Report in your Word document all your findings using the calculations, tables, and graphics from Excel. You can copy and paste from Excel into Word. Create a report worthy of submission to management. Submit your Excel file demonstrating your work using the appropriate Excel commands or Excel Calculators for full credit.