Need help with question 9. Having difficulty calculating next year budget columns.

Chapter 10, Question 1. a. Boniso operates Boniso's Mexican Restaurant in an urban city in the South. He has worked hard at setting up cost control systems, and he is generally happy with his overall results. However, he is not sure if all of his menu items are providing profitability for his restaurant. He decides to use food cost matrix and contribution margin matrix analyses to study each of his menu items. a. Complete his menu analysis worksheet. Menu Analysis Worksheet Menu Item Fajita Plate Enchilada Dinner Menudo Mexican Salad Chalupa Dinner Burrito Dinner Taco Dinner Total Weighted Average Chapter 10 Q 1.a. Item Total Number Selling Contribution Contribution Food Sold Price Total Sales Item Cost Total Cost Margin Margin Cost % 147 $12.95 $1,903.65 $4.92 $723.24 $8.03 $1,180.41 38% 200 $9.95 $1,990.00 $3.48 $696.00 $6.47 $1,294.00 35% 82 $6.95 $569.90 $1.74 $142.68 $5.21 $427.55 25% 117 $7.95 $930.15 $2.39 $279.63 $5.56 $650.52 30% 125 $8.95 $1,118.75 $2.51 $313.75 $6.44 $805.00 28% 168 $9.95 $1,671.60 $3.25 $546.00 $6.70 $1,125.60 33% 225 $5.95 $1,338.75 $1.55 $348.75 $4.40 $990.00 26% 1,064 $9,522.80 $3,050.05 $6,473.08 152 $8.95 $1,360.40 $2.87 $435.72 $6.08 $924.73 32% Chapter 10, Question 1. b. Using the results of Boniso's menu analysis worksheet (in part a), fill in the appropriate average food cost, popularity, and contribution margin in the blanks below. Then, place the menu items in the appropriate squares in the matrices. Food Cost Matrix High Food Cost % (Above 32 %) fajita enchilada, burrito menudo, mexican salad, chalupa taco Low Popularity (Below 152 Sales) High Popularity (Above 152 Sales) High Contribution Margin (Above $ 6.08 ) fajita, chalupa enchilada, burrito Low Contribution Margin (Below $ 6.08 ) menudo, mexican salad taco Low Popularity (Below 152 Sales) High Popularity (Above 152 Sales) Low Food Cost % (Below 32 %) Contribution Margin Matrix Chapter 10 Q 1.b. Chapter 10, Question 2 Garikai is a manager at Boniso's Mexican Restaurant (from the previous question), and he believes that goal value analysis, rather than Boniso's matrix analysis, is a better way to study the profitability of his menu items. a. Using the following goal value analysis data, help Garikai analyze the restaurant's menu items. 2.a. After computing the following goal values, sort (in descending rank order) by goal value. Be sure to include the overall menu in the appropriate rank order. Spreadsheet hint: Calculate the goal values by placing the ENTIRE Goal Value formula for each item in the Goal Value column as follows: (1 Food Cost %) Item Popularity Selling Price (1 (Variable Cost % + Food Cost %)). The table will not sort correctly if partial calculations for Goal Value exist in columns A, B, C, or D. For example, DO NOT calculate (1 Food Cost %) in the A column or (1 (Variable Cost % + Food Cost %)) in the D column. Goal Value Analysis Results Item Fajita Plate Enchilada Dinner Menudo Mexican Salad Chalupa Dinner Burrito Dinner Taco Dinner Overall Menu (Goal Value) Food Cost % Number Selling Variable Cost % (in decimal form) Sold Price (in decimal form) 0.38 147 $12.95 0.28 0.35 200 $9.95 0.28 0.25 82 $6.95 0.28 0.30 117 $7.95 0.28 0.28 125 $8.95 0.28 0.33 168 $9.95 0.28 0.26 225 $5.95 0.28 0.32 152 $8.95 0.28 2.b. b. After analyzing his menu items, Garikai believes he can improve the chalupa dinner by lowering the selling price to $8.55. He believes that this lower price will increase number of chalupa dinners sold to 150. However, the change in price will increase both his food cost percentage and variable cost percentage to 29 percent for the chalupa dinner. Results of Changes Made to Chalupa Dinner Item "A" "B" Food Cost % Number (in decimal form) Sold "C" Selling Price "D" Variable Cost % (in decimal form) Chalupa Dinner If he makes these changes, will the chalupa dinner meet or exceed the overall menu goal value? Should Garikai make these changes? Answer: Chapter 10 Q2 If he makes these changes, will the chalupa dinner meet or exceed the overall menu goal value? Should Garikai make these changes? Answer: Chapter 10 Q2 sure to include the overall he ENTIRE Goal Value rity Selling Price (1 oal Value exist in columns A, ble Cost % + Food Cost %)) Goal Value Goal Value value? Chapter 10 Q2 value? Chapter 10 Q2 Chapter 10, Question 3 Eunice manages a Thai restaurant in a primarily Asian section of a major West Coast city. She is interested in determining dollar sales and number of guests needed to break even and to generate her desired profits. Her check average (selling price) is $16.00, her variable cost per unit (guest) is $5.60, and her fixed costs are $170,000. 3. a. a. Complete the following grid, and determine her before-tax profit. Per Unit (Guest) Percentage SP VC CM Fixed costs Desired after-tax profit Tax rate Before-tax profit $170,000.00 $24,000.00 40% 3.b. Using the information from part a, calculate the following. (Spreadsheet hint: Use the ROUNDUP function for \"Rounded upBreak-even point in guest served\" and \"Rounded upGuests served to achieve desired after-tax profit.\") Break-even point in sales dollars Break-even point in guests served Rounded up = Sales dollars to achieve desired after tax profit Guests served to achieve desired after tax profit Rounded up = 3.c. Based on her calculations, Eunice doesn't think that she can attract as many guests as she needs to achieve her desired after-tax profit. Therefore, she has decided to make some changes to improve her situation. Due to these changes, she has been able to reduce her selling price by $1.00, decrease her variable cost percentage by 5%, and lower her fixed costs by $5,000. After these changes, what are Eunice's sales dollars and guests served to achieve her after-tax profit? Complete the following grid and calculations. (Spreadsheet hint: Use the ROUNDUP function for \"Rounded 10 upGuests served to achieve desired after-tax profit.\") Chapter Q3 Based on her calculations, Eunice doesn't think that she can attract as many guests as she needs to achieve her desired after-tax profit. Therefore, she has decided to make some changes to improve her situation. Due to these changes, she has been able to reduce her selling price by $1.00, decrease her variable cost percentage by 5%, and lower her fixed costs by $5,000. After these changes, what are Eunice's sales dollars and guests served to achieve her after-tax profit? Complete the following grid and calculations. (Spreadsheet hint: Use the ROUNDUP function for \"Rounded upGuests served to achieve desired after-tax profit.\") Per Unit (Guest) Percentage SP VC CM Fixed costs Sales dollars to achieve desired after tax profit Guests served to achieve desired after tax profit Chapter 10 Q3 Rounded up = Chapter 10, Question 4 Sinqobile runs a restaurant in an East Coast city that specializes in African-American cuisine. She has compiled her sales and cost data from last year, and she wants to develop a budget for this year. She has projected the following increases for this year: Projected Increases Meals served 3% Selling price per meal 2% Cost of food 5% Cost of labor 10% Other expenses 2% Using this information, help Sinqobile complete her budget. Meals served Selling price per meal Last Year 122,000 Budget $12.50 Last year's food cost per meal: Last year's food cost per meal + Estimated increase: Estimated cost of food this year: Last year's labor cost per meal: Last year's labor cost per meal + Estimated increase: Estimated cost of labor this year: Revenue Cost of food Cost of labor Other expenses Total expenses Profit Last Year $ Last Year % $1,525,000.00 $610,000.00 $488,000.00 $245,760.00 $1,343,760.00 $181,240.00 Budget $ Budget % The owner of the restaurant has requested that Sinqobile make at least a 10 percent profit for this year. Based on her budget figures, is she likely to meet this goal? If not, what can she do to achieve a 10 percent profit? Answer: Chapter 10 Q4 Chapter 10, Question 5 Sitabiso manages an executive dining room in an office building of a major food manufacturing company. Her sales, on average, run between $17,000 and $21,000 per week. She has decided to use yardstick standards for labor to predict labor costs for varying sales levels. With these data, she can determine if variations in her expenses are due to changes in sales volume or other reasons such as waste or theft. She has compiled information from last year to help her predict her weekly labor costs. Using this information, help Sitabiso complete her yardstick standards for labor. (Spreadsheet hint: Use the ROUND function for \"% Cost to Total Sales\" column. Also, round to 3 decimal places, e.g., 0.123 or 12.3%.) Yardstick Standards for Labor Total Sales: Labor Costs: Management Food Production Service Sanitation Total $900,000 Average Sales per Month: $75,000 $84,000 143,000 27,600 34,200 288,800 Weekly Sales Estimate Category Management Food production Service Sanitation Total Chapter 10 Q5 % Cost to % Cost to Total Total Cost Sales $17,000 $18,000 $19,000 $20,000 timate $21,000 Chapter 10 Q5 Chapter 10, Question 6 Toni Lamazza is developing next year's foodservice budget for the Springdale school system, consisting of 17 different schools in a two-county area. Toni knows the revenue she will get from the school board, but is not sure how much the board will give her to pay for anticipated increases in employee benefits. Complete the chart below to help Toni determine the amount employee benefits can increase and still allow her to show a budget surplus. Budget with Employee Benefits Increases Revenue Cost of Food Cost of Payroll Cost of Employee Benefits Other Expenses Total Costs Budget Surplus/ Deficit Current Budget 5% Increase 10% Increase $ 7,000,000 ### $ 7,000,000 $ 2,095,000 ### $ 2,095,000 $ 3,700,000 ### $ 3,700,000 $ $ $ $ 700,000 $ 735,000 $ 400,000 ### $ 6,895,000 $ 7,239,750 $ 105,000 $ 110,250 $ At what level of employee benefit cost increase will Toni have a \"break-even\" budget? How much would her surplus/deficit be if benefits increase by 20 percent? Answer: Toni will break even at a 5% increase. Her surplus would be $126,000. Chapter 10 Q6 770,000 400,000 7,584,500 115,500 yee Benefits Increases 15% Increase $ 7,000,000 $ 2,095,000 $ 3,700,000 $ $ $ $ 805,000 400,000 7,929,250 120,750 Chapter 10 Q6 20% Increase $ 7,000,000 $ 2,095,000 $ 3,700,000 $ $ $ $ 840,000 400,000 8,274,000 126,000 Chapter 10, Question 7 J. D. McAllister is really happy. He has just succeeded in securing a $2,000,000 bank loan with a variable percentage interest rate to build his dream restaurant. J. D.'s loan is for 25 years, and it carries an interest rate that is set at 7 percent for the first year (this year). Thus, his first year's monthly interest payments will be $14,136. An experienced restaurateur, J. D. has prepared the following annual budget for this year. Annual Budget for This Year Total Sales $2,046,000.00 Variable Costs Food Beverage Labor Other Variable Costs Total Variable Costs 429,660.00 257,796.00 572,880.00 171,864.00 $1,432,200.00 Fixed Costs (excluding loan repayment) Loan Repayment (7% interest) Total Fixed Costs $239,568.00 169,632.00 $409,200.00 Before Tax Profit Taxes (40%) After Tax Profit $204,600.00 81,840.00 $122,760.00 J. D.'s interest rate will likely vary over the life of the loan because it is tied to the prime interest rate established by the Federal Reserve Board (part of the federal government). Assume the Fed increases interest rates next year by 0.5 percent, and thus J. D.'s interest rate moves to 7.5 percent. As a result, his new monthly loan repayment will be $14,780. Also assume that his total variable cost percentage and fixed costs (excluding the increased loan repayment) will be the same next year, and his check average (selling price) will be $20 per cover. With the higher mortgage payment, what sales dollars and number of guests served will J. D. need to achieve next year to maintain the same number of after-tax profit dollars as he budgeted for this year? Per Unit (Guest) SP VC CM Fixed costs (excluding loan repayment) Loan repayment (7.5% interest) Chapter 10 Q7 Percentage Total fixed costs Desired after-tax profit Tax rate Before-tax profit Sales dollars to achieve desired after-tax profit Guests served to achieve desired after-tax profit Chapter 10 Q7 Chapter 10, Question 8 V. K. Sing is the manager of the Knight Kap restaurant. V. K. is preparing his budget for next year and would like to estimate his beverage revenue and expense. V. K. kept careful sales and cost records from last year, and these are listed below. Last Year Beverage Revenue and Expense: Knight Kap Guests Served Selling Price Beverage Cost $1.70 Beer 3,500 $8.50 $4.80 Wine 4,800 $16.00 $1.80 Spirits 2,500 $12.00 V. K. has made the following assumptions about next year: The cost he will pay for alcoholic beverages will increase by 2 percent in all of the beverage categories. V. K. will increase the selling price of all alcoholic beverages by 5 percent. The number of guests served will increase by 2 percent for beer, 2 percent for wine, and 3 percent for spirits. To help V. K. prepare next year's beverage revenue and expense budget, calculate the following for each of his three beverage types: 1. Forecasted total beverage revenue 2. Forecasted total beverage cost 3. Forecasted beverage cost percentage Projected Changes (Percentage Increases or Decreases) Guests Served Selling Price Beverage Cost Beer Wine Spirits Forecasted Beverage Revenue and Expense Forecast: Knight Kap Guests Served Beer Wine Spirits Total Chapter 10 Q8 Selling Price Beverage Cost Total Beverage Revenue Total Beverage Cost Chapter 10 Q8 Beverage Cost % Chapter 10, Question 9 The Wheatfield Valley golf course has been owned by the Miley family for two generations. Currently, it is managed by Cyrus Miley, a graduate of State University, where he majored in hospitality management. Last year was a good one for the golf course. Now Cyrus is preparing next year's operating budget. He has gathered a great deal of information to help him prepare the best budget possible. After carefully analyzing that information, Cyrus predicts that next year the course will experience: A 5 percent increase in food sales A 3 percent increase in beverage sales No change in food or beverage product cost percentage Salaries and wages (management and staff) that will increase 4.5 percent Employee benefits that will increase 10 percent An increase of 2.5 percent in each Other Expense category No change in Occupancy Costs and Depreciation & Amortization Interest payments of $1,000 per month Tax payments that are estimated to be 25 percent of income before income taxes Calculate this year's operating percentages for the food and beverage department on the budget worksheet provided, and then using his assumptions about next year, create Cyrus's new operating budget in dollars and percentages. The Wheatfield Valley Golf Course F&B Department Budget Worksheet for Next Year This Year Actual $ SALES Food Beverage Total Sales COST OF SALES Food Beverages Total Cost of Sales LABOR Management Staff Employee Benefits Total Labor PRIME COST OTHER CONTROLLABLE EXPENSES Direct Operating Expenses Utilities General & Administrative Expenses Repairs & Maintenance Total Other Controllable Expenses CONTROLLABLE INCOME Chapter 10 Q9 $173,250.00 56,750.00 230,000.00 $58,250.00 9,375.00 67,625.00 $9,000.00 27,250.00 6,200.00 42,450.00 110,075.00 $13,200.00 5,975.00 6,375.00 1,750.00 27,300.00 92,625.00 Next Year Budget $ % % % NON-CONTROLLABLE EXPENSES Occupancy Costs Equipment Leases Depreciation & Amortization Total Non-Controllable Expenses RESTAURANT OPERATING INCOME Interest Expense INCOME BEFORE INCOME TAXES Income Taxes NET INCOME Chapter 10 Q9 $15,000.00 0.00 4,200.00 10,800.00 81,825.00 $12,000.00 69,825.00 17,456.25 52,368.75 Chapter 10 Q9 Chapter 10 Q9 Chapter 10, Question 10 Menu analysis is typically associated with commercial foodservice operators who charge individual selling prices for their menu items. In many cases, however, noncommercial (nonprofit) foodservice operators receive a fixed amount of money per guest served regardless of the menu items selected by the guest. Those managers in charge of foodservice in a college residence hall's cafeteria, a senior living facility, and a military base are just three of many such examples. Despite the differences in how they charge for the items they serve, however, managers in both commercial and noncommercial operations are concerned about guest acceptance of the menu items they offer. What are some specific ways formal menu analysis (e.g., goal value analysis) can help noncommercial managers address this important issue? Answer: Chapter 10 Q 10