Operational Finance: Financial Modeling in Hospitality and Tourism

This report focuses on operational finance within the hospitality and tourism sector, utilizing financial modeling techniques to create budgets and income statements. The model is highly flexible and integrated, allowing for dynamic adjustments to input data, such as sales and purchase percentages, which automatically update output values. The report provides detailed instructions on using the model, including the input of data, the use of percentage tables, and the application of the IF function for calculating credit and debit amounts. It also highlights the model's flexibility and integration, showcasing how changes in one cell can affect multiple outputs. Furthermore, the report analyzes the financial performance of an example organization, noting increasing cash surplus balances, fluctuating sales and purchases, and a strong net profit, concluding that the firm demonstrates excellent performance. The financial modeling approach saves time and provides an integrated view of financial data, making it a valuable tool for managers in the hospitality and tourism industry.

Operational finance for hospitality and
tourism

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................3
2 (A) Use of model and its features............................................................................................3
2 (b).............................................................................................................................................7
Analysis and interpretation of financial performance of an organization ..................................7
CONCLCUSION.............................................................................................................................8
REFERENCES................................................................................................................................9
INDEX OF TABLES
Table 1: Input sheet 1......................................................................................................................4
Table 2: Percentage input sheet.......................................................................................................5
Table 3: Automatic carry forward of values on which credit and debit amount will be computed.5
Table 4: Budget for organization.....................................................................................................6
Table 5: Income statement ..............................................................................................................7

INTRODUCTION
Financial modeling is a technique by using which projections are made about the future.
By using this method income statement, balance sheet and cash flow statement is prepared. In
the present case by using financial modeling method budget and income statement is prepared.
Model that is prepared is highly flexible and integrated in nature which is its big positive point.
When one cell in input table will be altered then other values of the output sheet will also
changed. Hence, it can be said that every time managers does not need to prepare new budget on
paper. They can be changing values of input sheet can prepare new budget.
2 (A) Use of model and its features
Way or instruction for use of model
In order to use this model following instructions will be followed. Inserting input data- In order to prepare budget on this spreadsheet it is very important
to enter a value. User of this model can either directly enter values in input sheet section
of this model or he may first enter values on paper and thereafter can copy same values in
excel sheet. The approach user follow depend on the way in which he feel comfort. Percentage table- Second table in input sheet is a percentage table in which percentage of
cash sale, credit sales, credit purchase and cash purchase will be entered. These cells are
linked to the budget and with change in the values of cell in this table entire budget
values will get changed. Thus, user in future if any change comes in projected cash and
credit sales as well as purchase percentage must make changes in values of variables that
are in the percentage table. Automatic carry forward of values on which credit and debit amount will be computed-
This is third important table of the input sheet. Under this table formula of IF function of
excel is inserted. In this table values of previous months are carry forward that are related
to credit sales and purchase. In order to use this table it is instructed that user must double
click on specific value and must change in last two values in formula. For example in
case of facilities and room sales one month time is given to debtors. Hence, formula is
=IF(C27>C26;C5) and if user think that credit period may change in future then he will
make changes in last two values that are C26 and C5. When by replacing these values

new cells name will be entered then model will automatically calculate credit sales and
purchase. Hence, it is instructed that user must change only last two cell names. Instruction for use of budget cash flow model- In order to use this model it is instructed
that user must not change any formula that is inserted in this model. All cells of this
model are linked to input sheet. When values of input sheet will change each and every
value in this model will change automatically. Hence, it is instructed to user of this model
that any sort of change he must not done on this model. Instruction for use of P&L statement model- For making use of P&L statement model
also it is instructed that user must not make any type of change in this model. All values
of this model are linked to the budget model. Hence, change in budget model will directly
affect income statement of the firm.
Features built in model Flexibility- Flexibility is one of the most important feature of this model and under this
model is prepared in such a way that new values can also be entered in to budget. It can
be seen in the budget given in input table that additional column of other expenses is
added in the table (Charnes, 2012). Same thing is added in the output budget sheet. If in
future firm wants to add new things in budget then it can enter values in to column and
row of other expenses. This model is not rigid and all cells are interlinked to each other.
Thus, if change will come in one values all relevant values will automatically get
changed. In order to do one change user of the model does not need to change several
cells of the spreadsheet. He merely needs to make change in single cell in order to bring
changes in values of all variables in which changes are required. Integration- Integration refers to the interrelationship between different cells. This model
is highly integrated in nature and input as well as output sheet are highly integrated to
each other (Libman, 2011). Along with this all cells in single output model are connected
to each other. Thus, integration among cells is another important feature of this
spreadsheet.
Table 1: Input sheet 1
Facilities & Food Sales Drink Food Drink Wages Over Other

