Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Here is the excel sheet Health & Fitness Gym Date Prepared: Client Membership Cost Locker Annual Total Years Total Due Down Payment Balance Monthly Payment
Here is the excel sheet
Health & Fitness Gym | |||||||||
Date Prepared: | |||||||||
Client | Membership | Cost | Locker | Annual Total | Years | Total Due | Down Payment | Balance | Monthly Payment |
Andrews | Deluxe | Yes | 1 | ||||||
Baker | Individual | Yes | 2 | ||||||
Carter | Family | No | 3 | ||||||
Dudley | Deluxe | No | 2 | ||||||
Evans | Deluxe | Yes | 3 | ||||||
Foust | Individual | No | 1 | ||||||
Gardner | Individual | No | 2 | ||||||
Hart | Individual | No | 3 | ||||||
Ivans | Individual | Yes | 3 | ||||||
Totals | |||||||||
Membership | Cost | Down Payment | Summary Statistics | ||||||
Deluxe | $ 575 | $ 250 | Number of New Members | ||||||
Family | $ 1,500 | $ 700 | Lowest Monthly Payment | ||||||
Individual | $ 300 | $ 150 | Average Monthly Payment | ||||||
Maxium Monthly Payment | |||||||||
Locker Fee | $ 75 | Median Monthly Payment | |||||||
Interest Rate | 5.75% | ||||||||
Months Per Year | 12 |
Grader - Instructions Excel 2016 Project 1 0 EX16_XL_CH02_GRADER_CAP_AS - Gym Management 1.10 Project Description: Clear All Formatting alive at the local fitness center, Health & Fitness Gym. Your manager expects each representative to type. Clients can rent a locker for an additional annual fee. You are required to collect a down payment based on membership type, determine the balance, and then calculate the monthly payment based on a standard interest rate. In addition, you calculate general statistics to summarize for your manager. Spot-check results to make sure you created formulas and functions correctly Steps to Perform: Step Instructions Points Possible Open the downloaded file exploring e02 grader a1.xlsx 2 Insert a vertical lookup function in cell CS to display the basic annual membership cost for the 10 first client. 3 Insert a function in cell B2 to display the current date. 5 4 Use an IF function in cell ES to calculate the annual total amount, which is the sum of the 10 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 don't. In cell G5, enter a formula to calculate the total amount due for the first client based on the 5 annual total and the number of years in the contract. Copy the three formulas in columns C, G, and E down their respective columns 6 Insert a vertical lookup function in cell H5 to display the amount of down payment for the first 10 client based on the membership type. 7 Insert a formula in cell 15 to calculate the balance due on the membership. Copy the formulas 5 in columns Hand I for the rest of the clients. 8 Insert the PMT function in cell J5 to calculate the first client's monthly payment, using 15 appropriate relative and absolute cell references. Copy the formula down the column. 9 Calculate totals for Annual Total, Total Due, Down Payment, Balance, and Monthly Payment 10 on row 14. 10 Insert the appropriate functions in the Summary Statistics section of the worksheet: cells 10 H18.H22. Format the payments with Accounting number format. 11 Format the other column headings on rows 4 and 17 to match the fill color in the range 10 E17:H17. Wrap text for the column headings. 12 Set 0.3" left and right margins and ensure that the page prints on only one page. 5 13 Insert a footer with your name on the left side, the date code in the center, and the file name 5 on the right side. Save and close the workbook Submit the file as directed. 0 5 14 Total Points 100 Grader - Instructions Excel 2016 Project 1 0 EX16_XL_CH02_GRADER_CAP_AS - Gym Management 1.10 Project Description: Clear All Formatting alive at the local fitness center, Health & Fitness Gym. Your manager expects each representative to type. Clients can rent a locker for an additional annual fee. You are required to collect a down payment based on membership type, determine the balance, and then calculate the monthly payment based on a standard interest rate. In addition, you calculate general statistics to summarize for your manager. Spot-check results to make sure you created formulas and functions correctly Steps to Perform: Step Instructions Points Possible Open the downloaded file exploring e02 grader a1.xlsx 2 Insert a vertical lookup function in cell CS to display the basic annual membership cost for the 10 first client. 3 Insert a function in cell B2 to display the current date. 5 4 Use an IF function in cell ES to calculate the annual total amount, which is the sum of the 10 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 don't. In cell G5, enter a formula to calculate the total amount due for the first client based on the 5 annual total and the number of years in the contract. Copy the three formulas in columns C, G, and E down their respective columns 6 Insert a vertical lookup function in cell H5 to display the amount of down payment for the first 10 client based on the membership type. 7 Insert a formula in cell 15 to calculate the balance due on the membership. Copy the formulas 5 in columns Hand I for the rest of the clients. 8 Insert the PMT function in cell J5 to calculate the first client's monthly payment, using 15 appropriate relative and absolute cell references. Copy the formula down the column. 9 Calculate totals for Annual Total, Total Due, Down Payment, Balance, and Monthly Payment 10 on row 14. 10 Insert the appropriate functions in the Summary Statistics section of the worksheet: cells 10 H18.H22. Format the payments with Accounting number format. 11 Format the other column headings on rows 4 and 17 to match the fill color in the range 10 E17:H17. Wrap text for the column headings. 12 Set 0.3" left and right margins and ensure that the page prints on only one page. 5 13 Insert a footer with your name on the left side, the date code in the center, and the file name 5 on the right side. Save and close the workbook Submit the file as directed. 0 5 14 Total Points 100
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started