Business Plan Worksheets

CopyofBEP_RM17.xlsb

Home >Business & Finance homework help >Business Plan Worksheets

Break-Even Worksheet

Sales Break-Even Calculation
	We highly recommending reading the following article on Break-Even BEFORE using this worksheet.
		"Break-Even: It's Like Having Your Own Financial Crystal Ball"
		http://www.restaurantowner.com/members/126.cfm
	Step 1: Obtain your monthly or 4 week P&L's for the last year.
	Step 2: Enter the numbers from your P&L's in the worksheet below. If your P&L accounts are not grouped
	into these categories, use the P&L Account Detail worksheet (click on tab below) to group your
	accounts into these categories.
	Step 3: Use your actual P&L numbers to then enter your initial estimate of Cost of Sales % and average
	monthly or 4 week costs for each category at a sales level that approximates break-even based on
	your best judgement by looking the actual number on the left. You can modify this at any time.
Restaurant Break-Even Calculation Worksheet
			Lowest Sales Jim Laube: Lowest sales month or 4 week period in the past year.		Average Sales Jim Laube: Average monthly or period sales in the past year.		Highest Sales Jim Laube: Highest sales month or 4 week period in the past year.		Break-Even Estimate Jim Laube: Based on actual results on left, use your judgement to estimate what the variable cost % and fixed costs would be at what you thing your break-even sales level would be. This is only a starting point. You can modify and tweak this model at any time.
				%		%		%
SALES				0.0		0.0		0.0
COST OF SALES				0.0		0.0		0.0		Variable Cost
GROSS PROFIT			0	0.0	0	0.0	0	0.0
PAYROLL
	Management			0.0		0.0		0.0		Fixed Cost
	Hourly Personnel			0.0		0.0		0.0		Mixed Cost
	Taxes & Benefits			0.0		0.0		0.0		Mixed Cost
		Total Payroll	0	0.0	0	0.0	0	0.0
CONTROLLABLE EXPENSES
	Direct Operating Exp.			0.0		0.0		0.0		Mostly Fixed
	Music & Entertain.			0.0		0.0		0.0		Mostly Fixed
	Marketing			0.0		0.0		0.0		Mostly Fixed
	Utility Services			0.0		0.0		0.0		Mostly Fixed
	Admin. & General			0.0		0.0		0.0		Mostly Fixed
	Repairs & Maint.			0.0		0.0		0.0		Mostly Fixed
		Total Controll. Exp.	0	0.0	0	0.0	0	0.0
CONTROLL. PROFIT			0	0.0	0	0.0	0	0.0
OCCUPANCY COSTS
	Rent			0.0		0.0		0.0		Fixed Cost
	Property Taxes			0.0		0.0		0.0		Fixed Cost
	Other Taxes			0.0	0	0.0	0	0.0	0	Fixed Cost
	Property Insurance		0	0.0		0.0	0	0.0		Fixed Cost
		Total Occup.Costs	0	0.0	0	0.0	0	0.0
	Interest			0.0		0.0		0.0		Fixed Cost
	Depreciation			0.0		0.0		0.0		Fixed Cost
	Other Expenses		0	0.0	0	0.0	0	0.0	0	Mostly Fixed
	Other (Income) Jim Laube: Enter total income from rebates, commissions, etc. as a negative number. For example - (1,000).		0	0.0	0	0.0	0	0.0	0	Mostly Fixed
INCOME BEFORE TAX			0	0.0	0	0.0	0	0.0
							Total Fixed Costs		0
(restaurant name here)
Break-Even & Operating Profit Model
			Break-		Average		High
			Even	%	Sales	%	Sales	%
SALES			0	100.0		100.0		100.0
COST OF SALES			0	0.0	0	0.0	0	0.0
GROSS PROFIT			0	0.0	0	0.0	0	0.0
PAYROLL
	Management		0	0.0	0	0.0	0	0.0
	Hourly Personnel		0	0.0	0	0.0	0	0.0
	Taxes & Benefits		0	0.0	0	0.0	0	0.0
		Total Payroll	0	0.0	0	0.0	0	0.0
