Welcome to Excel & Business Math-- video number 40.
Hey, in this video, we got to talk
about partial payments on an invoice
with a cash discount applied.
That means, we have a bill of $125.
The terms are 3% discount if we pay within 10 days.
And if we pay $50--
not the whole amount--
$50 we pay within that 10-day window.
We cannot take the $50 subtract it from the $125 to get
the remaining balance.
We actually have to do a special calculation.
When we apply $50, that gets the cash discount,
and credit the account $51.55.
Now this calculation is not hard because, guess what?
We get to use what we learned from video number 38
about cash discounts and video 23
where we learned about increase and decrease problems.
Now let's go over to the sheet Partial Payment
and look at our first example.
Partial Payment on an Invoice with Cash Discount Applied--
example number 1.
Total invoice-- $125.
The terms are 3/10, net 40.
And the invoice date is 03/08/2018.
Partial payment is $50, and that is made on 03/17/2018.
And the question is, how much should the account be credited?
Well let's enter our numbers--
125 Enter.
The terms are 3% discount if paid within 10 days.
Otherwise, the total amount is due within 40 days
without avoiding a penalty.
Invoice date-- 3/8/2018.
And we paid it on 3/17/2018.
Partial payment made before the discount date.
We didn't pay the whole amount.
We only paid $50.
Cash discount-- there it is right there.
I'm going to enter it as a decimal--
0.03.
Number of days to take the discount-- that's the 10.
10.
Now I'm going to calculate this to make sure that Excel
tells me true or false.
So I first want to calculate days between date paid
and invoice date.
Hopefully it's less than 10.
So we do date math.
There's the invoice pay date minus the invoice date.
Remember, it's always later date minus earlier date.
And when I hit Enter, yes indeed, we get the discount.
9 days it took us to pay.
Now I'm going to do a formula and I'm
going to ask the question, hey, number
of days it took us to pay, are you less
than or equal to the number of days
they gave us to take the discount?
Enter.
Uh-oh.
What's the partial payment?
Now before we solve this with an increase/decrease problem,
let's just think about common sense
and how we could answer the question,
how much should the account be credited for?
First, think about how much you would have to pay if you owed
just $1.00 and you were given a 3% discount.
You would not have to pay the whole $1.00.
You would have to pay $1.00 minus $0.03,
so you would only have to pay $0.97.
So the idea is, you owed $1.00.
You paid within the discount period, so you only paid $0.97.
When you paid $0.97, they wiped away your $1.00 debt.
This means for every $1.00 in the account balance,
you only need to pay $0.97.
So if someone pays $50, you cannot just take the $50 off
the account.
You must take $1.00 off that $50 for every $0.97 that you pay.
Well, how in the world do we do that?
Well, we ask the question, how many $0.97's are in $50?
And of course, dividing $50 by $0.97
tells us how many $0.97's are in $50.
So this is the calculation.
This is how we credit the account.
We take $50 divided by $1.00 minus $0.03,
which gives us $50 divided by $0.97, and we get $51.55--
so the partial payment.
And of course, we have to use round.
We're dealing with money.
We're going to use this in a subsequent calculation.
And we have the potential of extraneous decimals.
I'm taking the $50, and I'm going
to divide it by 1 minus the discount, close parentheses.
Internally, that gives us the calculation.
But we need to round it to the penny, and Enter.
There it is-- $51.55.
Now to get the remaining balance,
I say, hey, there's the whole $125 minus.
Notice the business has to subtract
$51.55 even though they were only given $50--
and Enter.
Now let's look at this from a slightly different point
of view.
If you remember from video number 23,
we have a begin amount.
And if it's a decrease problem, which it is,
we're going to start with the full credited amount
and go down based on some rate of change,
which is minus 3%, down to the end part, which is our 50.
Now it is the begin that we don't know,
so I'm going to question mark that.
The end part we know.
We know we went from some bigger credit amount down
to the amount we paid.
So I'm going to say Equals, and get the $50 from right
over there.
Now in a decrease problem, the begin amount is always 100%,
and the rate of change is going to be negative.
Now we already have our rate of change--
3%, or $0.03, for every dollar.
So watch this.
I'm not even going to type an Equals sign
to do a [INAUDIBLE].
I'm going to type Minus, and I'm going
to click on the this $0.03.
Now when I hit Enter, that gives a minus 3%.
Now I want you to notice something.
If I click in the cell and look up
in the formula bar, any time you type a Minus
or a Plus as the first character in the cell,
it will automatically put the Equals sign in for you.
That was also preformatted.
Now rate-- that's 1 plus the rate of change.
And what does rate mean?
That means the amount you multiply by the
begin to get the end.
100%, or $1, plus the rate of change, and when I hit Enter,
there's the $0.97 for every $1.00.
Now we want to remind ourselves, what was our awesome trick?
Any time we have a begin amount, and associated
part and associated rate, remember,
we have a change part, that's the amount it goes down;
the rate of change, that's the percentage change downward.
Then we have a second part--
the end part and the rate.
That's what it means by associated part and associated
rate.
So since we have end part and rate,
those are associated with each other.
So when I take the Hand--
remember how this diagram works.
We cover up what we're trying to solve with,
and there's the formula--
part divided by rate--
associated part divided by associated rate.
So right up here, Equals sign, associated part
divided by associated rate.
And when I hit Enter, that is another way to solve
and another way to think about how to solve this problem.
Now with that in mind, of course, we have a check amount.
Anytime we calculate that $75.43 and the $51.55,
I'm going to check by taking partial payment
and I'm going to multiply it by the rate--
in parentheses, 1 minus--
I had to type the minus here.
Over here we didn't, because we entered it as a negative.
But there it is, 1 minus that.
That's the Complement Rule we learned from video 38--
close parentheses and Enter.
And sure enough, I get $50.
All right, let's look at our second example
for partial payment on an invoice with cash discount
applied.
They gave us a 2% discount.
The invoice total was this, but we
couldn't make the whole payment, so we only paid $900.
So when we send this in, they better not
just credit our account for $900.
It better be more than $500.
Remember, 2% means for every $1.00 in that amount,
we only have to pay $0.98.
So how do we do that?
We need to bump this $900 up to something bigger than $900.
So I take Equals the amount we paid divided
by 1 minus the cash discount.
That 1 minus will give us 98.
Notice, any time we divide some number
by a number smaller than 1--
that's 0.98-- it will always result in a bigger
number than the numerator.
So when I close parentheses and Enter,
there's the amount we get credited.
I'm absolutely happy to take $18.37 as a cash discount.
Now, I actually forgot to round.
I'm going to hit F2.
And after the Equals sign, I'm going to type "ROUND."
And after the calculation, comma 2, close parentheses.
We do run the risk of extraneous decimals
if that percentage cash discount changes.
We're doing money, and I'm going to use it
in a subsequent calculation.
The amount that's still owed equals the full amount
minus the amount that was credited to our account--
and Enter.
So we got credited $918.37, and we still owe $934.38.
All right, in this video, we very importantly
saw how to take what we learned in video 38
about cash discounts.
And if we make a partial payment,
apply the concepts from increase/decrease
or just straight logic.
We got 3% discount.
So when we sent in $50, actually every $0.97 in that $50 got us
$1.00 taken off our bill.
All right, if you like that video,
be sure to click that Thumbs Up, leave a comment,
and so, because there's always lots more videos to come from
Excel-- it was fun-- including next video--
video 41-- where we'll talk about markup on cost and markup
on sell price.
All right, we'll see you next video.