Expert answer:MS Excel project
Expert answer:
instructions_sc_ex16_5a.docx
sc_ex16_5a_igormbiso_1.xlsx
support_sc_ex16_5a_2019helmetsurvey.xlsx
Unformatted Attachment Preview
Shelly Cashman Excel 2016 | Module 5: SAM Project 1a
11th Gear Bike Rental
WORKING WITH MULTIPLE WORKSHEETS AND WORKBOOKS
GETTING STARTED
•
Open the file SC_EX16_5a_FirstLastName_1.xlsx, available for download
from the SAM website.
•
Save the file as SC_EX16_5a_FirstLastName_2.xlsx by changing the “1” to
a “2”.
o
•
To complete this SAM Project, you will also need to download and save the
following data files from the SAM website onto your computer:
o
•
If you do not see the .xlsx file extension in the Save As dialog box, do
not type it. The program will add the file extension for you automatically.
Support_SC_EX16_5a_2019HelmetSurvey.xlsx
With the file SC_EX16_5a_FirstLastName_2.xlsx still open, ensure that
your first and last name is displayed in cell B6 of the Documentation sheet.
o
If cell B6 does not display your name, delete the file and download a new
copy from the SAM website.
o
When opening your file or the Graded Summary Report, you may be
prompted to Update the links. Select Don’t Update in the dialog box.
PROJECT STEP
1.
Emelia Tapia is the owner of 11th Gear and Spoke Bike Rental. Each of her
location managers sent her their 2019 rental and revenue data. To make the
data easier to understand, Emelia wants to consolidate and standardize the
format of each location’s data.
Apply the Office theme to the workbook.
2.
Group the Santa Monica, Venice Beach, and Marina Del Rey worksheets. With
all three worksheets selected, make the following formatting changes to the
merged range A1:N1.
a.
Change the font to Arial.
b.
Change the font size to 18 pt.
c.
Change the font color to White, Background 1 (1st row, 1st column of
the Theme Colors palette).
Do not ungroup the worksheets.
3.
With the Santa Monica, Venice Beach, and Marina Del Rey worksheets still
grouped together, make the following updates:
Shelly Cashman Excel 2016 | Module 5: SAM Project 1a
a.
Apply the Heading 3 cell style to the range A2:N2.
b.
Apply the Accounting number format with zero decimal places and $ as
the symbol to the range B16:N16. (Hint: Depending on how you complete
this action, the number format may appear as Custom instead of
Accounting.)
c.
Apply the Comma number format with zero decimal places to the range
B17:N21. (Hint: Depending on how you complete this action, the number
format may appear as Custom.)
Do not ungroup the worksheets.
4.
Emelia notices two typos in the worksheets for each location.
With the Santa Monica, Venice Beach, and Marina Del Rey worksheets still
grouped together, make the following updates:
a.
In cell A8, edit the cell text to read Street (instead of Stret).
b.
In cell A9, edit the cell text to read Tricycle (instead of Trycycle).
Ungroup the worksheets.
5.
Now that a consistent format is applied to each location’s worksheet, Emelia
wants to create a template worksheet. She’ll use a copy of this template for
any new location that she opens, rather than formatting a new worksheet.
Select the Marina Del Rey worksheet and create a copy of it between the
Marina Del Rey and the All Locations worksheets. Update the worksheet as
described below:
a.
Rename the new worksheet using New Location as the name.
b.
Clear the contents (but not the formatting) in the merged range A2:N2.
c.
Clear the contents (but not the formatting) in the range B7:M12 and
B16:M21.
6.
Go to the All Locations worksheet. In cell B3, enter a formula using the TODAY
function to display the current date.
7.
Emelia wants to display the total number of monthly bike rentals from all of
11th Gear Bike Rental’s locations in one table.
In cell B7, enter a formula using the SUM function, 3-D references, and
grouped worksheets to total the values in cell B7 on the Santa Monica:Marina
Del Rey worksheets.
Copy the formula you created in cell B7 to the range B7:M12 without copying
any cell formatting. (Hint: Use the Paste Gallery.)
8.
Emelia created a 3-D pie chart showing how rental of each bicycle type
contributed to her total revenue in 2019. Now she wants to format it to make
the important information stand out better.
Resize and reposition the 3-D pie chart (with the title 2019 Total Revenue) so
that the upper-left corner is located within in cell C24 and the lower-right
corner is located within cell L44.
Shelly Cashman Excel 2016 | Module 5: SAM Project 1a
9.
Explode the slice of the 3-D pie chart representing the revenue from the
Tandem bike rentals by 25%.
10.
Modify the data labels of the 3-D pie chart as described below:
a.
Update the data labels to contain only the Category Name and the
Percentage values.
b.
Change the data label’s position to Outside End.
c.
Update the data label’s number format to display using the Percentage
number format with 1 decimal place.
11.
Add a header to the All Locations worksheet using the text For Internal
Distribution to the center header section.
12.
Using Header & Footer Elements, add a footer that displays the Current Date
in the left footer section and the Sheet Name in the center footer section.
13.
Change the margins of the All Locations worksheet so that the left and right
margins are set to 0.2. Do not change the top or bottom margins.
14.
Emelia is very concerned about safety and has conducted a study to determine
how many bike helmets were replaced at each location last year. She wants to
include the survey results in this spreadsheet.
Switch to the Helmets Per Location worksheet.
Open the Support_SC_EX16_5a_2019HelmetSurvey.xlsx workbook, and then
switch back to Helmets Per Location worksheet. Link the data to the Helmets
Per Location worksheet as described below:
15.
a.
In cell C3 of the Helmets Per Location worksheet, create a formula
without using a function that contains a relative reference to cell D2 of
the 2019 Damaged Helmet Survey worksheet in the
Support_SC_EX16_5a_2019HelmetSurvey.xlsx workbook.
b.
In the Helmets Per Location worksheet, copy the formula in cell C3 to the
range C4:C5 without copying the formatting.
c.
Close the Support_SC_EX16_5a_2019HelmetSurvey.xlsx workbook and
switch back to the Helmets Per Location worksheet.
Emelia now wishes to calculate how many helmets should be available at each
location, accounting for the percentage of damaged helmets returned at each
location. Emelia will need to use the ROUND function in her calculations, since
her customers won’t accept a fraction of a helmet.
In cell E3, enter a formula using the ROUND function to calculate the number
of helmets per location. The formula should multiply cell B3 (the max daily
rentals) by cell D3 (the total helmets required), and be rounded to 0 decimal
places. Copy the formula, but not the cell formatting, from cell E3 to the range
E4:E5. (Hint: Use the Paste gallery.)
Your workbook should look like the Final Figures on the following pages. Save your
changes, close the document, and then exit Excel. Follow the directions on the SAM
website to submit your completed project.
Shelly Cashman Excel 2016 | Module 5: SAM Project 1a
Final Figure 1: Santa Monica Worksheet
Final Figure 2: Venice Beach Worksheet
Shelly Cashman Excel 2016 | Module 5: SAM Project 1a
Final Figure 3: Marina Del Rey Worksheet
Final Figure 4: New Location Worksheet
Shelly Cashman Excel 2016 | Module 5: SAM Project 1a
Final Figure 5: All Locations Worksheet
Final Figure 6: Helmets Per Location Worksheet
Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from
website.
Shelly Cashman Excel 2016 | Module 5: SAM Project 1a
11th Gear Bike Rental
WORKING WITH MULTIPLE WORKSHEETS AND WORKBOOKS
Igor Mbiso
edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM
website.
11th Gear Bike Ren
Santa Monica
2019 Monthly Renta
Rental Type
Cruiser
Stret
Trycycle
Tandem
Youth
Kid’s Trailer
January
133
340
28
119
185
26
February
277
320
43
61
352
29
March
142
201
86
265
302
63
April
128
485
203
92
172
75
May
141
196
85
251
155
108
June
517
280
47
51
155
52
2019 Monthly Reven
Rental Type
Cruiser
Street
Tricycle
Tandem
Youth
Kid’s Trailer
Total
January
February
March
2793
5817
2982
7140
6720
4221
644
989
1978
5236
2684
11660
2405
4576
3926
520
580
1260
$
18,738 $
21,366 $
26,027 $
April
2688
10185
4669
4048
2236
1500
25,326 $
May
2961
4116
1955
11044
2015
2160
24,251 $
June
10857
5880
1081
2244
2015
1040
23,117
th Gear Bike Rentals
Santa Monica
2019 Monthly Rentals
July
216
73
165
126
189
74
August
340
100
63
91
40
87
September
438
164
22
173
85
121
October
147
508
113
168
306
41
November
249
75
29
79
192
63
December
92
280
166
80
220
22
019 Monthly Revenue
$
July
August
September
October
November
December
4536
7140
9198
3087
5229
1932
1533
2100
3444
10668
1575
5880
3795
1449
506
2599
667
3818
5544
4004
7612
7392
3476
3520
2457
520
1105
3978
2496
2860
1480
1740
2420
820
1260
440
19,345 $
16,953 $
24,285 $
28,544 $
14,703 $
18,450
Total
2820
3022
1050
1556
2353
761
$
Total
59220
63462
24150
68464
30589
15220
261,105
11th Gear Bike Rental
Venice Beach
2019 Monthly Renta
Rental Type
Cruiser
Stret
Trycycle
Tandem
Youth
Kid’s Trailer
January
446
133
70
178
76
80
February
152
207
35
109
53
169
March
565
425
33
153
58
43
April
306
160
146
106
88
128
May
271
250
184
162
204
186
June
459
220
142
92
179
173
April
6426
3360
3358
4664
1144
2560
21,512 $
May
5691
5250
4232
7128
2652
3720
28,673 $
2019 Monthly Reven
Rental Type
Cruiser
Street
Tricycle
Tandem
Youth
Kid’s Trailer
Total
January
February
March
9366
3192
11865
2793
4347
8925
1610
805
759
7832
4796
6732
988
689
754
1600
3380
860
$
24,189 $
17,209 $
29,895 $
June
9639
4620
3266
4048
2327
3460
27,360
11th Gear Bike Rentals
Venice Beach
2019 Monthly Rentals
July
368
253
140
172
359
160
August
179
423
48
191
144
194
September
296
105
94
134
61
53
October
369
89
86
246
151
161
November
252
184
35
94
113
141
December
261
498
69
127
71
47
019 Monthly Revenue
$
July
August
September
October
November
December
7728
3759
6216
7749
5292
5481
5313
8883
2205
1869
3864
10458
3220
1104
2162
1978
805
1587
7568
8404
5896
10824
4136
5588
4667
1872
793
1963
1469
923
3200
3880
1060
3220
2820
940
31,696 $
27,902 $
18,332 $
27,603 $
18,386 $
24,977
Total
3924
2947
1082
1764
1557
1535
$
Total
82404
61887
24886
77616
20241
30700
297,734
11th Gear Bike Renta
Marina Del Ray
2019 Monthly Renta
Rental Type
Cruiser
Stret
Trycycle
Tandem
Youth
Kid’s Trailer
January
268
367
58
97
174
138
February
447
263
70
93
383
90
March
200
396
56
171
168
51
April
284
263
146
100
150
146
May
218
348
61
108
447
59
June
107
71
168
244
263
63
April
5964
5523
3358
4400
1950
2920
24,115 $
May
4578
7308
1403
4752
5811
1180
25,032 $
2019 Monthly Reven
Rental Type
Cruiser
Street
Tricycle
Tandem
Youth
Kid’s Trailer
Total
January
February
March
5628
9387
4200
7707
5523
8316
1334
1610
1288
4268
4092
7524
2262
4979
2184
2760
1800
1020
$
23,959 $
27,391 $
24,532 $
June
2247
1491
3864
10736
3419
1260
23,017
11th Gear Bike Rentals
Marina Del Ray
2019 Monthly Rentals
July
124
316
66
102
130
136
August
196
85
15
48
174
108
September
230
199
159
85
65
40
October
377
408
71
216
58
86
November
216
515
72
164
287
93
December
121
467
98
34
46
66
019 Monthly Revenue
$
July
August
September
October
November
December
2604
4116
4830
7917
4536
2541
6636
1785
4179
8568
10815
9807
1518
345
3657
1633
1656
2254
4488
2112
3740
9504
7216
1496
1690
2262
845
754
3731
598
2720
2160
800
1720
1860
1320
19,656 $
12,780 $
18,051 $
30,096 $
29,814 $
18,016
Total
2788
3698
1040
1462
2345
1076
$
Total
58548
77658
23920
64328
30485
21520
276,459
11th Gear Bike Rentals
All Locations
Date Generated
2019 Monthly Rentals
Rental Type
Cruiser
Street
Tricycle
Tandem
Youth
Kid’s Trailer
January
February
March
April
May
June
July
August
September
October
November
December
2019 Monthly Revenue
Rental Type
Cruiser
Street
Tricycle
Tandem
Youth
Kid’s Trailer
Total
January
February
March
April
$
17,787 $
18,396 $
19,047 $
15,078 $
17,640
16,590
21,462
19,068
3,588
3,404
4,025
11,385
17,336
11,572
13,112
Kid’s25,916
Trailer,
5,655
10,244
6,864
5,330
Youth, $81,315$67,440
4,880
5,760
3,140
6,980
Cruiser,
$
66,886 $
65,966 $
80,454 $ $200,172
70,953 $
2019 Total Revenue
Tandem,
$210,408
Street, $203,007
Tricycle, $72,956
May
13,230 $
16,674
7,590
22,924
10,478
7,060
77,956 $
June
22,743 $
11,991
8,211
17,028
7,761
5,760
73,494 $
July
August
September
October
November
December
14,868 $
15,015 $
20,244 $
18,753 $
15,057 $
9,954
13,482
12,768
9,828
21,105
16,254
26,145
8,533
2,898
6,325
6,210
3,128
7,659
17,600
14,520
17,248
27,720
14,828
10,604
8,814
4,654
2,743
6,695
7,696
4,381
7,400
7,780
4,280
5,760
5,940
2,700
70,697 $
57,635 $
60,668 $
86,243 $
62,903 $
61,443
th Gear Bike Rentals
All Locations
2019 Monthly Rentals
Total
0
0
0
0
0
0
2019 Monthly Revenue
$
$
$
$
$
$
$
Total
200,172
203,007
72,956
210,408
81,315
67,440
835,298
11th Gear Bike Rentals
Location
Santa Monica
Venice Beach
Marina Del Rey
Max Daily Rentals
412
471
440
Damaged Helments (%)
ar Bike Rentals
Total Helmets Required (%)
100%
100%
100%
Helmets Per Location
Location
Santa Monica
Venice Beach
Marina Del Rey
Number of Helmets
450
450
450
Replaced Helmets
59
61
72
Damaged Helmets (%)
13.1%
13.6%
16.0%
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades