SC_EX19_5a_RileyKneisl_Report_1

Shelly Cashman Excel 2019 | Module 5: SAM Project 1a Riley Kneisl GE ver. 17.0.1-rc0000 1. 1/1 Create a formula using a function. 2. 1/1 Update text in a cell. 3. 1/1 AutoFill values in a range. 4. 1/1 Create a cell style. Apply a cell style. 5. 1/1 AutoFill series values in a range. 6. 1/1 AutoFill series values in a range. 7. 0/1 Create a formula using a function. Copy a formula into a range. In the All Locations worksheet, one or more cells in the ranges B7:B11 and C6:E11 contain an incorrect formula. Create a formula using a function. Copy a formula into a range. 8. 1/1 Create a formula using a function. Copy a formula into a range. 9. 2/2 Create a link to an external workbook. Enter a number in a cell. 10. 2/2 Insert a chart. Resize and reposition a chart. 11. 2/2 Insert a chart title. Add data labels to a chart. Update the data labels in a chart. Update the number format of data labels in a chart. Explode a data point in a chart. Remove the legend in a chart. 12. 2/2 Set custom margins for a worksheet. Set the print area for a worksheet. Add a footer to a worksheet. 13. 2/2 Add a new worksheet. Enter text in a cell. Change the column width. Create a formula. Change the column width. Insert a chart. Resize and reposition a chart. 14. 2/2 Copy a worksheet. Clear cell contents. SUBMISSION #1 | SCORE IS: 19 OUT OF 20 Olivia Clausen is a product analyst for Media Hub, a website that sells audio books, movies, TV shows, and other media around the world. Olivia is tracking sales for the year and asks for your help in projecting future sales and visualizing the sales data. The United States, Canada , and Australia worksheets have the same structure and contain similar data. Group the United States , Canada , and Australia worksheets to make changes to the three worksheets at the same time. The first change is to display today's date. In cell H1 of the United States worksheet, enter a formula using the TODAY function to display today's date. Find the text "Science fantasy" and then change it to Science fiction to use the more common term. Use the month name in cell H5 to fill the range I5:O5 with the names of the remaining months in the year. Olivia wants to use the cell formatting in merged cell H6 in other places in the workbook. Create and apply a cell style as follows: a. Create a cell style named Subhead based on the formatting in merged cell H6. b. Apply the new Subhead cell style to cell H8. Olivia thinks Media Hub has a good chance of increasing the number of audio book downloads in the United States to 14,000 in December. For May, she estimates 11,432 downloads, which is the average number of monthly downloads from January to April. Project the number of downloads in June to November by filling the series for the first projection (range H7:O7) with a linear trend. Olivia also wants to know how the number of downloads would increase if customers downloaded 3% more audio books each month from June to December. Project the number of downloads in June to December for the second projection (range H9:O9) based on a growth series using 1.03 as the step value. Olivia wants to consolidate the sales data in the United States, Canada, and Australia on the All Locations worksheet. Ungroup the worksheets, go to the All Locations worksheet, and then consolidate the data as follows: a. In cell B6, enter a formula using the SUM function and a 3D reference to total the number of downloads of Adventure audio books in January (cell B6 ) in the United States, Canada, and Australia. b. Copy the formula in cell B6 to calculate the number of downloads for the other types of books and months (ranges B7:B11 and C6:E11), pasting the formula only. c. In cell B16, enter a formula using the SUM function and a 3D reference to total the sales of Adventure audio books in January (cell B16 ) in the United States, Canada, and Australia. d. Copy the formula in cell B16 to calculate the sales for the other types of books and months (ranges B17:B21 and C16:E21), pasting the formula only. In the All Locations worksheet, the formula in cell B6 should use the SUM function to calculate the number of downloads of Adventure audio books in January. In the All Locations worksheet, the formula in cell B16 should use the SUM function to calculate the sales of Adventure audio books in January. In the All Locations worksheet, one or more cells in the ranges B17:B21 and C16:E21 contain an incorrect formula. Olivia wants to round the total sales values so that they are easier to remember. a. In cell B22, add the ROUNDUP function to display the total sales for January rounded up to 0 decimal places. b. Fill the range C22:F22 with the formula in cell B22. In cell F24, Olivia wants to display the total sales from the previous year for the same period. This data is stored in another workbook. Insert the total as follows: a. Open the file Support_EX19_5a_Sales.xlsx . b. In cell F24 of Olivia's workbook, insert a formula using an external reference to cell F22 in the All Locations worksheet in the Support_EX19_5a_Sales.xlsx workbook. Olivia wants to visualize how the sales of each type of audio book contributed to the total sales for January to April. Create a chart as follows to illustrate this information: a. Create a 3-D pie chart that shows how each type of book (range A16:A21) contributed to the total sales (range F16:F21). b. Move and resize the chart so that the upper-left corner is in cell B25 and the lower-right corner is in cell F40. Format the 3-D pie chart as follows to make it easier to interpret: a. Use Total Sales as the chart title. b. Add data labels to the chart on the Outside End of each slice. c. Display only the Category Name and Percentage values in the data labels. d. Change the number format of the data labels to Percentage with 1 decimal place. e. Explode the largest slice (Mystery audio books) by 8 percent. f. Remove the legend, which repeats information in the data labels. Prepare for printing the All Locations worksheet as follows: a. Change the top and bottom margins to 0.25" . b. Select the range A1:F41 as the print area. c. Insert a footer that displays the Sheet Name in the center section. Olivia wants to compare sales for January and April but doesn't want to clutter the All Locations worksheet with another chart. Create a new worksheet and chart for this comparison as follows: a. Create a worksheet using Sales Comparison as the worksheet name. b. In cell A1, type Total Sales , and then resize column A to its best fit. c. In cell B1, enter a formula using a worksheet reference to display the total sales amount from cell F22 on the All Locations worksheet, and then resize column B to its best fit. d. Return to the All Locations worksheet, and then insert a Clustered Column chart based on the nonadjacent ranges A15:B21 and E15:E21. e. Move the Clustered Column chart to the Sales Comparison worksheet. f. Position the Clustered Column chart so that its upper-left corner is in cell A3. Olivia wants to use a copy of the United States worksheet as a template to track sales in new locations. Copy the worksheet as follows: a. Create a copy of the United States worksheet at the end of the workbook and rename the copy using New Location as the worksheet name. c. On the New Location worksheet, clear only the contents from the cells containing data, not formulas, in the range B6:E11 and cell H2.

