Excel Homework 4 --- Fall 2017

Project Description:

Project Description: In this project, you will apply the Excel skills you learned in Excel chapters 1 and 2. More specifically, you will be applying your problem-solving skills by writing formulas and using Excel's IF and VLOOKUP functions and some statistical functions.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step	Instructions	Points Possible
1	NOTE: To minimize chances for confusion and misinterpretation of the instructions, when working with only the Instructions documents downloaded from MyITLab, either do NOT enable editing (do NOT click the Enable Editing button at the top) or always do the following: 1. Click the File tab, and then click Options 2. Click Proofing 3. Scroll down to locate the label Exceptions for: 4. Under the Exceptions for: label, check the last two checkboxes: Hide spelling errors in this document only checkbox Hide grammar errors in this document only checkbox 5. Click the OK button to close the Word Options dialog box NOTE: As a general rule, do NOT set/change any properties/parameters/settings unless asked specifically and explicitly by specific instructions. NOTE: As a general rule, any text/content between [ and ] characters must be typed literally and exactly as given. When typing text/content between [ and ] characters, do NOT type the [ and ] characters. NOTE: If and when asked to save a file/document as _YourFirstName_YourLastName , always replace the YourFirstName part with your real first name and the YourLastName part with your real last name.	0.000
2	Download and open the file named Excel_Homework_4.xlsx. Save the file, changing the file name to [ Excel_Homework_4_YourFirstName_YourLastName.xlsx ].	0.000
3	Insert a function in cell B2 to display both the current date and time. Note: You must use the proper function. Do NOT write your own formula.	5.000
4	Assign the name [ Locker_Cost ] to the range A24:B24.	5.000
5	Insert the VLOOKUP function in cell C5 to display the basic annual membership cost for the first client based on the information in cells A18:C20. Note: You must use the proper function. Do NOT write your own formula.	11.000
6	Copy the function in cell C5 to cells C6 through C13.	1.000
7	Use the IF function in cell E5 to calculate the annual total amount, which is the sum of the basic cost and locker fees for those who rent a locker. For people who do not rent a locker, the annual cost is only the cost shown in column C. The Locker column displays Yes for clients who rent a locker and No for those who dont. Note: You must use the proper function. Do NOT write your own formula.	11.000
8	Copy the function in cell E5 to cells E6 through E13.	1.000
9	In cell G5, enter a formula to calculate the total amount due for the first client based on the annual total and the number of years in the contract. Note: You must write your own formula. Do NOT use functions.	5.000
10	Copy the formula in cell G5 to cells G6 through G13.	1.000
11	Insert the VLOOKUP function in cell H5 to display the amount of down payment for the first client based on the information in cells A18:C20. Note: You must use the proper function. Do NOT write your own formula.	11.000
12	Copy the function in cell H5 to cells H6 through H13.	1.000
13	Insert a formula in cell I5 to calculate the balance due on the membership. NOTE: You must write your own formula. Do NOT use functions.	5.000
14	Copy the formula in cell I5 to cells I6 through I13.	1.000
15	Use the IF function in cell J5 to display the proper Payment Type Message for the first client based on the following rule: If the Balance is greater than or equal to the threshold balance in cell B27, the message [ No Checks. Only Cash ] should be displayed. Otherwise, the message [ Checks or Cash ] should be displayed. NOTE: You must use the proper function. Do NOT write your own formula.	11.000
16	Copy the function in cell J5 to cells J6 through J13.	1.000
17	Use the proper function to calculate totals for Cost, Annual Total, Total Due, Down Payment and Balance in row 14. NOTE: You must use the proper function. Do NOT write your own formula.	15.000
18	Insert the appropriate functions in the Summary Statistics section of the worksheet - cells F18:F22 to calculate number of new members, lowest balance, average balance, highest balance, and median balance. NOTE: You must use the proper functions. Do NOT write your own formula.	15.000
19	Save the workbook and submit/upload the Excel file based on your instructors directions.	0.000
	Total Points	100.000