APPLICATION CAPSTONE PROJECT

EXCEL 2016: IN PRACTICE—APPLICATION CAPSTONE PROJECT (5-7)

 

 

EXCEL FINAL PROJECT

 

PART II of II

 

DUE 12/12/17

 

~~~GRADES WILL CLOSE AT MIDNIGHT~~~

 

I am giving you 2 weeks to complete this which is very generous.

                                                                                                                                                                                                                                                         

 

Overview

 

• For this project, you will format and modify a spreadsheet for the manager of CMP’s Health division.
• If possible, I suggest you complete this project with a Windows computer vs. a Mac.
• You will insert hyperlinks, and logical, index, and nested functions to determine balance and late fee information.
• Lastly, you will add validation rules and messages before protecting the worksheet.

 

Learning Outcomes/Student Data File

 

• Student Learning Outcomes 5.4, 5.6, 5.7, 6.2, 6.3, 6.7, 6.8, 7.2, 7.4, 7.5, 7.6]
• File Needed: CMPClients (see link in Module)

 

Completed Project File Names

 

• [your initials] Excel ACP 3.xlsx

 

 

Skills Covered in this Project:

 

 Group and format worksheets.

 Insert SmartArt.

 Insert a hyperlink.

 Set data validation for lists.

 Create an input message.

 Create an error message.

 Nest the AND and IF functions.

 Create an IFS function – you may substitute if necessary

 Nest the MATCH and INDEX functions.

 Use the AVERAGEIFS function.

 Use the SUMIFS function.

 Use the COUNTIFS function.

 Use the Watch Window – OPTIONAL

 Unlock cells for editing.

 Protect a worksheet.

 Share a workbook.

 Check compatibility.

 

INSTRUCTIONS

 

1. Open the Excel workbook CMPClients and it save as [your initials] Excel ACP 3.

 

2. Group all three worksheets.

 

3. Edit and format the grouped worksheets.
4. Apply Bold formatting and Gold, Accent 6, Darker 50% to the text in A17:A18.

Align the selected cells to the left.

17. Enter the TODAY function in A17.
18. Type your name in A18.
19. Ungroup the worksheets.

 

4. Insert and format a SmartArt graphic.
5. Select the Goal Descriptions worksheet, and insert a Basic Pyramid from the Pyramid category.
6. Position the SmartArt graphic with its top left selection handle in cell D1. Size the graphic so

that its bottom right selection handle is in cell K22.

1. Type Goal 5 in the top text box.
2. Type Goals 2-4 in the middle text box.
3. Type Goal 1 in the bottom text box.
4. Change the text size in each text box to 24 pt.
5. Apply the Inset style from the 3‐D group.
6. Change the color to Colorful Range—Accent Colors 5 to 6.
7. AutoFit the width of column A. (Figure 1).

 

5. Insert a hyperlink to display the Goal Descriptions worksheet.
6. Select the MN Clients worksheet and click cell K18.
7. Create a hyperlink and edit the display text to read Open Target Goal Descriptions.
8. Change the hyperlink text format to Gold, Accent 6, Darker 50%.
9. Test the link.

 

6. Apply data validation to K4:K15 on the MN Clients worksheet that limits the user to enter whole

numbers between 0 and 5.

1. Create an input message titled Target Goal that displays Please enter a number

between 0 and 5. as the message when you select the cell.

1. Create a Stop error message titled Target Goal Error that displays Incorrect

Entry! You must enter a number between 0 and 5. when the contents

are incorrect.

2. Enter the following data in K11:K15 respectively: 1, 2, 3, 1, and 2.

 

7. Create a basic formula.
8. Select the AR Data worksheet and click cell D2.
9. Subtract the amount paid value in column C from the amount due value in column B.
10. Copy the formula in D2 to D3:D13 without formatting.

 

8. Nest AND and IF functions based on the client’s amount paid and balance amounts to determine

if a client should be contacted for collections.

2. Select the AR Data worksheet and click cell E2.
3. Compare the amount paid in column C to the amount paid value in column I for the first

AND function argument.

1. Determine if the balance amount in column D is larger than the balance value in column I for

the second AND function argument.

1. For the IF function, type “Yes” and “No” for the required arguments.
2. Edit the formula to include absolute references where appropriate. Copy E2 without

formatting to E3:E13.

 

9. Create an IFS function to determine late fee calculations based on balance and penalty amounts.

If the client’s balance is less than the amount located in column I, then multiply the balance by

their associated fees located in column J. Note: This function is available if you are an Office 365

subscriber and have the latest version of Office installed.

 

2. Select the AR Data worksheet and click cell F2.
3. Edit the formula to include absolute references where appropriate.
4. Copy F2 without formatting to F3:F13 (Figure 2).
5. Create 3D references in the MN Clients worksheet.
6. Select the MN Clients worksheet and click L4.
7. Create a 3D reference to the first balance amount in the AR Data worksheet.
8. Copy L4 in the MN Clients worksheet to M2:N2 without formatting.
9. Select L4:N4 in the MN Clients worksheet. Use the Fill handle to copy the range without

formatting to L5:N15.

 

11. Nest INDEX and MATCH functions in E19 on the MN Clients worksheet to determine the

Representative of the client based on the client number in E18.

102. Select E18 and type the number 102. Apply the General Number Format.
103. Click cell E19 and click the Lookup & Reference button from the Function Library. Choose INDEX.
104. Select the second argument list reference,row_num,column_num,area_num and click OK.

Type A3:N15 in the Reference box.

1. Click the Row_num box, click the Name box arrow, and choose MATCH. Click More Functions if necessary to find and select MATCH.
2. Select E18 as the Lookup_value argument for MATCH.
3. Click the Lookup_array box and select the cell range A3:A15. Click the Match_type box and type 0 and click OK.
4. Click before the last closing parenthesis in the formula bar and choose MATCH from the

            Name box. This opens the MATCH Function Arguments dialogue box.

 

1. Click the Lookup_value box and type “Representative”.

 

3. Click the Lookup_array box and select A3:N3. Type 0 in the Match_type box. Click OK.

 

3. Replace the plus sign with a comma in the formula. Compare your formula to Figure 3.

 

107. Select E18 and type the number 107.

 

12. Use typing or the Function Library to create another nested INDEX and MATCH function in cell E20 to determine the Target Goal based on the client number in E18.
13. Use the same argument ranges listed in step 11.
14. Hint: Add absolute reference symbols to the original function as required; then copy the formula to E20 for easier editing.
15. Apply the General Number Format to E20.

 

13. Apply the AVERAGEIFS function to cell I18 in the MN Clients worksheet to determine the

average late fee for a client with a positive overdue balance in Bemidji.

 

14. Copy and edit the AVERAGEIFS function in cell I18 to I19 in the MN Clients worksheet to

determine the total of late fees for clients in Saint Cloud with a positive overdue balance.

 

15. Apply COUNTIFS to cell I20 in the MN Clients worksheet to determine the number of clients in all cities that have a positive late fee posting and more than a $50.00 account balance. Apply the General Number Format to E20 (Figure 4).

 

16. Add cells I18:I20 from the MN Clients worksheet to the Watch Window. Close the Watch Window.

 

17. Unlock cells K4:K15 in MN Clients for users to edit.

 

18. Protect the MN Clients worksheet.
19. Enter Pass1 as the password.
20. Allow users to Select locked cells and Select unlocked cells.

 

19. Share the workbook to Allow changes by more than one user at the same time and set the Keep change history for option to 25 days.

 

20. Check compatibility for any issues with the workbook.

 

21. Save the workbook. Upload via the Assignment link in the Module. You are NOT uploading via SIMnet.