### 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?