One of our clients have several hotels all over the world that are financed mostly by bank loans. For taking new loans (for building / acquiring new hotels) from bank and to comply with the requirement conditions of the existing loans, our client had to submit its project financial statements and cash flow statement for the current year and further 10 years to the bank.
Preparing a forecast of financial statements and cash flow statement requires consideration of several factors, estimates etc which have a tendency to change year on year. Eg: growth rate, tax, expansion of hotels, change in staff.
By considering these variables, the clients team used to prepare the projected financial and cash flow statements for current and future 10 years by adjusting year on year figures according to the changing variables. This was a herculean task in itself as it used to take several days to complete and was prone to human error. Then again, checking and reviewing these projected data was another daunting task for the management.
Development and deployment of excel model:
Our team developed two integrated excel models.
- For calculating and preparing 10 years projected financials for n number of hotels.
- To combine the projections from independent hotels and make a consolidated financials.
The model was prepared after understanding clients exact needs and researching the earlier projections made by the client during last several years.
This model had just one input sheet, where the client needs to feed in the variables data i.e. say, the financial data from the last audited accounts, the expected growth rate of the company, expected extension/expansion in hotel rooms etc. i.e. information which are available with the client.
Based on the inputs given by the user, the model then automatically calculated all the fields and prepared the projected financial statements and cash flow statements for next 10 years.These projections calculated by excel model were made in the precise format that were required by the bank. This excel model could be used to prepare projections for n number of hotels and to merge the figures of these individual hotels and prepare a consolidated projected report.
Our client submits these resulting excel sheets (projections) to the bank for loan purposes and also for internal studies.
Before the introduction of the model, the client used spend 8-10 days in perfecting and reviewing the projections.
The work is now handled by the Excel model in matter of minutes!!