You are a sales representative at the local fitness center. Your manager expects each rep to...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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. 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.
Expert Answer:
Answer rating: 100% (QA)
SNO Name Annual membership cost Annual Total Amount Collected as of now Due of client Monthly installment 1 raju 1000 1750 1250 8250 138 2 ravi 2000 2000 1000 11000 153 3 ramana 500 1250 1000 8500 101 ... View the full answer
Related Book For
Organizational Behaviour Concepts Controversies Applications
ISBN: 978-0132310314
6th Canadian Edition
Authors: Nancy Langton,Stephen P. Robbins, Timothy A. Judge, Katherine Breward
Posted Date:
Students also viewed these mathematics questions
-
Fill in the cells in column B with groups in terms of the value in column A in tab Q2, and the range of each group lies in the table on the right side. For example, if the value is 5.5, the group...
-
You are a sales representative for an international software company. After four excellent years, sales in your territory are off 30 percent this year. Describe three impression management techniques...
-
You are a team of marketing executives that work for UCC Company. UCC Company has been selling its industrial clothing across Canada for over 25 years. It has 4 employee sales representatives, 3 are...
-
Derive the transfer function of the circuit shown in figure below (for an ideal op amp) and show that it can be written in the form - V. -R2/R1 V," [1+ ( +j) Where w1 = 1/C,R1 and w2 = 1/ C2R2....
-
Suppose that the demand function for pizzas is Qd = 65,800 - 1,200P. The supply function is Qs = 4,000P - 20,000. Suppose the College Student Party is elected and places a price ceiling on pizza of...
-
Refer to the periodic table and write the predicted electron configuration for each of the following negative ions using core notation. (a) Br (b) Te 2 (c) As 3 (d) O 2 . Periodic Table: 2 3 4 10...
-
Describe an experiment, and give the necessary equations for measurement of the complex flexural modulus, \(E_{\mathrm{fx}}^{*}\), of a symmetric laminated beam.
-
Beaty Company has the following internal control procedures over cash receipts. Identify the internal control principle that is applicable to each procedure. (a) All over-the-counter receipts are...
-
10. The cost, in dollars to produce q items is C(q) = 0.07q + 62q + 1500 Find the marginal cost function. b. Find C(40) and C'(40) showing a step of work for each one. Give units with your answers...
-
Three-Month Project NOTE! Templates needed Ampersand, Inc., is a small business that operates in Somerset, VT The company is located at 732 Appalachian Way, Somerset, VT 05363. Its federal Employer...
-
After watching the 15 minute video on Jeff Bezos, do some additional web-based research on either Bezos himself or Amazon in terms of strategy. In addition, read the article "Taming the Titans" from...
-
identify at least two operational risks and two operational risk mitigation strategies, at least two financial risks and two financial risk mitigation strategies, one market risk and one market risk...
-
How does the internal conflict between duty and desire shape the characters' choices and relationships in Leo Tolstoy's "Anna Karenina" and Gustave Flaubert's "Madame Bovary"?
-
Do you think investing in corporate bonds or quality stocks is the best way to increase income? Why or why not?
-
Draw a rough picture of the window produced by this code public class Main extends Application { @Override public void start (Stage primaryStage) { HBox data= new HBox(10); Label name LBL = new...
-
When renting an apartment, a security deposit represents: Group of answer choices some money to cover costs of repairs/cleaning after move-out the terms by which an apartment can be subleased a...
-
Recall that one of the Sampsons' goals is to invest for their children's future college education. They are considering investing in several stocks that are rated highly by analysts. Offer advice to...
-
Write the expression in radical notation. Then evaluate the expression when the result is an integer. 23 -1/2
-
OB is for everyone. Build an argument to support this statement.
-
Sexual harassment should not be tolerated at the workplace. Workplace romances are a natural occurrence in organizations. Are both of these statements true? Can they be reconciled?
-
Based on the information presented in this chapter, what would you do as a recent university graduate entering a new job to maximize your power and accelerate your career progress?
-
The balance sheet of Dot Co. for the year ended 31 December 20X2, together with comparative figures for the previous year, is shown in Figure 13.7 (all figures :000). You are informed that there were...
-
Under IFRS, a group is a set of entities that the parent: A. Significantly influences. B. Owns a majority of the voting power in. C. Jointly controls. D. Controls.
-
Under IFRS 10, control over an entity means: A. Power to govern its financial and operating policies. B. The right to appoint the majority of its Board members. C. Power to affect the variable...
Study smarter with the SolutionInn App