Case Problem: Capital State University Mis has also done some investigation into the costs associated with ordering magazines Hanov from CSU's publisher. Under the current CSU contract with the publisher, CSU mush determine order amounts for each upcoming home game by July. CSU pays $14.00 for each zine that they order. Vendors then sell magazines at each CSU home football gam P23.00. CSU's 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. This case draws rial from Chapter Managerial Report and 13 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 I through 9 to determine an order amount for Year 10. You should address each of the following in your report. 1. 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? 2. 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 DATA on observations of your analysis. Be sure to provide a thorough analysis of your final Hanov model (residual diagnostics) and provide assessments of its accuracy. What insights are available based on your regression model? 3. 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 simula- tion 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 individ- ual game to create a total football magazine sales amount for Year 10. 4. Kris has noticed that of the typical 60 pages in a football magazine, 45 of those 60 pag- es are the same for every game in a season. Only the 15 pages that discuss the weekly opponent change from week-to-week. CSU's 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 week's game, along with a book jacket cover displaying play- ers 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 con- tract, 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 beca e they risi out ) and unsold magazines (football magazines that CSU must send back to the publicher at the end of the season)? 5. 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. DATE on conta ADE 06/3 our reba operv VisionP hrough stic neut reba onPI he padlCase Problem: Capital State University Game-Day Magazines This case draws on mate- Capital State University (CSU) is a leading Midwest University with a strong collegiate foot- and 11. rial from Chapters 2, 3, 7, ball program. Kris Stetzel serves as CSU's 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 CSU's 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 publish- ing 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 CSU's 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 con- siderable 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 season's order quantities and then adjusting this amount up or down based on a gut feeling on how popular the current season's 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 opponent's preseason ranking, the number of preseason tickets sold for that game, the total game attendance, CSU's presea- son rank, the number of the home game within CSU's 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 CSU's 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 DATAfile anomaly because CSU wore special throwback uniforms to honor the players from their MagazinesCSU only National Championship season, which greatly increased attendance at that game. TABLE 20.1. Portion of Data Available for Use in Determining How Many CSU Football Magazines to Order Homecoming Tomason Home Game Conference Game (1= Yos 0 Kickon Previous Season Previous Seaso CSU's Previous CSU's Previous Total Game on Season Number of Season Number of nOpponent Preseason Year Season Preseason Rank Ticket Sales Number (1 = Yes 0 = No) Weather Number of Wins Number of Losses Sales (Units 47, 420 Cedar Falls University 120 Oklahoma A&M 47 420 Urbana College versity of Bloomington JaggU NNNNN~ Indiana A&M Minneapolis State University W 80 8 8 8 - 8 9 8 Mi Pleasant College University of Ames Ann Arbor University 2 8 R Evanston University Madison University Columbus University