• Write the Excel formula for each cell marked with ?? in column B of figure 1, and label each formula clearly.
• List all the constraints in terms of the cell positions in figure 1.

A construction company has been contracted by a major league football team to build a new football stadium with a car parking lot. The teams current stadium was built for both baseball and football, and it has an enclosed oval shape, with seating around the entire circumference. Football crowds can fill the stadium to full capacity, but baseball games only fill the stadium to 70% of the total capacity. Thus the new stadium will be for football games only.

It is decided that the new stadium will be U-shaped, like a horseshoe. Seating would be all around the U, but there would be no seat at the open end. The construction engineers have calculated the length around the U (leaving out the open end part) as 1375 feet, and plan to build 100 rows of seats that extend back and up from the playing field. In fact, the geometry of the stadium is such that the row closest to the playing field (1^st row) is shorter in length than the row farthest from the playing field (100^th row). However, some of those rows are broken by equipments, exits, and concessions. Thus to simplify calculations for this study, you can assume that each of the 100 rows in the new stadium is 1375 feet in length. The construction engineers plan to install four kinds of seats:

• Box seats are the rows of seats closest to the playing field and are usually considered the most desirable and expensive.
• Red seats are the rows of seats in the next level up.
• Blue seats are the rows of seats in the level above the red seats.
• Yellow seats are the farthest rows up and away from the playing field, and they are the least desirable and cheapest.

The construction company needs to know the optimal number of seats for each type of seats to build in the new football stadium so that the total revenue would be maximized in the forecast plan, which is done in a Microsoft Excel spreadsheet using Excels Solver. You are required to quantify the financial impacts of different stadium construction alternatives by writing Excel formulae and constraints to help with the calculations of this problem see the spreadsheet (in figure 1 below). Microsoft Excels Solver will calculate the number of seats to be built in cells B4 to B7 (changing cells) for each kind of seat.

Figure 1

Figure 1 - continued

The Constants section of the spreadsheet (see cells A9 to cells B38) contains the following data:

• The average length of a row of seats is 1375 feet (cell B10).
• The total number of rows in the stadium is 100 (cell B15).
• Major football league rules state that the stadium must have a minimum number of 44000 seats (cell B36).
• The team management has set a maximum of 46000 (cell B37) for the total number of seats.
• The seat types differ by more than just paint colour and their positions in the stadium. The most expensive seats will be the widest and therefore, the most comfortable. The least expensive seats will be the narrowest and therefore, the least comfortable. This means the number of seats in a Box seat row will be fewer than the number of seats in a Red seat row and so on. The width of seats and the admission ticket price for each seat type are shown in below table:

Seat Type	Seat Width (in feet)	Price($) per admission ticket
Box seat	3.5 (cell B11)	50 (cell B16)
Red seat	3.25 (cell B12)	35 (cell B17)
Blue seat	3 (cell B13)	25 (cell B18)
Yellow seat	2.75 (cell B14)	20 (cell B19)

• People who come to watch ballgames usually buy souvenirs and food. In fact, many fans spend more money on food and souvenirs than they do on their admission tickets. The teams research department has determined that there is a correlation between where fans sit and how much they spend on souvenirs and food. The table below shows the average expenditures by each seat type per game:

Seat Type	Average expenditures on souvenirs and food per seat per game
Box seat	30 (cell B20)
Red seat	25 (cell B21)
Blue seat	15 (cell B22)
Yellow seat	10 (cell B23)

• The new stadium will need a parking lot that can accommodate 15000 cars (cell B38). The teams researchers have studied how fans get to the stadium. They have found that the less-affluent fans are more likely to use public transportation rather than drive a car to the game. Their research reveals the following averages:

5. For every 2 fans sitting in Box seats (cell B32), 1 car arrives at the stadium.
6. For every 3 fans sitting in Red seats (cell B33), 1 car arrives at the stadium.
7. For every 3 fans sitting in Blue seats (cell B34), 1 car arrives at the stadium.
8. For every 5 fans sitting in Yellow seats (cell B35), 1 car arrives at the stadium.

The team management would like a balance among the different seating types. The minimum and maximum number of rows for each kind of seat is shown in below table:

Seat Type	Minimum number of rows	Maximum number of rows
Box seat	10 (cell B24)	30 (cell B28)
Red seat	10 (cell B25)	40 (cell B29)
Blue seat	10 (cell B26)	40 (cell B30)
Yellow seat	10 (cell B27)	50 (cell B31)

The Calculations section of the spreadsheet (see cell A41 to cell B72) contains immediate results that will be used in the income statement of the spreadsheet, and/or will be used as constraints. Write Excel formulae for the following:

• Number of seats per row for each seat type Box, Red, Blue, Yellow (cells B42 to cell B45) is a function of the length of the row and of the width of seats in each row. Since a fraction of a seat cant be built, the number of seats per row for each seat type must be a whole number, thus leaving some spare space in each row of seats.
• Total number of seats in each seat type Box, Red, Blue, Yellow (cells B46 to B49) is a function of the number of seats per row for each seat type and the number of rows for each seat type.
• The total number of seats for all seat types (cell B50) in the stadium.
• The number of cars arriving at the stadium for a match for each seat type Box, Red, Blue, Yellow (cells B51 to B54) is the function of the number of fans per car for each seat type and the number of seats for each seat type. Since a fraction of a car does not exist, the number of cars for each seat type must be a whole number, thus leaving some spare space in the parking lot.
• Total number of cars arriving at the stadium for a match for all seat types (cell B55).
• Total number of rows to be installed in the stadium (cell B56).
• Total number of rows to be installed for Red and Blue seats (cell B57).

The Revenue section of the spreadsheet (see cell A61 to cell B72) contains the total revenue that the Solver is expected to maximize. Write Excel formulae for the following:

• Total ticket sales revenue for each seat type Box, Red, Blue, Yellow (cells B62 to B65) is a function of the ticket price for each seat and the number of seats for each seat type.
• Total revenue from all tickets of all seat types (cell B66).
• Total revenue gained from selling food and souvenirs for each seat type Box, Red, Blue, Yellow (cell B67 to B70).
• Total revenue gained from selling food and souvenirs for all seat types (cell B71).
• Total revenue from ticket sales and from selling food and souvenirs for all seat types (cell B72).

The team managements goal is to maximize the total revenue subject to various constraints, with the assumption that all seats admission tickets will be sold for each match. Formulate constraints from the following statements that are made by the teams management:

The total number of rows in the stadium must be 100 (cell B15). Major football league rules state that the stadium must have a minimum number of 44000 seats (cell B36). However, team management wants the stadium to appear intimate, so it is decided that the stadium should not be able to seat more than 46000 fans (cell B37). The team management does not want the new stadium to have a massive cheap seats area. Thus it is decided that the number of rows of Red seats plus the number of rows of Blue seats combined, must be greater than or equal to the number of rows of Yellow seats. For the car parking lot, it is decided that more space should be allocated to the seating area, and thus the car parking lot should accommodate at most 15,000 cars (cell B38). The team management would also like a balance among the different seating types. The minimum and maximum number of rows for each kind of seat are shown in below table:

Seat Type	Minimum number of rows	Maximum number of rows
Box seat	10 (cell B24)	30 (cell B28)
Red seat	10 (cell B25)	40 (cell B29)
Blue seat	10 (cell B26)	40 (cell B30)
Yellow seat	10 (cell B27)	50 (cell B31)

Finally, fractional part for the number of rows for each seat type is not allowed, as this is an integer problem.