Shelly Cashman Excel 2016 | Module 8: SAM Mac Project 1a Peterbu Farm Share WORKING WITH TRENDLINES, PIVOTTABLES, PIVOTCHARTS, AND SLICERS GETTING STARTED ? Open the file SC_EX16_Mac_8a_ FirstLastName _1

Subject:MS ExcelPrice:20.87 Bought25

Share With

Shelly Cashman

Excel 2016 | Module 8: SAM Mac Project 1a

Peterbu Farm Share

WORKING WITH TRENDLINES, PIVOTTABLES, PIVOTCHARTS, AND SLICERS

GETTING STARTED

?

Open the file

SC_EX16_Mac_8a_

FirstLastName

_1.xlsx

, available for

download from the SAM website.

?

Save the file as

SC_EX16_Mac_8a_

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.

?

With the file

SC_EX16_Mac_8a_

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.

?

If you see a Message Bar with a security warning at the top of the Excel window,

click the Enable Content button in the Message Bar to enable the macros

contained in the file.

PROJECT STEPS

1.

Marcello Peterbu coordinates the farm share program for his family’s farm.

Besides the traditional vegetable and fruit shares, the Peterbu farm share has

also been expanded to include meat and dairy options for its members.

Marcello is reviewing the farm share sales data from the last few years to see

how best to expand the program. He first wants to create a line chart of the

total sales from the farm shares over a four year period.

Go to the

2018-2021 Share Totals

worksheet. Create a

Line with Markers

chart based on the range A3:E4. Move and resize the chart so that the upper

left corner is within cell A7 and the lower right corner is within in cell H24.

2.

Format the line with markers chart as described below:

a.

Use

2018-2021 Farm Share Sales

as the title of the chart.

b.

Add a Primary Vertical Axis title to the chart. Use

Dollars (U.S.)

as the

Primary Vertical Axis title.

c.

Add a Primary Horizontal Axis title to the chart. Use

Sales Year

as the

Primary Horizontal Axis title.

d.

Select the 2020 Sales data point on the chart, then change the size of the

marker to

10

and the fill color of the marker to

Green, Accent 6

(10

th

column, 1

st

row in the Theme Colors palette).

3.

Marcello now wishes to review how the sales of the non-produce shares might

grow over the next two years by adding a trendline to his sales data chart.

Go to the

Non-Produce Chart - Trends

worksheet. Add a linear trendline to the

chart with the following options:

a.

Set the trendline to forecast forward

2

periods.

b.

Display the R-squared value on the chart. (

Hint

: Depending on your

version of Office, the R-squared value may contain a different number of

decimal places.)

4.

Peterbu farm shares come in three different sizes: small, large, and regular (for

those shares without different size options). Marcello wants to visually display

how the sales of different share sizes have changed over time.

Go to the

2018-2021 Share Size Chart

worksheet. Remove the Average Share

Cost series from the chart.

5.

Format the chart as described below:

a.

Change the format of all four data series to

Cylinder

.

b.

Change the fill color of the Back Wall of the chart to

Blue, Accent 1,

Lighter 80%

(5

th

column, 2

nd

row in the Theme Colors palette) and fill

color of the Side Wall of the chart to

Orange, Accent 2, Lighter 80%

(6

th

column, 2

nd

row in the Theme Colors palette).

6.

Go to the

2018-2021 Share Sales

worksheet. Select cell A4 and create a

PivotTable and a chart on a new worksheet as described below:

a.

Use

Share Size Pivot

as the name of the worksheet.

b.

If necessary, move the worksheet directly after the

2018-2021 Share Size

Chart

worksheet.

c.

Add the Share Size field to the Rows area of the PivotTable field task pane.

d.

Add the Share Type field to the Values area of the PivotTable field task

pane. (

Hint

: The field name will be updated to Count of Share Type.)

e.

Insert a

Clustered Column chart

based on the range A4:B6. (

Hint

:

Depending on your version of Office, your chart may appear with field

buttons. If so, hide the field buttons, remove the legend, and continue.)

f.

Move the chart so that the upper left corner is located within cell A9 and

the lower right corner is located within cell G23.

g.

Use

Share Options by Size

as the title of the chart.

h.

Add a Primary Vertical Axis title to the chart. Use

Number of Share

Options

as the Primary Vertical Axis title.

i.

Add a Primary Horizontal Axis title to the chart. Use

Share Size

as the

Primary Horizontal Axis title.

7.

Customers can pick up their weekly farm shares at designated locations in

town. If Marcello decides to expand his program, he needs to know how each

location will be impacted by farm share pick up.

Go to the

2021 North Region Participants

