Club

• Used the if function.
• Used the and function.

Cells a2:N7 contain a number of example customers. Cells a9:b16 contain some assumptions that computations should be based upon. Cells e10:f15 contain space for final results.

Please note, blue boxes should contain a computation. All computations should be developed so that they can be entered once and copied to the other cells in the column.

1. Start excel.
2. Save your work
3. Download this workbook
4. Compute the member discount in column d based on column c and the discount rate in b9.
   • If the customer has a membership (y in column C), then they should receive the membership discount times the cost,
   • otherwise they should receive a discount of 0.
5. In column E place a yes if the member discount would pay for the cost of membership.
   • If the cost multiplied by the discount rate is greater then the membership cost, the answer should be yes.
   • Otherwise the answer should be no.
6. in column F, calculate if the customer should be notified that their discount would have paid for a membership.
   • If the potential member discount is greater than or equal to the membership cost, and the customer does not have a membership, then the answer should be yes.
   • Otherwise the answer should be no.
   • This will require use of the and function.
   • This one is tougher, you might want to skip it and come back.
7. Calculate the new price in column G by subtracting the member discount from the cost.
8. In column I compute if the customer gets a volume visit discount
   • If the number of visits is greater than or equal to the visit discount limit, print a yes.
   • Otherwise print a no.
9. In column J, compute the visit discount.
   • If the number of customer visits is greater than or equal to the visit discount limit, apply the visit discount amount to the new price (column g).
   • If not, enter a 0.
10. In column K, compute the visit discount price, subtract the visit discount amount from the new price.
11. In column L compute the tax. This is the Visit Discount amount minus the tax rate (b15 times the visit discount amount.
12. In column M, compute the final price. Th visit Discount Amount plus the tax.
13. In column N, print yes if the final price is greater than the Personal Thank You amount in B16.
14. In F10, compute the total sales (sum of b2:b7)
15. In F11, compute the total member discount
16. In F12, compute the total visit discount.
17. In F13, compute the total tax.
18. In F14, compute the Total income, (total sales - discount amounts)
19. In F15, compute the total of column m
20. In F16, put a yes if F14 is the same as F15, or a no otherwise.
21. 
22. Submit your saved document to the Club folder in the Assignment section of D2L for this class.