Homework answers / question archive / Shelly Cashman Excel 2019 | Module 5: SAM Critical Thinking Project 1c Media Hub CONSOLIDATE WORKBOOK DATA   GETTING STARTED Open the file SC_EX19_CT5c_FirstLastName_1

Shelly Cashman Excel 2019 | Module 5: SAM Critical Thinking Project 1c Media Hub CONSOLIDATE WORKBOOK DATA   GETTING STARTED Open the file SC_EX19_CT5c_FirstLastName_1

MS Excel

Share With

Shelly Cashman Excel 2019 | Module 5: SAM Critical Thinking Project 1c

Media Hub

CONSOLIDATE WORKBOOK DATA

• GETTING STARTED

• Open the file SC_EX19_CT5c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX19_CT5c_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • 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:
  • Support_EX19_5c_Sales.xlsx
• With the file SC_EX19_CT5c_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. 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.
   
   Enter a function to display today's date in the cell to the right of the Date label.
2. Replace the text "Science fantasy" with Science fiction to use the more common term throughout the worksheet.
3. In the Download Projections section of the worksheet, use the existing month name to autofill the remaining months in the year across the section.
4. Olivia wants to create a subheading to use in other places in the workbook. Create and apply a cell style as follows
   1. Create a cell style named Subhead based on the formatting in the merged cell below the month headings.
   2. Apply the new Subhead cell style to the merged cell in row 8.
5. 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 with a linear trend.
6. 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 (row 9) based on a growth series using 1.03 as the step value.
7. 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:
   1. Find the cell that represents downloads of Adventure books for the month of January. Enter a formula using a function and a 3D reference to total the number of downloads of Adventure audio books in January in the United States, Canada, and Australia.
   2. Copy the formula to calculate the number of downloads for the other types of books and months, pasting the formula only.
   3. Find the cell that represents sales of Adventure books for the month of January. Enter a formula using a function and a 3D reference to total the sales of Adventure audio books in January in the United States, Canada, and Australia.
   4. Copy the formula to calculate the sales for the other types of books and months, pasting the formula only.
8. Olivia wants to round the total sales values so that they are easier to remember.
   1. For the January total sales, add a function around the existing formula to display the total rounded up to zero decimal places.
   2. Fill the remaining monthly totals with the formula used for January.
9. 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:
   1. Open the file Support_EX19_5c_Sales.xlsx. Find the cell that contains the total sales for all months in the All Locations worksheet.
   2. In cell F24 of Olivia's workbook, insert a formula using an external reference to the total sales in the Support_EX19_5c_Sales.xlsx workbook.
10. 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:
    1. Create a 3-D pie chart that shows how each book type contributed to the total sales.
    2. Move and resize the chart so that it covers the range B25:F40.
11. Format the 3-D pie chart as follows to make it easier to interpret:
    1. Use Total Sales as the chart title.
    2. Add data labels to the chart on the outside end of each slice.
    3. Display only the category name and percentage values in the data labels.
    4. Change the number format of the data labels to Percentage with one decimal place.
    5. Explode the largest slice by eight percent.
    6. Remove the legend, which repeats information in the data labels.
12. Prepare for printing the All Locations worksheet as follows:
    1. Change the top and bottom margins to a quarter-inch.
    2. Select the range A1:F41 as the print area.
    3. Insert a footer that displays the sheet name in the center section.
13. 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:
    1. Create a worksheet using Sales Comparison as the worksheet name.
    2. In the first cell, type Total Sales, and then resize the column to its best fit.
    3. In the adjacent cell to the right, enter a formula using a worksheet reference to display the total sales amount from the All Locations worksheet, and then resize the column to its best fit.
    4. Return to the All Locations worksheet, and then insert a Clustered Column chart based on the nonadjacent ranges that represent book type, January sales, and April sales.
    5. Move the Clustered Column chart to the Sales Comparison worksheet.
    6. Position the Clustered Column chart so that its upper-left corner is in cell A3.
14. 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:
    1. 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.
    2. On the New Location worksheet, clear only the contents in the Downloads section. Be sure the book type labels, month labels, and total formulas remain on the worksheet.
    3. Clear the price per book amount below the date. Be sure the Price per book label remains on the worksheet.

Your workbook should look like the Final Figures on the following pages. Note: When opening your file or the Graded Summary report for this Project, you may be prompted to update external links. Select Don't Update in the dialog box to open your file or view your report. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.