CONTROLLABLE EXPENSES
	Direct Operating Exp.		0	0.0	0	0.0	0	0.0
	Music & Entertain.		0	0.0	0	0.0	0	0.0
	Marketing		0	0.0	0	0.0	0	0.0
	Utility Services		0	0.0	0	0.0	0	0.0
	Administrative & General		0	0.0	0	0.0	0	0.0
	Repairs & Maintenance		0	0.0	0	0.0	0	0.0
		Total Controllable Expenses	0	0.0	0	0.0	0	0.0
CONTROLLABLE PROFIT			0	0.0	0	0.0	0	0.0
OCCUPANCY COSTS
	Rent		0	0.0	0	0.0	0	0.0
	Property Taxes		0	0.0	0	0.0	0	0.0
	Other Taxes		0	0.0	0	0.0	0	0.0
	Property Insurance		0	0.0	0	0.0	0	0.0
		Total Occup.Costs	0	0.0	0	0.0	0	0.0
OTHER COSTS & EXPENSES
	Interest		0	0.0	0	0.0	0	0.0
	Depreciation		0	0.0	0	0.0	0	0.0
	Other Expenses		0	0.0	0	0.0	0	0.0
	Other (Income)		0	0.0	0	0.0	0	0.0
INCOME BEFORE TAX			0	0.0	0	0.0	0	0.0
% Increase in Sales Over Break-Even			-		0.0%		0.0%
% of Variable Hourly Labor Cost			60% Jim Laube: Hourly labor cost in most restaurant tend to run 50% - 70% variable cost to fixed cost above the sales break-even point. You can adjust this number according to how your hour labor cost tends to change with sales level changes.
Sales Equivalents:
	Number of Periods per Year		12 Jim Laube: If on a monthly P&L cycle put "12", if on a 4 week cycle put "13".
	Annual Sales		0		0		0
	Average Weekly Sales		0		0		0

From Actual P&L's Within The Last 12 Months

http://www.restaurantowner.com/members/126.cfm

P&L Account Detail

RESTAURANT CHART OF ACCOUNTS
P&L ACCOUNTS
3000	SALES		4500	MARKETING
3010	Food		4510	Selling & Promotion
3020	Liquor		4520	Advertising
3030	Beer		4530	Public Relations
3040	Wine		4540	Research
			4545	Complimentary Food & Beverages
4000	COST OF SALES		4550	Discounted Food & Beverages
4001	Food -
4002		Meat	4600	UTILITIES
4003		Seafood	4610	Electrical
4004		Poultry	4620	Gas
4005		Produce	4630	Water
4006		Bakery	4640	Trash Removal
4007		Dairy
4008		Grocery & Dry Goods	4700	GENERAL & ADMINISTRATIVE
4009		Non-alcoholic Beverages	4705	Office Supplies
4020	Liquor		4710	Postage & Delivery
4030	Bar Consumables		4715	Telephone / Communications
4040	Beer		4720	Payroll Processing
4050	Wine		4725	Insurance - General
4060	Paper (QSR)		4730	Dues & Subscriptions
			4735	Travel Expenses
4100	SALARIES & WAGES		4740	Credit Card Discounts
4110	Management		4745	Bad Debts
4120	Dining Room		4750	Cash (Over) / Short
4130	Bar		4755	Bank Deposit Services
4140	Kitchen		4760	Bank Charges
4150	Dishroom		4765	Accounting Services
4160	Office		4770	Legal & Professional
			4775	Security / Alarm
