Homework answers / question archive / Illustrated Excel 2019 | Module 7: SAM Project 1a Chastain Group EXCHANGE DATA WITH OTHER PROGRAMS GETTING STARTED Open the file IL_EX19_7a_FirstLastName_1

Illustrated Excel 2019 | Module 7: SAM Project 1a Chastain Group EXCHANGE DATA WITH OTHER PROGRAMS GETTING STARTED Open the file IL_EX19_7a_FirstLastName_1

MS Excel

Share With

Illustrated Excel 2019 | Module 7: SAM Project 1a

Chastain Group

EXCHANGE DATA WITH OTHER PROGRAMS

• GETTING STARTED

• Open the file IL_EX19_7a_FirstLastName_1.xlsx, available for download from the SAM website. Click Continue to start the project.
• Save the file as IL_EX19_7a_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_7a_365.png
  • Support_EX19_7a_365.xlsx
  • Support_EX19_7a_Banner.jpg
  • Support_EX19_7a_Combined.csv
  • Support_EX19_7a_Compare.docx
  • Support_EX19_7a_Summary.pptx
• With the file IL_EX19_7a_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. Ravi Chambal is a partner at the Chastain Group, a business consulting firm specializing in management and technology consulting. The Chastain Group wants to expand to include marketing consulting services by merging with an existing marketing company. Ravi is using an Excel workbook to compare the candidate companies. He asks for your help to improve the appearance of the workbook and include data stored in other files.
   Go to the Merger Options worksheet. In cell A1, insert the picture Support_EX19_7a_Banner.jpg.
2. Adjust the banner picture as follows to make it more attractive:
   1. Apply the Brightness +20% Contrast +20% correction to the picture.
   2. Sharpen the picture by 50% to remove the blurriness.
   3. Add the Tight Reflection: Touching effect from the Reflection gallery.
3. Ravi needs to add another shape and picture to the SmartArt on the worksheet to show four marketing companies instead of three. He also wants to indicate that one company specializes in technology marketing.
   Add a shape and picture to the SmartArt as follows:
   1. Add a shape after the D&M shape.
   2. Type 365 in the new shape.
   3. Use the picture placeholder to add the picture Support_EX19_7a_365.png to the SmartArt.
   4. Add another shape after the BrandUp shape.
   5. Type Tech in the new shape, and then demote the new shape.
4. Format the SmartArt as follows so that it coordinates more closely with the rest of the worksheet:
   1. Change the SmartArt colors to Colored Fill – Accent 1.
   2. Apply the Moderate Effect SmartArt style.
5. Ravi wants to include an image of a PowerPoint slide he created summarizing the four marketing companies. Include the image as follows:
   1. Use PowerPoint to open the presentation Support_EX19_7a_Summary.pptx.
   2. In Excel, use the Screen Clipping tool to paste a screenshot of Slide 1: Marketing Consultants into the Merger Options worksheet.
   3. Position the upper-left corner of the screenshot image in cell I1.
6. Go to the Revenue Comparison worksheet. Add a Black, Text 1, Lighter 50% border to the Chastain Group picture to match the same picture on the Merger Options worksheet.
7. Position and format the picture of the four logos as follows to improve the appearance of the worksheet:
   1. Copy the formatting of the Chastain Group picture to the picture of the four logos.
   2. Without moving the logos picture to the left or right, align the top of the logos picture with the top of the Chastain Group picture.
8. Ravi has a Word document with a table that compares the total 2021 revenues for the four companies.
   Embed the Word table in the Revenue Comparison worksheet as follows:
   1. Use Word to open the document Support_EX19_7a_Compare.docx.
   2. Copy the table and then paste it into the Revenue Comparison worksheet starting in cell A21. Use the Keep Source Formatting option.
9. The embedded data includes only an estimate for the D&M Group total revenues.
   In cell B24, edit the embedded data to use 279,150 as the total revenues for the D&M group.
10. Ravi has the data comparing the revenues for the four companies stored in a text file.
    Import the text file as follows:
    1. Get data from the Text/CSV file Support_EX19_7a_Combined.csv.
    2. Edit the text file before loading it to use the first row as headers.
    3. In the Power Query Editor window, choose to close and load to a location in the worksheet.
    4. View the imported data as a table and insert the data in cell A27 of the existing worksheet.
    5. Apply the Light Gray, Table Style Medium 22 to the imported table to coordinate with the rest of the worksheet contents, and then format the range B28:H31 in the Accounting number format with no decimal places.
11. Ravi originally inserted the 365 Marketing revenue data as a link to another Excel workbook. He asks you to update the data and then remove the link.
    Update and remove the link as follows:
    1. Display the links to external files, and then update the link to the workbook Support_EX19_7a_365.xlsx.
    2. Break the link to the Support_EX19_7a_365.xlsx file.

Your workbook should look like the Final Figures on the following pages. Close all support files. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.