GETTING  STARTED

• Save the file  SC–EX365–2021–EOMS-1–FirstLastName   1.xlsx as

SC–EX365–2021–EOMS-1–FirstLastName  –2.xlsx

• Edit the file name by changing “l” to “2”.
• If you do not see the .xlsx file extension, do not type it. The file extension will be added for you a utomatically.
• To complete this Project, you will also need the following files:
• Support_EX365_2021_EOMS-l_Accessories.xlsx
• With the file SC_EX365_2021_EOMS-l_FirstlastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
• If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Gilberto Ray is a financial ana lyst for Flexy Wireless Communications, a telecommunications company in Sacramento, Ca lifornia. 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 BS:B9, Gilberto wants to

consolidate the sa les data from Qua rters 1-4 in the previous year. In cell BS, enter a formula using the SUM function and 3D references to tota l the sales for batteries and cha rgers 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 BS to calculate the tota l annual sales for the other types of accessories.

• Gilberto also wants to display the total accessory sa les from the previous yea r. This data is stored in another workbook.

In cell Bl2 of Gilberto’s workbook, insert a formula using an externa l reference to cell BlO in the Accessories worksheet in the Support_EX365_2021 _EOMS- 1 Accessories.xlsx workbook.

• 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 Fl of the Annual Sales worksheet, enter a formula using the TODAY function to display today’ s date.

• In the range DS:D9 on all five worksheets, Gilberto wants to project next year’s sales for each accessory, rounded up to zero decima l places so the va lues a re easier to remember.

In cell DS, enter a formula  using the ROUNDUP function that adds the sa les for batteries and cha rgers in 2023 (cell BS) to the sales for the same accessories

This study source was downloaded by 100000890882035 from CourseHero.com on 09-13-2024 07:51:11 GMT -05 :00

-·  ..    –   .            –       · – — — – – – · – –     .  .   – –  .   – – –     .  .   –  .     –  –

(cell BS) multiplied by the projected increase percentage (cell CS). Round the

result up to 0 decimal places. Fill the range 06:09 with the formula in cell DS.

S. Ungroup the worksheets. Use the qua rter name in cell FS of the Annual Sales worksheet to fill the range GS:IS with the names of the remaining quarters in the year.

• In the first quarter of the year, Flexy Wireless sold 2SSO units of a new sma rt 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 Qua rters 2, 3, and 4 by filling the series for the first projection (range F7:17) with a linea r trend.

• 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 Qua rters 2, 3, and 4 for the second projection (range F9:19)

based on a growth trend using 1.02 as the step va lue.

• Gilberto wants to visualize how the sa les of each type of accessory contributed to the total annual sales for 2023.

Create a 2-D pie chart that shows how each type of accessory (range AS:A9) contributed to the total 2023 sales (range BS:B9). Move and resize the chart so the upper-left corner is in cell Al4 and the lower-right corner is in cell 030.

• To make the 2-D pie chart easier to interpret, type Accessory Sales 2023 as the chart title. Add data labels to the chart on the Inside End of each slice. Display the Va lue and Percentage in the data labels.
• Go to the Quarter 1 worksheet. Gilberto wants to use a copy of the worksheet to track sales in 2024. Create a copy of the Quarter 1 worksheet at the end of the workbook. Rename the copy using Quarter 12024 as the worksheet name.
• On the Quarter 1 2024 worksheet, clear only the contents from the range BS:B9.

In cell 84, use 2024 Sales as the column heading. In cell 04, use 2025 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 website to submit your completed project.

Final Figure 1: Annual Sales Worksheet

Final Figure 2: Quarter 1Worksheet

Final Figure 3: Quarter 2 Worksheet

Final Figure 4: Quarter 3 Worksheet

Final Figure 5: Quarter 4 Worksheet

Final Figure 6: Quarter 12024 Worksheet