4200	EMPLOYEE BENEFITS		4780	Training
4210	Payroll Taxes		4785	Miscellaneous
4220	Worker's Compensation Insurance
4230	Group Insurance		4800	REPAIRS & MAINTENANCE
4240	Management Meals		4810	Maintenance Contracts
4250	Employee Meals		4820	R&M - Equipment
4260	Awards & Prizes		4830	R&M - Building
4270	Employee Parties & Sports Activities		4840	Grounds Maintenance
4280	Medical Expenses		4850	Parking Lot
4300	DIRECT OPERATING EXPENSES		5000	OCCUPANCY COSTS
4305	Auto & Truck Expense		5010	Rent
4310	Uniforms		5020	Equipment Rental
4315	Laundry & Dry Cleaning		5030	Real Estate Taxes
4320	Linen		5040	Personal Property Taxes
4325	Tableware		5050	Insurance-Property & Casualty
4330	Silverware		5060	Other Municipal Taxes
4335	Kitchen Utensils
4340	Paper Supplies		6000	DEPRECIATION & AMORTIZATION
4345	Bar Supplies		6010	Buildings
4350	Restaurant Supplies		6020	Furniture, Fixtures & Equipment
4355	Cleaning Supplies		6030	Amortization of Leasehold Improvements
4360	Contract Cleaning
4365	Menu & Wine List		7000	OTHER (INCOME) EXPENSE
4370	Pest Control		7010	Vending Commissions
4375	Flowers & Decorations		7020	Telephone Commissions
4380	Licenses & Permits		7030	Waste Sales
4385	Banquet & Event Expenses		7040	Interest Expense
4390	Other Operating Expenses		7050	Officers Salaries & Expenses
			7060	Corporate Office Expenses
4400	MUSIC & ENTERTAINMENT
4410	Musicians & Entertainers
4420	Cable TV/Wire Services
4430	Royalties to ASCAP, BMI

Completed Sample

Restaurant Break-Even Calculation Worksheet
			Lowest Sales Jim Laube: Lowest sales month or 4 week period in the past year.		Average Sales Jim Laube: Average monthly or period sales in the past year.		Highest Sales Jim Laube: Highest sales month or 4 week period in the past year.		Break-Even Estimate Jim Laube: Based on actual results on left, use your judgement to estimate what the variable cost % and fixed costs would be at what you thing your break-even sales level would be. This is only a starting point. You can modify and tweak this model at any time.
				%		%		%
SALES			37,251	100.0	45,861	100.0	54,881	100.0
COST OF SALES			12,577	33.8	15,058	32.8	17,592	32.1	32.5%	Variable Cost
GROSS PROFIT			24,674	66.2	30,803	67.2	37,289	67.9
PAYROLL
	Management		4,200	11.3	4,200	9.2	4,200	7.7	4,200	Fixed Cost
	Hourly Personnel		7,324	19.7	8,157	17.8	8,954	16.3	7,500 Jim Laube: Hourly payroll has both fixed cost and variable cost characteristics. Here, estimate the amount of total hourly payroll at an estimated break-even sales level. You will estimate the percentage of variable payroll cost below. Hang in there. It will soon start to make sense.	Mixed Cost
	Taxes & Benefits		2,535	6.8	2,719	5.9	2,894	5.3	2,675	Mixed Cost
		Total Payroll	14,059	37.7	15,076	32.9	16,048	29.2
CONTROLLABLE EXPENSES
	Direct Operating Exp.		1,351	3.6	1,158	2.5	1,423	2.6	1,200	Mostly Fixed
	Music & Entertain.		285	0.8	280	0.6	280	0.5	280	Mostly Fixed
	Marketing		965	2.6	1,125	2.5	1,395	2.5	1,125	Mostly Fixed
	Utility Services		1,524	4.1	1,425	3.1	1,258	2.3	1,450	Mostly Fixed
	Admin. & General		1,158	3.1	1,254	2.7	1,058	1.9	1,250	Mostly Fixed
	Repairs & Maint.		689	1.8	650	1.4	551	1.0	650	Mostly Fixed
		Total Controll. Exp.	5,972	16.0	5,892	12.8	5,965	10.9
