Shelly Cashman Excel 2019 | Module 8: SAM Project 1b Delgado Designs ANALYZE DATA WITH CHARTS AND PIVOTTABLES GETTING STARTED ? Open the file SC_EX19_8b_ FirstLastName _1

Subject:MS ExcelPrice:20.01 Bought8

Share With

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

Delgado Designs

ANALYZE DATA WITH CHARTS AND PIVOTTABLES

GETTING STARTED

?

Open the file

SC_EX19_8b_

FirstLastName

_1.xlsx

, available for download from the

SAM website.

?

Save the file as

SC_EX19_8b_

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_8b_

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.

[Mac - Click the Tools menu, and then click Excel Add-

ins.]

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 Add-Ins

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.

Selena Delgado creates and sells jewelry to stores and gift shops in Denver, Colorado,

and nearby cities. She is using an Excel workbook to analyze sales from the previous

year and asks for your help in creating advanced types of charts and PivotTables to

provide an overview of her jewelry, buyers, and orders.

Go to the

Sales

worksheet, which contains a column chart in the range F17:K30 showing

sales of each jewelry item from July to December. Selena wants to create a similar chart

showing the sales data from January to June.

Provide a chart showing the January to June sales data as follows:

a.

Copy the July-Dec chart.

b.

Paste the chart in cell A17.

c.

Select the copied chart and drag the blue outline from the range G3:G15 to the

range F3:F15 to change the data shown in the chart.

2.

The Promos table in the range I3:K15 compares the expenses for promotions spent in the

previous year and the number of jewelry sets sold. Selena asks you to create a chart that

shows the relationship between the amount spent and the jewelry sets sold.

a.

Insert a

Scatter

chart that shows the relationship between the expense amount

(range

J3:J15

) and the number of jewelry sets sold (range

K3:K15

).

b.

Resize and position the scatter chart so that it covers the range L2:S15.

c.

Use

Promo Expense and Sets Sold

as the chart title.

3.

Selena wants to analyze the relationship between the expense amounts and the jewelry

sets sold.

Add a

Linear Forecast

trendline to the scatter chart.

4.

Go to the

Buyers

worksheet, which contains details about store buyers in a table named

Buyers. Selena wants to display the amount billed to buyers in each of five cities where

she sells her jewelry.

Insert a recommended PivotTable based on the Buyers table as follows:

a.

Insert the

Sum of Billed by Location

recommended PivotTable.

[Mac Hint: Use

the Location field in the Rows area and the Billed field in the Values area. Be sure to

uncheck the Buyer ID and Item ID check boxes to deselect and remove them from

the PivotTable, and drag the Location Field from the Columns area to the Rows

area.]

b.

Use

Billed by Location

as the name of the new worksheet.

c.

Apply the

Coral, Pivot Style Medium 12

style to the PivotTable.

d.

Add a second copy of the

Billed

field to the Values area of the Field List, and then

change its summary function to

Average

so that Selena can compare the average

billing amounts 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 Billed

as the column heading in cell B3, and use

Average Billed

as the

column heading in cell C3.

5.

Insert a PivotChart based on the new PivotTable as follows to help Selena visualize the

data:

a.

Insert a

Combo

PivotChart based on the Billed by Location PivotTable.

[Mac Hint:

Insert a

Clustered Column Chart

.]

b.

Display the Total Billed as a Clustered Column chart and the Average Billed as a

Line chart.

[Mac Hint: Select the "Average Billed" data series, select Change Chart

Type on the Design tab, point to

Combo,

and then select Clustered Column - Line.]

c.

Include a secondary axis for the Average Billed data.

[Mac Hint: Select the

"Average Billed" data series/line, control+click the series/line, select Format Data

Point, and then select the Secondary Axis radio button.]

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

800.0

.

f.

Change the PivotChart colors to

Monochromatic Palette 4

.

g.

Resize and position the chart so that it covers the range A11:G25.

h.

Display the Field List again

6.

