Information Tools For Business/EXCEL

YO16XLCH04GRADERPS2AS_-_Express_13_Instructions x
Office 2016 – myitlab:grader – Instructions Excel Project

YO16_XL_CH04_GRADER_PS2_AS – Express 1.3

Project Description:
Thomas Reynolds is the corporate buyer for vehicles put into service by Express Car Rental. Tom has the option to buy several lots of vehicles from another rental agency that is downsizing. He wants to get a better idea of the number of rentals nationwide by vehicle type. The corporate accountant has forwarded Tom a worksheet containing rental data for last year. Tom wants to use this summarized data but feels that embedding charts will provide a quicker analysis in a more visual manner for the chief financial officer at their meeting next week.

Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Open the file named e02ch04Express.xlsx. Save the file with the name e02ch04Express_LastFirst, replacing LastFirst with your name. 0.000
2 Use the cells A5:A10 and O5:O10 on the AnnualData worksheet to create a 3-D Pie chart of the number of annual rentals for the six car types. Reposition the chart so the upper left corner is inside cell H12.
13.000
3 On the 3-D pie chart, change the title to Annual Rentals. Change the font of the title to Arial Black, 16 pt, and bold. 8.000
4 Add data labels on the pie chart to include the category name and percentage only. Position the label information on the outside end of the chart. Change the font size of the labels to 8 and apply bold. Remove the legend. Explode the slice of the chart that represents the auto type with the lowest percentage of annual rentals by 20%.
12.000
5 Click the clustered column chart located below the monthly data. Change the chart type to Line.
10.000
6 Increase the data used in the line chart to include the months of July, August, and September. Switch the row and column data so that the months July to December are represented on the x-axis.
9.000
7 Change the chart style to Style 6. On the line chart, add primary major vertical gridlines. Change the chart title to Rentals by Auto Type for July to December.
9.000
8 Create a 3-D Clustered Column chart using the cells A5:10 and H5:H10 for the Semi-Annual Total for all auto types on the AnnualData worksheet. The primary horizontal axis should be the auto types.
15.000
9 Move the 3-D column chart from the AnnualData worksheet to a chart sheet, and then name that worksheet SemiAnnualReport.
9.000
10 If necessary, add the chart title Semi-Annual Total to the chart. Bold the title. Adjust the 3-D Rotation of the chart to have a X rotation of 30 and a Y rotation of 20. Add Data Labels to the chart. Change the size of the data labels to 14 and then bold.
8.000
11 Reposition the SemiAnnualReport worksheet after the AnnualData worksheet.
5.000
12 Insert the File Name in the left footer section of AnnualData worksheet.
2.000
13 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0.000
Total Points 100.000

Updated: 05/02/2017 1 Current_Instruction x

Nyarko_e02ch04Express.xlsx

AnnualData

Express Car Rental

Annual National Rental Data/ Vehicles Rented by Type

Created by Thomas Reynolds

Auto Type
Jan
Feb
Mar
Apr
May
Jun
Semi-Annual Total
Jul
Aug
Sep
Oct
Nov
Dec
Annual Total

Green Collection
300
250
366
522
477
699
2614
899
862
566
855
588
288
6672

Compact/Midsize
500
700
890
755
899
788
4532
566
425
655
547
689
588
8002

Full-size/Standard
250
355
652
553
650
800
3260
788
1056
852
544
455
977
7932

SUV/Minivan
150
260
555
456
556
652
2629
1230
1855
658
578
355
1155
8460

Sports car
200
350
355
233
155
132
1425
355
523
458
598
168
198
3725

Relic
50
122
224
126
98
155
775
189
166
137
199
99
66
1631

Oct Green Collection Compact/Midsize Full-size/Standard SUV/Minivan Sports car Relic 855 547 544 578 598 199 Nov Green Collection Compact/Midsize Full-size/Standard SUV/Minivan Sports car Relic 588 689 455 355 168 99 De c Green Collection Compact/Midsize Full-size/Standard SUV/Minivan Sports car Relic 288 588 977 1155 198 66

YO16XLCH04GRADERPCHW_-_Spa_Sales_14_Instructions x
Office 2016 – myitlab:grader – Instructions Excel Project

