Description

Olivia Clausen is a product analyst for Media Hub, a website that sells audio books, movies, TV shows, and other media around the world. Olivia is tracking sales for the year and asks for your help in projecting future sales and visualizing the sales data.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
the SAM website.
Shelly Cashman Excel 2019 | Module 5: SAM Project 1a
Media Hub
CONSOLIDATE WORKBOOK DATA
dina anyei
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from
the SAM website.
Media Hub Audio Books
United States
Downloads: January – April
Book Type
Adventure
Literary
Mystery
Romance
Science fantasy
Self-help
Total
Book Type
Adventure
Literary
Mystery
Romance
Science fiction
Self-help
Total
January
2,193
989
2,405
2,397
1,873
1,027
10,884
January
19,627.35
8,851.55
21,524.75
21,453.15
16,763.35
9,191.65
$ 97,411.80
February
2,282
1,036
2,788
2,655
1,869
1,095
11,725
March
2,130
798
2,239
3,244
1,904
1,124
11,439
Sales: January – April
February
March
20,423.90
19,063.50
9,272.20
7,142.10
24,952.60
20,039.05
23,762.25
29,033.80
16,727.55
17,040.80
9,800.25
10,059.80
$ 104,938.75 $ 102,379.05
April
2,215
803
3,011
2,769
1,874
1,006
11,678
April
19,824.25
7,186.85
26,948.45
24,782.55
16,772.30
9,003.70
$ 104,518.10
Date:
Price per book: $
8.95
Download Projections
Total
8,820
3,626
10,443
11,065
7,520
4,252
45,726
Total
78,939.00
32,452.70
93,464.85
99,031.75
67,304.00
38,055.40
$ 409,247.70
May
Increase downloads to 14,000 in December
11,432
Increase downloads by 3% per month
11,432
ojections
14,000
Media Hub Audio Books
Canada
Downloads: January – April
Book Type
Adventure
Literary
Mystery
Romance
Science fantasy
Self-help
Total
Book Type
Adventure
Literary
Mystery
Romance
Science fiction
Self-help
Total
January
February
1,082
836
1,288
1,055
879
795
5,935
968
689
1,205
1,097
773
707
5,439
$
January
9,631.60
6,855.55
11,989.75
10,915.15
7,691.35
7,034.65
54,118.05
$
March
1,120
708
1,339
1,144
904
824
6,039
Sales: January – April
February
March
10,765.90
11,144.00
8,318.20
7,044.60
12,815.60
13,323.05
10,497.25
11,382.80
8,746.05
8,994.80
7,910.25
8,198.80
59,053.25 $ 60,088.05
April
1,015
640
1,411
1,469
1,074
866
6,475
$
April
10,099.25
6,368.00
14,039.45
14,616.55
10,686.30
8,616.70
64,426.25
Date:
Price per book: $
9.95
Download Projections
Total
4,185
2,873
5,243
4,765
3,630
3,192
23,888
Total
41,640.75
28,586.35
52,167.85
47,411.75
36,118.50
31,760.40
$ 237,685.60
May
Increase downloads to 10,000 in December
5,972
Increase downloads by 3% per month
5,972
10,000
Media Hub Audio Books
Australia
Downloads: January – April
Book Type
Adventure
Literary
Mystery
Romance
Science fantasy
Self-help
Total
Book Type
Adventure
Literary
Mystery
Romance
Science fiction
Self-help
Total
January
652
189
805
497
712
227
3,082
January
7,139.40
2,069.55
8,814.75
5,442.15
7,796.40
2,485.65
$ 33,747.90
February
705
227
928
610
799
281
3,551
$
March
April
759
265
1,052
652
886
335
3,948
812
303
1,175
736
973
389
4,388
Sales: January – April
February
March
7,723.40
8,307.40
2,485.65
2,901.75
10,165.25
11,515.75
6,679.50
7,139.40
8,749.05
9,701.70
3,076.95
3,668.25
38,879.80 $ 43,234.25
April
8,891.40
3,317.85
12,866.25
8,059.20
10,654.35
4,259.55
48,048.60
$
Date:
Price per book: $
10.95
Download Projections
Total
2,928
984
3,960
2,495
3,370
1,232
14,969
Total
32,061.60
10,774.80
43,362.00
27,320.25
36,901.50
13,490.40
$ 163,910.55
May
Increase downloads to 5500 in December
3,742
Increase downloads by 3% per month
3,742
5,500
Media Hub Audio Books
All Locations
Downloads: January – April
Book Type
Adventure
Literary
Mystery
Romance
Science fantasy
Self-help
Total
Book Type
Adventure
Literary
Mystery
Romance
Science fiction
Self-help
Total
January
February


