i need help/support on this project…………………………………………………………………………………..Shelly Cashman Excel 2019 | Module 8: SAM Project 1a
Eastern Mobile
ANALYZE DATA WITH CHARTS AND PIVOTTABLES
GETTING STARTED

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

Save the file as SC_EX19_8a_FirstLastName_2.xlsx by changing the “1” to a “2”.
o

With the file SC_EX19_8a_FirstLastName_2.xlsx still open, ensure that your first
and last name is displayed in cell B6 of the Documentation sheet.
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.
If cell B6 does not display your name, delete the file and download a new copy
from the SAM website.
To complete this project, you need to add the Analysis ToolPak. If Data Analysis is not
listed under the Analysis section of the Data ribbon, click the File tab, click Options, and
then click the Add-Ins category. [Mac – Click the Tools menu, and then click Excel Addins.] In the Manage box, select Excel Add-ins and then click Go. In the Add-Ins box,
check the Analysis ToolPak check box, and then click OK to install. [Mac – In the AddIns available box, select the Analysis ToolPak check box, and then click OK.] If
Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it.
PROJECT STEPS
1.
Marina Andros works in the Financials Department of Eastern Mobile, a communications
company in Alexandria, Virginia. She is using an Excel workbook to analyze the
company’s recent financial performance, and asks for your help in creating advanced
types of charts and PivotTables to provide an overview of mobile phone plans,
customers, and orders. Go to the Plans worksheet, which contains a column chart in the
range F18:K31 showing sales of each plan type in Quarter 2. Marina wants to create a
similar chart showing the Quarter 1 data.
Provide a chart showing Quarter 1 data as follows:
2.
a.
Copy the Q2 Sales chart.
b.
Paste the chart in cell A18.
c.
Select the copied chart and drag the blue outline from the range G4:G16 to the
range F4:F16 to change the data shown in the chart.
The Ads table in the range I4:K16 compares the amount of advertising purchased in the
previous year and the number of mobile phone plans sold. Marina asks you to create a
chart that shows the relationship between the advertising spent and the plans sold.
a.
Insert a Scatter chart that shows the relationship between the advertising amount
(range J4:J16) and the number of plans sold (range K4:K16).
b.
Resize and position the Scatter chart so that it covers the range L3:R16.
c.
Use Ad Amount and Plans Sold as the chart title.
Shelly Cashman Excel 2019 | Module 8: SAM Project 1a
3.
Marina wants to analyze the relationship between advertising spent and the plans sold.
Add a Linear trendline to the scatter chart.
4.
Go to the Customers worksheet, which contains customer details in a table named
Customers. Marina wants to display the payments customers made in each of five
states where Eastern Mobile does business.
Insert a recommended PivotTable based on the Customers table as follows:
5.
a.
Insert the Sum of Payment by State recommended PivotTable. [Mac Hint: Use
the State field in the Rows area, the Payments field in the Values area, and show
grand totals for rows and columns.]
b.
Use Payments by State Pivot as the name of the new worksheet.
c.
Apply the Light Orange, Pivot Style Medium 12 style to the PivotTable.
d.
Add a second copy of the Payment field to the Values area of the Field List, and
then change its summary function to Average so that Marina can compare the
average payments to the totals.
e.
Change the Number format of the two value fields to Currency with 0 decimal
places and the $ symbol.
f.
Use Total Payments as the column heading in cell B3, and use Average
Payment as the column heading in cell C3.
Insert a PivotChart based on the new PivotTable as follows to help Marina visualize the
data:
a.
Insert a Combo PivotChart based on the Payments by State PivotTable.
b.
Display the Total Payments as a Clustered Column chart and the Average Payment
as a Line chart.
c.
Include a secondary axis for the Average Payment data.
d.
Hide the Field List so that you can format the value axis of the chart.
e.
Change the maximum bounds for the value axis on the left to 1,400.
f.
Change the PivotChart colors to Monochromatic Palette 4.
g.
Resize and position the chart so that it covers the range D3:J17.
h.
Display the Field List again.
Shelly Cashman Excel 2019 | Module 8: SAM Project 1a
6.
7.
Marina also wants to insert a PivotTable that includes other Customer information so
that she can analyze Family plan customer data. Return to the Customers worksheet,
and then create a PivotTable based on the Customers table as follows:
a.
Place the PivotTable on a new worksheet, and then use Customers Pivot as the
name of the worksheet.
b.
Display the order location as column headings.
c.
Display the state field and then the customer field as row headings.
d.
Display the years as the values.
e.
Display the plan type as a filter, and then filter the PivotTable to display customer
information for Family plans only.
f.
Hide the field headers to reduce clutter in the PivotTable.
Go to the Orders worksheet. The Deliveries table in the range A4:C27 lists the number
of days between a customer order and the delivery of a mobile device. Marina wants to
know how many orders were delivered in the periods listed in the range E5:E8.
Insert a histogram as follows to provide this information for Marina:
8.
9.
a.
Use the Data Analysis tool to create a histogram.
b.
Use the number of days until delivery (range C5:C27) as the input range.
c.
Use the Bin list (range E5:E8) as the bin range.
d.
Use cell A29 as the output range.
e.
Show a cumulative percentage and chart output in the histogram.
Modify the Histogram chart as follows to incorporate it into the worksheet and display
the data clearly:
a.
Resize and position the Histogram chart so that it covers the range A35:G48.
b.
Display the legend at the bottom of the chart to allow more room for the data.
The Order_Summary table in the range G4:J15 shows the sales for three categories of
mobile device orders, with each category divided into types and then into models.
Marina wants to display these hierarchies of information in a chart.
Insert a Sunburst chart to display the hierarchies for Marina as follows:
10.
a.
Insert a Sunburst chart based on the mobile device order data in the range
G4:J15.
b.
Resize and position the chart so that it covers the range G16:K34.
c.
Use Mobile Device Orders as the chart title.
Go to the Customers by Plan worksheet, which contains a PivotTable showing the
payments, customers, and lines for each plan. Marina does not need to display the
number of lines, but does want to show subtotals in a different layout since Eastern
Mobile is running a special on unlimited plans with three or more lines. Marina wants to
know where the current customers of these plans reside so that she can encourage sales
representatives to promote the plans in those states.
Shelly Cashman Excel 2019 | Module 8: SAM Project 1a
Modify the PivotTable for Marina as follows:
11.
a.
Remove the Sum of # of Lines field from the Values area.
b.
Change the report layout to Tabular Form.
Go to the Sales Pivot worksheet, which contains a PivotTable comparing sales for plans
in Quarter 1 and Quarter 2. Marina wants to know the difference between the two
quarters.
Reorder the fields and add a calculated field to the PivotTable as follows:
12.
a.
Change the order of the fields in the Values area to display the Total of Q1 Sales
before the Total of Q2 Sales.
b.
Create a calculated field using Difference as its name.
c.
The formula should subtract the Total Q1 Sales field value from the Total Q2
Sales field value to calculate the difference.
d.
Use +/- Q1 to Q2 in cell D1 as the column heading for the calculated field.
Go to the Location Pivot worksheet, which contains a PivotTable and PivotChart that
should show customer plan payments made according to where customers ordered their
plans.
Move the State field below the Order Location field in the Rows area so that the
PivotTable is easier to interpret.
13.
14.
15.
Eastern Mobile is raising the cost per line of their Family plans. Marina wants you to finetune the PivotChart on the Location Pivot worksheet, but first asks you to update the
data.
a.
Return to the Plans worksheet, and then change the cost per line to $50.00 for
the three Family plans.
b.
Refresh the PivotChart data on the Location Pivot worksheet so that it displays
accurate data.
Now you can make the PivotChart easier to understand and use as follows:
a.
Change the PivotChart to a Stacked Column chart.
b.
Apply Layout 4 to the PivotChart to display values in the columns, and then apply
the Monochromatic Palette 4 colors if the colors changed when you changed the
chart type.
c.
Increase the height of the chart until the lower-right corner is in cell G30.
Add a slicer to the PivotTable and PivotChart as follows to make it easy for Marina to
filter the data:
a.
Add a slicer based on the Plan Type field.
b.
Without resizing the slicer, position it so that its upper-left corner is in cell I9.
c.
Use the slicer to filter the PivotTable and PivotChart to display Family and
Unlimited plans only.
Shelly Cashman Excel 2019 | Module 8: SAM Project 1a
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.
Shelly Cashman Excel 2019 | Module 8: SAM Project 1a
Final Figure 1: Plans Worksheet
Final Figure 2: Payments by State Pivot Worksheet
Shelly Cashman Excel 2019 | Module 8: SAM Project 1a
Final Figure 3: Customers Pivot Worksheet
Shelly Cashman Excel 2019 | Module 8: SAM Project 1a
Final Figure 4: Customers Worksheet
Shelly Cashman Excel 2019 | Module 8: SAM Project 1a
Final Figure 5: Orders Worksheet
Shelly Cashman Excel 2019 | Module 8: SAM Project 1a
Final Figure 6: Customers by Plan Worksheet
Shelly Cashman Excel 2019 | Module 8: SAM Project 1a
Final Figure 7: Sales Pivot Worksheet
Shelly Cashman Excel 2019 | Module 8: SAM Project 1a
Final Figure 8: Location Pivot 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 the SAM website.
Shelly Cashman Excel 2019 | Module 8: SAM Project 1a
Eastern Mobile
ANALYZE DATA WITH CHARTS AND PIVOTTABLES
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.
Mobile Phone Plans
Plan ID
BA1
BA2
BA3
FA1
FA2
FA3
IN1
IN2
SI1
UN1
UN2
UN3
Plan Type
Basic
Basic
Basic
Family
Family
Family
International
International
Single
Unlimited
Unlimited
Unlimited
# of Lines
1
2
4
2
3
6
1
2
1
1
2
5
Cost / Line
$30.00
$30.00
$30.00
$40.00
$40.00
$40.00
$60.00
$60.00
$45.00
$65.00
$65.00
$65.00
Total Cost
$30.00
$60.00
$120.00
$80.00
$120.00
$240.00
$60.00
$120.00
$45.00
$65.00
$130.00
$325.00
Q1 Sales
$3,600
$7,200
$9,480
$8,720
$9,480
$11,050
$1,740
$3,960
$3,735
$5,135
$10,920
$10,400
$16,000
$14,000
$12,000
$10,000
$8,000
$6,000
$4,000
$2,000
$0
BA1
BA2
2021 Advertising
Q2 Sales
$4,290
$8,580
$7,560
$7,440
$8,720
$15,120
$960
$4,440
$2,610
$4,615
$10,270
$11,050
Month
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Amount
$6,500
$7,850
$8,275
$6,025
$6,400
$7,275
$5,750
$5,035
$7,525
$8,070
$8,250
$8,575
Plans Sold
56
72
80
50
55
55
60
55
60
78
72
80
Q2 Sales
BA2
BA3
FA1
FA2
FA3
IN1
IN2
SI1
UN1
UN2
UN3
Mobile Phone Customers
Customer
5010
5015
5019
5020
5023
5025
5027
5029
5031
5033
5037
5039
5102
5104
5106
5108
5110
5112
5114
5116
5118
5120
5122
5128
5201
5207
5208
Years
4
2
1
3
5
6
4
7
2
3
1
1
3
2
4
6
6
5
4
3
2
5
5
6
2
1
4
State
DC
NC
DE
DC
NC
MD
DC
VA
DE
VA
DE
VA
MD
DC
NC
DC
VA
MD
DC
VA
DC
MD
VA
NC
DE
MD
VA
Plan ID
BA2
FA2
FA3
UN2
FA1
SI1
UN2
FA3
IN1
UN3
UN2
BA3
FA2
SI1
UN3
UN2
FA3
FA2
BA3
UN1
FA1
FA2
FA3
IN2
FA2
UN2
UN2
Plan Type
Basic
Family
Family
Unlimited
Family
Single
Unlimited
Family
International
Unlimited
Unlimited
Basic
Family
Single
Unlimited
Unlimited
Family
Family
Basic
Unlimited
Family
Family
Family
International
Family
Unlimited
Unlimited
# of Lines
2
3
6
2
2
1
2
6
1
5
2
4
3
1
5
2
6
3
4
1
2
3
6
2
3
2
2
mers
Order Location
Online
Online
EM store
Online
Phone store
Online
Phone store
Online
EM store
Online
Online
Online
EM store
Phone store
Online
Online
EM store
Phone store
Phone store
Online
Online
Online
Online
Phone store
Online
EM store
Online
Payment
$60.00
$120.00
$240.00
$130.00
$80.00
$45.00
$130.00
$240.00
$60.00
$325.00
$130.00
$120.00
$120.00
$45.00
$325.00
$130.00
$240.00
$120.00
$120.00
$65.00
$80.00
$120.00
$240.00
$120.00
$120.00
$130.00
$130.00
Mobile Device Deliveries
Cust ID
5118
5015
5102
5010
5104
5031
5023
5108
5019
5120
5110
5020
5122
5112
5106
5029
5025
5039
5114
5027
5037
5116
5033
Order ID
10-112
10-220
10-221
10-237
10-337
10-499
10-553
10-663
10-882
11-037
11-054
11-098
11-364
11-366
11-599
11-633
11-721
11-882
12-076
12-171
12-504
12-754
12-764
Days
1
2
3
0
5
6
5
0
2
7
10
3
2
0
1
1
0
7
0
10
1
3
0
Mobile Device Orders
Bins
2
4
6
8
Category
Phone
Type
Smartphone
Tablet
Voice phone
Slate
Hybrid
Wearable
Activity tracker
Watch
evice Orders
Model
Large
Standard
Flip
Mini
Standard
Standard
Large
Fitness
Health
Standard
Rugged
Sales
$6,709
$5,928
$1,046
$2,188
$3,075
$4,210
$2,150
$2,568
$1,205
$2,677
$1,345
Plans
Total Payments # of Customers Sum of # of Lines
Basic
$300
3
10
BA2
$60
1
2
DC
$60
1
2
BA3
$240
2
8
DC
$120
1
4
VA
$120
1
4
Family
$1,720
11
43
FA1
$160
2
4
DC
$80
1
2
NC
$80
1
2
FA2
$600
5
15
DE
$120
1
3
MD
$360
3
9
NC
$120
1
3
FA3
$960
4
24
DE
$240
1
6
VA
$720
3
18
International
$180
2
3
IN1
$60
1
1
DE
$60
1
1
IN2
$120
1
2
NC
$120
1
2
Single
$90
2
2
SI1
$90
2
2
DC
$45
1
1
MD
$45
1
1
Unlimited
$1,495
9
23
UN1
$65
1
1
VA
$65
1
1
UN2
$780
6
12
DC
$390
3
6
DE
$130
1
2
MD
$130
1
2
VA
$130
1
2
UN3
$650
2
10
NC
$325
1
5
VA
$325
1
5
Grand Total
$3,785
27
81
Row Labels
Total Q2 Sales Total Q1 Sales
Basic
$20,430
$20,280
Family
$31,280
$29,250
International
$5,400
$5,700
Single
$2,610
$3,735
Unlimited
$25,935
$26,455
Grand Total
$85,655
$85,420
Sum of Payment Column Labels
Basic
Basic Total Family
Row Labels
DC
VA
DC
EM store
Online
$60 $120
$180
$80
Phone store
$120
$120
Grand Total
$180 $120
$300
$80
DE
MD
NC
$240 $120
$120 $120 $120
$120 $80
$360 $360 $200
Sum of Payment
Payments by Order Location
Phone store
Order Location
Plan Type
State
Unlimited – VA
Online
Unlimited – NC
Unlimited – MD
EM store
Unlimited – DE
$0
$200
$400
$600
VA
$240
$480
$720
Family Total
International
International Total Single
Single Total Unlimited
DE
NC
DC
MD
DC
$600
$60
$60
$920
$45
$45
$260
$200
$120
$120
$45
$45
$130
$1,720
$60 $120
$180
$45 $45
$90
$390
Unlimited
Unlimited Total Grand Total
DE
MD
NC
VA
$130
$130
$790
$130
$325 $520
$1,235
$2,380
$130
$615
$130 $130 $325 $520
$1,495
$3,785

Purchase answer to see full
attachment




Why Choose Us

  • 100% non-plagiarized Papers
  • 24/7 /365 Service Available
  • Affordable Prices
  • Any Paper, Urgency, and Subject
  • Will complete your papers in 6 hours
  • On-time Delivery
  • Money-back and Privacy guarantees
  • Unlimited Amendments upon request
  • Satisfaction guarantee

How it Works

  • Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
  • Fill in your paper’s requirements in the "PAPER DETAILS" section.
  • Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
  • Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
  • From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.