Shelly Cashman Excel 2019 | Module 5: End of Module Project 1 Flexy Wireless Communications CONSOLIDATE WORKBOOK DATA GETTING STARTED ? Open the file SC_EX19_EOM5-1_ FirstLastName _1

Subject:MS ExcelPrice:19.99 Bought15

Share With

Shelly Cashman Excel 2019 | Module 5: End of Module Project 1

Flexy Wireless Communications

CONSOLIDATE WORKBOOK DATA

GETTING STARTED

?

Open the file

SC_EX19_EOM5-1_

FirstLastName

_1.xlsx

, available for download from

the SAM website.

?

Save the file as

SC_EX19_EOM5-1_

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_EOM5-1_Accessories.xlsx

?

With the file

SC_EX19_EOM5-1_

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.

Gilberto Ray is a financial analyst for Flexy Wireless Communications, a

telecommunications company in Sacramento, California. In addition to cell phones and

wireless plans, Flexy sells electronic accessories, such as charging pads and headphones.

Gilberto is analyzing the revenue generated from the sales of accessories and asks for

your help in completing the analysis.

Go to the

Annual Sales

worksheet. In the range B5:B9, Gilberto wants to consolidate the

sales data from Quarters 1–4 in the previous year. In cell B5, enter a formula using the

SUM function and 3D references to total the sales for batteries and chargers using the

data on the

Quarter 1

,

Quarter 2

,

Quarter 3

, and

Quarter 4

worksheets. Fill the range

B6:B9 with the formula in cell B5 to calculate the total annual sales for the other types of

accessories.

2.

Gilberto also wants to display the total accessory sales from the previous year. This data

is stored in another workbook.

In cell B12 of Gilberto's workbook, insert a formula using an external reference to cell

B10 in the

Accessories

worksheet in the

Support_EX19_EOM5-1_Accessories.xlsx

workbook.

3.

Gilberto wants to make some changes to all five worksheets, which have the same

structure.

Group the

Annual Sales

,

Quarter 1

,

Quarter 2

,

Quarter 3

, and

Quarter 4

worksheets. In

cell F1 of the

Annual Sales

worksheet, enter a formula using the TODAY function to

display today's date.

4.

In the range D5:D9 on all five worksheets, Gilberto wants to project next year's sales for

each accessory, rounded up to zero decimal places so the values are easier to

remember.

In cell D5, enter a formula using the ROUNDUP function that adds the sales for batteries

and chargers in 2021 (cell B5) to the sales for the same accessories (cell B5) multiplied

by the projected increase percentage (cell C5). Round the result up to 0 decimal places.

Fill the range D6:D9 with the formula in cell D5.

5.

Ungroup the worksheets. Use the quarter name in cell F5 of the

Annual Sales

worksheet

to fill the range G5:I5 with the names of the remaining quarters in the year.

6.

In the first quarter of the year, Flexy Wireless sold 2550 units of a new smart home

product called Home Hub. The company wants to increase the sales of the new product

to 3000 units by the fourth quarter.

Project the sales for Quarters 2, 3, and 4 by filling the series for the first projection

(range F7:I7) with a linear trend.

7.

Gilberto also wants to know how many Home Hub units the company would sell if sales

increased by two percent each quarter.

Project the sales for Quarters 2, 3, and 4 for the second projection (range F9:I9) based on

a growth trend using 1.02 as the step value.

8.

Gilberto wants to visualize how the sales of each type of accessory contributed to the

total annual sales for 2021.

Create a 2-D pie chart that shows how each type of accessory (range A5:A9) contributed

to the total 2021 sales (range B5:B9). Move and resize the chart so the upper-left corner

is in cell A14 and the lower-right corner is in cell D30.

9.

To make the 2-D pie chart easier to interpret, type

Accessory Sales 2021

as the chart

title. Add data labels to the chart on the Inside End of each slice. Display the Value and

Percentage in the data labels.

10.

Go to the

Quarter 1

worksheet. Gilberto wants to use a copy of the worksheet to track

sales in 2022. Create a copy of the

Quarter 1

worksheet at the end of the workbook.

Rename the copy using

Quarter 1 2022

as the worksheet name.

11.

On the

Quarter 1 2022

worksheet, clear only the contents from the range B5:B9. In cell

B4, use

2022 Sales

as the column heading. In cell D4, use

2023 Sales

as the column

heading.

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: Annual Sales Worksheet

Final Figure 2: Quarter 1 Worksheet

Final Figure 3: Quarter 2 Worksheet

Final Figure 4: Quarter 3 Worksheet

Final Figure 5: Quarter 4 Worksheet

Final Figure 6: Quarter 1 2022 Worksheet