Selena also wants to insert a PivotTable that includes other Buyer information so that she

can analyze sales of Earrings, her most popular product. Create another PivotTable based

on the Buyers table as follows:

a.

Place the PivotTable on a new worksheet, and then use

Buyers and Products

as

the name of the worksheet.

b.

Display the Order Type as column headings.

c.

Display the Locations and then the Buyer IDs as row headings.

d.

Display the Billed amounts as the values.

e.

Display the Jewelry Type as a filter, and then filter the PivotTable to display buyer

information for

Earrings

only.

f.

Hide the field headers to reduce clutter in the PivotTable.

7.

Go to the

Orders

worksheet. The Shipping table in the range A3:C26 lists the number of

days between a buyer's order and its shipment. Selena wants to know how many orders

were delivered in the periods listed in the range E4:E7.

Insert a histogram as follows to provide this information for Selena:

a.

Use the

Data Analysis

tool to create a histogram.

b.

Use the number of days until shipment (range

C4:C26

) as the input range.

c.

Use the Bin list (range

E4:E7

) as the bin range.

d.

Use cell

A28

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

b.

Display the legend at the bottom of the chart to allow more room for the data.

9.

The Engraving table in the range G3:J14 shows the sales for three materials Selena

engraves as an extra service, with each material divided into types of engraving and

then into sizes. Selena wants to display these hierarchies of information in a chart.

Insert a Sunburst chart to display the hierarchies for Selena as follows:

a.

Insert a

Sunburst

hierarchy chart based on the engraving data in the range

G3:J14.

b.

Resize and position the chart so that it covers the range K3:R21.

c.

Use

Engraving Sales

as the chart title.

10.

Go to the

Buyers by Jewelry Type

worksheet, which contains a PivotTable showing buyer

data and billing amounts for each type of jewelry. Selena does not need to display the

number of pieces, but does want to list the jewelry types separate from the other data to

make the PivotTable easier to interpret.

Modify the PivotTable for Selena as follows:

a.

Remove the

Sum of Pieces

field from the Values area.

b.

Change the report layout to

Outline Form

.

11.

Go to the

Sales Totals

worksheet, which contains a PivotTable comparing sales for jewelry

from January to June and from July to December. Selena wants to know the difference

between the two 6-month periods.

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 Jan-June before

the Total July-Dec.

b.

Create a calculated field using

Difference

as its name.

c.

The formula should subtract the

Jan-June

field value from the

July-Dec

field value

to calculate the difference.

d.

Use

+/- Periods 1 and 2

in cell D1 as the column heading for the calculated field.

12.

Go to the

Locations

worksheet, which contains a PivotTable and PivotChart that should

show billings by order type and buyer location.

Move the

Location

field to the Rows area above the Order Type field so that the

PivotTable is easier to interpret.

13.

Selena is planning to raise the price of her earrings. She wants you to fine-tune the

PivotChart on the

Locations

worksheet, but first asks you to update the data.

a.

Return to the

Sales

worksheet, and then change the price per piece to

$70.00

for

the three earring products.

b.

Refresh the PivotChart on the

Locations

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 in the range A23:J38 to a

Stacked Column

chart.

b.

Apply

Layout 2

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

15.

Add a slicer to the PivotTable and PivotChart as follows to make it easy for Selena to filter

the data:

a.

Add a slicer based on the

Jewelry Type

field.

b.

Without resizing the slicer, position it so that its upper-left corner is in cell H3.

c.

Use the slicer to filter the PivotTable and PivotChart to display

Earrings

and

Necklace

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

Final Figure 1: Sales Worksheet

Final Figure 2: Billed by Location Worksheet

Final Figure 3: Buyers and Products Worksheet

Final Figure 4: Buyers Worksheet

Final Figure 5: Orders Worksheet

Final Figure 6: Buyers by Jewelry Type Worksheet

Final Figure 7: Sales Totals Worksheet

Final Figure 8: Locations Worksheet