Business Analytics Help !

profileDeMario34
McDonald_YO19_Excel_Ch04_Prepare_Spa_Sales1.zip

YO19_Excel_Ch04_Prepare_Spa_Sales_Instructions.docx

Grader - Instructions Excel 2019 Project

YO19_Excel_Ch04_Prepare_Spa_Sales

Project Description:

The Turquoise Oasis Spa managers, Irene Kai and Meda Rodate, are pleased with your work and would like to see you continue to improve the spa spreadsheets. They want to use charts to learn more about the spa. Meda has given you a workbook with some data and would like you to create various charts that accurately display the data. Visualizing the data with charts will provide knowledge about the spa for decision-making purposes.

Steps to Perform:

Step

Instructions

Points Possible

1

Start Excel. Download and open the file named Excel_Ch04_Prepare_SpaSales.xlsx. Grader has automatically added your last name to the start of the filename. Save the file to the location where you are storing your files.

0

2

To quickly identify the value of the data series, you will modify the existing pie chart on the TableUse worksheet by adding data labels. On the TableUse worksheet, add data labels to the pie chart to show only the percentage of each section. Apply Chart Style 3 to the chart.

0.2

3

You would like to use this pie chart in future presentations and would like to be able to easily isolate and print the chart. To facilitate this, you will move the chart to a chart sheet. On the TableUse worksheet, move the pie chart to a new sheet named DistributionChart On the TableUse worksheet, enter 7 in cell B6. View the changes on the DistributionChart worksheet.

0.3

4

To aid in the analysis of the data, you would like the data on the ProductData worksheet to be organized and presented in an effective graphical manner to compare the total number of massages given over an eight-week period. On the ProductData worksheet, create a Clustered Column chart using the data in cell range A2:B12. Move the chart to a new Chart Sheet, named TotalMassagesChart

0.4

5

Next, you will create a simple pie chart that shows the proportion of total revenue each of four different massage types earned for the spa in the month of June. On the Revenue worksheet, create a Pie chart using the data in cell range A1:E2. Move and resize the chart so that the top-left corner is in cell I3 and the bottom-right corner is in cell O18. **The book instructions say over cell O16. Use what is in this instructions file.

0.3

6

You will create a line chart displaying daily revenue by massage type in June. Each day will appear as a point on the line that is created and each massage type to be a separate line on the chart. On the Revenue worksheet, create a Line chart using the data in cell range A4:E34. Move and resize the chart so that the top left corner in cell I20 and the bottom right corner is in cell O34. Enter the Chart Title June Revenue by Massage Type

0.4

7

Next, you will create a stacked bar chart that will assist in visualizing the status of a project to redesign the massage therapy rooms. On the RedesignProject worksheet, create a Stacked Bar chart using the data in cell ranges A3:A8 and C3:D8. Move the chart so the top-left corner is in cell A10. Enter the chart title Project Redesign Status

0.3

8

You have data from a survey showing the requested temperature of the room used for massages and the age of the customer. This data may reveal important information about what temperature is typically requested by customers in different age groups. On the Survey worksheet, create a Scatter chart using the data in cell range A2:B53. Move the chart so that the top-left corner is in cell E2. Enter the chart title Age and Desired Room Temperature

0.4

9

You want to create a chart to further understand the differences in the types of massages given over the past eight weeks at the spa. You are particularly interested in Pregnancy, Shiatsu, Thai, and Hot Stone massages. On the ProductData worksheet, create a Stacked Area chart using the data in cell range A16:I20. Move and resize the chart so that the top-left corner is in cell D2 and the bottom-right corner is in cell I12. Enter the chart title Massage Types Over 8 Week Period

0.5

10

Next, you will analyze the quantities of spa products sold from the prior year’s sales and compare the result to the profits over the same time span. On the SpaSales worksheet, create a Clustered Column - Line on Secondary Axis Combo chart using the data in cell range A2:C13. Enter the chart title Profit by Quantity Sold

0.5

11

You were just provided with the December data for quantity and profit for the spa so you want to add that information to the chart on the SpaSales sheet. On the SpaSales worksheet, in cell A14, type December In cell B14, type 178 In C14, type 1901

0.3

12

Your chart does not include the recently added December data. You need to add the December data to the chart. On the SpaSales worksheet, adjust the data used to create the chart to include the data entered in cell range A14:C14. Change the chart type of the Profit data series to an Area chart. Apply Chart Style 6. Apply Quick Layout 9.

0.6

13

In cell B6, enter 112. **The book shows 122 as the value to be entered in B6. Use 112 as provided in these instructions. The combination chart now reflects the new value.

0.1

14

You will be using the TotalMassagesChart sheet in a variety of presentations and would like it to contain the Turquoise Oasis Spa logo. On the TotalMassagesChart worksheet, insert the e02ch04TurquoiseOasis.jpg picture. Adjust the Shape Height of the picture to 0.9 Apply Quick Layout 4 style. Apply the Gradient Fill - Gold, Accent 4, No Outline Shape Style to the data series (columns).

0.5

15

