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

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”.
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.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
o 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.
o 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