Shelly Cashman Excel 2019 | Module 10: SAM Project 1b WebSmith ANALYZING DATA WITH POWER TOOLS AND CREATING MACROS   GETTING STARTED Open the file SC_EX19_10b_FirstLastName_1

Subject:MS ExcelPrice: Bought3

Share With

Shelly Cashman Excel 2019 | Module 10: SAM Project 1b

WebSmith

ANALYZING DATA WITH POWER TOOLS AND CREATING MACROS

 

• GETTING STARTED

• Open the file SC_EX19_10b_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as SC_EX19_10b_FirstLastName_2.xlsm by changing the “1” to a “2”.
  • If you do not see the .xlsm 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_10b_Completed.xlsx
• With the file SC_EX19_10b_FirstLastName_2.xlsm 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.
• To complete this project, you need to display the Power Pivot and Developer tabs. If these tabs do not display, right-click any tab on the ribbon, and then click Customize the Ribbon on the shortcut menu. In the Main Tabs area of the Excel Options dialog box, click the following check boxes:
  • Developer
  • Layout
  • Power Pivot
    
    Click the OK button to close the Excel Options dialog box and add the Developer tab, the Power Pivot tab, and the buttons for the Power tools to the ribbon.

• PROJECT STEPS

1. Woon Seong runs a small web design agency in Champaign, Illinois, and is developing an Excel workbook that her partners can use to track and interpret data about clients, projects, and invoices. She asks for your help in setting up tools to analyze the data and to automate the workbook to make it easier to use.
   Go to the Home Page worksheet. Woon wants to include a list of links to resources she and her partners are likely to need. However, the first link she added is unnecessary.
   Remove the hyperlink from the text in cell B3.
2. Woon wants to make it easy for users to navigate to the other worksheets. Add links to other places in the workbook as follows:
   1. In cell B4, insert a link to cell A1 of the Clients worksheet.
   2. In cell B5, insert a link to cell A1 of the Projects worksheet.
   3. In cell B6, insert a link to cell A1 of the Invoices worksheet.
3. Woon also wants users to be able to access information about completed projects as they start working on new ones. Woon stores the completed project data in an Excel workbook. Add a link to the file as follows:
   1. In cell C4, insert a link to the file Support_EX19_10b_Completed.xlsx.
   2. Add the following text as a ScreenTip that explains what happens when users click the link:
      Open a workbook of past project data.
4. WebSmith displays their work for clients on their website. In cell C5, add a link to the www.ws.example.com website.
5. Woon wants to include a way that users can request help from the technical support firm the company uses. In cell C6, add a link to the info@support.example.com email address.
6. Woon's plan is that when users click the Add Client button in cell C1, the Clients worksheet opens and Excel adds a new, blank record to the Client table, where users can insert the new client information.
   Record a macro to automate the Add Client button as follows:
   1. Enable all macros in the workbook (if necessary), and then display the Developer tab on the ribbon.
   2. Begin recording a macro stored in this workbook using Add_Client as the name of the macro.
   3. Display the Clients worksheet, and then add a new blank record to the end of the Client table.
   4. Stop recording the macro.
   5. Assign the Add_Client macro to the Add Client button on the Home Page worksheet and then run the Add_Client macro to make sure it inserts a new record at the bottom of the Client table on the Clients worksheet.
7. Woon has already created similar macros to add a new project record and a new invoice record. Return to the Home Page worksheet, and then assign the macros to buttons as follows:
   1. Assign the Add_Project macro to the Add Project button.
   2. Assign the Add_Invoice macro to the Add Invoice button.
8. Go to the Clients worksheet. Woon wants to create PivotTables that include client, project, and invoice data. She already added the tables containing this data to the Data Model. Woon wants to list and total each project amount by city and signing date. Create a PivotTable that displays this information as follows:
   1. Use Power Pivot to create a PivotTable on a new worksheet, using Projects by City as the name of the worksheet.
   2. Display the City field values from the Client table data source as row headings.
   3. Display the SigningDate field values from the Project table data source as column headings.
   4. Add the ProjectAmt field from the Project table data source to the Values area to sum the field values.
   5. Group the signing dates to display them as years and quarters only. (Hint: Expand the year headings to display the quarter headings, if necessary.)
9. Woon also wants to list the paid and unpaid invoices for each month and then filter them by project type. Create a PivotTable that displays this information as follows:
   1. Use Power Pivot to create a PivotTable on a new worksheet, using Invoices by Month as the name of the worksheet.
   2. Display the SigningDate field values from the Project table data source as row headings.
   3. Display the InvoicePaid field values from the Invoice table data source as column headings.
   4. Sum the InvoiceAmt field values from the Invoice table data source.
   5. Use the ProjectType field from the Project table data source as a filter.
   6. Group the signing dates to display them as years and months only.
10. To properly combine data from the Client, Project, and Invoice tables, create relationships between tables as follows:
    1. In the Power Pivot for Excel window, create a relationship between the Client and Project tables using the ClientID column to relate the tables.
    2. Create a relationship between the Project and Invoice tables using the ProjectNum column to relate the tables.
11. Woon decides to show invoices for only certain project types and wants to make the PivotTable easier to interpret. Modify the PivotTable as follows to meet Woon's requests:
    1. Use Unpaid as the column heading in cell C4.
    2. Use Paid as the column heading in cell D4.
    3. Filter the PivotTable to display invoices for the Convert to responsive design and Update appearance project types.
12. Return to the Clients worksheet. In the range J2:K5, Woon wants to list the total project amounts for three cities. Extract this information from the PivotTable on the Projects by City worksheet as follows:
    1. In cell K3, use the GETPIVOTDATA function to display the total project amount for Champaign from the PivotTable on the Projects by City worksheet (cell J8). (Hint: Type an equal sign in cell K3 and then click the cell containing the value you want the GETPIVOTDATA function to get from the PivotTable.)
    2. In cell K4, use the GETPIVOTDATA function to display the total project amount for Urbana from the PivotTable on the Projects by City worksheet (cell J12).
    3. In cell K5, use the GETPIVOTDATA function to display the total project amount for Bloomington from the PivotTable on the Projects by City worksheet (cell J7). Save the workbook, making sure to save it as a macro-enabled workbook.

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.