Assignment Details

You will be analyzing the "Colonial Broadcasting" case. This can be found in the course pack you needed to purchase from Harvard Business Review at the beginning of the term. If you still need to purchase it, instructions and a link can be found in the syllabus. Begin by reading the description in the case. Then, answer the questions listed below, NOT the questions listed in the case. Ignore everything in the case document after the end of page 4.

The executives at CBC have four main areas in which they are interested: They want to see how they are doing in ratings against the other networks and how the ratings will continue to change in the upcoming months. They also want to know if hiring stars makes a difference and the impact of fact-based programming compared to hiring stars.

Remember that your audience is the management of CBC. Therefore, make sure your presentation is professional. Once you present your results, you need to justify and explain your findings with properly stated conclusions.

1. Answer the following questions:
   1. What is the average rating for all CBC movies? How about ABN movies and BBS movies? Provide a bar chart that compares these averages.
   2. Include a table that shows the descriptive statistics using the data analysis tool pack in Excel for the ratings of the three networks (one column for each network).
   3. Comment on how the networks are performing, by comparing and interpreting the metrics in the descriptive statistics table. Your analysis must extend beyond simply comparing the average ratings for each network.
2. Create a scatter graph of the monthly average ratings for CBC for the year. Note that there are multiple ratings data for the months; you will need to calculate an average for each month first, and then plot the averages. After you create the graph, fit a linear trend line and a 2nd order polynomial trend line, displaying the formula and the r-squared. Explain to the executives if you can use this time series data to forecast the ratings of upcoming months. Which forecasting method is better? How accurate can you expect this forecast to be?
3. The executives wish to know if they should hire stars for their programs. To answer this question, run a hypothesis test to see if the ratings of shows with stars are higher compared to those without stars. Use the data for CBC movies only. Use 95% confidence. Your answer should include the following:
   1. The null and alternative hypotheses (state in full sentences).
   2. The test results: Run the test using Excel and include the output table. Use a t-test assuming equal variances.
   3. What is your recommendation to the executives? Justify your answer referring to the relevant figures.
4. Run a multiple regression where the dependent variable is ratings and the independent variables are star and fact. Use data from CBC only. CBC Management has several questions:
   1. Executives wish to know how much being fact-based or having one star contributes to a movies rating. What can you tell them about this?
      1. Are either, both, or neither of the independent variables significantly related to the ratings at 95% confidence? Justify your answers referring to the relevant figures.
   2. How well does this regression analysis explain the ratings? Should the executives count on the results from this analysis? Justify your answers referring to the relevant figures.

Directions for Submitting your Assignment:

For this project, you will prepare a PowerPoint presentation to present your findings. The first slide after the title slide should be an "Executive Summary." It should contain a very brief overview of all the answers. Your final slide should include "conclusions" and provide an overall assessment of the analysis.

Make sure you also submit the Excel file to show your work for Part 2.You will receive a 100-point reduction if you fail to include the Excel file showing your work.

Place all calculations for each of the questions on a separate worksheet. Then, using the results of your work from Excel, prepare PowerPoint slides to answer the questions in a presentation format.All relevant content should be on the slides; do not use the notes section or leave information in the Excel file. The executives reviewing the presentation should not need to switch to another document to see the required information.

The data you need is provided to you in theUnit 6 Excel filein Course Documents.Make sure to use that file. Do not type anything in manually or download anything from the Internet.

