Intro:Painted
Paradise Resort and Spa (PPRS) would like to review the occupancy and
profitability of the various rooms in the hotel with the data on the occupancy
worksheet. Further, PPRS is developing a VIP club that will provide rewards
based on points earned. The points are earned based on the guest’s usage of the
different areas within the resort. The hotel manager has asked you to analyze
the potential points program using the data on the Villa worksheet. After your
analysis, management may want to change some of the point levels and values.
Thus, all of your formulas must be updated appropriately if those values
change.
Startup: Download and open the Excel file corresponding to this assignment document. (attached)
__excel_4_datafile.xlsx
excel_4_fernandini.xlsx
excel_4.docx
Unformatted Attachment Preview
Number of Rooms
Margin
Average Room Rate
Revenue
Gross Profit
Actual Usage
Day
Mon
Tue
Wed
Thu
Fri
Sat
Sun
Total
Average Occupancy
One Double
Two Doubles
Two Kings Deluxe Suite
Super Suite
105
210
175
140
42
29%
35%
37%
48%
61%
$
169.00 $
189.00 $ 199.00 $
229.00 $
279.00
One Double
Two Doubles
45
40
39
45
93
95
83
Two Kings
56
50
51
52
135
139
98
Deluxe Suite
45
35
32
39
98
97
79
Super Suite
35
32
30
35
75
79
62
15
12
11
16
31
30
22
Room Occupancy Analysis Revenue Class ODB
=>
Code
Mon
Moving Average
Tue
Wed
Thu
Fri
Grand Villa Suite
28
68%
349.00
$
Grand Villa Suite
10
7
8
11
12
16
11
Sat
Sun
Order Date
4/25/2016
4/25/2016
4/25/2016
5/24/2016
4/14/2016
5/26/2016
5/26/2016
4/25/2016
4/25/2016
8/22/2016
8/22/2016
8/22/2016
4/3/2016
5/23/2016
5/23/2016
4/25/2016
5/6/2016
4/20/2016
4/3/2016
4/30/2016
6/2/2016
6/2/2016
5/22/2016
5/4/2016
8/13/2016
6/25/2016
6/25/2016
8/3/2016
5/25/2016
4/21/2016
4/21/2016
6/6/2016
3/24/2016
3/24/2016
3/24/2016
3/24/2016
5/24/2016
5/21/2016
6/5/2016
6/5/2016
5/24/2016
7/25/2016
7/25/2016
7/14/2016
6/28/2016
8/20/2016
Region
ZIPCode Product Code
Western
78073 NWTB-1
Western
78073 NWTB-43
Western
78073 NWTB-81
Western
78073 NWTCM-40
Western
52345 NWTSO-41
Western
52345 NWTCO-3
Western
52345 NWTS-8
Western
52345 NWTB-81
Western
52345 NWTP-56
Eastern
61870 NWTDFN-7
Eastern
61870 NWTDFN-51
Eastern
61870 NWTDFN-80
Eastern
61870 NWTBGM-19
Eastern
61870 NWTJP-6
Eastern
61870 NWTG-52
Eastern
61870 NWTP-56
Central
53689 NWTS-8
Central
53689 NWTB-34
Central
53689 NWTCA-48
Central
53689 NWTCA-48
Central
53689 NWTDFN-7
Central
53689 NWTDFN-51
Mountain
71201 NWTB-43
Western
56941 NWTBGM-19
Western
56941 NWTCA-48
Western
56941 NWTS-8
Western
56941 NWTBGM-19
Western
56941 NWTD-72
Western
56941 NWTCA-48
Mountain
71467 NWTP-57
Mountain
71467 NWTD-72
Mountain
71467 NWTSO-41
Central
69005 NWTB-81
Central
69005 NWTJP-6
Central
69005 NWTCO-4
Central
69005 NWTBGM-19
Central
69005 NWTDFN-74
Central
69005 NWTDFN-80
Eastern
45957 NWTDFN-80
Eastern
45957 NWTB-81
Eastern
45957 NWTS-8
Western
53984 NWTB-1
Western
53984 NWTB-43
Central
46850 NWTBGM-21
Central
46850 NWTCO-4
Eastern
72383 NWTO-5
Product Name
8/20/2016
8/20/2016
4/9/2016
8/5/2016
8/5/2016
4/21/2016
8/6/2016
8/6/2016
5/23/2016
6/8/2016
5/14/2016
4/3/2016
Eastern
Eastern
Eastern
Western
Western
Eastern
Eastern
Eastern
Eastern
Mountain
Mountain
Mountain
72383
72383
72383
47771
47771
70116
70116
70116
70116
53017
53017
53017
NWTSO-41
NWTCM-40
NWTJP-6
NWTB-34
NWTDFN-80
NWTB-43
NWTSO-41
NWTCM-40
NWTB-43
NWTCA-48
NWTB-34
NWTCFV-17
Product Category
Price
18
46
2.99
18.4
9.65
10
40
2.99
38
30
53
3.5
9.2
81
7
38
40
14
12.75
12.75
30
53
46
9.2
12.75
40
9.2
34.8
12.75
19.5
34.8
9.65
2.99
25
22
9.2
10
3.5
3.5
2.99
40
18
46
10
22
21.35
Quantity
25
25
25
40
200
50
3
0
0
10
10
10
20
40
40
10
17
300
10
40
30
30
300
30
100
25
25
40
40
100
50
10
200
10
10
10
20
15
20
50
20
15
20
20
30
25
9.65
18.4
25
14
3.5
46
9.65
18.4
46
12.75
14
39
30
30
90
100
30
300
50
50
5
10
87
40
Revenue Class Code
ODB
2DB
2KNG
DLXS
SSTE
GVSTE
Friendly Name
One Double
Two Doubles
Two Kings
Deluxe Suite
Super Suite
Grand Villa Suite
ProductCode
NWTB-1
NWTB-43
NWTB-81
NWTCM-40
NWTSO-41
NWTCO-3
NWTS-8
NWTB-81
NWTP-56
NWTDFN-7
NWTDFN-51
NWTDFN-80
NWTBGM-19
NWTJP-6
NWTG-52
NWTP-56
NWTS-8
NWTB-34
NWTCA-48
NWTCA-48
NWTDFN-7
NWTDFN-51
NWTB-43
NWTBGM-19
NWTCA-48
NWTS-8
NWTBGM-19
NWTD-72
NWTCA-48
NWTP-57
NWTD-72
NWTSO-41
NWTB-81
NWTJP-6
NWTCO-4
NWTBGM-19
ProductName
Chai
Coffee
Green Tea
Crab Meat
Clam Chowder
Syrup
Curry Sauce
Green Tea
Gnocchi
Dried Pears
Dried Apples
Dried Plums
Chocolate Biscuits Mix
Marmalade
Long Grain Rice
Gnocchi
Curry Sauce
Beer
Chocolate
Chocolate
Dried Pears
Dried Apples
Coffee
Chocolate Biscuits Mix
Chocolate
Curry Sauce
Chocolate Biscuits Mix
Mozzarella
Chocolate
Ravioli
Mozzarella
Clam Chowder
Green Tea
Boysenberry Spread
Cajun Seasoning
Chocolate Biscuits Mix
ProductCategory
Beverages
Beverages
Beverages
Canned Meat
Soups
Condiments
Sauces
Beverages
Pasta
Dried Fruit & Nuts
Dried Fruit & Nuts
Dried Fruit & Nuts
Baked Goods & Mixes
Jams, Preserves
Grains
Pasta
Sauces
Beverages
Candy
Candy
Dried Fruit & Nuts
Dried Fruit & Nuts
Beverages
Baked Goods & Mixes
Candy
Sauces
Baked Goods & Mixes
Baked Goods & Mixes
Candy
Pasta
Baked Goods & Mixes
Soups
Beverages
Jams, Preserves
Condiments
Baked Goods & Mixes
NWTDFN-74
NWTDFN-80
NWTDFN-80
NWTB-81
NWTS-8
NWTB-1
NWTB-43
NWTBGM-21
NWTCO-4
NWTO-5
NWTSO-41
NWTCM-40
NWTJP-6
NWTB-34
NWTDFN-80
NWTB-43
NWTSO-41
NWTCM-40
NWTB-43
NWTCA-48
NWTB-34
NWTCFV-17
Almonds
Dried Plums
Dried Plums
Green Tea
Curry Sauce
Chai
Coffee
Scones
Cajun Seasoning
Olive Oil
Clam Chowder
Crab Meat
Boysenberry Spread
Beer
Dried Plums
Coffee
Clam Chowder
Crab Meat
Coffee
Chocolate
Beer
Fruit Cocktail
Dried Fruit & Nuts
Dried Fruit & Nuts
Dried Fruit & Nuts
Beverages
Sauces
Beverages
Beverages
Baked Goods & Mixes
Condiments
Oil
Soups
Canned Meat
Jams, Preserves
Beverages
Dried Fruit & Nuts
Beverages
Soups
Canned Meat
Beverages
Candy
Beverages
Canned Fruit & Vegetables
ISYS184
Excel 4: Excel Charts & Pivot Tables
Hotel Occupancy Problem
Introduction
Painted Paradise Resort and Spa (PPRS) would like to review the occupancy and profitability of the various
rooms in the hotel with the data on the occupancy worksheet. Further, PPRS is developing a VIP club that will
provide rewards based on points earned. The points are earned based on the guest’s usage of the different
areas within the resort. The hotel manager has asked you to analyze the potential points program using the data
on the Villa worksheet. After your analysis, management may want to change some of the point levels and
values. Thus, all of your formulas must be updated appropriately if those values change.
Startup
Download and open the Excel file corresponding to this assignment document. Using Save As…, save the
workbook with a name that incorporates the name part of your Duquesne email address. For example, I might
name my workbook Excel4_Gray.xlsx.
Problem 1 – Tasks
Task 1
Task 2
Task 3
Task 4
Task 5
Task 6
Task 7
On the Occupancy worksheet, create named ranges for each of the room types for Monday through
Sunday (Rows 12-18 for each room type). Do this in an efficient manner!
In rows 19 and 20, calculate the total occupancy and average occupancy for each room type. Format
the average occupancy as Currency.
For each room type, calculate the revenue (row 6) for each room type as the total occupancy × the
average room rate.
For each room type, calculate the gross profit for each room type as the revenue × the margin rate.
On the Parameters worksheet, the range A1:B7 contains some conversion codes provided by the IT
Department. The codes are used in the corporate database to uniquely identify room types. Assign
the range A2:B7 the name RevCodes using the name box.
In A25 of the Occupancy worksheet, author a lookup formula to translate the revenue code
appearing in C23 in to the corresponding room type. Be sure to use the RevCodes named range
where appropriate. Once correctly completed, the daily room occupancy rates for revenue code ODB
will appear in B25:H25.
Beginning in B26 author a formula to calculate a daily moving average for the occupancy data
showing in B25:H25. The moving average for Mon will just be the average of a single day – i.e., Mon.
The moving average for Tue will be the average of Mon and Tue. The moving average for Wed will be
the average of Mon, Tue and Wed and so on. Creative use of a mixed cell reference range will allow
you to author a single formula in B26 that can be copied across to column G. Hints: (1) a cell range
can contain a single cell and (2) the average of a single number is that number. For example, I can
calculate Monday’s occupancy daily moving average as AVERAGE(B25:B25). Format the results as a
number with 2 decimal places.
Page|1
ISYS184
Task 8
Task 9
Task 10
Excel 4: Excel Charts & Pivot Tables
Create a pie chart showing a comparison of Gross Profit in row 7 by room type in row 2.
Add data labels with the percentages showing but not the values
For the largest piece of pie only, pull it away from the rest of the pie so it is emphasized.
Add or adjust a chart title above the chart that says “Comparison of Gross Profit by Room Type”.
Resize the chart to have a height of 3″ and width of 6″
Using range A24:H26, create a Combo Clustered Column–Line chart showing a comparison of the
occupancy performance of single room categories against the moving average occupancy
performance.
The Moving Average should be the line chart type plotted against the secondary axis.
Change the line chart Chart Type to be a Line with Markers.
Ensure the secondary vertical axis displays labels
Ensure the X-axis displays the room types (One Double, etc.)
Change the chart title to be a dynamic label by selecting the chart title container, clicking the formula
bar, then type an = followed by clicking cell A25.
Problem 2 – Tasks
Task 1
On the Parameters worksheet , assign the range A12:C69 the name ProdCodes.
Task 2
Task 5
On the ProductData worksheet, complete the Product Name and Product Category columns using a
lookup approach. Use the ProdCodes named range in your solution where appropriate.
In the 1st unused column in ProductData, add a column titled Total and calculate the total sale
amount as Price x Quantity.
Using all of the product data, insert pivot table in a new worksheet and rename worksheet
ProductPivot.
Select the appropriate Pivot Table fields to show average total sales by region.
Task 6
Turn off Subtotals and Grand Totals for your Pivot Table.
Task 7
Create a Pivot Chart (pie) from your Pivot table
Change the chart title so that is adequately describes the results
Add Data Callouts to you chart
Copy the chart and paste the copied chart as a picture. Move the picture so that the upper-left
corner is in cell A25.
Task 8
Delete the original pivot pie chart
Task 9
Change your Pivot Table to show total sales (a SUM) first by product category and then by order
date. Show dates in the COLUMNS and the category in the ROWS of your table.
Group the dates such that the sums are calculated for and display Quarters only. I.e., Qtr1, Qtr2,
etc.
Format the total sales values as comma separated numbers with no decimal places.
Task 3
Task 4
Task 10
Task 11
Task 12
Sort your pivot table so that products having the least quarterly sales totals appear at the top (i.e.,
first) in the Pivot Table.
Page|2
ISYS184
Excel 4: Excel Charts & Pivot Tables
Task 13
Set your Pivot Table to be Pivot Style Medium 1.
Task 14
Use your finished Pivot Table to create Cluster Column Pivot Chart.
Task 15
Add an appropriate title to your chart
Task 1
Filter your chart to show results for Qtr1 and Qtr2 only.
Save your workbook and completely exit/quit Excel. Upload your completed Excel workbook to the Blackboard
assignment item.
Page|3
ISYS184
Excel 4: Excel Charts & Pivot Tables
Page|4

Purchase answer to see full
attachment

Are you having trouble with the above assignment or one similar?

To date, 239 students have ordered this same assignment from us and received 100% original work. We can do the same for you!

We offers 100% original papers that are written from scratch.We also have a team of editors who check each paper for plagiarism before it is sent to you.

Click this “order now” button to see free Cost Breakdown!