A1 Business and Technical College Spring Palm Hotel Hospitality Analysis The assignment included making excel chart and writing the analysis (executive sum

A1 Business and Technical College Spring Palm Hotel Hospitality Analysis The assignment included making excel chart and writing the analysis (executive summary) on the excel. I have already finished making excel charts. Please write the analysis based on the instructions(final dashboard assignment) and data given in excel charts. The place to write analysis is on the right of dashboard, you will see when you open the excel file that attached. The characters must over 300. The position is general manager and for the responsibilities please search online like the role of general manager of hotel. ADR,Occupancy, and Ppublic Awareness
Indexes
Net Operating Income chart
0
1
Average Daily rate
($)
150
100
50
0
Public Awareness
Occupancy %
2
3
-50,000
-100,000
-150,000
-200,000
Occupancy of Rooms Sold
breakeven chart for Q4
1000000
800000
600000
400000
200000
0
Compass Hotel
Tabahi Hotel
Grand Hotel
Spring Palm Hotel
0
Zion Hotel
renenue
fixed expenses
total expenses
Guest Satisfaction Surve
Occupancy % for each Hotel
60
43
42
41
40
39
38
37
40
20
0
Compass
Hotel
Tabahi
Hotel
Zion Hotel
Grand
Hotel
5
Spring Palm
Hotel
Occupancy %
ADR vs OCC% for Q4
120
4
100
80
60
40
20
0
1
2
3
Average Daily rate ($)
4
Occupancy %
5
Snapshot of Q4
OCC:43.08%
ADR:96.19
Room Sold: 4900
Revenue:484177
Net Loss:-127600
Sum of Total Revenue ($)
Row LabelsSum of Total Revenue ($)
Compass Hotel813691
Grand Hotel 586799
Spring Palm Hotel
741624
Tabahi Hotel 602674
Zion Hotel 556760
Grand Total 3301548
Hotel name
Compass Hotel
1000000
Grand Hotel
800000
Spring Palm Hotel
600000
Tabahi Hotel
400000
Zion Hotel
200000
0
Compass Hotel Grand Hotel
Hotel name
come chart
Percentage of Rooms, Food&Beverage and Other
Operated Departments
4
Other Operated
Departments
5%
Rooms
32%
Food &
Beverage
63%
Rooms
Food & Beverage
Other Operated Departments
Spring Palm
Hotel
4900
total expenses
est Satisfaction Survey(%)
1
43
42
41
40
39
38
37
2
3
Total
Total
Grand Hotel
Spring Palm
Hotel
Tabahi Hotel
Zion Hotel
EXcutive summary
TEAM1
TEAM2
Compass Hotel
Average
TEAM3
Tabahi Hotel
Rank
Average
TEAM4
Zion Hotel
Rank
Average
Grand Hotel
Rank
Average
OPERATIONS
REVPAR ($)
Gross Operating Profit
(IBFC %)
Rooms Market Share %
(Transient Business)
Relative Market Share
%
OWNER
ROCE % – Pretax
profit/Capital Employed
Guest Satisfaction
Survey(%)
Staff Relationship
survey (%)
OVERALL RANKING
45.92
1
31.6
4
30.64
5
33.3
8.55
3
11.2
2
13.76
1
-6.42
7.45
4.57
4.97
1
27.5
4
16.8
18.3
2
-2.08
3
-1.99
1
-3.02
43
1
41
3
40
4
39
70
3
83
1
67
4
62
1
TEAM2
Tabahi
Hotel
3
TEAM3
4
TEAM4
TEAM5
Spring Palm
Zion Hotel Grand Hotel
Hotel
FINANCES:
Total Revenue ($)
813,691
602,674
556,760
586,799
741,624
Room Revenue ($)
Gross Operating
Profit,GOP ($)
Gross Operating
Profit (GOP %)
Fixed Property &
Equipment ($)
Working Capital ($)
538,738
366,863
358,542
389,346
484,177
69,600
67,525
76,584
-37,657
-27,948
8.6
11.2
13.8
-6.4
-3.8
11,558,711
11,558,711
11,558,711
11,558,711
11,558,711
-236,278
-293,690
-213,047
-327,671
-602,079
19,056
16,981
26,040
-88,201
-78,492
-232,476
-234,551
-225,492
-339,733
-330,024
8,750,000
8,750,000
8,750,000
8,750,000
8,750,000
30
30
30
30
30
-232,476
-234,551
-225,492
-339,733
-330,024
Average Daily rate ($)
96.73
75.25
98.11
110.83
96.19
Occupancy %
47.47
42
31.24
30.05
43.08
44.6
30.45
37.17
32.83
45.79
Profit before Taxes ($)
Owners’ Equity ($)
Income Tax Rate (%)
Income after Taxes ($)
OPERATIONS:
Public Awareness
15.1
-2.05
TEAM1
Compass
Hotel
EBITDA (earnings before
interest, taxes & depn)($)
4.1
3
Hotel Quality Index
Annual Staff Turnover
(%)
Rooms Available
Number of Rooms Sold
12.27
12.92
11.85
11.65
12.2
71.72
33.91
43.33
71.38
51.11
11,375
11,375
11,375
11,375
11,375
5,400
4,777
3,553
3,418
4,900
TEAM5
Grand Hotel
Spring Palm Hotel
Rank
Average
Rank
3
41.43
2
5
-3.77
4
6.04
5
2
22.3
5
-3.01
4
5
42
2
5
75
2
5
2
ADR vs OCC% for Q4
200
100
0
1
2
3
Average Daily rate ($)
4
Occupancy %
5
Occupancy % for each Hotel
60
40
20
0
Compass
Hotel
Tabahi Hotel
Zion Hotel
Grand Hotel
Occupancy %
Spring Palm
Hotel
Occupancy of Rooms Sold
Compass Hotel
Tabahi Hotel
Zion Hotel
Grand Hotel
Spring Palm Hotel
ADR,Occupancy, and Ppublic Awareness Indexes
Average Daily rate ($)
120
100
80
60
40
20
0
Public Awareness
Occupancy %
Current Period
Year-To-Date
Current Year
$
Last Year
%
$
Current Year
%
$
%
Revenue
Rooms
484,177
65.3
401,950
63.3 2,220,257
65.2
245,059
33
222,449
35 1,125,049
33
12,388
1.7
10,729
1.7
59,154
1.7
741,624
100
635,128
100 3,404,460
100
127,221
17.2
133,323
251,064
33.9
164,325
21,212
2.9
6,968
399,497
53.9
304,616
138,057
18.6
88,853
14
514,725
15.1
83,850
11.3
72,000
11.3
373,450
11
96,186
13
83,020
13.1
443,381
13
51,982
7
48,910
7.7
228,241
6.7
Total Undistributed
Operating Expenses
370,075
49.9
292,783
46.1 1,559,797
45.8
Income Before Fixed
Charges
-27,948
-3.8
37,729
5.9
208,870
6.1
Fixed Charges
50,544
6.8
49,985
7.9
201,058
5.9
Interest Expense
46,873
6.3
39,394
6.2
195,187
5.7
Total Fixed Charges
97,417
13.1
89,379
14.1
396,245
11.6
Net Operating Income
-125,365
-16.9
-51,650
-8.1
-187,375
-5.5
Food & Beverage
Other Operated
Departments
Total Revenue
Departmental Expenses
Rooms
Food & Beverage
Other Operated
Departments
Total Departmental
Expenses
556,272
16.3
25.9 1,001,744
21
29.4
1.1
77,777
2.3
48 1,635,793
48
Undistributed Operating
Expenses
Administrative & General
Sales & Marketing
Property Operation &
Maintenance
Utilities
Fixed Charges
Net Operating Income chart
0
-20,000
-40,000
1
2
3
4
-40,000
-60,000
-80,000
-100,000
-120,000
-140,000
-160,000
-180,000
-200,000
Last Year
$
%
1,743,252
61.6
1,037,049
36.7
48,781
1.7
2,829,082
100
571,242
20.2
752,933
Revenue
fixed expenses
total expenses
741,624
420,619
820,116
26.6
vc%
cm%
48%
52.00%
31,681
1.1
besr
808882.7
1,355,856
47.9

beu
adr
8409.218
96.19
360,844
12.8
249,400
8.8
414,143
14.6
212,457
7.5
1,236,844
43.7
236,382
8.4
room sold
0
4900
renenue
0
741,624
fixed expenses
467,492
467,492
total expenses
467,492
866,989
breakeven chart for Q4
1000000
199,940
7.1
164,042
5.8
363,982
12.9
-127,600
-4.5
500000
0
0
4900
renenue
fixed expenses
Percentage of Rooms, Food&Beverage and Other
Operated Departments
Other Operated
Departments
5%
total expenses
Departments
5%
Rooms
32%
Food & Beverage
63%
Rooms
Food & Beverage
Other Operated Departments
4900
Hotel
name
Compass
Hotel
Tabahi
Hotel
Total
Revenue
($)
813,691
602,674
Zion Hotel
556,760
Grand
Hotel
586,799
Spring
Palm Hotel
741,624
Sum of Total Revenue ($)
Row Labels
Compass Hotel
Grand Hotel
Spring Palm Hotel
Tabahi Hotel
Zion Hotel
Grand Total
Sum of Total Revenue ($)
813691
586799
741624
602674
556760
3301548
900000
800000
700000
600000
500000
400000
300000
200000
100000
0
Compass Hotel
Hotel name
Sum of Total Revenue ($)
Hotel name
Total
Compass Hotel
Grand Hotel
Spring Palm Hotel
Tabahi Hotel
Total
Compass Hotel Grand Hotel
Hotel name
Spring Palm
Hotel
Tabahi Hotel
Zion Hotel
Zion Hotel
Final Dashboard Assignment
– 30% of total grade – due anytime before your class time in Week 15
Please use Brightspace to upload your completed Dashboard file
All dashboards should be interactive and the worksheets they are part of are in the submitted
workbook.
The dashboard should include but is not limited to the following related to your assigned season:
1. Executive Summary top left-hand corner with the following elements
 Your name, position and what you were responsible for
 An analysis as to your decisions for your assigned quarter
***This analysis should examine why you made your decisions, what you thought would
happen, what did happen and why you think it happened that way.
Who was your main competition?
This is why journaling and taking good notes will help you write this with ease.
You will also require having the following charts on your dashboard
1. Net income line graph covering the year
2. An XY Scatter graph positioning map for each quarter; Either ADR vs. RDE% or
ADR vs. OCC%
3. A pivot chart and slicer of your choice; only for your quarter
4. A radar chart for each quarter showing at least one competitor that season in
whatever you want. Index may be a good idea
5. A break-even analysis, three series line graph, with a text box explaining the breakeven revenue rooms sold
6. A column chart displaying your weekday and weekend occupancy sold for your
quarter
7. A graph of your own personal choice demonstrating your use of a different chart you
have learned either in class, on your own, or by using Chandoo.org
You will also be required to show examples of skills that you acquired ;
a) Slicer
b) Charts
c) Tables-Pivot tables
d) Conditional Formatting
e) Sparklines
f) Any interactive elements suchas Data Validation or Vlookup skill
The following Dashboard tips must be used:
1. Use a chart that clearly shows the data (ie XY to compare two variables and Line or Column
to compare trends such as sales)
2. Remove all gradients, backgrounds, grid lines, borders, and 3D effects from charts for clarity
3. Use Sparklines and conditional formatting to show trends
4. Make the charts small enough to be able to view well on one screen
5. More important data can be shown using a larger font
6. Align all elements for consistency

Purchase answer to see full
attachment