Docstoc

Break Even Analysis - Excel

Document Sample
Break Even Analysis - Excel Powered By Docstoc
					                               BASIC BUDGET
              REVENUE               #         Unit Price          Totals
Registration                           800          $378.50        $302,800.00
Exhibit Sq. Feet                     5,000           $30.00        $150,000.00
Sponsorship                                      $50,000.00         $50,000.00
                                                                                                    Basic Budget
TOTAL REVENUE                                                     $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 revenues,
Convention Staff Salaries                                     $      8,000.00    based on the Break Even Analysis model. This model is
Decorations                                                   $      6,000.00    used throughout the industry, and is recognized as the
Decorator                                                     $     15,000.00    method to determine registration fees when sitting for
Entertainment                                                 $      6,000.00    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
                                                                                 Notice that the key cells from each spreadsheet are
Trucking/Shipping                                             $      2,000.00
TOTAL FIXED EXPENSES                                                             highlighted, so that the planner can clearly see from
                                                              $    162,800.00
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
enues,




t box.
                         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, variable
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 -
 ► Adding Extra Revenue                                             QuickCalc
 ► Budgeting for Desired Profit Margin                              Worksheet - This
 ► Working with Varied Guarantee Numbers                            sheet provides a
                                                                    quick calculation
       -

     This
ovides a
lculation
                            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
                                                                                                 Registration Fee Only
Convention Staff Salaries                                      $       8,000.00
Decorations                                                    $       6,000.00
Decorator                                                      $      15,000.00     No other sources of revenue (e.g. exhibit fees,
Entertainment                                                  $       6,000.00     sponsorship) are added to the budget. The three
Flowers/Plants                                                 $         800.00     elements for this budget are:
Internet & Networking                                          $       8,000.00
Lead Retrieval                                                 $       5,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
                                                                                    Enter this amount into cell C4 (Registration Fee).
                                                                     $22,400.00
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
Sponsorship                                      $50,000.00         $50,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 numerator,
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
Sponsorship                                       $50,000.00         $50,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)           -
Lead Retrieval                                                 $       5,000.00                                              $250,000
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                   $162,800 - $200,000 + $250,000
VARIABLE EXPENSES                  Attn.       Cost/Pp             Totals
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
                                                                                  Once you add the Registration Fee of $691 into
TOTAL VARIABLE EXPENSES                                            $340,000.00
     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
                               VARIED GUARANTEE NUMBERS
REVENUE                                                #           Unit Price            Totals
Registration                                               800           $562.38          $449,904.00    OK. Here's a sl
TOTAL REVENUE                                                                             $449,904.00    aren't the same
FIXED EXPENSES                                                                                           process, we're
AV & PC Rental/Labor                                                                 $       62,000.00   budget. No add
Buses                                                                                $        7,500.00
Convention Staff Salaries                                                            $        8,000.00   Total Fixed Exp
Decorations                                                                          $        6,000.00   Number of Atte
Decorator                                                                            $       15,000.00   Total Variable C
Entertainment                                                                        $        6,000.00
Flowers/Plants                                                                       $          800.00
Internet & Networking                                                                $        8,000.00
Lead Retrieval                                                                       $        5,000.00   B.E.P.
Meeting Room Rental                                                                  $       10,000.00
Microphones                                                                          $        4,500.00
Miscellaneous                                                                        $        1,000.00   B.E.P.
Photocopies                                                                          $        1,000.00
Photographer                                                                         $        2,000.00   B.E.P.
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          5
difference between the projected and full             you are shaving off of your guarantees by not using the full      subtract that from
attendance (DiffVE). Note that shortcut column H      attendance as your base (PctVE).                                  will give you the R
combines steps 2 and 3 into a single Excel formula.                                                                     to use in the Brea
           Varied Guarantee Numbers                                In order to make this work, a subtle adjustment to the formu
                                                                   will be required. What used to be Variable Costs will now be
OK. Here's a slice of real life. In this meeting, all guarantees   replaced with Revised Variable Costs (RevVC). The five ste
aren't the same throughout the event. In order to simplify this    outlined at the bottom of this worksheet will show the user h
process, we're going back to the first Registration Fee Only       to get that number.
                                                                                                  TFE
budget. No additional revenue. No desired profit.
                                                                   Break Even Price = ----------------------------+ RevVC
Total Fixed Expenses (TFE)        - $162,800                                             Number of Attendees
Number of Attendees                - 800
Total Variable Cost/Pp            - $425
                                                                   Don't worry. Due to percentage rounding, you may be a few
                                                                   dollars off - like our $4 profit on this meeting. Now, to add
                                                                   additional revenue or profit margins into this equation, we d
                          $162,800
                                                                   so just like the examples in the previous worksheets.
B.E.P.          = ----------------------------+ $358.88
                           800

B.E.P.          =       $203.50 + $358.88

B.E.P.          =       $562.38




                       FuVE                  Shortcut
                          $44,000.00              $8,250.00
                          $16,000.00                  $0.00
                          $11,200.00              $1,400.00
                          $36,000.00              $2,250.00
                           $9,600.00              $1,200.00
                          $32,000.00                  $0.00
                          $16,000.00              $1,000.00
                          $11,200.00              $2,100.00
                          $36,000.00              $4,500.00
                           $9,600.00              $3,600.00
                          $80,000.00             $20,000.00
                          $16,000.00              $3,000.00
                          $22,400.00              $5,600.00
                        $340,000.00

PctVE                  #REF!                   #REF!
RevVC                  #REF!                   #REF!
le Costs (RevVC)
 Variable Expenses for each event, based on the full event attendance
s is the total as if the full group was attending each function. Sum the total of
uVE). This column is reflected in cells G30 through G43 (which is the total)




                5 - Multiply PctVE * Variable Costs per Person, and
                subtract that from the Variable costs per person. This
                will give you the Revised variable costs per person (RevVC)
                to use in the Break Even Formula.
 adjustment to the formula
ariable Costs will now be
sts (RevVC). The five steps
heet will show the user how


       + RevVC




unding, you may be a few
s meeting. Now, to add
s into this equation, we do
 vious worksheets.
                                   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
Total Additional Revenue         $200,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
Total Additional Revenue         $200,000.00
Desired Profit                   $250,000.00

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:141
posted:8/23/2012
language:Latin
pages:15