April


Sales: January – April
February
March
January
$
March

$

$
April

$

Total sales last year (Jan-Apr)
Total

Total
$
$
$
$
$
$
$
(Jan-Apr)

Shelly Cashman Excel 2019 | Module 5: SAM Project 1a
Media Hub
CONSOLIDATE WORKBOOK DATA
GETTING STARTED

Open the file SC_EX19_5a_FirstLastName_1.xlsx, available for download from the
SAM website.

Save the file as SC_EX19_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_EX19_5a_Sales.xlsx
With the file SC_EX19_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.
PROJECT STEPS
1.
Olivia Clausen is a product analyst for Media Hub, a website that sells audio books,
movies, TV shows, and other media around the world. Olivia is tracking sales for the
year and asks for your help in projecting future sales and visualizing the sales data.
The United States, Canada, and Australia worksheets have the same structure and
contain similar data. Group the United States, Canada, and Australia worksheets to
make changes to the three worksheets at the same time. The first change is to display
today’s date.
In cell H1 of the United States worksheet, enter a formula using the TODAY function to
display today’s date.
2.
Find the text “Science fantasy” and then change it to Science fiction to use the more
common term.
3.
Use the month name in cell H5 to fill the range I5:O5 with the names of the remaining
months in the year.
4.
Olivia wants to use the cell formatting in merged cell H6 in other places in the workbook.
Create and apply a cell style as follows:
5.
a.
Create a cell style named Subhead based on the formatting in merged cell H6.
b.
Apply the new Subhead cell style to cell H8.
Olivia thinks Media Hub has a good chance of increasing the number of audio book
downloads in the United States to 14,000 in December. For May, she estimates 11,432
downloads, which is the average number of monthly downloads from January to April.
Project the number of downloads in June to November by filling the series for the first
projection (range H7:O7) with a linear trend.
Shelly Cashman Excel 2019 | Module 5: SAM Project 1a
6.
Olivia also wants to know how the number of downloads would increase if customers
downloaded 3% more audio books each month from June to December.
Project the number of downloads in June to December for the second projection (range
H9:O9) based on a growth series using 1.03 as the step value.
7.
Olivia wants to consolidate the sales data in the United States, Canada, and Australia on
the All Locations worksheet.
Ungroup the worksheets, go to the All Locations worksheet, and then consolidate the
data as follows:
8.
9.
10.
a.
In cell B6, enter a formula using the SUM function and a 3D reference to total the
number of downloads of Adventure audio books in January (cell B6) in the United
States, Canada, and Australia.
b.
Copy the formula in cell B6 to calculate the number of downloads for the other
types of books and months (ranges B7:B11 and C6:E11), pasting the formula only.
c.
In cell B16, enter a formula using the SUM function and a 3D reference to total the
sales of Adventure audio books in January (cell B16) in the United States, Canada,
and Australia.
d.
Copy the formula in cell B16 to calculate the sales for the other types of books and
months (ranges B17:B21 and C16:E21), pasting the formula only.
Olivia wants to round the total sales values so that they are easier to remember.
a.
In cell B22, add the ROUNDUP function to display the total sales for January
rounded up to 0 decimal places.
b.
Fill the range C22:F22 with the formula in cell B22.
In cell F24, Olivia wants to display the total sales from the previous year for the same
period. This data is stored in another workbook. Insert the total as follows:
a.
Open the file Support_EX19_5a_Sales.xlsx.
b.
In cell F24 of Olivia’s workbook, insert a formula using an external reference to cell
F22 in the All Locations worksheet in the Support_EX19_5a_Sales.xlsx
workbook.
Olivia wants to visualize how the sales of each type of audio book contributed to the
total sales for January to April.
Create a chart as follows to illustrate this information:
a.
Create a 3-D pie chart that shows how each type of book (range A16:A21)
contributed to the total sales (range F16:F21).
b.
Move and resize the chart so that the upper-left corner is in cell B25 and the
lower-right corner is in cell F40.
Shelly Cashman Excel 2019 | Module 5: SAM Project 1a
11.
12.
13.
14.
Format the 3-D pie chart as follows to make it easier to interpret:
a.
Use Total Sales as the chart title.
b.
Add data labels to the chart on the Outside End of each slice.
c.
Display only the Category Name and Percentage values in the data labels.
d.
Change the number format of the data labels to Percentage with 1 decimal place.
e.
Explode the largest slice (Mystery audio books) by 8 percent.
f.
Remove the legend, which repeats information in the data labels.
Prepare for printing the All Locations worksheet as follows:
a.
Change the top and bottom margins to 0.25″.
b.
Select the range A1:F41 as the print area.
c.
Insert a footer that displays the Sheet Name in the center section.
Olivia wants to compare sales for January and April but doesn’t want to clutter the All
Locations worksheet with another chart. Create a new worksheet and chart for this
comparison as follows:
a.
Create a worksheet using Sales Comparison as the worksheet name.
b.
In cell A1, type Total Sales, and then resize column A to its best fit.
c.
In cell B1, enter a formula using a worksheet reference to display the total sales
amount from cell F22 on the All Locations worksheet, and then resize column B to
its best fit.
d.
Return to the All Locations worksheet, and then insert a Clustered Column chart
based on the nonadjacent ranges A15:B21 and E15:E21.
e.
Move the Clustered Column chart to the Sales Comparison worksheet.
f.
Position the Clustered Column chart so that its upper-left corner is in cell A3.
Olivia wants to use a copy of the United States worksheet as a template to track sales in
new locations.
Copy the worksheet as follows:
a.
Create a copy of the United States worksheet at the end of the workbook and
rename the copy using New Location as the worksheet name.
b.
On the New Location worksheet, clear only the contents from the cells containing
data, not formulas, in the range B6:E11 and cell H2.
Your workbook should look like the Final Figures on the following pages. Note: When opening
your file or the Graded Summary report for this Project, you may be prompted to update external
links. Select Don’t Update in the dialog box to open your file or view your report. Save your
changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to
submit your completed project.
Shelly Cashman Excel 2019 | Module 5: SAM Project 1a
Final Figure 1: United States Worksheet
Final Figure 2: Canada Worksheet
Shelly Cashman Excel 2019 | Module 5: SAM Project 1a
Final Figure 3: Australia Worksheet
Shelly Cashman Excel 2019 | Module 5: SAM Project 1a
Final Figure 4: All Locations Worksheet
Shelly Cashman Excel 2019 | Module 5: SAM Project 1a
Final Figure 5: Sales Comparison Worksheet
Final Figure 6: New Location Worksheet
Media Hub Audio Books
All Locations
Downloads: January – April
Book Type
Adventure
Literary
Mystery
Romance
Science fiction
Self-help
Total
Book Type
Adventure
Literary
Mystery
Romance
Science fiction
Self-help
Total
January
3,813
1,867
4,415
3,991
3,358
1,961
19,405
January
31,398.35
15,776.65
39,329.25
30,810.45
32,251.10
18,711.95
$ 168,278.00
February
4,069
2,099
5,004
4,320
3,547
2,171
21,211
March
4,009
1,771
4,630
5,040
3,694
2,283
21,426
Sales: January – April
February
March
33,913.20
33,514.90
18,076.05
15,088.45
44,933.45
44,877.85
33,939.00
41,556.00
34,222.65
35,737.30
20,787.45
21,926.85
$ 185,872.00 $ 192,702.00
All Locations
April
4,042
1,746
5,597
4,974
3,921
2,261
22,541
Total
15,933
7,483
19,646
18,325
14,520
8,676
84,583
April
33,814.90
14,872.70
49,854.15
41,458.30
38,112.95
21,879.95
$ 199,993.00
Total
$
132,641.35
$
63,813.85
$
178,994.70
$
147,763.75
$
140,324.00
$
83,306.20
$ 746,844.00

Purchase answer to see full
attachment

  
error: Content is protected !!