excel text book project

Marylynd

NP_EX_5-1_MaryLynDanielakcopy.ods

Home >Business & Finance homework help >excel text book project

pavI8MeUFtEyxX2I4tky00d6ebc7-48ef-4a2c-9669-2f28b9eae395

Tibetan Grill

Author

Date

Purpose

To consolidate profit and loss statements from Region 5 (Illinois and Iowa) Tibetan Grill franchises into a single summary report and to compare each store's profit and loss statement against industry benchmarks.

Tibetan Grill

Region 5: Illinois and Iowa Franchises

Annual Profit & Loss Statement ending March 31, 2024

SALES

2024

Food Sales

Beverage Sales

TOTAL SALES

COST OF GOODS SOLD

Food Costs

Beverage Costs

TOTAL COST OF GOODS SOLD

PAYROLL COSTS

Wages

Employee Benefits

Payroll Tax

TOTAL PAYROLL COSTS

PRIME COSTS

OPERATING EXPENSES

Maintenance

Utilities

Administrative

Marketing

Other Expenses

TOTAL OPERATING EXPENSES

CONTROLLABLE PROFIT

OCCUPANCY COSTS

Rent

Property Taxes

Insurance

TOTAL OCCUPANCY COSTS

INTEREST AND DEPRECIATION

Interest

Depreciation

TOTAL INTEREST AND DEPRECIATION

PRETAX PROFIT

Tibetan Grill
Annual Profit & Loss Statement

SALES		Percent of Sales	Benchmark	Region	5
Food Sales	$5,371,576	82.2%		Franchise Number	G5-01
Beverage Sales	1,163,073	17.8%		Franchise Manager	April Molinero
TOTAL SALES	6,534,649	100.0%		Franchise Location	563 Mack Street Chicago, IL 60623
				Franchise Location	563 Mack Street Chicago, IL 60623
COST OF GOODS SOLD				Contact Number	(773) 555-2061
Food Costs	1,669,965	31.1%
Beverage Costs	324,816	27.9%
TOTAL COST OF GOODS SOLD	1,994,781	30.5%

PAYROLL COSTS
Wages	1,740,000	26.6%
Employee Benefits	414,000	6.3%
Payroll Tax	220,845	3.4%
TOTAL PAYROLL COSTS	2,374,845	36.3%

PRIME COSTS	4,369,626	66.9%

OPERATING EXPENSES
Maintenance	100,000	1.5%
Utilities	285,655	4.4%
Administrative	143,200	2.2%
Marketing	126,000	1.9%
Other Expenses	101,425	1.6%
TOTAL OPERATING EXPENSES	756,280	11.6%

CONTROLLABLE PROFIT	1,408,743	21.6%

OCCUPANCY COSTS
Rent	360,575	5.5%
Property Taxes	193,204	3.0%
Insurance	125,491	1.9%
TOTAL OCCUPANCY COSTS	679,270	10.4%

INTEREST AND DEPRECIATION
Interest	197,092	3.0%
Depreciation	113,500	1.7%
TOTAL INTEREST AND DEPRECIATION	310,592	4.8%

Tibetan Grill
Annual Profit & Loss Statement

SALES		Percent of Sales	Benchmark	Region	5
Food Sales	$1,784,016	84.3%		Franchise Number	G5-02
Beverage Sales	332,109	15.7%		Franchise Manager	Ramón Suarez
TOTAL SALES	2,116,125	100.0%		Franchise Location	340 Lotus Street Des Moines, IA 50312
				Franchise Location	340 Lotus Street Des Moines, IA 50312
COST OF GOODS SOLD				Contact Number	(515) 555-3686
Food Costs	516,439	28.9%
Beverage Costs	109,509	33.0%
TOTAL COST OF GOODS SOLD	625,948	29.6%

PAYROLL COSTS
Wages	562,500	26.6%
Employee Benefits	130,500	6.2%
Payroll Tax	72,581	3.4%
TOTAL PAYROLL COSTS	765,581	36.2%

PRIME COSTS	1,391,529	65.8%

OPERATING EXPENSES
Maintenance	33,000	1.6%
Utilities	87,750	4.1%
Administrative	44,550	2.1%
Marketing	39,000	1.8%
Other Expenses	53,672	2.5%
TOTAL OPERATING EXPENSES	257,972	12.2%

CONTROLLABLE PROFIT	466,624	22.1%

OCCUPANCY COSTS
Rent	112,500	5.3%
Property Taxes	44,846	2.1%
Insurance	39,671	1.9%
TOTAL OCCUPANCY COSTS	197,016	9.3%

INTEREST AND DEPRECIATION
Interest	57,747	2.7%
Depreciation	32,600	1.5%
TOTAL INTEREST AND DEPRECIATION	90,347	4.3%

Tibetan Grill
Annual Profit & Loss Statement

SALES		Percent of Sales	Benchmark	Region	5
Food Sales	$5,361,021	81.8%		Franchise Number	G5-03
Beverage Sales	1,195,716	18.2%		Franchise Manager	Sajja Adulet
TOTAL SALES	6,556,737	100.0%		Franchise Location	423 South Canfield Avenue Chicago, IL 60607
				Franchise Location	423 South Canfield Avenue Chicago, IL 60607
COST OF GOODS SOLD				Contact Number	(312) 555-8817
Food Costs	1,735,398	32.4%
Beverage Costs	366,021	30.6%
TOTAL COST OF GOODS SOLD	2,101,419	32.0%

PAYROLL COSTS
Wages	1,725,750	26.3%
Employee Benefits	401,250	6.1%
Payroll Tax	274,461	4.2%
TOTAL PAYROLL COSTS	2,401,461	36.6%

PRIME COSTS	4,502,880	68.7%

OPERATING EXPENSES
Maintenance	172,500	2.6%
Utilities	244,374	3.7%
Administrative	156,021	2.4%
Marketing	144,000	2.2%
Other Expenses	172,224	2.6%
TOTAL OPERATING EXPENSES	889,119	13.6%

CONTROLLABLE PROFIT	1,164,738	17.8%

OCCUPANCY COSTS
Rent	330,900	5.0%
Property Taxes	190,350	2.9%
Insurance	134,136	2.0%
TOTAL OCCUPANCY COSTS	655,386	10.0%

INTEREST AND DEPRECIATION
Interest	213,024	3.2%
Depreciation	94,200	1.4%
TOTAL INTEREST AND DEPRECIATION	307,224	4.7%

Tibetan Grill
Annual Profit & Loss Statement

SALES		Percent of Sales	Benchmark	Region	5
Food Sales	$1,050,387	81.3%		Franchise Number	G5-04
Beverage Sales	241,652	18.7%		Franchise Manager	Travon Hughes
TOTAL SALES	1,292,039	100.0%		Franchise Location	122 Henke Street Rockford, IL 61101
				Franchise Location	122 Henke Street Rockford, IL 61101
COST OF GOODS SOLD				Contact Number	(815) 555-8275
Food Costs	311,879	29.7%
Beverage Costs	78,401	32.4%
TOTAL COST OF GOODS SOLD	390,280	30.2%

PAYROLL COSTS
Wages	425,300	32.9%
Employee Benefits	84,117	6.5%
Payroll Tax	46,175	3.6%
TOTAL PAYROLL COSTS	555,592	43.0%

PRIME COSTS	945,872	73.2%

OPERATING EXPENSES
Maintenance	28,350	2.2%
Utilities	49,100	3.8%
Administrative	31,400	2.4%
Marketing	32,500	2.5%
Other Expenses	29,781	2.3%
TOTAL OPERATING EXPENSES	171,131	13.2%

CONTROLLABLE PROFIT	175,036	13.5%

OCCUPANCY COSTS
Rent	71,200	5.5%
Property Taxes	38,400	3.0%
Insurance	29,350	2.3%
TOTAL OCCUPANCY COSTS	138,950	10.8%

INTEREST AND DEPRECIATION
Interest	36,200	2.8%
Depreciation	18,400	1.4%
TOTAL INTEREST AND DEPRECIATION	54,600	4.2%

Tibetan Grill
Annual Profit & Loss Statement

SALES		Percent of Sales	Benchmark	Region	5
Food Sales	$1,201,300	81.5%		Franchise Number	G5-05
Beverage Sales	272,652	18.5%		Franchise Manager	Tami Brooks
TOTAL SALES	1,473,952	100.0%		Franchise Location	14 Clark Court Iowa City, IA 52246
				Franchise Location	14 Clark Court Iowa City, IA 52246
COST OF GOODS SOLD				Contact Number	(319) 555-8954
Food Costs	391,481	32.6%
Beverage Costs	85,214	31.3%
TOTAL COST OF GOODS SOLD	476,695	32.3%

PAYROLL COSTS
Wages	415,400	28.2%
Employee Benefits	80,250	5.4%
Payroll Tax	34,700	2.4%
TOTAL PAYROLL COSTS	530,350	36.0%

PRIME COSTS	1,007,045	68.3%

OPERATING EXPENSES
Maintenance	30,250	2.1%
Utilities	49,871	3.4%
Administrative	35,117	2.4%
Marketing	37,500	2.5%
Other Expenses	24,158	1.6%
TOTAL OPERATING EXPENSES	176,896	12.0%

CONTROLLABLE PROFIT	290,011	19.7%

OCCUPANCY COSTS
Rent	85,250	5.8%
Property Taxes	43,175	2.9%
Insurance	35,288	2.4%
TOTAL OCCUPANCY COSTS	163,713	11.1%

INTEREST AND DEPRECIATION
Interest	44,800	3.0%
Depreciation	29,250	2.0%
TOTAL INTEREST AND DEPRECIATION	74,050	5.0%

Tibetan Grill
Annual Profit & Loss Statement

SALES		Percent of Sales	Benchmark	Region	5
Food Sales	$2,931,720	81.5%		Franchise Number	G5-06
Beverage Sales	664,443	18.5%		Franchise Manager	Julie Mahoney
TOTAL SALES	3,596,163	100.0%		Franchise Location	22 West Maple Street Springfield, IL 62704
				Franchise Location	22 West Maple Street Springfield, IL 62704
