New Perspectives Excel 365 | Module 12: End of Module Project 1 Addison Creative

GETTING STARTED

1. Save the file NP_EX365_EOM12-1_FirstLastName_1.xlsm as NP_EX365_EOM12-1_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. 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. For PC: 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.

b. For Mac: If a dialog box about macros appears, click Enable Macros.

3. With the file NP_EX365_EOM12-1_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.

PROJECT STEPS

1. Grady MacNeil is the accounts manager at Addison Creative, a marketing company in White Plains, New York. He is developing an Excel workbook to track fourth quarter sales prospects and invoice payments, which he and his staff record weekly. He asks for your help in automating the workbook. Go to the Prospects worksheet and then unprotect it so that you can edit the contents.

2. In the range B4:C8, Grady listed the current number of potential clients at each stage of the sales process. He asks you to create a chart that illustrates the data. Create a funnel chart based on the data in the range B3:C8. Change the chart colors to Monochromatic Palette 5 to coordinate with the worksheet design. Use Sales Stages as the chart title. Move the chart so it covers the range E3:K15.

3. Go to the Invoices worksheet, which lists invoice data for October and the first week in November. The Total payments value in cell I11 and the Total amount value in cell J11 are each 0 because they depend on data in the Invoices table (the range B3:F204) that has not been entered yet. Grady asks you to resolve the error in cell K11. Determine why cell K11 displays a #DIV/0! error, and then add an IFERROR function to the formula that displays a blank cell ("") if the calculation returns an error.

4. Go to the Payments worksheet. Grady and his staff use this worksheet to enter invoice payments and summarize invoice information. He wants you to make it easy to record the payments. After users enter the invoice ID, date due, date paid, and amount paid, Grady wants them to click the Enter Payment button to add a record to the Late_Payments table on the Invoices worksheet. Record a macro named New_Payment stored in the current workbook. With the macro recording, perform the following steps: · Go to the Invoices worksheet, and then click cell H4. · Insert a table row above, click an empty cell, then click cell H4 if it is not already selected. · Go to the Payments worksheet, and then copy the data in the range C4:C7. · Return to the Invoices worksheet, click the Paste arrow, and then click the Transpose option. · Select cell G1. · Return to the Payments worksheet, press ESC, and then click cell B17. · Stop recording the macro.

5. Grady asks you to display a confirmation message after users add a payment record to the Late_Payments table. Edit the New_Payment macro in the Visual Basic window. After the line of code that selects cell B17 (Range("B17").Select), insert the following new lines of code, and then save and close the macro. ActiveCell.FormulaR1C1 = "Payment information entered" Range("C4").Select Assign the New_Payment macro to the Enter Payment button (a rounded rectangle shape).

6. Enter the data on the Payments worksheet as indicated in Table 1, and then click the Enter Payment button to run the New_Payment macro and test that the confirmation message has been added correctly.

Table 1: Data for the Range C4:C7

| C 4 | INV-1161 5 | 11/6/2029 6 | 11/15/2029 7 | 4000

7. Grady created a macro named Clear that clears the data in the range C4:C7 as well as the confirmation message. He assigned the Clear macro to the Clear Entries button, and asks you to test the assigned macro. Click the Clear Entries button to run the Clear macro.

8. Grady asks you to provide a way to avoid errors when entering payment information. In cell C4, add a data validation list that accepts only the values in the range I5:I205.

9. In cell C5, create another data validation rule that allows date values greater than or equal to 10/1/2029. Add an input message to the cell with the title Due date and use Enter a date of 10/1/2029 or later. as the input message. Add a Warning alert with the title Invalid date as use Verify the due date. as the error message.

10. Hide column I to avoid distracting users with the Invoice ID list.

11. Grady wants you to display statistics about invoice payments in the range E5:G15. This information is stored in the Summary PivotTable on the Invoice Summary worksheet. In the merged cell E5, use the GETPIVOTDATA function to display the total number of invoices from cell C10 in the Summary PivotTable on the Invoice Summary worksheet. In the merged cell G5, display the total amount billed from cell D10 of the Summary PivotTable. In the merged cell G9, display the total amount paid from cell E10 of the Summary PivotTable.

12. To prevent users from modifying the formulas, Grady wants you to protect the Payments worksheet. Unlock cells C4:C7 so that users can enter data in the range. Protect the worksheet without using a password.

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: Prospects Worksheet

Final Figure 2: Invoices Worksheet

Final Figure 3: Payments Worksheet

Final Figure 4: Invoice Summary Worksheet