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