Room Sales Sales
Purch
ases
Purchas
es heads
expens
es
£ £ £ £ £ £ £ £
February 60000 24000 7000 8000 4500 40000
2400
0 0
March 70000 25000 8000 8500 4500 44000
2500
0 0
April 100000 40000 10000 15000 5000 50000
3200
0 0
May 110000 50000 11000 21000 5500 52000
3000
0 0
June 60000 40000 9000 16000 4000 40000
2500
0 0
July 80000 30000 15000 12000 7000 30000
2400
0 0
August 75000 26000 12000 9000 6000 28000
2000
0 0
Septembe
r 50000 20000 10500 8000 5800 25000
1800
0 0
Table 2: Percentage input sheet
Percentage
Cash sale of food 40.00%
Credit sale of food 60.00%
Cash sale of drink 40.00%
Credit sale of drink 60.00%
Drink purchase on credit 100.00%
Food purchase on credit 100.00%
Sales of rooms on credit 80.00%

Cash sale of room 20.00%
Table 3: Automatic carry forward of values on which credit and debit amount will be computed
Number
allotted to
months for
applying IF
function of
excel
Facilities &
Room Sales
Food
Sales
Drink
Sales
Food
Purchases
Drink
Purchases
February 1
March 2
April 3 70000 24000 7000 8500 4500
May 4 100000 25000 8000 15000 5000
June 5 110000 40000 10000 21000 5500
July 6 60000 50000 11000 16000 4000
August 7 80000 40000 9000 12000 7000
September 8 75000 30000 15000 9000 6000
Output sheet
Table 4: Budget for organization
April May June July August
Septemb
er
Cash inflow
Opening balance 27600 76800 127900 166500 218100
Facilities & Room Sales 20000 22000 12000 16000 15000 10000
Facilities & room sales on
credit 56000 80000 88000 48000 64000 60000
Food sales 16000 20000 16000 12000 10400 8000

Credit food sales 14400 15000 24000 30000 24000 18000
Drink sales 4000 4400 3600 6000 4800 4200
Credit drink sales 4200 4800 6000 6600 5400 9000
Total cash inflow 114600 173800 226400 246500 290100 327300
Cash outflow
Food purchase 8500 15000 21000 16000 12000 9000
Drink purchase 4500 5000 5500 4000 7000 6000
Wages 50000 52000 40000 30000 28000 25000
Overhead 24000 25000 32000 30000 25000 24000
Depreciation on fixtures 8000
Depreciation on equipment 5625
Other expenses 0 0 0 0 0 0
Total expenses 87000 97000 98500 80000 72000 77625
Cash surplus/ deficit 27600 76800 127900 166500 218100 249675
Total sales excluding transfer
ed balance 114600 146200 149600 118600 123600 109200
COGS 13000 20000 26500 20000 19000 15000
Table 5: Income statement
Particulars 2016
Sales 761800
Cost of goods sold 113500
Gross profit 648300
Expenses
Wages 225000
Overhead 160000
Depreciation 13625
Other expenses 0
Total expenses 398625
Net profit 249675

2 (b)
In the spreadsheet given model is prepared flexible and change in months of credit
allowed to and taken from debtors as well as creditors are taken in to consideration. In context to
this separate table is prepared in input sheet. In this part of table number is allotted to each month
and if function is applied on all credit related items in terms of debtor and creditor. As mentioned
above that if user wants to make changes in credit period then he will need to make minor
changes in the formula. After doing so all relevant values of the budget and income statement
will get changed automatically. Hence, by making change in single formula and by stretching
same changes can be made all cells that contain IF formula. So, by making changes in one place
all credit sale, purchase and cash sale as well as purchase will be computed automatically.
Analysis and interpretation of financial performance of an organization
From analysis of budgeted figures it can be said that firm give good performance in its
business. As cash surplus balance of the firm is increasing consistently. If we look at purchase
value of food and drink purchase then it can be seen that their values are fluctuation consistent.
Some times firm increase product sales but some times it increase product purchase. Sales of the
firm is also fluctuating continuously. This means that firm in alignment to change in sales is
bringing changes its purchase. This is the reason due to which firms cash balance is increasing
continuously even its sales is fluctuating consistently. Hence, it can be said that firm is giving
magnificent performance in its business. Net profit earned by the firm is also of high amount
and it can be assumed that firm is earning good profit in its business.
CONCLCUSION
On the basis of above discussion it is concluded that financial modeling is a very
important method because by using same lots of time is saved easily. By using financial model
all things that are related to problem are integrated easily and impact of change in one factor on
the budget and firm profitability can easily be tested. Hence, it is concluded that financial model
must be used by each and every type of manager.

REFERENCES
Books & journals
Charnes, J., 2012. Financial modeling with crystal ball and excel. John Wiley & Sons
Online
Libman, A., 2011. [Online]. Finacial modeling techniques. Available through <
https://www.wallstreetprep.com/blog/financial-modeling-techniques/>. [Accessed on 5th
May 2016].