MGMT 250

Case 1

Instructions:

• Please read the following assignment and complete the tasks it assigns in the Excel template provided. Answers should be placed in the appropriate highlighted cells. Only Excel files will be accepted. Note that you will need to save this as a macro enabled file.

• Please name your case Case1_ when you save it, where the piece in <> is the last name(s) of the individuals who worked on the case

• All submissions must include the first and last names of the students in the group within the file itself

• All submissions are due to PLATO by the deadline indicated on PLATO

Background

Many college football powerhouses have sizeable football stadiums eight programs have stadiums that house more than 100,000 people. Each of these stadiums was costly to build and to maintain, with average costs of construction in excess of $100 million. One of the cornerstones of covering the costs of such an expense is to sell season tickets. There are many factors that go into convincing fans to purchase season tickets. And while the on-field product is one of the largest drivers of demand for these tickets, it is not the only one. (If you doubt the validity of this statement, just look at ticket sales for any sports team that has been perennially bad but still has solid season ticket numbers, like the Buffalo Bills.) Another major factor is the quality of the experience for fans inside the stadium. This covers things like accessibility of bathrooms, availability of programs, and the quality and cost of snacks.

The schools that operate these stadiums want to make sure fans are having a good experience, but they also want to make sure that the fan experiences are helping offset the cost of the stadiums. Therefore, the schools would like these experiences to generate enough revenue to be profitable. Given this, we will consider the concessions offered at Penn States Beaver Stadium. Each group will open and generate their own menu and costs for concessions at Beaver Stadium. The following is a sample of what this might look like, but please note that students should NOT be copying and pasting these numbers to use them. Students should complete their own with randomly generated numbers.

Below is a potential menu for the stadiums concession stand.

ITEM	Selling Price	Variable Cost	Percent of Revenue
Hot Dog	$3.00	$2.00	15%
Hamburger	$4.00	$2.50	15%
French Fries	$4.50	$3.25	20%
Pizza	$5.00	$4.00	5%
Nachos	$4.25	$2.75	10%
Pretzel	$2.75	$2.00	15%
Soda/Water	$3.00	$1.00	25%

And here is a sample of the fixed costs for operating food services over the seven home games in a season:

• $725,000 total yearly salary expense for managers for 18 food stands (covers all 7 games, and is not a per-game figure)

• 8,100 total square feet of stadium space rented out at $5.75/sq.ft. for each game (covers the square footage necessary to operate all 18 food stands, but must be rented for each game)

• Five workers at each of 18 food stands for 8 hours at PA minimum wage of $7.25/hour, for each of the seven home games

In previous examples, we have considered a single product and applied the total amount of fixed costs to just that one product. Here, since we have more than one product, we must treat the fixed costs differently. The method students should make use of here is allocating a portion of the fixed costs to each of the products listed above on the menu according to the percent of revenue they represent. For instance, if the total per game fixed cost was $100 (Hint: It will be much more than this), then the per-game fixed cost for Hot Dogs would be $100*15% = $15 (in this particular example).

Assignment Notes

To complete the tasks assigned below, you must first generate the data you will use. Navigate to the Answers worksheet, and click the Generate Data button to generate a unique dataset for your file (you will see that this fills in the relevant cells on the Manual sheet). In order to receive a grade for the assignment, your group must click the button to generate your own unique assignment. If your work is the same as another submission, both submissions will receive a grade of 0.

Note two other things. First, your answers to the questions below should all be placed in the appropriate cells on the Answers or Manual worksheets in the template provided. The correct location will be denoted by highlighted cells. Second, I have included the macro that will be used to grade your assignment. The macro itself requires a very high level of precision, which means you will need to use Excel to perform your calculations and then link to wherever these were done in order to have the macro grade your assignment as correct. Students should not be rounding or using rounded numbers in calculations.

The inclusion of the grading macro will allow you to check your work at any point along the way by clicking the Grade Assignment button*. Correct answers will be highlighted green, and incorrect answers will be highlighted red. As such, I encourage all of you to complete assignments in advance, so that you may check your work. That way, if you are unhappy with your performance, you have the opportunity to contact me via PLATO to learn what it is you did not know and fix it. Thus, this means the ability to achieve the grade you desire is well within your grasp, you need only recognize the responsibility lies solely at your own feet and take the initiative to succeed. Also, it means you need to complete the case file early enough that you can make the corrections necessary and have enough time to seek help if it is needed.

