Financial Modelling & MIS for Hospitality Industry in MS Excel & VBA

Automation of Budget Preparation in Hospitality Industry (in MS Excel and VBA for MS Excel)

“Enabling non-technical users to harness low-code tools to drive their own automation and digital transformation projects can take huge pressure off IT teams” ( (Kelly, n.d.) https://www.forbes.com/sites/jackkelly/2022/02/08/companies-are-racing-toward-digitalization-to-unlock-value-and-improve-the-customer-and-employee-experience/?sh=15b08018589c

Executive Summary

Hospitality Industry consists of multiple investment, profit, revenue and cost centres. It also consists of different categories of business as well as delivery models. They are on the basis of ownership, product, service, bouquet, brand, customer segment, geographical locations etc.

The industry is also subject to a peculiar characteristic of ‘perishability’ of its inventory of rooms in terms of ‘loss of revenue’, even though the rooms, physically are not perishable. Every revenue generating infrastructural unit (such as Rooms) or one that contributes (such as Tables in restaurants) towards earning of revenue becomes an ‘idle’ investment representing financial and other resources, if not occupied. Similar scenario is true with reference to other resources of the industry as well.

Hence, an effective planning and control mechanism must be established and operational, leading to effective and efficient application of resources in a way the outcome aligns congruently with organizational strategies and goals. This requires a well designed and implemented ‘Enabler System’ that consists of a robust budgeting process and performance evaluation process.

Role of Financial Models and Automated MIS / Budgeting in Hospitality Industry

While nature and methodology of preparation of Budgets depends upon organizational policy and Standard Operating Procedures, the basic concepts and the linkage between objectives, budgeting process and outcomes essentially remain the same. An entity is guided by its Vision and Mission that are achieved by appropriate means and ways. While Vision and Mission form the basic mandate for the Top Management, the Middle and subsequent layers of management devise, implement and execute the ways and means towards achieving the goals and targets and in turn reaching the Vison and Mission. The management team chalks out strategies and plans and thereby policies and procedures. In that process, an organization considers the organizational resources and the constraints the organization is subjected to. Budgeting forms the primary ways and means tool in planning and deployment of resources. The following flow chart illustrates the basic relationship between Organizational Vision, Mission, Strategies, Goals and ways and means of achieving the goals.

Automation of Budgeting - Figure 1

Budgeting Process in Hospitality Industry

Budgeting process may either be a simple document or a strategic and planning tool depending upon the management’s mandate, preferences, size and nature of operations of the hospitality unit and the time horizon for which the budgets are prepared. A Hospitality unit may follow either a ‘Top Down’ approach or ‘Bottom up’ approach. A ‘top down’ approach emphasizes on ‘what the unit wants to achieve’, while a ‘bottom up’ approach emphasizes on what individual process such as ‘Accommodation’ or ‘Food and Beverage’ plans to / can achieve during the budget period subject to the availability of resources, constraints and limitations.

Irrespective of the approach, respective process managers such as Accommodation Manager, F&B Manager, Other operating departmental and service departmental heads prepare the budgets.

The budgets are prepared under the guidance from the General Manager and the Finance and Business Support Manager. The Finance and Business Support Manager (or Financial Controller) also coordinates the process of preparation of budgets, provides technical guidance and compiles in collaboration with the General Manager or the Corporate Unit Finance Head, depending upon the organizational structure / hierarchy. ‘Compilation’ refers to consolidation of individual process budgets and preparation of ‘Master Budget’.

Types of Budgets

At a macro level, Budgets are classified into Master Budget and its component Budgets. Nonetheless, based on the nature, objectives and time horizon they may be classified further as well. Since all the budgets contribute to organizational performance, financial position and cash flows of the Unit, the Master Budget consists of Budgeted Income Statement, Budgeted Statement of Financial Position and Budgeted Cash Flows Statement. Based on ‘Objectives’, ‘Budgets’ are classified into Operating Equipment budget (eg: kitchen equipment, hair dryer in rooms), Capital Expenditure (very often denoted by the acronym ‘CapEx’ ) Budget, Revenue Budget etc., Based on hotel business process, budgets are classified into ‘Accommodation Division Budget’, F&B Budget, Administration Expenses Budget, Human Resources Division Budget etc., Based on time horizon, they are classified into Short Term (monthly, quarterly, annual) and Long term budgets (say Five year). Budgets are also classified into Scenario based budget (eg: budget during COVID), Rolling Budget etc., Based on the ‘concepts’, it may be either a ‘Zero base’ budget or ‘Incremental (Decremental) budget. On the basis of ‘scope’, it can be as much to the extent of valuation of a Hotel Unit.

Budget vs Forecast

A budget refers to a financial and or quantitative document that describes deployment of resources in a future period. A forecast on the other hand refers to ‘what is most likely to happen’. For example, based on the current scenario of booking of rooms, determining the possible occupancy of rooms is considered a forecast. Hence while budget is more of a plan even though it may or may not approximate actuals, forecast on the other hand is closer towards the future actual occurrence. For example, calculating the ‘expected’ revenue of accommodation division and food and beverage division based on the current booking and expected walk in guests during the next one week is a ‘forecast’.

‘Ingredients’ of a Budget

Preparation of budget requires consideration of various inputs, known as ‘variables’. They arise from both outside and within the organization. They are independent variables and dependent variables. Such variables can be controllable or uncontrollable. Variables which determine the behaviour of other variables that contribute to the income or financial position of a hotel are known as ‘independent variables’. They are for example caused by factors such as future economic growth (say, impending recession), geopolitical scenario (say Russian invasion of Ukraine), expected / unexpected pandemic (say COVID). Room occupancy, tariff rates, F&B sales etc., are negatively impacted by those factors. Thus, the variables which are impacted by independent variables are known as ‘dependent variables’. Another example of a ‘dependent variable’ is, number of full time employees that are to be recruited by the hotel unit. Nonetheless, actual number of FTEs that may be recruited is within the control of the process manager, because she / he might go in for ‘y’ number of Part time employees instead of ‘x’ number of full time employees. Such variables are known as ‘controllable variables’.

Automation of Budgeting - Figure 2

A ‘good budget’ depending upon the objective and scope must consider the relevant ‘controllable’ and ‘hotel dependent variables’ as they impact the hotel unit’s operation, besides incorporating to a reasonable extent the ‘expected impact’ of the ‘independent’ and ‘uncontrollable’ variables which greatly influence the performance parameters in preparation of the budgets.

Rationale for preparation of Budgets in MS Excel and VBA for MS Excel

While budgets can be prepared in pen and paper, such budgets can not provide for sensitivity analysis based on the constraints and limiting factors. Budgeting software solutions on the other hand make human participation, involvement and understanding less desirable and make process managers and finance managers much dependent upon the Information Technology division of the hospitality unit.

However, spreadsheets such as MS Excel along with the respective programming features empower finance and other process managers with the ability and skills to prepare budgets in a professional way. While the managers already possess the requisite / expert Business knowledge, a little bit of more MS Excel and VBA skills make them completely independent in relation to translation of business knowledge into budgets.

Preparation of Automated Budgets in MS Excel and VBA for MS Excel

The various steps involved in relation to preparation of Budgets in MS Excel and VBA are:

No Description No Description

1 Study the External Macro factors (eg: economy, inflation, competition etc.,) 11 Add Excel Macros and VBA programming features wherever required

2 Assess the expected impact of the external factors 12 Prepare the Limiting factor budget (such as Capital Expenditure budget)

3 Study the Internal Macro factors (such as required / change in hurdle rate of ‘ROI’) 13 Prepare Master Budget & other component Budgets (by process owners)

4 Map the organizational goals and targets with the external and internal factors 14 Validate the Individual budgets

5 Assess the impact of mapping on internal macro factors (such as revenue required) 15 Consolidate the Individual budgets

6 Identify and determine the constraints 16 Validate the Consolidated Budgets

7 Identify the Limiting Factor Budget, Master Budget and Component Budgets 17 Review the Individual and Consolidated Budgets

8 Gather and collect the data 18 Prepare the Report and Present the Budget to the Top Management

9 Prepare the possible scenarios for various key impacting factors 19 Incorporate the changes and corrections as instructed by the Top Management

10 Design the Spread sheets, create links between sheets and build in Controls 20 Submit the corrected Budget to the Top Management for Approval and Roll out.

Contact us for further enrichment of your Intellectual asset

We are conducting workshops and training programs that ‘Empower Managers of different processes of the Hotel Industry with the requisite conceptual knowledge, the significance of ‘identification’ with organizational goal congruence and skills in designing Automated Budgets and Performance Evaluation tools in MS Excel and VBA for MS Excel.

Dr. Subramanian Nalayiram M.Com., FCA, CISA (USA), Ph.D., Data Science (Johns Hopkins)

Dr Nala FinSys Consulting and Training, Singapore

https://www.4kfinlearntech.com subbunassociates@gmail.com / drnala@4kfinlearntech.com