YO16_XL_CH04_GRADER_PC_HW – Spa Sales 1.4

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 spreadsheet with some data and would like you to develop some charts. Visualizing the data with charts will provide knowledge about the spa for decision-making purposes.

Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Open the file named e02ch04_grader_pc_SpaSales.xlsx. Save the file with the name e02ch04_grader_pc_SpaSales_LastFirst, replacing LastFirst with your last and first name. 0.000
2 On the TableUse worksheet, add data labels to the pie chart to show only the percentage of each section. Apply Chart Style 7 to the chart.
3.000
3 On the ProductData worksheet, create a Clustered Column chart using the data in A2:B12. Modify the Chart Area property so that the chart will move but not resize with cells. Move the chart to a new Chart Sheet, named TotalNumberOfMassages.
5.000
4 Modify the color of the TotalNumberOfMassages worksheet tab to Blue, Accent 1.
3.000
5 On the Revenue worksheet, create a Pie chart using the data in A1:E2. Move and resize the chart so that the top left corner is in cell G4 the bottom right corner is in cell M17.
5.000
6 On the Revenue worksheet, create a Line chart using the data in A4:E34. Move and resize the chart so that the top left corner in cell G20 and the bottom right corner is in cell M34. Edit the chart title to read Revenue by Massage Type for June.
6.000
7 On the RedesignProject worksheet, create a Stacked Bar chart using the data in A3:A8 and C3:D8. Move the chart so the top left corner is in cell A10. Edit the chart title to read Project Redesign Status.
5.000
8 On the Survey worksheet, create a Scatter chart using the data in A2:B53. Move the chart so that the top left corner is in cell E2. Modify the minimum bound of the vertical axis to 65. Edit the chart title to read Relationship Between Age and Temperature.
5.000
9 On the ProductData worksheet, create a Stacked Area chart using the data in A16:I19. Move and resize the chart so that the top left corner is in cell D2 and the bottom right corner is in cell I14. Edit the chart title to read Massage Types Over 8 Week Period.
5.000
10 On the SpaSales worksheet, create a Clustered Column – Line Combo chart using the data in A2:C13. Create a secondary axis for Profit. Move the chart so that the top left corner is in cell E1. Edit the chart title to read, Quantity Sold and Profit.
6.000
11 On the SpaSales worksheet, in cell A14, type December, in cell B14, type 165, and in C14, type 1701.
3.000
12 On the SpaSales worksheet, adjust the data used to create the chart to include the new row of data. Change the chart type of the Profit data series to an Area chart. Apply Style 6 Chart Style and Layout 9.
3.000
13 Edit the value in cell B3 to be 125 and then edit the value in cell C3 to be 650.
2.000
14 On the TotalNumberOfMassages chart worksheet, apply the Chart Layout 4 to the chart. Insert the e02ch04TurquoiseOasis picture. Adjust the shape height of the picture to 0.9. Apply the Subtle Effect – Orange, Accent 2 to the columns in the chart.
5.000
15 Insert a Rounded Rectangle shape onto the TotalNumberOfMassages chart worksheet, just under the logo, with the text Number of Massage Services by Type. Adjust the height to 0.8 and the width to 2.2. Apply the Subtle Effect – Orange, Accent 2 Shape Style to the shape. Adjust the font size of the text inside the shape to 16.
Note, depending upon the version of Office being used, the shape name may be Rectangle Rounded Corners. 4.000
16 On the RedesignProject worksheet tab, edit the chart title to be a cell reference to A1.
2.000
17 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.
3.000
18 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.
3.000
19 On the RedesignProject worksheet, modify the bounds of the horizontal axis to have a minimum of 20.
3.000
20 On the Revenue worksheet, add Primary Major Vertical gridlines to the Line chart.
3.000
21 On the Survey worksheet, add a Linear Trendline to the Scatter chart. Modify the trendline to be a solid line rather than a dashed line.
3.000
22 On the Revenue worksheet, change the colors of the Pie chart to be Color 4. Change the Shape Fill of the chart area to Gold, Accent 4, Lighter 60%.
Note, depending upon the version of Office used, the color name may be Colorful Palette 4.
3.000
23 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, Text 2, Dark Upward Diagonal, Shadow to the title.
Note, depending upon the version of Office used, the WordArt Style name may be Fill: White; Outline: Orange, Accent color 2; Hard Shadow: Orange, Accent color 2. 4.000
24 On the Revenue worksheet, modify the Pie chart so that the Hot Stone slice of the pie is exploded away from the center, to show emphasis.
0.000
25 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 30. Apply a Cool Slant Top Bevel 3-D Format effect to the chart area.
4.000
26 On the HairProducts worksheet, create Line Sparklines into A3:A7, using the data in cells C3:N7. Apply the Sparkline Style Accent 2, Darker 50% to the Sparklines.
Note, depending upon the version of Office being used, the Sparkline Style name may be Brown, Sparkline Style Accent 2, Darker 50%. 5.000
27 On the HairProducts worksheet, apply Conditional Formatting, in the form of Gradient Fill, Green Data Bars to the range C10:C14.
3.000
28 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 -45 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.
4.000
29 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0.000
Total Points 100.000

Updated: 01/03/2017 1 Current_Instruction x

Nyarko_e02ch04_grader_pc_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
14

Shiatsu
19

Thai
24

Hot Stone
32

Sports
41

Reflexology
47

Deep Tissue
74

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
1
1
1
1
1

Shiatsu
1
4
4
1
1
3
2
3

Thai
4
2
2
2
5
4
3
2

Hot Stone
1
1
2
5
6
6
5
6

Sports
6
5
5
7
8
3
3
4

Reflexology
5
7
10
8
4
4
5
4

Deep Tissue
15
3
2
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/18
498
238
523
546

6/2/18
468
275
567
572

6/3/18
466
184
501
505

6/4/18
215
78
462
438

6/5/18
254
231
523
295

6/6/18
325
25
524
257

6/7/18
412
238
487
345

6/8/18
422
184
498
617

6/9/18
396
256
567
665

6/10/18
321
88
420
321

6/11/18
286
140
479
372

6/12/18
327
134
582
358

6/13/18
325
123
511
368

6/14/18
415
207
468
459

6/15/18
468
199
566
621

6/16/18
467
236
526
676

6/17/18
434
195
534
536

6/18/18
215
34
367
412

6/19/18
245
134
419
501

6/20/18
400
89
426
370

6/21/18
326
242
479
401

6/22/18
452
297
564
485

6/23/18
500
176
560
443

6/24/18
457
242
524
451

6/25/18
368
239
343
471

6/26/18
265
162
365
413

6/27/18
319
125
384
421

6/28/18
389
192
438
390

6/29/18
417
279
523
423

6/30/18
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

2017 Product Sales

Month
Quantity
Profit

January
90
$ 405

February
50
$ 450

March
80
$ 500

April
95
$ 700

May
98
$ 689

June
120
$ 842

July
90
$ 835

August
50
$ 450

September
101
$ 500

October
115
$ 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