# Break Even Formula - Excel

Document Sample

```					                               BASIC BUDGET
REVENUE               #         Unit Price          Totals
Registration                           800          \$378.50        \$302,800.00
Exhibit Sq. Feet                     5,000           \$30.00        \$150,000.00
TOTAL REVENUE                                                                                      Basic Budget
\$502,800.00
FIXED EXPENSES
AV & PC Rental/Labor                                          \$     62,000.00    This workbook is designed to provide the meeting
Buses                                                         \$      7,500.00    professional the ability to calculate registration
Convention Staff Salaries                                     \$      8,000.00    revenues, based on the Break Even Analysis model. This
Decorations                                                   \$      6,000.00    model is used throughout the industry, and is recognized
Decorator                                                     \$     15,000.00    as the method to determine registration fees when sitting
Entertainment                                                 \$      6,000.00    for the CMP (Certified Meeting Professional) exam.
Flowers/Plants                                                \$        800.00
Internet & Networking                                         \$      8,000.00    This is the Basic Budget, from which all calculations are
Lead Retrieval                                                \$      5,000.00    derived. The second worksheet, Break Even Analysis, will
Meeting Room Rental                                           \$     10,000.00    discuss how these formulas work, as well as provide
Microphones                                                   \$      4,500.00    information about the four budget worksheets that
Miscellaneous                                                 \$      1,000.00    follow.
Photocopies                                                   \$      1,000.00
Photographer                                                  \$      2,000.00    The last worksheet, Quick Calculator, is designed to
Security                                                      \$      2,500.00
provide a quick calculation for those who know their
Signage                                                       \$      6,000.00
budget numbers.
Stage Set                                                     \$     10,000.00
Telephone                                                     \$      5,500.00
Trucking/Shipping                                             \$      2,000.00
Notice that the key cells from each spreadsheet are
TOTAL FIXED EXPENSES                                          \$    162,800.00    highlighted, so that the planner can clearly see from
VARIABLE EXPENSES                  Attn.       Cost/Pp            Totals         where the data is taken.
Sunday Reception                        800          \$55.00         \$44,000.00
Monday Continental Bkfst                800          \$20.00         \$16,000.00
Monday AM Break                         800          \$14.00         \$11,200.00
Monday Lunch                            800          \$45.00         \$36,000.00
Monday PM Break                         800          \$12.00          \$9,600.00   Critical budget information on each worksheet is
Monday Reception                        800          \$40.00         \$32,000.00   identified with text formatting similar to this text box.
Tuesday Continental Bkfst               800          \$20.00         \$16,000.00    Blue text, yellow background and bold are used to
Tuesday AM Break                        800          \$14.00         \$11,200.00   indicate the formula components that need to be
Tuesday Lunch                           800          \$45.00         \$36,000.00   used.
Tuesday PM Break                        800          \$12.00          \$9,600.00
Tuesday Banquet                         800         \$100.00         \$80,000.00
Wednesday Continental Bkfast            800          \$20.00         \$16,000.00
Wednesday Brunch                        800          \$28.00         \$22,400.00
TOTAL VARIABLE EXPENSES                                           \$340,000.00
VARIABLE COSTS/PERSON                         \$425.00
TOTAL EXPENSES                                                    \$502,800.00
TOTAL REVENUE                                                      \$502,800.00
PROFIT - LOSS   \$0.00
Break Even Analysis
Break Even Analysis is a method by which meeting organizers can determine
at which point revenue covers expenses. There are a number of ways that a
planner can use this tool. They can use it to:

■ Determine registration fees required to break even
■ Determine registration fees required to costs based on pre-determined
Estimate attendance required to cover produce a desired profit margin
registration fee.
The Break Even Price formula is given below. In order to use the formula,
three pieces of information are required to be able to establish a registration
fee:

► Total Fixed Expenses
► Anticipated Number of Attendees the per person variable expenses from
Variable Costs. This is the sum of
the budget.

Total Fixed Expenses
Break Even Price = -------------------------------- + Variable Costs
Number of Attendees

The most critical part of working with this formula on any budget is to separate
fixed from variable expenses. Simply stated, variable expenses are those
which fluctuate with the changing number of attendees. For most meetings,
This spreadsheet will use the BasicBudget worksheet as a standard budget for
this meeting. Four additional worksheets are contained, each one showing
how the Break Even Analysis works. The four models shown are:

► Registration Fee Only                                             Additional Tool -
► Budgeting for Desired Profit Margin                               Worksheet - This
► Working with Varied Guarantee Numbers                             sheet provides a
quick calculation
REGISTRATION FEE ONLY
REVENUE                              #         Unit Price          Totals
Registration                             800                                \$0.00
TOTAL REVENUE                                                               \$0.00
FIXED EXPENSES
AV & PC Rental/Labor                                           \$      62,000.00
Buses                                                          \$       7,500.00
Convention Staff Salaries                                      \$       8,000.00
Registration Fee Only
Decorations                                                    \$       6,000.00
Decorator                                                      \$      15,000.00     No other sources of revenue (e.g. exhibit
Flowers/Plants                                                 \$         800.00     three elements for this budget are:
Internet & Networking                                          \$       8,000.00
Meeting Room Rental                                            \$      10,000.00     Total Fixed Expenses - \$162,800
Microphones                                                    \$       4,500.00     Number of Attendees - 800
Miscellaneous                                                  \$       1,000.00     Total Variable Cost/Pp - \$425
Photocopies                                                    \$       1,000.00
Photographer                                                   \$       2,000.00
Security                                                       \$       2,500.00
Signage                                                        \$       6,000.00                        The Formula
Stage Set                                                      \$      10,000.00
Telephone                                                      \$       5,500.00                      Total Fixed Expenses
Trucking/Shipping                                              \$       2,000.00     Break Even Price = ------------------------------+ Variable Costs
TOTAL FIXED EXPENSES                                           \$    162,800.00                        Number of Attendees
VARIABLE EXPENSES                   Attn.       Cost/Pp            Totals
Sunday Reception                         800          \$55.00         \$44,000.00
Monday Continental Bkfst                 800          \$20.00         \$16,000.00
Monday AM Break                          800          \$14.00         \$11,200.00
Monday Lunch                             800          \$45.00         \$36,000.00                                \$162,800
Monday PM Break                          800          \$12.00          \$9,600.00     B.E.P.              = --------------------------------+ \$425
Monday Reception                         800          \$40.00         \$32,000.00                                    800
Tuesday Continental Bkfst                800          \$20.00         \$16,000.00
Tuesday AM Break                         800          \$14.00         \$11,200.00
Tuesday Lunch                            800          \$45.00         \$36,000.00
Tuesday PM Break                         800          \$12.00          \$9,600.00     Break Even Price = \$628.50
Tuesday Banquet                          800         \$100.00         \$80,000.00
Wednesday Continental Bkfast             800          \$20.00         \$16,000.00
Wednesday Brunch                         800          \$28.00         \$22,400.00
Enter this amount into cell C4 (Registration Fee).
TOTAL VARIABLE EXPENSES                                            \$340,000.00      It will completely balance the Profit Loss cell (D48).
VARIABLE COSTS/PERSON                           \$425.00

TOTAL EXPENSES                                                     \$502,800.00
TOTAL REVENUE                                                             \$0.00
PROFIT - LOSS                                                      -\$502,800.00
EXTRA REVENUE MODEL
REVENUE                             #         Unit Price          Totals
Registration                           800                               \$0.00
Exhibit Sq. Feet                      5000           \$30.00        \$150,000.00
TOTAL REVENUE                                                     \$200,000.00                  Extra Revenue Model
FIXED EXPENSES
AV & PC Rental/Labor                                          \$     62,000.00
In this variation, the planner needs to integrate anticipated
Buses                                                         \$      7,500.00
sponsorship revenue and exhibit square footage revenue in
Convention Staff Salaries                                     \$      8,000.00
order to establish the registration fee. One more
Decorations                                                   \$      6,000.00
component is required in this calculation, the total additional
Decorator                                                     \$     15,000.00
revenue
Entertainment                                                 \$      6,000.00
Flowers/Plants                                                \$        800.00
Total Fixed Expenses (TFE)     - \$162,800
Internet & Networking                                         \$      8,000.00    Number of Attendees             - 800
Lead Retrieval                                                \$      5,000.00    Total Variable Cost/Pp         - \$425
Meeting Room Rental                                           \$     10,000.00    Total Additional Revenue (TAR) - \$200,000
Microphones                                                   \$      4,500.00
Miscellaneous                                                 \$      1,000.00
Photocopies                                                   \$      1,000.00    In order to make this work, the planner must subtract the
Photographer                                                  \$      2,000.00    total additional revenue from the Total Fixed Expenses.
Security                                                      \$      2,500.00    This may result in a negative number in the
Signage                                                       \$      6,000.00
Stage Set                                                     \$     10,000.00                         TFE - TAR
Telephone                                                     \$      5,500.00    Break Even Price = ------------------------------+ Variable Costs
Trucking/Shipping                                             \$      2,000.00                      Number of Attendees
TOTAL FIXED EXPENSES                                          \$    162,800.00
VARIABLE EXPENSES                  Attn.      Cost/Pp             Totals
Sunday Reception                        800          \$55.00         \$44,000.00                            \$162,800 - \$200,000
Monday Continental Bkfst                800          \$20.00         \$16,000.00   B.E.P.           = --------------------------------+ \$425
Monday AM Break                         800          \$14.00         \$11,200.00                               800
Monday Lunch                            800          \$45.00         \$36,000.00
Monday PM Break                         800          \$12.00          \$9,600.00   B.E.P.           =        -\$37,200
Monday Reception                        800          \$40.00         \$32,000.00                     --------------------------------- +\$425
Tuesday Continental Bkfst               800          \$20.00         \$16,000.00                               800
Tuesday AM Break                        800          \$14.00         \$11,200.00
Tuesday Lunch                           800          \$45.00         \$36,000.00   B.E.P.           =      -\$46.50 + \$425
Tuesday PM Break                        800          \$12.00          \$9,600.00
Tuesday Banquet                         800         \$100.00         \$80,000.00
Wednesday Continental Bkfast            800          \$20.00         \$16,000.00   Break Even Price = \$378.50
Wednesday Brunch                        800          \$28.00         \$22,400.00
TOTAL VARIABLE EXPENSES                                            \$340,000.00   Note that in this budget there are 2 lines of additional
VARIABLE COSTS/PERSON                      \$425.00                      revenue (exhibit square feet and sponsorship). Any/all
TOTAL EXPENSES                                                    \$502,800.00    additional revenues must be tallied here in order to allow this
TOTAL REVENUE                                                      \$200,000.00   formula to work properly.
PROFIT - LOSS                                                     -\$302,800.00
PROFIT MARGIN
REVENUE                             #          Unit Price          Totals
Registration                             800                              \$0.00
Exhibit Sq. Feet                        5000          \$30.00        \$150,000.00
TOTAL REVENUE                                                      \$200,000.00                      Profit Margin Model
FIXED EXPENSES
AV & PC Rental/Labor                                           \$      62,000.00    In addition to the TAR from the previous example, this
Buses                                                          \$       7,500.00    budget requires a profit to be made from the meeting.
Convention Staff Salaries                                      \$       8,000.00
Decorations                                                    \$       6,000.00    Total Fixed Expenses (TFE)     - \$162,800
Decorator                                                      \$      15,000.00    Number of Attendees             - 800
Entertainment                                                  \$       6,000.00    Total Variable Cost/Pp         - \$425
Flowers/Plants                                                 \$         800.00    Total Additional Revenue (TAR) - \$200,000
Internet & Networking                                          \$       8,000.00    Desired Profit (Prof)           -
Meeting Room Rental                                            \$      10,000.00    In order to make this work, once again the planner must
Microphones                                                    \$       4,500.00    factor in this information as part of the TFE-TAR numerator.
Miscellaneous                                                  \$       1,000.00
Photocopies                                                    \$       1,000.00
Photographer                                                   \$       2,000.00
Security                                                       \$       2,500.00
TFE - TAR + Prof
Signage                                                        \$       6,000.00
Break Even Price = -----------------------------+ Variable Costs
Stage Set                                                      \$      10,000.00
Number of Attendees
Telephone                                                      \$       5,500.00
Trucking/Shipping                                              \$       2,000.00
TOTAL FIXED EXPENSES                                           \$    162,800.00
VARIABLE EXPENSES                  Attn.       Cost/Pp             Totals
\$162,800 - \$200,000 + \$250,000
Sunday Reception                         800          \$55.00         \$44,000.00   B.E.P.       = -----------------------------------------+ \$425
Monday Continental Bkfst                 800          \$20.00         \$16,000.00                                 800
Monday AM Break                          800          \$14.00         \$11,200.00
Monday Lunch                             800          \$45.00         \$36,000.00   B.E.P.        =         \$212,800
Monday PM Break                          800          \$12.00          \$9,600.00                     --------------------------------- +\$425
Monday Reception                         800          \$40.00         \$32,000.00                                800
Tuesday Continental Bkfst                800          \$20.00         \$16,000.00
Tuesday AM Break                         800          \$14.00         \$11,200.00   B.E.P.        =       \$266 + \$425
Tuesday Lunch                            800          \$45.00         \$36,000.00
Tuesday PM Break                         800          \$12.00          \$9,600.00
Tuesday Banquet                          800         \$100.00         \$80,000.00   \$250,000 Profit Registration Fee = \$691
Wednesday Continental Bkfast             800          \$20.00         \$16,000.00
Wednesday Brunch                         800          \$28.00         \$22,400.00
TOTAL VARIABLE EXPENSES                                            \$340,000.00
Once you add the Registration Fee of \$691 into
VARIABLE COSTS/PERSON                          \$425.00                       cell C4, the Profit/Loss cell (D49) will now reflect a
TOTAL EXPENSES                                                     \$502,800.00    \$250,000 profit, which is what we desired.
TOTAL REVENUE                                                       \$200,000.00
PROFIT - LOSS                                                      -\$302,800.00
REVENUE                                                #           Unit Price            Totals
Registration                                               800           \$562.38          \$449,904.00
TOTAL REVENUE                                                                             \$449,904.00
FIXED EXPENSES
AV & PC Rental/Labor                                                                 \$       62,000.00
Buses                                                                                \$        7,500.00
Convention Staff Salaries                                                            \$        8,000.00
Decorations                                                                          \$        6,000.00
Decorator                                                                            \$       15,000.00
Entertainment                                                                        \$        6,000.00
Flowers/Plants                                                                       \$          800.00
Internet & Networking                                                                \$        8,000.00
Meeting Room Rental                                                                  \$       10,000.00
Microphones                                                                          \$        4,500.00
Miscellaneous                                                                        \$        1,000.00
Photocopies                                                                          \$        1,000.00
Photographer                                                                         \$        2,000.00
Security                                                                             \$        2,500.00
Signage                                                                              \$        6,000.00
Stage Set                                                                            \$       10,000.00
Telephone                                                                            \$        5,500.00
Trucking/Shipping                                                                    \$        2,000.00
TOTAL FIXED EXPENSES                                                                 \$     162,800.00
VARIABLE EXPENSES                                    Attn.          Cost/Pp              Totals
Sunday Reception                                             650           \$55.00           \$35,750.00
Monday Continental Bkfst                                     800           \$20.00           \$16,000.00
Monday AM Break                                              700           \$14.00            \$9,800.00
Monday Lunch                                                 750           \$45.00           \$33,750.00
Monday PM Break                                              700           \$12.00            \$8,400.00
Monday Reception                                             800           \$40.00           \$32,000.00
Tuesday Continental Bkfst                                    750           \$20.00           \$15,000.00
Tuesday AM Break                                             650           \$14.00            \$9,100.00
Tuesday Lunch                                                700           \$45.00           \$31,500.00
Tuesday PM Break                                             500           \$12.00            \$6,000.00
Tuesday Banquet                                              600          \$100.00           \$60,000.00
Wednesday Continental Bkfast                                 650           \$20.00           \$13,000.00
Wednesday Brunch                                             600           \$28.00           \$16,800.00
TOTAL VARIABLE EXPENSES (PrVE)                                    \$287,100.00
VARIABLE COSTS/PERSON                            \$425.00
TOTAL EXPENSES                                                                           \$449,900.00
TOTAL REVENUE                                                                             \$449,904.00
PROFIT - LOSS                           This number replaces the Variable Cost Per              \$4.00
Person in the Varied Guarantees
Person in the Varied Guarantees
Calculation.

Steps to Calculate Revised Variable Costs (RevVC)
1 - Determine Variable Expenses for each event, based on the projected event             2 - Determine Variable Expenses for each even
attendance. These numbers go directly into the budget. In this situation, each           estimate. This is the total as if the full group wa
event may have a different number of participants. Sum the total of this column          this column (FuVE). This column is reflected in
(PrVE).

3 - Subtract FuVE – PrVE. This gives you the          4 - Divide DiffVE/FuVE. This gives you a percentage that
difference between the projected and full             you are shaving off of your guarantees by not using the full
attendance (DiffVE). Note that shortcut column H      attendance as your base (PctVE).
combines steps 2 and 3 into a single Excel formula.
Break Even Budget Calculator
Just fill in the blanks, and let the calculators determine your break even (or profit-desired) registration fee.
Only the cells with Yellow background can be edited.

STANDARD CALCULATOR

Total Fixed Expenses             \$162,800.00
Anticipated Attendees                    800           YOUR TARGET REGISTRATION FEE IS:                    \$691.00
Total Variable Cost/Pp               \$425.00         WITH A PROFIT MARGIN OF:                             \$250,000.00
Desired Profit                   \$250,000.00

VARIED GUARANTEES CALCULATOR

Total Fixed Expenses             \$162,800.00
Anticipated Attendees                    800           YOUR TARGET REGISTRATION FEE IS:                    \$624.88
Revised Variable Costs               \$358.88         WITH A PROFIT MARGIN OF:                             \$250,000.00