New Perspectives Excel Module 7 SAM Critical Thinking Project 1c Ballantyne Investments

Excel 365/2021 | Module 7: SAM Critical Thinking Project 1c | Ballantyne Investments #shellycashman
If you directly want to get the project from us then contact us on our Whatsapp. Link is given here,
Whatsapp Contact Link:
wa.me/message/...
Whatsapp Contact Number:
[phone]
Email ID:
[e-mail]
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:
a. Apply the Fill: Turquoise, Accent Color 1; Shadow WordArt Style to the title.
b. 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:
a. Open the webpage Support_EX365_2021_CT7c_Team.html in a browser.
b. In the Asset Management worksheet, import the data from the Support_EX365_2021_CT7c_Team.html webpage in cell G12.
c. 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:
a. Delete the row for Employee 520 because Chad Johnson is no longer with Ballantyne Investments.
b. 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:
a. 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.
b. 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:
a. Copy the Position data from the imported data and paste only the values in the Position column of the shaded range.
b. 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.
c. 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.
d. 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.
e. 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.]
f. 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: