Question

PLEASE SOLVE THIS QUESTION IN AN EXCEL SHEET ?

1 a. Given that food sales are $450,000 for the year and beverages sales are 80,000, prepare   a statement of income for Mavericks Restaurant using the expenses below. (Use Figure 1.1 on pg 5 as a guide). Hint: Remember “controllable fixed” costs fall under Other Controllable Expenses. (round to two decimal points)

Administrative Expenses     $4,000

Employee Benefits $27,550

Cost of food sold $140,000

Salaries and wages $120,000

Utilities $26,000

Depreciation on equipment $14,100

Cost of beverages sold $16,230

Interest expense     $3,100

Occupancy costs $33,200

Advertising     $5,000

b. Based on the figures above, calculate:

Prime cost percent

Overhead cost percent

Profit percent

(Remember to ensure they equal up to 100%)

Labor cost percent

Food cost percent

Answer 1

1a) INCOME STATEMENT:		Amount $
Sales revenue:
Food	450000
Beverage	80000
Total Sales revenue		530000
Cost of Sales
Cost of food sold	140000
Cost of beverages sold	16230
Total Cost of Sales		156230
Gross Profit		373770
Less: Controllable Costs:
Salaries and Wages	130000
Employee Benefits	27550
Utilities	26000
Advertising	5000
Total Controllable costs		188550
Income before Non-Controllable costs:		185220
Less: Non Controllable Costs:
Occupancy costs	33200
Interest	3100
Depreciation on equipment	14100
Total Non-Controllable costs		50400
Restaurant Income		134820
b.)
Prime cost percent	Cost of sales / Sales = 156230/530000= 29.48%
Overhead percent	(Controllable + Non-Controllable costs)/Sales
	(188550+50400)/530000 = 45.08%
Profit persent	Restaurant Income / Sales = 134820/530000 = 25.44%
Labor cost percent	(Salaries & wages exp. + employee benefits)/Sales
	(130000+27550)/530000 = 29.73%
Food cost percent	Cost of food sold / Sales = 140000/530000 = 26.41%