Shelly Cashman Excel 2019 | Module 10: SAM Critical Thinking Project 1c Franklin National ANALYZE DATA WITH POWER TOOLS AND CREATE MACROS   GETTING STARTED Open the file SC_EX19_CT10c_FirstLastName_1

Subject:MS ExcelPrice: Bought3

Share With

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

Franklin National

ANALYZE DATA WITH POWER TOOLS AND CREATE MACROS

 

• GETTING STARTED

• Open the file SC_EX19_CT10c_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as SC_EX19_CT10c_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_CT10c_Archive.xlsx
• With the file SC_EX19_CT10c_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. Ravi Mehta is the finance director for the Home Security Group of Franklin National, a North American manufacturing company. Ravi is developing an Excel workbook that his managers can use to track and interpret data about customers, contracts, and invoices. He asks for your help in setting up tools to analyze the data and to automate the workbook in order to make it easier to use. Go to the Home Page worksheet. Ravi wants to include a list of links to resources his managers are likely to need. However, the first link he added is unnecessary.  Remove the hyperlink from the Links text.
2. Ravi wants to make it easy for users to navigate to the other worksheets. Add links to other places in the workbook as follows:
   1. Create a link from the Customers text to the Customers worksheet.
   2. Create a link from the Contracts text to the Contracts worksheet.
   3. Create a link from the Invoices text to the Invoices worksheet.
3. Ravi also wants users to be able to access archived contract data as they prepare new contracts. Ravi stores the archived data in an Excel workbook. Add a link to the file as follows:
   1. Create a link from the Archived data text to the file Support_EX19_CT10c_Archive.xlsx.
   2. Add the following text as a ScreenTip that explains what happens when users click the link:
      Open an archive of contract data.
4. The Franklin National website provides information Ravi's managers use every day. Create a link from the Franklin National website text to the www.fn.example.com website.
5. Ravi also wants to include a way for managers to request information from the home office of Franklin National. Create a link from the Contact Franklin National text to the info@fn.example.com email address.
6. Ravi's plan is that when users click the Add Customer button in the range D3:E3, the Customers worksheet opens and Excel adds a new, blank record to the customer table, where users can insert the new customer information. Record a macro to automate the Add Customer button as follows:
   1. Enable all macros in the workbook, and then record a macro stored in this workbook using Add_Customer as the name of the macro.
   2. While recording the macro, display the Customers worksheet, and then add a new blank record to the end of the customer table. Stop recording the macro.
   3. Return to the Home Page worksheet. Assign the Add_Customer macro to the appropriate button, and then run the Add_Customer macro to make sure it works correctly.
7. Ravi has already created similar macros to add a new contract record and a new invoice record. Return to the Home Page worksheet, and then assign the Add_Contract and Add_Invoice macros to the appropriate buttons.
8. Go to the Customers worksheet. Ravi wants to create PivotTables that include customer, contract, and invoice data. He already added the tables containing this data to the Data Model. Ravi wants to list and total each contract 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 Contracts by City as the name of the worksheet.
   2. Display the City data from the Customer Query as row headings and the SigningDate data from the Contract Query as column headings, and then sum the ContractAmt values from the Contract Query.
   3. Display the signing dates as years and quarters only. (Hint: Expand the year headings to display the quarter headings, if necessary.)
9. Ravi also wants to list the paid and unpaid invoices for each month and then filter them by contract 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 data from the Contract Query as row headings and the InvoicePaid data from the Invoice Query as column headings, and then sum the InvoiceAmt values from the Invoice Query.
   3. Filter the PivotTable using the ContractType data from the Contract Query.
   4. Group the signing dates to display them as years and months only.
10. To properly combine data from the customer, contract, and invoice tables, create relationships between the tables in the Power Pivot for Excel window as follows:
    1. Create a relationship between the customer and contract tables using the CustomerID column to relate the tables.
    2. Create a relationship between the contract and invoice tables using the ContractNum column to relate the tables.
11. Ravi wants to show invoices for security plans only, and he wants to make the PivotTable easier to interpret. Modify the PivotTable as follows to meet Ravi's requests:
    1. From left to right, change the headings to Unpaid and Paid for the InvoiceAmt columns.
    2. Filter the PivotTable to display invoices only for contract types that begin with "Security".
12. Return to the Customers worksheet. In the Contracts by City section, Ravi wants to list the total contract amounts for three cities. Extract this information from the PivotTable on the Contracts by City worksheet as follows:
    1. To the right of the Atlanta label, display the grand total contract amount for Atlanta from the PivotTable on the Contracts by City worksheet.
    2. To the right of the College Park label, display the grand total contract amount for College Park from the PivotTable on the Contracts by City worksheet.
    3. To the right of the Dunwoody label, display the grand total contract amount for Dunwoody from the PivotTable on the Contracts by City worksheet. Save the workbook 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.