Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

image text in transcribed
image text in transcribed
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 C 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. f) 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. g) Save and submit the file to Blackboard

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Accounting For Non-Accounting Students

Authors: John R. Dyson, Ellie Franklin

9th Edition

978-1292128979, 1292128976

More Books

Students also viewed these Accounting questions

Question

Learning is a good thing for everyone. Discuss.

Answered: 1 week ago

Question

What should be the role of managers in HRD?

Answered: 1 week ago

Question

What should be the role of government in HRD?

Answered: 1 week ago