Excel & Business Math 37: Trade Discounts, Series Discounts, Net Cost Equivalents & Wholesale Cost


Welcome to Excel and
Business Math, video 37. In this video, we got to talk
about trade discounts, series discounts, net cost
equivalents, and wholesale cost. Now we have all sorts of awesome
topics, but here’s the basics. We’re going to get a
wholesale catalog like this, listing the products,
the list price. That’s the suggested
retail price that it should sell
for in the store. And instead of telling us
how much the business should pay when it buys the
boomeranging product to resell, they give us something
called a trade discount. So from this wholesale
catalog, we’ll have to be able to calculate
each one of the wholesale costs for each one of the products. Now we’ll see some different
variations, including single trade discount
percentages and series trade discount percentages. And last example in the video,
when we get to series discounts and the discounts are dependent
on the quantity purchased, we’ll see this
cool formula where we get to use IF
and VLOOKUP together to make our calculations easy. Now we have to
start out by talking about terms relating to retail
and wholesale businesses. So I want to go over
to the sheet list price wholesale cost. Now a retail business
is a business that sells to the final customer. And the list price,
which is also known as the suggested
retail price, is the price that
the final customer pays, meaning the full
price that the customer pays if there is no markdown. For example, target sells shoes
and Coke to the final customer. The shoes, the customer pays
$34.99 and $1.79 for the Coke. Another example, Kite
Flight is a kite store that sells boomerangs. So they sell a Bellen
boomerang for $24.95 to the final customer. Now final customer usually
pays the list price or suggested retail
price, because lots of times the businesses will
mark down or change the price. But there is a list price, which
is the suggested retail price. And the final customer
has to pay tax. Now wholesale business,
that’s a business that sells items to
other businesses, usually in large
quantities or in bulk. Wholesale cost, also
known as wholesale price. Net cost, cost of goods sold. That’s the dollar amount
paid for one business to the other for a single item. That single item is usually
a discounted list price. So for example– oh, here’s
Coca-Cola selling to Target. And when Coca-Cola
sells to target, they charge them $0.95 each. So that means when Target
sells the final product to the final customer,
$1.79, but when they buy it, they only paid $0.95. Of course that has
to be, because target couldn’t survive if
they bought it for $0.95 and sold it for $0.95. The Kite Flight example up here,
they sold to the final customer for $24.95, but when Kite Flight
was the customer that means they bought from the
manufacturer Gel Boomerangs. Gel Boomerangs sold lots
of Bellen boomerangs to Kite Flight at $13.72 each. So the purchaser pays the
wholesale cost for net costs and there is no tax. Tax is only when the final
customer makes the purchase. Now let’s go over to the sheet
trade discount dollar amount. Here’s the same example. And in fact, this will be
one of the examples we use throughout this whole video. Gel Boomerangs sells
boomerangs to Kite Flight. And the particular boomerang
Bellen is sold for $13.72 each. Kite Flight sells the boomerang
to the customer for $24.95. Customer buys the
boomerang from Kite Flight. Uh-oh, that’s not big enough. And then has fun throwing
and catching boomerangs. So the list price is $24.95,
the wholesale cost $13.72. When we subtract
these two, that’s the amount called trade
discount dollar amount. Now the trade
discount dollar amount is also known as
markup or gross profit. When we’re talking
about wholesale cost, this is the term we use. When we’re talking about
markups and markdown, we might call that the markup. And if we were talking
about cost of goods sold, meaning instead of using
the word wholesale cost, we’re using cost of
goods sold, you’d hear the word gross profit here. But when we’re talking about
selling from one company to another, list price,
wholesale cost, and trade discount amount are common. Now we want to go over to
the sheet wholesale catalogs and look at four different
types of trade discounts. Now we have 1, 2, 3, 4 different
wholesale cost catalogs. Now I’m going to zoom in. Gel Boomerang sends Kite
Flight a wholesale catalog for boomerang prices. And the first one
we want to look at is a single trade discount
percentage wholesale catalog. So we have the product
name, a picture of a product, the list price. That’s how much we charge
the final customer when there’s no markdown or markup. And here’s the single
trade discount percentage. So when Kite Flight goes to
buy the Bellen boomerang listed at $24.95, they actually
get a 45% discount when they purchase it wholesale. Now there’s a few ways
that we can calculate the final wholesale cost. One way is to
simply take the 45% and multiply it
by the list price. That gives us the
amount of the discount and then subtract the
discount from the $24.95. However, we have learned
a lot about percentages. And when we see 45%,
we know that 100% would be $24.95 divided by $24.95. So 45%, that
represents 45 pennies for every $1 of list price. That is the discount. That means if we do 1
minus 45 pennies that would give us 55 pennies. That means 55
pennies of every $1 here is actually
the wholesale cost. Now, we’ll actually see
how to do it both methods. But up here, the
calculation we’re going to use most often to
calculate wholesale cost is we’ll take list price times– and then in parentheses, knowing
that two percentages always add up to the whole
or 100%, we’ll take 1 minus the trade
discount percentage, multiply that by the
list price, and we’ll get our wholesale costs. Now, in this example, we have a
single trade discount percent. Sometimes they give it
to us a series discount. So we have the same three
columns, the product, the picture, the list price. But look at this, series
trade discount percentage. And they didn’t give it
to us as a percentage. They gave it to
as listed as 43/5. So for the Bellen,
it’s listed as 45/9. That actually means 45%. That is applied
first as a discount. Then when we have
the correct amount, we apply a second
calculation using 9%. Now, just as we saw
in our first example, we don’t have to do
it in multiple steps. We can actually calculate
the wholesale cost by taking the list
price and multiplying 1 minus the first
trade discount times 1 minus the second trade discount. If we make that
calculation, that’s the correct wholesale cost. Now we’ll do a series trade
discount example the long way, and then we’ll do
it the short way. So we’ll get to
see it both ways. But when it comes to
making the calculation, that’s our formula. Now, let’s scroll over. Sometimes you’re given
the same exact list price, but you don’t see it listed as
a whole number with a slash. You’ll actually see the two
different percentage discount. Not only that, but
oftentimes, you’re not given the second discount,
unless you pass some hurdle. So any time Kite
Flight buys wholesale, they’re not going to
pay the list price. They’re going to
pay 1 minus 45%. That’s the first trade discount. But guess what? They get a second 9% discount
if they buy 100 or more. Now if Kite Flight decided
to buy the Bellen boomerang, the Bellen wholesale cost
when they buy more than 100 would be same thing we just saw. It’s a double discount. We take what’s called
the complement, 1 minus the percentage. We have to do it
for both discounts, multiply them, and then multiply
them times the list price. That formula right there gives
us the correct wholesale cost of $12.49 for each Bellen. Now, we saw a series discount
with two trade discounts. We definitely can have
three trade discounts. In this catalog here, we’re
offering 43% trade discount. Then if you buy a 100 or
more, you get five extra. And for the Quad, if
you buy 500 or more, you get a third trade
discount of 15%. So for our Bellen,
it’s 45, 9, and 14. So if we were to
buy more than 500, here would be the calculation
for wholesale cost, list price times 1, 2, 3
complements all in a row. You simply take 1
minus the discount. And for us, when we’re doing
it in a single cell calculating it, it’ll be in parentheses
times the next 1 minus the discount times the
third 1 minus the discount. So this would be our
formula, and look at that. When we buy 500, for
the Bellen, it’s $10.74. Now, let’s go see how to
make these calculations. We want to go over to the
sheet single trade discount percentage. Here’s the Quad and the Bellen,
just the two first products in that catalog. Here’s $24.95. Here’s the 45% discount. I’ve listed all
the details here. If we were going to
do it in two steps, we calculate the
trade discount first. That is simple. We simply take 24.95 times 45. Now, we run the risk
of having decimals pass the position when we’re
multiplying decimals like this. We’re required to
round, because of money. I’m not going to use this
in a subsequent calculation. So really the third
condition for requiring round is not met here. But in the next
couple videos, when we start doing invoicing
and adding the totals, we are definitely going to
have to worry about rounding. So for all the
examples in this video, when we’re calculating
the dollar amounts, I’m going to round. So it equals round. Number, we simply
take– hey, there’s the list price times single
trade discount percentage comma 2, because we’re
rounding to the penny, close parentheses. When I hit Enter, $11.23
is our trade discount. Now, the wholesale
cost is simple. I say list price minus the
discount that was given to us. And that’s how much
we pay, $13.73. Now, there is a special name,
net cost equivalent for taking 1 minus the discount. Now, we only have
one discount here. So we don’t have to
do the multiplying. We just have to take 1
minus that to get what’s called the net cost equivalent. So I’m going do that. It equals 1 minus single
trade discount percentage. And when I hit Enter, 55%. 55 pennies for every $1 of
list is what we have to pay. So when we do our calculation– and I’m going to use
round here, because this is the dollar amount, the
actual list price times net cost equivalent. By the way, don’t ever
round the actual net cost equivalent or any
of the percentages that you’re multiplying
by each other. You want those unrounded it. This one happened to
come out nice and clean, but they don’t always come
out nice and clean like that. So we took dollar amount
times the net cost equivalent comma 2 close parentheses,
and that would be $3.72. Notice this is still
a 2-step method. The short method
and the method we’re going to use, especially
if you’re doing this a lot, is we’re dealing with money. So I’m going to round. We have to take the list
price and then times. This calculation has
to be in parentheses, because we have to
force subtraction first before multiplying. 1 minus the number of
pennies for the discount– when we take 1 minus the number
of pennies for the discount, that right there, that’s
called the net cost equivalent, we calculated it up
here, it’s actually how many pennies of
cost that the buyer must pay for every $1
in that list price. Now, I comma 2 close
parentheses Control-Enter. And that will be the formula
that we use for a single trade discount. We got the same exact
number each time. Now, let’s go and look at
how we do a series discount. Now, we’re not going to look
at the boomerang example. I just want to look
at a simple example with easy numbers, $100. And we’re given a
series discount, 40/20. So I already typed
them into the cell. Remember, if you see 40/20
and it’s a serious discount, it doesn’t mean the
whole number 40. It means 40 pennies or 40%. Here’s our second
trade discount. Now if we do the
multiple step method, it’ll help us to understand
exactly how the calculation is made. Now, I want to
calculate the dollar amount of the first discount. So we have $100 times
40 pennies of discount for every $1 of list price. So that means the amount of
our first discount is $40. Now the dollar amount
after the first discount, that’s the amount we’re going to
have to multiply times the 10%. Well, our first discount gave
us $40 that we get to subtract. So we simply take a list minus
the first amount of discount, and that gives us 60. So notice, we only
calculated the first one. 40% gives us an amount
of $60, but that’s not the wholesale price. Now, we have to calculate the
second step, dollar amount of the second discount. That means I take– hey,
there’s the $60 times the 10%. Now, when I hit
Enter, oh, that means we get to subtract another $6. So here I say $60 minus the
amount of the second discount and Enter. We get $54. So the wholesale
cost is actually $54, a series discount
of 40% and then 10%. Now if we want to calculate
the net cost equivalent, we can actually do that and
then use that single number times our list price. Now, the net cost equivalent
is 1 minus the 40% times 1 minus the 10%. So let’s do that down here
to calculate the net cost equivalent equals
an in parentheses is 1 minus the
first trade discount close parentheses
times in parentheses 1 minus the second trade
discount close parentheses. Really, what you’re doing
here is you’re taking 60%, because 1 minus 40% is 60%,
times or 1 minus 10% is 90%. So it’s like taking
60% times 90%. Said a different way, it’s like
taking the 90% times the 60%. When I hit Enter, guess what? That net cost equivalent
tells us how many pennies the product will cost for
every $1 of list price. Now, we can simply
come down here. And I have to use round,
because this is the final dollar amount, the total list price
times the net cost equivalent. Comma 2 close
parentheses and Enter. Now, I want to do this
one the other way. I’m going to copy all of
this and paste it down here. Delete that formula. Now, here we did the
net cost of equivalent, the complements of the two
trade discounts multiplied by each other. And then in our
wholesale cost formula, we simply took list times
that, but we can definitely do that all in one formula. So down here equals round. In the number argument, I’m
going to take the total list price times– and we can create the
net cost equivalent right in our formula. 1 minus the 40% times
in parentheses 1 minus the 10% close parentheses
comma 2 close parentheses. And when I hit Enter, 54. 1, 2, 3 different ways to
calculate the same number. Now, I want to take this formula
here and run Formula Evaluator. So we can see how Excel
calculates this step-by-step. If we go up to formula,
over the Formula Auditing, click on Evaluate Formula. We can see the underline. Click Evaluate or hit Enter. So it’s going to look
to find the number 100. Now, it’s going to look at
the first trade discount. Enter. It sees 40%. Notice, 1 minus 40% is 60%. Now, it’s going to get
rid of the parentheses. It’ll do this first
multiplication. Even though if we were
doing it on paper by hand, we would do the stuff
inside the parentheses, but it’s OK, because
it’ll eventually do the subtraction before
it does that multiplication. So when I hit Enter, oh,
we’ve got that first 60. Now, it’s going to look and
see what the second trade discount is. Enter. 1 minus that, oh, 90%. So 90% of 60– there is are 54. And it will it round
for our final answer. Now I’m going to click Escape. Now, we want to go look at
the series discount over here on the sheet series
trade discount percentage 2 successive
or sequential discounts. Here’s our Bellen and here’s
our $24.95 list price, but look at that. They give it to us as 45/9. So we literally have
to come over and type. And I’ve already
pre-formatted this. If you look up in
the Home Ribbon tab, I have it as a percentage. So when I type 45,
the percentage symbol pops up, and Enter. Now, I can type a 9 and Enter. So once we translate it
from the series trade discount as integers
into percentages, we can create our
formula equals round tab. We simply take the
list price times– and we have to take
our two complements. When we multiply
the two complements, it will give us our
net cost equivalent. So 1 minus first trade discount
close parentheses times in parentheses 1 minus 9%– that’s our second
trade discount– close parentheses comma 2
close parentheses. And when I hit Enter,
there it is, $12.49. Now for us series
discount it like this, they just give you that. You have to calculate it. I don’t know why they
don’t give you the 54%. And I have definitely
seen catalogs where they give you
the series discount. Now, the more common scenario
is as you get more discounts you have to purchase more. So let’s go look
at that example. Over here on series trade
discount percentage, second requires a
correct quantity. So here’s our Bellen
boomerang $24.95. The first trade discount is 45%. But if we buy a 100 or more,
we get an additional 9% trade discount. Now, the quantity
we purchased is 144. So we definitely get it. And we’re going to calculate
our total wholesale cost based on 144 purchased at
this series discount. Now, notice this looks
like a lookup table. Here is the first column,
second, third, fourth, and fifth. So we’re going to use VLOOKUP
to look up the prices, because then we can
simply change this product name and the quantity, and
everything will update. Not only that, but to
make it easy on myself, I’ve put the column
number off to the side. So in our formula, we can simply
refer to it and copy it down, and it will know to go to
the third, fourth, and fifth column. All right, so we’re going
to look up the list price based on this lookup item. Now notice, this is
not approximate match. This is exact match. I need my VLOOKUP formula to
look up exactly the character’s Bellen and find a match and then
jump over to the right column. So in cell C14 equals VL tab. The lookup value is
going to be Bellen. And guess what? I’m going to lock
that with the F4 key, because I’m going
to copy it down. And I want to always
look at the product name entered in C12 comma. Table array, that’s our table. First column always has the
item we’re trying to look up and find, get a match. Subsequent columns
have the items we want to go get and
bring back to the cells. Now, I have to lock
that with the F4 key– comma, column index number. Well, I was smart. I put them in the cell as
a relative cell reference. That is so cool– comma. We’re not doing an
approximate match. We are looking up
exact character. So I’m arrow down. When I put false in to
VLOOKUP’s fourth argument, it tells VLOOKUP to
do an exact match, not an approximate match. Tab, close parentheses,
Control-Enter. And now I’m going
to copy it down. And sure enough, if we
go to last cell and F2, it’s got all the
right cell references. And it looks like it got all
the right numbers I need. Now, the wholesale cost for one
Bellen boomerang then becomes equals– and this is money. So we have to around. I need to take the full
list price times the two complements. So 1 minus 45% times the
next complement 1 minus 9%. If I were to
evaluate all of those that together is the
net cost equivalent. That gives us how many
pennies per $1 of list that we actually pay
as a wholesale cost. Now comma 2 close
parentheses Control-Enter. And there it is, $12.49. Now this is the same example
we did just a second ago, but we’ll see we’ll be able
to change these inputs. And it will totally work. Now, this amount
needed to be rounded, because we were multiplying
and have the potential for lots of extraneous decimals. But guess what? Total wholesale cost, we
did not have the round. And the reason why
is because that’s an integer times
that’s a number that’s already been properly rounded. So when I hit Enter, that
is the total, $1,798.56. Now, the beauty of this is I
can come up and change this to Quad. And instantly the VLOOKUP
got the right price, the right first trade discount,
the correct second trade discount. It did its job here, and there’s
our total wholesale cost. Now I’m going to Control-Z
and leave it as Bellen. Now, this example involves
two trade discounts. Let’s now go over
to the sheet series trade discounts per cent second
and third based on quantity. Now, here is our
wholesale catalog. We have the same two
products, list price, and there is trade
discount 1, 2, and 3. Now, in order to get
trade discount 2, you have to buy 100 or more. For a trade discount 3, you
have to buy 500 or more. Now, same situation,
we have the product. Here’s the quantity purchased. And I’m going to change
this to 500 to start off. We need to get the
list price and then 1, 2, 3 trade discounts. Notice, I’m going to
do the same thing. I put the column numbers
for VLOOKUP, list prices in the third column,
trade discount is in 4. This is 5, and the third
discount is in column 6. So we’re going to
use equals VL tab. I’m going to get Bellen. And I need to lock
it with the F4 key. So as I copy down, it remains
locked on Bellen, comma. Table– it’s the whole table
all the way to column 6. I need to lock it. So a hit F4, comma. Column index– I’m
going to be smart. I’m going to put it off to
the side as a relative cell reference. Comma, false, down arrow,
tab, close parentheses, Control-Enter. And let’s copy it
all the way down. And sure enough, F2 cell
references are looking good. And it got the right list price
and our three trade discounts. Now, let’s do our round. We’re going to take list price. In writing our formula,
we’re going to multiply it. And we’re going to create
our net cost equivalent. Here, though, we have to do
three total complements or 1 minus each discount. Multiplying the
complement, always 1 minus. So there we have
three complements. When we multiply, that will
be the net cost equivalent, the number of pennies
for every $1 of list that goes to wholesale costs. Well, look at that. I can already see I’m
missing a cell reference. Wow. The new price is $500. That would be terrible. So I’m going to come down
here and double click C13. I don’t have to delete it. I just click on the cell I want. Hopefully, that will work. I’ve come to the end. Comma 2 close parentheses–
because we’re rounding to the penny– Control-Enter, and
there it is, $10.74. Now we can calculate total
wholesale cost equals. We’ve got a whole integer
there times a number that’s already properly rounded. So we don’t need to
round and Enter, $5,370. And the beauty of
this, of course, is I can change this to Quad and
Enter, and everything updates. Now, I’m going to Control-Z.
There is a problem, however. If I change this to $250,
although, Kite Flight would love it that they got
this third discount, Gel Boomerangs would
not be paid enough for each one of the boomerangs. It shouldn’t be $10.74. It should be our
$12 and few pennies. If I were to manually change
this to 0– oh, there it is. There’s the $12.49. Well, I don’t want
to have to do that. I’m going to
Control-Z, but that 0 should give us a good idea
of how to fix our formula. Now, I’m going to
Control-Z. Now, I’m going to leave this
one here, Control-Z again. Leave it like that. And in our next example, we’ll
amend your formula so no matter which product or
quantity we put in, we’ll always get the
correct wholesale cost. Now I’m going to scroll
over a bit, IF and VLOOKUP. Now, I’ve already put
the formulas here, F2. There’s the VLOOKUP. We copied it all
the way down, F2, to the third trade discount. And then we have F2. There’s our round with all
of our complements, F2, and there’s the total. Now let’s change this
to 250 and Enter. Right there, that needs to be 0. If this was 99, this one
would have to be 0 too. So F2– this formula fine. That VLOOKUP always
needs to run. It’s these two right here. Well, let’s think about this. Right now, if quantity is
99, that needs to be 0, and so does that. That means both of these
cells get one of two things, either the cell
needs to contain 0 or it needs to
contain the VLOOKUP. That sounds like the perfect
job for the IF function. Now, we’re going to have to
add an IF to this formula and this one separately. Because guess what? In order to get the second
series trade discount, quantity has to be greater
than or equal to 100. In order to get
the third discount, quantity has to be greater
than or equal to 500. No problem. We’ll edit each formula. F2, this is the second
series discount. So right after the equal
sign, I’m going to say IF. Now logical test, notice
that the VLOOKUP is already sitting there. So very carefully,
after the parentheses, I’m going to type
my logical test. Now watch what happens when I
click on Quantity Purchased. Notice, it’s black. That means the formula
is incorrect right now, because you cannot have a cell
reference next to VLOOKUP. But no problem, I’m going
to keep building it. The question is, is quantity
purchased greater than or equal to the 100? That’s the hurdle for
the second discount. Now, watch when I type a
comma, instantly all the color comes back, because
now Excel understands that that’s the logical test
and this is the value if true. That means if 99 really
is bigger than 100, then please put
VLOOKUP in the cell. Now we come to the end,
comma, value if false. That’s if, for example, quantity
is less than 100 like 99. Then we want to show a 0. Close parentheses and Enter. Wow, that is so cool. Now, it’s working fine. If I move this up to 150 just
to test it, that is working. Now, we still have
to fix this one F2. We have a slightly
different logical test. If in the logical test– I’m going to ask
the question– is quantity purchased greater than
or equal to the 500 hurdle? Now as soon as I type a comma,
everything’s looking good. Value if true, that’s
what I want in the cell if it comes out true. Come to the end, type a comma. And in value if false,
I’m going to type 0. Close parentheses and Enter. Now let’s test it. Let’s type 99 and Enter. Just like that, it is working. Not only that, but
F2– this formula works fine, because
1 minus 0 is. 1 minus 0 is 1,
and 1 times 1 times the complement here will
give us exactly what we want. Now if I change this
to 250, it is working. 499, oh, that’s a bummer. Notice $12.49 each. All I have to do is
buy one more 500. And I when I hit Enter,
everything is working. And the new cost for
one Bellen is $10.74. All right, that’s
our last example. We actually have some
cool homework problems over here for you to do
for homework problems. And in this video, we learned
how to calculate wholesale cost based on trade discounts. On this sheet, we saw three
trade discounts called a series discount. We also on this sheet saw
how to alter our formula with IF and VLOOKUP. So it doesn’t matter
what quantity or product name we type in here. Over on the sheet that
ends second quantity, we saw a series discount
that had to trade discounts. Over on the sheet
series TD percent, we saw sometimes were
given trade discounts that are listed as integers. And we have to be sure and
list them as percentages. We talked about three
different methods to calculate series discounts. We saw how to calculate a
straight single discount, three different methods. We’ve talked about
wholesale catalogs. We talked about what
a trade discount is. And we started off by
talking about the difference between retail businesses
and wholesale business, including list price
in wholesale cost. And if you feel
like that video, be sure to click that thumbs-up. Leave a comment and [INAUDIBLE],,
because there’s always lots more videos to come from
Excel that’s fun, including next video, video number 38. We’ll talk about cash discounts,
which are discounts given when you pay your invoice early. All right, we’ll
see you next video.

Leave a Reply

Your email address will not be published. Required fields are marked *