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