Finance excel

Final Assignment

Written assignment (case studies) Activity brief BAT323 Financial Management in Tourism Online campus

Professor: Marco Botta – [email protected]

Description The assessment includes four case studies, each contributing for a fourth of the total score of the assessment.

Format This activity must meet the following formatting requirements:

 Font size 12

 1.5-spaced

 Harvard Referencing System

Goal(s) This assignment aims at testing the ability of students to properly use financial analysis to assess performance and to make forecasts and set financial targets

Due date Date: 10 May 2021

Time: 23:59 CEST

Weight towards final grade

This activity has a weight of 60% towards the final grade.

Learning outcomes

With the present assessment, students will be meeting the following learning outcomes:

 Understand financial

management techniques in

the tourism industry

 Use cost-volume-profit analysis to set revenue targets and understand the impact of changes in activity volume on income

 Understand the use of financial analysis to establish pricing policies and to set average price targets

 Evaluate the financial impact of revenue management policies

 Prepare and analyze budgets

Assessment criteria

9-10: The student demonstrates an excellent understanding of the concepts. 8-8.9: The student demonstrates a good understanding of the concepts. 7-7.9: The student demonstrates a fair understanding of the concepts. 6-6.9: The student demonstrates some, but insufficient understanding of the concepts. 3-5.9: The student demonstrates insufficient understanding of the concepts. They may mention some relevant ideas or concepts, although it is clear that the relationship between them is not understood by the student. 1-2.9: The student demonstrates insufficient understanding of the concepts and does not mention any relevant ideas or concepts. 0: The student leaves the question blank or cheats.

Case 1.

The following financial data relates to Tokyo’s EasternSunrise Hotel.

YEAR 2020 2019

REVENUE 5.250 5.000

COST OF SALES 1.080 1.000

GROSS PROFIT 4.170 4.000

SELLING EXPENSES 980 800

ADMINISTRATION

EXPENSES

1.900 1.200

EBIT 1.290 2.000

CASH 220 200

ACCOUNTS RECEIVABLE 360 350

INVENTORY 440 400

FIXED ASSETS 24.660 18.600

TOTAL ASSETS 25.680 19.5550

a.) Using EBIT data, calculate EasternSunrise hotel’s ROI for each year to determine whether

the hotel’s profitability is increasing or decreasing.

b.) Conduct further ratio analysis of the data provided to determine what are the main factors

that lie behind EasternSunrise’s changed ROI level.

Case 2.

Saturn Ltd, a large American hotel organization, has purchased a small rooming house on a

property adjacent to one of its main hotels. The acquisition was made with a view to demolishing

much of the existing structure and building a custom-designed health club that could be used by

hotel guests. Building of the new health club is not due to start for at least a year.

Saturn has been approached by Reg Norman, a contact of the property’s vendor. Norman

specializes in arranging golfing holidays. In keeping with the last 5 years, Norman wants to use the

building for 30 weeks to house a particular niche market of ‘economy golfers’. Norman says that

he can provide between six and fifteen guests per week. Under the proposed arrangement,

Norman will pay Saturn $200 for each guest provided with lodging and morning and evening

meals for a week.

Saturn’s accountant has developed the following cost data, which he believes relevant to the

decision of whether to accept Norman’s offer.

Weekly cost incurred by Saturn per guest:

Food $70

Electricity $6

Laundry, cleaning etc. $10

Casual staff to provide cleaning and other services for 30 weeks:

For 6 to 10 guests per week: $22,000

For 11 to 15 guests per week: $34,000

Other incremental costs if building occupied for 30 weeks:

Maintenance and security: $12,000

(a) From Saturn’s perspective what is the breakeven number of guests per week?

(b) Calculate the change to Saturn’s total profit if 10 guest rooms are sold per week throughout

the 30-week period.

(c) Calculate the change to Saturn’s total profit if 12 guest rooms are sold per week throughout

the 30-week period.

Case 3.

Val Dizzy Air is a hotel complex located in a well-known ski resort in Queenstown, New Zealand.

The town’s population doubles during the skiing months of June through to October, and hotel

activity also doubles during these months.

A new chief administration officer was hired one year ago as part of an initiative designed to

increase the hotel’s profitability. Among the new ideas introduced was responsibility accounting.

This was formally announced in a memorandum accompanying quarterly cost reports supplied to

department heads. Previously, cost data were presented to department heads infrequently.

Excerpts from the announcement and the first cost report received by the supervisor of laundry

services are presented below.

The new administrator constructed the annual budget for 20X3 and then divided it by four to

facilitate the provision of quarterly feedback to the operating managers. The administrator

considered establishing a budget according to an average of the prior three years’ costs, hoping

that installation of the system would reduce costs to this level. However, because of rapidly

increasing prices, 20X2 costs, less 3%, were finally chosen for the 20X3 budget. Activity levels

