1 MGS 3100 PROJECT 1 (Due 23rd Oct, Beginning of Class) Profitability Analysis - Road Atlanta The Business 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." The irony of this is that these events often provide the most exciting racing. In an effort to build a fan base and perhaps turn a profit on these amateur events as well, Road Atlanta management is thinking about opening these events to spectators at a very reasonable price. Business Scenario 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. On the other hand, spectators often make purchases from the concession stand and from the Souvenir Shop. Let's speculate on these costs and revenues per race. There is an advertising cost of $20,000 to bring in the spectators. Selling tickets at all requires a ticket seller. We will presume a cost of $400 for this person. The additional insurance cost is estimated at $10,000. The current security employees can monitor upto 500 attendees. But they need 1 security guard for each full 500 spectators over that limit (in other words, no extra guard for 0-500 spectators, one for 501-1000, two for 1001- 1500, etc.). An additional security guard costs roughly $450. [Hint: you can use rounddown 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 400 or fewer spectators, they can handle the cleanup. Each additional spectator above the first 400 requires 80 cents worth of overtime. [Hint: use if function.] The snack shop is run by a separate company as a concession. If Road Atlanta gets 20% of the concession's gross revenue and the average spectator spends $5.00 on food and drink, that could be some nice change. 2 Assume that opening up the Souvenir Shop costs Road Atlanta $800, that the average dollar of sales brings in 55 cents of margin, and that only 25% of spectators buy anything. If a spectator does buy something, let's guess that he or she will spend around $50. Your Task Phase 1: Road Atlanta management has not yet decided whether 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): Answer the following questions assuming that the ticket price is $30. 1. How many spectators (in integer) must Road Atlanta attract to at least break even? 2. How much better or worse off is Road Atlanta (than a closed event) if it attracts 0, 300, 500, 1000, 1500, 2000, or 2500 spectators? [You MUST use data-table command to answer this question.] 3. Road Atlanta expects that there would be roughly 1000 spectators if it opens the race. Would you recommend Road Atlanta to open the race? 4. In addition to the questions above, provide a graph that shows the relationship between attendance and the profit. Phase 2: Incorporate a 100-trial simulation that uses a probability distribution based on three states of weather for a whole weekend. E Rain -25% chance E Partly cloudy -40% E Sunny and warm -35% Based on an attendance of 1,000: E rain will reduce the attendance by 50% E partly cloudy weather will have no effect E 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. 3 Answer the following questions assuming that the ticket price is $30. 1. What is the average profit of 100-trial simulation? 2. What is the worst profit, and what is the best profit? 3. Would you recommend Road Atlanta to open the race? What I want to see You should construct a spreadsheet that will let you answer the questions above and other questions on a "what if?" basis. (i.e., Any parameter can be changed, and your Excel model should be able to reflect such changes with relative ease.) 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. Enhancements such as graphs would be wonderful. Submittals: In What Format Directions MS Word or PDF Based on your answers in the phase 1 & 2 and other insights you find in the analysis, write single spaced one or two page report to the General Manager informing her/him of the potential consequences of opening these events to spectators. Remember that the memo should summarize your analysis and provide a recommendation. Any assumptions made for the analysis, other than ones provided on this page, must be clearly stated. It must be word processed neatly. MS Word or MS Excel or PDF An Influence diagram for the problem. This can be done in Word or Excel using the Drawing tools. MS Excel Your spreadsheet model. Make sure that besides being accurate, it looks neat. Your spreadsheet must include a real Excel data table showing the answers to the questions in phase 1 and 2. Email your project to me using the email address specified in the syllabus (kkarmakar1@gsu.edu). Only one submission per group is required. If you are working in a group the subitted MS Word/Excel/PDF files must contain the full name of all the group members. Moreover, when emailing me the documents, also forward the same to all of your group members. In other words, the submission email that I receive, must contain the email address of all your group members in the To address field. Grading Rubric to be added soon.