MOS Excel 2019 | Module 4: SAM Project 1a Prism Real Estate IMPORT DATA, FORMAT WORKSHEETS, AND INSERT FORMULAS   GETTING STARTED Open the file SAM_EX19_MOS_4a_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought8

Share With

MOS Excel 2019 | Module 4: SAM Project 1a

Prism Real Estate

IMPORT DATA, FORMAT WORKSHEETS, AND INSERT FORMULAS

GETTING STARTED

• Open the file SAM_EX19_MOS_4a_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SAM_EX19_MOS_4a_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.
• With the file SAM_EX19_MOS_4a_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. Kendra Han is an administrative assistant for the Prism Real Estate Agency in Princeton, New Jersey. She is using an Excel workbook to consolidate information about the firm's agents and sales. She asks for your help with incorporating text data and inserting links and charts to complete the workbook.
   
   Go to the Agents worksheet. Kendra plans to print this worksheet for a graphic designer who is creating a brochure for the agency.
   
   Apply the Office theme to coordinate with other Prism Real Estate workbooks.
2. Kendra imported the data in the range A16:F28 from another source and needs to transfer the data to the range A4:E15, which is where the brochure information should appear. The imported data separates first and last names, but Kendra wants to list the full names on the Agents worksheet. List the first and last names of each agent in a single cell as follows:
   1. In cell A4, enter a formula using the CONCAT function that displays the first name shown in cell A17 followed by a space (" "), and then the last name shown in cell B17.
   2. Fill the range A5:A15 with the formula in cell A4 to list the full names of the agents.
3. The graphic designer needs to know how many characters each name contains, including spaces, so he can provide enough room in the brochure design. Insert the number of characters in each name as follows:
   1. In cell B4, enter a formula using the LEN function to count the number of characters, including spaces, in the first agent's name (cell A4).
   2. Fill the range B5:B15 with the formula in cell B4 to count the characters in the names of the other agents.
4. Incorporate the remaining imported data as follows:
   1. In cell C4, enter a formula using the LEFT function to insert the first 2 characters on the left of cell C17. Copy the formula in cell C4 to the range C5:C15.
   2. In cell D4, enter a formula using the RIGHT function to insert the last 4 characters on the right of cell D17. Copy the formula in cell D4 to the range D5:D15.
   3. In cell E4, enter a formula using the TEXTJOIN function to insert the text in cell E17 followed by the text in cell F17, separated by a comma and a space (", "), and ignoring empty cells. Copy the formula in cell E4 to the range E5:E15.
5. Kendra wants to provide a commission calculator for agents so that they can quickly estimate their commissions on sales. Calculate the commissions in the range H5:M15 as follows:
   1. In cell H5, enter a formula without using a function that multiplies the sales amount in cell G5 by the commission percentage in cell H4.
   2. Change the cell references to mixed cell references so that the formula always refers to column G and row 4 when copied.
   3. Copy the formula in cell H5 to the range I5:M5, and then to the range H6:M15.
6. Kendra asks you to format merged cell N3 to better display its contents and to complete the list of links in the range O3:O6.
   1. Rotate the contents of cell N3 by 90 degrees to display the complete text of the cell.
   2. In cell O3, insert a hyperlink to the website prism.example.com to provide access to the agency's website.
   3. Add a ScreenTip to the hyperlink in cell O3 that displays the following text:
      Prism home page
   4. In cell O4, insert a hyperlink to the January Sales worksheet.
7. Insert a row above row 16 to separate the imported data from the brochure data, formatting the new row the same as the one below it.
8. When Kendra prints the worksheet, she does not want to include the imported data or the commission information. She does want to provide a phone number so that the graphic designer can contact her if necessary. Prepare the worksheet for printing as follows:
   1. Set the print area to include the range A1:E15.
   2. In the center section of the header for the Agents worksheet, type the following text: Kendra Han (609) 555-3422
9. Go to the January Sales worksheet, which contains agent sales data for January. Kendra does not need to scroll this worksheet horizontally but does want to display the first seven rows as she scrolls vertically. Unfreeze and freeze panes as follows:
   1. Unfreeze the first column.
   2. Freeze the first seven rows.
10. Complete and format the data in the first five rows of the worksheet as follows:
    1. Middle Align the worksheet title in cell A1 vertically to match the format of the Agents worksheet.
    2. Format cell B2 as Text to prepare for entering an employee ID, which has a leading zero.
    3. In cell B2, enter 0253 as Kendra's employee ID.
    4. In cell B5, enter a formula without using a function that divides the total sales from last year (cell B3) by 12 and then multiples the result by the targeted increase (cell B4).
    5. Wrap the text in cell A5 to display the entire contents of the cell.
    6. Add a bottom border to the range A5:E5 using the second dotted style in the Style list and the Blue-Gray, Text 2 color to separate the overview information from the agent sales data.
11. In the range E8:E19, Kendra wants to list each agent's email address, which all follow the same pattern. Type bob.coleman@example.net in cell E8. Select the range E8:E19, and then use Flash Fill to enter the remaining email addresses. (Hint: You must use Flash Fill to receive proper credit for this step.)
12. Complete and format the summary information as follows:
    1. Indent the range A23:A24 to clarify that the information is part of the SUMMARY section.
    2. In cell B23, insert a formula using the COUNTBLANK function to count the blank sales in the range C8:C19 and identify the number of agents with no sales in January.
    3. Clear the formatting from the range A25:B25 to keep the note separate from the summary data.
13. Kendra plans to create worksheets for each month and wants to format totals as they are shown on the January Sales worksheet. Create a custom cell style named SalesTotal based on the formatting in cell C20.
14. Insert a column to the left of column F, formatting the new column the same as the one on the right, to separate the agent demographics from the rest of the worksheet.

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.

 Final Figure 1: Agents Worksheet

 Final Figure 2: January Sales Worksheet