Case Problem: Capital State University Game-Day Magazines This case draws on material from Chapters 2, 3, 7, and 11. Capital State University (CSU) is a leading Midwest University with a strong collegiate football program. Kris Stetzel serves as CSUs Associate Athletic Director for External Affairs. His job responsibilities include negotiating with commercial vendors for services such as concessions at sporting events, event staff and security, and game-day hospitality. Kris brokers deals for corporate sponsorship of CSU athletic programs and arranges for radio and television coverage of CSU athletic events. Kris also manages CSU sports advertising and marketing and sports information-related media relations for print, radio, television, and online. Recently, Kris has been examining CSUs business arrangement with the publishing company that prints the game-day sports magazines for CSU home football games. As part of a recent comprehensive university-wide sports media contract, CSU has a new publishing agreement with its print vendor. The magazines typically contain about 60 pages of information on the CSU football team and its opponent for that week. The magazines are sold at vendor stands positioned outside of CSUs football stadium. Currently, CSU places one order in July, several months prior to the first home football game, that states how many magazines CSU wants for each home game of the season. The publishing company prints the magazines and ships all magazines to CSU prior to the first game of the season. From data collected in past football seasons, Kris knows that CSU is often off by a considerable amount in their order quantities. Most weeks, CSU has many leftover magazines, but because the magazines are tailored to each home opponent, they cannot be resold in future weeks. In some weeks in previous football seasons, demand surpassed supply and CSU ran out of football magazines. Currently, CSU determines order quantities for each home game by looking at the past seasons order quantities and then adjusting this amount up or down based on a gut feeling on how popular the current seasons game would be in comparison to games in the previous season. Kris believes that it should be possible to improve this ordering process. He has located data from the past nine football seasons. Kris has information on the following variables for each home game: the number of magazines sold, the year the game took place, the week that the game took place during the season, the opponents preseason ranking, the number of preseason tickets sold for that game, the total game attendance, CSUs preseason rank, the number of the home game within CSUs season, whether or not the game was an in-conference game for CSU, whether or not the game was Homecoming for CSU, the game-day weather, the game-day kickoff temperature, the number of wins and losses for CSUs opponent in the previous season, and the number of wins and losses for CSU in the previous season. These data are in the file MagazinesCSU; Table 20.1 displays the data for Years 1 and 2. Kris also noted that the CSU game in Week 1 of Year 8 was somewhat of an anomaly because CSU wore special throwback uniforms to honor the players from their only National Championship season, which greatly increased attendance at that game. Details Kris has also done some investigation into the costs associated with ordering magazines from CSUs publisher. Under the current CSU contract with the publisher, CSU must determine order amounts for each upcoming home game by July. CSU pays $14.00 for each magazine that they order. Vendors then sell magazines at each CSU home football game for $25.00. CSUs agreement with the vendors states that CSU pays the vendor $2.50 for each magazine sold and keeps the remaining revenue. The current contract with the publisher states that the publisher must buy back any unsold magazines from CSU for $11.50. Managerial Report Use the concepts you have learned from Chapters 2, 3, 7, and 11 to write a report that will help Kris analyze football magazine sales in Years 1 through 9 to determine an order amount for Year 10. You should address each of the following in your report. There is a considerable amount of data available in the file MagazinesCSU, but not all of it may be useful for your purposes here. Are there variables contained in the file MagazinesCSU that you would exclude from a forecast model to determine football magazine sales in Year 10? If so, why? Are there particular observations of football magazine sales from previous years that you would exclude from your forecasting model? If so, why? Based on the data in the file MagazinesCSU, develop a regression model to forecast the average sales of football magazines for each of the seven home games in the upcoming season (Year 10). That is, you should construct a single regression model and use it to estimate the average demand for the seven home games in Year 10. In addition to the variables provided, you may create new variables based on these variables or based on observations of your analysis. Be sure to provide a thorough analysis of your final model (residual diagnostics) and provide assessments of its accuracy. What insights are available based on your regression model? Use the forecasting model developed in Part 2 to create a simulation model that Kris can use to estimate the total football magazine sales amounts in Year 10. Your simulation model should have seven uncertain inputs: one input for football magazine sales at each CSU game in Year 10. Then you should sum these sales amounts for each individual game to create a total football magazine sales amount for Year 10. Kris has noticed that of the typical 60 pages in a football magazine, 45 of those 60 pages are the same for every game in a season. Only the 15 pages that discuss the weekly opponent change from week-toweek. CSUs publisher has indicated that it is possible for CSU to order generic game-day football magazines in the July preceding the season. This generic magazine contains the 45 pages of material that is the same for each game. Closer to the week of each game, CSU could then tailor the generic magazine with inserts specific to that weeks game, along with a book jacket cover displaying players and coaches from the two teams playing that week. The number of game-specific inserts and book jacket covers can be determined closer to the actual games in order to allow for a more accurate forecast. Thus, the simulation model developed in Part 3 effectively represents the sales amount for the generic magazine, and then CSU would order the game-specific inserts and book jacket covers much closer to the actual games when they have a much more accurate forecast of attendance and sales. However, Kris still is not sure how many generic magazines he should order. Should he order exactly the forecasted amount from Part 3? More? Less? Why? Based on the cost values described from the publishing contract, if Kris orders 21,500 generic magazines in July, what are the estimated expected costs of lost sales (football magazines that CSU does not sell because they run out) and unsold magazines (football magazines that CSU must send back to the publisher at the end of the season)? Assuming that CSU can tailor the specific magazines for each game in Year 10 at a later date, what is the optimal order amount for Kris to place in July prior to Year 10 for the generic magazines? The optimal order amount should minimize the total expected lost sales and unsold magazines cost in Year 10? Assume that Kris must order in batches of 500 magazines.