Shelly Cashman Excel 365 | Module 10: SAM Critical Thinking Project C Lincoln Construction

GETTING STARTED

1. Save the file SC_EX365_CT10C_FirstLastName_1.xlsm as SC_EX365_CT10C_FirstLastName_2.xlsm

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsm file extension, do not type it. The file extension will be added for you automatically.

2. SC_EX365_CT10C_FirstLastName_1.xlsm, available for download from the website. Files downloaded from the website are safe and do not contain viruses, but due to a recent Microsoft policy update, macros in downloaded files are disabled by default. To complete this project, you will need to enable macros in the file. To enable macros on this file:

a. Open Windows File Explorer and go to the folder where you saved the file. Right-click the file and choose Properties from the context menu. At the bottom of the General tab, select the Unblock checkbox and select Apply, and then click OK.

3. To complete this Project, you will also need the following files:

Support_EX365_CT10C_History.xlsx

4. With the file SC_EX365_CT10C_FirstLastName_2.xlsm open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

5. To complete this project, you need to display the Power Pivot and Developer tabs. If these tabs are not displayed, 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:

a. Developer

b. 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. Corey Hess is a financial analyst for Lincoln Construction, a company in Lincoln, Nebraska specializing in architectural restoration services. Corey is developing an Excel workbook to use for tracking and interpreting data about clients, projects, and invoices. He 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. Corey wants you to include a list of links to resources he and other managers are likely to need. However, the first link he added is unnecessary. Remove the hyperlink from the "Links" text, and then apply bold formatting and a 14-point font size to indicate the text is a list heading.

2. Corey asks you to make it easy to navigate to the other worksheets. For the "Clients," "Projects," and "Invoices" text, insert a link to cell A1 of the appropriate worksheet.

3. Corey also needs to be able to access data about past projects as he prepares proposals for new projects. He stores the historical data in an Excel workbook. Add a link to the file as follows:

a. For the "Past projects" text, insert a link to the file Support_EX365_CT10C_History.xlsx.

b. Add the following text as a ScreenTip that explains what happens when users click the link: Access past project data.

4. The Lincoln Construction website provides information the company managers use every day. For the "Lincoln website" text, add a link to the www.lc.example.com website.

5. Corey also wants you to include a way that he and other managers can request information from other Lincoln Construction offices. For the "Contact Lincoln Construction" text, add a link to the info@lc.example.com email address.

6. Corey's plan is that when users click the Add Client button on the Home Page worksheet, 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:

a. Enable all macros in the workbook, and then display the Developer tab on the ribbon.

b. Record a macro stored in this workbook using Add_Client as the name of the macro.

c. While recording the macro, display the Clients worksheet and then add a new blank record to the end of the Client table.

d. Stop recording the macro.

e. 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. Corey has already created similar macros to add a new project record and a new invoice record. Return to the Home Page worksheet and assign the Add_Project and Add_Invoice macros to the appropriate buttons.

8. Go to the Clients worksheet. Corey wants you to create PivotTables that include client, project, and invoice data. He already added the tables containing this data to the Data Model. Corey asks you to list and total each project amount by city and start date. Create a PivotTable that displays this information.

a. Use Power Pivot to create a PivotTable on a new worksheet, using Projects by City as the name of the worksheet.

b. Display the City field values as row headings, the StartDate field values as column headings, and then sum the ProjectAmt field values. (Ignore the alert about relationships because they will be created in a later step.)

c. Group the start dates to display them as years and quarters only.

9. Format the PivotTable on the Projects by City worksheet to make it easier to interpret by displaying the quarter headings below the year headings and formatting the ProjectAmt values using the Currency format with 0 decimal places and the $ symbol.

10. Corey also wants you to list the paid and unpaid invoices for each month and then filter them by project type. Create a PivotTable that displays this information.

a. Use Power Pivot to create a PivotTable on a new worksheet, using Invoices by Month as the name of the worksheet.

b. Display the StartDate field values as row headings and the InvoicePaid field values as column headings, and then sum the InvoiceAmt field values. (Ignore the alert about relationships because they will be created in a later step.)

c. Group the start dates to display them as years and months only.

11. Format the PivotTable on the Invoices by Month worksheet to make it easier to interpret by displaying the month headings below the year headings and formatting the InvoiceAmt values using the Currency format with 0 decimal places and the $ symbol.

12. To properly combine data from the Client, Project, and Invoice tables, create relationships between tables.

a. In the Power Pivot for Excel window, create a relationship between the Client and Project tables using the ClientID column to relate the tables.

b. Create a relationship between the Project and Invoice tables using the ProjectNum column to relate the tables.

13. On the Invoices by Month worksheet, Corey wants you to show invoices for renovation and restoration projects only and make the PivotTable easier to interpret. Modify the PivotTable to meet Corey's requests.

a. Use Unpaid to replace the "FALSE" column heading and use Paid to replace the "TRUE" column heading.

b. Add the ProjectType field as a filter, and then filter the PivotTable to display invoices for the Renovation and Restoration project types only.

14. Return to the Clients worksheet. In the "Projects by City" area, Corey asks you to list the total project amounts for three cities. Extract this information from the PivotTable on the Projects by City worksheet by using the GETPIVOTDATA function to display the total project amount for Lincoln, Omaha, and Scottsbluff. 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 website to submit your completed project.

Final Figure 1: Home Page Worksheet

Final Figure 2: Invoices by Month Worksheet

Final Figure 3: Projects by City Worksheet

Final Figure 4: Clients Worksheet

Final Figure 5: Projects Worksheet

Final Figure 6: Invoices Worksheet