Welcome. We're going to take a look at Excel today and Excel spreadsheet

and help you prepare a tool that we can use to prepare a simple accrual.

Excel's a very powerful tool for accountants in terms of making sure

you prepare a journal entry correctly,

and it allows you to quickly make changes as the parameters change.

So, we're going to look at the problem that we have here for preparing an accrual.

So, on November first, 2017,

Bitsy Company secures a short term borrowing of 100,000 at six percent interest,

and we know it's going to be due in 90 days with principal and interest.

So, let's set up a quick spreadsheet.

Now, I've already put in some of the comps.

It's really important to identify in

every spreadsheet exactly where your parameters are going to be.

You don't have to hard wire them into your formulas.

You want to set out everything in a table that shows you exactly what you've got.

So for example, let's enter the amount here that we have.

So let's put in a 100,000 for the amount,

and we're going to put in interest at six percent,

and the term is going to be 90 days for the loan, okay?

The loan date was November first, 2017.

So, enter the date now.

Always use the date formula function in Excel,

and what that does is it makes sure that you can do date math with that.

So just type in 11/1/17 and that will be in the date format.

Now the accrual date that we're going to be measuring the accrual at is year end.

So we're going to put in 12/31/17.

Now, doing date math,

I can subtract the number of days at 12/31/17 from the loan date.

So if I put that in and subtract those two dates,

I get 60 days.

Now, I can change the accural date later which

we'll try and you'll see and it will automatically correct this.

Now, we used the convention,

mainly in this course, of 360 days.

This is what's usually done.

A 360-day year with a 30-day month which

helps calculate an even amount of interest over the course of the year,

but we could change that.

I mean, we could do it otherwise.

But the daily rate then is going to be the stated interest rate divided by 360.

Now, notice we're entering these formulas.

Quick backup on the formula here.

So when we're entering these formulas,

we're just putting in cell references at the moment, all right?

So, it's possible that we can name

these cells to make it even clearer where

these are coming from and we're going to do this for the accrual.

So the accrual, we know,

is going to be the amount of the loan times the number of days to accrue.

The accrual we know is going to be the amount of the loan times the number of

days that we want to accrue times the daily rate.

In order to make the formula even more understandable,

what we can do is give the parameters of the variables

in the equation range names and that helps us identify very quickly where they came from.

So let's, for example, go to the $100,000,

the principal here, which is the amount of the loan.

If we go down and assign a range name to it now,

Excel will give you a default of

the label that happens to be right next to it which is exactly what we want.

So, let's put in amount and call that, the range name, Amount.

And let's go down, and then we'll do the same thing for the days outstanding.

And it conveniently provides us with Days_outstanding.

Again, exactly what we want, so let's choose that.

And then the daily rate,

and we define the name.

Now, we can make a formula that will be the amount

times the days outstanding times the daily rate and this will our accural.

So let's give that a try.

So let's create a formula using the range names we just created.

So now, we'll put in our formula to calculate the accrual,

and it's going to be equal to the Amount,

that's going to be times the Days_outstanding,

and then times the Daily_rate.

Now you see our formula here has exactly that in it.

So, the next person that comes along, or you yourself,

later when you've forgotten exactly how you made the spreadsheet,

can see exactly how this works.

So click enter, and we've got our accural.

Our accural for 60 days on this $100,000 loan that earn six percent interest is 1,000,

and we've put the journal entry in here automatically.

It's got the accrued interest and the note payable.

We can verify where that accrual comes from by using,

by the way, the trace precedence function.

Now that's up in formulas.

I can choose trace precedence.

So if I want to look at the accrual and I want to trace the precedence,

there they are right here.

Now I've also put in just a little sheet to show the journal entry that we have here,

and you can see that I've just had that come straight

down from the accrual and then the note payable is going to be the same point.

So my journal entry at the end is going to be accrued interest is,

debited and the note payable is credited.

Now, another thing we can do here is we can change it.

Now this was a nice even problem,

and in a CPA exam,

the CPA exam problems are often very even like this.

They have notes that are conveniently start the first of the month.

But what if it starts on the 17th of the month?

Well, I can just change this date here to November 17th.

And what that does is it gives me a new number of days outstanding.

But I've already created a formula down here that will take that

into account and calculate the number of days outstanding.

So, what that lets me do is it

lets me make any change and it will automatically update my journal entries.

So this is a tool I can use for any accrual

that I've got at end year for a short term note.

And that's our first tool and our toolbox for the course.

Excel Segment

Accounting Analysis II: Measurement and Disclosure of Liabilities

Conoce a los instructores