BUDGET ACCOUNTING ASSIGNMENT

You will prepare various budgets, conduct a variance analysis and write a memo to the CEO of Crown Hotel regarding ethical practices during budgeting.

Calibri 11 BIU A 7 A D E K M F G H Instructions for students 2 3 4 5 6 7 You will prepare various budgets, conduct a variance analysis and write a memo to the CEO of Crown Hotel regarding ethical practices during budgeting. This relates to content taught in OOO 10 11 13 1. Your input is required for the following spreadsheets whose tabs are highlighted in yellow: > Static Budget > Actuals > Flexible Budget > Var Analysis - Static and Flex 12 > Sales variances > Assumptions 14 15 2. Within each of the above spreadsheets, your input is required for cells highlighted in grey: You calculate ? Marked values, as those values will help in preparing static, Actual and flexible budget tabs 17 3. The input data is availalble in the spreadsheet,'Input Database'. This spreadsheet is locked and you will not be able to make any changes to its values. You are required to link data from this spreadsheet and complete the inputs in the spreadsheets above ( whose tabs are highlighted in yellow). 20 4. Ensure that there are no plugged values in cells, use firmual to get figures 5. IF statements are used where appropriate. 22 6. Upload your completed spreadsheet to eConestoga budget drop box by 10th Dec 16 18 19 21 23 24 8. You will also write a memo regarding ethical conduct during budget preparation and how building in "slack' represents unethical behaviour. 25 Grading: 26 This is an individual assignment worth 13% of your final grade. 27 Related textbook content: 28 The content you are being assessed on, appears in Chapter 7 and Chapter 11 of the textbook. This content is covered in class during weeks 9,10 and 11. Cover page Read this first Input Static Budget Actuals Flexible budget Var Analysis - Static and Flex Sales variances Rubric Calibri Y 11 - B I U A ++ x . A B E F F G H H 1 J D Crown Hote udget for Quarter ending on Septem! L M M N o P town Hote Actuals forQuarter ending on September 30, 2020 Sales Rooms: Days Available Rooms available # Occupancy Rate (%) ) Parameters - Budget July Aug 31 31 115 115 100% 95% Sep 30 115 85% Sales Rooms: Days Available Rooms available # Occupancy Rate (%) Parameters - Actual July Aug 31 31 115 115 100% 95% Sep 30 115 80% % Sales Restaurant: Days Available Seats Available Turnover Rate 31 80 1.20 31 80 1.10 30 80 0.90 Sales Restaurant: : Days Available Seats Available Turnover Rate 31 80 1.00 31 80 1.05 30 80 0.85 1 2 3 1 5 5 - 3 Selling price ( $/hr): Average Daily Rate (ADR) $ - Rooms Average Check $ - Restaurant 115 15 115 15 115 15 Selling price ( $/hr): Average Daily Rate (ADR) $ - Rooms (S- Average Check $ - Restaurant 120 17 120 17 120 17 Housekeeping: Hourly Wage rae ( $/hr): 1 housekeeper /occupancy Hours per shift 15 20 8 15 20 8 Housekeeping: Hourly Wage rae ( $/hr): 1 housekeeper /occupancy / Hours per shift 15 20 8 16 20 8 1 2 3 16 20 8 15 20 co 5 5 - 3 2 Restaurant Servers: 1 Server per guests Hourly wage rate ($/hr) Hours per shift variable OH expenses -$/room 20 14 8 10 20 14 8 8 10 20 14 8 10 Restaurant Servers: 1 Server per guests Hourly wage rate ($/hr) Hours per shift variable OH expenses -$/room 20 14 8 10 20 13 8 10 20 13 8 10 Fixed overhead: Fixed overhead: XOAN Innan LA Inn LANAN Loco over page Read this first Input Static Budget Actuals Flexible budget Var Analysis - Static and Flex Sales variances Rubric E, H 1 J L M N N 0 K Fixed overhead: Manager & supervisor salaries Receptionist & Front office staff *Marketing & Promotional expenses *Depreciation on fixed assets Property taxes and insurance expenses Office supplies 60000 9500 4000 4000 8000 2000 60000 60000 10000 10000 4500 6000 4000 4500 8000 8000 2000 2000 *Increase in marketing expense in Sept expense increased as hotel decided from Silver to Platimum package in Marketing Show Calibri 11 B I USA H: + f* B B D E F G 30 Fixed overhead: 31 Manager & supervisor salaries 60000 60000 60000 32 Receptionist & Front office staff 10000 10000 10000 33 * Marketing & Promotional expenses 4000 4000 4500 34 *Depreciation on fixed assets 4000 4000 4500 35 Property taxes and insurance expens 8000 8000 8000 36 Office supplies 2500 2500 2500 37 38 39 *Increase in marketing expense in Sept because Hotel taking part in Hospitality Marketing Show 40 *Depreciation increased in Sept due to purchase of additional asset 41 42 For Food purchase budget 43 1 Cost of sales amounts assumed to be 30% of sales from the restaurant budgetted sales 44 2 Beginning inventory is $3,500 and Forecasted food sales for October is $55,000 45 3 Hotel Crown's standard is 20% of next month's requirements as a desired ending inventory. 46 47 Cash Collection Budget 48 1 Accounts receivable, beginning balance on July is 15000 49 2 60% of Crown's Restaurant sales charged to customers' room to be paid when they check out 50 3 40% of restaurant sales paid immediately by credit card 51 4 70% of room revenue will be paid by credit card at the time of check out 52 5 30% of room revenue comes from corporate customers, hotel invoice them to pay later (credit sales) 53 of Credit sales, 50% will be collected in same month in which sales is made 54 of Credit sales, 30% will be collected one month after sales were made 55 of Credit sales, 20% will be collected 2 months after sales were made 56 57 Cover page Read this first Input Static Budget Actuals Flexible budget Var Analysis - Static and Flex Sales variances Rubric a Calibri 11 B I U JIT f* B E F G G H J K K L M 1 D Crown Hotel perating Budget for Quarter ending on September 30, 202 2 3 4 5 6 7 8 July Quarters Aug Sep Q3 Total Marks N00E Sales budget Number of rooms sold 9 Number of meals/guests served 10 Sales Rooms $ 11 Sales Restaurant $ 12 Total sales 13 14 # of required staff for rooms 15 # of required staff for restaurant 16 Total housekeeping cost 17 Total restaurant servers cost 18 variable OH expenses 19 Total variable costs 20 21 Fixed Overhead: 22 Manager & supervisor salaries 23 Receptionist & Front office staff 24 Marketing & Promotional expenses 25 Depreciation on fixed assets 26 Property taxes and insurance expenses 27 Office Supplies Total Fixed Cost 2 28 Cover page Read this first Input Static Budget Actuals Flexible budget Var Analysis - Static and Flex Sales variances Rubric 5 2 Calibri 11 Y BIU A IT f* A B D E F G I 1 J K L M N Crown Hotel Operating Budget for Quarter ending on September 30, 2021 2 July Quarters Aug Sep Q3 Total Marks HNMOOS 11 14 4 Sales budget Number of rooms sold Number of meals/guests served 10 Sales Rooms $ Sales Restaurant $ 12 Total sales 13 # of required staff for rooms 15 # of required staff for restaurant 16 Housekeeping Cost 17 Restaurant Servers 18 variable OH expenses 19 Total variable costs 20 21 Fixed Overhead: 22 Manager & supervisor salaries 23 Receptionist & Front office staff 24 Marketing & Promotional expenses 25 Depreciation on fixed assets 26 Property taxes and insurance expenses 27 28 Total Fixed Cost 2 Cover page Read this first Input Static Budget Actuals Flexible budget Var Analysis - Static and Flex Sales variances Rubric 3 4 - 1 E 1 = f* A G G H - J K L M B D E Crown Hotel perating Budget for Quarter ending on September 30, 202 1 2 3 4 Quarters Aug 5 July Sep Q3 Total Marks 6 7 8 12 Sales budget Number of rooms sold 9 Number of meals/guests served 10 Sales Rooms $ 11 Sales Restaurant $ Total sales 13 14 # of required staff for rooms 15 # of required staff for restaurant 16 Housekeeping Cost 17 Restaurant Servers 18 variable OH expenses 19 Total variable costs 20 21 Fixed Overhead: 22 Manager & supervisor salaries 23 Receptionist & Front office staff 24 Marketing & Promotional expenses 25 Depreciation on fixed assets 26 Property taxes and insurance expenses 27 Office Supplies 28 Total Fixed Cost N 2 Cover page Read this first Input Static Budget Actuals Flexible budget Var Analysis - Static and Flex Sales variances Rubric Calibri Y 11 B 1 U ; f A B E G H 1 J K L 1 D F Crown Hotel Operating Budget for Quarter ending on September 30, 2020 AWN 3 Marks Static Budget Variance Actual for the year Static for the yea Variance UZE Flexible Budget Variance Actual for the year Flexible for the year Variance U/E 2 2 1 1 11 2 2 1 6 Sales budget 7 Sales Rooms 8 Sales Res 9 Total sales 10 Housekeeping Cost 12 Restaurant Servers 13 variable OH expenses 14 Total variable costs 15 16 Fixed Overhead: 17 Manager & supervisor salaries 18 Receptionist & Front office staff 19 Marketing & Promotional expenses 20 Depreciation on fixed assets 21 Property taxes and insurance expenses 22 Office Supplies 23 Total Fixed Cost 24 25 26 27 28 1 1 1 1 1 1 Total 16 Cover page Read this first Input Static Budget Actuals Flexible budget Var Analysis - Static and Flex Sales variances Rubric @a Calibri 11 BIU A f* A B B D D E 1 J J L L M F F G Operating Budget for Quarter ending on September 30, 2020 Sales variance - Rooms Marks 2 3 4 5 6 7 8 Actual Quantity -AQ Actual Rate -AR Actual Quantity -AQ Standard Rate -SR Standard Quantity - SQ Standard Rate - SR 4 9 Price Variance -0 Quantity Variance 2 10 11 Total Variance 1 1 1 12 13 14 15 Explanation for Quantity variance: 16 17 18 Explanation for price variance: 19 20 21 22 23 24 25 Actual Quantity -AQ 26 Actual Rate -AR 27 28 29 Price Variance Sales Variance - Restaurant Actual Quantity -AQ Standard Rate -SR Standard Quantity - SQ Standard Rate - SR - Quantity Variance 2 Cover page Read this first Input Static Budget Actuals Flexible budget Var Analysis - Static and Flex Sales variances Rubric a Calibri 11 B I U A' E B D D E F G H 1 J K L M P Sales Variance - Restaurant f* A 21 22 23 24 25 Actual Quantity -AQ 26 Actual Rate -AR 27 28 29 30 Actual Quantity -AQ Standard Rate -SR Standard Quantity - SQ Standard Rate - SR 4 Price Variance Quantity Variance 2 31 Total Variance 1 32 33 34 35 1 36 Explanation for Quantity variance: 37 38 39 Explanation for price variance: 40 41 42 43 44 45 AC 46 47 48 49 Total 18 Cover page Read this first Input Static Budget Actuals Flexible budget Var Analysis - Static and Flex Sales variances Rubric