BASIC BUDGET
REVENUE
Registration Exhibit Sq. Feet Sponsorship
#
800 5,000
Unit Price
$378.50 $30.00 $50,000.00
Totals
$302,800.00 $150,000.00 $50,000.00
TOTAL REVENUE FIXED EXPENSES
AV & PC Rental/Labor Buses Convention Staff Salaries Decorations Decorator Entertainment Flowers/Plants Internet & Networking Lead Retrieval Meeting Room Rental Microphones Miscellaneous Photocopies Photographer Security Signage Stage Set Telephone Trucking/Shipping $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
$502,800.00
62,000.00 7,500.00 8,000.00 6,000.00 15,000.00 6,000.00 800.00 8,000.00 5,000.00 10,000.00 4,500.00 1,000.00 1,000.00 2,000.00 2,500.00 6,000.00 10,000.00 5,500.00 2,000.00
Basic Budget This workbook is designed to provide the meeting professional the ability to calculate registration revenues, based on the Break Even Analysis model. This model is used throughout the industry, and is recognized as the method to determine registration fees when sitting for the CMP (Certified Meeting Professional) exam.
This is the Basic Budget, from which all calculations are derived. The second worksheet, Break Even Analysis, will discuss how these formulas work, as well as provide information about the four budget worksheets that follow.
The last worksheet, Quick Calculator, is designed to provide a quick calculation for those who know their budget numbers.
Notice that the key cells from each spreadsheet are highlighted, so that the planner can clearly see from where the data is taken.
TOTAL FIXED EXPENSES VARIABLE EXPENSES
Sunday Reception Monday Continental Bkfst Monday AM Break Monday Lunch Monday PM Break Monday Reception Tuesday Continental Bkfst Tuesday AM Break Tuesday Lunch Tuesday PM Break Tuesday Banquet Wednesday Continental Bkfast Wednesday Brunch
$ Attn.
800 800 800 800 800 800 800 800 800 800 800 800 800
Cost/Pp
$55.00 $20.00 $14.00 $45.00 $12.00 $40.00 $20.00 $14.00 $45.00 $12.00 $100.00 $20.00 $28.00
162,800.00 Totals
$44,000.00 $16,000.00 $11,200.00 $36,000.00 $9,600.00 $32,000.00 $16,000.00 $11,200.00 $36,000.00 $9,600.00 $80,000.00 $16,000.00 $22,400.00
Critical budget information on each worksheet is identified with text formatting similar to this text box. Blue text, yellow background and bold are used to indicate the formula components that need to be used.
TOTAL VARIABLE EXPENSES VARIABLE COSTS/PERSON TOTAL EXPENSES TOTAL REVENUE
$340,000.00 $425.00 $502,800.00 $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, 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 ► Adding Extra Revenue ► Budgeting for Desired Profit Margin ► Working with Varied Guarantee Numbers
Additional Tool QuickCalc Worksheet - This sheet provides a quick calculation
REGISTRATION FEE ONLY
REVENUE Registration TOTAL REVENUE FIXED EXPENSES
AV & PC Rental/Labor Buses Convention Staff Salaries Decorations Decorator Entertainment Flowers/Plants Internet & Networking Lead Retrieval Meeting Room Rental Microphones Miscellaneous Photocopies Photographer Security Signage Stage Set Telephone Trucking/Shipping
# 800
Unit Price
Totals $0.00 $0.00
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 62,000.00 7,500.00 8,000.00 6,000.00 15,000.00 6,000.00 800.00 8,000.00 5,000.00 10,000.00 4,500.00 1,000.00 1,000.00 2,000.00 2,500.00 6,000.00 10,000.00 5,500.00 2,000.00
Registration Fee Only
No other sources of revenue (e.g. exhibit fees, sponsorship) are added to the budget. The three elements for this budget are:
Total Fixed Expenses - $162,800 Number of Attendees - 800 Total Variable Cost/Pp - $425
The Formula
Total Fixed Expenses Break Even Price = ------------------------------+ Variable Costs Number of Attendees
TOTAL FIXED EXPENSES VARIABLE EXPENSES
Sunday Reception Monday Continental Bkfst Monday AM Break Monday Lunch Monday PM Break Monday Reception Tuesday Continental Bkfst Tuesday AM Break Tuesday Lunch Tuesday PM Break Tuesday Banquet Wednesday Continental Bkfast Wednesday Brunch
$ Attn.
800 800 800 800 800 800 800 800 800 800 800 800 800
Cost/Pp
$55.00 $20.00 $14.00 $45.00 $12.00 $40.00 $20.00 $14.00 $45.00 $12.00 $100.00 $20.00 $28.00
162,800.00 Totals
$44,000.00 $16,000.00 $11,200.00 $36,000.00 $9,600.00 $32,000.00 $16,000.00 $11,200.00 $36,000.00 $9,600.00 $80,000.00 $16,000.00 $22,400.00
B.E.P.
$162,800 = --------------------------------+ $425 800
Break Even Price = $628.50
Enter this amount into cell C4 (Registration Fee). It will completely balance the Profit Loss cell (D48).
TOTAL VARIABLE EXPENSES VARIABLE COSTS/PERSON TOTAL EXPENSES TOTAL REVENUE PROFIT - LOSS
$340,000.00 $425.00 $502,800.00 $0.00 -$502,800.00
EXTRA REVENUE MODEL
REVENUE
Registration Exhibit Sq. Feet Sponsorship
#
800 5000
Unit Price
$30.00 $50,000.00
Totals
$0.00 $150,000.00 $50,000.00
TOTAL REVENUE FIXED EXPENSES
AV & PC Rental/Labor Buses Convention Staff Salaries Decorations Decorator Entertainment Flowers/Plants Internet & Networking Lead Retrieval Meeting Room Rental Microphones Miscellaneous Photocopies Photographer Security Signage Stage Set Telephone Trucking/Shipping $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
$200,000.00
62,000.00 7,500.00 8,000.00 6,000.00 15,000.00 6,000.00 800.00 8,000.00 5,000.00 10,000.00 4,500.00 1,000.00 1,000.00 2,000.00 2,500.00 6,000.00 10,000.00 5,500.00 2,000.00
Extra Revenue Model
In this variation, the planner needs to integrate anticipated
sponsorship revenue and exhibit square footage revenue in order to establish the registration fee. One more component is required in this calculation, the total additional revenue
Total Fixed Expenses (TFE) - $162,800 Number of Attendees - 800 Total Variable Cost/Pp - $425 Total Additional Revenue (TAR) - $200,000
In order to make this work, the planner must subtract the
total additional revenue from the Total Fixed Expenses. This may result in a negative number in the numerator, TFE - TAR Break Even Price = ------------------------------+ Variable Costs Number of Attendees $162,800 - $200,000 = --------------------------------+ $425 800
= -$37,200 --------------------------------- +$425 800 = -$46.50 + $425
TOTAL FIXED EXPENSES VARIABLE EXPENSES
Sunday Reception Monday Continental Bkfst Monday AM Break Monday Lunch Monday PM Break Monday Reception Tuesday Continental Bkfst Tuesday AM Break Tuesday Lunch Tuesday PM Break Tuesday Banquet Wednesday Continental Bkfast Wednesday Brunch TOTAL VARIABLE EXPENSES VARIABLE COSTS/PERSON TOTAL EXPENSES TOTAL REVENUE PROFIT - LOSS
$ Attn.
800 800 800 800 800 800 800 800 800 800 800 800 800
Cost/Pp
$55.00 $20.00 $14.00 $45.00 $12.00 $40.00 $20.00 $14.00 $45.00 $12.00 $100.00 $20.00 $28.00 $425.00
162,800.00 Totals
$44,000.00 $16,000.00 $11,200.00 $36,000.00 $9,600.00 $32,000.00 $16,000.00 $11,200.00 $36,000.00 $9,600.00 $80,000.00 $16,000.00 $22,400.00 $340,000.00 $502,800.00 $200,000.00 -$302,800.00
B.E.P.
B.E.P.
B.E.P.
Break Even Price = $378.50 Note that in this budget there are 2 lines of additional revenue (exhibit square feet and sponsorship). Any/all additional revenues must be tallied here in order to allow this formula to work properly.
PROFIT MARGIN
REVENUE
Registration Exhibit Sq. Feet Sponsorship
#
800 5000
Unit Price
$30.00 $50,000.00
Totals
$0.00 $150,000.00 $50,000.00
TOTAL REVENUE FIXED EXPENSES
AV & PC Rental/Labor Buses Convention Staff Salaries Decorations Decorator Entertainment Flowers/Plants Internet & Networking Lead Retrieval Meeting Room Rental Microphones Miscellaneous Photocopies Photographer Security Signage Stage Set Telephone Trucking/Shipping $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
$200,000.00
62,000.00 7,500.00 8,000.00 6,000.00 15,000.00 6,000.00 800.00 8,000.00 5,000.00 10,000.00 4,500.00 1,000.00 1,000.00 2,000.00 2,500.00 6,000.00 10,000.00 5,500.00 2,000.00
Profit Margin Model
In addition to the TAR from the previous example, this budget requires a profit to be made from the meeting.
Total Fixed Expenses (TFE) - $162,800 Number of Attendees - 800 Total Variable Cost/Pp - $425 Total Additional Revenue (TAR) - $200,000 Desired Profit (Prof) In order to make this work, once again the planner must factor in this information as part of the TFE-TAR numerator.
$250,000
TFE - TAR + Prof Break Even Price = -----------------------------+ Variable Costs Number of Attendees
TOTAL FIXED EXPENSES VARIABLE EXPENSES
Sunday Reception Monday Continental Bkfst Monday AM Break Monday Lunch Monday PM Break Monday Reception Tuesday Continental Bkfst Tuesday AM Break Tuesday Lunch Tuesday PM Break Tuesday Banquet Wednesday Continental Bkfast Wednesday Brunch
$ Attn.
800 800 800 800 800 800 800 800 800 800 800 800 800
Cost/Pp
$55.00 $20.00 $14.00 $45.00 $12.00 $40.00 $20.00 $14.00 $45.00 $12.00 $100.00 $20.00 $28.00
162,800.00 Totals
$44,000.00 $16,000.00 $11,200.00 $36,000.00 $9,600.00 $32,000.00 $16,000.00 $11,200.00 $36,000.00 $9,600.00 $80,000.00 $16,000.00 $22,400.00
B.E.P.
$162,800 - $200,000 + $250,000 = -----------------------------------------+ $425 800
= $212,800 --------------------------------- +$425 800
B.E.P.
B.E.P.
=
$266 + $425
$250,000 Profit Registration Fee = $691
TOTAL VARIABLE EXPENSES VARIABLE COSTS/PERSON TOTAL EXPENSES TOTAL REVENUE PROFIT - LOSS
$340,000.00 $425.00 $502,800.00 $200,000.00 -$302,800.00
Once you add the Registration Fee of $691 into cell C4, the Profit/Loss cell (D49) will now reflect a $250,000 profit, which is what we desired.
REVENUE Registration TOTAL REVENUE FIXED EXPENSES
AV & PC Rental/Labor Buses Convention Staff Salaries Decorations Decorator Entertainment Flowers/Plants Internet & Networking Lead Retrieval Meeting Room Rental Microphones Miscellaneous Photocopies Photographer Security Signage Stage Set Telephone Trucking/Shipping
# 800
Unit Price $562.38
Totals $449,904.00 $449,904.00
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 62,000.00 7,500.00 8,000.00 6,000.00 15,000.00 6,000.00 800.00 8,000.00 5,000.00 10,000.00 4,500.00 1,000.00 1,000.00 2,000.00 2,500.00 6,000.00 10,000.00 5,500.00 2,000.00
TOTAL FIXED EXPENSES VARIABLE EXPENSES
Sunday Reception Monday Continental Bkfst Monday AM Break Monday Lunch Monday PM Break Monday Reception Tuesday Continental Bkfst Tuesday AM Break Tuesday Lunch Tuesday PM Break Tuesday Banquet Wednesday Continental Bkfast Wednesday Brunch
$ Attn.
650 800 700 750 700 800 750 650 700 500 600 650 600
Cost/Pp
$55.00 $20.00 $14.00 $45.00 $12.00 $40.00 $20.00 $14.00 $45.00 $12.00 $100.00 $20.00 $28.00
162,800.00 Totals
$35,750.00 $16,000.00 $9,800.00 $33,750.00 $8,400.00 $32,000.00 $15,000.00 $9,100.00 $31,500.00 $6,000.00 $60,000.00 $13,000.00 $16,800.00
TOTAL VARIABLE EXPENSES (PrVE) VARIABLE COSTS/PERSON TOTAL EXPENSES TOTAL REVENUE PROFIT - LOSS
$287,100.00 $425.00 $449,900.00 $449,904.00 $4.00
This number replaces the Variable Cost Per 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 attendance. These numbers go directly into the budget. In this situation, each event may have a different number of participants. Sum the total of this column (PrVE).
2 - Determine Variable Expenses for each even estimate. This is the total as if the full group wa this column (FuVE). This column is reflected in
3 - Subtract FuVE – PrVE. This gives you the difference between the projected and full attendance (DiffVE). Note that shortcut column H combines steps 2 and 3 into a single Excel formula.
4 - Divide DiffVE/FuVE. This gives you a percentage that you are shaving off of your guarantees by not using the full attendance as your base (PctVE).
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 Anticipated Attendees Total Variable Cost/Pp Total Additional Revenue Desired Profit $162,800.00 800 $425.00 $200,000.00 $250,000.00
YOUR TARGET REGISTRATION FEE IS: WITH A PROFIT MARGIN OF:
$691.00 $250,000.00
VARIED GUARANTEES CALCULATOR Total Fixed Expenses Anticipated Attendees Revised Variable Costs Total Additional Revenue Desired Profit $162,800.00 800 $358.88 $200,000.00 $250,000.00
YOUR TARGET REGISTRATION FEE IS: WITH A PROFIT MARGIN OF:
$624.88 $250,000.00