Cash register information - What was the original price before discount?

Today I want to break away from how Excel accomplishes things (well kind of) and focus on an example of how you can use Excel.

In today’s example I have receipts from Tom’s Appliance Store. Sales were slow at Tom’s so they decided to have a one day 10% off all purchased items sale. They had 3 sales that day (told you things were slow). Each sale consisted of items applicable fees for the purchased items.

Now a typical Tom’s Appliance Store receipt will have a date, receipt #, Item # and description as well as shown cost of item. Indeed here is the sample data…


A
B
C
D
E
F
1
Date Of Sale
Cash Register Receipt #
Item #
Description
 Cash Register Shown Amount
 Item Quantity
2
6/21/2012
12345
1
Delivery fee
                 100.00
                1
3
6/21/2012
12345
18
Stove
                 975.00
                1
4
6/21/2012
12345
19
Microwave
                 450.00
                1
5
6/21/2012
13579
17
Refrigerator
             1,200.00
                1
6
6/21/2012
13579
23
Trash Compactor
                 260.00
                1
7
6/21/2012
24680
1
Delivery fee
                 100.00
                1
8
6/21/2012
24680
20
Dishwasher
                 375.00
                1
9
6/21/2012
24680
21
Sink
                 200.00
                1
10
6/21/2012
24680
22
Faucet
                 150.00
                1

On its own this data is not very informative. It does not tell how much each cash register receipt total was. Nor does it tell what the original price of the item was or how much discount was applied at an item or ticket level.

This is a perfect opportunity for Excel to shine and a good example of how you can utilize Excel to obtain this information knowing only a few basic calculations.

First off I want to know the total of every register receipt. Now I could just manually add each receipt placing the value in a new column but I can use the =sumif formula to automatically calculate my result.

I start by adding a header in column G “Total Cash Register Receipt” and use the =sumif function to automatically calculate my answer. Sum If will check my Cash Register Receipt column (B:B) and for every receipt number (B2 or B3, or B4…), sum the Cash Register Shown Amount (E:E).

A
B
C
D
E
F
G
Date Of Sale
Cash Register Receipt #
Item #
Description
Cash Register Shown Amount
Item Quantity
Total Cash Register Receipt
6/21/2012
12345
1
Delivery fee
100
1
=SUMIF(B:B,B2,E:E)
6/21/2012
12345
18
Stove
975
1
=SUMIF(B:B,B3,E:E)
6/21/2012
12345
19
Microwave
450
1
=SUMIF(B:B,B4,E:E)
6/21/2012
13579
17
Refrigerator
1200
1
=SUMIF(B:B,B5,E:E)
6/21/2012
13579
23
Trash Compactor
260
1
=SUMIF(B:B,B6,E:E)
6/21/2012
24680
1
Delivery fee
100
1
=SUMIF(B:B,B7,E:E)
6/21/2012
24680
20
Dishwasher
375
1
=SUMIF(B:B,B8,E:E)
6/21/2012
24680
21
Sink
200
1
=SUMIF(B:B,B9,E:E)
6/21/2012
24680
22
Faucet
150
1
=SUMIF(B:B,B10,E:E)

Date Of Sale
Cash Register Receipt #
Item #
Description
 Cash Register Shown Amount
 Item Quantity
Total Cash Register Receipt
6/21/2012
12345
1
Delivery fee
    100.00
             1
       1,525.00
6/21/2012
12345
18
Stove
    975.00
             1
       1,525.00
6/21/2012
12345
19
Microwave
    450.00
             1
       1,525.00
6/21/2012
13579
17
Refrigerator
 1,200.00
             1
       1,460.00
6/21/2012
13579
23
Trash Compactor
    260.00
             1
       1,460.00
6/21/2012
24680
1
Delivery fee
    100.00
             1
          825.00
6/21/2012
24680
20
Dishwasher
    375.00
             1
          825.00
6/21/2012
24680
21
Sink
    200.00
             1
          825.00
6/21/2012
24680
22
Faucet
    150.00
             1
          825.00

Great start! Now I know what my total Cash Register Receipt is but this amount already had the 10% discount applied. I want to know what the original price of each item was as well as the original price of each cash register receipt.

Well to get that I need to go back to my high school math Algebra days.

The formula to figure this out is:  Cash Register Shown Amount / (1.0 – 10% discount)

