Insert a function in cell h5 to display the amount of down payment for the first client.

Office 2013 – myitlab:grader – Instructions Exploring - Excel Chapter 2: Homework Project 1

Formulas and Functions

Project Description: In the following project, you will perform preliminary work on the gym worksheet. You will then calculate cost, annual total, and total due. You will also determine the down payment and balance of gym membership. Your last steps will be to calculate the monthly payment and finalized the workbook.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Rule 1: If an object is selected and a new tab appears, it is 95% in that new tab. Rule 2: If you are ever confused, right click on the selected item. Formulas: + = Addition - = Subtraction * = Multiplication / = Division Step Instructions Points Possible 1 Download and open the file named exploring_e02_grader_h1.xlsx. Click OK in the message regarding the circular reference. In File, use the SAVE AS function to save the document in Computer / Z Drive as Excel Chapter 2 Capstone. 0 2 Create a range name for cells A18:C20 named Membership. Select the columns A through J and autofit them. To do this, move the curser in-between two of the selected columns, and double click when the curser is a vertical line with an arrow sticking out from both sides. This will autofit ALL selected columns. 5 3 Insert the TODAY function to enter the current date in cell B2. 5 4 In cell C5, insert the VLOOKUP function to display the basic annual membership cost of the first client. Because of the range name being Membership for the table, absolute references will not be required in regards to the table_array. Simply use Membership as the table_array, and it should select the preassigned range to that name. 5 5 Insert the IF function in cell E5 to calculate total amount. The function should add the cost of membership plus, if applicable, the locker fee. Logical_Test – This will test to see whether or not the client has a locker. If the client has a locker, the cell, D5, will say Yes. This row needs to test whether or not the Locker column has “Yes” in its cell. Value_if_True – This is the equation for if the Logical_Test is true. Meaning, the price of the locker added to the cost of the membership. Value_if_False – This is the equation for if the Logical_Test is false. Meaning, the cost of the membership WITHOUT the locker fee. Be sure to go through each equation within the function, and check to see if absolute reference are necessary. Look at each cell reference and ask whether or not that cell changes as you copy the formula, or if it stays the same. If it stays the same, ABSOLUTE REFERENCES will be needed. 7 6 In cell G5, enter a formula to multiply the annual total and years of membership to calculate the total due in column F. 5 7 Copy the three formulas down their respective columns. 5 8 Insert the VLOOKUP function in cell H5 to display the amount of down payment for the first client. 5 9 Locate and correct the circular reference for the balance in cell I5. A circular reference is when an equation refers to the cell it is in, which will void the equation. Be sure the equation calculates the difference between total due and the down payment. 7 10 Copy the two formulas down their respective columns. 5 11 Insert the PMT function in cell J5 to calculate the first client’s monthly payment. Rate – Objective This is the interest rate the payments will be appropriated. For This Assignment In this instance, it is found in cell B23 as the interest rate for the payments divided by the payment plan, being that it’s monthly, will be 12, located in B24. Nper – Objective This is the total number of payments for the loan, meaning what type of payment plan the loan is on. For This Assignment In this instance, it is a monthly plan, meaning that the payments will be 12 in total. This is located in cell B24. But, the payments can also go over multiple years, located in column F. To find the true total number of payments, multiply B24 by F5. Use the cell reference rather than the physical number. Pv – Objective This is the current amount due toward the loan. For This Assignment In this instance, it is located in I5, being that the current balance for each member is what each member owes. Because this number is a debt, it should be a negative number. Use appropriate absolute references as needed, being that absolute references are for cells that do not change. Fv and Type are rarely used, and will be clearly directed if ever needed. 6 12 Copy the formula down the column. 5 13 Insert the SUM function in cell G14 to total the column. 5 14 Fill the function in cell G14 across the range H14:J14 to add additional totals. 5 15 Insert functions in cells H18:H22 to calculate basic summary information. Be sure the correct information is selected for each individual function. COUNTA MIN AVERAGE MAX MEDIAN 7 16 Format the payments in cells H19:H22 with Accounting Number Format. 5 17 Select cell E17 and select Format Painter in Home / Clipboard. This will copy the formatting of the selected cells into another range of cells. Now format the column headings on row 4 and 17 to match the fill color in the range E17:H17 using format painter. 6 18 Format the cells G5:J5 and G14:J14 with Accounting Number Format. Use zero decimal places for whole numbers. 6 19 Apply Comma Style to the range G6:J13. Use zero decimal places for whole numbers. 6 20 Save the file and close Excel. Submit the file as directed. 0 Total Points 100

Updated: 01/07/2013 1 E_CH02_EXPV1_H1_Instructions.docx