Insert a Rectangle: Rounded Corners shape onto the TotalMassagesChart worksheet, just under the logo, with the text Number of Massage Services by Type Adjust the height of the shape to 0.8 and the width to 2.2 Apply the Gradient Fill – Gold, Accent 4, No Outline Shape Style to the shape. Adjust the font size of the text inside the shape to 16 Apply the Black, Text 1 font color.

0.4

16

Chart Titles can be added within the chart, or they can reference cells on the spreadsheet for easy updating. You want to reference a cell for the chart title on the RedesignProject worksheet. On the RedesignProject worksheet, edit the chart title to be a cell reference to A1.

0.2

17

You want to alter the appearance of the combination chart. On the SpaSales worksheet, delete the horizontal axis title box. Edit the vertical axis title to read, Quantity Add a secondary vertical axis title with the text Profit

0.3

18

When the parts are labeled on the chart, the legend is not needed and can be removed. On the Revenue worksheet, edit the line chart so that the legend appears to the right. On the Pie chart, add Data Callout style data labels and remove the legend.

0.5

19

The pie chart will be used in presentations. Therefore, you will enhance the visual appeal of the chart for the presentations. On the Revenue worksheet, change the colors of the Pie chart to be Colorful Palette 4. Change the Shape Fill of the chart area to Gold, Accent 4, Lighter 60%.

0.5

20

To continue improving the appearance and readability of the pie chart, you will adjust the chart's title font style and font size. On the Revenue worksheet, increase the font size of the Pie chart title to 16 and apply the Bold style. Apply the WordArt Style, Pattern Fill: White; Dark Upward Diagonal Stripe; Shadow to the title.

0

21

You want to emphasize the hot stone massage type because it represents the least amount of revenue in the data series. On the Revenue worksheet, modify the Pie chart so that the Hot Stone slice of the pie is exploded at 20% away from the center.

0.5

22

You want to enhance the pie chart by adding 3-D effect and 3-D rotation. On the Revenue worksheet, change the Pie chart to be a 3-D Pie chart. Rotate the chart by changing the Y Rotation to 50 and the Perspective to 35

0.6

23

On the RedesignProject worksheet, you created a stacked bar chart. Since the data for the chart does not go below 20, you want to adjust the axis bounds to improve the appearance of the chart. On the RedesignProject worksheet, modify the bounds of the horizontal axis to have a minimum of 20

0.3

24

You also want to modify the Scatter chart on the Survey worksheet to improve the chart's value. You will adjust the minimum bounds and add a trendline. Adding a trendline for the scatter chart on the Survey worksheet data may help to confirm the hypothesis that older customers desire a warmer room than younger customers. On the Survey worksheet, set the minimum bounds of the scatter chart vertical axis to 65 Add a Linear Trendline to the scatter chart. Modify the trendline to be a solid line rather than a dashed line.

0.6

25

Finally, to help you better examine sales of hair products at the spa, you will add sparklines adjacent to the data to emphasize the trend in products over time. On the HairProducts worksheet, create Line Sparklines in cell range A3:A7, using the data in cell range C3:N7. Apply the Blue, Sparkline Style Accent 1, Darker 25% to the Sparklines.

0.6

26

You would like a small visual cue added to a list of profits by hair product type so you will add data bars. On the HairProducts worksheet, in cell range C10:C14, apply Gradient Fill, Blue Data Bars.

0.4

27

On the HairProducts worksheet, modify the Clustered Bar chart by changing the chart type to a Line chart. Switch the row/column so that the time data is on the x-axis. Modify the angle of the Horizontal axis to have a custom angle -40 degrees. Edit the chart title to read Hair Products Sold Over the Last 12 Months Filter the chart so that only Shampoo and Conditioner items are visible.

0.3

28

Save and close Excel_Ch04_Prepare_SpaSales.xlsx. Exit Excel. Submit the file as directed.

0

Total Points

10

Created On: 05/18/2020 1 YO19_Excel_CH04_Prepare - Spa Sales 1.2

McDonald_Excel_Ch04_Prepare_SpaSales.xlsx

TableUse

Portable Table Use
Employee Times Used
Christy 15
Kendra 9
Jason 16
Pat 5

Massage Distribution

Times Used Christy Kendra Jason Pat 15 9 16 5

ProductData

Massage Totals
Type of Massage Total Number of Massages
Pregnancy 15
Shiatsu 21
Thai 24
Hot Stone 34
Sports 41
Reflexology 47
Deep Tissue 81
Swedish 91
Back 84
Aromatherapy 87
Massages over 8 week period
Type of Massage Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8
Pregnancy 2 3 4 2 1 1 1 1
Shiatsu 3 4 4 1 1 3 2 3
Thai 4 2 2 2 5 4 3 2
Hot Stone 1 1 2 5 6 6 7 6
Sports 6 5 5 7 8 3 3 4
Reflexology 5 7 10 8 4 4 5 4
Deep Tissue 15 5 7 9 8 11 12 14
Swedish 9 7 15 14 11 10 13 12
Back 12 15 6 5 13 11 10 12
Aromatherapy 10 15 7 8 12 14 10 11

Revenue

