• 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 team's 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^strow) is shorter in length than the row farthest from the playing field (100^throw). 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 Excel's 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 Excel's Solver will calculate the number of seats to be built in cells B4 to B7 (changing cells) for each kind of seat.

A B 61 REVENUE: 62 TICKET SALES: BOX ?? [1 mark] 63 TICKET SALES: RED ?? [1 mark] 64 TICKET SALES: BLUE ?? [1 mark] 65 TICKET SALES: YELLOW ?? [1 mark] 66 TOTAL TICKET REVENUE ?? [1 mark] 67 REVENUE FROM SOUVENIR & FOOD SALES: BOX ?? [1.5 marks] 68 REVENUE FROM SOUVENIR & FOOD SALES: RED ?? [1.5 marks] 69 REVENUE FROM SOUVENIR & FOOD SALES: BLUE ?? [1.5 marks] 70 REVENUE FROM SOUVENIR & FOOD SALES: YELLOW ?? [1.5 marks] 71 TOTAL REVENUE FROM SOUVENIR & FOOD SALES: ?? [1 mark] 72 TOTAL REVENUE ?? [1 mark] 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:--3- n CONSTANTS: m NUMBER OF FEET IN A ROW WIDTH PER SEAT: BOX WIDTH PER SEAT: RED WIDTH PER SEAT: BLUE In WIDTH PER SEAT: YELLOW NUMBER OF ROWS PRICE PER TICKET: BOX PRICE PER TICKET: RED PRICE PER TICKET: BLUE In PRICE PER TICKET: YELLOW Avg. Expenditures on Souvenirs & Food per seat per game: BOX Avg. Expenditures on Souvenirs & Food per seat per game: RED Avg. Expenditures on Souvenirs & Food per seat per game: BLUE Avg. Expenditures on Souvenirs & Food per seat per game: YELLOW MIN # Rows: Box MIN # ROWS: RED MIN # Rows: BLUE MIN # Rows: YELLOW MAX # Rows:Box MAX # ROWS:REDS MAX # ROWS:BLUE MAx # ROWS:YELLOW NUMBER OF PEOPLE PER CAR:BOX NUMBER OF PEOPLE PER CAR:RED NUMBER OF PEOPLE PER CAR:BLUE NUMBER OF PEOPLE PER CAR:YELLOW MIN ATTENDANCE MAx ATTENDANCE NUMBER OF PARKING PLACES 15000 Figure 1- A B 41 CALCULATIONS: 42 NUMBER OF SEATS PER ROW-BOX ?? [1.5 marks] 43 NUMBER OF SEATS PER ROW-RED ?? [1.5 marks] 44 NUMBER OF SEATS PER ROW-BLUE ?? [1.5 marks] 45 NUMBER OF SEATS PER ROW-YELLOW ?? [1.5 marks] 46 NUMBER OF BOX SEATS ?? [1 mark] 47 NUMBER OF RED SEATS ?? [1 mark] 48 NUMBER OF BLUE SEATS ?? [1 mark] 49 NUMBER OF YELLOW SEATS ?? [1 mark] 50 TOTAL NUMBER OF SEATS ?? [1 mark] 51 NUMBER OF CARS - BOX ?? [1.5 marks] 52 NUMBER OF CARS - RED ?? [1.5 marks] 53 NUMBER OF CARS - BLUE ?? [1.5 marks] 54 NUMBER OF CARS - YELLOW ?? [1.5 marks] 55 TOTAL NUMBER OF CARS ?? [1 mark] 56 TOTAL NUMBER OF ROWS ?? [1 mark] 57 NUMBER OF RED + BLUE SEATS ?? [1 mark]Box seat 30 (cell B20)