Shelly Cashman Excel 2016 | Module 7: SAM Project 1b J Smith Events Importing Data and working with SmartArt, Images, and Screenshots   GETTING STARTED Open the file SC_EX16_7b_FirstLastName_1

Subject:MS ExcelPrice:19.87 Bought4

Share With

Shelly Cashman Excel 2016 | Module 7: SAM Project 1b

J Smith Events

Importing Data and working with SmartArt, Images, and Screenshots

GETTING STARTED

• Open the file SC_EX16_7b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX16_7b_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_SC_EX16_7b_Balloons.jpg
    • Support_SC_EX16_7b_Cake.jpg
    • Support_SC_EX16_7b_Flowers.jpg
    • Support_SC_EX16_7b_Celebrations.docx
    • Support_SC_EX16_7b_Rose.jpg
    • Support_SC_EX16_7b_Occasions.txt
    • Support_SC_EX16_7b_Bloom.accdb
    • With the file SC_EX16_7b_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. Jules Smith owns the J Smith event planning company. She is evaluating whether to partner with a supporting event company in the area. One of Jules’s assignments is to combine the data from each possible partner company into a single workbook, format the data, and create graphics to make the data easier to analyze.

Go to the Partnerships worksheet. Insert a SmartArt graphic using the Continuous Picture List layout (in the Pictures section of the SmartArt Graphic dialog box) to include an eye-catching graphic that displays the possible partner companies.

1. Using the support files available for download from the SAM website, insert pictures into the SmartArt graphic’s picture placeholders as follows:

1. Insert Support_SC_EX16_7b_Balloons.jpg into the first shape.
2. Insert Support_SC_EX16_7b_Cake.jpg into the second shape.
3. Insert Support_SC_EX16_7b_Flowers.jpg into the third shape.
4. Add text to the SmartArt graphic to identify the partner companies as follows:
   1. Type Celebrations into the first text placeholder.
   2. Type Occasions into the second text placeholder.
   3. Type Les Fleurs into the third text placeholder.
   4. Change the format of the SmartArt graphic to coordinate with the J Smith logo as follows:
      1. Change the colors to Colored Outline – Accent 1 (1st option in the Accent 1 section of the Change Colors gallery).
      2. Apply the Moderate Effect SmartArt style.
      3. Update the SmartArt graphic as follows to include a fourth partner company:
         1. Add a new shape to the right side of the SmartArt graphic.
         2. In the new shape, insert the file Support_SC_EX16_7b_Rose.jpg.
         3. Type Bloom in the new shape below the rose picture.
         4. Improve the appearance of the Partnerships worksheet as follows:
            1. Move the SmartArt graphic so its upper-left corner is within cell B3.
            2. Resize the SmartArt graphic so its lower-right corner is within cell J15.
            3. Remove the worksheet gridlines from view.
            4. Go to the Celebrations worksheet, and then arrange the data as follows:
               1. Copy the range I3:N8 to the Office Clipboard.
               2. Paste the data from the range I3:N8into cell B3, transposing the data when you paste.
               3. Delete the contents of the range I3:N8.
               4. The Celebrations company has provided sales data for the last five years in a Word table. Insert the data into the Celebrationsworksheet as follows:
                  1. Open the Support_SC_EX16_7b_Celebrations.docx Word document.
                  2. Select the first six columns of the Financial Information table, copy the data to the Office Clipboard, paste the Word data into cell B11 of the Celebrations worksheet, matching the destination formatting, and then close the Word file.
                  3. Use the Format Painter to copy the formatting of the range B11:G16 to the range B3:G8 in the Celebrations worksheet.
                  4. The Occasions events center has provided sales data for their different event types in a text file. Insert the data into the Occasions worksheet as follows          1.Go to the Occasions worksheet, and then import data from the Support_SC_EX16_7b_Occasions.txt text file.

1. Organize the historical data in the Occasions worksheet as follows:
   1. Use the Convert Text to Columns Wizard to convert the text in the range B12:B15 to columns.
   2. Use only a space as the column delimiter. (Hint: Click OK to replace the data if the warning message comes up.)
   3. Change the width of columns B:G to 12.0 characters.
   4. Add totals to the historical data as follows
2. Import the data into cell B3.
3. Also specify that the data is delimited only witha comma.
4. Specify that the data in the text file has headers.

In cell C16, create a formula using the SUM function that total the values in the range C12:C15. Copy the formula in cell C16 to the range D16:G16.

1. To make the Les Fleurs worksheet consistent with the other worksheets, go to the Les Fleurs worksheet and replace all instances of the word Quarter with the letter Q (Hint: you should make four replacements).
2. To help analyze Les Fleurs’s historical data, select the range C12:G15 and apply a Data Bars conditional formatting rule using Solid Fill Blue Data Bars.
3. Make the pie chart in the Les Fleurs Revenue Chartworksheet more attractive as follows:
   1. Change the chart style to Style 3.
   2. Format the data series to vary colors by slice. (Hint: In the Format Data Series task pane, display the Fill options.)
   3. The Bloom store provided sales data in an Access database. In cell B11 of the Bloom worksheet, import data from the Sales table in the Support_SC_EX16_7b_Bloom.accdb Access database file.
   4. To make the data in the Bloom worksheet consistent with the data for the other events companies, format the data and the worksheet as follows:
      1. Convert the imported Access table to a range.
      2. Change the width of columns B:G to 12.0 characters.
      3. Apply the Accounting number format with zero decimal places and $ as the symbol to the range C4:G8.
      4. Use the Format Painter to copy the formatting of the range B2:G8 to the range B10:G16.
      5. To help analyze the Bloom store’s sales in 2020, create a chart as follows:
         1. For the range B3:F7, insert a Clustered Bar chart with each quarter as a data series.
         2. Move the clustered bar chart to a new chart sheet named 2020 Bloom Chart. (Hint: Don’t type the period.)
         3. Move the 2020 Bloom Chart chart sheet after the Bloom worksheet.
         4. To coordinate the new chart with the Les Fleurs revenue chart, make the following changes to the 2020 Bloom chart:
            1. Change the chart style to Style 3.
            2. Add a Primary Horizontal Axis Title using Sales as the title.
            3. Use Bloom as the chart title.

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.