The data you need for Part 2 is provided in the following Excel file. Be sure to use this file and do not enter anything manually from the case or use other sources for the data.

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. line 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?
   2. 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.
   3. 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, 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 from Excel, 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

\fColonial Broadcasting Company Part A Barbara Warrington, VicePresident of Programming at Colonial Broadcasting Company (CBC), sat in her ofce preparing for a meeting with Bruce Gold, an independent movie producer. Warrington thought that Gold would probably try to sell to CBC a movie idea based on the true story of a young boy who overcomes a debilitating illness. In recent years, the number of TV movies based on real-life events (fact-based movies) had been steadily growing. The networks seemed to believe that these movies brought higher ratings than fictional movies. But Warrington wasn't so sure. She suspected that other factors could be responsible for the high ratings of fact-based movies. Warrington had data on TV movies that were broadcast during 1992. She decided to have one of her assistants run some regressions to find out what was really driving ratings. Colonial Broadcasting Company Along with American Broadcasting Network (ABN) and Bellmore Broadcasting Service (BBS), Colonial Broadcasting Company is one of three major American television networks.1 Every week, each network broadcasts hundreds of hours of national programming, including news, sports, talk shows, as well as prime-time programming (theatrical and made-for-television movies;2 action, comedy, and drama series; news specials, etc.).3 Broadcasting TV Movies TV movies were first broadcast in the mid19605, and in the following decades came to play a major part in network programming. By 1992, the three major networksABN, BBS, and CBC 1- A fourth network, Derby Television Network (DTN), has some highlyrated national shows and is an important competitor to the three majors. However, since DTN does not have daily national prime time programming, it is not considered in this case. 2- Theatrical movies are originally released in theatres and are shown on television several years after theatrical release. In contrast, madefortelevision or TV movies are made explicitly for television. 3' Prime time runs from 811 p.m. on the East and West Coasts and from 710 p.m. in the Central States. The TV season runs from September to May. Thus, the three networks jointly broadcast approximately 2,300 hours of primetime programming annually. were underwriting the production costs for more than two hundred hours of TV movies annually, approximately 10% of network prime time programming. The typical TV movie is made by an independent producer, with the cost underwritten by one of the networks. It is approximately 92 minutes long, but with commercials, it runs for two hours. TV movies are broadcast during prime time, usually beginning at 9 pm. on the East and West Coasts and at 8 p.m. in the Central States. The networks' aim in broadcasting TV movies is to attract a large and demographically desirable audience. Networks are funded by advertisers who pay for on-air advertising time, and advertisers pay higher prices for programs which attract larger audiences. A network's success in attracting a large audience is reflected in its Nielsen ratings.4 These ratings, expressed as a percentage of all American households with televisions, measure how many televisions are turned on to particular programs. In 1992, each rating point represented 921,000 American households. Therefore, if a movie received a 25 rating during a given half-hour time slot, about 23 million households were tuned in to that movie at that time. The TV movie with the highest rating on record is The Day After (a movie about the aftermath of a nuclear holocaust) which garnered a rating of 46 when broadcast in 1983. The broadcast of the 1992 Super Bowl, by comparison, received a Nielsen rating of 40. Making a TV Movie The networks do not produce their own TV movies but instead contract with independent producers to have them made. Producers must first sell a movie concept to a network. The concepts the producer has to choose from fall into two basic categories: those drawn from fact and those drawn from fiction. A typical fact-based concept for a TV movie might be drawn from national or regional newspapers or a nonfiction book. On the other hand, a fictional movie might be based on a novel, play, screenplay, or simply the producer's brainstorming. If necessary, the producer must arrange for an option on the story rights. If the movie concept is based on a truelife occurrence, the option takes the form of an agreement between the producer and the rights holder, usually the people involved in the real-life events. In contrast, for novels, plays, and screenplays, the producer merely needs to get an option on the appropriate copyright. In most cases, a fee is paid for an exclusive option. The option then gives the producer the right to buy a piece of material within a specified period of time (e.g., the producer might pay $10,000 for a oneyear option to buy the rights to a story for an additional $100,000). Once the producer has the storyrights, he can approach the networks and pitch his concept for the movie. The networks have several basic criteria for judging potential TV movies. Unlike television series, for which audience loyalty can be built over the course of a season or even many seasons, TV movies are usually a oneshot deal. Ideally, network executives believe, TV movies should be "'believable' and sensational at the same time Characters should be simple and simply motivated, heroes familiar, stories full of conict, endings resolved, uplift apparent, and each act should end on a note of suspense sufficient to carry the viewer through the commercial break."5 4- A. C. Nielsen Company is a major research firm which provides data on ratings and marketshare for prime time programming based on information gathered from sample households throughout the continental United States. 5' Todd Gitlin, Inside Prime Time (New York: Pantheon Books, 1983): 161, 165. 2 If a movie concept generates enough interest from the network, the network will pay a scriptwriter to develop the idea into a full-length script. If the script is acceptable, then the network will commit to produce the movie. Typically, the network and production company agree on a licensing fee (usually $2.60 to $2.75 million which covers the bulk of the production costs) for the network's exclusive North American right to broadcast the movie twice in four years. The licensing fee covers the bulk of the producer's production costs.6 Warrington's Decision Warrington knew that CBC's programming decisions were motivated primarily by ratings. She made a mental list of factors that might affect a TV movies' ratings: the day of the week or month it was broadcast, the broadcasting network, whether the movie had a bigname star, whether it was scheduled against tough competition, or whether the program immediately before it on the same network had high or low ratings. Was the movie conceptfact-based or fictionalone of the factors that drove ratings? Warrington looked down at the regressions which her assistant had brought in (see Exhibits 1 and 2). What did they tell her? 5- The licensing fee paid by the network usually falls about $400,000 short of actual production costs. After the network broadcasts the movie, the rights revert to the production company. The production company covers the shortfall with national and international fees for theater and television syndication and video cassette release. Nielsen ating for VIOUS RATING rating the mov ressions Which to T NETWORK INETWOR DEC