were set at the volume achieved in 20X2, which was approximately equal to the volume in each of

the previous two years.

(a) Describe two ways that the budget-setting exercise could be improved at the Val Dizzy Air

Hotel. Your answer can refer to technical as well as behavioral aspects of budget setting.

(b) Explain whether the report effectively communicates the level of efficiency of the laundry

department.

(c) Redesign the quarterly performance report so that it provides a more meaningful and fair

appraisal of the performance of the supervisor of the Laundry Department.

Case 4.

The LoveWallabies Hotel in Perth has 200 rooms and it is specialized in hosting visitors that are attending

rugby events (or sports in general) at the nearby stadium. The Revenue Manager of the hotel has just

received a request from a tour operator client who is indicating that he would be interests in booking 50

rooms for two consecutive nights in four months’ time, proposing a rate of 100$ per room night, so that each

room would be paid 200$ (100$ per night for two nights).

The hotel’s revenue management platform predicts that the hotel will reach an 88% occupancy rate for those

two nights if the 50- room grou booking is not accepted. This occupancy would be reached by selling rooms

to guests who would be paying a room rate of $150.

Knowing that the high occupancy is due to an upcoming rugby match between England and Australia taking

place in Perth during those two days, and that many rugby fans would be willing to sharing a room if rates

are discounted, the Revenue Manager wants to examine two options to offer to the tour operator:

1. Nightly room rates discounted to $95 if the tour operator can consolidate the group booking down to

40 rooms

2. Nightly room rates discounted to $90 if the tour operator can consolidate the group booking down to

25 rooms

Based on the information above, discuss the following:

(a) Prepare a financial analysis to demonstrate whether the two proposals would be justified from the

point of view of the hotel (i.e.: show if they increase revenue compared to the alternatives of either

rejecting the tour operator request, or accepting the request as it was originally made)

(b) Which is the best solution for the hotel?

(c) How much would each guest save if the tour operator accepts the offer of 40 rooms at 95$?

(d) How much would each guest save if the tour operator accepts the offer of 25 rooms at 90$?

Rubric: written assignment

Criteria Accomplished (A) Proficient (B) Partially proficient (C) Borderline (D) Fail (F)

Problem identification

The business issue has been correctly identified, with a competent and comprehensive explanation of key driving forces and considerations. Impact on company operations has been correctly identified. Thorough analysis of the issue is presented.

The student correctly identified the issue(s), taking into account a variety of environmental and contextual drivers. Key case information has been identified and analyzed.

The student correctly identified the case (issues), considering obvious environmental/contextual drivers. There is evidence of analysis, but it lacks depth.

The student correctly identified the issue(s) but analysis was weak. An absence of context – the work is basically descriptive with little analysis.

The student failed to correctly identify the issue(s); analysis was incorrect or too superficial to be of use; information was misinterpreted.

Information gathering

The student showed skill in gathering information and analyzing it for the purposes of filling the information gaps identified. Comprehensive and relevant.

Relevant information gaps were identified and additional relevant information was found to fill them. At least two different types of sources were used. The student demonstrates coherent criteria for selecting information but needs greater depth.

The student correctly identified at least one information gap and found relevant information, but which was limited in scope. Some evidence of sound criteria for selecting information but not consistent throughout. Needs expansion.

An information gap was identified and the student found additional information to fill it. However, this was limited in scope. Weak criteria for the selection of necessary information.

Information was taken at face value with no questioning of its relevance or value. Gaps in the information were not identified or were incorrect.

Conclusions The student evaluated, analyzed, synthesized all information provided to create a perceptive set of conclusions to support the decisions and solutions.

The student evaluated, analyzed and synthesized to create a conclusion(s) which support decisions and solutions.

The student reached conclusions, but they were limited and provided minimal direction for decision-making and solutions.

The conclusion was reasonable but lacked depth and would not be a basis for suitable strategy development.

The student formed a conclusion, but it was not reasonable. It was either unjustified, incorrect or unrelated to the case in hand.

Solutions The student used problem solving techniques to make thoughtful, justified decisions about difficult and conflicting issues. A realistic solution was chosen which would provide maximum benefit to the company. Alternative solutions were explored and ruled out.

The student used problem solving techniques to make appropriate decisions about complex issues. Relevant questions were asked and answered. A realistic solution was chosen. Alternatives were identified, explored and ruled out.

The student used problem- solving techniques to make appropriate decisions about simpler issues. The solution has limited benefit but does show understanding of implications of the decision. Alternatives were mentioned but not explored.

The student used problem solving techniques to make decisions about simpler issues but disregarded more complex issues. Implications of the decision were not considered. Alternatives were not offered.

The student formed a conclusion, but it was not reasonable. It was either unjustified, incorrect or unrelated to the case in hand.