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_EX365_2021_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 2023 (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 2023.
   Create a 2-D pie chart that shows how each type of accessory (range A5:A9) contributed to the total 2023 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 2023 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 2024. Create a copy of the Quarter 1 worksheet at the end of the workbook. Rename the copy using Quarter 1 2024 as the worksheet name.
11. On the Quarter 1 2024 worksheet, clear only the contents from the range B5:B9. In cell B4, use 2024 Sales as the column heading. In cell D4, use 2025 Sales as the column heading.

Answer rating: 100% (QA)

To deal with Gilbertos requests permits go through each step 1 Consolidate sales information inside ... View the full answer