COST OF GOODS SOLD				Contact Number	(217) 555-0022
Food Costs	873,534	29.8%
Beverage Costs	198,624	29.9%
TOTAL COST OF GOODS SOLD	1,072,158	29.8%

PAYROLL COSTS
Wages	1,044,750	29.1%
Employee Benefits	176,079	4.9%
Payroll Tax	72,624	2.0%
TOTAL PAYROLL COSTS	1,293,453	36.0%

PRIME COSTS	2,365,611	65.8%

OPERATING EXPENSES
Maintenance	96,300	2.7%
Utilities	120,744	3.4%
Administrative	93,765	2.6%
Marketing	93,600	2.6%
Other Expenses	58,383	1.6%
TOTAL OPERATING EXPENSES	462,792	12.9%

CONTROLLABLE PROFIT	767,760	21.3%

OCCUPANCY COSTS
Rent	183,750	5.1%
Property Taxes	99,594	2.8%
Insurance	90,966	2.5%
TOTAL OCCUPANCY COSTS	374,310	10.4%

INTEREST AND DEPRECIATION
Interest	123,900	3.4%
Depreciation	74,000	2.1%
TOTAL INTEREST AND DEPRECIATION	197,900	5.5%

Tibetan Grill
Annual Profit & Loss Statement

SALES		Percent of Sales	Benchmark	Region	5
Food Sales	$1,597,424	82.4%		Franchise Number	G5-07
Beverage Sales	341,829	17.6%		Franchise Manager	Michael Cooper
TOTAL SALES	1,939,253	100.0%		Franchise Location	582 Corbett Avenue Urbana, IL 61802
				Franchise Location	582 Corbett Avenue Urbana, IL 61802
COST OF GOODS SOLD				Contact Number	(217) 555-7203
Food Costs	512,642	32.1%
Beverage Costs	91,489	26.8%
TOTAL COST OF GOODS SOLD	604,131	31.2%

PAYROLL COSTS
Wages	545,250	28.1%
Employee Benefits	125,000	6.4%
Payroll Tax	64,189	3.3%
TOTAL PAYROLL COSTS	734,439	37.9%

PRIME COSTS	1,338,570	69.0%

OPERATING EXPENSES
Maintenance	34,150	1.8%
Utilities	73,289	3.8%
Administrative	41,400	2.1%
Marketing	42,150	2.2%
Other Expenses	38,162	2.0%
TOTAL OPERATING EXPENSES	229,151	11.8%

CONTROLLABLE PROFIT	371,532	19.2%

OCCUPANCY COSTS
Rent	83,900	4.3%
Property Taxes	53,200	2.7%
Insurance	39,850	2.1%
TOTAL OCCUPANCY COSTS	176,950	9.1%

INTEREST AND DEPRECIATION
Interest	55,178	2.8%
Depreciation	39,400	2.0%
TOTAL INTEREST AND DEPRECIATION	94,578	4.9%

Tibetan Grill

Terms and Definitions

Benchmarks

Industry Standard

Notes

COGS Percent

30%

Cost of goods as a percent of total sales

Payroll Percent

35%

Total payroll costs as a percent of total sales

Prime Cost Percent

65%

Cost of goods and labor as a percent of sales

Controllable Profit Percent

22%

Controllable profit as a percent of total sales

Occupancy Percent

10%

Total occupancy costs as a percent of total sales

Pretax Profit Percent

Pretax profit as a percent of total sales

Term

Definition

Total Sales

Total Sales measures the income from the sale of food and beverage menu items at the franchise.

Cost of Goods Sold (COGS)

Cost of Goods Sold measures the raw material cost for food and beverage; increasing as sales volume increases.

Payroll Costs

Payroll Costs measures the cost of salaries, wages, employee benefits, and payroll taxes.

Prime Costs

Prime Costs measures the sum of COGS and Payroll Costs and is considered the largest area of expenditure for the franchise with typically about 2/3rds of expenses coming from Prime Costs.

Operating Costs

Operating Costs measures the expenses required for franchise operation not directly related to COGS and Payroll Costs.

Controllable Profit

Controllable Profit is profit considering only those expense factors under the franchise's direct control (Prime Costs and Operating Costs).

Occupancy Costs

Occupancy Costs are expenses directly related to occupancy of the franchise space, including rent, property taxes, and insurance.

Interest

Interest measures the amount paid in interest of loans to fund the purchase and operation of the franchise; it does not include payments made to pay off the principal of any loan.

Depreciation

Depreciation measures the amount by which the franchise's material assets (cooking equipment, seating space, infrastructure) decline in value.

Pretax Profit

Pretax Profit is the restaurant profit, calculated by subtracting Prime Costs, Operating Expenses, Occupancy Costs, Interest, and Depreciation from Total Sales. Successfully-run franchises should show a Pretax Profit of at least 4% of their Total Sales.