Project 1: Road Atlantis
Breakeven Analysis
Road Atlantis is a beautiful 2.52 mile automobile
road racing facility. In addition to its highly publicized professional
race events, Road Atlantis 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 Atlantis management is thinking about opening
these events to spectators at a very
reasonable price.
The amateur races cover the costs associated with a
"closed" race out
of fees paid by the amateurs themselves. 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
Atlantis
staff will need to do some additional cleanup. Since there is some risk
of
other misbehavior, they will need additional security guards. (These
"additional"
costs are over and above that needed, and paid for, by a closed
race.
The latter are irrelevant to the decision and should be
ignored)
On the other and, spectators often make purchases from the concession
stand.
Let's speculate on these costs and revenues.
Selling tickets at all requires a ticket seller.
We will presume a cost of $350 for the weekend for this person. The
additional insurance cost
is estimated at $5000.
An additional security guard costs roughly $500.
We'll guess that they need 1 additional security guard for each full
500
spectators (in other words, one extra guard for 1-500spectators, two
for
501-1000, et cetera). Hint: the number of extra security
guards in Excel terms would be =ROUNDUP(Z400/Z500,0) if you put the
number
of spectators per guard in cell Z500 and the number of spectators
attending
in cell Z400. DO NOT really use cells Z400 and Z500 for this!
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 about 60 cents worth of overtime.
The snack shop is run by a separate company as a
concession. If Road
Atlantis gets 30% of the concession's gross revenue and the average
spectator
spends $4.50 on food and drink, that could be some nice change.
YOUR JOB
Road Atlantis management has not yet decided whether to open
these
events to spectators. Road Atlantis'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
(Note
the example has it as $35. You have to change it to $30!)
-- What would the total cost, total revenue, and profit be if 500
spectators
come?
-- What would the total cost, total revenue, and profit be if 501
spectators
come?
-- What is the smallest (integer) number of spectators that would have
to come in order for allowing spectators to at least break
even? (Do not express you answer in fractional spectators!)
Phase 1:
Prepare a hand-drawn influence diagram relating all of the
following nine quantities and none other:
- ticket price
- number of spectators
- ticket revenue
- total snack revenue
- total fixed cost
- total security guard cost
- total cleanup cost
- total variable cost
- profit
Put each quantity in a rectangle. Whenever
one variable is used DIRECTLY as input in the computation of another,
draw
an arrow from the input variable to the variable it is DIRECTLY used to
calculate.
Do NOT draw an arrow if the effect is indirect. For example,
ticket
price affects ticket revenue and number of spectators directly, but it
affects
total revenue only indirectly. Number of spectators affects most
of
the other variables directly, but it doesn't affect total fixed cost or
ticket
price at all and it affects total profit only indirectly.
The influence diagram will be page 2 of your project report, after the
required cover sheet.
Use a pencil; I do not recommend doing it on ink! It's easier to
do
this on graph paper, though it's not required.
Phase 2:
Starting with a new blank spreadsheet, re-create the spreadsheet
shown here.
Put your name in cell A2. Make whatever other (minor)
changes are necessary to adapt the apredsheet to the actual assignment.
No file sharing of any kind whatsoever
is allowed: Do not modify or copy any part of any existing
spreadsheet from any source, especially a fellow student.
If I detect file sharing, all students
involved will receive zero for project 1 regardless of who copied
from whom
or whether the copier obtained the original he or she copied from with
or
without the permission of the originator of the file.
You may discuss this project with your fellow
students (or with your
instructor, which I encourage!).
You must use the =ROUNDUP function. You must use
a real Excel
data table.
(You can
see how the formulas view should be formatted by clicking here.
)
VERY IMPORTANT:
Do not use any "embedded parameters" at all. In other words, each
and every single number that comes from the problem statement must be
typed
once and only once into its very own separate, clearly labeled
cell.
Never ever type any of these numbers directly into any calculation;
always
use the cell reference instead. If you do not understand these
instructions,
ASK -- failure to understand will not be accepted as an excuse at
grading
time.
| Your spreadsheet
must include a real Excel data table showing the total cost,
total revenue and profit, to the penny, for: |
|
zero spectators, |
|
the highest integer
number of spectators for which opening the event leads to a (small) loss |
|
one more than that number |
|
500 spectators, and |
|
501 spectators |
Use this data table to create
a full page Excel xy graph (NOT a line graph!) in portrait orientation
with
excellent formatting and labeling, showing the relation between
attendance
and profit. ("Series 1" is NOT excellent labeling.)
Do
not use the smoothing feature The graph must use only black
lines and symbols on a white background. Do not use colored lines
on a gray background.
Instructions
for formatting graphs
Page 3 of your project report must be a full-page
printout of the
graph. Select the graph and print it, don't print the spreadsheet
containing the graph on page 3.
Hint: start with a wild guess at the integer break-even number of
spectators between 0 and 499, create the graph, then use the graph to
guide
your search for the exact integer break-even point. Or use Goal
Seek and manually
change the answer to an integer.
The last 2 pages of your project report must consist of two clearly
labeled, easy to read printouts of the spreadsheet.
Page 4 must be a printout in numeric format, in portrait
orientation.
Page 5 must be in
formulas (control-~) format, in landscape orientation. The latter
must include gridlines and row/column headings.
Do not use shaded or
colored backgrounds in the cells (borders are optional). You can
include
the entire small version of the graph like I did or leave it out
totally
as you choose, but you must not go over 1 page and you must not show
only
part of the graph. Use font sizes, column widths, and
portrait or landscape
alignments wisely to make the printout as communicative as possible.
Your first attempt at printing out the spreadsheet
will
probably not meet this criterion -- it takes some experimentation.
(If you wait until the day the project is due to print it out, you will
almost certainly not have time to develop a printout worth full
credit!)
The first page of your project report must be a copy of the Project 1 Cover Page in portrait mode with
your name in the correct spot.
The second page must be the influence diagram
The third page must be the graph, filling
the page, in portrait
orientation, black lines and symbols on a white
background.
The fourth page must be the numeric printout of the spreadsheet in
portrait
orientation
The fifth page must be the formulas printout of the spreadsheet in
landscape
orientation
Assemble all the parts in the correct order, and attach them with a
single staple in the upper left hand corner. Do not use a report
cover or a paper clip. Bring this completed and stapled package
with you to class on the day indicated. Also send me your
spreadsheet as an attachment to email addressed to 3100 at whalen3 dot org.
If you have any trouble with this assignment, I will
be very happy to help you BEFORE the due date.
Send me email at 3100 at whalen3 dot org.