The file RFMdata.xlsx contains the date and size of transactions for 256 customers of a mail order catalog company. RFM (recency, frequency, and monetary value) attempts to predict how a customer will perform in the future based on ranking for recency, frequency, and monetary value.

a. Rate each person on a 1-4 scale on each attribute, with a rating of 4 being the best and 1 the worst.

b. On each attribute a customer will get a rating of 4 if he is in the top 25 percent, and so on. Use the RANK function to determine where a customer ranks on each attribute. Thus if you rank customers so that the largest value of an attribute gets a 256, then anyone with a rank of 193–256 gets a 4 rating; a rank of 129–192 gets a 3 rating; 65–128 gets a 2 rating; and a rank of 1–64 gets a 1 rating. Assume that each customer has received 80 mailings and a mailing costs $0.50.

c. In E8:E71 use conditional formatting to highlight in yellow each profi table RFM combination.