CMP 117 Exploring Excel Chapter 2 Homework Project 1 Formulas and Functions solutions answers

CMP 117 Exploring Excel Chapter 2 Homework Project 1 Formulas and Functions complete solutions correct answers key

Project Description: DUE TUESDAY SEPTEMBER 22, 2015 Before Class Upload assignment Excel Sheet upon completion. How ? Go to Blackboard, open course link, open Assignment link. In Submission area, upload excel sheet. 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:
Step Points Possible Instructions 1 Download and open the file named cmp117_hw1.xlsx, and then save the file as cmp117_h1_LastFirst. Click OK in the message regarding the circular reference.
Where LastFirst is your Lastname Firstname (Capitalize first initial letter in both) 2 2 Insert a function to enter the current date in cell B2. 5 3 In Column C from C5 to C13 Type in the appropriate Cost, depending on whether Membership type is Deluxe, Family, or Individual. 5 4 Insert a function in cell E5 to calculate total amount. The function should add the Cost of membership plus the locker fee. The locker fee is found in B22. 7 5 In cell G5 Calculate the total due based on the annual total and years of membership in
column F. 5 6 Copy the formula in E and G down their respective columns. 5 7 In Column H from H5 to H13 Type in the appropriate Down Payment, depending on whether
Membership type is Deluxe, Family, or Individual. 5 8 Locate and correct the circular reference for the balance in cell I5. The balance should be
calculated as the difference between total due and the down payment. 7 9 Copy the formula in I down the column. 5 Page 1|2 CMP 117 Step Exploring - Excel Chapter 2: Homework Project 1 Points
Possible Instructions 10 Insert a function in cell J5 to calculate the first client’s monthly payment. Use the number of
years the client signs up for in the calculation of the monthly payment. Use appropriate
relative and absolute cell references as needed. =PMT(B23/12,F5*12,-I5)
(B23 is the rate F5 is the number of years I5 is the Balance) 6 11 Copy the formula down the column. 5 12 Insert a function in cell G14 to total the column. 5 13 Fill the function in cell G14 across the range H14:J14 to add additional totals. 5 14 Insert functions in cells H18:H22 to calculate basic summary information. 7 15 Format the payments in cells H19:H22 with Accounting Number Format. 5 16 Format the column headings on row 4 and 17 to match the fill color in the range E17:H17. 6 17 Format the cells G5:J5 and G14:J14 with Accounting Number Format. Use zero decimal places
for whole numbers. 6 18 Apply Comma Style to the range G6:J13. Use zero decimal places for whole numbers. 6 19 Type your First and Last Name in Merged Cells K1: M1. Font in Red 3 20 Save the file and close Excel. Submit the file as directed. 0 Total Points 100 Page 2|2