New Perspectives Excel 2019 | Module 12: End of Module Project 1 Abernathy Academy   DEVELOP AN EXCEL APPLICATION     GETTING STARTED Open the file NP_EX19_EOM12-1_FirstLastName_1

Subject:MS ExcelPrice:20.01 Bought12

Share With

New Perspectives Excel 2019 | Module 12: End of Module Project 1

Abernathy Academy

 

DEVELOP AN EXCEL APPLICATION

• GETTING STARTED

• Open the file NP_EX19_EOM12-1_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as NP_EX19_EOM12-1_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.
• With the file NP_EX19_EOM12-1_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.

• PROJECT STEPS

1. Tarif Hajany is the admissions director at the Abernathy Academy, a private secondary school in Roswell, Georgia. He is developing an Excel workbook to track and record tuition payments from registered students, which he and his staff record weekly. He asks for your help in automating the workbook. Go to the Payments worksheet and then unprotect it so that you can edit the contents.
2. Tarif wants to include a title at the top of the worksheet similar to the ones used in the rest of the workbook. Insert WordArt using the Fill: Gray, Accent color 1; Shadow style. Type Payments by Account as the worksheet title. Change the font size to 28 point, and then move the WordArt to row 1 so that it spans columns C:F.
3. Tarif thought he would print the worksheet, but no longer plans to do so. Clear the print area from the worksheet.
4. Go to the Entry Form worksheet. The Admissions staff will use this worksheet to enter tuition payments and summarize tuition information. Tarif wants to make it easy to record the payments and avoid errors. In cell C3, add a data validation list that accepts only the values in the range I3:I203.
5. In cell C4, create another data validation rule that allows date values greater than or equal to 10/7/2021. Add an input message to the cell with the title Date of Payment and use Enter a date of 10/7/2021 or later. as the input message. Add a Warning alert with the title Invalid Date as use Verify the payment date. as the error message.
6. Hide column I to avoid distracting users with the Account ID list.
7. After users enter the account ID, date, and payment amount, Tarif wants them to click the Enter Payment button to add a record to the Late_Payments table on the Payments worksheet. Record a macro named Enter_Payment stored in the current workbook. With the macro recording, perform the following steps:
   · Go to the Payments worksheet, and then click cell H4.
   · Insert a table row above, click an empty cell, then reclick on cell H4.
   · Go to the Entry Form worksheet, and then copy the data in the range C3:C5.
   · Return to the Payments worksheet, click the Paste arrow, and then click the Transpose option.
   · Select cell L1.
   · Return to the Entry Form worksheet, press ESC, and then click cell B15.
   · Stop recording the macro.
8. Tarif planned to display a confirmation message after users add a payment record to the Late_Payments table. Edit the Enter_Payment macro in the Visual Basic window. After the line of code that selects cell B15 (Range("B15").Select), insert the following new lines of code, and then save and close the macro.
   ActiveCell.FormulaR1C1 = "Payment information entered"
   Range("C3").Select
   
   Assign the Enter_Payment macro to the Enter Payment button (a rounded rectangle shape).
9. Enter the data found in Table 1 on the Entry Form worksheet, and run the Enter Payment macro to test that the confirmation message has been added correctly.

10. Tarif created a macro named Clear that clears the data in the range C3:C5 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.
11. Tarif wants to display statistics about tuition payments in the range E3:G12. This information is stored in the Summary PivotTable on the Payment Summary worksheet. In the merged cell E4, use the GETPIVOTDATA function to display the number of registered students from cell B9 in the Summary PivotTable on the Payment Summary worksheet. In the merged cell G4, display the total amount of tuition billed from cell C9 of the Summary PivotTable. In the merged cell G8, display the total amount of tuition paid from cell D9 of the Summary PivotTable.
12. To prevent users from modifying the formulas, Tarif wants you to protect the Entry Form worksheet. Unlock cells C3:C5 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 SAM website to submit your completed project.