In this day in the life activity you will help Stacy create an operating budget for the upcoming year. Creating the operating budget for the upcoming year is no small task. Stacy has been busy preparing for changes based on her recent draft soda analysis and menu engineering. She wishes to incorporate these changes into her upcoming budget. Help stacy by: First, analyzing her past year's profit and loss (P&L) statement (question 1); Second, creating an initial forecast for next year (question 2): and Third, budgeting all revenues and expenses for the upcoming year while incorporating all the budget assumptions (questions 3-8). Driver Key 10 Beverage Revenue BR 71 Food Revenue FR 12 Merchandise Sales MS 13 Pre Taxes/Benefits Labor PTBL 14 Total Net Revenue TNR Question 1: Determine the fixed, variable, and mixed costs for each relevant revenue/expense line using the 2018 P&L below. Complete this question by filling in the yellow shaded boxes in columns M:N below. If the type (column @) is fixed, divide the total cost/revenue by the number of periods (13). If the type (column @) is variable, divide the item's total cost/revenue by the designated driver. If you are unsure what the driver Acronym refers to, use the table entitled driver key above. If the type (column Q) is mixed, use the trend line method to determine the fixed and variable costs. When inserting graphs to complete the trend line method, position your graphs in line 145 in the designated fields. If the type (column Q) is calculation or reference, no action is needed. Only fields that are yellow need to be filled out. (10 pts) 17 18 Period 1 Period 2 Period 3 Period 4 Period 5 Period 8 Period 7 Period 8 Period 9 Period 10 Period 11 Period 12 Period 13 Total 10 Period Start 1/1/2018 1/20/2018 2/28/2018 3/28/2018 4/23/2018 5/21/2018 6/18/2018 7/18/2018 8/13/2018 0/10/2018 10/8/2018 11/5/2018 12/3/2018 20 Period Finish 1/28/2018 2/25/2018 3/25/2018 4/22/2018 5/20/2018 6/17/2018 7/15/2018 8/12/2018 9/9/2018 10/7/2018 11/4/2018 12/2/2018 12/30/2018 21 Type Fixed Variable Driver 22 Revenue 23 Food Revenue 17709 17877 19821 24297 25104 32358 39495 38081 30273 22455 21159 19877 18540 327726 Variable 74.65% TNR 24 Beverage Revenue 8534 7376 7334 8038 8978 11840 13084 13838 10640 8604 8536 7702 8212 119686 Variable 27 26% TNR 2.84% TNR 25 Merchandise Sales 804 741 788 845 808 1286 1486 1383 1185 043 837 752 870 12488 Variable 26 Complimentary Meals -200 -302 -252 -376 410 -508 -574 -808 306 -270 -280 -220 -236 -5012 Variable -1.14% TNR 27 In-Store Promotions -538 -444 -908 -1084 -702 -1772 -2188 -2478 -1350 388- -1544 -802 -976 -15894 Variable -3.62% TNR 28 Total Net Revenue 24109 25248 28783 32810 33868 43114 51283 50876 40352 30824 28708 27009 24210 438904 Calculation 20 30 Cost of Sales 4243 4718 4809 8264 8386 7781 9804 8648 7096 5460 5183 4625 4271 79357 Variable FR 31 Meat and Seafood 32 Produce 976 1026 1014 1347 1409 1756 2381 2144 1642 1205 1141 1031 1015 18087 Variable FR 2788 2643 3383 4142 4070 3151 2641 2309 2213 2040 35478 Variable FR 33 Grocery 1884 2002 2142 439 438 509 648 615 772 1016 188 722 580 552 480 432 8174 Variable FR 34 Dairy 35 Employee Meal Credit -311 -284 289 285 -286 -300 -202 278 -285 -202 -283 -274 -275 -3875 Fixed 36 Total Food Cost 7231 7800 8305 10782 10747 13352 17051 15575 12345 9603 2888 8055 7483 137421 Calculation BR Beverage 1003 1164 1225 1458 1439 1828 2183 2161 1847 1322 1454 1127 19007 Variable 38 Merchandise 440 429 468 502 531 742 939 805 707 572 492 438 400 7485 Variable MS 30 Total Cost of Sales 8674 9493 9008 12742 12717 15022 20173 18541 14689 11497 10938 9620 8879 183893 Calculation 4040 41 Labor 3846 3846 3846 3846 3846 3846 3848 3846 3846 3846 49098 Fixed 42 Supervisor 3846 3846 3846 3310 Calculation 0 93 241 651 821 1039 421 44 43 Supervisor Profit Sharing 4290 3813 3208 3583 3483 3087 45825 Mixed TNR 3119 2048 3209 3437 3806 3850 4252 44 Cooks TNR 2188 2408 2282 2744 2744 2758 2484 2483 2103 2281 2265 31230 Mixed 45 Cashier 2255 2165 Calculation 9220 8059 9333 9601 9827 10481 11483 11715 11182 10048 9646 9570 9198 130383 46 Pre Taxes/Benefits Labor 286 202 283 274 275 3675 Reference 47 Employee Meals 311 284 280 285 286 300 202 278 1435 1380 19554 Variable PTBL Taxes/Benefits 1383 1344 1400 1454 1474 1572 1724 1757 1877 1507 1447 48 10853 153502 Calculation Total Labor 10914 10587 11002 11410 11587 12353 13509 13750 13125 11847 11376 11279 40 50 51 Direct Operating Expenses 30 21 15 12 28 23 9 8 242 Fixed 52 Laundry and Uniforms 20 28 18 21 9 80 89 73 1243 Variable TNR 53 Utensils and Smallwares 40 81 88 115 139 102 124 113 109 587 545 490 8870 Variable TNR 501 489 505 606 861 922 1043 1006 791 624 54 Paper Supplies 355 218 192 191 175 3098 Variable TNR 352 275 Janitorial Supplies/Services 182 183 061 226 230 320 561 480 428 415 377 8154 Mixed TNR 56 Utilities/Trash Removal 386 403 424 483 450 556 800 811 730 718 783 880 955 809 852 776 710 745 804 10089 Mixed TNR 57 Gas / Propane 862 715 183 287 128 196 192 67 171 191 87 2082 Fixed 58 Repairs/Maintenance 111 200 100 209 85 133 31 70 86 1329 Variable TNR Misc Operating Expenses 71 53 05 108 126 124 107 240 1973 2162 2150 2576 2561 3210 3333 3432 2858 2415 2222 2255 1970 33117 Calculation Total Direct Operating Expenses 61 62 Marketing & Administrative 980 861 805 650 10083 Fixed 63 500 919 523 877 615 756 1164 351 Advertising 122 145 1977 Variable TNF 64 Office Supplies 189 129 154 113 108 255 235 203 69 179 80 80 80 80 80 80 80 80 1040 Fixed 65 Telephone 80 80 80 80 80 210 180 250 90 250 340 210 340 250 190 190 2800 Fixed 66 Licenses/Dues 170 230 21 17 21 25 31 25 29 18 3 234 Fixed 67 Bank Charges 24 13 14 12 12 12 8 4 13 5 114 Fixed 68 Cash Variances 13 18 15 276 Fixed Returned Checks/Credit Cards 29 35 20 20 20 14 25 27 15 20 17 18624 Calculation Total Marketing & Administrative 976 1371 949 1308 950 1241 1893 930 1855 1279 1450 1444 1088 70 71 72 Non-Controllable Expenses 1000 1000 1000 1000 1000 1000 1000 1000 13000 Fixed 1000 1000 73 Rent 1000 1000 1000 30 30 30 30 30 30 30 30 30 30 30 30 390 Fixed 74 Property Tax 30 Fixed 300 300 300 300 300 300 300 300 300 300 300 300 300 3900 75 Insurance (Truck/Property/General) 088 088 088 088 088 088 088 088 088 12870 Fixed 76 Depreciation 088 088 088 088 TNR 583 875 709 820 911 1140 1426 1086 1088 809 754 715 575 1117 Variable 77 Credit Card Fees 415 415 415 415 415 5305 Fixed 78 Interest 415 415 415 415 415 415 415 415 3534 3555 3646 3875 4161 3831 3803 3344 3489 3450 3310 48726 Calculation Total Non-Controllable Expenses 3318 3410 80 -1746 -1775 850 628 2407 8513 8214 10302 4212 442 -787 -1039 -1890 25042 Calculation 81 Net Operating Income Before Taxes 143