SC_EX19_EOM5-1_PhadrianDAvis_Report_1

Shelly Cashman Excel 2019 | Module 5: End of Module Project 1 Phadrian DAvis GE ver. 17.1.0-rc0000 1. 1/1 Create a formula using a function. Copy a formula into a range. 2. 0/1 Create a link to an external workbook. 3. 2/2 Create a formula using a function. 4. 0/2 Create a formula using a function. In the Annual Sales worksheet, the formula in cell D5 has not been entered correctly. Copy a formula into a range. In the Annual Sales worksheet, the formula in cell D5 has not been entered correctly. 5. 2/2 AutoFill values in a range. 6. 2/2 AutoFill values in a range. 7. 2/2 AutoFill values in a range. 8. 2/2 Insert a chart. Resize and reposition a chart. 9. 1/2 Insert a chart title. Change the position of the data labels. Update the data labels in a chart. 10. 0/2 Copy a worksheet. In the workbook, the Quarter 1 2022 worksheet should appear after the Quarter 4 worksheet. 11. 1/2 Delete cell contents. Update values in cells. In the Quarter 1 2022 worksheet, cell B4 should contain the text "2022 Sales". SUBMISSION #1 | SCORE IS: 13 OUT OF 20 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. 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. In the Annual Sales worksheet, cell B12 should contain an external reference to cell B10 in the Support_EX19_EOM5-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 F1 of the Annual Sales worksheet, enter a formula using the TODAY function to display today's date. 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. 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. 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. 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. 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. 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. In the Annual Sales worksheet, the 2-D pie chart's data labels should be formatted to display the value and percentage. 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. 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.

Author: Note: Do

Flexy Wireless Communications CONSOLIDATE WORKBOOK DATA Phadrian DAvis o not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SA Shelly Cashman Excel 2019 | Module 5: End of Module Project 1

.

Flexy Accessories Annual Sales Product Category 2021 Sales % Increase 2022 Sales H Batteries and chargers $ 123,274.42 1.50% 125124 Headphones $ 232,299.63 2.00% 125124 Phone cases $ 83,290.51 1.00% 125124 Smart watches $ 323,806.96 2.25% 125124 Wireless speakers $ 277,788.63 3.00% 125124 ### $ 625,620.00 Total accessory sales 2020: $123,274.42 ; 11.85% $232,299.63 ; 22.33% $83,290.51 ; 8.01% $323,806.96 ; 31.12% $277,788.63 ; 26.70% Accessory Sales 2021 Batteries and chargers Headphones Phone cases Smart watches Wireless speakers