MGS 3100 Project 1 Road Atlanta Race Track

1. MGS 3100 Project 1Road Atlanta Race Track Changes are written in a red-colored font.

2. The Facts • Road Atlanta is a beautiful 2.52-mile automobile road racing facility located on Georgia Highway 53, about 5 miles West of I-85 and roughly 50 miles Northeast of Atlanta. In addition to its highly publicized professional race events, Road Atlanta sees a variety of other uses. One of these uses is SCCA amateur racing. Although a typical year sees 4 to 6 such events, most of them have always been closed to the public. To get in, you had to be a participant or "know somebody". • Road Atlanta instituted a new policy some years ago. In an effort to build a fan base and perhaps turn a profit on these amateur events as well, management opened these events to spectators at a very reasonable price. A full weekend (i.e., Saturday and Sunday) ticket for a typical event cost only $25, and included admission to infield, grandstands, and paddock.

3. Scenario • The above is true. From this point forward, we engage in a bit of fiction in creating an approximation to the decision-making scenario that Road Atlanta management might have faced. Assume that they have not yet made the decision to open to spectators. Opening up these events to spectators generates not only new revenues, but also new costs. The presence of spectators changes the liability situation, requiring additional insurance. Spectators sometimes act like slobs, meaning that after the event the Road Atlanta staff will need to do some additional cleanup. Since there is some risk of other misbehavior, they will need more security people. But spectators often make purchases from the concession stand and from the Souvenir Shop. Let's speculate on these costs and revenues.

4. Details • There is an advertising cost of $75,000 per weekend to bring in the spectators. • Selling tickets at all times requires a ticket seller. We will presume a cost of $350 for the weekend for this person, plus insurance of $100 per weekend. Both are fixed costs. • An additional security guard costs $300 per day. We'll guess that they need 1 security guard for every 2000 spectators. [You can use if, vlookup, or roundup function.] • Let's suppose that staff salaries and wages for a race weekend and the following week amount to $10,000. These staff members are employed and paid with or without a race, and with or without spectators. If there are 2000 or fewer spectators, they can handle the cleanup. • Each additional spectator above 2000 requires 37 cents worth of overtime cleanup help.[You MUST use if statement for this cost.] • Road Atlanta gets 30% of concession sales and the average spectator spends $15.00 per day on food and drink. • Assume that the Souvenir Shop costs $300 to open for the weekend, that the average dollar of sales brings 40 cents of margin, and that only 25% of spectators buy anything. If a spectator buys something, he or she will spend an average of $33. Use this average figure.

5. The Assignment • We will assume that Road Atlanta management has not yet decided to open these events to spectators. Road Atlanta's owners have several questions for you about a typical SCCA amateur race (the race weekend happens with or without spectators): • How much do we lose if no spectator shows up? • How many spectators per weekend must we attract to break even? • How much better or worse off are we (than a closed event) if we attract 2000 spectators? 5000? 10,000 per weekend? [Use data-table.] • How will the situation change if the ticket price is varied? Vary ticket price between $20 and $30.[Use data-table.] • Design a model that would aid them in making the decision. Begin with an influence diagram, then specify the required inputs, computations, and outputs. Next, construct a spreadsheet that will answer these and other questions on a "what if?" basis. Assume that the boss will want to try it out. Since you don't want to get fired, that means it had better be well organized, attractive, easy to use, hard to mess up, and economically correct.

6. The Assignment, con’t • Incorporate a 100-trial simulation that uses a probability distribution based on three states of weather for a whole weekend. • Rain -30% chance • Partly cloudy -50% • Sunny and warm -20% • Based on an attendance of 5,000: • rain will reduce the attendance by 50% • partly cloudy weather will have no effect • sunny and warm weather will increase attendance by 25% • Simulate the effect of this weather on attendance and state the effect this would have on profit. [Use vlookup function.]

7. Project Contents – what you need to turn in: • A one page report to the General Manager informing her or him of the potential profitability (or lack thereof) of opening these events to spectators. Remember that the memo should summarize your analysis and provide a recommendation. Any assumptions made for the analysis must be clearly stated. This should be put into a text box and included right on the Excel spreadsheet so that one document contains the complete assignment. • An Influence diagram for the problem. This can be done using the Excel Drawing tools (again, in the same spreadsheet) • Your spreadsheet model. The spreadsheet should have the following items: • Divided inputs: decision variables, parameters, physical and financial results –just like in the class examples. • ‘What if’ analysis for 3 different levels of attendance • Sensitivity analysis using a data table for ticket prices from $20 to $30 • A 100-trial simulation that uses a probability distribution based on three states of weather. All parts –report, influence diagram, and spreadsheet and charts should be included in one file

8. Grading Criteria • Accuracy 50% -this covers the relationships between the variables, numbers and the results • Excel Implementation 30% this covers the formulas, data tables, formatting, and graphs • Creativity 20% this covers the infinite number of ways that one might make the project stand out. For example, a well thought-out lay-out, graphing expertise, sophisticated formulas and use of tools like Goal Seek.