Today I want to address a common occurrence in my job. I am often asked to calculate an instant discount based on the total amount of a cash register receipt. For instance, a customer can instantly save $20 for every $100 spent on a cash register receipt.

Now that does not seem so bad. But the receipt already shows the discounted amount. In this example you can see the for cash register receipt 11111, there are 6 items purchased totaling $241.01.

Receipt #
ITM_NBR
 Item Amount Paid At Cash Register
 Total Amount Of Receipt
11111
1
                                          48.09
                                           241.01
11111
2
                                          14.79
                                           241.01
11111
3
                                          10.00
                                           241.01
11111
4
                                            0.78
                                           241.01
11111
5
                                            9.35
                                           241.01
11111
6
                                        158.00
                                           241.01

My manager wants to know what the original amount of the Total Amount Of Receipt would have been before the discount.

On a one off, it’s not too hard to determine and with a bit of patience, I can manually generate the result ($40). However this does not work when I have 100,000 records.

To automate this I need a few key bits of information. First I need to know what the savings is. In this example $20 for every $100 spent.  As this is an instant savings, A $100 purchase would ring up as $80 (100 – 20). A $200 purchase would ring up as $160 (200 – 40). So when I look at the above example, this customer got a $40 instant savings 241.01 + 40 = 281.01(original total before the $40 instant discount).  There is no sliding scale so if the customer does not hit the next threshold; they only qualify for the lower discount.

The next bit of information needed to automatically calculate this is the maximum Cash Register Receipt total. This sets my high Tier that I will use in my matrix. A quick check of my Total Amount Of Receipt column shows that the highest valued ticket that I have is $1539.99. My low number is .01 (the bare minimum of a purchase).

So what I need to do is build up my matrix of Max purchase price and Low purchase price and how much of a discount should be provided. Remember that these values have the discount already applied…

So let’s start with the bottom and work our way up.

If a customer’s receipt shows $79.99 or less then they have not spent enough to qualify for an instant $20 discount (too bad they didn’t purchase some gum at checkout). If the receipt shows $80 to $179.99 then they have earned the instant $20 savings. Think about that for a minute. If the receipt shows $179.99, when you add the discount of $20, you get 199.99. The promo is save $20 for every $100 spent. They did not spend $200 so they only qualify for the $20 discount (again they should have purchased some gum). If their receipt shows $80, then they got the discount of $20 (80 + 20 = 100).

High Ticket Price
 Low Ticket Price
 Discount Amount
                          259.99
                             180.00
                                  40
                          179.99
                             100.00
                                  20
                             99.99
                               80.00
                                  20
                             79.99
                                 0.01
                                   -  

Now in my matrix, the high tier is always going to be one cent less than the next qualifying Discount amount threshold. No overlap can occur so the low tier on the next discount is always .01 greater than the high tier on the lower threshold.

When I finish my matrix it looks like the following and I place it on a tab labeled Discount…


 A
 B
 C
1
 High Ticket Price
 Low Ticket Price
 Discount Amount
2
                       1,539.99
                         1,460.00
                                360
3
                       1,459.99
                         1,380.00
                                340
4
                       1,379.99
                         1,300.00
                                320
5
                       1,299.99
                         1,220.00
                                300
6
                       1,219.99
                         1,140.00
                                280
7
                       1,139.99
                         1,060.00
                                260
8
                       1,059.99
                             980.00
                                240
9
                          979.99
                             900.00
                                220
10
                          899.99
                             820.00
                                200
11
                          819.99
                             740.00
                                180
12
                          739.99
                             660.00
                                160
13
                          659.99
                             580.00
                                140
14
                          579.99
                             500.00
                                120
15
                          499.99
                             420.00
                                100
16
                          419.99
                             340.00
                                  80
17
                          339.99
                             260.00
                                  60
18
                          259.99
                             180.00
                                  40
19
                          179.99
                             100.00
                                  20
20
                             99.99
                               80.00
                                  20
21
                             79.99
                                 0.01
                                   -  

My receipt information is on a separate tab

Receipt #
ITM_NBR
 Item Amount Paid At Cash Register
 Total Amount Of Receipt
 Discount Applied
11111
1
                                          48.09
                                           241.01
                    40.00

The formula I use in the Discount Applied column is…

=VLOOKUP(INDEX(Discount!A:A,MATCH(D2,Discount!A:A,-1)),Discount!A:C,3,FALSE)

Now you are saying, not the dreaded Index / Match function that causes me so much grief….. Sorry but yes, it’s the perfect tool for this.

Let’s break the formula down piece by piece to help you better understand.

=MATCH(D2,Discount!A:A,-1) would return 18

This formula is taking the Total Amount Of Receipt in cell D2 (241.01) and asking Excel to return the row number that has a value that is just above the value from our matrix on the Discount Tab.

Row
High Ticket Price
 Low Ticket Price
 Discount Amount
16
419.99
340.00
80
17
339.99
260.00
60
18
259.99
180.00
40
19
179.99
100.00
20

So what does the INDEX portion of this formula do?

=INDEX(Discount!A:A,MATCH(D3,Discount!A:A,-1))

Well it returns the value in Column A of row 18 in my matrix (259.99). I can use that number to do a Vlookup and return the discount amount to apply.

=VLOOKUP(INDEX(Discount!A:A,MATCH(D2,Discount!A:A,-1)),Discount!A:C,3,FALSE)

Receipt #
ITM_NBR
 Item Amount Paid At Cash Register
 Total Amount Of Receipt
 Discount Applied
11111
1
                                          48.09
                                           241.01
                    40.00
11111
2
                                          14.79
                                           241.01
                    40.00
11111
3
                                          10.00
                                           241.01
                    40.00
11111
4
                                            0.78
                                           241.01
                    40.00
11111
5
                                            9.35
                                           241.01
                    40.00
11111
6
                                        158.00
                                           241.01
                    40.00
22222
7
                                        123.52
                                           313.04
                    60.00
22222
8
                                        176.00
                                           313.04
                    60.00
22222
9
                                            1.58
                                           313.04
                    60.00
22222
10
                                          11.94
                                           313.04
                    60.00
33333
11
                                          93.90
                                             97.47
                    20.00
33333
12
                                            3.57
                                             97.47
                    20.00
44444
13
                                          28.51
                                           108.99
                    20.00
44444
14
                                            3.33
                                           108.99
                    20.00
44444
15
                                            4.17
                                           108.99
                    20.00
44444
16
                                          61.96
                                           108.99
                    20.00
44444
17
                                            5.47
                                           108.99
                    20.00
44444
18
                                            1.24
                                           108.99
                    20.00
44444
19
                                            3.75
                                           108.99
                    20.00
44444
20
                                            0.56
                                           108.99
                    20.00
55555
21
                                          10.56
                                             97.95
                    20.00
55555
22
                                          11.03
                                             97.95
                    20.00
55555
23
                                          76.36
                                             97.95
                    20.00

Well If I know the discount applied to a ticket, then I can calculate the original ticket amount with simple addition. I can also figure out how much discount was applied to each item by dividing the Total Amount of Receipt by Item Amount Paid At Cash Register. Then multiply the discount by this percent.

So with some organization of my data upfront and utilizing three functions, Vlookup, Index and Match I can back into my original ticket price (as well as my original Item price).

How would you calculate the tier discount that I showed today?


No comments:

Post a Comment