How do you make a flexible budget with the given information and how do you calculate variances between the actual and flexible budget?
Table 1: Local and Regional Competitor Sales Data Local Competitors Metropolitan Area Rooms ADR Occupancy Rooms ADR Occupancy Year Rented USS (6) Rentals USS (6) 2012 96,099 60.77 58.72 181,361 47.53 56.11 2013 104,013 64.70 58. 16 1 96,033 54.78 59.15 2014 100,651 65.86 55.86 215,797 59.04 62.80 2015 94,268 67.67 51.55 218,162 62.73 59.35 2016 79,391 62.40 43.41 192,519 55.73 51.81 Note: ADR is average daily rate and is measured as total revenue divided by the number of rooms rented (ie, sales price ). Occupancy is measured as rooms rented divided by the total number of rooms available x 100 (ie, capacity utilization). Table 2: 2017 Profit-and-Loss Summary for the Amarillo Property Panel A: Summary Statistics and Revenues Budget Actual Rooms Available 45.990 45.990 Rooms Rented 27,376 25,435 Occupancy 59.5% 55.3% Average Daily Rate (ADR) 43.79 44.65 Revenue per Available Room (RevPAR) 26.07 24.69 REVENUES Room Rentals $ 1,200,138 $ 1,136,619 Revenue Allowance (1280) LLOGO Total Room Revenue $ 1,198,858 $ 1,135,559 Internet Access 4.900 5,534 Vouchers 5,000 1,262 Laundry 1,310 1,240 Vending 3.900 3,708 Total Other 15,1 10 $ 11,744 TOTAL REVENUE $ 1,213,968 $ 1,147,303 Note: All monetary values are represented in U.S. dollars.Table 2: 2017 Profit-and-Loss Summary for the Amarillo Property Panel B: Expenses Budget Actual EXPENSES General Manager 28,800 27.434 Front Desk Staff 72.000 82.089 Total Front Office S 100,800 S 109.523 Head Housekeeper 19.000 S 22.226 Housekeepers and Laundry 81.816 82.556 Total Housekeeping S 100,816 S 104,782 Maintenance Wages 20,700 S 23,577 Regional Maintenance Support 3,180 6.452 Incentive Pay (Staff) 0 0 Bonus Expense, General Manager 2.520 1.346 Payroll Tax Expense 19.381 21.339 Total Other-Personnel Payroll 45,781 S 52,714 Gross Payroll S 247,397 S 267,019 Vacation Expense S 5,700 3.494 Holiday Expense 1.620 1,690 Staff Incentives 640 639 Employee Relocation Expense 0 1,506 Employee Training 2.260 576 Workers Compensation 1 8,000 17,588 Health Insurance 28,800 20,005 Retirement Plan Contribution Expense 720 841 Group Life Insurance 672 613 Total Benefits Expense S 58.412 S 46.952 Total Personnel Costs S 305,809 S 313,971 Supplies, Cleaning Chemicals 6.083 Supplies, Linens 13,916 Supplies, Other 18.064 Total Supplies S 26.007 S 38,063 Memberships and Subscriptions 300 0 Travel 3.360 4,141 Meals 840 921 Total Travel and Meals S 4.500 S 5.062 Note: All monetary values are represented in U.S. dollars.Table 2: 2017 Profit-and-Loss Summary for the Amarillo Property Panel B: Expenses (continued) Budget Actual Bad Debt Expense S S 350 Cash Shortage and Excess 1 13 Bank Deposit Shortage and Excess 27 Credit Card Chargeback 619 Credit Card Fees 14.568 14.376 Guest Vouchers 1.200 540 Refunded Rentals 1,000 1.887 Total Other Expense 16.768 S 17,912 Other External Services 600 722 Total Miscellaneous Operating Expenses 21.868 23.696 Property Marketing 6.000 S 4.807 Building 1.800 $ 1.913 Landscaping 9.900 10.524 Contracts 8.100 8.460 Laundry 6.000 2.713 Rooms (floors, doors, windows) 2,400 5.610 HVAC 4.800 3.219 Pool 1.600 2.447 Electrical and Fire 4.320 5.852 Plumbing 3.300 5.243 Supplies 3.600 4.160 Unclassified (including petty cash) 900 2.411 Total R&M 46.720 S 52,552 Electricity 42.000 36.144 Natural Gas 16.800 13.428 Water 33.000 21.919 Total Energy S 91.800 S 71.491 Garbage Collection 3.720 $ 3.494 Sewer 18.900 16,160 Salt (Water Softener) 2.100 1.740 Telephone 12,000 10.524 Satellite Television Fees 15.000 13.413 Utility Fees 900 802 Total Other Utilities 52.620 46.133 TOTAL EXPENSES 550.824 550,713 PROFIT 663.144 596.590 Note: All monetary values are represented in U.S. dollars.Table 3: Employee Information by Job Type Hours Hourly Employee ID Worked Wage Rate General Manager 44215 2,040.0 $13.45 Front Desk Employees 251256 2,077.1 $8.98 488442 (front desk supervisor) 2,087.5 $9.72 743796 1,610.1 $8.45 766468 1.520.0 $8.55 871219 1,425.1 $8.60 98051 1 517.1 $8.30 Head Housekeeper 134076 2.291.3 $9.70 Housekeepers 172931 1,462.2 $8.15 215403 1,627.8 $8.10 219100 1.249.2 $8.10 264365 1,117.2 $7.60 306002 1,188.0 $7.65 322777 1,151.5 $7.55 352605 423.6 $7.40 586120 43.7 $7.30 693453 245.7 $7.35 696487 155.5 $7.25 724731 73.4 $7.25 729577 511.1 $7.60 748933 111.6 $7.25 772040 121.4 $7.30 805364 282.4 $7.30 828589 125.4 $7.25 863038 63.6 $7.25 882112 21.9 $7.25 885651 235.4 $7.30 901105 124.2 $7.25 911934 320.0 $7.35 Maintenance Staff 289183 1,966.3 $11.99 166365 (regional support) 367.4 $17.56 Note: Due to the rounding, the total cost is different from income statement amounts. All monetary values are represented in U.S. dollars.Table 4: Jack Smith's Monthly Budget Worksheet Item Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Rooms Available 3.906 3.528 3.906 3.780 3.906 3.780 3.906 3,906 3,780 3.906 3.780 3.906 Rooms Rented 45.990 2.247 2,100 2.210 2.484 2,195 2.390 2.297 2.730 2.303 1.890 2.239 2.291 27.376 Occupancy 57.5% 59.5% 56.6% 65.7% 56.2% 63.2% 58.8% 69.9% 60.9%% 48.4% ADR $42.00 $40.00 59.2% $40.00 $44.00 58.7% 59.5% $42.00 $48.00 $48.00 $50.00 $46.00 RevPAR $44.00 $24.14 $38.00 $23.79 $22.61 $42.00 $28.89 $23.58 $43.79 $30.31 $28.19 $34.91 $27.99 $21.26 $22.49 $24.62 $26.07 Revenues Room Rentals $94.374 $84,000 $88,400 $109.296 $92,190 $114,720 $1 10.256 $136,500 $105,938 $83,160 Revenue Allowance $85,082 $96,222 $1,200.138 80 80 80 100 100 140 160 160 Total Room Revenue $94,294 $83.920 130 100 $88320 $109,196 80 70 1.280 $92,090 $114,580 $110,096 $136,340 $105,808 $83.060 $85,002 $96,152 $1,198,858 Internet Access $402 $376 $396 $445 $393 $428 Vouchers $411 $489 $412 $338 $401 $410 410 384 $4.900 404 454 401 437 420 499 421 345 409 418 Laundry 5.000 50 50 50 80 80 200 200 200 Vending 200 100 50 150 50 150 150 1,310 240 300 600 600 600 500 300 160 150 Total Other $1.013 3.900 $959 $999 $1.218 $1.174 $1.664 $1.631 $1,787 $1.533 $1.083 $1.020 $1.028 $15,110 Total Revenue $95.307 $84.879 $89319 $110.414 $93.264 $116.244 $111,727 $138,127 $107,341 $84,143 $86,022 $97,180 $1,213.968Table 4: Jack Smith's Monthly Budget Worksheet (continued) Item Jan Feb Mar May Jul Aug Sep Oct Nov Dec Total Expenses General Manager $2.400 $2.400 $2.400 $2.400 $2.400 Front Desk Staff $2.400 $2.400 $2.400 $2.400 6.000 6.000 $2.400 6.000 $2.400 6.000 Total Front Office 6.000 $2.400 6.000 $28,800 $8.400 6.000 $8,400 6.000 6.000 $8,400 $8.400 $8.400 6.000 6.000 $8.400 $8,400 6.000 $8,400 72.000 $8.400 $8,400 $8,400 $8,400 $100,800 Head Housekeeper $1.583 $1.583 $1,584 $1.583 Housekeepers $1.583 $1.584 6.698 6.183 $1.583 $1.583 $1.584 6.568 $1.583 $1,583 $1.584 $19,000 Total Housekeeping $7.865 7.527 6.516 $7.350 7.198 $7.735 6.873 8.388 6.894 $8.694 $7.683 5.448 6.670 $8.040 6.853 $9.555 $8,061 81.816 $6.615 $7.837 $8.019 $100,816 Maintenance Wages $1.725 $1.725 $1,725 $1.725 $1.725 Regional Maintenance $1.725 $1.725 265 $1.725 265 265 $1.725 265 $1.725 $1,725 $1.725 265 Incentive Pay (staff) 265 $20.700 265 265 265 265 265 3,180 Bonus Expense, GM 210 210 210 210 Payroll Taxes 210 210 210 1.605 210 1.594 210 210 2.520 Total Other Personnel $3.805 1.561 $3.761 $3.794 1.589 $3.875 1.647 $3.789 $3.847 1.749 1.622 1.499 $3.820 $3,949 1.603 1.618 19.381 $3.699 $3.803 $3.818 $45.781 Gross Payroll $20.486 $20.346 $21,385 $20.288 $21,029 $20.676 $22,320 $20.700 $19.130 $20,456 $20.655 $247.397 Vacation Expense $475 $475 Holiday Expense 135 $475 135 $475 135 $475 $475 $475 $475 135 135 $475 Staff Incentives 135 $475 $5.700 35 35 135 35 135 35 135 Employee Relocation 90 90 1,620 90 90 35 35 35 35 640 Employee Training 720 140 140 Workers Compensation 140 140 1,500 140 140 1,500 1,500 140 1,500 140 1,500 140 Health Insurance 2,400 1,500 1,500 2,400 1,500 2.260 2.400 1,500 1,500 Retirement Plan Contr. 2,400 2.400 2.400 2.400 1,500 1,500 2.400 18,000 60 60 2.400 2.400 2,400 60 60 28,800 Group Life Insurance 60 60 60 56 60 56 Total Benefits Expense $5.381 56 56 720 $4.801 56 56 $4.801 $4.801 56 56 56 $4.856 56 $4.856 56 $4.801 $4.801 672 $58.412 Total Personnel Costs $25,867 $24.728 $25,147 $26,186 $25.144 $25,885 $25.532 $27.176 $25.501 $23,931 $25.257 $25.456 $305.809 Total Supplies $2.135 $1.995 $2.100 $2.360 $2.085 $2.271 $2,182 $2.594 $2.188 $1,796 $2,127 $2.176 $26.007Table 4: Jack Smith's Monthly Budget Worksheet (continued) Item Jan Feb Mar Apr May Jul Sep Oct Nov Expenses (continued) Membership & Subs. SO SO Travel SO SO SO 280 280 SO SO 280 280 SO SO Meals 280 $300 70 280 $300 70 280 280 70 280 280 3,360 Total Travel & Meals 70 70 $350 70 $350 70 $350 70 $350 70 $350 70 $350 70 $350 70 840 $350 $350 $350 $650 $4,500 Bad Debt Expense Cash Shortage/Excess SO SO SO Bank Deposit Short/Exc. Credit Card Chargeback Credit Cards (40% cc) 1,144 1,072 1,119 Guest Vouchers 1.341 1,658 1.288 98 1.010 97 1.032 109 1,166 14.568 Refunded Rentals 96 101 82 18 120 101 83 98 Total Other Expense 80 87 84 1,200 $1,187 $1,525 100 84 69 $1.249 $1,296 $1,587 82 84 1.000 $1,877 $1,162 $16,768 Other External Services $50 $50 Total Mise. Operating $1,724 $50 $50 $1,925 $50 $1,696 $1,987 $50 $2,277 $600 $1,873 $1,562 $1,612 $2,050 $21,868 Property Marketing $500 $500 $500 $500 $500 $500 $500 $500 $500 $500 $500 $500 $6,000 Building $150 $150 $150 $150 Landscaping $150 $150 825 $150 825 825 $150 825 $150 $150 $150 Contracts 825 825 825 825 675 675 825 825 Laundry 675 675 675 9.900 500 675 500 675 675 500 500 675 500 675 500 8,100 200 500 500 200 500 200 500 200 500 200 6,000 HVAC 400 400 200 200 200 400 400 400 200 200 400 200 400 400 400 2,400 400 400 400 300 4,800 Electrical & Fire 360 360 600 360 600 100 360 1,600 Plumbing 275 360 275 360 275 275 360 360 275 360 360 Supplies 275 275 300 300 275 300 275 4320 300 275 275 275 Unclassified (petty cash) 300 3.300 75 300 300 75 300 75 300 75 75 3.600 Total R&M $3,760 75 $3,760 $3,760 $3,760 75 75 75 $4.060 $3.760 75 $4.360 75 $4.360 $3.860 $3,760 $3,760 $3,760 $46,720Table 4: Jack Smith's Monthly Budget Worksheet (continued) Item Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dee Total Expenses (continued) Electricity $3.500 $3.500 Natural Gas $3.500 $3.500 1.400 $3.500 $3.500 1,400 $3.500 1,400 $3.500 1.400 $3.500 1.400 $3.500 1.400 1.400 $3.500 1.400 $3.500 $42,000 Water 2.750 2.750 2.750 2.750 1,400 1.400 2.750 1.400 Total Energy 2.750 1.400 $7.650 $7.650 $7.650 2.750 2.750 16.800 $7.650 2.750 2.750 $7.650 $7.650 $7.650 2.750 2.750 33.000 $7.650 $7.650 $7.650 $7.650 $7.650 $91,800 Garbage Collection $310 $310 $310 $310 $310 Sewer 1,575 $310 1.575 $310 $310 $310 1,575 $310 $310 Salt (water softener) 1.575 1.575 $310 1,575 $3.720 0 350 1.575 1.575 0 350 1.575 0 350 1.575 1,575 1.575 18.900 Telephone 0 1,000 350 0 1,000 1.000 350 0 1,000 1.000 350 2.100 Satellite TV Fees 1.250 1.000 1.250 1.250 1.000 1,000 1,000 1,000 Utility Fees 1.250 1.250 1,000 1.250 1.250 1.250 1,000 75 1.250 12,000 75 75 1.250 1.250 1.250 15.000 Total Other Utilities 75 $4.210 75 75 $4.560 75 $4210 75 75 $4.560 $4.210 75 75 900 $4.560 $4.210 75 $4.560 $4.210 $4.560 $4,210 $4.560 $52,620 TOTAL EXPENSES $45.846 $44.780 $45.016 $46.941 $45.345 $46.613 $46.359 $49.116 $45.782 $43.758 $45.116 $46.153 $550.824 PROFIT $49.461 $40.099 $44.303 $63.474 $47.919 $69.631 $65.367 $89.011 $61.559 $40.386 $40.906 $51.028 $663.144 Note: All monetary values are represented in U.S. dollars