CONTROLL. PROFIT			4,643	12.5	9,835	21.4	15,276	27.8
OCCUPANCY COSTS
	Rent		2,350	6.3	2,350	5.1	2,350	4.3	2,350	Fixed Cost
	Property Taxes		450	1.2	450	1.0	450	0.8	450	Fixed Cost
	Other Taxes		226	0.6	521	1.1	385	0.7	521	Fixed Cost
	Property Insurance		1,125	3.0	1,125	2.5	1,125	2.0	1,125	Fixed Cost
		Total Occup.Costs	4,151	11.1	4,446	9.7	4,310	7.9
	Interest		698	1.9	725	1.6	719	1.3	725	Fixed Cost
	Depreciation		942	2.5	942	2.1	942	1.7	942	Fixed Cost
	Other Expenses		756	2.0	479	1.0	852	1.6	480	Mostly Fixed
	Other (Income) Jim Laube: Enter total income from rebates, commissions, etc. as a negative number. For example - (1,000).		(225)	(0.6)	(442)	(1.0)	(325)	(0.6)	(400)	Mostly Fixed
INCOME BEFORE TAX			(1,679)	(4.5)	3,685	8.0	8,778	16.0
							Total Fixed Costs		26,523
Pacific Park Grille
Break-Even & Operating Profit Model
			Break-		Average		High
			Even	%	Sales	%	Sales	%
SALES			39,293	100.0	45,000	100.0	50,000	100.0
COST OF SALES			12,770	32.5	14,625	32.5	16,250	32.5
GROSS PROFIT			26,523	67.5	30,375	67.5	33,750	67.5
PAYROLL
	Management		4,200	10.7	4,200	9.3	4,200	8.4
	Hourly Personnel		7,500	19.1	8,154	18.1	8,726	17.5
	Taxes & Benefits		2,675	6.8	2,824	6.3	2,955	5.9
		Total Payroll	14,375	36.6	15,178	33.7	15,882	31.8
CONTROLLABLE EXPENSES
	Direct Operating Exp.		1,200	3.1	1,200	2.7	1,200	2.4
	Music & Entertain.		280	0.7	280	0.6	280	0.6
	Marketing		1,125	2.9	1,125	2.5	1,125	2.3
	Utility Services		1,450	3.7	1,450	3.2	1,450	2.9
	Administrative & General		1,250	3.2	1,250	2.8	1,250	2.5
	Repairs & Maintenance		650	1.7	650	1.4	650	1.3
		Total Controllable Expenses	5,955	15.2	5,955	13.2	5,955	11.9
CONTROLLABLE PROFIT			6,193	15.8	9,242	20.5	11,913	23.8
OCCUPANCY COSTS
	Rent		2,350	6.0	2,350	5.2	2,350	4.7
	Property Taxes		450	1.1	450	1.0	450	0.9
	Other Taxes		521	1.3	521	1.2	521	1.0
	Property Insurance		1,125	2.9	1,125	2.5	1,125	2.3
		Total Occup.Costs	4,446	11.3	4,446	9.9	4,446	8.9
OTHER COSTS & EXPENSES
	Interest		725	1.8	725	1.6	725	1.5
	Depreciation		942	2.4	942	2.1	942	1.9
	Other Expenses		480	1.2	480	1.1	480	1.0
	Other (Income)		(400)	(1.0)	(400)	(0.9)	(400)	(0.8)
INCOME BEFORE TAX			(0)	(0.0)	3,129	7.0	5,800	11.6
% Increase in Sales Over Break-Even			-		14.5%		27.2%
% of Variable Hourly Labor Cost			60% Jim Laube: Hourly labor cost in most restaurant tend to run 50% - 70% variable cost to fixed cost above the sales break-even point. You can adjust this number according to how your hour labor cost tends to change with sales level changes.
Sales Equivalents:
	Number of Periods per Year		12 Jim Laube: If on a monthly P&L cycle put "12", if on a 4 week cycle put "13".
	Annual Sales		471,520		540,000		600,000
	Average Weekly Sales		9,068		10,385		11,538

From Actual P&L's Within The Last 12 Months