Transcripts
1. Introduction: Create Excel
accounting worksheet, enter opening balances
and add sub ledgers is a project-based course creating a new accounting
system using Excel. Although you might
be able to use other electronic
spreadsheet programs such as Google Sheets as well. We will start from
a blank sheet. So you could open up
a blank sheet and work straight through
the problem with it. However, we also provide
these downloadable items, which will be Excel worksheets, worksheet you might be able to open in Google Sheets as well. These worksheets will
be helpful if you want to jump forward within
the practice problem or if you would like to rework a particular component
within the practice problem, these Excel worksheets will
have at least two tabs. One tab will have
the completed work up until that point in time, including the work and the
related instructional video. The second tab will
have the information populated in the worksheet
up to the prior video so that you can then
populate the new information that will be contained in
the current presentation. We will be creating another
new accounting system. We'll be adding a
general journal, a general ledger, a trial balance, and
subsidiary ledgers, creating those and then enter
the beginning balances into the system so we can get a feel for doing the data
input into the system, imagining that we already had some balances or a
prior accounting system with beginning balances
that we're gonna be putting into our current
accounting system. The completed Excel worksheets
will be the final project.
2. Create General Journal : Excel accounting
practice problem, set up Excel worksheet, get ready because
it's time to AC. So here we are in Excel. You don't need access to
this workbook to follow along because we'll be starting
with a blank worksheet. But if you do have
access to this workbook, we have two tabs down below,
example and practice. The example tab in essence
DNA and answer key. Let's take a look at it now, we're going to be constructing this worksheet as the basis on which we're gonna be doing a comprehensive type of problem, left-hand side being worth of transactions,
journal entries. We'll go and then we're looking at the trial balance
where we're gonna have a quick look at the impact of the transactions
from a beginning point entries to
the ending point, we have a short list
of accounts here, because these are gonna
be the starting accounts that we're going
to put together. We're gonna put them together
based on the idea that we have a beginning balances that we need to put
into the system. In other words, typically when you're starting an
accounting system, whether you're changing
accounting systems from a prior system like a prior
software to another software, or whether you're just starting
a new accounting system. You often have prior
accounts that have already been set up that
you've got to put in essence, the beginning balances in. If you had a prior accounting
system that you were using, then one concept you
might try to do is say, Hey, look, I'm going to use
the prior accounting system as the basis for any
questions beyond that point. Use the cut-off date
which you would like to be usually January if it's a calendar year end so that
you have a full year and the current accounting
system and then roll in all your current data into the new accounting system
would be the general idea. Therefore, even if
you're starting the accounting systems sometime in the middle of the year, it could be useful
to try to replicate the information into your
new accounting system, run parallel to the
old accounting system. And then, and then move off the old accounting
system as you move forward into basically
the new period. That would be one concept if you had an old accounting system. Or you might have a
situation where you had an informal accounting system in the past and now
you're trying to formalize the accounting system. Most of the time people might have set up a checking account. They might have started a
couple of transactions. They might have some
inventory, for example. And we've got to put those
beginning balances on the books in order for
us to move forward from that point and do it
more formally going forward and doing that cutoff and just being able
to say, Hey, look, I'm doing what I need
to do in order to get the financial
statements correct at the beginning can be kind of challenging because
people feel like, how exactly am I going to record the transactions
in the past? Again, the general
idea would be, Hey, look, what's in the
past is in the past. We're going to have
the cutoff, which hopefully is going
to be January first. We're going to try to
make everything proper as of January 1st going forward, we're going to put the
beginning balances in place to the best of our ability
before that time, based on the records we have, we're going to imagine
this being the records. We have noticed
there's not gonna be any income statement accounts
related to it because the income statement
accounts for the prior period would have rolled over into
the equity accounts. We're only dealing with basically
balance sheet accounts. And we're going to
just basically imagine these are our
beginning balances. That's our first challenge
to set these up. So we'll set up these
accounts then in our, in our Excel system, we'll set them up in Excel. We'll color-code
them for fanciness, and that'll be helpful to just learn Excel as well as give us hopefully a better
idea about them. We'll have to add new
accounts as we go, which will be a challenge
when you're looking at an Excel worksheet because then we'll have to practice excel, inserting more
cells and so on and moving things around in such a way that
it'll still add up. And we'll see how to build this entire worksheet and
then adjust it as we go. Then we'll have the
general ledger accounts on the right-hand side. We'll try to color code the general ledger
accounts as well, and then we'll move from there. This will be basically
our starting point, and then we'll start to
enter transactions will add subsidiary ledgers
for accounts receivable, accounts payable, and then
we'll add basically inventory. Subledger is when
we start to add the inventory account and we'll
basically put on the book starting out each one
of these items and try to look into the
specialized needs that are related to them. Adding them to our
worksheet as we go. And you can imagine on a parallel basis what
would be happening and say to software if you were to be adding this kind
of information, how to software basically
do this and how could it help you meeting these needs? Then I'm gonna go back
on over here and we're going to go back to
our practice tab. Now I'm gonna do
this fairly quickly, but I'm trying not to
do it too quickly. So I'm gonna try to find that
happy medium point here. So first I'm going to increase the size
of the worksheets. So we're at 100 down here, I'm going to increase
the size plus the net up to maybe 160,
so we can see it. I might go down a little
bit from there in a bit, but then we're going to start and I'm just going
to build out where I want the journal entries to go when we're actually
entering transactions. So I'm gonna call that a
date field and then tab, we're going to have the
accounts that will be involved when we enter the
accounts and then debit. And credits. So I'm going to
format these items. Let's format the
entire worksheet first and give it our
number formatting. And then I'll format
each individual cell. So the baseline formatting, I'm gonna select
this triangle here. And that'll highlight
the entire worksheet or select it as they're
more properly termed, right-click on it
and we're gonna say that we want to
format the cells. Formatting the cells. We want to then go to
the, to the currency. This is what I like
to format it in. So I'm just going to tell
you what I would do. This is going to be the
brackets at the bottom. I'm going to put the negative
numbers read and bracketed. I'm gonna get rid
of the dollar sign, removed the dollar sign, and remove the decimals. So that's what I'll
typically do and it'll say, Okay, then I'll go to the font group and
go to the Bolden. Now also just realized
that obviously you can develop any kind
of formatting that you think is best as well. But when you're following along, you might want to try
to kind of follow along exactly what I'm doing here because that might make
it a little bit easier. You might not see that
I'm doing something that could have an
impact in the future. I might not see it.
It's just something that got used to doing. And then once you go
back and refine it again and work through problems against setup
for your own worksheets. You can then kind of deviate. That's what I would
basically suggests doing. And I have learned
that the hard way because teaching these
types of courses, I actually was able to
look at the textbooks, Excel courses and whatnot. And I was always
trying to say, well, that doesn't seem like
the easiest way to do it, but you don't have
to looking at it. It was a lot of times
they were right. And I would try to learn it this way and then look at
the deviations from it. And that was what I would
recommend in practice as well. If you're working in
an accounting firm trying to try to learn it the way it's done and
then then fix it. If you think there's any
improvements that can be made. In any case, I'm gonna make this top part black and white. So I'm gonna select
these items up top. We're gonna go into
the font group. We're gonna go to
the bucket to drop down and we'll make this black. And then I'm gonna make
the a here for the font. I'm going to make
that black and white. That's gonna be
my typical format of the headers that
I'm going to be using. And then I'm going to center it. So let's go to the Home tab up top alignment and let's
center this thing. There we have it. Now, the accounts
fields gonna need to be a little bit
wider typically. So I'm gonna grab this
in the middle of c, of b and c and pull
it to the right. So there we have that. These didn't sensor typically that one can stay to the left, but let's send her
the whole thing. I'm going to select this
again. Why didn't it? I didn't center it.
I did that center. There we go. Then I'm going to
format this date field. And what I would like to do is format the date field because it's a practice problem
to not have the year. A couple of ways
you can format it. If I go up top, I can hit the drop-down. And we've got the
date, the short date. But that short date still, if I put like 11, it puts a year in there and I'd rather just remove that
from the practice problems. So what I'm gonna do is I'm
gonna right-click on this. And I'm gonna go, Let's go to
the formatting of the cell, format the cell down below. And then if I was to go on to say to say the date formatting, this is the one I'm looking for, but you might not
have that because I think I might have
made that myself. So basically I could do
something like this. I can say, well, I would
like it to look like this, but without the year on the end. And if you want to change that, I can go to the custom
item down below and say there is basically
the formatting of it, the month, the day,
and then the year, and then you can just
eliminate the year. So I don't want the year. Let's just remove that. And that should do it. And when you just
say OK, and you just made a custom format, custom format has been created. So there we have that. And then I'm going to copy
that formatting down. Copy that formatting
down to here or so. I'm gonna make that blue
right now just so I can, I can see where, how far
down I formatted it. By selecting the bucket, I'm going to use this blue, which is what, which is what
the Excel is fungi use. So that's what,
that's what I do. You can find it here on the more colors and it's
in the standard area. And then that blue right there, that's the one. That's the one. And then I'm gonna say, okay, and then we can select
this whole thing and put, put brackets around
it, font group. And I'm going to put
brackets around it, so it's blue and bracketed. And then I'm just gonna
do the same thing for these accounts and
the account I'm going to leave the
cell the way it is. And I'm just going to
go down here and put some blue and blue
and brackets here. Let's go with the blue
and the brackets, or the brackets and the blue. Alright, so next time
let's stop it here. And then next time
we're going to go, if I go to the example tab, we'll start adding
the heart of this, which is gonna be
the accounts and we'll color-code
it and everything. And they will probably
have another one where we will start to get into the general ledger
and build the GL.
3. Create Trial Balance : Excel accounting
practice, problem, setup, Excel worksheet part number two, get ready because
it's time new Excel. Here we are in our
Excel worksheet in a prior presentations, we started out from
a blank sheet. So if you would like to
start from a blank sheet, you can take a look at
the prior presentations we're going to be
continuing on here. If you have access
to this workbook, There's two tabs down below, example and the practice tab, example tab in essence
being an answer key. Let's take a look at it now. Last time we built our information
on the left-hand side, which is where we're
going to be entering the transactions. Now we're going to be
constructing the trial balance, which is gonna give us a
quick look as we record the transactions so we can see the impact on the
accounts themselves, the accounts we're
going to be setting up, are you gonna be coming
from this work sheet imagining that these are in essence our
beginning balances, then we're actually going
to be adding accounts as we go through the
practice problem from there. Then we'll go through
and we will add our general ledger
type of accounts. And then as we need to add
new things going forward, we will do so things such as subsidiary ledgers for
the accounts receivable, the accounts payable, and
the inventory, and so on. Let's go back to
the practice tab we're gonna be
working over here. I'm going to make the column E a little bit shorter so we
can have it side-by-side. I'm going to grab this
middle item between E and F and just make it a
little bit smaller there. We're going to be
then saying this is gonna be our trial balance. So I'm going to
have the accounts. Now note that I'm gonna
pull this one a little bit to the right now
between F and G, I'm gonna put this
down on the third, the third row here, because what I would like to do then when I have my titles, I know it's gonna take me
three cells to enter a title. Let me show you that first. For example, if I have over here and I want
to type into here the title of beginning
trial balance. And I want to make
that to fit in a length about this length here. How could I do that? A couple of different ways. I could put my cursor on it. I convinced say that I want
to go to the home group or the Home tab and we want to wrap in the alignment
group wrapping the text. But if I do that, that
makes this whole, this whole rule
really a large road. Now if I'm going to use a table, I will typically do that. But I would like to, if I'm
not going to use a table, not to do that because I don't
want to impact everything to the right and
left of that cell. So what I'd like to do is do
that as little as possible. So I'm going to undo
that. And instead, I'm just going to type
each word that will fit on each line and take however
many lines it's going to take. I'm gonna type beginning. You can also use abbreviations,
of course, beginning, trial, balance, balance.
So there we have it. So now it's on, It takes three rows for that
to basically work. But it looks kind
of ugly like that. But if I put my header formatting
on it and I center it, then it cleans it up a bit. So if I go to the
Home tab and I go to the font group and
say I want to make this black, then white. And then I went to alignment and center it. There. It
looks pretty good. Now if you're using a table, then you can't do that so much
because the header row in the table or the header column needs to be in one
cell typically, but we're not doing
an actual table, meaning I'm not going to Insert, go to the Insert tab and insert
a table or a pivot table. I've just building
basically a worksheet. With a worksheet, I think
this format looks better. I'm gonna go back to
the first tab then. And so then I'm gonna put my header down here
because I'm only going to need one row for this one which is just
gonna be cold accounts. We're gonna put that down here. And you can also use
this format then to have this top area gives you name the worksheet or something like that if you wanted to. Which is kind of nice,
can't come in useful, but I'm just going
to make this whole thing black and white. Like you could put
trial balance up here in a different color
or something like that, where it's still in
the header area. I'm going to select
these and go to the font group and make
it black and white here. And then I'm gonna say
these are going to be my entries, entries. And then I'm going to have the Indian trial
balance, balance. Now I'm going to do
my Format Painter. We're going to select these
three and format paint it so I don't have
to do it manually. I'm just going to go over to the home home area, clipboard, paint brush and paint
brushes right there. And there's our formatting has been brushed with a paintbrush. Then I'm gonna say these are my beginning balance
accounts that I need. So I'm just going to list those
atoms is going to type in the accounts first and then
I'll get into the numbers. We won't add the numbers
yet because I'm going to do that with basically
a journal entry, imagining how we'd kind of enter them into an accounting system. And think about how that, why that might happen
if you are using other software like QuickBooks
or something like that. I'm going to minimize this. I'm just going to type
in my accounts here. If I misspell
anything, I apologize. Accounts, receivable
and inventory, and then furniture and
furniture and equipment. The equipment, It's funny. I usually say furniture
and fixtures, but because it sounds good. But whatever accumulated
depreciation, accounts payable, these
loan payable, equity. Now note when you're entering
data into the account, you might hit an account
and you're saying I wouldn't like I don't
like calling that visa. I want to call it
like a credit card payable or something like that, then you might want to make
those changes like later. But usually if you're trying to mirror something like
another set of books, what do you want to do is
first put the books in exactly as they show in the data
you're working with. And then if you want to adjust them out of that account,
you can't do so, especially if you're
working with someone else's books and
you're trying to make a trial balance so that
you can then put it into a tax return or
something like that. You're you're usually
better off to first start off with
exactly what you're given and then make adjustments if you
want to adjust from one account to the other
because you're going to have to go back and
communicate to a client. If you're dealing with a client
and say why you did what you did and if you
just make changes as you enter the data, because that's how you
want to put it into a tax return or
something like that. You're not gonna
be able to do that because you may change this
that aren't transparent. There's no audit trail to it. So in any case,
I'm just going to type those in the way
the data is given to me. Yeah, I'm gonna say let's spell
check it that I spell it. Okay. Spellcheck says I'm okay. If spellcheck says it's okay, then it must be okay. Then I'm just going to put zeros on the beginning balances. So I'm starting
at 0, so I'm not, I'm not in this
worksheet just going to enter the beginning
balances because we're going to imagine kind
of like we were in accounting software and we had to actually enter the beginning
balances in that format and think about the other
needs as we enter the data, for example,
accounts receivable, it's gonna need a
subsidiary ledger, which is going to break
it up by customer, the inventory needing
a subsidiary ledger breaking out by what
inventory we have. And the payable is going to need a subsidiary ledger by vendor. I'm going to enter these
beginning transactions as we might do it if we were to construct it in
accounting software and try to see if y that
would be needed, why you would need to do that. Then I'm going to
format these cells. I'm going to try
to make this this fancy as we did over here. The fancy green and
green for the assets. This is something you
might not of course do if you're working
on your own. But some colors is nice. And I think when we're
practicing on it, it's nice to have the color. I'm just going to select
these items up top. I'm gonna go to the font
group and I think I made this the dark green, dark green. And then the font then
needs to be a bright color. So I'm going to hit
the drop-down and make that, that light green. Looks amazing. I'm amazed. It's full of wonder. It's full of wonder,
I call it wonderful. Then we're going to
select these ones. I think I'll make these ones. These are the liabilities
font group dropped down. Let's make that the dark orange or was it this one that I use? This one. Then we're
gonna say drop down. And then we want the bright. What went Not that on the, on the, on the
font. There we go. There we go. You might
say, where's the borders? Will add the borders
in a second. But then we're going
to go to the equity. The equity we're gonna say
that one's going to be blue font group the bucket. And then I think I made this
one like this blue was it? I think last time I
made it this blue. That blue kind of like the other one, but
we'll keep it there. And then I'm gonna,
I'm gonna make this one, this light blue. Looks wonderful. The wonderfulness. Then let's select these items then we'll put a
border around it. Now you could go to the
font group and select all borders and make
the black border, which looks pretty good. I mean, that's fine, but it
can give you a little bit more fancy if you put a
white border around it, since we have this dark colors, that can be more fancy, fancy, defying, more fanciful. If we go to the
Font group up top, we're gonna go to the
borders drop-down. I'm going to scroll
all the way down to the more borders, more borders. There's not enough in there. We need more. And then I'm gonna hit the
color which is going to be white. I'm gonna
make it white. Then I'm going to outline the outer outline in
the middle outline, which is outlined
in this box below, but you can't see it
because it's white. So they're all white. So then I'm gonna say, okay, then it puts that
whiteboard or which might give it a little
bit more definition. So the black width fine too, but depends on your
preference there. The black would be easier. And then I'm also going to
say that the ending balance, this is going to be equal
to the sum of these two. And then I'll copy that down. I'm just going to copy
that formula down. So we're gonna have our
beginning numbers here, which are going to
start out at 0. We're then going to enter
our data here to get to the ending balance
at the beginning. When we do our
beginning balances, these two are gonna be the same because our beginning
point is 0. But when we start to enter data. Into our practice problems for the actual data in
the current period, we may then move over the ending balances to
the beginning balances. So with this worksheet
will give us that look right at what is happening
with the transaction. So we can enter the
transactions and see what's happening
to the accounts. And then we can go
to the added detail which will be on
the general ledger on the right-hand side. So that's kind of where
we're going with this. Then let's highlight
this one here. And I'm going to
format painted over to this column on the right, Home tab clipboard paintbrushes. And then we're gonna
put that right here. Boom. And then down below, let's say this is
gonna be the total, total debit and cred. Now the debits and credits over here we're going
to be representing in the traditional debit and
credit column over here, we're going to be squishing the debits and credits together, representing debits
with positive numbers, credits with negative numbers. And that will make the
formulas that we're gonna be using
simplified within Excel, we can use simple sum
functions for this item, simple sum function,
simple sum function, and that will make the
use of Excel easier. It will also allow us to a
nice worksheet like this to have three columns
instead of six columns. If I had a debit and credit
column for each one. And what's happened to
here to here to here. Tedious formulas, ugly mess. I think this is honestly the most efficient way to do it if you're doing it with Excel, which is really nice to
do in Excel because it's a transparent way to
see this information. So we're going to
break out the debits and credits here
when we post it, the debits will be
positive, credits negative. What does that allow us to do? Sum up the debits and
credits here with a simple sum
function, simple sum. And that'll tell us that these debits and credits
are in balance there. Of course I'll 0 right now, because the debits minus
credits will equal 0. And I can see that
very easily without having to add up
to column and then subtract them and see if they
actually line up and there could be off and I don't
see it because I'm tired. My eyeballs aren't
working right or whatever my head is
not working right. I blame it on my eyeballs, but it's probably my mind. I said my darn eyeballs
didn't see it retina. Now you're just not thinking. You're not thinking, right. Then we're gonna say this
is gonna be the net income. The net income. There is no net income yet because we haven't added any
equity accounts, but we're gonna add that later. Then I'm going to make these, I'm gonna make this our
black and white too, because this has got
the bottom lines. I'm going to make
that black and white. And let's make this
one black and white. So when we get to that, we'll make that black and white. This one, we're going to use our special formulas,
conditional formatting. So we're going to get fancy on this because this
is our check figures. So if everything's good to go, we want it to be green. If not, we wanted to turn red. We're gonna do some
fanciness here by going to the Home tab styles. We're gonna go to the Conditional Formatting
Highlight rules. And we're gonna say if it's, if it's greater than, let's say if it's
greater than one. And the reason I'm
not going to put 0 it because there might be
a kind of rounding off. There's like pennies.
If it's off by pennies, I'm not too concerned. I'm gonna say if it's
greater than one, I wanted to turn
red because then my debits and credits
earned and balance. If this was two, it turns red. And then, and then I'm gonna, I'm gonna say that I
also wanted to turn red. If I go to the conditional
formatting style, I also want you to
turn red if it's less than negative one. So I'm gonna say negative one, make it turn red again. Because that's not good either. That means my debits and credits don't want
that mean that there's more credits than negative,
than negative two. There it turns red. And then the last conditional
formatting, we'll, we'll select these items
that I do that for the whole thing. Now
I didn't do that. Well, I'll, I'll take this cell and format
painted over here. There we have that.
Now that's good. So now we'll take
these three cells. And lastly, conditional format. If it's between, then if
it's between negative 11, that's when I want
it to be green because that's where we
are in the good area. I wanted to safe area things. The world is good.
The world is good. Then we'll make that
like that. There we go. So now we got, if it turns, if I get negative two, turns red, it turns red. This one does two, negative two. And then if I say positive to, everything turns red,
everything turns red. But then if it's in balance, green and the world is, the world is fine,
the world is okay. It's okay. It's
okay. There we go. So now next time we're gonna
we're gonna start to build our general ledger on the right-hand side to
support these accounts.
4. Create General Ledger : Excel accounting
practice, problem, setup, Excel worksheet number three, Get ready because
we're about to excel. We are in our Excel worksheet
in prior presentations, we put this together
from a blank sheet we're gonna continue to
construct from here. If you have access
to this workbook, there's gonna be two
tabs on down below, an example tap and
a practice tab. The example tab, in essence
being an answer key, let's take a look at it now. On the left-hand side, we've been constructing the area where the journal
entries are gonna go, the debits and credits. Then we're gonna
post those to both the trial balance into the center area and
the general ledger. The trial balance having
these three columns, which is gonna give us a look at the transactions quickly to see the impact on the accounts that are
going to be affected. Then we're gonna post it
to the general ledger. That's where we're
at at this time. That's typically the
most tedious part of the construction
process here, the creation of the
general ledger, and we will do that now. So we're gonna go back
to the practice tab and start to build out
that general ledger. And essence only needs
to do with the GAO, which stands for
the general ledger, is to take these accounts
and put them in order over here and so that we can sort
our information by date. And we're not gonna put a
lot of other information, as you might see, like
an accounting software. They might put the
transaction type, was it a check,
was it a deposit, and so on, so forth. The customers involved, they
tried to add more stuff into a basic kind of GL type
of report, which is great. But we're not going to, we
don't need to do that for the basics of the GAO that's added information
that you can do. A database system which
again is nice to be able to. That's why you would have kind
of a database system that you can do those types of
things and combine more data. But the essence of
a general ledger is simply giving
the supporting data over a certain timeframe of
the activity behind by date, behind the accounts that
are gonna be impacted. So we're gonna have set
up the general ledger. There's no activity yet, and we're gonna try to
give it some room for transactions that we're going
to apply in the future. So first, I'm going
to try to make this column J a little skinnier. We could try to uniform the skinny columns by going
over to the E, for example, go into the home tab
clipboard and getting a paint brush and paint
brush yet so that it's kind of the same uniformed skinny notice you can also take it and then these two
columns you could try to hold control down. So the F2 non-adjacent areas
not next to each other ones. And then you can just adjust the width on one of them and
they'll both kind of adjust. And then we're going to say
at this top space right here, I'm going to save this row up top because I'm going to call
this the general ledger, but I'm also going to have
some check figures up top. So let's just for now, I'm gonna change this later. I'm just going to call
this two general, the gin neural ledger or the GL as it's
often referred to. I'm gonna make that black
and white up top so we know that that
space is reserved. And we'll then go
to the Font group making that black and white
for our normal headers. Then I'm going to say
that the I'm gonna try to refer the general
ledger accounts to the trial balance
with a formula. So I'm gonna say the first
one is going to be equal to the name of the
checking account. Then within there we're
gonna have a date field, we're going to have
an entry field, and then a balance, balance. The activity date of
each transaction, the entry of the transaction
is taking place, and then the Indian balance. And so then I can
try to format this. I'm going to try to make
this our color coding. So this is an asset
type of account. So I'm gonna try to make this
the asset and I could this, I could put my cursor here, maybe go to the Home tab, clipboard and paintbrushes and paintbrush that thing on down. So that looks good. And
then I'd like to have the checking account over the whole this whole basis
instead of out in the left, I think that would look nice. So let's go up top. I could do that by
going to the Home tab. And we could say, I'm gonna basically expanded and the alignment
group this way. But I don't like that because then it makes that one large cell instead of three cells which can mess things up. So
I don't typically do that. I'm going to undo. There's another way to do that. We can select those three
cells, right-click on it. And then we can go say I'm
going to format those cells, formatting them, and
go to the alignment. And then in this
horizontal area, we got this choice of center
across the selection. Then we got that
and there it is. Much better, way better
in my personal opinion, my personal
professional opinion, my personal and
professional opinion. Let's select these two. I'm going to center
this whole thing. Let's take these three
and then Home tab, Alignment and center
those three things. This date one, I want to have
it a date field against. They didn't show me a date. I can paint brushy
the date over here, that date field we used
on that one paintbrush, that one over here somewhere. Check it out, test it out. 11, that looks like the proper formatting.
That looks good. And then the balance is
going to be starting out at 0 on the beginning balance. So I could, you know,
they're all gonna be zeros. I could just basically type into zeros because they're not pulling any beginning balance. It's going to be in place.
Oftentimes they'll start off with a beginning balance. And sometimes if there
was a beginning balance, you might pull the
beginning balance basically from the beginning trial
balance so that you could type the numbers in here
and then basically make sure that they tie out
pulling them in over here. Then we'll have our
date information. So I want to make sure I use the same formatting and
put that down a bit. And so I'm going to take
this down a few cells. Let's bring it down to 12. Bring it down to 12, which isn't too far down. And actually, for the for
the checking account, I'm going to bring that
all the way down to 24 because we might have a lot of transactions in
the checking account. And that's getting these
uniform transactions could be a little bit tricky in terms of how long you want
these accounts to be. Every other GL, I'm going to
make half half that size. So I'll have to GL accounts
per, per section here. So let us see what
that'll look like. Then we're going to make this whole thing
blue and bordered. Let's blue and border that. I'm going to go to
the Font group. Drop-down, make that that blue, which is in the More
Colors option standard. It's that blue right there. That's the Excel is fun guy blue and then font group and
put the borders around it. So that's not, I just want normal borders this
time, normal boards. So there we have that. And then we could make it
a little bit skinnier, maybe can make it a skinnier
so we could save some space. So that looks pretty good. That looks pretty good. Now we've got our format down. So now I'm gonna, I'm gonna
paint brush this thing. I'm gonna take this, I'm just
going to take it down to 12 at this point and I'm going
to try to copy just that. Well, actually let's
copy this whole, let's copy this
whole thing first, I'm gonna take from j, the skinny to m, M a copy that whole thing. And then I'm gonna put that
up top right here, control V. And so there we have it. So now we've got the
same starting format. I'm gonna say this is
just gonna be equal to the accounts receivable, but then I'm not going
to make it as long. So the beginning balance, I could say that equals 0 and make sure it's the
0 in column G. And then I'm going to
just make it shorter. I don't want to make
it go down to 12 here. Everything after 12,
I'm gonna delete these. I can right-click and Delete. And I'm going to
shift the cells up because there's
nothing underneath it, so I'm just going to shift
them up and that won't mess anything else up there. We have that and then
I'm going to copy that. I'm going to copy that
one on down, on down. So I'm gonna copy
this whole thing here and put that right there. There we have the next
one and it lines up. It looks very nice. Wonderful. This is full of
wonder. Full of wonder. Call it wonderful. This is gonna be equal then to the inventory beginning balance, which is of course is 0 again. And then we could do that again. So now I'm gonna copy
this from the skinny. I'm going to copy
it from the skinny. To give me the skinny. What's the skinny? To the queue? The skinny to the queue. Copy that and paste
it on down here. Now, once you, once you
get a little further out, we can hide stuff as we go. So I couldn't say, I'm
gonna take the skinny here and go on over to
column Q because now I'm getting over to the right
and this is why I'm building it to the right so
that I can have this hiding feature
allowing us to have everything on one sheet,
which is kind of nice. The other way you
could do it is to have multiple sheets down below, but I'm going to try
to construct it on one sheet that we
can maneuver around, get everything close
together by hiding stuff. So I'm gonna right-click
and hide this stuff. There, we have it. So
what was I on now? I was on furniture and fixtures. Furniture and fixtures. That one. And then this one is
just gonna be from the beginning balance of the furniture and
equipment, not fixture. Furniture and equipment.
That doesn't sound. Even half is nice. Furniture and fixture does. But whatever, I guess
it's not important. I guess it's not important in Atmos too long of a
word that's okay too. Might make that a
little bit wider later. But there we have
that and then we can move on to the liabilities. Let's finish up the
liabilities and the equity. Next time I'm going
to put my cursor on, I scroll over two. Let go, right-click on that
selected area and unhide. And this is what we
have thus, thus far. So we will continue on with the liabilities and
equity next time.
5. Finish General Ledger : Excel accounting
practice, problem, setup, Excel worksheet
part number four, get ready because
we're about to excel. We are in our Excel worksheet
in prior presentations, we put this information
together from a blank sheet. So if you want to go back and
work from a blank sheets, you can if you have
access to this worksheet, then we'll have two tabs on down below the example tab
and the practice tab. Example tab in essence
being an answer key. Let's take a look at it now. On the left-hand side, we started out by constructing the area that we're gonna be
putting the journal entry, the debits and credits
to transactions, then the trial balance
with three columns, we're going to be posting
the trial balance here, given us a quick look at the
impact on the accounts and the balancing process to see how the double-entry
accounting system works. Then we're going to
be constructing and we are at this point in time constructing the general ledger accounts given us the support, the backup data information, breaking out the activity
in the accounts by date. Let's go back to
the practice tab. This is what we have thus farm. Now I'm going to start off here. We left this area kind of blank. I'd like to make that blue. That's where the data
input is gonna go and we've been making that
information blue. So let's start off there
and we're gonna go then to the Home tab, fonts grouped, make that blue, which
if you don't have that, it's in the dropdown, it's
in the more colors area. And the standard,
there's the blue, the accounting is fun blue. That's the channel font group. And then we'll
bracket that one up. There, we have it
now we left off with our general ledger
accounts over here. We're now focusing in on the
liability type of accounts. These types of accounts. Let's first copy
the same format of the GL and then we'll adjust
it to a liability types. I'm going to put my cursor
on the skinny here, on the skinny column, and then drag it on over to you. I'm gonna say Control
C or copy and put that in V and control V
and paste that on down. Now I need to color-code it a little bit differently here. So then let's try and
get a side-by-side with our information on the trial balance by taking
our skinny column here, left-click and selecting it
and drag it on over to the u. And then let go, right-click the
selected area Hide. So we got the side-by-side. We don't have to do the
scrolling around thing. One way I could try to do this as I could put my
cursor on this one, possibly, go up top and say Home tab, clipboard,
paintbrush yet. And then I want to
make this color coding like that color-coding. And then I can reformat
this and this is going to be equal to
the accounts payable. I'd like to center
this across here, so I'm gonna right-click on it and say, Let's center that. Let's center it and
needs to be like me, completely centered
at all times. Then we're gonna save
the journal we're gonna say and the alignment. We want to center it
across the selection. And then, okay, so
there we have it. Let's center these ones too. We're going to go up
top Home tab Alignment and center those items as well. The beginning balance might be coming from the beginning 0, they're all going to be zeros. And there we have it. Let's then take this formatting and format paintbrush yet
right on top of the next one. So we're just gonna format
paint brushy at home, tap, clipboard, paintbrush, and brush that paint
brush right there. And now that we've got
the paint on the brush, we just put that paint
where we want to put it right there,
making it orange. That's what color
paint that we had. And this is gonna be Visa. Will take the visa. And then what we'll do is put the beginning balance here
equals the beginning balance. And that's going to be right
there, which is of course 0. Let's do it one more
time for the equity. So let's take the skinny column, take the skinny on
out to call them why Control C put that on down, paste it down on the z, and let's do it again. So this is now we've
got the last liability, which is the loan payable. Loan payable. And the beginning
balance is gonna be 0, but we'll pull that
in from this 0, making sure it's in column
G naught, call them i. And then finally we've got this equity account
which is blue. So I'm going to do my
paint brushy, Home tab, clipboard, get that
blue paint on my brush, the blue paint on my brush. And then I brushed
this, making it blue because I had
blue on my paintbrush. And then this is
going to be equal to then the equity area. Then we'll center
this out again, I'm gonna select these items, right-click on it,
right-click on it. Format this thing, and then
alignment area horizontal, center it across that area. Poor father. Which means please, I'm pretty
sure I've been practicing. And then we're gonna center here Home tab, Alignment and center. Then we're going to basically
save this is going to be the beginning balance right
there. There we have it. Wonderful. Okay, so now let's unhide what we have so forth by
putting the cursor on column I to wi to w because the hidden
sales are in-between there. So we've selected across the hidden sales
right-clicking and unhide. It's time to unhide. Now. There we have all the stuff all the
stuff we've done thus far. So now we'd like to put
our check figures up top. And so what I'm gonna
do up top is try to put a check figure right here
and so that we have, we have a nice, a
nice check figures. So how could we do that? I'm going to delete the journal entry or
the GL right there. And I'm gonna say delete that
and I'm going to just sum up all the ends of the accounts. Now I would like to sum
up at the bottom so that every time I
enter a transaction, then it's going to be included. Meaning I can't sum up using
this number because it's 0. When I add the transaction here, it will be incorrect. So what I need to do
is sum this thing up, all the way down, have a running balance. The running balance
will simply be equal to the prior balance
plus the entry, which is a simple formula. That is due to the
fact that I only have one column for the
debits and credits. If it was a debit, it would be a positive number,
increasing it. If it was a credit, it would be a negative number
in this, in this case, decrease in it
because we're talking about a debit balance account. So then I can just
take that formula and then just copy it down. Just going to copy that
down to the bottom. And that last number is what
I want to put in my balance. So if I had this 100 up top and then I had a negative
50 or something like that. Then the 50 runs all the
way down to the bottom. I want to take the
bottom number as my check figures so that so
that I don't have to raise, sum them up each time. So that's going to
be the general idea. I'm gonna do that the same kind of thing
all the way across. I'm gonna save this one
equals to one above it plus the one to the left. And I can copy that formula
down to the bottom. And then it's the same
all the way down. So we've got that
running balance to bounce runs down to the bottom. It runs downhill like
water does to the bottom. There we have that. Then we pick it up right at the bottom. Once it's at the
bottom and the bucket, we pick it up in our
summing balance. Then the next one, Let's do
that all the way across. We're gonna say this
is the one above it plus the one to the left of it. We're gonna run
that balance down, run it down the hill. And there we have it. We
use gravity in our favor. Here. We use the force of gravity to run this balance
down the hill, run it down the hill, right there with the auto-fill. Auto-fill runs it down the hill. We got the one above it
plus the one to the left. And autofill runs
it down the hill. And so we got the
running balance one more or three more times. We get to do this three more
times, which is a pleasure. Here we go a couple
more times here. We get to do this
and then we have to stop because that's all
the accounts we have, but we will do it most likely in the future at
some point again. So don't be sad. We're gonna copy
this one down too. Okay, so then, then now we've got those ending
balances for our check figure. If we go to our check figure, I'm gonna say this equals
the ending balances, which is gonna be this one. This is a tedious formula. There'll be careful this one at the end plus the
end of this one, the one at the
bottom of that hill, plus the one at the bottom of the inventory hill with
a balanced ran too, because it followed the gravity plus the one at the bottom. The furniture and
equipment Hill, plus the one at the bottom of the accumulated
depreciation Hill, plus the one at the bottom of
the accounts payable Hill, plus the one at the
bottom of the visa Hill, plus the one at the bottom
of the loan payable Hill, plus the one at the bottom of
the equity hill and enter. Now you might say, how do I know if that balance is correct? It's difficult right now. But notice, once we
start entering data, if I had like one hundred, one hundred here, the other side has to go somewhere it would go and see that that's out of
balance now it needs to be 0. The other side would go
into like accounts payable, negative 100 or so we got a loan would be a
negative 100 here, and that would net out to 0. So this should always be 0. That's our check figure. It's the same check figure. I'm going to delete these items
here. What did I do here? This is just clusters. We deleted those atoms. I didn't mess anything up. So that's the same check figure that we have here with
our running balance. This way we're checking them independently instead of
tying them together so that we can have this nice
double-check that will allow us to basically look at the double-entry accounting
system two ways. Then what I wanted to do
up here is I want to do the same formatting
so that if it's, if it's out of balance than
I'd like it to turn red. And then we get
scared the world is bad because it's
red, things are red. So we got to say
what's going on, but then if it's green,
then the world is good. And everybody can be happy. We're gonna go down here
and format paint that. If I can go up top home group are going to paint
brush that one, brush the paint brush there. So now if I was to make
this like negative 100, so now it's out of balance, it's red and everybody needs to be scared and whatnot we got. If something needs
to be, something needs to be done around here, we can't have that redness. But then if it's
negative 100 Steam red, but then if it goes back to 0, then the world is
good and it's 0. So that's good. So
there we have that. And so that's gonna be
basically our starting point. Let's format a little
bit, a little bit more. We're also going to have
another check figure later on that I'm
going to put here, which is going to sum up the
income statement accounts. And then I'll tie that
out to what is on, what is on Vietnam
net income here. But there's nothing
in net income because we don't have any
income statement accounts. That'll be another kind of
check figure that we can have. So I'm gonna say this equals this number minus this number, which if it's proper,
then that should be 0. And I'm gonna format
paint it again. And that won't help us until we get income
statement accounts here. Format pane at same
kind of thing. If that turns red, then we
got something has to happen. We got fixed something, we've got to fix
things around here. But if it's 0,
then we can relax, go to the beach, whatever. There we have it,
So that looks good. And normally, I did highlight this whole space and make
that black and white. Black and white so that we can see that's
all one kind of GL. I guess we can keep the GOs. Usually I had less
jails across the top, but I guess that's
okay to have them on every space just to indicate
it's the same thing. So that looks pretty good. It looks pretty good. I
think we're good to go. So next time, we'll
actually start using this to record our
beginning balances now, which are gonna be,
which are gonna be here into our
worksheet as we do, we're gonna try to think about what problems we might have, like an accounting
software systems so that if it was like a database program,
like a QuickBooks, so that we can try to address
those problems and see how we enter that into Excel to see kind of like the
behind the scenes, constructing it in
an Excel worksheet. But also thinking about
what are the needs of a database program
and what is it doing as we construct
these items as well?
6. Inventory & Inventory Subsidiary Ledger: Excel accounting
practice problem, inventory and inventory
subsidiary ledger. Get ready because
we're about to Excel. Here we are in our
Excel worksheet and prior presentations we put together this worksheet
from a blank sheet, which is good practice if you would like to take
a look at that. And now we're gonna be using this worksheet in order to enter transactions starting with our beginning balance
transactions, two tabs on down bellow
example and practice example tab in essence
DNA and answer key. Let's take a look at it now. We're going to be entering
the beginning balance for our inventory. So if we look at the inventory, we're thinking about this accounts as being our
beginning balances. We're gonna start off
with the accounts that are a bit more difficult in that they have some kind of subsidiary information
we need to add to it. So for example, the inventory, probably the most
difficult if we're tracking inventory
in the system, we cannot just put
the dollar amount representing the
inventory that we have, but also need to support
that with inventory units. This is important when you're
building this in Excel. And you can imagine
if we're trying to mirror what would happen
in a database program, what the database program like a QuickBooks program might do, or other accounting software with regards to
this transaction, what they would like to be
able to say a say, Hey, look, you cannot just put the beginning balance
in adult or amount. If you want me to track
the items as well, you have to give me the
inventory items as well. So we can do that supporting a calculation and you
can imagine how or what kind of components can and cannot be automated in
that type of system. So for example, if I
go back on over to the QuickBooks here or back
to the Excel worksheet. We're going to enter our
journal entry to get that beginning balance
in the system. Then we're also going to go
all the way to the right over here and enter our
subsidiary ledger. We're going to imagine
that we're getting this from this information. This is our beginning
information with regards to the
inventory items. We're imagining our guitars, but we're just going
to list them here as items on the left. And then you can imagine
if you enter this into a QuickBooks or
something like that, you would have to
enter the information for the sales description, the purchase
description, and so on. Because you're going to use
this in order to populate bills, invoices, sales receipts. But what we really want is the cost here because
that's going to help us to support the amount of the beginning balance we're
putting on the books. And then we'd have
to sales price. We're not going
to deal with that right now because we're just really supporting what's
on the balance sheet, which is the cost and the number of units
that is on-hand. So if I was to multiply the number of units
times the cost, that would add up to that 2896. We're going to get an
idea of that concepts as we put it into
our system as we do. So you might want to be considering what kind
of components might a software system be automating and what kind of things can it not really automate as easily? And so I'm going to go
back on over to the left. And so I'm going to go into
our practice problem. Now. Just enter the transaction here. It would be an easy
transaction to enter. We would just simply say, okay, I'm gonna basically debit. Let's make this item a little
bit skinnier for the date, I'm gonna say we're gonna debit the inventory, increasing
the inventory. So I'm gonna say equals the
inventory the other side, then I'm going to
take to equity. Now notice I'm putting
this on the books as of January 1st because
really I'm saying that this information was
from the prior period and I'm putting it
on the books as of the beginning of the period. I'm not going to have anything
that's gonna be going to the income statement, but rather I'm just going to be putting everything on
balance sheet accounts. You might say, Hey, look, why don't you just do the
entire transaction here with the debits and
credits as it is shown on the trial balance, and you just need one
journal entry to do that. That's true. We do just need one journal entry
and within Excel, it might be easier
to do it that way. But note that if you were
trying to do this in a software like QuickBooks
or something like that. The software will
often be designed to try to manage one
account at a time. And that actually can
be useful because that allows us to construct the subsidiary
ledgers as we do it. So what we're going
to do then is enter each transaction
at a time, focusing in on one account
and the other side, we're just going to
be putting to equity. And that's how a lot of software
will be set up as well. And that means that if I get all my balance sheet
accounts incorrectly, the rest will kind of
wash out to equity. Equity will be the difference, which in our case
will be the 77896. Now we have something
here that clearly shows a balance sheet kind of activity and what equity is. You might have a
situation where you don't know what equity
is, but you know, you've got this much in
the checking account and so on and so forth. And you would have
to have equity as basically be in your plug, in that case being what you need to do in order
to reconcile. So we're gonna do this
instead of one journal entry as a piecemeal journal entry posting the other
side to each account, to the equity account, as you might see it in software. And so I'm going
to minimize this. So we're gonna say the
inventory needs to go on the books for the 2896. The debit, I'm gonna represent the credits with a negative, so I'm going to say
negative of that number. And so it's also in
the credit column and negative for that
double verification. We can indent this if
we want by going to the Home tab
Alignment and indent, given that added indent, if I post this here we can see the activities straight
on the trial balance. So we're gonna post it twice. So we could see the
activity in both areas, the beginning balances
are obviously 0 because we're entering the
beginning balances in place. Gonna put this in H6. This is going to be equal to, I'm gonna pull in
that debit 2896. And so it increases in
the debit direction represented by a
positive number to 2896, the credit is going in equity, which is unusual for like
if I was to buy inventory, but we're putting the beginning
balances on the books. And therefore that's
why it's going to net out into equity. Note that you might
be saying, well, what if it was a sole
proprietorship versus a partnership versus a company corporation? Well, then we could still
use the same method. Netting it out to equity. Equity as a total will
be will be correct. Then we can go in after this has all been
entered and adjust the equity accounts to what the proper accounts should be
for a sole proprietorship, we would just have one, the capital account or
equity for a partnership, we would have to
break it out or more, two or more for the partners, breaking out for the partners. And for a corporation, we'd have to break
it out between the common stock and the
retained earnings and so on. If that's gonna be the idea. So the equities isn't
going to go over here to the equity and
there we have it there. Now we can also need
to post this to the general ledger accounts. So I'm gonna post it
to the GL as well. So we got the third account
being the inventory account. So I'm gonna post this on
over here, go into inventory. And so that's gonna
be the third account which is down here in cell 017. I'm going to type 11 on the
date we're in cell PI in 17. I'm going to use a
formula to do this. You could hardcode
meeting type it in there, but it's better to
do with a formula because then you can
see what is going on. I'll show you that in a second. So I'm gonna say equals, I like to do this
just with the arrows left until you hit the wall, up until you hit the
transaction last transactions, the ones that we want
to record and C2. You may also just memorize
that you're trying to post C2 and just
type in equals C2. Once you have this in place, you can see that we're
out of balance up top. And if you're trying
to figure out why, you can go into
that cell and you can basically say where's
that coming from. You can use these icons to kind of trace back where he
could simply double-click. Those icons are in
the formulas area. The formula auditing
right there. I use them often so I put a shortcut to them
for right-clicking on it and then adding it
to my Toolbar up top, back to the Home tab, I'm going to remove that
blue line the other side, then it's going to
be going to equity. That's my last account, thus forests the blue ones. I'm gonna go to the right,
assets, then liabilities, and then the equity
account way down here. And so AAA 17, I'm going to type
a one dash one, an AB 17, I'm gonna say equals left arrow
till I hit the wall. Scrolling back up,
we're going to pick up that equity to 896 and enter. There we have our equity. If I go back to the left, then that puts us back in
balance up top and the GL. And that also puts us back, ties us to what we have in the, in the trial balance
hopefully as well. Now, we also need to
support this number with a subsidiary ledger telling us what kind of
inventory we have. Now to do this, you might use different methods depending
on the type of inventory. You might use specific
identification. If you had a large piece of inventory like
cars or something, or some kind of flow assumption. If basically normal, smaller units of inventory
that are the same, such as you'd be using First-In, First-Out, last-in, first-out
or weighted average. I don't want to
get too into depth on the differences between them, but it's a flow assumption. The QuickBooks Desktop
version, for example, by default, will use
the weighted average. And if you have like
an online version, it'll typically use the FIFO. Those are probably the two
most common flow assumptions. But you can get into a courses on how to calculate the
flow assumptions here, we just want to
show that you need that subsidiary
ledger in general. This will be a little bit
more convoluted to construct. So let's just take
us a bit of time. I'm gonna make this one a
little bit skinnier up top. I'm going to call this the
inventory subsidiary ledger. And then I'm gonna say
that there's gonna be three main categories. And each of those
categories are gonna have three columns in it. One is gonna be purchases 123, and the next is going
to be cost of goods sold 123 and then
ending inventory. So there we have that. And then I'm going to label the item that we're gonna
be putting in place. Is coming from our
items worksheet, which is this one. We're going to be
labeling these items. So I'm gonna say this
first one is an ELP. So I'm just going to
call that here an ELP. Elp. So there we have that. And then each one of these three components are
going to have three parts, which is units that we're
going to have of that guitar. And then we're going
to have the unit cost. Then we're going to
have the total cost. And I'm gonna copy that across same headers to
the cost of goods sold and to the
ending inventory. This is what we have
on the outline. I'm gonna say. All right, So then up top I'm going to make
this black and white because that's basically
our header font group making it black and white. And then down here
I'm going to choose these three so I can
center it across the top. They're going to right-click
and I'm going to use the form, the Format Cells. And I'd like to
center it across, which is going to be this one. And so we'll say, okay, and then I'm going to make
that, that green and green. So I'm going to hit
the bucket up top. We're gonna make
that the dark green and then this one
with the light green. And then I'll do the
same thing here, formatting and these three
right-click on this. I'm going to right-click
on this one. And we're going to
format those cells. And we'll say alignment. Let's center that across. Okay, right-click on it
or let's do it this way. Drop-down, up top, making that the dark yellow and
then the orange on top. And then same thing here. Let's try doing it this way. I'm going to select these
three paintbrush it, a paintbrush on down, but then change the color. This one's that
dark blue with the light blue on top of it. Then on this one
I'm also going to make this that green,
green and green. So let's make this one the
green and the green. Then. I did that backwards and dark green and then
the light green. Then all of these I'm
going to make black and white by going to
the Font group. We'll make this one black and white and then
alignment and centers. I know that was a
lot of tedious work that we're gonna put under here. This is gonna be the date, list is gonna be the date. And then let's make that one
black and white as well. Black and white.
I want to format the date and the same format
as the date form over here, like a 11 format. To do that, I'm
going to then say our Home tab paint brush. And I'm gonna paint
brush it down to those 12 items in a similar fashion as
we had on the left. So now I've got the date that looks like it's
formatted correctly. I could probably
make this date field a little bit smaller, maybe. There we have it. And then I'm going to start off with
the beginning balance, beginning balance in
a similar fashion as we have seen with
our GL information. Okay, So then I'm gonna
make this whole thing blue, blue and bordered. So let's make this whole thing blew and bordered font group and make the borders on it and make it that
blue right there. We've been looking at
same kind of blue color. So that looks good. And then finally,
we're gonna say, how many units do we
have of this thing? They cost 400. And we have one I think
one of that item. I'm going to center it at
401 at the ending balance, units 400, we've
got one of them. Total cost is going to
equal 400 times one. That was a lot. Alright? But now we can just
copy this down. We can copy this format
down for the rest of them. We're going to have to
do the same thing now for the rest of these
inventory items, but we can copy and
paste them down. I'm gonna go back on over
and let's just copy it down. So what I'm gonna
do is I'm gonna take this whole thing now, can now keep it the way
it take this whole thing. I'm just going to Control C and I'm gonna paste
it on down here. Let's just do how many
units do we have? We've got to do this
for, let's do 123456. So I'm going to go
back on over and I'm just going to do to just
like we did with the GL. So I'm not gonna
go down anymore. I'm going to go to the right. So I'm gonna paste that again. This time. Let's take from
the skinny column. From the skinny. What's the skinny? I don't know what it is
because it's hidden, but there's the
skinny on over to the AN and copy that,
paste it there. So 1234, and then we'll
paste it again right here. The AAC. And so there we have the
six with the six items. And now we can just go in and adjust the amounts
and the names. The first one is an ELT and then the next
one's going to be an EPSP standing for an
episode standard Pro EPSP. So I'm gonna call this an EPSP. And that one, Let's see how much that one costs
and how many we have. What did that one cost? That one was 480 and we
have one of them for 81. We got 480 on the unit
costs starting 0.1. Okay, what's the
next one we got? The next one we have
on hand happens to be an EPR, epifaunal Riviera. Epr. That's going to be an IEP. That one then how much does
it cost an EPR for 40. And we have one of them, 440. We got 14414401. And then the next one is going
to be what's the next one? The next one is an EPS
standing for an EPA phone, semi hollow body EPA
SH EPS down here. Right there. Eps. And what's the cost
of that thing? How much does one
of those costs? This is, these are not
accurate prices, had no idea, but it's gonna be
320 and we've got, we've got two of them, 322. We're going through 22 of them. Then we're going to go to
the next one, almost there. The next one that we
have a bit cheaper, it's a Gibson, a GI USA. Usa. We're gonna send us that one then is going to be how much
does that one cost? It's going to cost 304. Notice I'm not taking
the sales price. We're gonna sell it
for that amount, but I'm just picking
up the costs. 304 and we got 40 of those ones. We've got three of
those, 3043 of them. And then finally, one more, We got one more thing which is I think it's just a ukulele. We also have a ukulele. It's the Diamond Head
ukulele. A duck. It's a duck. We got
the good old duck. And then it's going to be the cost of those
things. Cost 24. And we've got one
of them, 241241. So we're gonna say $24
and we got one of them. Then we got to do our
check figure up top. So now we've got our different
units and we gotta do, I need to do the
running balance, similar kind of concept
we saw in the past. I'd like this running
balance to go to the bottom. So when I get my check figure, it's pulling from
the bottom here. So I'm gonna say
this. I'm just gonna say this equals
the one above it. I'm just going to copy
that all the way down and run that balance
to the bottom. Run it to the bottom. And then we'll adjust it as
we make sales and whatnot. So I'm gonna say this
equals the one above it, and then run it to the bottom. Let's do that across the board. Here's the board and
we're going across it. We're going across the board
with the same technique of equals the one above and then run it down to the bottom. Now wait a second. This one's should
be to the units. And I've got the units. I've got the units and
the unit cost backwards. Dona. Let me see if I can fix that. The unit should be
should be 1400. And this one should be unit 14 AT this one should be 1440. This one should
be, I think, 2320. Then this one should be 3300, for this one should be 124. Alright, let's see
if that works. Well, check it out so
we'll double-check. And it obviously those
errors were made purposely. We can do our double-checking
figure, figure it out. Part of the process, people, part of the process. So we're doing this
across the board. This is like a checkerboard. And we're sweeping
the checkerboard across the board
with this technique. Up top, running it down. Okay, so now we've
got our check figure. Let's see if we did
it right over here. Gets, get something
right. Let's see. I'm going to delete
that and I'm gonna say, I'm gonna say this equals
the n number on all these. And it should come out to that 2896 that we saw here, 2896. So let's see if that's the case. This one plus the end here
plus the last one here. Plus the last one here. I feel like I'm
missing something. I am missing something. Hold on. I don't want to do I
needed to take this back. I needed to take this back
on over here, wherever here. So wrong cell, I'm in cell one. Let's try it again. Do it again, do it right this time. Do it right. You're
confusing people. We got four hundred and
four hundred and eighty. Plus. Then we're gonna
go to the right. We're going to pick up the 440 plus the 640 plus,
go to the right. And we're going to pick
up the 912 plus the 24 and enter the truth moment. There it is. Moment of the truthiness. If I was to say where do these come from there right there. We could see with the
tools we were looking at, they're coming from
the ending balances. And that adds up to that 2896, which I believe is correct. So now we would like to
kind of check that off. I would like to check
figure tying it out to what is on
the trial balance. So what I'm gonna do
is save this equals this number minus
the trial balance, which I'm going to go all the
way to the left to pick up. And I'm gonna say minus this number and it
should be zeros, subtracting those two
routes should be 0. That's our check figure. I went to apply my same kind of rules for the check figure. I'm gonna do the conditional
formatting here again. So I'm gonna remove
these things. I'm gonna go to the, to the homepage conditional
formatting and say if it's less, if it's greater than
one, make it red. So if it's two, turns red. And then I'm going to say
conditional formatting. If it's less than one, less than one, make it red. If it's negative two, turns red. Then finally,
between negative 11, then we want it to turn green. So there we have our
conditional formatting. And then I'm gonna
call this right here, the inventory subsidiary ledger. Let's make this whole thing black and white all
the way across. This whole thing needs
to be black and white. That whole thing
needs to be black and white. Then I'm
gonna say this. I'm just gonna say this
equals that one over here. Here. I have the same thing. And then this whole
thing needs to be black and white again. Let's just make it
black and white across black and white. And then this one also, I'm just gonna say this
equals the one before it. So if I misspell, less likely to misspell or
maybe I don't know, whatever. Black and white
There we have it. So now we've got the subledger
that's going to tie out. And that was tedious
and I made and I kinda, Let's spell check it. Did I spell everything right? Bow. I'm gonna I'm
gonna ignore that. Everything seems to be still. They say it's spelled right. Good enough for me that
this adds up to the 2896, which I think
everything's appropriate. Let's make it a little smaller. Not that small. What in the world? Let's make this a
little smaller. So I wanted I think this ties out everything
looks correct. It has out to this and
it ties out to this. Then as we enter
data going forward, we're going to have
to say the purchases will be in this
column and adjust the ending balances
and then the cost of goods sold will put here
in the ending balance. We'll see how that works
once we start entering data. But the point here is we've got the supporting subsidiary
ledger that being important if you're tracking the inventory on a
perpetual basis, if I go all the way
back to the left, then you could see that
ties out to the 2896 here. And it looks good.
7. Accounts Receivable & Subsidiary Ledger: Excel accounting practice
problem, accounts receivable, subsidiary ledger, get ready
because it's time to Excel. Here we are in our
Excel worksheet in prior presentations
we put together at the Excel worksheet
from a blank sheet. Now we're going to be using it to record transactions
started with our beginning
balance transactions two tabs on down below, example and practice example tab in essence Dean
and answer key. Let's take a look at it now, last time we put the
beginning balance on for the inventory, now we're focusing in on
the accounts receivable. Imagining that as we
set up our books, we have the beginning balances here that we want to
put into our system. We would like to do
this in Excel in a similar way as
we might imagine. It would be put together in a database program to think
what it would be doing, such as a QuickBooks
type of program. And look at those
accounts that have a bit more challenged to them. That's what we're
gonna be starting out with this time, the receivable, which is a little
bit more challenging because as we put
that on the books, we can't just put that number on the books because we also need to know who
owes us the money. We need a subsidiary ledger
broken out by customer. You can imagine
when you put this into a software situation, if you were trying to put these
as the beginning balance, it will have a similar
situation making it difficult for us to just
enter a journal entry. And that is why we're gonna do this one account at a time. And I'm just going to add focusing in on the
accounts receivable, provide the data necessary
for the subsidiary ledger, which is going to be
the breaking out that number by account
the other side, then it's going to go to the equity account at that point, this is gonna be
our breakout for our customers that we're
gonna be entering that owe us money notice we're
just going to be representing the customers that owe us money for our practice
problem here, if you were to enter
this information into a database program or into
your accounting system, you might try to add
more customers and get the contact information
and so on and so forth. We're not going to be entering
the contact information here because we're focused
in on the accounting. I'll want to basically
know the names at this point and we're just going to show how the
ending balances would be used. If I then add up these balances for the customer that's going
to tie out to that 20,500. And you can imagine if you
enter this into the system, something like a
QuickBooks system. You would also want to be providing things like
the contact information, that billing information and so on so that you can
contact his people, possibly send out a reminder
to pay you and so on. Now let's go back on
over to our worksheet and see how this would be
going into our worksheets. I'm going to go up
to the practice tab. And I'm going to start off,
we're gonna put these all in as of 11 here. And we're not going
to put anything to the income accounts because we're just looking
at those beginning balance sheet accounts. We know that just from a
journal entry standpoint, it's simple in that the accounts receivable
needs to go up. That's where our focus is. It's gonna go up by that 20,500. The other side is then going to go to the equity account in some way because everything else is going to wash out
in essence to equity. I'm going to indent
this one by going to the Home tab
Alignment and indent, I'm going to put the negative
of that 20 thousand. There is our debit and
credit posting this out. I could post this
to the accounts receivable right here in H5 equals that 20,500 equity
has something in it. Somebody to double-click on it, go to the end of
it and say plus, and then point to that 20,500. And that'll take us
back in balance here. So now we're back in balance. So easy to do within Excel, but you can see
why the software, if you allow someone
to do that in a QuickBooks without
giving a customer, then you're not going to have that subsidiary ledger that you would like to
see in the system. So some accounting
softwares will say No, you can't do that with just a journal entry until
you give me the data that I need in order for it to meet a break that out with
a subsidiary ledger. Let's construct the
subsidiary ledger now. I'm gonna go all the way. Well, let's post it first was posted to the jails and
then we're going to also post it to the GIL to the accounts receivable
second account. So we got the checking account than the accounts receivable. I'm in the so O5 I'm going
to put this in as 11, the beginning of the
period I'm in cell P5, I'm gonna say equals
scroll to the left. We're gonna pick up that 20,500
and accounts receivable. This breaks out
the data by date, puts us out of balance. We also need to see It's
still though by customer, other side's gonna be going to then to the equity account, which is way at the bottom. So I'm gonna go all
the way to the right. We've got the assets
and green liabilities and orange equity
finally over here. And this is the second
transaction, inequity 11. We are in Cell AB 18, I'm gonna say equals scroll to the left till I hit the wall
all the way to the left, hitting the wall up. And then we're going to pick up that equity account of the 20,500 and enter. So
there we have it. The cell needs to be a little
bit wider, it looks like. So I'm going to pull this cell
a little bit to the right. And same with this cell a
little bit to the right. We have enough
information to see them. And so there we have it. So now that pulls
down to the 23396, which should show on
the trial balance that should put us
back in balance here. So that puts us back in
balance with the green zeros. And the trial balance
has to at 23936. This color should be
a light blue color. So I'm going to try to mirror this over here and it's
not light blue because I made I made them the
bracketed numbers to be read. So possibly I want to
remove that formatting. So maybe let's try to do it
to all these numbers here. And I'm going to right-click on it and I'm going to format that cell wobbler here and
go into the number group. And maybe I don't
want it to be red. I'm just going to
make it black and bracketed and then okay, and so now we've got that
nice blue instead of the red. So that's a preference. So either way will work, but
I'm going to put it there. Okay, so there we have
that and so we're back in balance now we got to add
the subsidiary ledger, breaking this information out by the customers that owe us money because that's going
to be the next question. I'm going to put that all
the way to the right here. I'm gonna put it before
the inventory items. So it's gonna be between
the general ledger and then the inventory. So all these skinny
sales right here, I'm going to try to
make some space to put the accounts receivable
subsidiary ledger. What I'd like to do to
do that is try to copy the same formatting as I see
possibly in a GL ledger. Maybe I'll go from
the skinny cell here. This two right here. And then let go. And I'm gonna copy this because I'm going to
do a similar kind of look and feel to
it and copy that. And then I'm going to go into here and I'm going to try to paste it but inserted I'm not
gonna paste an overlap it. But instead right-click
and say I want to insert the copied cells. Insert the copied cells. So there we have it. Now it's not gonna be a general
ledger up top. I'm gonna it's gonna be like the accounts receivable
subsidiary ledger, which an accounting software
might be in a report, right? It would be something like
the accounts receivable aging report or the accounts receivable summary and so on. So now I'd like to hide
some cells so that I can see the data on the trial
balance right next to it. I'm going to put
my cursor on AAC. I'm going to scroll on to the left until we
get to the skinny J. Skinny J, right-click and then hide that information, hide it. So now we've got our
accounts receivable right next to the subsidiary
ledger, right next to here. And then I'm just going to name these items compared to the
name that we have here. So here's our four customers, Anderson, Jones, and Smith. So I'm gonna try to
just copy these. You'll have to type them,
but I'm gonna copy them. And so we're gonna say Anderson, I'm gonna double-click here
and put that inside Anderson. And then the next one was Jones. I'm gonna Sarah Jones. I'll put the next
one down below here. I don't want anything
in this cell. Then I'm gonna say the
next one is Smith. Smith is going to be. Then here I'm gonna
double-click and say Smith. And so there we have that. And so then I'm going to
delete the 0 on this one. And this one. Let's delete that and
I'll just put it right in the beginning balance area. What the totals are. This one I'm going
to leave blank for now is because we don't
have something there, but I'll leave it
there so that we can add a customer later. That'll be fine. And then we'll put the will put the amounts which
was 5075800050758. So I'm gonna put that
in here, 5 thousand. And I'm gonna save this
equals that 5 thousand, and then it'll pull
on down. And it's 75. I think it was for
Jones, wasn't it? Next one was Jones. 75. That's for Jones. And then 8 thousand
is the last one. The last one is 8 thousand. We'll put up here
and then we've got a blank space that
we can use later. Now I want to give a check
figure. A check figure. What I'll do now
is this one's not a general ledger
anymore. Deleting that. I want to cut this
one and put it over here so that I have
some room to put, to put the other data in place. It seems like it's
too long now what if I abbreviate it to AR? I'm just going to
abbreviate to AR. Hopefully that doesn't
mess anyone AR subledger. Okay, so now we can sum up the four customers which
should add up to that 20,500. So this is going to be
the one at the bottom, the 5 thousand plus
this one down here, which is not currently adding up properly, so
it's not going to work. So I'm going to fix that
plus that plus this one, plus this one doesn't add up to the 20,500 because
this one needs to, I need to say equals to 8
thousand to pull it down. This one needs to
equal to 7 thousand. And this one I'll just
say equals the zeros. And now the beginning
balances are pulling down. You might say You should've
put it down here on the 11, but okay. I'll keep it there. So we're
going to keep it there. And that's gonna be,
that's going to be it. So now I'd like to compare this balance to what's
on the trial balance. Give me a quick check figure. If I don't have the two items
right next to each other, I can say this will be equal
to this minus that 20,500, that should always be 0. There's my check figure. I can then do my
conditional format in here to make
it red and so on. I could just copy and paste. I could just do my paintbrush. I'll go here and go to
the Home tab paintbrush and then brush that
conditional formatting. Not there, that's
not where it goes. I'm gonna do the paintbrush. Paintbrush and I'll
put it right there. Then. It was like off by
two or negative two, it turns red, for example. And then I'll make
this one the black and white again, black and white. That's gonna, that's
kind of the idea that you could see what
would be positioning. And the same kind of idea
that you would have to do. That's what the
accounting software would be doing as well. And you want these two ledgers
to basically tie out from the GL to the subsidiary ledger. So now I'm going to unhide
these cells going from, from I selecting column I to right-click and unhide
those cells, so they're back. Those cells are back now. So that's where we
stand on this one. And then we're going
to continue on with the rest of the beginning
balances next time. Next time looking at the API which will be similar
accounts payable.
8. Accounts Payable & Subsidiary Ledger: Excel accounting practice
problem, accounts payable, subsidiary ledger, get ready because
we're about to Excel. Here we are in our
prior presentation and prior presentations, we built this worksheet
from a blank worksheet. Now we're going to be using
it to record transactions. We're recording the beginning
balanced transactions here we have two tabs on down
below example and practice. Example tab in essence
being an answer key. Let's take a look at it now. Last time when
prior presentations we put on the inventory, then the accounts receivable. Now we're looking
at that accounts payable imagining we had some prior accounting system or some accounts that we
needed to put on the books, the beginning balances as of the first day of
the current year, we're gonna be working
in January 1st. We are imagined or as of basically the end
of the prior year. These are those accounts were picking and choosing
those that are gonna be the more complicated
type of accounts and those that need
a subsidiary ledger, such as at this point,
the accounts payable. So we're gonna be working
on the accounts payable. We're entering these
balances one at a time. The other side then
go into some kind of equity account which will kind of wash out at the
end of the day. This being a similar process
that we would see in a lot of accounting software when you're going through
this process. And it allows us then to create the subsidiary
ledger as we build this accounts payable
representing people that we owe money
to for goods and services we have
received in the past. Therefore, it needing
a subsidiary ledger that's breaking out by vendor
who we owe the money to. I'm gonna go back on over. So let's go back on over here. I'm gonna go to the practice. This will look a lot
like the receivable, but now on the payable side, so the journal entry is
gonna be straightforward. It's going to be 11 on
the journal entry is same as at the beginning
of the period. Now here we're gonna
say it's a liability, so it's gonna go
up with a credit. So I might want to put that on the bottom because that's
traditional to-do. But the credit on the bottom, we're gonna say that
it's going up as we can see here by that 15 thousand. So I'm going to put the credit
of a negative 15 thousand. The debit is going to be
negative of that number, or a debit or positive number of the 15 thousand
it's going to go to as all other kind of
beginning balances have thus far to
the equity account. So that we can keep this
imbalance as we go. We're going to then be indenting this alignment indent no
team that we're not having anything go into any
income statement accounts here because we're just setting up the beginning balances, which should be just basically the balance sheet accounts. So let's go ahead and post this. I'll post the equity. So here's the equity account
double-clicking on it, go into the end of it and saying plus and then pointing
to that 15 thousand, it's going to
decrease the equity, put us out of balance here, then we're going to
the accounts payable. Accounts payable is right here. It's gonna be a
liability account, I'm gonna say equals and
pointed at 15 thousand. It's gonna go up into the credit direction
to that 15 thousand. We can also point fat or post
this to the general ledger, same kind of activity here. And notice it's kind
of redundant for us to post it to the general
ledger you might be saying, but remember, we're doing
this so that we can see the impacts directly here, the debits and credits
and how they work in terms of the double-entry
accounting system and then posting again to the GL
because normally you posted the GL and then create the trial balance from
the general ledger. So let's do that now.
We're gonna say this is going to go onto the equity, which is gonna be the last GL We set up as the
blue one assets, liabilities, equity down here before we get to the subledger. Now we're down here
on sale in 1911. We're in cell B19. C19 equals left on over
till we hit the wall. And going up to that 15
thousand on the debit side, that's gonna give us, if we
see are running balance. We started at 0, went up in the credit direction
by the 2896, and then we increase it
again by the 20,005 to the 23396 decrease in
about 15 to the 8,396. That is the amount
that should be on the trial balance as it is here. I believe that's the same number out of balance up
top till we record the other side and the
GL or general ledger, This is gonna be the
first liability account, the accounts payable,
so its assets and liabilities. There we have it. We're going to be in cell W5, 11, we're now in cell X5. We're gonna say equals
left to the wall. And we're going to pick up that accounts payable of
the 15 thousand, increasing it in the
credit direction. The cell is too skinny. The cell is too skinny. I'm gonna widen the cells. If the cells are too skinny, you going to have to
widen them up there. I might widen them
all later so there'll be a little bit wider
on your worksheet. There we have that. And that's going to
then put us back in balance on the general ledger. We also need to know this by a subsidiary ledger if
you're dealing with accounting software like
QuickBooks or something, oftentimes it won't
let you post to the accounts payable account
unless you assign a vendor. And so it does that
to say, Hey, look, I want to be able to
generate reports for you that are going to be by vendor to give you that
subsidiary ledger. Otherwise, this
number is meaningless because you don't know how
to pay it in the future. So we're going to set up a subledger and a
similar way as we did. With the accounts receivable, I'd like to copy the style of these ledgers that were maybe maybe I can copy the style of these subledger and then
I'll change the color. I'm gonna take, I'm
gonna grab this stuff, I'm gonna grab from the skinny
all the way over to AK. And I'm just going to copy it. We're gonna copy
that right-click and copy, right-click and copy. It doesn't want to
right-click sometimes my right-click is stubborn. When I'm recording, then I'm going to
right-click and paste. I think my computer is
slow, but that's okay. That's okay. I'm
kinda slow. Two. We're going to then
paste it right here. We're going to insert,
insert the copied cells. So there we have it. Now we'll just, we'll just
adjust this for the payable. So this is an AR,
this is gonna be API. Then we'll delete these
beginning balances. Get rid of those. And it needs to be not green, but it needs to be, it
needs to be orange. I'm going to delete
the names to. These are not our vendors, those are our customers. Then I'm going to go on
over here and say We're just going to grab
some yellowness, grab some yellow and put
that on our paintbrush. Let's grab that yellow Home tab. Put that on the paintbrush. When I brush the
paint brush on stuff, yellow gets on it. If I brush my
paintbrush right there, the yellow stuff
that was once on my paintbrush is
now right there. I'm gonna do it again. I'm gonna paint brush it and do this one and paint brushes. This one. Paintbrush. I should
have been a painter. I have skills. I have skills that are mad. I'm totally calm, but
I got mad skills. It's not easy sometimes. Anyways. Then up top we're gonna say that we want
then the vendor, I think it's gonna
be EPA phone phone. And I'm just going
to say that we have that one vendor for epifaunal, which is gonna be
negative 15 thousand. And so that's the one vendor. It's too skinny.
It's too skinny. You got to make it. Okay, So there we
go. There we go. Now we got that one vendor and these less than
these other ones. I'm just going to have that one vendor at this point in time. I'm just going to leave these
on the books right now. So when I have other
stuff happened in India, accounts payable, I can go
over here and adjust it. Then here I would like to
say this is my check number. So I'm gonna say this is
equal to, then this 15. Hold on a sec. This is too
skinny. This is too skinny. This is too skinny. Now I'm gonna say this is equal to that 15 thousand minus the amount
that's on the balance sheet. And that'll give me my
check figure. Check. It's green. Check. Is that
check figure green? It is. Check, roger that it's green. Then that's going to match out. So if I tie, this adds
up to my four vendors. So that looks good. So now
we've got the subledger, the accounts payable
on the books. Let's add now we're
starting to get some substance over here
in our trial balance. So next time we'll start to
construct this trial balance into our financial
statements balance sheet and income statement, and see how that can be
constructed in essence, as we go as we start and
continue to add accounts.
9. Financial Statements: Excel, accounting
practice problem, financial statement creation. Get ready because
it's time to which still we are in our
Excel worksheet and prior presentations
will be constructed the Excel worksheet from a
blank sheet now using it in order to enter transactions
two tabs down below example and practice the example tab in essence being an answer key. Let's take a look at it now. In prior presentations,
we've been entering the beginning
transactions imagining that this is our beginning balances that we want to enter into the system, start entering new transactions. From that point forward, we have entered the inventory, the accounts receivable, and the accounts payable
into our trial balance. Now we want to start
to think about constructing the financial
statements as we go. Obviously, the only
financial statement that's four is in essence going to be the balance sheet because we don't have any
income statement activity yet. We put that all the way to
the right on our worksheet. We're gonna be constructed
it way over here, which means we're
going to practice hiding sales to
get way over here, to take a look at the construction
of our balance sheet. Let's go back on over to the p, to the practice tab. And then I'm gonna
hide themselves. I want to hide some sales. So I got my trial
balance right next to where we are going
to be constructing the financial statements. I'm going to put my cursor on the skinny column,
the skinny J. Skinny j, I knew a
skinny j one time. He's still pretty skinny. But he's not asking, is
it used to be any case. We're gonna go over here to BZ. We're going to right-click and
we're going to hide those. So we're gonna hide that, hide. And so now we've got
this side-by-side. Let's make this another skinny, skinny CA, skinny California. Skinny CA, California is
huge. It's not skinny. But whatever. Here we go, we're gonna say then it's gonna be
the balance sheet. Balance sheet. And then let's make, let's make some of it
just black up top. So we have some, some items just to note that
that's the heading. So I'm going to make
that our standard black and white for the head in black and white.
So there we go. And then we'll typically have the assets, liabilities,
and equity, which we can stack on
top of each other, or we can do a side-by-side. And so a lot of software
will stack it like the assets on top and then liabilities and
equity on the bottom. Because that's a nice
vertical analysis when you're in a situation where your screen is kind of
scrunched in space this way. But oftentimes people liked
to see it where sprawled out where the assets equal
to liabilities and equity. So you can see two sides of
kind of like a big T-account. So I'm actually going
to construct it that way with the
assets on the left, liabilities and
equity on the right. So let's start what we're
going to have the assets here. And we might want to
make that as a color. I'm gonna make it like
this same formatting by paintbrush in that green. Put that grade on my paintbrush
and put it right there. And then we're gonna
say we usually have a subcategory of current assets. Those are going to be
more liquid assets. In accounting software, it'll often name the account
types as current assets. So in other words,
if I was to put these accounts into
software, like QuickBooks, software would have
a special name for the checking account and the cash accounts because
it wants to have a ledger, they would also be a current
asset type of accounts. Accounts receivable would
have a special account, but also a current asset, I believe inventory then would simply be under the designation of an account type of a current asset asset type
of account generally. But they're all gonna
be current assets. And note, I don't need a special sub categorization for the checking account
in our worksheet, because it's just
a current asset which really should go
under basically cash. We should even rename it under financial accounting
to just call it, this should be grouped up
in the category of cash. But we're just going to call
that the checking account. We don't need another category
for accounts receivable. It's just a current asset. If you see subcategories of these accounts other than
current assets in software, it's probably because
the saltwater is using these accounts
in a different way. Needs needs to have special uses for those accounts and
therefore creating another category
that's not usually on a financial statement as a
sub-category type of account. Okay, in any case, we're
gonna say this is gonna be cash equal to the cash. Do I want to build
this whole thing out? Let's just add all the
accounts here then this one's going to be the
accounts receivable. I was going to
build it as we go, but let's just build
the whole thing out accounts receivable. And then this one is also a current asset
on the inventory. So then I'm gonna, I'm gonna
make this cell a little bit larger between the
CB and the SCC, make it a bit larger. And we're gonna call that
total Current Assets. Let's do our
indentation stuff here. So I'm gonna, I'm gonna say
that this is current assets, meaning it's a subcategory
indicated by the colon. I'm also going to show us a
subcategory by indenting it, go into the alignment
group and indenting it. And then I'm going to
show us a subcategory by putting my information
into an inner column, which I will sum up
in the outer column. But a bit overkill on the fact that
it's going to be current and I'm going
to pick up that 0, which will pick it up and
change once we complete it. Gonna say this is going
to be equal to the 20,500 and this one's going
to be equal to the 2896. And then I'll underline it here. Home tab thought
group underlying. Let's do another
indentation on the total down here, alignment
indentation. And then we'll sum
up in the CD column. Cds are almost obsolete
these days, aren't they? Because now we've got like
other steps streaming stuff. I don't even use my
CD player anymore. Whatever, either
cassette player once. Any case, let's do the next one. Then we're gonna say that's
the total current assets. And then we can just
have the total assets because we don't have
any long-term Oh, yeah. We got property
plant and equipment. So let's say property plants, let's just say plant and
equipment. And colon. Now the property
plant and equipment might be called
depreciable asset. It might just be
called fixed assets. This is something that would
have a separate category. As you can see, a normal
financial statement reporting and software will often call it
something different. So if I was to add
these accounts into the accounting software, they would probably need to be categorized with a special
asset category type, like fixed assets or PPE and l are depreciable assets
or something like that. This is gonna be then
equal to the furniture and equipment and then the
accumulated depreciation. Now note that if you don't have account numbers and software, funny things could happen. That these two might be might be under the
category of property, plant and equipment,
or fixed assets. And then be an
alphabetical order putting the accumulation, accumulated depreciation for
furniture and equipment. Second, that's not, that's ugly. That shouldn't
happen that because it should be the
other way around. It shouldn't go that way,
it shouldn't go that way. That's not how things should be. But that's kind of a limitation. If you don't have
the account numbers, you can fix that with basically account numbers and software. Obviously, when we
construct our statements, we can fix that as
we construct it. Also realized that
if you're mirroring, it has been constructed from an accounting
standpoint and you're doing year-end adjustments
or something like that, and you're trying
to trying to format your trial balance to
what is in the system. You probably want to list your information not on
the financial statements, but when you enter
their information into a trial balance in
the same funny order, you're going to look at
it, say that's ugly. I don't want to enter it
like backwards or whatever, if this was on top or something. But you want to start with
that as your starting point. Because when you try to
adjust their books again, try to tell them to
make adjustments. That's the order of they're
going to see things. In other words, if
you're going to adjust your trial balance, I probably should
have put this one on top because that's how it's gonna show up in the
QuickBooks software. That's generally how
you want to do it. You don't want to
adjust their ledgers in your worksheet until you actually make the adjusting
entries to it because you'd like to give
it back to them in the same order
that they have. Okay, So in any case,
we're gonna then, let's make this one a little
bit larger here again, make that larger again. And then we'll bring this
into the inner column. This is gonna be
equal to this one, the furniture and
equipment and acute, acute deem ACC debri. And then this is just gonna
be the total property, plant, and equipment. Tab. Outer column equals the
sum of those two numbers, underlining this
number by going to the font group and underline
doing some indentation, selecting these items by
going to the Home tab Alignment in debt
those poor or five or. And then we'll indent
this one again alignment and indent once again. And then we're gonna have
down here in the total, this will be the total assets, which we're just going to
sum up the outer column. Now just summing up
the outer column, because now we've got
our subcategories of the current and the property
plants and equipment. I'll just say equals the SUM. The most popular function
ever for good reason to, it should be the most popular. Not just a fluke. We're gonna say,
we'll have those two. And then let's make
this one a skinny. Let's make that one
a skinny column. Then over here we're gonna have the liabilities and the
equity on the other side. So we're gonna say
this is gonna be lie, lie a bill, bill at T's. And maybe I don't need the colon because I'm going to yellow phi. And then let's make
that a yellow. Yellow. Put that yellow on my
paintbrush Home tab. Dip in that paintbrush into the yellow paint bucket so
I can paint it right there. Painted yellow. And then we've got the current, current liabilities colon, which is gonna be
equal to the accounts payable. All these are current. Well, the loan payable
might we'll say that's long-term loan payable. So this is going to be current. Let's make this cell larger. It's not too skinny already, but we're gonna make it wide
and this is a wide, so fat. So here we go. This is equal. I don't want
to call it a fat cell, but It's certainly not skinny. That's gonna be the 15 thousand. And then this is going
to be equal to the VCF, which nothing is in there yet. So this is gonna be,
we always want to say negative because
I want to flip the sign from this
negative credit to actual. We don't have debits and
credits over here because it's assets equal liabilities plus equity, the accounting equation. And so that's gonna give us
total current liabilities. I think this is spelled wrong. I fixed it, don't worry, I fixed current liabilities. What about the 100 other
things you've spelled wrong? Whatever my spelling,
it is what it is. So we're gonna go
to the Home tab Alignment, indent this one. And then we'll indent
this one again. Alignment indent again. Let's put an underline
here by going into the font group
and underline. And then we'll sum this
in the outer column, equals the sum, otherwise
known as the SUM. Summing it up. There we go. Now let's pick up the
equity side of things. Now notice this is a
total liabilities. Well, let's put the
long-term liabilities down here as well. So let's say that will have
long term liabilities. Colon, which just if
we have right now, is this one in the long-term,
which nothing's there, but we're thinking that loan payable is going to
happen at some time. I'm gonna say negative of that 0 to pick it up when
it does happen. When it does happen, we're going to be picking that thing up. Now when you only have one
thing in the category, I might just call this total, total liabilities and just
put it in the outer column. But I want to make
a subcategory in the same fashion
because we might have more liabilities in there. So I'm gonna call this then
total long term liabilities. And then we'll say
that equals that 0. Put an underlying there
because emphasize it. A line underneath, also known as the underlying Home tab
alignment indentation. Once again alignment in den. That's going to give us the
total liability liabilities, IES, summing it up once
again, the trustee, some function trustee
sum once again, come into our rescue rescue us. Some function fought
group underline. And then we got the
total Hold on a second. Then we got the equity
equity section. Equity. We're going to have just
a sole proprietorship. So we only got that
one basically account. It was a sole proprietor,
we're gonna call it equity. You might call it capital, but also owner's equity,
you might call it. And if it was, if it
was a corporation, you might have retained
earnings and then common stock, breaking those two things
out by total equity, you could think about as
kind of like the same. Remember that equity
represents what the owner has in the
company, or in other words, what the asset's allocated to the owners in essence
is as opposed to, as opposed to
third-party liabilities. And then you have to think about how you're going
to break that out. Most of the owners, which if there's only one, which would be a
capital account. But if there's two or more
partnership, for example, you've got to break it out
between the partners to say who has claim, and so on. Then if it's the corporation, then you've got shares
that are basically all the same kind of
ownership distribution. So you could just call
it retained earnings, one chunk, which the shares represent a uniform chunk of it. And then the common stock, which is basically
the investment versus the earnings that have been
accumulated and retained. Alright, so let's make
this paintbrush, that one, pulling out the paintbrush and
put it in the blue bucket. Home tab paintbrush
puts some blue on it. Put some blue. That's a
wonderful looking blue. And we're just
going to paint that right there. That's nice. Okay, so then we're gonna
say this is gonna be the equity account.
Equity account. I'm just gonna put this
right in the right and the total column
because I don't need any subcategories here because we only got one equity accounts. So I'll just put
that in the total. And we might just
call it total equity. I'll just keep it with equity. Then we're gonna say
the total is gonna be the total liabilities
and equity. Summing this up,
we're going to sum this up then which is
gonna be equals the SUM, SUM function of the 15
thousand and the 8396, which should equal this
number when we're done. This is a little suspense here. Before I hit Enter,
little suspense, hold on, here we go. And it did, it did it
did just what we saw, just what we planned
it should do. The very thing we plan to happen has indeed actually happened. What a coincidence. I'm going to underline here, font group and underline. Then let's put an underline, a double underline
here by going to the font group and double
underline, that one. That looks good. Is there
any more formatting stuff I can put an underline here. Let's put the double,
double underline here. And then the one underlying
there. That looks good. And then that looks
pretty good, doesn't it? Is there anything I'm missing? Do you guys see
something I'm missing. I probably misspelled
something, right. Let me check my spelling. See if it says I misspelled
something. I'm gonna say. Ignore. Ignore that. I don't
think that's a problem. Spellcheck says it's okay. Alright, that's
good enough for me. Then we're gonna make this blue. Let's make this blue for fonts
sake, for formatting sake. Font group will put
the borders around it, all the borders, all border. Then the blue, which is
that blue right there, which I could find right here. I go into that blue just
like the accounting, it's fun or the Excel
is fun guy used to say. Then I can move this one over. I can put that on my paintbrush. Paintbrush. See that
one right here. Let's paint brush this one over. Paintbrush that black
two right there. And let's make this our blue and bordered blue and border. Font group border
blue, border blue. And then do that here.
Border blue, border blue. There, we've got that. And then let's put, let's put this balance sheet
in the middle. So I can put it in the middle by selecting this whole thing. And we could go to the Home
tab Alignment and do that, which is what most
people would do. But then you've got
that one large cell which messes things
up sometimes. I don't recommend it personally. I'm going to undo
that the better way, mucho Mac or is gonna go down to the format cells here when you've got that whole
thing selected alignment, as we've seen in the past, the horizontal alignment
center across the selection. And okay, there it is. So we get a similar result.
So there we have it. And we can double-check
that to this trial balance. If you've formatted this
trial balance this way, which is the best way
to do it personally. If I do say so myself. And that means that you can add this whole thing up and see
that you're in balance. You can add these up
and just say that confirms what's in my acid area. And then we've got
the liabilities, which I can confirm
here, which is quite easy because there's
only one account in it. And you can see that
the liabilities and equity, if I summed them up, 23396 adds up to the 2396. I don't need to do any
adding and subtracting, subtract one column from the other column and
blah, blah, blah. It's really easy. If you set it up this debits
positive credits negative on the trial balance
to then convert that to the financial
statements. Being able to check
every subtotal along the way very much more easily than you could if you
have them broken out with this basically two columns or in this worksheet would
have six columns here. And then you've got to add together the debits
and credits and so on.
10. Add New Accounts & Opening Balances: Excel accounting
practice problem, add new accounts and
opening balances. Get ready because
we're about to Excel. Here we are in our Excel worksheet and prior
presentations, we set up this worksheet
from a blank sheet and are now entering
transactions Intuit. If you have access
to this worksheet, There's two tabs down below,
example and practice. The example tab in essence
Dean and answer key. Let's take a look at it now. In prior presentations,
we've been putting in place these beginning balances
that have the sub ledgers, making them a bit more
complex like inventory, accounts receivable,
and accounts payable. Now we are gonna be
entering transactions that should be easier to enter given the fact that
we don't need as much in terms of sub
ledgers for them. The checking account, the
furniture and equipment, the accumulated depreciation, Visa account, and loan payable. So we're gonna go back
to the practice tab and add these items in. If this is gonna be our example that we
have been following of the beginning balances
that we've been trying to populate
into our system. Imagining these
beginning balances from the prior accounting
system that we started, or possibly just some beginning balances
that we had set up before we started putting them into our accounting system. We're putting them into
our accounting system as it's the first period of the year because we'd like
to have a full years of transactions into the system. Typically, these are as of then 1231 of the prior year we are imagining and we're putting
them in our system. And we'll be going forward
starting January first, 2022 is our practice problem. So now we're gonna be doing
the checking accounts. So let's add the checking
account balance, which should be fairly
straightforward. Note that the checking account does have an issue with it. That would be that
if you're doing bank reconciliations
and the prior system, we might have some difference between the bank
statement and what's on our books due to the fact that there's
reconciliation items, outstanding checks and deposits. We'll talk more
about that later, possibly in our
practice problem. But for now we're gonna put
on the balance that was on our prior bookkeeping system. And so we're gonna say
let's do this as 11, putting our cursor and B 11 equals we'll pick up
that checking account, taking up the good
old checking account. And then the other side, like we have been doing before, everything else is
going to be going to the equity account
and it will all Walsh out inequity as we
enter one account at a time. Then we had in the
checking account that twenty five thousand
twenty-five thousand. Note, however, that because we're in this part of
the practice problem, we could make the
rest of these with one large journal entry possibly because we don't have that same problem
with the sub ledgers, but we're mirroring the
data input process that she would see possibly in
an accounting system, which might allow you to enter
these transactions using kind of a beginning
balance type of system. This is basically
what they would do so that people can enter
the beginning balances, even if they don't fully
know the debits and credits the system forcing out the debits and
credits to work out, washing out in the
equity side of things. So that's gonna be the
concept we want to keep in our mind and visualize as you transfer from here to possibly a database programs, something like a QuickBooks or other kind of
accounting software. I'm gonna say negative
of this number. There's our debit and credits. Let's do an indentation here. Indentation, and that's in the Home
tab Alignment indenting. We're going to record this out. So let's record it first to our trial balance so we can
see what is happening in H4. Within h4, this is gonna be
equal to the 25 thousand. And then I'm in the
equity down here, we're going to start
to use the keyboard. There's something in it. So I'm gonna say
F2, which allows us to kind of drill into
what's in there so far, of course these items
are all in thus far, I'm gonna say plus
and then F2 again, allowing me then to
maneuver outside of that cell and picking
up the 25 thousandths. So we did that in a very geeky type of way
without even using the mouse, which is what we're aiming for. That's what we're
going for here. We've got the checking
account, let's post it to the general ledger than in the GEO and cell K5. Within K5, this is on 11 tab. This is going to
be equal to an L5, about twenty five thousand. Twenty five thousand. That puts us out of balance up top until we've recorded
the other side, which is an equity that's
the last account we have thus far because we don't have any income
statement accounts, It's an order, assets, liabilities, and
then the equity. So that's going to
be down here in AA, Aa 211, and then an AB 20. We're gonna do this
by saying equals, I'm gonna hold down the
left arrow till we hit the wall bone and then scroll up top and pick up that equity of the twenty-five thousand. Twenty-five thousand. There we have it. Now we're at the 33 three
ninety eight, ninety six. The equity which
should tie out and match what is on
the trial balance. We're back in balance given
the green items up top. So it looks good
on the GL as well. So now we need some more blue, I need some more blue
lines down below. So I can do this by basically selecting this column up top, going up to our
Home tab clipboard with the little paintbrush, I want to put some blue
paint on my paintbrush. And then I'm going to think
we're going to go down to, let's go down to like 27 and
see if that'll work for us. Painting that painting
that just like I paint a fence like the karate kid did with the I don't know
what I'm doing Here. We go back to it. So now we're gonna
say with the 11. Then we've got
what's the next one? The next one we did
accounts receivable. We did inventory
because they had those sub ledgers and then
accumulated depreciation. I'm gonna do with the
furniture and equipment first because it just
makes more sense in my mind to do that 1 first
because these are out of order due to the fact that
they're the same account type. But in the accounting software we use, which was QuickBooks, it put it in alphabetical
order there, so I won't get into that now, but I'm gonna do the furniture
and equipment first. So let's do that. Go back on over furniture
and equipment right there. So that's going to be going up. We've got furniture and
equipment that we're putting in play as we start
our business here. And then the other sides
go into once again equity. The equity is never seen so much action to
it at 1 in time. Now we're gonna say
this is going to be the seventy five thousand, seventy five thousand
in the equity. There's our debit,
There's our credit. Let's put an indentation and we're gonna
alignment indentation. And then we got the
furniture and equipment. Let's post it out to the
trustee TB trial balance first, furniture and equipment
will be equal to pick it up that 75 thousand. So now we've got that
beginning balance and then in the equity recording out the Equity doing it
the geeky way, Bye. Selecting F4 on the
keyboard and then plus and then F4 so I
can maneuver outside. That will impress
all of your friends. And so now we're
gonna say this is in the furniture and equipment. That's like the fourth
asset account on the GL, it's posted this is called
posting to the general ledger. Furniture and equipment were in S11 one and now I'm in t5, that's the cell reference equals and then the
left to the wall. Boom, I'm going to
destroy that wall. One of these times. I'm going
to get a head of steam. That's gonna be the 75 thousand, bringing it up to
the 75 thousand. And then we've got
the 75 thousand here and we're out
of balance up top. The other side go and
then of course to equity the last account
that we have thus far because they don't have
any income statements. So it's the same on
the GL general ledger, posting it over here to
the GL general ledger. There's our blue equity account with all the action in it. It's still weird for equity because that doesn't
normally happen. This action and
equity, then we're in, so AB 21, we're gonna say equals holding
down that left arrow. This is the time. This
is I know there's a weak spot in that wall
somewhere. There's a weak spot. I'm gonna go right through
it. One of these days. 75 thousand Enter. There, we have it. The cells a little too small. I should I should widen all of these cells so that because they should be
able to accommodate. What I'm gonna do is I'm gonna try to format as we go here. I'm gonna select these two. And then I'm gonna
hold down Control. And then these two cells
let go of Control, hold down control
these two cells, these IT control, hold down control these two
cells, liquid control. Then I think that's it. And then I want to make it wide enough to accommodate
that large of a number of these cells in case I have over a 100
thousand, There we go. So you can widen those cells
as you go if you so choose. But that's how you could
do it at one time, highlighting non-adjacent cells, those that are not
next to each other. Let's do another one. This
is going to be now we're on the accumulated
depreciation 7,500. This being a credit balance because it's a contra
asset account. So I'm gonna say, All right,
that's not a problem. There's accumulated
depreciation. 11. This time I'll put the equity on top because I'm going to credit the accumulated depreciation
if being a contra asset, so equity and then
the ACC de Pree, as I call it, a lot of times
because it sounds cooler. And it doesn't take as
long to say we've got 75. Then negative of the
75, there it is. Less, post it out. We then have equity up top first this time equity we're
right here, f2 plus F2, scrolling down for picking up that 7,005 out of balance now, by the 7,005 till we
post the ACC de Pree. So we're going to then
go up top to where that account is
here on it's an H8. H8 equals scrolling back on down to that 75
back in balance. Looking good. We're looking real good. Now let's post it to
the equity section 7,005, the general ledger, the good old GL GO
that blue account, the only blue account we
have thus far will make more later with all the action. That's where all the action is. That's where all the action is. Ab 22 equals left to the wall. I've hit that point right there, 22 and the wall. So many times actually
know everyone. Just hit that same spot. I feel like I can go through
it one of these days. But any case, there's the one hundred and eight
hundred three hundred ninety six plus the 7,005 pixels to the one hundred and eight
hundred ninety six. So obviously it's a
decrease because it's a debit to a credit account. And it decreases because we're doing the opposite thing to it. And that amount is also going to be here
on the trustee TB, and we're out of balance
by then the 7,500. So we will record
the other side, which is to accumulated
depreciation, which is the last green
account on the trustee TB, which will be the same
in the general ledger, the GL as it's otherwise known. So we got the accumulated
depreciation 11. And then in T7 equals left to
the wall and a 7,005 and d. Eighteen, was it
something like that? You could you could copy
you could just memorize D19 if you don't like doing
that left to the wall thing. But I would recommend putting those cell references in
there because then again, you can use these
little functions up top to see where
it's coming from. If there's a problem, much
easier to solve the problem, much easier to
solve the problem. Those, by the way, are
located in this area in the Formulas tab and the
format formula auditing. They're useful tool. Useful tool. I don't need to use
them that much anymore because I don't
make any mistakes. Just kidding. I make
I make mistakes. But so yeah, very
useful. Any case. Now, next one, what's next then? We're on the accounts payable. We did that one because it has a subledger and so let's do the visa and then the loan
payable were on the visa. Now, same thing, it's
a liability accounts. It's gonna go up with a credit. So equity is gonna be on top. Equity on top. We're going to start
with the equity account and then we're gonna be picking up the accounts,
the Visa account. What was it? I
think it was 1000. Was it do you remember? Yeah, it's 1000 thing here. We're going to say
100000. Credit. The 100000 will do
the indentation and the alignment group
posting it out equity first to the trustee TB up top. So we're going to put it
into equity section F2. Look at how that action plus F2. Scrolling back down to
the last transaction, we could add another line and we will shortly because notice
how far downward going. But as we add more accounts will have the trial balance
will be longer too, because we don't have
an income statement. So we will end up in a
situation most likely will. I'll show you how to
add accounts that you not way down below where you're posting to at some
point in the future as well. So that you've got
that to look forward to, which is nice. You've got that going for you. We got that going for a visa up top is
going to be up top. It's gonna be in age ten equals. Then we'll pick up the
10000 again in HTN, and then we're back
in balance here. So now let's post it to the to the trustee trial balance
equity first because equity is on top
and that's gonna be the last account in the
general ledger as we posted, the one with all the
action these days, all the actions in equity, all the actions in equity. Ab 23 equals left to the wall, left to the wall. The 10000 Enter. Now we have the one
hundred eight hundred and ninety six debit decrease, the 998997. It's decreasing because it's
a credit balance account. We did the opposite thing to it, that 99896 also the amount
on the trustee trial balance and we're out of balance on the GL until we record
the other side, the other side go into
the Visa account. That's our second yellow account or payable same in
the general ledger, the assets and green
liabilities and orange, There's the second account, otherwise known as the
thesis credit card account. So we're in w, let's say x 17 with an x 17 equals
left to the wall. And then we're going to
pick up that last one, which is the 1000001000 there that should be on the trustee trial
balance as it is. We should be back in
balance up top again. One more round. Do we have time for
one more round? I didn't hear no bell. Here. I only stopped when
I hear loan payable. Loan payable. Let's do that one. Then we're gonna say
that this is going to be 11 loan payable. Once again, it's a
liability accounts, so the equity on top and then the other side going
to the loan payable. Loan payable will indent that
with alignment indentation. And what was the amount
again, do you remember? It was like Twenty two thousand and
twenty two thousand. Okay. 22 thousand
debit and the credit. Let's post it out to
the trustee treat TB first equity on top. Last account on the trustee
TB were in cell H2, I'm gonna say F2 plus F2. Look at that action in
equity, equity action. So there's the 22
thousand and Enter. So now we're out of balance
by the 22 thousand. And then the second side is going to go to
the loan payable. That's our last, That's our last liability,
the last orange. We'll pick that one up and
complete the cycle here on age 11 equals we'll pick
up that, that 22 thousand. So we've got our balances there. That looks good,
that looks good. Let's post that to the GL. Now. We've got the equity on top
starting with the equity, that's the last account in the last account in
the trial balance. So therefore same as in the GL looking for
that blue account. That's the one, you know, the one with the
action these days. That's the one with the
action, the equity. Then we're in the AB 24
equals left to the wall. Boom. And then we're gonna go
down to that last one, the equity 22 thousand, putting that in place, There's our 7,896
went from 99896 down because we did the
opposite thing to a debit and a credit balance account
at a 7789677896, also in the trustee TB GL out
of balance by that amount, which will fix now by
posting the other side, which is in the loan payable. The loan payable. That's the last it's gonna be the last liability account
on the trial balance. It will be in the
same order on the GL. And it just happens
to be that that one isn't so AAA five, so it's in column a, A5, I'm gonna say 11.
Possibly it starts. And so U5 going to be
putting equals an ABO F5. Because if you have a large loan payable that might
drive you to drink. So you have to have so AAA AAA could help with
the support or sorry, we got the 22
thousand and enter. Then we've got the 0 going
up by twenty two thousand, two hundred twenty two thousand. That amount should also be on the trial balance.
So there it is. On the trial balance. We're back in balance on
the general ledger as well. So now we've got our beginning
balances in place here, and we've got the
supporting subledger is for those that need them. We could ticket tie this off. We could say here's my here's my sheet that we
pulled this from. I got the checking account.
Let's see if I could do it like a side-by-side
thing here. Can I do if I do this, but make this one smaller? Something like that, and then
I scroll maybe this way. So we do this. Let
me go that way. If I scroll this way and then I put this one on the other side, how about that? That works? I feel like I think it works. So we've got the
checking account, 25 thousand accounts receivable,
20,500, inventory 2896. We've got this 7,005
out of order a bit because we liked the
ordering better over here. So I put this on the
75 up top 75 thousand. Notice. Again, if you're tracking this directly
from a client and you're trying to mirror their
accounts that you're gonna be working with their
bookkeeping system later, you might want to
put the stuff in a funny order and your books, but if you're making
your own books from scratch and this is going to be your new accounting system. You'd like to then of
course, setup your accounts in the best way to
do the data input. And normally that
would be of course, the furniture and equipment
on top there generally. And then the 7,500 for the
accumulated depreciation, the 15 thousand for the accounts payable,
the one hundred, ten hundred for the visa, the Twenty-two thousand
for the loan payable, and then everything
else washed out as we had predicted
with all that action, all that action,
you'd think with all that action and equity, how could it possibly
tie out to that 77896? But it does, and it has to do
to the fact that basically, if we put the every
other balance ties out and the other side
is going to equity. Equity has to then work. And that's how a lot of
accounting softwares will work when you enter those
beginning balances in place. So it's useful
concept to consider.
11. Adjust Beginning Balances in Worksheet for Current Period Data Input: Excel accounting
practice problem, adjust beginning balances and worksheet for current
period at data input. Get ready because
we're about to Excel. Here we are in our
Excel worksheet and prior presentations, we set up this Excel
worksheet from a blank sheet now
entering data into it, if you have access
to this worksheet to tabs on down below example tab and that practice tab example tab in essence DNA
and answer key, the practice tabs
starting out at the point which we left off at last time, the last time we entered
all of our information into the system for the
beginning balances both into a worksheet type
of format where we have the beginning entries and Indian trial balance and
into the general ledger. Now we'd like to set
up the system and clean up our
worksheet as if we're starting at the
first in time and moving forward like in January
in our practice problem, when we're actually
gonna do data input for the current period after having entered these
beginning balances. To do that, I would like to
take these ending balances, move them to the starting
point in our worksheet. We have the beginning
balances in our worksheet and in the general
ledger types of accounts that we would like to refresh these items that the beginning
balances will show up, up top here in the
beginning balances here. And instead of as we post
them to the accounts, to do that, it's going
to look something like this on the example tab. When we are done, if I
go to the example tab, will have no transactions here will hide some of the cells, so we won't delete
the transactions. And then we'll have our
beginning balances. And then the entries will be a nice clean column here and
then it'll Indian balances. Now be aware that we will treat this whole trial balance a little bit different
because you can, you can assume or
visualize this being created as just being created from in essence
the general ledger, to just have one
column here that's being constructed from
the general ledger. But as we go through
each transactions, It's nice to see the impact
on a nice little worksheet. We're going to try to maintain this nice little worksheet, which will allow us to give a zoom in to each transaction. And then on the general
ledger accounts, we want to have all
the beginning balances up top so we don't have any activity and we have a nice clean worksheet to work on for the next
accounting period, which we're imagining
Starting in basically January
of the next period. Let's go to the practice tab
and see how we can do that. Okay, so to work this out, what we would like to
have then are these, these entries, I want to
put them on this side here, so we have the
beginning balances. I'd like to keep the
formulas so that we don't actually
delete the items that we have over here and we can
still kind of see how we constructed those beginning
balances in the worksheet. I can't copy and paste them over because it's going to
change the cell reference. What I'd let him what I
need to do then is cut and paste them or simply
drag them over. What I'm gonna do is right-click
and I'm going to cut the whole thing and then I'm gonna paste it just normal here. And then I'll deal
with adjusting the formatting of the sales. So we're just going to
paste the whole thing. So there we have it. So
now these are pulling from the journal entries
so we can see those beginning balances and see where they're coming from. Then I'm going to
do the formatting. I'm going to select
this cell here. And then let's go to
the Home tab clipboard and the paintbrush and put a paint brushy on
down on this one. So there's our formatting. I'd like to make these middle
ones, that blue format. For that. I'm gonna go here and
we're gonna go to then the bucket drop-down. If you don't have
this blue indicated, you can go to the more colors, the standard and you'll pick
up that blue right there. Okay. I'm going to put borders
around it and the font group and put the borders
around all borders. Then we have the
formulas down here, maybe off as well. Since we'd cut, I'm
gonna say this equals the sum of the items above it. So now that formulas
should work and we'll copy that across to the right. There are our
beginning balances. Now these aren't going anywhere
in here in the middle. They're going to that
beginning balance now. So that looks pretty good. What I'd like to do then is
copy this cell formatting and add another series
here so we can do our data input in a
fresh, new, new set. I'm going to copy from a to E to D not to eat at D. I'm
going to Control C, copy that and I'm
going to try to paste that right in
the skinny cell, but I'm not gonna paste it like normal because I don't
want to overwrite stuff. I want to insert
the copied cells. So I'm going to
right-click and then say insert the copied cells. And so there we have it. And then I want to
make one more column, which is like a skinny
column right there. So I can try to,
maybe I can like, well let's just insert a column. I'm going to put my
cursor on column E, right-click and insert here. So we're going to
have another column. I want to get rid
of the formatting. I got a little paintbrush
and I can clear formatting. And then I want
to make it just a skinny is this j over here? So I'm gonna put my cursor on J, Home tab and clipboard
it and then just paint brushy that one and
it makes it more skinny. Now all the data's not
right because that's fine. I just want to blink sales. I'm going to just make
that whole thing blink. Just remove all the data. But before I do notice these
second ones are indented. We use the alignment and indent. I don't want that. I want just the normal format in case I have more than
two accounts impacted, otherwise those indented cells are going to mess things up. So I'm gonna go to the Home tab. Clipboard paintbrush
and just make this whole thing the same formatting without
the indentation, and then delete it and
delete this whole thing. Then I'd like to hide the cells over here
because I don't want them in the way as we go forward with the
new data inputs. So I'm going to go from
column on over to column E. Let go. We're gonna right-click
that selected area and just hide that area, hide it there that
is hidden now. So now our general ledger, we're going to set up the
GL and we have to basically fix our our general ledger here. And to do that,
if they should be matching up to our
beginning balances. Now you might say,
Okay, I could just say, I'm going to say
this is going to be equal to my beginning balance, but I don't want to do
that because I'm going to, I'm going to use the beginning
balance has a little bit differently as we go with the beginning trial balance
to try to help us to zoom in on the transactions we're
particularly looking into. What I'm gonna do instead
is just hard-code these, just copying the number
and then typing them in 25 thousand here. And then I'm going to delete
this and delete this. And this should tie out because
we should be in balance. If everything works out up top. As we pull the information in, we'll see how this works. We could delete these items. This is going to be
accounts receivable, which according to this
as 20,500 hardcoded, which means type it in there. That's what hard-coded means. We're just type in it. It's not very hard coding. It's easy to code because
you just type it in. That's the easy way to do it. Really. Not hard. That's not hard. Inventory is going to be
this 12896 hard-coded. It's been hard-coded into the sounds fancy. And
then let's delete this. I'm gonna hide some cells
so I can see what I'm working on in a
side-by-side fashion. Hiding from this
cell on over to v, and then right-click
the selected area and hide, hide those cells. Poor father. Then we're gonna go
to the furniture and equipment and this is
gonna be the 75 thousand. And then scrolling down to
the accumulated depreciation, which is gonna be negative 75
thousand with a hard code. This is some hard code, man, this is some hard code. This is gonna be equal to
the last one plus this one. And then we've got this one over here
on accounts payable. We're gonna hard-code
this one at negative negative 15 thousand. Then the visa on down below is going to be the
negative 1 thousand. And let's hide some
more cells then let's hide some sales
from the skinny. Hide and from the
skinny to the AD, right-click and
hide those cells. They have been
hidden. There we go. And then the loan payable
is going to be hard coded at negative 22 thousand. And then we've got the equity which has all the action in it. We had all this
action in equity. We're not going to equity
is not going to see that much action anymore except
for the closing process. So it'll be a while
before we see that kind of activity
in equities. So negative 77896 and the
equity. And there we have it. So then the beginning balance in a sub ledgers
I think we're okay with we'll just keep
those as is as is on VM. And then let's just
unhide some cells and see if everything works
out the way it should. So we're gonna put our
cursor from N on over to AF. So we're expanding over those hidden sales right-click
and we're going to unhide those cells so we can see them because
they were hidden. Meaning they're out of
sight, they hit well, they know how to hide those
cells when they hide. You can't. They're
good at hide and seek. But then if you just look
at the alpha, any case, I think we're in balance here, so we've got the zeros across. We're ready to do
data input with the beginning
balances posting both to the trial balance format. So we can see a nice
little worksheet format and we've got our
beginning balances, starting the new period
in our general ledger, ready to move forward, which we will do next. But before we do, let's, let's unhide some cells over here just so we always see
the unhidden sales. I'm just going to select
the whole thing and just unhide just unhide everything. It won't let me unhide that
way. On this last cell. This is the best place to hide because they won't
let you unhide. So there we have that. Now next time when we
go back into this, this is where we
start out and we'll see the whole thing
going into it. And we'll start to do data input for the first month shortly.
Robert Steele
