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, cont

• 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.