The Manager of Costa del Sol Hotel in Marbella has asked you to prepare a budgeting forecast for the years 2023 – 2026.
I need the answer in the excel sheet, typically as per the requirement in each file.
Please read the file carefully and answer each point.
Kindly, return to me three files on excel as required
Instructions
| Costa del Sol Hotel in Marbella | ||
| The Manager of Costa del Sol Hotel in Marbella has asked you to prepare a budgeting forecast for the years 2023 – 2026. | ||
| You have acquired the following forecasted information: | ||
| General Info | The occupancy rate is expected to increase 3% per year | |
| The average rate is expected to increase 2€ in 2023, 3€ in 2024, 5€ in 2025 and 5€ in 2026 | ||
| Revenue Info on Ratios | Food revenue is expected to increase 1% per year | |
| Beverage revenue is expected to remain constant in 2023, decrease 1% in 2024 and another 1% during 2025, then it will remain constant until 2026 | ||
| Wifi Films and other revenue will remain constant in 2023, decrease 0.6% in 2024, then it will remain constant until 2026 | ||
| SPA revenue will increase 1% per year | ||
| Departmental Expenses Info on Ratios | Rooms department expenses will increase 1.5% per year | |
| F and B department expenses will increase 0.5% per year | ||
| Wifi Films and other revenue department expenses will increase 0.2% per year | ||
| Spa department expenses will increase 0.2% per year | ||
| Undistributed Operating Expenses Info on Ratios | General and admistrative expenses will decrease 0.3% per year | |
| Sales and marketing will increase 0.4% per year | ||
| Property, operations and maitenance will decrease 0.1% per year | ||
| Energy will increase 3% in 2023 and another 3% in 2024, then it will decrease 1% per year in the last 2 years | ||
| Required: | ||
| Completed the 2023 – 2026 figures using the above information. | ||
| Occupancy : +2% | ||
| Average Rate: +15€ | ||
| Food revenue +3% | ||
| Beverage Revenue +2% | ||
| Spa +1,5% | ||
| Golf Revenue +1,5% | ||
| Rooms Dep Expense:+1,5% | ||
| F&B Dep Expense:+1% | ||
| Spa Dep Expense -2% | ||
| Golf Dep Ex -1% | ||
| Admin & General: -0,3% | ||
| Sales & Marketing: +0,5% | ||
| P.O & Maintenance: -0,1% | ||
| NO CHANGE | ||
| FIND PEB FOR THE TWO YEARS | ||
| FIND LoS for the two years to be able to make a GOP of 1,2M€ |
2022-2026 Projections
| Costa del Sol Hotel in Marbella | ||||||||||||||||||
| 1 | 2 | 3 | 4 | 5 | ||||||||||||||
| Year | 2022 | 2023 | 2024 | 2025 | 2026 | |||||||||||||
| Number of Rooms | 205 | |||||||||||||||||
| Room Nights Available | 74,825 | |||||||||||||||||
| Room Nights Sold | 53,874 | |||||||||||||||||
| Days Open | 365 | 365 | 366 | 365 | 365 | |||||||||||||
| Occupancy | 72% | |||||||||||||||||
| Average Rate | 110 | |||||||||||||||||
| RevPAR | 79 | |||||||||||||||||
| REVENUE | Ratios | Weight | % Change | Ratios | Weight | % Change | Ratios | Weight | % Change | Ratios | Weight | % Change | Ratios | Weight | ||||
| Rooms | 5,926,140 | |||||||||||||||||
| Food | 1,066,705 | |||||||||||||||||
| Beverage | 711,137 | |||||||||||||||||
| WiFi Films & Other Revenue | 213,341 | |||||||||||||||||
| SPA Revenue | 711,137 | |||||||||||||||||
| Total Revenue | 8,628,460 | |||||||||||||||||
| DEPARTMENTAL EXPENSES | ||||||||||||||||||
| Rooms | 1,784,141 | |||||||||||||||||
| Food and Beverage | 1,493,387 | |||||||||||||||||
| WiFi Films & Other revenue | 66,520 | |||||||||||||||||
| SPA | 45,578 | |||||||||||||||||
| Total Expenses | 3,389,627 | |||||||||||||||||
| DEPARTMENTAL INCOME | 5,238,832 | |||||||||||||||||
| UNDISTRIBUTED OPERATING EXPENSES | ||||||||||||||||||
| General and Administrative | 525,627 | |||||||||||||||||
| Sales & Marketing | 166,820 | |||||||||||||||||
| Property, Operations & Maintenance | 391,718 | |||||||||||||||||
| Energy | 355,568 | |||||||||||||||||
| Total Expenses | 1,439,732 | |||||||||||||||||
| GROSS OPERATING PROFIT | 3,799,100 | |||||||||||||||||
| Management Fee | ||||||||||||||||||
| GOP After Management Fee | ||||||||||||||||||
,
Management Contract Valuation
| Costa del Sol Hotel in Marbella | ||||||||||||||||||
| 1 | 2 | 3 | 4 | 5 | ||||||||||||||
| Year | 2022 | 2023 | 2024 | 2025 | 2026 | |||||||||||||
| Number of Rooms | 205 | |||||||||||||||||
| Room Nights Available | 74,825 | |||||||||||||||||
| Room Nights Sold | 53,874 | |||||||||||||||||
| Days Open | 365 | 365 | 366 | 365 | 365 | |||||||||||||
| Occupancy | 72% | |||||||||||||||||
| Average Rate | 110 | |||||||||||||||||
| RevPAR | 79 | |||||||||||||||||
| REVENUE | Ratios | Weight | % Change | Ratios | Weight | % Change | Ratios | Weight | % Change | Ratios | Weight | % Change | Ratios | Weight | ||||
| Rooms | 5,926,140 | |||||||||||||||||
| Food | 1,066,705 | |||||||||||||||||
| Beverage | 711,137 | |||||||||||||||||
| WiFi Films & Other Revenue | 213,341 | |||||||||||||||||
| SPA Revenue | 711,137 | |||||||||||||||||
| Total Revenue | 8,628,460 | |||||||||||||||||
| DEPARTMENTAL EXPENSES | ||||||||||||||||||
| Rooms | 1,784,141 | |||||||||||||||||
| Food and Beverage | 1,493,387 | |||||||||||||||||
| WiFi Films & Other revenue | 66,520 | |||||||||||||||||
| SPA | 45,578 | |||||||||||||||||
| Total Expenses | 3,389,627 | |||||||||||||||||
| DEPARTMENTAL INCOME | 5,238,832 | |||||||||||||||||
| UNDISTRIBUTED OPERATING EXPENSES | ||||||||||||||||||
| General and Administrative | 525,627 | |||||||||||||||||
| Sales & Marketing | 166,820 | |||||||||||||||||
| Property, Operations & Maintenance | 391,718 | |||||||||||||||||
| Energy | 355,568 | |||||||||||||||||
| Total Expenses | 1,439,732 | |||||||||||||||||
| GROSS OPERATING PROFIT | 3,799,100 | |||||||||||||||||
| Management Fee | ||||||||||||||||||
| GOP After Management Fee | ||||||||||||||||||
| FIXED EXPENSES | ||||||||||||||||||
| Property Taxes | ||||||||||||||||||
| Building and Business Interruption Insurance | ||||||||||||||||||
| Rent | ||||||||||||||||||
| Incentive Fee | ||||||||||||||||||
| Other Fixed Expenses | ||||||||||||||||||
| Reserve for Replacement | ||||||||||||||||||
| Total Expenses | ||||||||||||||||||
| NOI for the Operator | ||||||||||||||||||
| NOI for the Owner of the Building | ||||||||||||||||||
| Advantages | ||||||||||||||||||
| 1 | ||||||||||||||||||
| 2 | ||||||||||||||||||
| 3 | ||||||||||||||||||
Lease Contract Valuation
| Costa del Sol Hotel in Marbella | ||||||||||||||||
| 1 | 2 | 3 | 4 | 5 | ||||||||||||
| Year | 2022 | 2023 | 2024 | 2025 | 2026 | |||||||||||
| Number of Rooms | 205 | |||||||||||||||
| Room Nights Available | 74,825 | |||||||||||||||
| Room Nights Sold | 53,874 | |||||||||||||||
