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 (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: . . MAX 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. MAX 61 REVENUE: 62 TICKET SALES: BOX 63 TICKET SALES: RED . 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. . 64 TICKET SALES: BLUE 65 TICKET SALES: YELLOW 66 TOTAL TICKET REVENUE 67 REVENUE FROM SOUVENIR & FOOD SALES: BOX 68 REVENUE FROM SOUVENIR & FOOD SALES: RED 69 REVENUE FROM SOUVENIR & FOOD SALES: BLUE 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. 70 REVENUE FROM SOUVENIR & FOOD SALES:YELLOW 71 TOTAL REVENUE FROM SOUVENIR & FOOD SALES: 72 TOTAL REVENUE 3 CHANGING CELLS: 4 NUMBER OF BOX ROWS 5 NUMBER OF RED ROWS 6 NUMBER OF BLUE ROWS 7 NUMBER OF YELLOW ROWS 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 28 MAX #ROWS:BOX 29 MAX #ROWS:REDS 30 MAX #ROWS:BLUE 31 MAX #ROWS:YELLOW 17 PRICE PER TICKET: RED 18 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 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 41 CALCULATIONS: 42 NUMBER OF SEATS PER ROW-BOX 43 NUMBER OF SEATS PER ROW-RED 44 NUMBER OF SEATS PER ROW-BLUE 45 NUMBER OF SEATS PER ROW-YELLOW 46 NUMBER OF BOX SEATS 47 NUMBER OF RED SEATS 48 NUMBER OF BLUE SEATS 49 NUMBER OF YELLOW SEATS 50 TOTAL NUMBER OF SEATS 51 NUMBER OF CARS - BOX 52 NUMBER OF CARS - RED 53 NUMBER OF CARS - BLUE 54 NUMBER OF CARS YELLOW 55 TOTAL NUMBER OF CARS 56 TOTAL NUMBER OF ROWS 57 NUMBER OF RED + BLUE SEATS table: Seat Type Box seat Red seat Blue seat Yellow seat 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 Box seat Red seat Blue seat Yellow seat Seat Type Box seat Red seat Blue seat Yellow seat 2.75 (cell B14) Total number B49) The "Revenue" "total revenue" the following: is NUMBER NUMBER NUMBER NUMBER car for each each the team's The total number of The number of cars Red. Blue. Yellow a function number of rows for each seat type. 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. match. revenue revenue 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 B37). The "cheap seats" number of number of space should admission tickets. The team's 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: 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, constraints. Write Excel formulae for the following: and/or will be used as Number of seats per row for each seat type Box, Red, Blue, Yellow (cells B42 to cell B45) is row and of the width of seats a function of the length of the a fraction of in each row. Since a seat can't 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. Minimum number of rows 10 (cell B24) 10 (cell B25) 10 (cell B26) 10 (cell B27) seat fraction does of a car thus leaving whole number, Total number of cars arriving at the stadium for B55). Total number of rows Total number of rows CHANGING CELLS: NUMBER NUMBER NUMBER NUMBER The new stadium will need a parking lot that can accommodate 15000 cars (cell B38). The team's 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: For every 2 fans sitting in Box seats (cell B32), 1 car arrives at the stadium. For every 3 fans sitting in Red seats (cell B33), 1 car arrives at the stadium. For every 3 fans sitting in Blue seats (cell B34), 1 car arrives at the stadium. For every 5 fans sitting in Yellow seats (cell B35), 1 car arrives at the stadium. of seats team section that the Total ticket B65) B62 to seats for each seat type. from all gained Yellow CALCULATIONS: rows rows However, that # ROWS:BLUE OF BOX ROWS # ROWS:REDS RED ROWS BLUE ROWS # ROWS:YELLOW of The total number of rows league rules state (cell B36). it is decided 30 (cell B20) 25 (cell B21) 15 (cell B22) 10 (cell B23) accommodate most among like a balance number of rows for each Seat Type seats arriving (cells area. Thus OF OF OF YELLOW ROWS CONSTANTS: NUMBER OF WIDTH PER SEAT: BOX WIDTH PER SEAT: RED WIDTH PER SEAT: BLUE WIDTH PER SEAT: YELLOW NUMBER OF ROWS PRICE PER TICKET: BOX PRICE PER TICKET: RED PRICE PER TICKET: BLUE PRICE PER TICKET: YELLOW Avg. Expenditures on Souvenirs Avg. Expenditures on Souvenirs Avg. Expenditures on Souvenirs Avg. Expenditures on Souvenirs MIN #ROWS: BOX MIN #ROWS: RED MIN #ROWS: BLUE MIN #ROWS: YELLOW MAX #ROWS:BOX MAX A type sales revenue is a function Seat Width (in feet) 3.5 (cell B11) 3.25 (cell B12) 3 (cell B13) gained FEET IN A ROW The team management's goal is constraints, with the assumption that Formulate constraints from management: be allocated at Figure 1 A Finally, this is an integer problem. NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER TOTAL NUMBER NUMBER OF CARS - BOX NUMBER OF CARS - RED NUMBER OF CARS - BLUE NUMBER OF CARS - YELLOW TOTAL NUMBER OF CARS TOTAL NUMBER OF ROWS NUMBER OF RED + BLUE SEATS REVENUE: TICKET SALES: BOX TICKET SALES: RED TICKET SALES: BLUE TICKET SALES: YELLOW TOTAL TICKET REVENUE REVENUE REVENUE REVENUE REVENUE FROM SOUVENIR & TOTAL REVENUE FROM SOUVENIR TOTAL REVENUE to (cell & MIN ATTENDANCE MAX ATTENDANCE NUMBER OF PARKING PLACES to of Blue of Yellow OF SEATS PER ROW-BOX OF SEATS PER ROW-RED OF SEATS PER ROW-BLUE PER ROW-YELLOW OF SEATS OF BOX SEATS OF RED SEATS OF BLUE SEATS OF YELLOW SEATS OF SEATS Average expenditures on souvenirs and food per seat per game 4 and not exist, A A Box seat 10 (cell Red seat 10 (cell Blue seat 10 (cell Yellow seat of the spreadsheet Solver the stadium should it & Food & Food Total Total Red, Blue, Total revenue B71). Total revenue from ticket sales and from selling food and souvenirs for all seat types (cell B72). management does not is decided seats at Food to 15,000 the in each seat type - Box, Red, Blue, Yellow (cells B46 to the number of seats per row for each seat type and the for all seat types (cell B50) in the stadium. be installed be installed for OF PEOPLE PER CAR:BOX OF PEOPLE PER CAR:RED OF PEOPLE PER CAR:BLUE OF PEOPLE PER CAR:YELLOW the number the number of cars some spare space in the for each seat of the ticket FROM SOUVENIR & FOOD SALES: BOX FROM SOUVENIR & FOOD SALES: RED FROM SOUVENIR & FOOD SALES: BLUE the different stadium for a match for each seat type - Box, B51 to B54) is the function of the number of fans per B24) B25) B26) 10 (cell B27) FOOD SALES:YELLOW & FOOD SALES in the Maximum number of rows 30 (cell B28) 40 (cell B29) 40 (cell B30) 50 (cell B31) not that combined, seats. For the the seating cars (cell in the stadium must Major football be 100 (cell B15). that the stadium must have a minimum number of 44000 seats team management wants the stadium to appear "intimate", so be able to seat more than 46000 fans (cell stadium to have ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [1.5 marks] ?? [1.5 marks] ?? [1.5 marks] ?? [1.5 marks] ?? [1 mark] ?? [1 mark] type price 6 B tickets of all seat types (cell B66). selling food from B67 to B70). from selling food and souvenirs for all seat types (cell stadium (cell B56). Red and Blue seats (cell B57). want Price(S) per admission ticket 50 (cell B16) 35 (cell B17) 25 (cell B18) 20 (cell B19) to maximize the total revenue subject all seats' admission tickets will the following statements that Minimum number of rows & Food per seat per game: BOX per seat per game: RED per seat per game: BLUE per seat per game: YELLOW (see cell A61 to cell B72) contains the is expected to maximize. Write Excel formulae for the the number car area, B38). B ?? [1.5 marks] ?? [1.5 marks] ?? [1.5 marks] ?? [1.5 marks] ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [1 mark] 1 ?? [1.5 marks] ?? [1.5 marks] ?? [1.5 marks] ?? [1.5 marks] ?? [1 mark] ?? [1 mark] ?? [1 mark] of seats for each seat type. Since a for each seat type must be a parking lot. The seating types. kind of seat are shown in below table: 1 new and 1375 3.5 3.25 3 2.75 100 50 35 25 20 30 25 15 10 10 10 10 10 30 40 40 50 must be greater parking lot, 12 13 3 CamScanner 15 of rows and souvenirs for each seat type - Box, 44000 46000 15000 a match for all seat types (cell - Box, Red, Blue, Yellow (cells for each seat and the number of 30 40 40 50 the fractional part for the number of rows for each seat type is not allowed, as it of than is decided car parking Red (cell B28) (cell B29) (cell B30) (cell B31) a massive seats plus the to the that more thus lot should team management would also The minimum and maximum or equal B to various be sold for are made by Maximum number of rows B 1375 3.50 3.25 3.00 2.75 100 50 35 25 20 30 25 15 10 10 10 10 10 30 40 40 50 2 3 3 5 44000 46000 15000