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 scats 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 (1st row) is shorter in length than the row farthest from the playing field (100th 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 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. B 3 CHANGING CELLS: 4 NUMBER OF BOX ROWS 5 NUMBER OF RED ROWS 6 NUMBER OF BLUE ROWS 7 NUMBER OF YELLOW ROWS 1 1 1 1 B 1375 3.5 3.25 3 2.75 100 50 35 9 CONSTANTS: 10 NUMBER OF FEET IN A ROW 11 WIDTH PER SEAT: BOX 12 WIDTH PER SEAT: RED 13 WIDTH PER SEAT: BLUE 14 WIDTH PER SEAT: YELLOW 15 NUMBER OF ROWS 16 PRICE PER TICKET: BOX 17 PRICE PER TICKET: RED PRICE PER TICKET: BLUE 19 PRICE PER TICKET: YELLOW 20 Avg. Expenditures on Souvenirs & Food per seat per game: BOX 21 Avg. Expenditures on Souvenirs & Food per seat per game: RED 22 Avg. Expenditures on Souvenirs & Food per seat per game: BLUE 23 Avg. Expenditures on Souvenirs & Food per seat per game: YELLOW 24 MIN # ROWS: BOX 25 MIN # ROWS: RED 26 MIN #ROWS: BLUE 27 MIN #ROWS: YELLOW 28 MAX #ROWS:BOX 29 MAX # ROWS:REDS 30 MAX # ROWS:BLUE 31 MAX # ROWS:YELLOW 32 NUMBER OF PEOPLE PER CAR:BOX 33 NUMBER OF PEOPLE PER CAR:RED 34 NUMBER OF PEOPLE PER CAR:BLUE 35 NUMBER OF PEOPLE PER CAR:YELLOW 36 MIN ATTENDANCE 37 MAX ATTENDANCE 38 NUMBER OF PARKING PLACES 20 30 25 15 10 10 10 10 10 30 40 40 50 2 3 3 5 44000 46000 15000 Figure 1