Excel Functions/Formula Pivots Chart

User Generated

YbirOht88

Business Finance

Description

Hello, Attached are the spreadsheet, the instructions and rubrics grading scale to complete this assignment.

Due Date: Dec 8 at 09:00AM (eastern time)

Please be sure to use Excel functions in the cells

Excel Fundamentals - Formulas--

Levels of Achievement: 20 (33.33%) points

14 (23.33%) points

10 (16.67%) points

4 (6.67%) points

0 (0%) points

Excel Fundamentals - Naming Cells--

Levels of Achievement: 5 (8.33%) points

4 (6.67%) points

3 (5%) points

2 (3.33%) points

0 (0%) points

Documentation - Accuracy--

Levels of Achievement: 15 (25%) points

10 (16.67%) points

7 (11.67%) points

5 (8.33%) points

0 (0%) points

Chart Formatting--

Levels of Achievement: 20 (33.33%) points

17 (28.33%) points

15 (25%) points

10 (16.67%) points

0 (0%) points

Unformatted Attachment Preview

ISYS184 Assignment – 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 ExcelA10_robertsj1503.xlsx. Problem 1 - Tasks Task 1 (pts 2) Task 2 (pts 2) Task 3 (pts 2) Task 4 (pts 2) Task 5 (pts 1) Task 6 (pts 5) Task 7 (pts 5) On the Occupancy worksheet, create named ranges for the weekday occupancy numbers (rows 1218) for each of the room types. 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. Assignment 7 - Instructions.docx Page|1 ISYS184 Task 8 (pts 15) Task 9 (pts 15) Task 10 (pts 1) Assignment – 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 (pts 1) Task 2 (pts 4) Task 3 (pts 2) Task 4 (pts 4) Task 5 (pts 7) Task 6 (pts 1) Task 7 (pts 7) On the Parameters worksheet , assign the range A12:C69 the name ProdCodes. Task 8 (pts 7) Task 9 (pts 3) Task 10 (pts 1) Task 11 (pts 2) Task 12 (pts 1) Delete the original pivot pie chart 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, 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. Turn off Subtotals and Grand Totals for your Pivot Table. 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. 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. Sort your pivot table so that products having the least quarterly sales totals appear at the top (i.e., first) in the Pivot Table. Assignment 7 - Instructions.docx Page|2 ISYS184 Task 13 (pts 7) Task 14 (pts 1) Task 15 (pts 2) Task 1 (pts 1) Assignment – Excel Charts & Pivot Tables Set your Pivot Table to be Pivot Style Medium 1. Use your finished Pivot Table to create Cluster Column Pivot Chart. Add an appropriate title to your chart 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. Assignment 7 - Instructions.docx Page|3 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
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hello buddy, attached is the complete work. Thank you

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
105
29%
$
169.00
$ 74,360.00
$ 21,564.40

Two Doubles
210
35%
$
189.00
$ 109,809.00
$ 38,433.15

Two Kings

One Double

Two Doubles

Two Kings

45
40
39
45
93
95
83
440
$62.86

56
50
51
52
135
139
98
581
$83.00

$
$
$

175
37%
199.00
84,575.00
31,292.75

45
35
32
39
98
97
79
425
$60.71

Deluxe Suite
140
48%
$
229.00
$ 79,692.00
$ 38,252.16

Super Suite

Deluxe Suite

Super Suite

35
32
30
35
75
79
62
348
$49.71

42
61%
$
279.00
$ 38,223.00
$ 23,316.03

15
12
11
16
31
30
22
137
$19.57

Room Occupancy Analysis Revenue Class ODB
=>
Code
Mon
One Double
Moving Average

45
45.00

Tue

Wed
40
42.50

Thu
39
41.33

Fri
45
42.25

93
52.40

Grand Villa Suite

$
$
$

28
68%
349.00
26,175.00
17,799.00

Comparison of Gross profit Vs Room Type

One Double

10% 13%
14%

Two Doubles

23%
22%

Grand Villa Suite
10
7
8
11
12
16
11
75
$10.71

Sat

Deluxe Suite

18%

Super Suite
Grand Villa Suite

One Double

Sun
95
59.50

Two Kings

83
62.86

100
90
80
70
60
50
40
30
20
10
0
Mon

Tue

Wed
One Double

Thu

Fri
Moving Average

Sat

One Double
Two Doubles
Two Kings
Deluxe Suite
Super Suite
Grand Villa Suite

70.00
60.00
50.00
40.00
30.00
20.00
10.00
0.00
Sun

Column Labels
Qtr1

Qtr2

319
128
510

Cluster Column Pivot Ch

736
220

660
500
686
70
280

250
2,745
380
1,950
0
680
800
560
97
1,930

Eastern

Western

Western

Baked Goods & Mixes
Beverages Candy
Canned
Canned
Condiments
Fruit
Dried Fruit
Jams,
& Nuts
Preserves
&Meat
Vegetables

Product Category Region

1,560

Western

Central

8,000
7,...


Anonymous
I use Studypool every time I need help studying, and it never disappoints.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags