Shelly Cashman Excel 2019 | Module 7: SAM Critical Thinking Project 1c Ballantyne Investments IMPORT DATA AND WORK WITH SMARTART AND IMAGES   GETTING STARTED Open the file SC_EX19_CT7c_FirstLastName_1

Subject:MS ExcelPrice: Bought3

Share With

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

Ballantyne Investments

IMPORT DATA AND WORK WITH SMARTART AND IMAGES

 

• GETTING STARTED

• Open the file SC_EX19_CT7c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX19_CT7c_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_7c_Disclaimer.pptx
  • Support_EX19_7c_Group.jpg
  • Support_EX19_7c_Sample.xlsx
  • Support_EX19_7c_Team.html
• With the file SC_EX19_CT7c_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.
• When opening your start file or the Graded Summary Report, you may be prompted to update the links. Select Don't Update in the dialog box.

• PROJECT STEPS

1. Elena Dorado is an associate investment banker at Ballantyne Investments. She is preparing a draft of a workbook that will be presented to shareholders to describe Ballantyne's asset management team and client relationships. She asks for your help in importing data and adding other content to the workbook.
   
   Go to the Asset Management worksheet. Change the formatting of cell D4 and the WordArt containing the worksheet title "Ballantyne Investments" as follows to coordinate with the rest of the worksheet:
   1. Apply the Fill: Turquoise, Accent Color 1; Shadow WordArt Style to the title.
   2. Copy the formatting from the "Name" heading to the "Years Experience" heading.
2. The worksheet should list information about the asset management team, which is contained in a webpage. Copy data from the webpage as follows:
   1. Open the webpage Support_EX19_7c_Team.html in a browser.
   2. In the Asset Management worksheet, import the data from the Support_EX19_Team webpage in cell G12.
   3. Format the imported data as a table using Turquoise, Table Style Light 9. Remove the external connection, if prompted.
3. In the imported table, change some data to reflect updates in the company:
   1. Delete the row for Employee 520 because Chad Johnson is no longer with Ballantyne Investments.
   2. Find the text "Financial advisor" and replace it with Investments to use a position name the company recently updated.
4. Elena wants to list the team information in the shaded range on the left. The webpage table separated the first and last names, but Elena wants to list full names on the Asset Management worksheet. List the first and last names of each team member in a single cell as follows:
   1. For the first employee's name in the shaded range, use a function to concatenate the first name followed by a space and then the last name for the first employee from the imported data.
   2. Fill the remainder of the Name column with the new formula to list the full names of the remaining team members.
5. Use the imported data to fill in the rest of the table as follows:
   1. Copy the Position data from the imported data and paste only the values in the Position column of the shaded range.
   2. For the first employee's Specialty data in the shaded range, use a function to capitalize the first letter in each word in the Specialty text for the first employee from the imported data. Fill the remainder of the Specialty column with the new formula to list the specialties of the remaining team members.
   3. For the first employee's Years Experience data in the shaded range, use a function to insert the first two characters from the Experience text for the first employee from the imported data. Fill the remainder of the Years Experience column with the new formula to list the years of experience of the remaining team members.
   4. For the first employee's From data, use a function to insert the last two characters from the BirthPlace text for the first employee from the imported data.
   5. Resize the first four columns to their best fit, resize the next two columns to 7.00, and size column G to 21.00. [Mac Hint: Size the first three columns to 20.00, size the fourth column to 15.00, resize the next two columns to 7.00, and size column G to 21.00.]
   6. Hide the rows that contain the imported data so that the worksheet does not display duplicated data.
6. Elena already imported data summarizing the performance of the asset management team on the Performance Summary worksheet but wants to display the data in the range G5:H8 of the Asset Management worksheet. She asks you to switch the rows and columns when you insert the data to fit in the Performance Summary table.
   
   Go to the Performance Summary worksheet. Copy the data in the shaded range, and then transpose the rows and columns as you paste the data on the Asset Management worksheet below the "Performance Summary" heading.
7. In the Asset Management worksheet, insert and format a picture of the asset management team as follows so that shareholders can identify members of the team:
   1. Insert the picture Support_EX19_7c_Group.jpg.
   2. Move and resize the picture proportionally so that it covers the range G19:J36.
   3. Add a border to the picture using Teal, Accent 6 as the border color to coordinate the picture with the rest of the worksheet.
   4. Apply the Offset: Center picture effect from the Shadow gallery.
8. Add a caption to identify the picture as follows:
   1. In cell H37, insert a text box to create the caption.
   2. Enter Asset management team at work in the text box.
   3. Display the entire caption and move the text box so that it is centered below the picture in the range G37:J38.
9. Elena also wants to provide an organization chart to show the hierarchy of the asset management team. Insert an organization chart as follows:
   1. Insert an organization chart from the SmartArt gallery.
   2. Move and resize the SmartArt so that the upper-left corner is in cell A19 and the lower-right corner is in cell D36.
10. Add text to the SmartArt as follows, using Figure 1 as a guide:
    1. Enter Frye in the top shape.
    2. Add a shape after the second shape so that it appears to the right of the second shape.
    3. In the second row, enter the following names from left to right, one name per shape: Montgomery, Garcia.
    4. In the bottom row, enter the following names from left to right, one name per shape: Lattimer, Gao, Krause.
    5.  
    6. Add a caption to identify the SmartArt as follows:
       1. In cell B37, insert a text box to create the caption.
       2. Enter Org chart in the text box.
       3. Resize the text box to display the entire caption. Align the top of both captions in row 37.
    7. Hide the gridlines on the worksheet to increase its visual appeal.
    8. Elena wants to include the company slogan and a standard disclaimer about Ballantyne Investments' services on the Asset Management worksheet. She has this information stored in a PowerPoint presentation. Include the information as follows:
       1. Use PowerPoint to open the presentation Support_EX19_7c_Disclaimer.pptx.
       2. In Excel, use the Screen Clipping tool to paste a screenshot of only the slogan and disclaimer into the Asset Management worksheet.
       3. Position the upper-left corner of the screenshot image in cell B40.
    9. Go to the Client Investment Tracker worksheet, which Elena asks you to finish. The worksheet shows sample data for a fictional client to illustrate the type of information Ballantyne Investments provides to its clients.
       
       Elena inserted the data in the range A20:C28 as a link to another worksheet. Complete the sample data and break the link as follows:
       1. Use the Quick Analysis tool to calculate the total investment amount below the values in the Investment column. This total should match the total at the bottom of the Total Invested column. [Mac Hint: Instead of using the Quick Analysis tool, select the appropriate cells and use the tools on the Home tab of the Ribbon.]
       2. To help clients visualize the data, use the Quick Analysis tool to create a conditional formatting rule that adds solid fill blue data bars to the values in the Total % Gain (Loss) column to help clients visualize the data. [Mac Hint: Instead of using the Quick Analysis tool, select the appropriate cells and use the tools on the Home tab of the Ribbon.]
       3. Break the link to the Support_EX19_7c_Sample.xlsx workbook because Elena no longer needs to update the date.
    10. Elena wants to show the total amount invested compared to the total value during the eight months of investments. Insert a chart in the Client Investment Tracker worksheet as follows to show this information:
        1. Based on the headings and data in the Date, Total Value, and Total Invested columns, insert the first type of chart that Excel recommends, which is a Clustered Column – Line chart.
        2. Move and resize the chart so that it covers the range A5:F19.
        3. Remove the chart title because the legend identifies the data clearly.
    11. Elena wants to make sure that people reviewing the worksheet understand it displays sample data. Add a shape to the worksheet as follows to provide this information:
        1. In cell E30, insert a Callout: Line shape from the Callouts section of the Shapes gallery.
        2. Move the callout line so that it points to the bottom of the Total Invested column.
        3. Type Sample data only in the callout shape.
        4. Apply the Subtle Effect – Turquoise, Accent 1 shape style to the callout shape.
    12. Elena wants to format the column chart in the range G20:M33 to call attention to it. She also wants to change the layout of the chart so that it provides another way to compare the total amounts by month. Modify the column chart as follows:
        1. Switch the rows and columns so that the chart compares the total value and the total invested by months 1 to 8.
        2. Apply the Ice Blue, Background 2 shape fill color to the plot area of the chart.
        3. Apply the Offset: Bottom Right shadow shape effect to the legend.
    13. Elena wants to add one more element of visual interest on the worksheet.
        1. Insert an online picture using investments as the search text. Select the first picture in the search results. Delete the text box underneath that contains the attribution. (Hint: The picture may differ from the image in the Final Figure.)
        2. Reposition and resize the picture so that it covers the range G1:K13.
    14. 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.