New Perspectives Excel 365 | Module 12: SAM Project A Crest Financial
DEVELOP AN EXCEL APPLICATION
· Excel Projects Help
GETTING STARTED
1. Save the file NP_EX365_12A_FirstLastName_1.xlsm as NP_EX365_12A_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. With the file NP_EX365_12A_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.
3. 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: o 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. o For Mac: If a dialog box about macros appears, click Enable Macros.
4. To complete this project, you need to add the Developer tab. If this tab does 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 Developer check box, and click OK.
PROJECT STEPS
1. Minho Jung is in charge of public relations for Crest Financial, an investment bank in San Francisco, California. Minho is coordinating a community-service program that provides employees from Crest Financial to serve as instructors for seminars on financial topics. He is developing an Excel workbook to track and record the seminar information, and asks for your help in automating the workbook. Go to the Seminars worksheet and then unprotect it so you can edit the contents.
2. Minho wants to include an eye-catching title on the worksheet, which he shares with the seminar instructors. Insert and format WordArt as follows:
a. Insert WordArt using the Fill: Black, Text color 1; Shadow style.
b. Type Financial Seminars as the worksheet title.
c. Change the text fill of the WordArt to Aqua, Accent 4.
d. Move the WordArt to row 1 so that it spans columns B:G.
3. Minho created the Print Setup button on this worksheet using a Rounded Rectangle shape. He asks you to make it possible to click the shape to run the Print_Setup macro. Assign the Print_Setup macro to the Print Setup shape.
4. The range G13:H21 is designed to list the number of instructors trained to conduct each seminar. The average number of instructors from the range H15:H20 should be displayed in cell H21. However, that cell displays an error because no data has been entered in the range H15:H20. Minho asks you to display a message instead of an error when the range does not contain any Trained Instructors data. Add an IFERROR function to the formula in cell H21 to display the message "No data is available" if the formula returns an error.
5. Go to the Evaluations worksheet, which lists the instructors of the top three most popular seminars and the ratings each instructor received from participant evaluations. Minho asks you to assign a Performance rating of Excellent for instructors who average ratings greater than 8, a rating of Good for instructors who average ratings greater than 6, and a rating of Needs Improvement for all other instructors. Provide this information for Minho as follows:
a. In cell F4, enter a formula using the LET function.
b. Use avg as the name of the variable.
c. For the name_value1 argument, use the AVERAGE function to average the values in the range C4:E4.
d. For the calculation argument, enter an IF function that displays "Excellent" if avg is greater than 8 and a second IF function that displays "Good" if the avg is greater than 6.
e. Display "Needs improvement" for any other avg value.
f. Fill the range F5:F13 with the formula in cell F4.
6. Go to the Instructors worksheet, where Minho and his staff list instructors, the seminars they conduct, and the size of the instructor's polo shirt, which the bank provides to instructors. Because seminars start on September 12, 2029 and end on November 14, 2029, he wants you to make sure users enter a correct date in the Seminar Start Date column. Create a data validation rule as follows:
a. In the range D4:D17, create a data validation rule that accepts only date values between 9/12/29 and 11/14/29.
b. Create an input message that uses Seminar Start Date as the title and the following text as the input message: Enter the date to start instruction.
c. Create a Stop style error alert that uses Invalid Date as the title and the following text as the error message: Enter a date between September 12 and November 14.
7. Minho wants to make sure all of the dates entered in the Seminar State Date column are valid. Identify and correct any invalid values as follows:
a. In the range D4:D17, circle invalid data.
b. Change the first invalid date to 9/26/29 and change the second invalid date to 9/12/29 to correct the errors.
8. Go to the Assignments worksheet. Once a week, Minho and his staff assign instructors to seminars using the form on this worksheet. Minho has created two macros to automate the form, but they are not working correctly. View and edit the macros as follows:
a. Open the Clear_Form macro in the Visual Basic Editor. The macro should delete the values in the range D4:D8, but specifies the range C4:C8.
b. In the Clear_Form macro VBA code, change the statement between the "'Edit the code below" and "'Edit the code above" comments to the following statement: Range("D4:D8").Select
c. Scroll down to display the code for the Add_Instructor macro, the second macro Minho created. This macro calls, or runs, the Clear_Form macro at the end of the code, so it should now work correctly. Save the code and then close the Visual Basic Editor.
d. Assign the Clear_Form macro to the Clear Form button so that Minho and his staff can use the button to clear the form.
e. Use the Clear Form button to clear the form and test the macro.
9. The Add_Instructor macro should copy the data in the range D4:D8 on the Assignments worksheet, go to the Instructors worksheet, find the next blank row, and then paste the copied data, transposing it to fit in a row instead of a column. The macro should then return to the Assignments worksheet and run the Clear_Form macro, which deletes the data in the range D4:D8. Minho wants you to assign the Add_Instructor macro to a new button to make it easy to run the macro. Create the button as follows:
a. Insert a Button (Form Control) button in cell D10 to the right of the Clear Form button.
b. Assign the Add_Instructor macro to the new button.
c. Change the height to 0.3" and the width to 1.8" to match the size of the Clear Form button.
d. Align the top of the new button with the top of the Clear Form button.
e. Edit the text to display Add Instructor on the button.
f. Format the button control to use Trebuchet MS 11-point text if necessary.
10. Insert the instructor assignment data shown in Table 1 in the Entry column of the form. Next, use the Add Instructor button to run the Add_Instructor macro, and then verify that it added a record on the Instructors worksheet.
Table 1: Instructor Assignment Data
Instructor Name | Ed Chavez Location | Riverside Convention Center Seminar Start Date | 9/26/29 Topic | Home Buyers Shirt Size | XL
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: Seminars Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 2: Evaluations Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 3: Instructors Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 4: Assignments Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.