Capital State University (CSU) is a leading Midwest University with a strong collegiate football program. Kris Stetzel serves as CSU's Associate Athletic Director for External Affairs.
His job responsibilities include negotiating with commercial vendors for services such asconcessions at sporting events, event staff and security, and game-day hospitality. Kris brokersdeals for corporate sponsorship of CSU athletic programs and arranges for radio and televisioncoverage of CSU athletic events. Kris also manages CSU sports advertising and marketing andsports information-related media relations for print, radio, television, and online.Recently, Kris has been examining CSU's business arrangement with the publishingcompany that prints the game-day sports magazines for CSU home football games. As pa 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 ofinformation 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 footballgame, that states how many magazines CSU wants for each home game of the season. Thepublishing company prints the magazines and ships all magazines to CSU prior to the firstgame 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 infuture weeks. In some weeks in previous football seasons, demand surpassed supply andCSU ran out of football magazines. Currently, CSU determines order quantities for eachhome game by looking at the past season's order quantities and then adjusting this amountup or down based on a gut feeling on how popular the current season's game would be incomparison to games in the previous season.
Kris believes that it should be possible to improve this ordering process. He has locateddata from the past nine football seasons. Kris has information on the following variablesfor each home game: the number of magazines sold, the year the game took place, theweek that the game took place during the season, the opponent's preseason ranking, thenumber of preseason tickets sold for that game, the total game attendance, CSU's preseason rank, the number of the home game within CSU's season, whether or not the game wasan in-conference game for CSU, whether or not the game was Homecoming for CSU, thegame-day weather, the game-day kickoff temperature, the number of wins and losses forCSU's opponent in the previous season, and the number of wins and losses for CSU in theprevious season. These data are in the file MagazinesCSU; Table 20.1 displays the data forYears 1 and 2. Kris also noted that the CSU game in Week 1 of Year 8 was somewhat of ananomaly because CSU wore special throwback uniforms to honor the players from theironly National Championship season, which greatly increased attendance at that game.

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 orderamount 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 ofmav be useful for your purposes here. Are there variables contained in the file Mazazinessthat you would exclude from a forecast model to determine football magazine sales in Year10? if so, why? Are there particular observations of football magazine sales from previousyears 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 theaverage sales of football magazines for each of the seven home games in the upcomingseason (Year 10). That is, you should construct a single regression model and use it toestimate the average demand for the seven home games in Year 10. In addition to thevariables provided, you may create new variables based on these variables or basedon observations of your analysis. Be sure to provide a thorough analysis of your finalmodel (residual diagnostics) and provide assessments of its accuracy. What insights areavailable based on your regression model?

3. Use the forecasting model developed in Part 2 to create a simulation model that Kriscan 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 ateach 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.

4. Kris has noticed that of the typical 60 pages in a football magazine, 45 of that 60 pages are the same for every game in a season. Only the 15 pages that discuss the weeklyopponent change from week to week. CSU's publisher has indicated that it is possiblefor CSU to order generic game-day football magazines in the July preceding the season.
This generic magazine contains 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 withinserts specific to that week's game, along with a book jacket cover displaying players and coaches from the two teams playing that week. The number of game-specificinserts and book jacket covers can be determined closer to the actual games in order toallow for a more accurate forecast.
Thus, the simulation model developed in Part 3 effectively represents the salesamount for the generic magazine, and then CSU would order the game-specific insertsand book jacket covers much closer to the actual games when they have a much moreaccurate forecast of attendance and sales. However, Kris still is not sure how manygeneric 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 colt-tract, if Kris orders 21,500 generic magazines in July, what are the estimated expectedcosts of lost sales (football magazines that CSU does not sell because they fia out) andunsold magazines (football magazines that CSU must send back to the publisher at theend of the season)?

S. Assuming that CSU can tailor the specific magazines for each game in Year 10 at a laterdate, what is the optimal order amount for Kris to place in July prior to Year 10 for thegeneric magazines? The optimal order amount should minimize the total expected loss!sales and unsold magazines cost in Year 10? Assume that Kris must order in batches of500 magazines.

Answer rating: 100% (QA)

1 When analyzing the data in the file MagazinesCSU there are certain variables and observations that may not be useful for forecasting football magazine sales in Year 10 and should be excluded from th... View the full answer