Let’s start by adding two more columns (Original Item Price and 10% Discount

A
B
C
D
E
F
G
H
I
Date Of Sale
Cash Register Receipt #
Item #
Description
Cash Register Shown Amount
Item Quantity
Total Cash Register Receipt
Original Item Price
10% discount

I place my discount of 10 percent in each cell of column I (.1). I can then use the following formula in cell H2.
=E2/(1-I2)

C
D
E
F
G
H
I
Item #
Description
Cash Register Shown Amount
Item Quantity
Total Cash Register Receipt
Original Item Price
10% discount
1
Delivery fee
100
1
=SUMIF(B:B,B2,E:E)
=E2/(1-I2)
0.1
18
Stove
975
1
=SUMIF(B:B,B3,E:E)
=E3/(1-I3)
0.1
19
Microwave
450
1
=SUMIF(B:B,B4,E:E)
=E4/(1-I4)
0.1
17
Refrigerator
1200
1
=SUMIF(B:B,B5,E:E)
=E5/(1-I5)
0.1
23
Trash Compactor
260
1
=SUMIF(B:B,B6,E:E)
=E6/(1-I6)
0.1
1
Delivery fee
100
1
=SUMIF(B:B,B7,E:E)
=E7/(1-I7)
0.1
20
Dishwasher
375
1
=SUMIF(B:B,B8,E:E)
=E8/(1-I8)
0.1
21
Sink
200
1
=SUMIF(B:B,B9,E:E)
=E9/(1-I9)
0.1
22
Faucet
150
1
=SUMIF(B:B,B10,E:E)
=E10/(1-I10)
0.1

C
D
E
F
G
H
I
Item #
Description
 Cash Register Shown Amount
 Item Quantity
Total Cash Register Receipt
 Original Item Price
10% discount
1
Delivery fee
           100.00
          1
         1,525.00
           111.11
10%
18
Stove
           975.00
          1
         1,525.00
       1,083.33
10%
19
Microwave
           450.00
          1
         1,525.00
           500.00
10%
17
Refrigerator
       1,200.00
          1
         1,460.00
       1,333.33
10%
23
Trash Compactor
           260.00
          1
         1,460.00
           288.89
10%
1
Delivery fee
           100.00
          1
            825.00
           111.11
10%
20
Dishwasher
           375.00
          1
            825.00
           416.67
10%
21
Sink
           200.00
          1
            825.00
           222.22
10%
22
Faucet
           150.00
          1
            825.00
           166.67
10%

Now I know my original price before the 10% discount.

With that information I can simply subtract the Original Item Price from the Cash Register Shown Amount to find out how much of a discount was given per item.

I can also use the same =sumif function above to determine the original Receipt price but this time using column H instead of column E in my formula.

Finally I can see how much discount was applied to the entire cash register receipt by simply subtracting the Original Cash Register Receipt Price to the Total Cash Register Receipt (after the 10% had been taken off).

D
E
F
G
H
I
J
K
L
Description
 Cash Register Shown Amount
 Item Quantity
Total Cash Register Receipt
 Original Item Price
10% discount
 Discount Applied per item
 Original Cash Register Receipt Price
 Original To Discount Cash Register Amount
Delivery fee
           100.00
          1
         1,525.00
           111.11
10%
        11.11
         1,694.44
                  169.44
Stove
           975.00
          1
         1,525.00
       1,083.33
10%
      108.33
         1,694.44
                  169.44
Microwave
           450.00
          1
         1,525.00
           500.00
10%
        50.00
         1,694.44
                  169.44
Refrigerator
       1,200.00
          1
         1,460.00
       1,333.33
10%
      133.33
         1,622.22
                  162.22
Trash Compactor
           260.00
          1
         1,460.00
           288.89
10%
        28.89
         1,622.22
                  162.22
Delivery fee
           100.00
          1
            825.00
           111.11
10%
        11.11
            916.67
                    91.67
Dishwasher
           375.00
          1
            825.00
           416.67
10%
        41.67
            916.67
                    91.67
Sink
           200.00
          1
            825.00
           222.22
10%
        22.22
            916.67
                    91.67
Faucet
           150.00
          1
            825.00
           166.67
10%
        16.67
            916.67
                    91.67

So here is your challenge. How would you modify the above formula(s) to accommodate changes in item quantity?

No comments:

Post a Comment