Expert answer:
support_sc_ex16_7a_baseball.png
support_sc_ex16_7a_basketball.png
instructions_sc_ex16_7a.docx
sc_ex16_7a_igormbiso_1.xlsx
support_sc_ex16_7a_prime.docx
Unformatted Attachment Preview
Shelly Cashman Excel 2016 | Module 7: SAM Project 1a
Antioch Sports Center
IMPORTING DATA AND WORKING WITH SMARTART, IMAGES, AND SCREENSHOTS
GETTING STARTED
•
Open the file SC_EX16_7a_FirstLastName_1.xlsx, available for download
from the SAM website.
•
Save the file as SC_EX16_7a_FirstLastName_2.xlsx by changing the “1” to
a “2”.
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.
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
Support_SC_EX16_7a_Baseball.png
o
Support_SC_EX16_7a_Basketball.png
o
Support_SC_EX16_7a_Prime.docx
o
Support_SC_EX16_7a_Soccer.png
o
Support_SC_EX16_7a_Stillman.accdb
o
Support_SC_EX16_7a_Volleyball.png
o
Support_SC_EX16_7a_TonyBrown.txt
With the file SC_EX16_7a_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.
Lindsey Bates works part-time at the Antioch Sports Center, which is evaluating
whether to merge with another sports center in the area. One of Lindsey’s
assignments is to combine the data from each sports center into a single
workbook, format the data, and create graphics to make the data easier to
analyze.
Go to the Candidates worksheet. Insert a SmartArt graphic using the
Continuous Picture List layout (in the Picture section of the SmartArt Graphic
dialog box) to include an eye-catching graphic that identifies the merger
candidates.
Shelly Cashman Excel 2016 | Module 7: SAM Project 1a
2.
3.
4.
5.
6.
7.
8.
9.
Using the support files available for download from the SAM website, insert
pictures into the SmartArt graphic’s picture placeholders as follows:
a.
Insert Support_SC_EX16_7a_Baseball.png into the first shape.
b.
Insert Support_SC_EX16_7a_Basketball.png into the second shape.
c.
Insert Support_SC_EX16_7a_Soccer.png into the third shape.
Add text to the SmartArt graphic to identify the merger candidates as follows:
a.
Type Prime Sports into the first text placeholder.
b.
Type Tony Brown into the second text placeholder.
c.
Type Northside into the third text placeholder.
Change the format of the SmartArt graphic to coordinate with the Antioch
Sports Center logo as follows:
a.
Change the colors to Colorful Range – Accent Colors 5 to 6 (5th option
in the Colorful section of the Change Colors gallery).
b.
Apply the White Outline SmartArt style.
Update the SmartArt graphic as follows to include a fourth merger candidate:
a.
Add a new shape to the right side of the SmartArt graphic.
b.
In the new shape, insert the file
Support_SC_EX16_7a_Volleyball.png.
c.
Type Stillman Sports in the new shape below the volleyball picture.
Improve the appearance of the Candidates worksheet as follows:
a.
Move the SmartArt graphic so its upper-left corner is within cell B3.
b.
Resize the SmartArt graphic so its lower-right corner is within cell J15.
c.
Remove the worksheet gridlines from view.
Go to the Prime Sports worksheet, and then arrange the data as follows:
a.
Copy the range I3:N8 to the Office Clipboard.
b.
Paste the data from the range I3:N8 into cell B3, transposing the data
when you paste.
c.
Delete the contents of the range I3:N8.
The Prime Sports Center has provided sales data for the last five years in a
Word table. Insert the data into the Prime Sports worksheet as follows:
a.
Open the Support_SC_EX16_7a_Prime.docx Word document.
b.
Select the first six columns of the Financial Information table, copy the
data to the Office Clipboard, paste the Word data into cell B11 of the
Prime Sports worksheet, matching the destination formatting, and then
close the Word file.
Use the Format Painter to copy the formatting of the range B11:G16 to the
range B3:G8 in the Prime Sports worksheet.
Shelly Cashman Excel 2016 | Module 7: SAM Project 1a
10.
11.
12.
The Tony Brown Sports Center has provided sales data for the last five years in
a text file. Insert the data into the Tony Brown worksheet as follows:
a.
Go to the Tony Brown worksheet, and then import data from the
Support_SC_EX16_7a_TonyBrown.txt text file.
b.
Specify that the data in the text file has headers.
c.
Also specify that the data is delimited only with a comma.
d.
Import the data into cell B3.
Organize the historical data in the Tony Brown worksheet as follows:
a.
Use the Convert Text to Columns Wizard to convert the text in the
range B12:B15 to columns.
b.
Use only a space as the column delimiter. (Hint: Click OK to replace the
data if the warning message comes up.)
c.
Change the width of columns B:G to 12.0 characters.
Add totals to the historical data as follows:
In cell C16, create a formula using the SUM function that total the values in the
range C12:C15. Copy the formula in cell C16 to the range D16:G16.
13.
To make the Northside worksheet consistent with the other worksheets, go to
the Northside worksheet and replace all instances of the word Quarter with the
letter Q (Hint: you should make four replacements).
14.
To help analyze Northside’s historical data, select the range C12:G15 and apply
a Data Bars conditional formatting rule using Solid Fill Blue Data Bars.
15.
Make the bar chart in the Northside Revenue Chart worksheet more attractive
as follows:
a.
Change the chart style to Style 3.
b.
Format the data series to vary colors by point. (Hint: In the Format Data
Series task pane, display the Fill options.)
16.
The Stillman Sports Center provided sales data in an Access database. In cell
B11 of the Stillman worksheet, import data from the Sales table in the
Support_SC_EX16_7a_Stillman.accdb Access database file.
17.
To make the data in the Stillman worksheet consistent with the data for the
other sports centers, format the data and the worksheet as follows:
18.
a.
Convert the imported Access table to a range.
b.
Change the width of columns B:G to 12.0 characters.
c.
Apply the Accounting number format with zero decimal places and $ as
the symbol to the range C4:G8. (Hint: Depending on how you perform
this step, the number format may be displayed as Custom.)
d.
Use the Format Painter to copy the formatting of the range B2:G8 to
the range B10:G16.
To help analyze the Stillman Sports Center’s sales in 2020, create a chart as
follows:
Shelly Cashman Excel 2016 | Module 7: SAM Project 1a
19.
a.
For the range B3:F7, insert a Clustered Bar chart with each quarter as a
data series.
b.
Move the clustered bar chart to a new chart sheet named 2020 Stillman
Chart. (Hint: Don’t type the period.)
c.
Move the 2020 Stillman Chart worksheet after the Stillman worksheet.
To coordinate the new chart with the Northside revenue chart, make the
following changes to the 2020 Stillman chart:
a.
Change the chart style to Style 3.
b.
Add a Primary Horizontal Axis Title using Sales as the title.
c.
Use Stillman Sports Center as the chart title.
Your workbook should look like the Final Figures on the following pages. Save your
changes, close the workbook, and then exit Excel. Follow the directions on the SAM
website to submit your completed project.
Final Figure 1: Candidates Worksheet
Shelly Cashman Excel 2016 | Module 7: SAM Project 1a
Final Figure 2: Prime Sports Worksheet
Final Figure 3: Tony Brown Worksheet
Shelly Cashman Excel 2016 | Module 7: SAM Project 1a
Final Figure 4: Northside Worksheet
Final Figure 5: Northside Revenue Chart
Shelly Cashman Excel 2016 | Module 7: SAM Project 1a
Final Figure 6: Stillman Worksheet
Final Figure 7: 2020 Stillman Chart
Shelly Cashman Excel 2016 | Module 7: SAM Project 1a
Antioch Sports Center
IMPORTING DATA AND WORKING WITH SMARTART, IMAGES, AND
SCREENSHOTS
Author: Igor Mbiso
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.
ew copy of the
Merger Candidates
Prime Sports – 2020 Sales
Prime Sports – Historical Sales
Sales
Events
Leagues
Memberships
Programs
Total
Q1
$ 6,500
$ 7,000
$ 36,000
$ 42,000
$ 91,500
Q2
$ 7,750
$ 7,000
$ 39,000
$ 41,500
$ 95,250
$
$
$
$
$
Q3
7,450
8,550
45,550
49,800
111,350
Q4
$ 8,000
$ 9,750
$ 47,500
$ 50,750
$ 116,000
Total
$ 29,700
$ 32,300
########
########
########
Tony Brown – 2020 Sales
Tony Brown – Historical Sales
Sales
Q1 18550
Q2 25455
Q3 30650
Q4 44500
Total
2016
2017
$
$
$
$
2018
43,760
44,750
50,500
52,900
$
$
$
$
55,000
51,800
53,900
55,500
2019
$
$
$
$
2020
56,500
55,000
59,300
62,750
$
$
$
$
61,900
65,750
67,300
70,750
Northside Sports – 2020 Sales
Sales
Q1
Q2
Q3
Q4
Total
$
$
$
$
$
Events
7,150
8,085
6,116
7,425
28,776
$
$
$
$
$
Leagues
Memberships Programs
3,250 $
11,700 $
10,400
3,675 $
13,230 $
11,760
2,780 $
10,008 $
8,896
3,375 $
12,150 $
10,800
13,080 $
47,088 $
41,856
Total
$
32,500
$
36,750
$
27,800
$
33,750
$ 130,800
Northside Sports – Historical Sales
Sales
Quarter1
Quarter2
Quarter3
Quarter4
Totals
$
$
$
$
$
2016
26,500
22,700
21,600
27,700
98,500
2017
$
25,900
$
30,000
$
25,000
$
26,500
$ 107,400
2018
$
27,000
$
28,500
$
23,575
$
27,000
$ 106,075
2019
$
29,750
$
32,550
$
28,750
$
31,500
$ 122,550
2020
$
32,500
$
36,750
$
27,800
$
33,750
$ 130,800
Northside Sports Center
2020
2019
2018
2017
2016
$-
$20,000
$40,000
$60,000
$80,000
Sales
$100,000
$120,000
$140,000
Stillman Center – 2020 Sales
Sales
Q1
Q2
Q3
Q4
Total
Events Leagues Memberships Programs
11594
5270
18972
16864
14091
6405
23058
20496
12490.5
5677.5
20439
18168
13211
6005
21618
19216
51386.5 23357.5
84087
74744
Total
52700
64050
56775
60050
233575
Stillman Center – Historical Sales
Total
0
0
0
0
0
Prime Sports Center
Financial Information
Revenues
2016
2017
2018
2019
2020
5-yr
Growth
Q1
38,000
50,400
51,750
61,050
64,000
41%
Q2
47,800
51,750
60,500
65,500
65,500
27%
Q3
53,575
60,675
63,750
70,500
69,350
23%
Q4
59,850
61,750
67,775
75,500
82,500
27%
Total
201,241
226,592
245,793
274,569
283,370
29%
…
Purchase answer to see full
attachment
You will get a plagiarism-free paper and you can get an originality report upon request.
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.
Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.
Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.
Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.
Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.
Read more