Shelly Cashman excel 2019 module 5 sam project 1b Jefferson School District CONSOLIDATE WORKBOOK DATA GETTING STARTED ? Open the file SC_EX19_5b_ FirstLastName _1

Subject:MS ExcelPrice:19.99 Bought13

Share With

Shelly Cashman excel 2019 module 5 sam project 1b

Jefferson School District

CONSOLIDATE WORKBOOK DATA

GETTING STARTED

?

Open the file

SC_EX19_5b_

FirstLastName

_1.xlsx

, available for download from the

SAM website.

?

Save the file as

SC_EX19_5b_

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.

?

To complete this SAM Project, you will also need to download and save the following data

files from the SAM website onto your computer:

?

Support_EX19_5b_Fundraisers.xlsx

?

With the file

SC_EX19_5b_

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.

PROJECT STEPS

1.

Allison Kenyon is on the Finance Committee of the Jefferson School Foundation, an

organization that raises funds for schools in the Jefferson School District. Allison is

tracking sales from four cookie dough fundraisers held during the year. She asks for your

help in projecting future sales and visualizing the sales data for an upcoming committee

meeting.

The

Elementary School, Middle School

, and

High School

worksheets have the same

structure and contain similar data. Group the

Elementary School, Middle School

, and

High School

worksheets to make changes to the three worksheets at the same time. The

first change is to display today's date.

In cell B3 of the

Elementary School

worksheet, enter a formula using the

TODAY

function

to display today's date.

2.

Find each instance the text "Pistachio cranberry" on the three worksheets and then

change it to

Pumpkin spice

to use the correct cookie flavor.

3.

Use the text in cell H5 to fill the range I5:K5 with the remaining quarters in the year.

4.

Allison wants to use the cell formatting in merged cell H8 in other places in the

workbook. Create and apply a cell style as follows:

a.

Create a cell style named

Notes

based on the formatting in merged cell H8.

b.

Merge cells H6:K6 without centering the contents.

c.

Apply the new

Notes

cell style to cell H6.

5.

Allison thinks each school has a good chance of increasing the average number of

fundraising items sold to 125 in Quarter 4 next year. For Quarter 1, she estimates 80

fundraising items sold, which is the average number sold each quarter this year.

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

 

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

Project the average number of items sold next year by filling the series for the first

projection (range H7:K7) with a linear trend.

6.

Allison also wants to know how the average number of items sold would increase if

people bought 5% more fundraising items next year.

Project the average number of items sold next year for the second projection (range

H9:K9) based on a growth series using

1.05

as the step value.

7.

Allison wants to consolidate this year's sales data for the elementary, middle, and high

schools on the

All Schools

worksheet.

Ungroup the worksheets, go to the

All Schools

worksheet, and then consolidate the data

as follows:

a.

In cell C6, enter a formula using the

SUM

function and a 3D reference to total the

number of chocolate chip cookie dough packages sold in Quarter 1 (cell

B6

) in

Jefferson elementary, middle, and high schools.

b.

Copy the formula in cell C6 to calculate the number of sales for the other flavors

and quarters (range C6:F10), pasting the formula only.

c.

In cell C15, enter a formula using the

SUM

function and a 3D reference to total the

sales amounts for chocolate chip cookie dough packages sold in Quarter 1 (cell

B15

) in Jefferson elementary, middle, and high schools.

d.

Copy the formula in cell C15 to calculate the sales for the other flavors and

quarters (range C15:F19), pasting the formula only.

8.

Allison wants to round the total sales values for each quarter so that they are easier to

remember.

a.

In cell C20, add the

ROUNDUP

function to display the total sales for Quarter 1

rounded up to

0

decimal places.

b.

Fill the range D20:F20 with the formula in cell C20.

9.

In cell G22, Allison wants to display the total fundraising sales from the previous year.

This data is stored in another workbook. Insert the total as follows:

a.

Open the file

Support_EX19_5b_Fundraisers.xlsx

.

b.

In cell G22 of Allison's workbook, insert a formula using an external reference to

cell F20 in the

All Schools

worksheet in the

Support_EX19_5b_Fundraisers.xlsx

workbook.

10.

Allison wants to visualize how the sales of each cookie dough flavor contributed to the

total fundraising sales for the year.

Create a chart as follows to illustrate this information:

a.

Create a

3-D Pie

chart that shows how each cookie dough flavor (range B15:B19)

contributed to the total sales (range G15:G19).

b.

Move and resize the chart so that the upper-left corner is in cell B23 and the lower-

right corner is in cell G38.

 

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

11.

Format the 3-D Pie chart as follows to make it easier to interpret:

a.

Use

Fundraising Sales

as the chart title.

b.

Add data labels to the chart on the

Inside End

of each slice.

c.

Display only the

Category Name

and

Percentage

values in the data labels.

d.

Change the number format of the data labels to

Percentage

with

1

decimal place.

e.

Explode the largest slice (Chocolate chip) by

5

percent.

f.

Remove the legend, which repeats information in the data labels.

12.

Prepare for printing the

All Schools

worksheet as follows:

a.

Change the left and right margins to

0.5"

.

b.

Select the range B1:G39 as the print area.

c.

Insert a footer that displays the Sheet Name in the center section.

13.

Allison wants to compare sales for Quarters 1 and 4 but doesn't want to clutter the

All

Schools

worksheet with another chart. Create a new worksheet and chart for this

comparison as follows:

a.

Create a worksheet using

Quarterly Comparison

as the worksheet name.

b.

In cell A1, type

Fundraising Sales

, and then resize column A to its best fit.

c.

In cell B1, enter a formula using a worksheet reference to display the total sales

amount from cell G20 on the

All Schools

worksheet, and then resize column B to its

best fit.

d.

Return to the

All Schools

worksheet, insert a

Clustered Column

chart based on

the nonadjacent ranges B14:C19 and F14:F19, and then move the Clustered

Column chart to the

Quarterly Comparison

worksheet.

e.

Position the Clustered Column chart so that its upper-left corner is in cell A3.

14.

Allison wants to use a copy of the

Elementary School

worksheet as a template to give to

other schools.

Copy the worksheet as follows:

a.

Create a copy of the

Elementary School

worksheet at the end of the workbook.

b.

Rename the copy using

Other School

as the worksheet name.

c.

On the

Other School

worksheet, clear only the contents from the cells containing

data, not formulas, in the range B6:E10 and cell H2.

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 5: SAM Project 1b

Final Figure 1: Elementary School Worksheet

Final Figure 2: Middle School Worksheet

Final Figure 3: High School Worksheet

Final Figure 4: All Schools Worksheet

Final Figure 5: Quarterly Comparison Worksheet

Final Figure 6: Other School Worksheet