https://docs.google.com/spreadsheets/d/1qcy244ET5ba-f5SyZukw2x04kQmJ9ky_/edit?usp=sharing&ouid=109827163743961910244&rtpof=true&sd=true

EXCEL FILE

FOOD AND BEVERAGE COST AND LABOR CONTROL

On the below statement: (9 MARKS) A. Assume that the variable costs were not controlled and that the budgeted costs of sales for food and beverage increase to $600,000 (instead of the amount established in Question 4). Total sales are as budgeted (in Question 4). Calculate the profit for Barnaby's Hideaway. All other costs remain the same. B. Calculate the new variable rate and new contribution rate, assuming the same variable salaries and employee benefits. The fixed portion of of Salaries and Wages is 40%. C. Calculate the break-even point for Barnaby's Hideaway using the new contribution rate. D. Using the new contribution rate, calculate the sales level necessary for Barnaby's Hideaway to earn the budgeted profit of $166, 794.43 E. Assume that the new contribution margin falls to $12.60. Calculate the number of customers necessary to break even. New Budgeted Profit New Food & Beverage Cost $600.000.00 Old Food & Beverage Cost $494.68 Reduction in Profit $181,529 (1 MARK) Budgeted Profit $166.794.43 Reduction in Profit New Budgeted Profit $ [1 MARK] New Variable Rate Cost of Food and Beverage $600,000.00 Variable Labour Cost $247.441.58 Variable Employee Benefits $61.860.40 Total Variable Costs $909.301.98New Variable Rate Sales Level Necessary to Earn a Profit of $166.794.43 VR= VC S S = FC+ P CR VR= $ SE 0.4 VR= (1 MARK] S = New Contribution Rate CR = 1- VR S = (2 MARKS] CR = 1-.5459 New Unit Sales to Break Even CR = (1 MARK] SU = EC Av CM per Unit New Break Even SU = BE = EC+ 2 CR SU = (2 MARKS] BE = BE = (1 MARK]For the Income Statement for Barnaby's Hideaway the following changes are anticipated in the coming year: (26 MARKS) Both Food and Beverage Sales are expected to increase by 5%% Food and Beverage cost percents will remain the same Salaries and wages will increase by 4% The cost of employee benefits will increase, but will continue to be the same percentage of salaries and wages Other Controllable Costs will increase by $6500 Occupancy Costs will increase by $2000 nterest and Depreciation will remain the same Prepare the operating Budget for the coming year BARNABY'S HIDEAWAY INCOME STATEM YEAR ENDED DECEMBER 31, 20XX Change Upcoming Sales Food ###### ### (2 MARKS) Beverage $465,200.00 (2 MARKS) Total Sales $1,586.164.00 (1 MARK] Cost of Sales Food $392,337.00 (2 MARKS) Beverage $102.344.00 (2 MARKS) Total Cost of Sales $494,681.00 (1 MARK] Gross Profit $1,091,483.00 $ (1 MARK] Controllable Expenses Salaries and Wages $396,541.00 0.24993937 (2 MARKS) Employee Benefits $99.135.00 (2 MARKS) Other Controllable Expenses $275,330.00 (2 MARKS] Total Controllable Expenses #771,006.00 $ (1 MARK] Income Before Docu ancy Costs. $320,477.00 (1 MARK) Interest, Depreciation, and Income Taxes Docupancy Costs $75,230.00 (2 MARKS) Interest $25,600.00 (1 MARK] Depreciation $79,099.00 (1 MARK] Total $179,929.00 (1 MARK] Restaurant Profit $140,548.00 (2 MARKS)A. Barnaby's Hideaway uses the perpetual B. Given the following amounts found on the inventory method for its non-perishable shelf, and using the periodic ordering foods. Given the following information, system, determine the amounts to order for determine the reorder point and the the following items. Use the usage and case reorder quantity for each of the following sizes in part A. Delivery is every two weeks. items. Add 50% to the amount needed until Add 50% to the amounts calculated as a delivery as a safety factor when safety factor. (6 MARKS) Canned Tomato Juice Canned Tomato Juice Par Stock 24 Amount required for upcoming period (1/2 MARK) Reorder Point (1/2 MARK) Amount on hand 10 cans Subtotal (1/2 MARK) (1/2 MARK) Time to get delivery 5 days Amount needed until delivery + safety factor (1/2 MARK) Normal Usage Until Delivery 2 cans/day Amount to order (1/2 MARK) Reorder Quantity (1 MARK) Number of cases (1 MARK) Number of cans in a case 6 Canned Peaches Canned Peaches Amount required for upcoming period (1/2 MARK) Par Stock 36 Amount on hand 9 cans Reorder Point (1/2 MARK) (1/2 MARK) Subtotal (1/2 MARK) Amount needed until delivery + safety factor (1/2 MARK) Time to get delivery 4 days Amount to order (1/2 MARK) Normal Usage Until Delivery 3 cans/day Number of cases (1 MARK) Reorder Quantity (1 MARK) Number of cans in a case 12 TOTAL MARKS 0 0 4 MARKS 6 MARKSCalculate the Food, Beverage and Labour Cost percents For Barnaby's Hideaway ( 4 MARKS) BARNABY'S HIDEAWAY INCOME STATEMENT YEAR ENDED DECEMBER 31, 20XX Food $1,120,964.00 Beverage 5 465,200.00 Total Sales Cost of Sales Food $ 392,337.00 Beverage 102.344.00 Total Cost of Sales Gross Profit Controllable Expenses Salaries and Wages S 396,541.00 Employee Benefits S 99,135.00 Other Controllable Expenses 275,330.00 Total Controllable Expenses Income Before Occug ancy Costs, Interest, Depreciation, and Income Taxes Occupancy Costs S 75,230.00 Interest S 25,600.00 Depreciation 79,099.00 Total Restaurant Profit MAKE SURE TO ANSWER THE $1.,586.164.00 494.681.00 $1,091.483.00 771.006.00 320.477.00 179.929.00 140.548.00 Calculation of Food Cost Percent Food Cost Food Cost % = ood oSt Food Sales $392.337 o, Food Cost % 51,120,964 Food Cost % 35.0%| (1 MARK) Calculation of Beverage Cost Percent Beverage Cost Beverage Cost % = g "~ Beverage Sales $102 344 o, Beverage Cost % 5465200 Beverage Cost % (1 MARK) TOTAL MARKS TOTAL WORKSHEET MARK Calculation of Labour Cost 5396541 599,135 Salaries and Wages Employee Benefits Labour Cost 495.676 Calculation of Labour Cost Percent Labour Cost % = Labour Cost Total Sales $495 676 o, Labour Cost % %1536 164 Labour Cost % 4 MARKS 55 MARKS (1 MARI Calculate overhead as a percentage of total sales for Barnaby's Hideaway (2 MARKS) BARNABY'S HIDEAWAY INCOME STATEMENT YEAR ENDED DECEMBER 31, 20XX Calculation of Overhead Costs Other Controllable Expenses Sales Occupancy Costs Food $1,120,964.00 Interest Beverage $465,200.00 Depreciation Total Sales $1,586, 164.00 Total Overhead Costs (1 MARK) Cost of Sales Food $392,337.00 Calculation of Overhead Cost Percent Beverage $102,344.00 Total Cost of Sales 6494, 681.00 Total Overhead Costs Cost Percent = Gross Profit $1,091,483.00 Total Sales Controllable Expenses Salaries and Wages $396,541.00 Employee Benefits $99,135.00 Cost Percent Other Controllable Expenses $275,330.00 Total Controllable Expenses $771,006.00 Income Before Occupancy Costs, $320,477.00 Cost Percent (1 MARK) Interest, Depreciation, and Income Taxes Occupancy Costs $75,230.00 Interest $25,600.00 0 Depreciation $79,099.00 TOTAL MARKS 2 MARKS Total $179,929.00 Restaurant Profit $140,548.00Calculate Prime Cost as a percentage of Total Costs for Barnaby's Hideaway. When completed, add the cost percents for Prime Cost, Overhead Costs and Profit to arrive at 100% (4 MARKS) BARMNABY'S HIDEAWAY INCOME STATEMENT Food Beverage Total Sales Cost of Sales Food Beverage Total Cost of Sales Gross Profit Controllable Expenses Salaries and Wages Employee Benefits Other Controllable Expenses Total Controllable Expenses Income Before Occugancy Costs, Interest, Depreciation, and Income Taxes Occupancy Costs Interest Depreciation Total Restaurant Profit EAR ENDED DECEMBER 31, 20XX $1,120,964.00 $465,200.00 $1.586,164.00 $392,337.00 $102,344.00 5494.681.00 $1.091,483.00 $396,541.00 $99,135.00 $275,330.00 5771.006.00 $320.477.00 $75,230.00 $25,600.00 $79.099.00 $179,929.00 $140,548.00 Calculation of Prime Cost Total Cost of Sales Labour Cost Prime Cost (1 MARK) Calculation of Prime Cost Percentage Calculation of Profit Percentage Calculation of Total Cost Percents Prime Cost % Owverhead Cost % Profit % Total (1 MARK) Prime Cost % Prime Cost % Profit %