Sports Hot Stone Deep Tissue Back
Total Revenue $ 11,264.00 $ 5,606.00 $ 14,606.00 $ 13,618.00
Date Sports Hot Stone Deep Tissue Back
6/1/22 498 238 523 546
6/2/22 468 275 567 572
6/3/22 466 184 501 505
6/4/22 215 78 462 438
6/5/22 254 231 523 295
6/6/22 325 25 524 257
6/7/22 412 238 487 345
6/8/22 422 184 498 617
6/9/22 396 256 567 665
6/10/22 321 88 420 321
6/11/22 286 140 479 372
6/12/22 327 134 582 358
6/13/22 325 123 511 368
6/14/22 415 207 468 459
6/15/22 468 199 566 621
6/16/22 467 236 526 676
6/17/22 434 195 534 536
6/18/22 215 34 367 412
6/19/22 245 134 419 501
6/20/22 400 89 426 370
6/21/22 326 242 479 401
6/22/22 452 297 564 485
6/23/22 500 176 560 443
6/24/22 457 242 524 451
6/25/22 368 239 343 471
6/26/22 265 162 365 413
6/27/22 319 125 384 421
6/28/22 389 192 438 390
6/29/22 417 279 523 423
6/30/22 412 364 476 486

RedesignProject

Massage Room Redesign Project
Hours
Manager Completed Remaining
Project Planning Irene 75 15
Gathering Bids Irene 73 29
Design Meda 98 11
Installation Irene 45 20
Painting & Decorating Meda 23 32

Survey

Client Survey Data
Age Temp
Author: Temperature Requested by the Client
Rating Massage Type
40 70 52 Hot Stone
61 70 81 Back
42 72 62 Sports
44 72 64 Sports
50 73 98 Reflexology
33 73 68 Pregnancy
65 75 98 Hot Stone
62 73 96 Aromatherapy
46 74 99 Sports
43 75 79 Sports
60 75 84 Reflexology
36 76 89 Swedish
26 74 76 Pregnancy
69 77 94 Shiatsu
63 77 65 Reflexology
71 80 75 Back
71 76 82 Deep Tissue
64 79 65 Swedish
66 80 83 Thai
30 71 93 Swedish
34 75 77 Deep Tissue
32 72 65 Pregnancy
67 79 94 Back
26 73 82 Back
44 71 86 Reflexology
39 71 75 Thai
45 72 98 Aromatherapy
76 78 83 Deep Tissue
45 74 67 Deep Tissue
30 72 88 Aromatherapy
29 74 83 Deep Tissue
55 78 95 Shiatsu
59 81 93 Back
76 85 89 Sports
63 79 91 Thai
22 73 89 Aromatherapy
31 75 94 Back
37 73 90 Back
48 76 85 Sports
81 77 92 Shiatsu
63 76 81 Deep Tissue
42 75 80 Sports
45 76 94 Back
75 78 90 Shiatsu
53 75 90 Reflexology
44 75 75 Deep Tissue
60 76 79 Thai
26 72 87 Sports
42 74 86 Reflexology
33 74 97 Aromatherapy
36 74 96 Back

SpaSales

2021 Product Sales
Month Quantity Profit
January 190 $ 1,405
February 150 $ 1,450
March 111 $ 1,500
April 95 $ 1,700
May 109 $ 1,689
June 120 $ 1,842
July 123 $ 1,835
August 98 $ 1,450
September 101 $ 1,500
October 115 $ 1,700
November 156 $ 1,652

HairProducts

Hair Products Sold Over the Last 12 Months
Units Sold January February March April May June July August September October November December
Shampoo 25 23 23 41 20 17 30 27 34 19 10 14
Conditioner 14 24 44 48 46 38 42 39 22 26 34 25
Treatment 38 31 39 67 50 44 47 35 54 21 45 44
Styling 20 50 14 19 18 22 15 24 33 11 25 45
For Men 25 26 26 25 14 13 40 35 12 10 50 14
Profit
Shampoo $ 1,090.50
Conditioner $ 1,786.50
Treatment $ 1,236.00
Styling $ 651.20
For Men $ 551.00

Hair Product Sales

January Shampoo Conditioner Treatment Styling For Men 25 14 38 20 25 February Shampoo Conditioner Treatment Styling For Men 23 24 31 50 26 March Shampoo Conditioner Treatment Styling For Men 23 44 39 14 26 April Shampoo Conditioner Treatment Styling For Men 41 48 67 19 25 May Shampoo Conditioner Treatment Styling For Men 20 46 50 18 14 June Shampoo Conditioner Treatment Styling For Men 17 38 44 22 13 July Shampoo Conditioner Treatment Styling For Men 30 42 47 15 40 August Shampoo Conditioner Treatment Styling For Men 27 39 35 24 35 September Shampoo Conditioner Treatment Styling For Men 34 22 54 33 12 October Shampoo Conditioner Treatment Styling For Men 19 26 21 11 10 November Shampoo Conditioner Treatment Styling For Men 10 34 45 25 50 December Shampoo Conditioner Treatment Styling For Men 14 25 44 45 14

e02ch04TurquoiseOasis.jpg