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

Subject:MS ExcelPrice:19.99 Bought23

Share With

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”.

?

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.

?

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.

?

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. 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. 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:

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.

2.

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.

3.

Marina wants to analyze the relationship between advertising spent and the plans sold.

Add a

Linear

trendline to the scatter chart.

Shelly Cashman Excel 2019 | Module 8: SAM Project 1a

 

Shelly Cashman Excel 2019 | Module 8: SAM Project 1a

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:

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.

5.

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.

6.

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.

 

Shelly Cashman Excel 2019 | Module 8: SAM Project 1a

7.

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:

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.

8.

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.

9.

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:

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.

10.

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.

Modify the PivotTable for Marina as follows:

a.

Remove the

Sum of # of Lines

field from the Values area.

b.

Change the report layout to

Tabular Form

.

11.

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:

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.

 

Shelly Cashman Excel 2019 | Module 8: SAM Project 1a

12.

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.

Eastern Mobile is raising the cost per line of their Family plans. Marina wants you to fine-

tune 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.

14.

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.

15.

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.

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

Final Figure 3: Customers Pivot Worksheet

Final Figure 4: Customers Worksheet

Final Figure 5: Orders Worksheet

Final Figure 6: Customers by Plan Worksheet

Final Figure 7: Sales Pivot Worksheet

Final Figure 8: Location Pivot Worksheet