Create a flexible budget using the given information
Table 1: Local and Regional Competitor Sales Data Local Competitors Metropolitan Area Rooms ADR Occupancy Rooms ADR Occupancy Year Rented (USS) (%) Rentals (USS) (%) 2012 96,099 60.77 58.72 181,361 47.53 56.11 2013 104,013 64.70 58.16 196,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 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) 62.73 Table 2: 2017 Profit-and-Loss Summary for the Amarillo Property Panel A: Summary Statistics and Revenues Budget Actual Rooms Available Rooms Rented 45,990 27,376 45,990 25,435 Occupancy Average Daily Rate (ADR) Revenue per Available Room (RevPAR) $ $ 59.5% 43.79 26.07 $ $ 55.3% 44.65 24.69 REVENUES Room Rentals Revenue Allowance Total Room Revenue $ 1,200,138 1.280) $ 1,198,858 $ 1,136,619 01.060 $ 1,135.559 $ $ Intemet Access Vouchers Laundry Vending Total Other 4,900 5,000 1,310 3,900 15.110 5,534 1,262 1,240 3,708 11.744 $ $ TOTAL REVENUE $ 1.213.968 $ 1,147,303 Note: All monetary values are represented in US dollars. Table 2: 2017 Profit-and-Loss Summary for the Amarillo Property Actual S $ 27,434 82,089 $ 109,523 S S $ 22.226 82.556 104,782 S S 23,577 6,452 0 1,346 21,339 $ 52,714 S $ 267,019 S $ Panel B: Expenses Budget EXPENSES General Manager 28,800 Front Desk Staff 72.000 Total Front Office 100,800 Head Housekeeper 19,000 Housekeepers and Laundry 81.816 Total Housekeeping S 100,816 Maintenance Wages 20,700 Regional Maintenance Support 3,180 Incentive Pay (Staff) 0 Bonus Expense, General Manager 2,520 Payroll Tax Expense 19,381 Total Other-Personnel Payroll $ 45,781 Gross Payroll 247,397 Vacation Expense 5,700 Holiday Expense 1,620 Staff Incentives 640 Employee Relocation Expense 0 Employee Training 2,260 Workers Compensation 18,000 Health Insurance 28,800 Retirement Plan Contribution Expense 720 Group Life Insurance 672 Total Benefits Expense 58.412 Total Personnel Costs $ 305,809 Supplies, Cleaning Chemicals Supplies, Linens Supplies Other Total Supplies 26,007 Memberships and Subscriptions 300 Travel 3,360 Meals 840 Total Travel and Meals S 4.500 Note: All monetary values are represented in US dollars. 3,494 1,690 639 1,506 576 17,588 20,005 841 613 46.952 $ 313,971 $ 6,083 13,916 18.064 38,063 S $ S S 0 4,141 921 5,062 S Table 2: 2017 Profit-and-Loss Summary for the Amarillo Property $ 0 Actual 350 113 27 619 14,376 540 1.882 17,912 $ $ S 722 23,696 S 4,807 $ Panel B: Expenses (continued) Budget Bad Debt Expense Cash Shortage and Excess Bank Deposit Shortage and Excess Credit Card Chargeback Credit Card Fees 14,568 Guest Vouchers 1,200 Refunded Rentals 1.000 Total Other Expense S 16,768 Other External Services $ 600 Total Miscellaneous Operating Expenses 21,868 Property Marketing S 6,000 Building 1,800 Landscaping 9,900 Contracts 8,100 Laundry 6,000 Rooms (floors, doors, windows) 2,400 HVAC 4,800 Pool 1,600 Electrical and Fire 4,320 Plumbing 3,300 Supplies 3,600 Unclassified (including petty cash) 900 Total R&M S 46,720 Electricity S 42,000 Natural Gas 16,800 Water 33.000 Total Energy S 91,800 Garbage Collection $ 3,720 Sewer 18,900 Salt (Water Softener) 2,100 Telephone 12,000 Satellite Television Fees 15,000 Utility Fees 900 Total Other Utilities 52.620 TOTAL EXPENSES 550.824 PROFIT 663,144 Note: All monetary values are represented in US dollars. 1,913 10,524 8,460 2,713 5,610 3,219 2,447 5,852 5,243 4,160 2.411 52,552 $ 36,144 13,428 21.919 71,491 $ $ 3,494 16,160 1,740 10,524 13,413 802 46.133 550,713 596,590 S S S S Table 3: Employee Information by Job Type Hourly Hours 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 980511 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 45.990 Rooms Rented 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% 59.2% 58.7% 59.5% ADR $42.00 $40.00 $40.00 $44.00 $42.00 $48.00 $48.00 $50.00 $46.00 $44.00 $38.00 $42.00 $43.79 RevPAR $24.14 $23.79 $22.61 $28.89 $23.58 $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 $110,256 $136,500 $105,938 $83,160 $85,082 $96.222 $1,200,138 Revenue Allowance 80 80 80 100 100 140 160 160 130 100 80 70 1280 Total Room Revenue $94.294 $83.920 $88,320 $109,196 $92,090 $114,580 $110,096 $136,340 $105,808 $83,060 $85,002 $96,152 $1,198,858 Internet Access S402 $376 $396 $445 $393 $428 411 $489 $412 $338 S401 $410 $4,900 Vouchers 410 384 404 454 401 437 420 499 421 345 409 418 5.000 Laundry 50 50 50 80 80 200 200 200 200 100 50 50 1,310 Vending 150 150 150 240 300 600 600 600 500 300 150 3.900 Total Other $1,013 $959 $999 $1,218 $1.174 $1,664 $1.631 $1,787 $1,533 $1,083 $1,020 $1.028 $15.110 160 Total Revenue $95,307 $84,879 $89.319 $110.414 $93.264 $116,244 $111.727 $138,127 $107,341 $84.143 $86,022 $97.180 $1,213,968 TSIL Table 4: Jack Smith's Monthly Budget Worksheet (continued) 210 210 Item Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Expenses General Manager $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $28,800 Front Desk Staff 6,000 6,000 6,000 6,000 6,000 6.000 6.000 6,000 6.000 6,000 6,000 6.000 Total Front Office 72.000 $8,400 $8,400 $8.400 $8,400 $8,400 $8,400 $8,400 $8.400 $8,400 $8,400 $8.400 $8,400 $100,800 Head Housekeeper $1,583 $1,583 $1,584 $1.583 $1,583 $1,584 $1,583 $1,583 $1,584 $1,583 $1,583 $1,584 Housekeepers $19,000 6.698 6.183 6.568 7.527 6.516 22198 6.873 8388 6.894 5.448 6.670 6.853 Total Housekeeping $7,865 81.816 $7,350 $7,735 $8,694 $7,683 $8,365 $8,040 $9,555 $8,061 $6,615 $7,837 $8,019 $100,816 Maintenance Wages $1.725 $1,725 $1.725 $1,725 $1,725 $1.725 $1,725 $1,725 $1,725 $1,725 $1,725 $1,725 $20,700 Regional Maintenance 265 265 265 265 265 265 265 265 265 265 265 265 3.180 Incentive Pay (staff) 0 0 0 0 0 0 0 0 0 0 0 0 0 Bonus Expense, GM 210 210 210 210 210 210 210 210 210 210 2,520 Payroll Taxes 1.605 1.561 1.594 1675 1.589 1647 1.620 1.749 1.622 1.499 1603 1.618 Total Other Personnel $3,805 19.381 $3.761 $3.794 $3.875 $3.789 $3.847 $3.820 $3.949 $3.822 $3.699 $3.803 $3.818 $45,781 Gross Payroll $20,486 $19,927 $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 S475 S475 $475 S475 S475 $475 $475 $475 $475 $475 $5,700 Holiday Expense 135 135 135 135 135 135 135 135 135 135 135 135 1.620 Staff Incentives 35 35 35 90 90 90 35 35 35 640 Employee Relocation 0 0 0 0 0 0 0 0 0 0 0 0 0 Employee Training 720 140 140 140 140 140 140 140 140 140 140 140 Workers Compensation 1,500 2,260 1,500 1,500 1,500 1,500 1,500 1.500 1.500 1.500 1,500 1,500 1.500 Health Insurance 18,000 2,400 2.400 2,400 2.400 2,400 2.400 2,400 2,400 2.400 2.400 2,400 2.400 Retirement Plan Contr. 60 28.800 60 60 60 60 60 60 60 60 60 60 720 Group Life Insurance 56 56 56 56 56 56 56 56 56 56 56 672 Total Benefits Expense $5,381 $4,801 $4801 $4,801 S4,856 $4.856 $4,856 S4856 $4,801 $4,801 $4801 $4,801 $58,412 Total Personnel Costs $25,867 $24,728 $25.147 $26,186 $25,144 S25,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,007 35 90 35 Table 4: Jack Smith's Monthly Budget Worksheet (continued) Item Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Expenses (continued) Membership & Subs. Travel Meals Total Travel & Meals SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 $300 280 70 $650 $300 3,360 840 $4,500 SO 0 0 0 1,019 92 77 S1.187 SO 0 0 0 1,072 97 81 SI 249 SO 0 0 0 1.325 109 91 $1,525 SO 0 0 0 1,119 96 80 $1.296 SO 0 0 0 1.395 105 87 $1,587 SO 0 0 0 1,341 101 84 $1.525 SO 0 0 0 1.658 120 100 $1,877 SO SO 0 0 0 0 0 0 1.288 1,010 101 83 84 69 $1,473 $1,162 $50 $50 $1,873 $1,562 $500 $500 SO 0 0 0 1,032 98 82 $1,212 SO 0 0 0 1,166 100 84 $1,350 SO 0 0 0 14,568 1,200 1.000 $16,768 $50 $50 $1,587 $50 $1.649 $50 $1,925 $50 $1,696 $50 $50 $1,987 $50 $1.925 $50 $2,277 $1,612 $50 $2,050 $600 $21,868 $500 $500 $500 $500 $500 $500 $500 $500 $500 Bad Debt Expense SO Cash Shortage/Excess 0 Bank Deposit Short/Exc. 0 Credit Card Chargeback 0 Credit Cards (40%ce) 1.144 Guest Vouchers 98 Refunded Rentals Total Other Expense $1,324 Other External Services Total Misc. Operating $1,724 Property Marketing $500 Building S150 Landscaping 825 Contracts 675 Laundry 500 Rooms 200 HVAC 400 Pool 0 Electrical & Fire 360 Plumbing 275 Supplies 300 Unclassified (petty cash) 75 Total R&M $3,760 $6,000 SI50 $150 825 675 500 825 SISO 825 675 500 200 400 $150 825 675 500 $150 825 675 500 675 500 200 400 S150 825 675 500 200 400 200 200 200 400 $150 825 675 500 200 400 0 360 275 300 75 $3,760 $150 825 675 500 200 400 0 360 275 300 75 $3.760 $150 825 675 500 200 400 600 360 275 300 75 $4,360 400 300 360 275 300 75 $4,060 $150 825 675 500 200 400 600 360 275 300 75 $4.360 S150 825 675 500 200 400 100 360 275 300 75 $3,860 360 275 300 75 $3,760 S1,800 9.900 8,100 6,000 2.400 4,800 1.600 4320 3,300 3,600 900 $46,720 360 275 300 75 $3,760 400 0 360 275 300 75 $3.760 360 275 300 75 $3,760 360 275 300 75 $3,760 Table 4: Jack Smith's Monthly Budget Worksheet (continued) May Jun Jul Aug Sep Oct Nov Dec Total $3,500 1,400 2.750 $7,650 $3,500 1,400 2.750 $7,650 $3.500 1.400 2.750 $7,650 $3,500 1.400 2.750 $7,650 $3,500 1,400 2.750 $7,650 $3,500 1,400 2.750 $7,650 $42,000 16,800 33.000 $91,800 Item Jan Feb Mar Apr Expenses (continued) Electricity $3,500 $3,500 $3.500 $3,500 Natural Gas 1,400 1,400 1,400 1,400 Water 2.750 2.750 2.750 2.750 Total Energy $7,650 $7,650 $7,650 $7,650 Garbage Collection $310 $310 $310 S310 Sewer 1.575 1.575 1.575 1,575 Salt (water softener) 350 0 350 Telephone 1,000 1,000 1,000 1,000 Satellite TV Fees 1.250 1,250 1.250 1.250 Utility Fees 25 23 25 75 Total Other Utilities $4.210 $4,560 $4,210 $4,560 TOTAL EXPENSES $45,846 $44,780 $45,016 $46,941 PROFIT $49,461 $40,099 844.303 $63,474 Note: All monetary values are represented in U.S. dollars. $310 1.575 0 1,000 1.250 25 $4,210 0 $310 1.575 350 1,000 1.250 25 $4,560 $310 1.575 0 1.000 1.250 25 $4,210 $310 1,575 350 1,000 1.250 25 $4,560 $310 1,575 0 1.000 1.250 25 $4,210 $3,500 $3,500 1,400 1,400 2.750 2.750 $7,650 $7,650 $310 $310 1,575 1.575 0 350 1,000 1.000 1,250 1.250 25 25 $4,210 $4,560 $310 1.575 350 1,000 1,250 23 $4,560 $3,720 18,900 2,100 12,000 15.000 900 $52,620 $45,345 S46,613 S46,359 $49,116 $45,782 S43,758 $45,116 $46,153 S550.824 $47.919 $69.631 $65,367 $89,011 S61,559 S40,386 S40,906 S51,028 $663,144 Table 1: Local and Regional Competitor Sales Data Local Competitors Metropolitan Area Rooms ADR Occupancy Rooms ADR Occupancy Year Rented (USS) (%) Rentals (USS) (%) 2012 96,099 60.77 58.72 181,361 47.53 56.11 2013 104,013 64.70 58.16 196,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 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) 62.73 Table 2: 2017 Profit-and-Loss Summary for the Amarillo Property Panel A: Summary Statistics and Revenues Budget Actual Rooms Available Rooms Rented 45,990 27,376 45,990 25,435 Occupancy Average Daily Rate (ADR) Revenue per Available Room (RevPAR) $ $ 59.5% 43.79 26.07 $ $ 55.3% 44.65 24.69 REVENUES Room Rentals Revenue Allowance Total Room Revenue $ 1,200,138 1.280) $ 1,198,858 $ 1,136,619 01.060 $ 1,135.559 $ $ Intemet Access Vouchers Laundry Vending Total Other 4,900 5,000 1,310 3,900 15.110 5,534 1,262 1,240 3,708 11.744 $ $ TOTAL REVENUE $ 1.213.968 $ 1,147,303 Note: All monetary values are represented in US dollars. Table 2: 2017 Profit-and-Loss Summary for the Amarillo Property Actual S $ 27,434 82,089 $ 109,523 S S $ 22.226 82.556 104,782 S S 23,577 6,452 0 1,346 21,339 $ 52,714 S $ 267,019 S $ Panel B: Expenses Budget EXPENSES General Manager 28,800 Front Desk Staff 72.000 Total Front Office 100,800 Head Housekeeper 19,000 Housekeepers and Laundry 81.816 Total Housekeeping S 100,816 Maintenance Wages 20,700 Regional Maintenance Support 3,180 Incentive Pay (Staff) 0 Bonus Expense, General Manager 2,520 Payroll Tax Expense 19,381 Total Other-Personnel Payroll $ 45,781 Gross Payroll 247,397 Vacation Expense 5,700 Holiday Expense 1,620 Staff Incentives 640 Employee Relocation Expense 0 Employee Training 2,260 Workers Compensation 18,000 Health Insurance 28,800 Retirement Plan Contribution Expense 720 Group Life Insurance 672 Total Benefits Expense 58.412 Total Personnel Costs $ 305,809 Supplies, Cleaning Chemicals Supplies, Linens Supplies Other Total Supplies 26,007 Memberships and Subscriptions 300 Travel 3,360 Meals 840 Total Travel and Meals S 4.500 Note: All monetary values are represented in US dollars. 3,494 1,690 639 1,506 576 17,588 20,005 841 613 46.952 $ 313,971 $ 6,083 13,916 18.064 38,063 S $ S S 0 4,141 921 5,062 S Table 2: 2017 Profit-and-Loss Summary for the Amarillo Property $ 0 Actual 350 113 27 619 14,376 540 1.882 17,912 $ $ S 722 23,696 S 4,807 $ Panel B: Expenses (continued) Budget Bad Debt Expense Cash Shortage and Excess Bank Deposit Shortage and Excess Credit Card Chargeback Credit Card Fees 14,568 Guest Vouchers 1,200 Refunded Rentals 1.000 Total Other Expense S 16,768 Other External Services $ 600 Total Miscellaneous Operating Expenses 21,868 Property Marketing S 6,000 Building 1,800 Landscaping 9,900 Contracts 8,100 Laundry 6,000 Rooms (floors, doors, windows) 2,400 HVAC 4,800 Pool 1,600 Electrical and Fire 4,320 Plumbing 3,300 Supplies 3,600 Unclassified (including petty cash) 900 Total R&M S 46,720 Electricity S 42,000 Natural Gas 16,800 Water 33.000 Total Energy S 91,800 Garbage Collection $ 3,720 Sewer 18,900 Salt (Water Softener) 2,100 Telephone 12,000 Satellite Television Fees 15,000 Utility Fees 900 Total Other Utilities 52.620 TOTAL EXPENSES 550.824 PROFIT 663,144 Note: All monetary values are represented in US dollars. 1,913 10,524 8,460 2,713 5,610 3,219 2,447 5,852 5,243 4,160 2.411 52,552 $ 36,144 13,428 21.919 71,491 $ $ 3,494 16,160 1,740 10,524 13,413 802 46.133 550,713 596,590 S S S S Table 3: Employee Information by Job Type Hourly Hours 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 980511 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 45.990 Rooms Rented 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% 59.2% 58.7% 59.5% ADR $42.00 $40.00 $40.00 $44.00 $42.00 $48.00 $48.00 $50.00 $46.00 $44.00 $38.00 $42.00 $43.79 RevPAR $24.14 $23.79 $22.61 $28.89 $23.58 $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 $110,256 $136,500 $105,938 $83,160 $85,082 $96.222 $1,200,138 Revenue Allowance 80 80 80 100 100 140 160 160 130 100 80 70 1280 Total Room Revenue $94.294 $83.920 $88,320 $109,196 $92,090 $114,580 $110,096 $136,340 $105,808 $83,060 $85,002 $96,152 $1,198,858 Internet Access S402 $376 $396 $445 $393 $428 411 $489 $412 $338 S401 $410 $4,900 Vouchers 410 384 404 454 401 437 420 499 421 345 409 418 5.000 Laundry 50 50 50 80 80 200 200 200 200 100 50 50 1,310 Vending 150 150 150 240 300 600 600 600 500 300 150 3.900 Total Other $1,013 $959 $999 $1,218 $1.174 $1,664 $1.631 $1,787 $1,533 $1,083 $1,020 $1.028 $15.110 160 Total Revenue $95,307 $84,879 $89.319 $110.414 $93.264 $116,244 $111.727 $138,127 $107,341 $84.143 $86,022 $97.180 $1,213,968 TSIL Table 4: Jack Smith's Monthly Budget Worksheet (continued) 210 210 Item Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Expenses General Manager $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $2,400 $28,800 Front Desk Staff 6,000 6,000 6,000 6,000 6,000 6.000 6.000 6,000 6.000 6,000 6,000 6.000 Total Front Office 72.000 $8,400 $8,400 $8.400 $8,400 $8,400 $8,400 $8,400 $8.400 $8,400 $8,400 $8.400 $8,400 $100,800 Head Housekeeper $1,583 $1,583 $1,584 $1.583 $1,583 $1,584 $1,583 $1,583 $1,584 $1,583 $1,583 $1,584 Housekeepers $19,000 6.698 6.183 6.568 7.527 6.516 22198 6.873 8388 6.894 5.448 6.670 6.853 Total Housekeeping $7,865 81.816 $7,350 $7,735 $8,694 $7,683 $8,365 $8,040 $9,555 $8,061 $6,615 $7,837 $8,019 $100,816 Maintenance Wages $1.725 $1,725 $1.725 $1,725 $1,725 $1.725 $1,725 $1,725 $1,725 $1,725 $1,725 $1,725 $20,700 Regional Maintenance 265 265 265 265 265 265 265 265 265 265 265 265 3.180 Incentive Pay (staff) 0 0 0 0 0 0 0 0 0 0 0 0 0 Bonus Expense, GM 210 210 210 210 210 210 210 210 210 210 2,520 Payroll Taxes 1.605 1.561 1.594 1675 1.589 1647 1.620 1.749 1.622 1.499 1603 1.618 Total Other Personnel $3,805 19.381 $3.761 $3.794 $3.875 $3.789 $3.847 $3.820 $3.949 $3.822 $3.699 $3.803 $3.818 $45,781 Gross Payroll $20,486 $19,927 $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 S475 S475 $475 S475 S475 $475 $475 $475 $475 $475 $5,700 Holiday Expense 135 135 135 135 135 135 135 135 135 135 135 135 1.620 Staff Incentives 35 35 35 90 90 90 35 35 35 640 Employee Relocation 0 0 0 0 0 0 0 0 0 0 0 0 0 Employee Training 720 140 140 140 140 140 140 140 140 140 140 140 Workers Compensation 1,500 2,260 1,500 1,500 1,500 1,500 1,500 1.500 1.500 1.500 1,500 1,500 1.500 Health Insurance 18,000 2,400 2.400 2,400 2.400 2,400 2.400 2,400 2,400 2.400 2.400 2,400 2.400 Retirement Plan Contr. 60 28.800 60 60 60 60 60 60 60 60 60 60 720 Group Life Insurance 56 56 56 56 56 56 56 56 56 56 56 672 Total Benefits Expense $5,381 $4,801 $4801 $4,801 S4,856 $4.856 $4,856 S4856 $4,801 $4,801 $4801 $4,801 $58,412 Total Personnel Costs $25,867 $24,728 $25.147 $26,186 $25,144 S25,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,007 35 90 35 Table 4: Jack Smith's Monthly Budget Worksheet (continued) Item Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Expenses (continued) Membership & Subs. Travel Meals Total Travel & Meals SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 SO 280 70 $350 $300 280 70 $650 $300 3,360 840 $4,500 SO 0 0 0 1,019 92 77 S1.187 SO 0 0 0 1,072 97 81 SI 249 SO 0 0 0 1.325 109 91 $1,525 SO 0 0 0 1,119 96 80 $1.296 SO 0 0 0 1.395 105 87 $1,587 SO 0 0 0 1,341 101 84 $1.525 SO 0 0 0 1.658 120 100 $1,877 SO SO 0 0 0 0 0 0 1.288 1,010 101 83 84 69 $1,473 $1,162 $50 $50 $1,873 $1,562 $500 $500 SO 0 0 0 1,032 98 82 $1,212 SO 0 0 0 1,166 100 84 $1,350 SO 0 0 0 14,568 1,200 1.000 $16,768 $50 $50 $1,587 $50 $1.649 $50 $1,925 $50 $1,696 $50 $50 $1,987 $50 $1.925 $50 $2,277 $1,612 $50 $2,050 $600 $21,868 $500 $500 $500 $500 $500 $500 $500 $500 $500 Bad Debt Expense SO Cash Shortage/Excess 0 Bank Deposit Short/Exc. 0 Credit Card Chargeback 0 Credit Cards (40%ce) 1.144 Guest Vouchers 98 Refunded Rentals Total Other Expense $1,324 Other External Services Total Misc. Operating $1,724 Property Marketing $500 Building S150 Landscaping 825 Contracts 675 Laundry 500 Rooms 200 HVAC 400 Pool 0 Electrical & Fire 360 Plumbing 275 Supplies 300 Unclassified (petty cash) 75 Total R&M $3,760 $6,000 SI50 $150 825 675 500 825 SISO 825 675 500 200 400 $150 825 675 500 $150 825 675 500 675 500 200 400 S150 825 675 500 200 400 200 200 200 400 $150 825 675 500 200 400 0 360 275 300 75 $3,760 $150 825 675 500 200 400 0 360 275 300 75 $3.760 $150 825 675 500 200 400 600 360 275 300 75 $4,360 400 300 360 275 300 75 $4,060 $150 825 675 500 200 400 600 360 275 300 75 $4.360 S150 825 675 500 200 400 100 360 275 300 75 $3,860 360 275 300 75 $3,760 S1,800 9.900 8,100 6,000 2.400 4,800 1.600 4320 3,300 3,600 900 $46,720 360 275 300 75 $3,760 400 0 360 275 300 75 $3.760 360 275 300 75 $3,760 360 275 300 75 $3,760 Table 4: Jack Smith's Monthly Budget Worksheet (continued) May Jun Jul Aug Sep Oct Nov Dec Total $3,500 1,400 2.750 $7,650 $3,500 1,400 2.750 $7,650 $3.500 1.400 2.750 $7,650 $3,500 1.400 2.750 $7,650 $3,500 1,400 2.750 $7,650 $3,500 1,400 2.750 $7,650 $42,000 16,800 33.000 $91,800 Item Jan Feb Mar Apr Expenses (continued) Electricity $3,500 $3,500 $3.500 $3,500 Natural Gas 1,400 1,400 1,400 1,400 Water 2.750 2.750 2.750 2.750 Total Energy $7,650 $7,650 $7,650 $7,650 Garbage Collection $310 $310 $310 S310 Sewer 1.575 1.575 1.575 1,575 Salt (water softener) 350 0 350 Telephone 1,000 1,000 1,000 1,000 Satellite TV Fees 1.250 1,250 1.250 1.250 Utility Fees 25 23 25 75 Total Other Utilities $4.210 $4,560 $4,210 $4,560 TOTAL EXPENSES $45,846 $44,780 $45,016 $46,941 PROFIT $49,461 $40,099 844.303 $63,474 Note: All monetary values are represented in U.S. dollars. $310 1.575 0 1,000 1.250 25 $4,210 0 $310 1.575 350 1,000 1.250 25 $4,560 $310 1.575 0 1.000 1.250 25 $4,210 $310 1,575 350 1,000 1.250 25 $4,560 $310 1,575 0 1.000 1.250 25 $4,210 $3,500 $3,500 1,400 1,400 2.750 2.750 $7,650 $7,650 $310 $310 1,575 1.575 0 350 1,000 1.000 1,250 1.250 25 25 $4,210 $4,560 $310 1.575 350 1,000 1,250 23 $4,560 $3,720 18,900 2,100 12,000 15.000 900 $52,620 $45,345 S46,613 S46,359 $49,116 $45,782 S43,758 $45,116 $46,153 S550.824 $47.919 $69.631 $65,367 $89,011 S61,559 S40,386 S40,906 S51,028 $663,144