Rating Network Rating Network Rating CBC 14.7 BBS ABN CBC 15.6 ABN 14.6 10.8 ABN 9.8 CBC 11.3 14.405 Mean 16.01842105 Mean 13.59 14.1 ABN 16.2 CBC 13.5 Mean Standard Error 0.325341554 16.8 ABN 12.8 CBC 12.9 Standard Error 0.411254499 Standard Error 0.501276539 Median 15.8 Median 13.2 14.3 ABN 16 CBC 13.2 Median 14.4 Mode 13.5 17.1 18.9 CBC 12.8 Mode 15.6 Mode 12.8 ABN 15.8 ABN 17.6 CBC 13.2 Standard Deviation 1.839186033 Standard Deviation 3.090076118 Standard Deviation 1.781969077 13.5 Sample Variance 3.382605263 Sample Variance 9.548570413 Sample Variance 3.175413793 16.2 ABN 19.5 CBC 16.9 CBC 17.5 Kurtosis 0.382865613 Kurtosis 4.831522253 Kurtosis 2.154155524 12.6 ABN 16.3 CBC 11.6 Skewness 0.559459006 Skewness 1.545888095 Skewness 1.359901282 13.5 ABN 17.6 Range 7.8 15.6 ABN 15.8 CBC 12.1 Range 6.3 Range 10.8 Minimum 9.8 Minimum 11.3 12.1 ABN 17.1 CBC 15.6 Minimum 17.1 Maximum 27.4 Maximum 19.1 15.8 ABN 15.8 CBC 12.6 Maximum 288.1 Sum 608.7 Sum 407.7 15 ABN 16 CBC Sum 12.3 Count 20 Count 38 Count 30 16.3 ABN 27.4 CBC 13.3 ABN 14.2 CBC 14.2 10.8 ABN 18.1 CBC 11.6 14.4 ABN 15.2 CBC 13.3 la) 14.4 ABN 19.8 CBC 13.6 The Average rating for CBC movies is 13.59 13.6 ABN 15.5 CBC 12.4 The average rating for ABN movies is 16.07 ABN 16.6 CBC 13.8 The average rating for BBS movies is 14.405 ABN 16.3 CBC 11.9 ABN 23.8 CBC 14.6 1b) ABN 16.8 CBC 15.8 AVERAGE ABN 15.4 CBC 15.4 MOVIES RATING FACT STARS ABN 14.7 CBC 13.1 BBS 14.4 0.5 0.55 ABN 15.5 CBC 12.8 ABN 16 0.3421 0.4737 ABN 14.7 CBC 15.1 CBC 13.6 0.4667 0.3667 ABN 15.9 CBC 12.4 ABN 13.8 CBC 19.1 From the data of table, it fair to say that ABN has the highest rating. However, BBS has the highest fact with an average ABN 14.9 of 0.5 and 0.55 stars rating comparing to CBC with the lowest rating of 13.43 and 0.3667 lowest stars . ABN 12.9 Lastly , CBC is doing much better than ABN on facts with an average of 0.4667 comparing to ABN fact average of 0.3421 ABN 15.4 The Mean shows ABN is doing best in the rating with an average rating of 16 compared to 14.4 and 13.6 repectively for BBS and ABN 14.5 CBC. From the Ranges and Standard Deviations we can also see that ABN has the most consistent ratings. Based on these results ABN 12.6 I can conclude that the ABN networking is the best overall. ABN 11.8 ABN 12.8 ABN 16.8Network Month Rating Month Average CBC 14.7 1 13 Average Monthly Rating BC 11.3 IN 13.2 13.5 18 BC W 13 BC 12.9 13.35 16 CBC 13.2 11.85 14 age . . . . . ............. CBC 12.8 6 13.33 .... . . . .......: 12 CBC 13.2 7 13.25 CBC 13.5 8 12.83 y = 0.1436x + 12.421 CBC 17. 12.7 8 R = 0.2496 CBC 11.6 10 15.27 6 BC 12.1 11 12.95 A -BC 15.6 12 15.53 N CBC 12.6 BC 1 1.8 0 0 10 12 14 BC 12.3 2 6 8 CBC 14.2 Month BC 11.6 CBC LD LD 00 CO CO 13.3 CBC 13.6 SUMMARY OUTPUT CBC 12.4 BC 13. Regression Statistics CBC 11.9 Multiple R 0.499616235 CBC 10 14.6 R Square 0.249616382 CBC 10 15.8 Adjusted R Square 0.174578021 CBC 10 15.4 Standard Error 0.941754166 CBC 11 13.1 Observations 12 CBC 11 12.8 CBC 12 15.1 ANOVA CBC 12 12.4 df SS MS F Significance F CBC 12 19.1 Regression 2.950290909 2.950291 3.326517 0.098153741 Residual 10 8.869009091 0.886901 Total 11 11.8193 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% ower 95.09 Upper 95.0% Intercept 12.42136364 0.579609622 21.43057 1.09E-09 11.12991292 13.71281435 11.12991 13.71281435 Month 0.143636364 0.07875344 1.823874 0.098154 -0.031837235 0.319109962 -0.03184 0.319109962 b ) The equation of the trend line is y = 0.1436x + 12.421, and the R-squared value is 0.2496 This R-squared value indicates that less than 25% of the variation in the monthly average rating is explained by the variation in the month. As a result, the equation cannot be used to forecast future ratings, and any such forecast would be unlikely to be accurate.CBC movies CBC movies Rating with Stars Ratings Without stars 14.7 11.3 a) HWS = Movie with a star 13.5 12.9 HNS = Movie without a star 13.2 13.2 The Null Hypothesis: There is no significant between the ratings of 13.5 12.8 the movies with stars and movies without stars. This can be expressed as HO: UNS= HWS 12.6 17.5 14.2 11.6 12.4 12.1 15.8 15.6 The Alternative hypothesis: There is significant different between the ratings of the 15.4 11.8 movies with stars and movies witout stars. This can be expressed as HO: HANS # HWS. 13.1 12.3 12.4 11.6 13.3 b) t-Test: Two-Sample Assuming Equal Variances 13.6 13.8 Rating with Stars Ratings Without stars 11.9 Mean 13.70909091 13.52105263 14.6 Variance 1.382909091 4.333976608 12.8 Observations 11 19 15.1 Pooled Variance 3.280023923 19.1 Hypothesized Mean Differen 0 df 28 t Stat 0.274043985 P(T