Author: Note: Do

M

Media Hub Audio Books United States Downloads: January - April Book Type January February March Adventure 2,193 2,282 2,130 Literary 989 1,036 798 Mystery 2,405 2,788 2,239 Romance 2,397 2,655 3,244 Science fiction 1,873 1,869 1,904 Self-help 1,027 1,095 1,124 Total 10,884 11,725 11,439 Sales: January - April Book Type January February March Adventure 19,627.35 20,423.90 19,063.50 Literary 8,851.55 9,272.20 7,142.10 Mystery 21,524.75 24,952.60 20,039.05 Romance 21,453.15 23,762.25 29,033.80 Science fiction 16,763.35 16,727.55 17,040.80 Self-help 9,191.65 9,800.25 10,059.80 Total $ 97,411.80 $ 104,938.75 $ 102,379.05

Download Projections July August September October November December ,000 in December 12,165 12,532 12,899 13,266 13,633 14,000 % per month 12,128 12,492 12,866 13,252 13,650 14,059

Media Hub Audio Books Canada Downloads: January - April Book Type January February March Adventure 968 1,082 1,120 Literary 689 836 708 Mystery 1,205 1,288 1,339 Romance 1,097 1,055 1,144 Science fiction 773 879 904 Self-help 707 795 824 Total 5,439 5,935 6,039 Sales: January - April Book Type January February March Adventure 9,631.60 10,765.90 11,144.00 Literary 6,855.55 8,318.20 7,044.60 Mystery 11,989.75 12,815.60 13,323.05 Romance 10,915.15 10,497.25 11,382.80 Science fiction 7,691.35 8,746.05 8,994.80 Self-help 7,034.65 7,910.25 8,198.80 Total $ 54,118.05 $ 59,053.25 $ 60,088.05

Download Projections August September October November December ecember 7,698 8,274 8,849 9,425 10,000 nth 6,526 6,722 6,923 7,131 7,345

Media Hub Audio Books Australia Downloads: January - April Book Type January February March Adventure 652 705 759 Literary 189 227 265 Mystery 805 928 1,052 Romance 497 610 652 Science fiction 712 799 886 Self-help 227 281 335 Total 3,082 3,551 3,948 Sales: January - April Book Type January February March Adventure 7,139.40 7,723.40 8,307.40 Literary 2,069.55 2,485.65 2,901.75 Mystery 8,814.75 10,165.25 11,515.75 Romance 5,442.15 6,679.50 7,139.40 Science fiction 7,796.40 8,749.05 9,701.70 Self-help 2,485.65 3,076.95 3,668.25 Total $ 33,747.90 $ 38,879.80 $ 43,234.25

Download Projections August September October November December cember 4,496 4,747 4,998 5,249 5,500 nth 4,089 4,212 4,338 4,468 4,602

All Locations Media Hub Audio Books All Locations Downloads: January - April Book Type January February March Adventure 3,813 4,069 4,009 Literary 1,867 2,099 1,771 Mystery 4,415 5,004 4,630 Romance 3,991 4,320 5,040 Science fantasy 3,358 3,547 3,694 Self-help 1,961 2,171 2,283 Total 19,405 21,211 21,426 Sales: January - April Book Type January February March Adventure 36,398.35 38,913.20 38,514.90 Literary 17,776.65 20,076.05 17,088.45 Mystery 42,329.25 47,933.45 44,877.85 Romance 37,810.45 40,939.00 47,556.00 Science fiction 32,251.10 34,222.65 35,737.30 Self-help 18,711.95 20,787.45 21,926.85 Total $ 185,278.00 $ 202,872.00 $ 205,702.00 Total sales Romance; 21.4% Science fiction; 17.3% Self-help; 10.3% Total Sales

Total Sales $ 810,844.00 Adventure Literary Mystery Romance Science fiction Self-help - 10,000.00 20,000.00 30,000.00 40,000.00 50,000.00 60,000.00 Chart Title January April

Media Hub Audio Books United States Downloads: January - April Book Type January February March Adventure Literary Mystery Romance Science fiction Self-help Total - - - Sales: January - April Book Type January February March Adventure - - - Literary - - - Mystery - - - Romance - - - Science fiction - - - Self-help - - - Total $ - $ - $ -

Download Projections July August September October November December ,000 in December 12,165 12,532 12,899 13,266 13,633 14,000 % per month 12,128 12,492 12,866 13,252 13,650 14,059