Shelly Cashman Excel 365 | Module 10: SAM Project A Lincoln Construction

GETTING STARTED

1. Save the file SC_EX365_10A_FirstLastName_1.xlsm as SC_EX365_10A_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_10A_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_10A_History.xlsx

4. With the file SC_EX365_10A_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 text in cell C4, 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. Add links to other places in the workbook as follows:

a. In cell C5, insert a link to cell A1 of the Clients worksheet.

b. In cell C6, insert a link to cell A1 of the Projects worksheet.

c. In cell C7, insert a link to cell A1 of the Invoices 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. In cell C8, insert a link to the file Support_EX365_10A_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. In cell C9, 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. In cell C10, 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.

d. Also, while recording the macro, add a new blank record to the end of the Client table.

e. Stop recording the macro.

f. 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 macros to buttons as follows:

a. Assign the Add_Project macro to the Add Project button.

b. Assign the Add_Invoice macro to the Add Invoice button.

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 as follows:

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 from the Client data source as row headings.

c. Display the StartDate field values from the Project data source as column headings.

d. Add the ProjectAmt field from the Project data source to the Values area to sum the field values. (Ignore the alert that states "Relationships between tables may be needed." because these relationships will be created in a later step.)

e. Group the start dates to display them as years and quarters only. (Hint: If necessary, remove any extra StartDate field appearing in the Columns area.)

9. Format the PivotTable on the Projects by City worksheet as follows to make it easier to interpret:

a. Expand the year headings to display the quarter headings.

b. Display 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 as follows:

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 from the Project data source as row headings.

c. Display the InvoicePaid field values from the Invoice data source as column headings.

d. Sum the InvoiceAmt field values from the Invoice data source. (Ignore the alert that states "Relationships between tables may be needed." because these relationships will be created in a later step.)

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

11. Format the PivotTable on the Invoices by Month worksheet as follows to make it easier to interpret:

a. Expand the year headings to display the month headings.

b. Display 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 as follows:

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 as follows to meet Corey's requests:

a. Use Unpaid as the column heading in cell C4.

b. Use Paid as the column heading in cell D4.

c. Add the ProjectType field from the Project data source as a filter.

d. Filter the PivotTable to display invoices for the Renovation and Restoration project types only.

14. Return to the Clients worksheet. In the range J2:K5, Corey asks you to list the total project amounts for three cities. Extract this information from the PivotTable on the Projects by City worksheet as follows:

a. In cell K3, use the GETPIVOTDATA function to display the total project amount for Lincoln from the PivotTable on the Projects by City worksheet (cell J10). (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.)

b. In cell K4, use the GETPIVOTDATA function to display the total project amount for Omaha from the PivotTable on the Projects by City worksheet (cell J14).

c. In cell K5, use the GETPIVOTDATA function to display the total contract amount for Scottsbluff from the PivotTable on the Projects by City worksheet (cell J15). 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