worksheet and select cell A4. Create a

blank PivotTable report in a new worksheet. (

Hint

: The PivotTable report should

be based on the table NorthRegion2021.)

a.

Use

North Pickup Location Report

as the name of the worksheet.

b.

Add the Pickup Location and Share Type (in that order) to the Rows area of

the PivotTable Fields task pane.

c.

Add the Share Cost field to the Values area of the PivotTable Fields task

pane. (

Hint

: The name of the field will be updated to Sum of Share Cost.)

d.

Apply the

Currency

number format, with

0

decimal places and

$

as the

symbol, to the Sum of Share Cost field.

e.

Add the Share Size field to the Filters area of the PivotTable Fields task

pane. Use the filter to display only records for L in the PivotTable report.

8.

Go to the

Size Chart

worksheet. Update the worksheet as described below:

a.

Rename the worksheet using

Season Size Chart

as the new name of the

worksheet.

b.

Select the range A2:D4 and insert a

Clustered Column chart

. Switch the

rows and columns so that the horizontal axis is grouped by season. (

Hint

:

Depending on your version of Office, your chart may appear with field

buttons. If so, hide the field buttons, do not switch the rows and columns,

and continue from substep e.)

c.

Remove the chart title.

d.

Format the legend so that it is in the Right position.

e.

Move and resize the chart so that the upper left corner is located within

cell G2 and the lower right corner is located within cell O18.

9.

Marcello already created a PivotTable to determine how many sales were

associated with each pickup location. He now wishes to format the table to

make it easier to read and determine the average sales per participant at each

location.

Go to the

Location Report

worksheet. Refresh the PivotTable report data. (

Hint

:

After refreshing the PivotTable data, the value in cell D18 should be 17410.)

10.

Apply the

Pivot Style

Medium 14

to the PivotTable report. (

Hint

: Depending

on your version of Office, the PivotTable style may appear as Light Green, Pivot

Style Medium 14.)

11.

Modify the Sum of Share Cost field as described below:

a.

Summarize the values in the column using the

AVERAGE

function.

b.

Use

Average Sale per Share

as the name of the field.

12.

Modify the number format of the Total Share Sales field, so that the values

display in the

Accounting

number format, with

0

decimal places and

$

as the

symbol. (

Hint

: Depending on how you complete this action, the number format

may appear as Custom instead of Accounting.)

13.

Change the Report Layout so that the report is viewed in

Outline Form

.

14.

Switch to the

Sales Total Report

worksheet. Each customer has purchased the

same farm share package each year they have participated in the program.

Add a new calculated field to the end of the PivotTable report to calculate the

cumulative share sales for each customer as described below:

a.

For the field, create a formula without using a function that multiplies the

Share Cost

by the

Years Participating

. (

Hint

: The calculated field will

automatically be added to your PivotTable.)

b.

Use

Cumulative Share Sales

as the custom name of the calculated field.

(

Hint

: The name will appear as “Sum of Cumulative Share Sales” in the

PivotTable.)

c.

Apply the

Accounting

number format, with

0

decimal places and the

$

symbol, to the field.

15.

Go to the

Share Type Report

worksheet. Format the Season Slicer as described

below:

a.

Format the Slicer using the

Slicer Style Light 6

. (

Hint

: Depending on

your version of Office, the Slicer style may appear as Light Green, Slicer

Style Light 6.)

b.

Use the Slicer to filter the PivotTable report to display only data for the

Summer-Fall field.

16.

Add another Slicer to the Model PivotTable report based on the Share Type field,

then complete the following actions:

a.

Resize and reposition the Share Type Slicer, so that the upper left corner is

located within cell D10 and the lower right corner is located within cell

G26.

b.

Format the Slicer using the

Slicer Style Light 6

. (

Hint

: Depending on

your version of Office, the Slicer style may appear as Light Green, Slicer

Style Light 6.)

c.

Use the Slicer to filter the PivotTable report to display only data for Fruit,

Fruit & Vegetable, and Vegetable fields. (

Hint

: The PivotTable should

already be filtered using the Season Slicer.)

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: 2018-2021 Share Totals Worksheet

Final Figure 2: Non-Produce Chart - Trends Worksheet

Final Figure 3: 2018-2021 Share Size Chart Worksheet

Final Figure 4: Share Size Pivot Worksheet

Final Figure 5: 2018-2021 Share Sales Worksheet

Final Figure 6: North Pickup Location Report Worksheet

Final Figure 7: Season Size Chart Worksheet

Final Figure 8: Location Report Worksheet

Final Figure 9: Sales Total Report Worksheet

Final Figure 10: Share Type Report Worksheet