The sheet "Customers" contains data from City Health Club. Customers are listed by a customer ID with the plan they signed up for and the date that they paid for the period starting on July 1st. You are given a spreadsheet with the data using a code for the plan.
The codes for the plans are as follows:
P = Platinum
G = Gold
S = Silver
The Platinum plan costs $99 per period, the Gold plan costs $75 per period and the Silver plan costs $50 per period. If a customer does not want to sign up for a plan and has a no-frills membership, he/she pays $20 per period.
Customers can get a discount for paying their dues early according to the following rules:
Customers who pay
Receive a discount of
0 <= days < 7 early
0%
7 <= days <14 early
5%
14 <= days < 30 early
10%
30 <= days early
15%
You are asked to fill in the spreadsheet with the full text for each customer's plan, the
LabE2
CS1100
2 of 4
discount offered and the amount paid.
1. Download, save, and then open the cs1100.e2.xlsx workbook.
2. Create a lookup table for the plan codes and prices that will use an exact match. Put your table in the worksheet titled "Health Club Tables" and name the table PlansTable.
3. Create an interval lookup table for the discount that will use an inexact match. Put your table in the worksheet titled "Health Club Tables" and name the table DiscountTable.
4. Fill in the column for "Discount" using a VLOOKUP function. (Hint: You can find the difference between the dates by subtracting Payment Date from Start Date.)
5. Fill in the columns for "Fees" and "Plan" using a VLOOKUP function
6. Calculate the total amount due after the discount
7. Use IFERROR to strengthen your VLOOKUP formulas so that your model will work even if a customer has no plan listed.
8. Using an IF statement, filter the data for each plan. You should be able to copy the IF statement down and across.
9. Using SUM calculate the total number of customers in each plan.
10. Make sure all of your formulas are copyable and resilient to changes in data.
11. Format the worksheet as shown in the figure below:
Attachment:- 351123_1_cs1100.e2.xlsx
Attachment:- cs1100.e2.pdf