0:13

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.

Â