* Provided you have filled in your first and last name. The macro assigns a grade of 0 otherwise.

Please complete the following tasks:

In all cases, please place your answer in the appropriate highlighted cell(s) on the Answers or Manual sheets. Also, for any of the work involving numbers, you should be referencing your cells and not hard coding them. You should review the walkthrough videos for clarity on this point if you are confused.

• Given that there are 7 home games in a given season, please make use of the information provided in your file regarding fixed costs to calculate the total fixed costs for the season. This information is generated on the Manual sheet. To assess the cost of the square footage and the hourly employees, please assume that square footage costs $5.75/sq ft. and that each of the hourly workers at each of the 18 food stands will work 8 hour shifts at $7.25/hr. Also, remember that the space will need to be rented out for all 7 home games (not just one), and the employees will need to be paid for 7 home games-worth of hours. Note: The salary figure for the managers covers the entire season and has already been adjusted for all the games. No further adjustments to this figure are necessary to computer total yearly cost.

• Using your answer from number 1, please calculate the per-game total fixed cost. This figure will be necessary to calculate the per-game values for break-even point.

• Using the information from question 2 and the information in the table that you generated, assign the appropriate amount of per-game fixed cost to each of the items listed. That is, what is the correct dollar value allocation of the fixed costs for each menu item? The allocations are based on the percent of revenue (see the example above). Please place your answers in the relevant cells of column F on the Manual sheet.

• Use the information on fixed costs calculated above in conjunction with the other information in the table to calculate a breakeven point quantity for each item listed. In other words, how many of each item must be sold to cover the portion of fixed costs allocated to it? This may require you to review the model we covered in the video and the chapter. Please place your answers in the relevant cells of column G on the Manual sheet.

• Now that we have the breakeven quantity, use the selling price to show the dollar value of sales that is necessary to break even for each item. That is, how much revenue do we need to generate from each of the items listed in order to break even? Please place your answers in the relevant cells of column H on the Manual sheet.

• Calculate the total amount of revenue that the stadium must generate in order to break even on a per-game basis (sum the individual revenues, using the SUM function in Excel). Please place your answer in cell H12 on the Manual sheet.

• If Penn State averages attendance figures of 100,000 people, what is the total dollar amount that the average attendee must spend for the stadium to break even? That is, how much must each person spend so that the stadium breaks even? Place your answer on the Answers sheet.

• The stadium has an entire section dedicated to students, and this section holds approximately 40,000 individuals. This helps fill the stadium on game days with rowdy and rabid fans who will cheer loudly. However, one drawback of this is that students tend to have less disposable income relative to the other fans, and so they are much more likely to fill up on food and drink tailgating before the game starts. This means students will not purchase anything from the snack bar. In reality, students do buy snacks, but the percentage of those that do is not high. To be conservative with our estimates, we will assume that no students purchase anything from the snack bar. Given this, what must the average non-student fan spend for the stadium to break even on its food and drink? Place your answer on the Answers sheet.

Now that we have conducted this breakeven analysis by hand, we will repeat the steps using the Template provided in Excel. ***For each product on the menu***, please complete the following steps:

• Create a copy of the template for each menu item. You may want to name the sheets after whatever product you are looking at (i.e. naming the sheet examining the BEP for Hotdogs as Hotdogs), as this makes it easier to keep track of.

• Place the relevant information into the template so that it will calculate the breakeven points for you. Note that Revenue on the template means the revenue generated from selling each unit of the product (i.e the per-unit revenue). Also, note that revenue and profit are not the same. If you need a refresher on what composes revenues, please see the text/video.

• On the Manual sheet, you will see a profit target figure in cell D1. This is the profit figure from each product that the university hopes to earn. On your newly generated templates, make use of the Volume portion in conjunction with Excels Goal Seek application, found under the What-If Analysis option on the Data tab of the ribbon, to find out what quantity of the product must be sold to achieve that profit level. The video review posted will help you with this (as will Google) if you get stuck, or asking the instructor.

• Reference the relevant breakeven quantity and the quantity that yields your target profit level into the relevant cells on the Answers sheet. You should not copy/paste your values, as this is bad practice.