You are a sales representative at the local fitness center. Your manager expects each rep to track weekly new membership data, so you created a spreadsheet to store data. Membership costs are based on membership 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 need to calculate general statistics to summarize for your manager. Perform Preliminary Work a) Open ExamGym file and save as ExamGym_lastnameFirstname. b) Assign the name Membership to the range A18:C20-Range name c) Insert a function to display the current date in Cell B2- Date Calculate Cost, Annual Total, and Total Due You are ready to calculate the basic annual membership cost and the total annual cost. The basic annual membership is determined based on each client's membership type, using the lookup table. a) Insert a function in Cell CS to display the basic annual membership cost for the first client. VLOOKU b) Use a function to calculate the annual total amount, which is the sum of the basic cost and F locker fees for those who rent a locker. The locker column display Yes for clients who rent a locker and No for those who don't. c) Calculate the total amount due for the first client based on the annual total and the number of years in the contract d) Copy the three formulas down their respective columns. Determine the Down Payment and Balance You need to collect a down payment based on the type of membership for each client. Then you must determine how much each client owes. a) Insert the function to display the amount of down payment for the first client. VLOOKUP b) The balance is the difference between the total due and the down payment. c) Copy the two formulas for the rest of the clients. Calculate the Monthly Payment Clients pay the remainder by making monthly payments. Monthly payments are based on the number of years specified in the client's contract and a standard interest rate. a) Insert the function to calculate the first client's monthly payment using appropriate relative PMT and absolute cell references. b) Copy the formula down the column. Finalize the Workbook You need to perform some basic statistical calculations and finalize the workbook with formatting and page setup options. a) Calculate totals on row 14. b) Insert the appropriate functions in the Summary Statistics section of the worksheet: cells H18:H22. Format the payments with Accounting Number Format and format the number of new members appropriately. c) Format the other column headings on rows 4 and 17 to match the fill color in the range E17:H17. Wrap text for the column headings. d) Format the monetary values for Andrews and the total row with Accounting Number Format. Use zero decimal places for whole amounts and display two decimal places for the monthly payment. Underline the values before the totals, and then apply Double Accounting Underline for the totals. e) Set 0.3" left and right margins, and then ensure the file prints on only one page. 9 Insert a footer with your name on the left side, the date code in the center, and the file name code on the right side. 8) Save and submit the file to Blackboard.