Transcripts
1. Introduction: Enter normal accounting
transactions into Excel worksheet is a project-based course
where we will enter normal accounting transactions
into an Excel worksheet, which he could probably open
and Google sheets as well. If you wanted to try
out that option. As we enter the transactions, we will be referencing normal data input forms
often used in software, software like QuickBooks
in a database program. So we can get a feel for the actual transactions
that are being put in place when we enter
data input forms, data input forms
such as invoices, bills, checks, and so on. Down below we have items
that can be downloaded. These will be Excel worksheets. They will all have
at least two tabs, one tab with the
completed work done. So you can see the
end product of the second tab having the formatted worksheet
where we're gonna be adding the new information into the completed Excel worksheets
will be the final project.
2. Bill Form: Excel, accounting practice,
problem, build, form. Get ready because we're
about to excel with xo. We are in our Excel worksheet
that you have access to. The Excel worksheet would
like to follow along. We've got two tabs down below. Example tab, practice tab, example tab in essence
being an answer key. Let's take a look at it now. Also note in prior
presentations we put together this entire example sheet
from a blank sheet, which is great practice
and go through and put that together
if you so choose. In our example tab, then we're gonna be looking at the activity that
might be put in place with a bill type of form in an accounting system
or in other words, if you use something like
accounting software, the transactions are
typically going to be tied to a particular
form that transactions that increase accounts payable
within accounting software usually been done so with the data input for
called a bill. When we think about a bill,
from this standpoint, we've got to
differentiate a bill from an invoice even though
they're the same thing, but from different sides of the table with regards
to accounting software. So in other words, most
accounting software, the bill means that a vendor
is going to be building, AS for goods and services that we have been
purchased cash ultimately going out at the end
of the day to pay for the goods and services as
opposed to an invoice, which is worth gonna be the
people that do that goods and services and our billing
or invoicing the clients, which we'll talk about later. A bill form might look
something like this at the bottom with regards
to accounting software. And the bill form
is set up so that the data input can be as
easy as possible by at someone doing the
data input that doesn't really know
how or does it need to know how per se to set up the bill with the
items and so on. What is gonna be the impact
on the financial statements? But we want to do is
now look at the behind the scenes work basically
in an Excel formula in Excel format to see
what's going to happen in the excel system with regards to the creation of the
financial statements. So that's, we're going to
have three components. If we have a bill and we're
paying accounts payable, we might be paying
for a normal kind of expense or we're gonna
pay in the future. We're basically encourage
some normal expense like a utilities expense. We may have purchased
something like equipment that we're
gonna pay in the future, and that would be
a fixed assets. And then we'll take
a look at inventory, which is possibly
the most complex. If we're gonna be
tracking inventory within the accounting system. Let's go back to our
practice tab and we'll enter those into our items here. So let's say the first one. Let's say we're entering a bill and let's
say the date was 115 and we're paying the
utility bill of some kind, like the electric company
or something like that. So we're gonna say that the expense then would
be utilities expense. When you enter this
into a system, it looks like a check here. On the way that would enter
into accounting system, you put the name
which would be like Edison or something like that, the date than the amount. And then over here
on the expense tab, typically if we're just going
to be assigning an expense, we would put the
account that's gonna be impacted which might populate automatically if it's like the second month of data input because we'll follow
what we did last time, which would be
utilities in this case. If we do that with a debit and credit format to see
what the impact will be. We're gonna say that the
expense is going to go up a debit utilities expense. And if you don't fully
understand the debits and credits than
working problems like this will give you a better
understanding of the debits and credits and
how they function. But it does take some time to really understand
debits and credits. It's not exactly
just knowing math. There's rules to it, like a puzzle
that's a little bit different than just math. So take some time to do that. If you so choose the other side then is going to be going
to the accounts payable, which is a credit
balance accounts. So there's the credit
balance account. And then we'll have the amount at which I'm gonna just say it's $500 for the utilities. I'm going to put the
$500 on the debit side. The debit's gonna go up. The utilities is
a expense account which have debit balances. And every time you
do the same thing to it, you'll increase it. So we're debiting a debit,
so it's gonna go up. The accounts payable is
gonna be on the credit side, which we're representing
with two columns here, which is the traditional
way to do it. And I'm representing it
with a negative number. Note that negative number, that means something different even though we're gonna be using the mathematical
formula of it being negative to make our format
easier to use in Excel. But it also means a credit which is something different
than being negative. So although we're gonna be
using the fact that it's a negative for Excel formulas
to work the Excel formulas, you also need to think about it in terms of debits and credits. That takes a little
bit of a time to do. The most transparent way to
post something like this, to see what happens is
actually just simply to post it into a trial
balance like this, that looks like basically an adjusting entry type
of trial balance, giving us an adjusting column in the middle because that allows us to see the
beginning balances, the change and then
the ending balance. So let's, let's do that now. Let's post this
utilities account to the utilities account down
below, which is right here. I'm gonna post it
to the blue area. We're always going
to post it to the blue and the blue area. I'm never gonna put
a negative number. It's always going to
be equal or plus. I'm gonna say this equals, gonna pull over that 500. And it took this 1900, it went up by 500 to the 2400. It's a debit balance. We debited it anytime you do the same thing to
it, it increases. Also note what happened is that we're out
of balance here in our entries and the ending
balance is out of balance because we have more
debits than credits. And you can also see the
impact on net income. It was at income
because that represents the negative represents
a credit of 24710. We then decreased it by 500
with an expense to 24 to ten. Then if we go up top, then on the accounts payable
side of things, we're gonna say the accounts
payable is gonna be equal to, that's
accounts receivable. Accounts payables down here, credit side equal to the 500. Now it also went up in
the credit direction. And here's what I mean about
we're using the minus sign mathematically because
1600 up by 500, or you could say down in the negative direction
mathematically to 2500. Mathematically you could say, well wouldn't down
on the timeline to negative two
thousand, one hundred, two thousand one hundred. But from a debit and
credit standpoint, because we're using the
negatives to be credit. The credit went up in
the credit direction. But by 2100, that's a little
bit of a distinction. You've gotta kinda,
it'll get clearer and clearer when you start
using debits and credits. Now, what now we'd
also want to post it to that will put us
back in balance. By the way, now the
accounts payable went up, we're back in balance down here. Then there's no effect
from that second port to the net income because that's not part of the
income statement. Okay. So now we're also going to
post it to the general ledger. The general ledger
sometimes like later on, will actually post it only
to the general ledger and then use the general ledger
to make the Indian balances. But that's another
level of automation. And once you do that, then it becomes a little bit more less transparent for us to see if this transparency right here, to see what the journal entries impact on the accounts
are is quite nice. So let's do this. We're going to post
this utilities all the way to the
general ledger. Now this is gonna be
all the way at the end, it's the last account. So I'm gonna have to
scroll all the way over and I still want
to use formulas. I'm gonna go all the way until utilities, Here's the assets, and then here's the
liabilities and yellow and then the
dark blue for equity. And then here's the expenses and all the way at the
end and down here, we're gonna have our utilities. I'm going to put the
date here which I said I think I said it was 115. I hope I got that
right possibly. And then I'm in I'm in, I'm in cell AV 17. I'm gonna do the same
thing equals I'm never going to put a negative
or anything in here. I'm gonna say equals, I'm gonna
hold down the left arrow. I'm just going to hold it
down until I hit the wall, is going to hold it down
until I hit the wall. There's the wall and
then I'm gonna go up. You can see the
cells changing up top as we go in the formula bar, I'm going to find that $500, which is equals C2. I'm going to enter on C2. There it is. Now if
you wanted to just type it in there, if
you're practicing, you could say equals s2, if you would like to do
it that way as well. And what does that do? It it took the 1900
previous utilities amount, increased it by 500 to 2400 because we have a
running balance formula here. Once again, a debit balance
account going up in the debit direction
takes it up to 2400. That 2400 is also what should
be on the trial balance. So if I go over to
the trial balance all the way over here, we've got the two
thousand two thousand, four hundred right there. It looks good. Also note that we're
out of balance on the trial balance by 500 now, given the fact that we didn't record the other side
to the trial balance. The other side now go into accounts payable.
Accounts payable. I'm going to find that not on the trial balance in
the general ledger, the trial balances imbalance the general ledger
there TL is not. Let's find the accounts payable. It's gonna be down here to
the first liability account. So I'm looking for the
first yellow account. So scrolling over, here's
the accounts payable. I'm going to put it
in here in cell W 17115 were in cell X, X 1717. I'm not gonna put a negative
but just an equals. I'm going to hit the left arrow until I hit the wall again, left until I hit the wall, up until we get to
our accounts payable. There it is, the 500 and enter. There we have it now this
one went up from 1600. You can think about it from
a plus and minus standpoint. It wrote down further
on the timeline, the number line to
a negative 2100. But we think about
that as going up in the credit direction to two thousand and forty
two thousand one hundred, that 2100 should be what's on the trial balance
as well because this should be supporting
the trial balance. So there's the 2100
on the trial balance. It also puts our general
ledger back in balance up top. The next thing we want
to do is say, okay, this accounts payable is sorted
now by the trial balance. It's also sorted by
the fact by date here, but I also need to know
who we owe the money to. Who do we owe the money to? So I need to another
ledger which we call a subsidiary ledger. And that's gonna be alleged that will add
up to the same amount, but it's sorted by vendor. So I'm going to post the
same accounts table to the accounts payable
subsidiary ledger, which we put way on the right
past the general ledger, way way over here past the Geo. There's the accounts
the accounts receivable subsidiary ledger. Here's the accounts, the
accounts payable subsidiary. We got our vendors here. This vendor had 700, let's say it's called vendor for which might be like
the utility company. I won't change the
name of the vendor, but let's just say it's like the utility companies
vendor floor. And so I'm gonna say 115. I'm going to put the
same transaction here. I'm gonna do it the same
way. I'm gonna say equals. I'm going to hold
the left arrow until I go all the way to the wall, all the way to the wall
and then scrolling backup. And I want that
accounts payable again, which is which is going
to be in cell D3. D3. You can just put
equals D3 if you want. And that brings us back
up to that 500 here, 500 for that particular vendor. If I added up all the vendors, then it's going to take
the ending balance of all vendors that
should add up to 2100, which should match what's
on the trial balance in it that would be indicated
by the 0 here that it does. Let's double-check. If I go back to the right. We've got the 2100
here. Once again. Also note that this trial
balance to 2100 should automatically adjust our
financial statements all the way to the right. The financial statements There's the accounts payable
has been adjusted. We're still in balance here
because it adjusted it automatically and our utilities got adjusted automatically
here as well. And so everything looks like it populated on the balance sheet. Now note in accounting system, most likely what would
happen is you would post this and then you jump to your balance sheet and
double-check your balance sheet, and then drill down on
the balance sheet to see what's happening
on the GL Account. The GL Account here, then you would drill down
by making another report. That would be a
subsidiary ledger report, breaking this out by vendor, and you probably
wouldn't be looking at the trial balance that
much unless you'd like to look at the trial
balance instead of pulling up the financial statements. Let's do it again now this time, I'm gonna say this one's on 120. I'll do this a
little bit faster. And we're gonna say same thing, but now we got this bill, were going to pay something for Bill because we're gonna
pay it in the future. But we're gonna pay
it to say like like an Office Depot or something for equipment that we purchased, a larger purchase item. Now, over here it always says expenses as if it's
always an expense, but you could have a bill for an asset if something
like equipment. So I might say,
Okay, I'm now I'm purchasing something
that's not an expense. It's gonna be furniture
and equipment that I'm purchasing because it's
a larger dollar amount, let's say it's $5
thousand and therefore, I shouldn't be expensing it, but I should be putting
on the books as an asset. We may still run that through
the accounts payable here. So it's going through
the accounts payable. Here's our debit
and credit again, also note that you
might indent this. Some people really
liked to indent this Home tab Alignment, indent the credits.
Indent the credit. That I don't do
that all the time because they kinda messes
me up if I have to fix things and I got this indentation
there as long I mean, we're already kind
of double indicating that it's a credit
by having debit and credit column and the credits are negative and we're
going to indent it here. It's kind of an overkill, but it depends on your taste and who you're working with and whatnot and what
they like to do. So same kind of thing here. We're gonna, we're gonna,
we're gonna still be increasing the accounts payable painted off in the future. But this time we
bought an asset. So we're gonna put it
up top on the assets. So if I was to record that here, I'm going to say
let's record this. I'm gonna post it right here. We had 1000, 1600s,
I should say. Now we're going to
increase that by 5 thousand to 21 thousand, then the accounts
payable is gonna go up. Now there's something in
it already that's the, that's the downside to
having a system like this where you're trying to do this really transparent format. Because if I have a
lot of transactions and multiple transactions
to the same account, I'm gonna have to
add them in one cell instead of breaking them
out in the general ledger. That's why we break them
out in a general ledger. But for just a few accounts, this works quite well, which is why we use it when we have the
adjusting entries. If something's in it, I'm
going to double-click on it. I'm gonna go to the
end of it, say plus. It's always plus or equals, and then take that 5
thousand and Enter. That should put us back
in balance down below. There's no impact from
this transaction on the income statement because now we didn't we
didn't expense it. We will be an
expensing it later. It's not that we didn't
expense it because we didn't pay cash by the way. It's because even if
we paid cash for it, we put it on the
books as an asset, still no impact on
the income statement. We didn't expense it because it's something that's
going to impact multiple periods in the future. We will expense it in the
form of cost of goods sold, allocating the cost
over the useful life. We won't get into the detail
on how to do that now. But that's the idea. We just to point out, we do need a subsidiary ledger, but we're not going to report
it to the subsidiary ledger because oftentimes
we might depend on tax software or
something outside the accounting system
to support that. Oftentimes we'll talk
about that later. But any case. Now we're going to record it
again to the general ledger. I'm going to record
this account to the GL. Let's go on over to the Geo
and say we're looking for, we're looking for the
fixed asset. Right there. It's an S7, I'm gonna
say 115, T0, T7. And we're gonna say this equals, I'm going to go
to the left arrow to I hit the wall again, left until I hit the wall up. There's the 5
thousand and enter. You could simply
type in if you so choose the C5 and notice it increased debit
balance went up in the debit direction
to 21 thousand. So like things, a debit
will increase a debit. That 21 thousand should also
now be on the trial balance. It was before. We're out of balance
on the GL by 5 thousand until we
record the other side. The other side being
two accounts payable, of course, that's the
first liability accounts. Let's find the accounts payable. Here is the accounts payable. The next transaction
I said was on 120, I think it was just gonna
say equals and x equals. I'm going to hit the left
arrow till I hit the wall again, scrolling back up. And we'll pick up that
5 thousand negative. You can see what's
happening here. We got the 1006, it went up in the
credit direction. You can see it went
down on the number line from a mathematical formula, but it went up from
debits and credits to 21. Then it went up by five
thousand seven thousand, one hundred that 7,100 should be what's on
the trial balance. It is. That looks good. However, we also need
to record this on the subsidiary ledger by
who we owe the money to. The subsidiary ledgers
way over past the GL. So I'm gonna go way
over past the GL. And so here it is,
and I'm just going to pretend that it was vendor one. This time. I'm gonna say 120 vendor wound, let's say it was Office
Depot or whatever. And so now what we're going
to break this out by who we owe it to vendor
one, Office Depot. Bh x5 equals I'm gonna go all the way to the left till
I hit the wall again way over here with the arrow and
find that 5 thousand Enter. Now it also, by the way, you could just type in
negative 5 thousand. You might do that. But the less connections you have connected them
with formulas, the more likely if
something goes wrong, you won't be able to
find out what went wrong and you'll have to do
the whole thing over again. In any case it's back in
balance, indicated by the 0. If I double-click up top, we see that we're adding up
these bottom line accounts. And those four vendors add
up to the same amount, 7,100, that should be
on the trial balance. Now, there it is. On the trial balance. It is now the last one that the most complicated
oftentimes is inventory that we might
purchase on account. And this would be
common if we purchase inventory lots and
now we're going to not pay for an expense. It's not furniture and fixtures we purchased but inventory. Then we're going
to pay out later. So it's accounts payable. Same, same concept. Now let's just say we bought
one hundred, ten hundred, which let's say we
bought 20 units times $50 a unit I think is
well, I'm going to say that. And so that means that we're gonna pay from a dollar
amount, one hundred, ten hundred dollars will
post this the same way. So I'm gonna go over here. I'm going to be an inventory
on H7 equals the 100000. The other side to
accounts payable, so we're out of balance
now by the way, I'm going to double-click
on the accounts payable because
something's in it, go to the end of it, say plus pointed at one hundred, ten hundred increase in
the accounts payable to 8,100 back in balance down below no impact on the income statement
because once again, we didn't an accrual basis, we didn't, we didn't
earn anything yet. We will once we sell the
inventory therefore, and at that time, expense in the format
of cost of goods sold. Now we'll post this to the general ledger,
the inventory asset. Let's do that first. So that's gonna be like
the fourth account over fourth account
inventory asset, which we said on
this, I said 125, I think I said, I'm gonna say this equals
left to the wall. We're gonna say one
hundred, ten hundred. It went up and the
debit direction 4,375 up by one thousand five thousand,
three hundred seventy five. And then we're out of
balance on the GL. Then accounts payable, we
will post accounts payable. This is our highlighted account, accounts payables
in the spotlight. This is its moment of fame. Were in x. This equals all the
way to the left. Hit the wall. We're gonna pick up that
one hundred, ten hundred. And you could just type in, type in equals d nine. Some people might try
to memorize the cell. It might be easier
to do it that way. I used the arrows because
my memory is not that long. I can't memorize. Five seconds. There we go. We're back in
balance up top here. Now let's post it to
the accounts payable subsidiary ledger because
we don't know who we pay, which will be the vendor that
we owe and we buy inventory from all the way to
the right past the GL. We've got our accounts
payable subsidiary ledger. Let's say we purchased
it from vendor a three here on 125, that would be
whoever we purchased our inventory from happens
to be vendor three. Equals left all the
way to the wall, all the way to the wall, down to the accounts payable,
one hundred ten hundred. Again, you could type in D equals D nine
if you so choose. 3 thousand goes up by 100010003. If I add up all the vendors, we are now adding up all the vendors and
getting to that 8,100. It looks like it matches what's on the trial
balance by that green 0. Let's double-check. Going
back to our trial balance. We've got the 8,100. Lastly, we're going to record that same that same amount to the inventory subledger
because we now also need to be supporting the fact
that we need to adjust this inventory inventory
number here. So let's do that. So I'm gonna go all
the way to the right, all the way to the right
where we have our inventory. You can see it's
added balance now. And we purchased this first item of inventory and we're going
to use an average method. Remember, you can use a
FIFO and lifo average. I won't get into that
in detail because we're really focusing
on accounts payable. But just note, you're
gonna have to update this. The subledger will
update if you're using accounting software
that tracks inventory. And then we'd have
to put I'm going to put 20 units that we purchased and they cost 50 each. We've purchased 20 times 50, there's that 1 thousand. Our ending inventory
then is going to equal same thing I'm gonna say
the 20 times the 50. And so I'm gonna multiply
that out 20 times 50. And then I need to give me, I need to get basically
my average inventory. So what I'm gonna do
is underlying this. And say if I sum this up, if I sum this up, I get two. I get 270. And what I'm gonna do
here is I'm gonna, I'm gonna sum up the
tooth two items above it equals the sum of the
two items above it. That gets us to the 3,500. So that means the unit cost
is going to be equal to the 3,500 divided by 70 or $50. Now, you might think about doing that a couple
of different ways, given the fact that
we use the same cost. But notice if this cost changed, like if it increased
because of inflation at 55, then, then they would have
two different costs up here. And if we're trying
to average them, then I'm trying to take
the weighted average. So that's, that's what we're
attempting to do here. If it's 50, then of course
the weighted average will be still 50 at
that point in time. So underline,
basically these two, that 3,500 is flowing
down to the end. So that means that my total for my two types of inventory
still adds up to, well now adds up to 5,375, which looks like it matches
what's on the trial balance. Let's check it again, 5,375. Going back to the trial balance, if I go all the way
back, there's the 5,375. Note that on a bill in
accounting software to do that, you'd basically have to
set up items to do that. And when you do the data inputs, then it's kind of like when
you check something out at a check register or something like that
at the grocery store, you know what the sales prices, but the software then just does what we just did right
there behind the scenes. But in order to set that up, in order to understand that, in order to
understand the impact on the financial statements, you've got to have some
idea of what what we just did there and understand what
the inventory method is. Is it FIFO lifo, average and what the cost
is versus the sales price. We're not concentrated
on that now. We're just focusing
in on the bill. But those are the major
three kind of things that you can typically
use a bill for. You pay for something, you're usually paying
for an expense, or you're paying possibly
for fixed assets. That happens less often
if you're in the type of company where you
buy and sell inventory, you'll be purchasing
inventory quite often. And then if we go all the
way to the financials, to the right, note, they have populated
automatically, so the accounts payable
has gone up properly. It should have 8,100. We're still in balance here. The inventory has been
increased and we see that the utilities accounts increased and our equity still ties out. So everything ties out
here on the balance sheet and you can see how
everything ties together. If you put this into an
accounting system would like, you would just have
data input the bill. Then when you check it, you'd probably be checking
the balance sheet, checking things like
the accounts payable, the inventory possibly
drilling down on it. When you drill down on it, say drilling down
to the inventory, you would then most likely go to a transaction detailed report, which would basically
be the general ledger. It might be called a
transaction detail or whatever, but it's breaking out
the detailed by date. And then you might say, well, I would also like to
know what kind of inventory items and look
at your inventory items, then you would be
looking at sub ledgers, making another report
backing up or supplementing, coming up to the same
total as those accounts, which would be the accounts payable subledger in this case. And or the inventory subledger, which might be called different things in accounting software. But in essence they're
subordinate ledgers and that they're just going
to order the data in some other order
other than by date, but add up to the same amount on the primary ledger that
being the general ledger.
3. Pay Bills Form Check: Excel accounting
practice problem, pay bills form, check form, get ready because we're
about to excel with Excel if we are in
our Excel worksheet, if you have access to
the Excel worksheet, would like to follow along. We got two tabs down below, example and practice example tab in essence being an answer key. Let's take a look at it now. We're gonna go through the
pay bill process to do so, we want to first enter
the bill recording the transaction that would be related to it to
the trial balance, the general ledger and
the subsidiary ledger, looking at the financial
statements and then think about the pay bill process
and what will happen when we do that. Note that in
accounting software, this will typically be driven by forums forms that will help
with the data input process. And then the data input will
be a little bit easier. What we're trying to do
is see what the behind the scenes is when
you enter the forum. So for example, the first bill, as we saw in our
prior presentation, might look something like this, looks like kind of
like a check form, but the bill means accounts
payable is going to go up. We would select the vendor, the person that we
will be paying. And then typically an
accounts down here, or if there's an inventory item, selecting an inventory item, the account might be
an expense account, utilities that might
be something like equipment that we're purchasing
fixed asset account. It might be inventory, in which case we'd have to
track the inventory as well, then that would increase
the accounts payable. That's our focus this time. If we're focusing in this
time on paying the bill, which an accounting software might look something like this. You'd have a list of the outstanding bills
which represent amounts that you owe increases in essence
to accounts payable, selected those bills, and then paying them with some form like a check form or some
form that indicates a decrease of the checking
account, like a transfer. And so you might select those and record them at one time. A screen like this
helping you to sort those outstanding amounts
that need to be paid. And then if you were
to drill back in on individual form that
was used to pay a bill. It might simply be called the check form or some
kind of form that would indicate a decrease
to the checking account. But oftentimes
still differentiate a pay bill form from
other checks or other electronic
transfers because the pay bill form might have something on the
bottom like this, which will not be
the standard kind of check form which you would have similar similar options
as we saw in the bill, which would include accounts or expense accounts are accounts
that you could select. A utilities expense
or the equipment. You're not having an
account and you're not selecting the accounts
payable down here. Instead, it's indicating
the bill that this check is connected to because this
check is paying off on a bill, which means accounts
payable is going down. If you want to know
what the account was affected when you
purchase the bill, like the expense account or the asset account or
the inventory account, you would then have to
go to the related bill that was paid off because that's the point in time that
we actually recorded the expense or the
asset or the inventory. Now let's see what
would happen with these transactions in terms of the journal entries up top, that's where we're
gonna focus in on here. So we can see how this
whole thing would kind of tie together
in terms of how it would be recorded and say the database system to make
the financial statements. Going back to the practice
tab, Let's record that now. So the first one, we're
going to record the bill. We did this in a
prior presentation. We'll do this a bit
faster this time, and then we'll pay off the bill. We're starting
over from scratch. We don't have the
same bills that we entered in the
prior presentation. I'm gonna say 115. Let's say that we paid for
office supplies. We went to like a Home
Depot or something. We purchased
something on account. We haven't paid
cash, we're going to pay it in the future. We would then be increasing
the office supplies. We're gonna say it's an expense because it's below a
certain dollar amount. We're not we're not gonna be
putting the office supplies and on the books as an asset. Expense account is
going to be debited. And then the other
side is going to be the accounts payable account. We're gonna say that we've paid, let's just say 750 this
time debiting the expense accounts because expensive
council are always going up in the debit direction. The accounts payable is
gonna go up with a credit. So I'm gonna say
negative of that 750. We might also indicate
that this is a credit by indenting Home tab
Alignment, indent. Now, note that again, the expenses basically are
debit balance accounts. They usually go only
in one direction, up in the debit direction. And we know the
payable is going up. It's a credit balance account. It goes up by doing
the same thing to it. We always do the same thing
to it to increase it. In this case, a credit increase
in the accounts payable. If we post this out, we're going to go
to the supplies. The supplies are down here in
the expense area we're in. So H 19 equals
pointed to that 750. Then we get to say this four hundred and fifty four ninety went up by 750 to 1240. We're out of balance by the 750. And we can see the net income went down because it's
a credit balance. This is income not a loss,
24710 minus 3656100923960. Then we're gonna
post the accounts payable, which will be here. So accounts payables in HTN. Equals the 750 credit. This went from 1600 up
in the credit direction. It went down on the
number line and the whole 22350 for but from a debit
and credit standpoint, it went up to 2350. Then we're gonna post this same same information in the GL. I'm gonna look for the supplies account which is somewhere in-between on the supply. So I'm going to find that. I'm gonna go over to the GL. It's in the same order assets, then liabilities and orange
equity, the dark blue. And then we've got the
income and expenses looking for the supplies expense
there it is, office supplies. In a Q5, 115, we put our cursor in AR five, I'm gonna say equals hold down the left arrow until
I hit the wall, all the way to the wall, then I'm gonna go
up to that 750. You could also memorize
these cell which is s2. There it is. You could double-click
on it, equals C2. The 490 went up because we did the same thing to a debit
and debit by 751,240, that went 1240
should be what is on the trial balance scrolling all the way back to
the trial balance. There it is, 1240. We're out of balance on the GL now until we record
the second half. The second half is gonna be in the accounts payable, the 750, that's the first boy orange
account, first liability. So I'm going to scroll to the
right assets and then the first liability first
orange account, cell W 17115 here. Now I'm in, so X7 equals holding the left
arrow till I hit the wall. Scrolling up to the
last transaction looking for that
accounts payable. There's the 750
in cell D3 inter. I'm going to double-click
on this item. You can see you could just
memorize and type in D3. Remember, you could just
type in negative 750, but you want to use
references if applicable, because if something's wrong, something's out of balance, I need to find something. I could use these tools up top and point to where
they came from, which is really useful. Those tools, by the way, are in the Formulas
tab right here. I like to put them up top and the quick access because
I use them quite often, they are very useful to me. That makes them go
up by one thousand, six hundred seven
hundred fifty to 2350. That 2350. Then also here
on the trial balance, we're back in balance
on the general ledger. Next, we're going
to then record this to the accounts payable
subsidiary ledger. The ledger subsidiary to the general because
it's going to add up to the same amount but ordered
by vendor instead of by date. That's all the way to the
right past the general ledger. There we have our subsidiary
ledger which we can see it's out of balance indicated
by the negative 750. We're gonna assume this is vendor for which I'm
gonna say is like Office Depot 115
on NCLB L7 be L7, I'm gonna say equals hold down the left arrow till we hit all
the way to the wall again. So I'm just going to hold goal down until we get to the wall. Find that 750 credit, there's the 750 and enter. You could memorize
that was in cell D3. Just type in equals D3, increasing it from 0 by
seven hundred fifty, two hundred seven
hundred and fifty. If we add up all four
vendors now that adds up to this 2350, we can see the 0 here
indicating we're back in balance that 2350 should be what's the GL as well as what's on the trial
balance over here. There's the 2350 on
the trial balance. Now let's assume on 120
we're going to pay, pay that thing off this time. In accounting software, you might be using
something like this. You'd be sorting out your bills, seeing which one
you want to pay. We pick one bill. Let's imagine we
picked one bill. It happens to be that one
that we're looking at up top, which I know is different in dollar amount than this here, but this is just an example. And then we could pay it off, which would generate
if we drilled down on the forum that we paid off in a check type form or
some form that would be similar to a check form indicating a decrease to
the checking account, which could also be a transfer
or something like a like an electronic
transfer that's just electronic decreases
the checking account. And the bottom of
it would indicate the fact that it's
tied to a bill. And then the bill is
where we actually recorded what we
purchased in this case, that being the supplies which
could have been inventory, it could have been
something like equipment. That's So now we're
gonna record that second half when we pay it with a check or something
that's gonna decrease the checking account whenever the checking account
is impacted, I'll usually start to construct my journal entry thinking
about cash first, because that's the account
we use most often, the one therefore
that people used to get usually get
a better handle on. In terms of debits and credits. And people usually start to think about debits and credits as if they always
increase or decrease in accordance with what they
do to the checking account, which is a trap that
people fall in. Because, because
of the fact that we deal with cash so often. But let's not think about
that now we're gonna say this equals the checking
account. It's gonna go down. Now, also note that I
put it on the bottom because generally the credits
are gonna go on the bottom. That's just a tradition. If you have a long journal entry and you want to
put the debits on top because you're
trying to think through it or because it makes
more sense to you, go ahead and do that as long as your debits and credits
are formatted properly, that will be fine. But if it's a
simple transaction, you want to be in
conformity with the normal, the normal conventions. So we're gonna say then that's
gonna be a credit of 750, will then debit the
accounts payable. Accounts payable
is gonna go down with a debit note window. It's gonna be a debit
because it has to be the opposite
of what we did to the checking account
and we credited the checking account
and we usually have a good idea of what
we're gonna do with that. So therefore, we're
gonna have to debit the accounts payable. We also know we have to debit the accounts payable because
the accounts payable is a credit balance account
that's the normal balance. And to make something go down, we must do the
opposite thing to it, which in this case is a debit. Now let's record it out. We're gonna say let's record
the accounts payable. So there's something
in it already here. So I'm going to
double-click on it, go to the end of it, say plus, and then point to that 750 and accounts payable in
our transaction. That brings the transactions
back down to 0, netting out the two
that brings that 1600 back to 1600
checking account. Now going down,
that's up here in h4, this is gonna be
equal to that 750. This is a debit, debit balance
account normal bounds, that's a credit opposite. Therefore, it's gonna
make it go down to 99250. There we have that, that
puts us back in balance. Notice that the second
transaction here, even though we paid cash, did not affect net income, no impact on net income. Net income was impacted
by this account, which was when we put the bill on because we purchased and expense and that happens on an accrual basis at the point
in time we enter the bill, because that's more likely to be the point in time
that we actually consumed the supplies or
the utilities or whatever, if it was an expense
account that was impacted. We're going to enter
then we're going to record this to this accounts payable to the general ledger or this whole transaction
to the general ledger. Let's go to find that
accounts payable on the GL, which is gonna be the
first orange account. There's the accounts payable. We said this happened on 120, I believe we're in cell X 18, I'm gonna say equals hold the left arrow down until we hit the wall, hitting the wall. Scrolling up, we're
going to find them that 750 and C5 and enter. If I double-click on that 750, you could simply
type in equals C5. We set, we now see that the
accounts payable went from 1600 up in the credit
direction to 2350. Then it went back
down with a debit, doing the opposite thing to it, back to that 1600. That's the trend we would expect to see an
accounts payable. It will not always be
like that close together. It could be a little bit
more complex since we have many different vendors
typically we're dealing with. But if you look at the
accounts payable account, it will always be going
up with a bill type form. Unlike the checking account, which has a whole lot of
different things involved in it, the accounts payable will always be going up with a bill type of transaction if we're using forums instead of
journal entries. And it'll always
be going down with a pay bill form when we're
paying down the bill. And we should be able to go
through the accounts payable and kind of tick and tie off. We should be seeing
it going up and down, matching up the
bills and pay bills. That then brings us
back to that 10006, which should be on the
trial balance here. We're out of balance
by the 750 tilt. We record the second half
which is right there. Let's record that second half
on the checking account. Here's the checking
account on 120, we are in cell L5, I'm gonna say equals
point to that 750. And there we have it.
So that went from 100 thousand down a credit because we're doing
the opposite thing to it. Anytime we do the opposite, it's going to bring it down to 99250 matches what's
on the trial balance, GL back in balance indicated
by the green zeros. Now we also need to
record this accounts payable on the
subsidiary ledger. So breaking that out, noting go all the
way to the right. It's a subsidiary ledger
to the general ledger because it's gonna come out to the same amount all the time, but broken out in a different
way ordered by vendor. We see that it's out of
balanced by that 750. We're going to imagine here
it's vendor for vendor four. We're going to imagine is Office Depot or something that
we purchased from So 120. Down here. Then I'm
in cell B, LT 18. I'm gonna put the equals button, hold the left arrow down until we hit all the way
to the wall again, all the way to the wall,
hitting the wall, scrolling up, we're picking up that 750 debit and the accounts payable
that's in cell C5. You could also simply type
in C5 here if you so choose. Note that if you just typed
in 750, that will work too. But if there was a problem, I use these tools up top
to see where that cell is coming from and
you can't do that if you just kinda hard
code the numbers. The more you tie things
to get together, the better that will be. Typically, these icons are
in the Formulas tab note. So they're here in
the formulas tab. So keep an eye on those items. I would use those and use
formulas when you can. It went from 0 up to 750
to 750, back down 750. This same trend as we saw in the GL accounts should
be even more clear and easy to see when we think about the individual vendors activity. It going up with a bill and then it going back down
with a pay bill kind of transaction that
should be easier for us to take and tie
those two things out and see if the items that
have not been paid by us, we're paying the bills, then we can see the ones
that haven't been paid and basically
connect those two out. Now, if we scroll up top, we're back in balance,
indicated by the green 0, adding up all four vendors here, the ending balances
adds up to 1600. That 1600 should be what is on then the trial balance as
well as the general ledger. There it is. Note that if you put this into
a database program, what you would probably do
is enter the bill here, enter the pay bills, then you'd run the reports. Those reports usually not being a trial balance unless you
like seeing the trial balance, which I do oftentimes, but most people will pull up the balance sheet and
the income statement. And look, look at the fact
that we're back in balance. So that works here and
you can see when we created this balance
sheet, why that is. And then we've recorded the the supplies expense
when we enter the bill, not when we paid it
affecting net income. And then you might
drill down on one of these accounts like
the accounts payable. That's where our focus is. If you drill down on
it and software say double-clicking on it
and zooming in on it. That will typically take you to a GL general ledger
type of report, which might be called
something else like a transaction detailed
report or something, but it's the same thing. Then we'd say, okay, there's
the accounts payable. You could see that
the activity that is happening on a date
by date format. However, that date
by date format, although it gives you
some nice activity, does not give you who
we still owe money to. It doesn't tell you where
that 1600 is still. O2. We could go back and dig out, ticking and tying
off what we have paid out and try to
figure out where was the bills that
actually make up that 1600 that is still remaining. But of course we would like to re-figure this another way. Most likely you would run another report which would
be a subsidiary reports, which you might not call
a subsidiary report, but its subsidiary in
that it's going to add up to that same balance
ordered in a different way, possibly a vendor
balanced detail, Vendor Balance Summary,
vendor balance, aging report that
would look like this, which gives you your
information by vendor. And then you can see the
activity on a vendor by vendor basis to see what would happen on a vendor by vendor. Now note in software
you also could run this report many
different ways. We can only, we only have so much we can do in Excel
because you have, it's like one sheet
as compared to a cube in a database program. Like a cube, you can pull together multiple
different sheets and then them together
in different orders. So you can have a
similar report by aging, like how old the transactions are sorted in different ways. You get a summary report
in a detailed report, but they're all in
essence the same in that they're subsidiary to the GL because the 1600s needs to tie out to the
geo the trial balance. It's probably not something
that many people, unless you like looking at it in debit and credit
formats look at, but it's usually
traditionally thought of as the building block document to create the
financial statements. In other words, usually goes you record the
general ed that's the journal entry posted
to the general ledger. The general ledger is used
to create the trial balance. The trial balance
to use to create the financial statements
in practice and software. Then you enter the forms, the forms then create magically
the financial statements. And then you'll zoom back down on the financial
statements, kinda like an
auditing procedure. Going back and drilling
back down to save the general ledger and then
generate other forums, life the subsidiary reports. That's how it will
typically work. Note that if you are using software and you get used
to the trial balance, it could be a nice quick
form to look at oftentimes, because then instead
of opening up the balance sheet and
income statement, you've got your trial
balance in one format, one list of accounts
that all the accounts that have
activity to them. And you've got the balances, and you don't have any of those subcategories like
total assets, current assets, and that's that stuff which increases the size
of the report a lot. So you get a lot longer if a report if you're just trying to see what happened
to each account, you'll have to deal
with debits and credits instead of a plus
and minus format. But even using
accounting software, It's often nice to go to the trial balance
because you can get to one report and kind of taken tie off what
actually happened. Going from the data
input like a form bill, pay bill to the trial balance, then drilling down on the trial balance to get
an essence to the GEO, which is like a
transaction detail report.
4. Check Form: Excel accounting practice
problem, check forearm. Get ready because
we're about to excel. With Excel. We are in our Excel worksheet. If you have access to
the Excel worksheet, would like to follow along. We got 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 put this worksheet
together from a blank sheet, which is a great exercise if you would like to go
through that process, you can take a look at
prior presentations. Now, we're gonna be using that sheet in order to
enter transactions. Transactions that
would be related to a check type form or items that would be decreasing
the checking account. So in other words, if you
look at accounting software, the forms that would be
driving a decrease to the checking account would typically be a check type form, which might look
something like this. Also note that this check
type form may be used or similar form may be used if there's like an electronic
transfer of the forum, basically meaning that there's gonna be a decrease to
the checking account. So it looks basically like
a check in this format. We've got the date here, we've got the name
of who we're paying. That will typically be a vendor. And then down below, we're gonna have
the expenses item or the Items tab on down below. This is very similar to a bill type of transaction
that we saw in the past. The difference being here, We're gonna be decreasing the
checking accounts directly. That's what the check
form will be doing. Or you can think about
this type of form as a form that would just be a form that decreases
the checking account, whether it be electronic
transfer or a check. That means that down here
under the expense items, we can either put the thing
that we're paying for, which could be like utilities or telephone or
something like that. It may not be an expense. We might be buying
something like an assets like equipment. And then if it was an item, we're purchasing inventory item, then we might be
purchasing inventory. Let's think about that up top, we've got the three
kinds of things that we might be purchasing
with a check generally will be paying to check for things like expenses, is like the utilities
expense, paints, say Edison company or something like that
for electricity. And then we might be purchasing an asset which we might be paying cash for and therefore
will hit equipment. And then we might
be paying something for inventory and be purchasing it simply
with a payment or transfer decreasing the
checking account directly. Those are the three transactions
we'll look at here. Back to the practice tab. We're gonna start
off with this first one saying we're just
going to write a check for like a year end or month end type of expense
like utilities, which would be most likely the most straightforward trying to transaction and the most common that would be decreased
in the checking account. As we do this, you can
think about a check form, same kind of transaction. If you had an electronic
type of transfer. If you use something like bank feeds and your
accounting system that connects to the bank and you're trying to record
with a bank feeds, you're still going to
be basically using a form like this in essence, within the accounting system because it's going to create, It's gonna see this
kind of form as the transaction that
you're going to use to decrease the
checking account. The journal entry related to that form would look
something like this. We're gonna say 115. Now, anytime the checking
account is impacted, I will typically think
about it first when looking at the journal entry
because there's so many things hitting the
checking account that we usually get a good idea
about what it's doing first. So it will be easiest to think
about it oftentimes first, I'm gonna say the checking
account is gonna be credited because it's
going to be decreasing. That's because it's a debit
balance account is going to be going down by doing
the opposite thing to it, which will be a credit. I put in the credit
on the bottom here. Because that's the
general convention. And I'm going to just
say that it was for negative 500 on top. Then I'm gonna put the expense, which we're gonna say it
was like the Edison bill. So we're going to put that
to the utilities expense. So I'm just going to
say utilities expense and I'm gonna say
negative of that 500s. So that's the debit. And we could indent then the
checking account down here, there's our journal entry. The expenses note are
debit balance accounts. They almost always go up, so they're gonna be debited all the time going up
in the debit direction, doing the same thing to them, to increasing them, then
we can post this out. Here's the utilities. If I post that out, then we're going to
scroll on down to utilities and we're
in cell in each 22, this is going to be equal
to that $500 and enter, it goes from 1900 up
by the 500 to the 2400 were out of balance due to the fact we haven't recorded the other side, the net income, this is income credit
balance income, not a loss, which is just the income
statement accounts went from 24710 down because we increased an expense by 500 to 24 to ten record
in the other side to the checking account is
gonna go right here in H4 equals that $500. That means this debit balance in the checking account went down because we did the
opposite thing to it, crediting it to 99500. We can then record
this transaction to the general ledger. Same kind of thing. The utilities is
way at the bottom. So I'm gonna go to the right. It's in the same order as
the trial balance on the GL. So we're gonna go to the right. Here's our asset accounts. The orange accounts are our liabilities account
dark blue, the equity, and then the revenue and expense accounts in blue
way at the bottom, we've got our utilities. I'm going to put 115 here
and I'm in cell AV 17, gonna do this with
a, with a formula. You can hard-code it, but I highly recommend using the formula because
that allows you to fix problems by tying the
whole worksheet together. And so I'm gonna say equals, I'm gonna hold down the left
arrow until I hit the wall. We're going to just hold
it down until I hit the wall and then I'm
going to scroll up to the last transaction
looking for that utilities. You could also just see
that it's in cell C2. And you could just type in C2. So I hit enter, you
could say equals C2. You should also
pull in that 500. It goes from 1900
up by 500 to 2400. That 2400 should be
the same amount. That's on the trial
balance wherever here, the 2004 hundredths on
the trial balance there, we're out of balance
on the GL by 500 until we'd record
the other side, which is way easier because
it's the first account, $500 in the checking account. The checking accounts
right there on 115, we're going to say this
is gonna be equal to and L5 equal to that negative
or credit of 500. We went from 100 thousand, we went into credit
direction 500, taken it down to the 995. That matches out what's
on the trial balance. And we have the green items up top indicating that
our GL is in balance. There's nothing else
we need to do here with a subsidiary ledger, unlike when we did
the accounts payable because generally when you
look at the checking account, that detail account
that we want is the account information by date. Oftentimes, the
utilities account, generally we're going to walk that expense account
simply by date. We don't need to break
it out typically by vendor or anything like that. Then the other thing we
might write a check for us. We might purchase
something like equipment. So if I say 120, same check form down here, it has an expense thing here, but you could put an
equipment account because we might buy something that's
over a certain dollar amount, something like a forklift
or something like that, That's going to be benefiting multiple periods
into the future. Therefore, even if we're
using a cash-basis method, we're required to do an accrual type of
thing in this process. Like if you bought a
building or something like that and you paid
400 thousand cash, even if you're on a cash basis, you're not going to
expense building expense. You're going to put it
on the books as an asset because that expense is going to be used over the
life of the building. It wasn't really an investment. So what we're gonna do is
we're gonna say, all right, that means then that we're going to say the checking account is
gonna go down again. So you might think
about it first, I know cash is going down. That's straightforward. I'm gonna say we
spent 5 thousand for some piece of equipment like
a forklift or something. And then we're going
to debit an expense. It's not going to impact the income statement at this
point in time, but rather, we're going to
debit furniture and equipment account,
an asset account. It's going to put it on
the books as an asset, when will we expense it then
expense it with the form of depreciation as we then allocate the cost of it over the
useful life of the asset. We won't get into that now, but that's the general idea. We'll say negative
of this number. Note that this furniture and fixtures is
an asset account. Therefore, it's going to
go up to make it go up, we do the same thing to it, which is a debit. We also knew that we needed to debit the furniture
and fixture because we credited cash and
therefore we got to do the opposite thing to it to make our journal entry balance, debit the furniture and
fixtures going to indent here, Home tab, Alignment and dent. Let's post this out. We got the furniture
and fixtures. Let's put that over
here in cell A2. It equals the 5 thousand. So it went from
16 thousand up by 5 thousand to 21 thousand. That takes us out of
balance down here. It didn't have any
differential impact on the net income
because it's an asset. The other side is go into
the checking account. So even though the
cash is going out, no impact on net income is yet to get to the
checking account. There's something in this cell, therefore, we're going
to double-click on it, go to the end of
it and say plus, and then point to
that 5 thousand, taken it down to
that 94 or five. That should put us
back in balance down below as it does with
the green zeros. And then we're going
to record this out. So we've got the
furniture and equipment. That's like last green account. So let's go into
the GL looking for that second to last
green account before we get to the orange, There it is. We're gonna say this happened. I think I said it was on 120. I'm in cell T 17, T7. I'm gonna say equals gonna hold down the left arrow
till we hit the wall, holding down the left till
we hit the wall scrolling up until we get to
that 5 thousand. We're now in C5. That's the other way
you might see it. Just memorizing the cell at C5. So if you double-click
on it there it is at C5 taken us from the 1800s. Thousand debit balance
up by five thousand, two hundred twenty one thousand
debiting a debit account, making it go up. That 21 thousand also being
found on the trial balance. There's the 21
thousand right there. We're out of balance
on the general ledger by 5 thousand because we haven't recorded
the second half, but that will be easy because
it's the checking account. And the checking
accounts right there, that's our first account. So we're gonna say
on 120, right there, we're gonna say we're in L6, I'm gonna say equals and scroll on over to
that 5 thousand. So now we took that 100
thousand down by 500 to 995 and then we took it down by another 5 thousand because we're doing the opposite thing to it. Crediting a debit
balance account, taken it down to the 94 or five, that 94 or five is what's on the trial balance and our
general ledger is in balance. Now, the last one is the most
confusing one because we do have to deal with a subledger
that's gonna be inventory. We're gonna say we're
tracking the inventory. So let's say at 125, this time, cash is
going down again. We're gonna say
cash is going down. We're paying cash for inventory. And cash has a debit balance. I'm going to make it go down. I usually think about cash first because that's the word
we'll get in her mind first because there's more
transactions to cash than any other account when you take everything into consideration
so you get good at cash. And so we're gonna say
cash is going down by, we're gonna say it's
gonna be 50 times 20. I'm gonna say, which is 10000. I said 50 times 20 because I'm gonna say it's
like 50 units, I think times $20 or something, something like that when
we actually record it, the actual units
that we purchased. But for journal entries, components, it just means
we're gonna pay $1000. And then we're going to say
negative of that number. That's gonna be the 1000 debit. Now just realize when you put this into accounting software, if you did this in software, this transaction
would not be like in this particular software,
it would be an item, meaning you'd have to assign the inventory item if you were to track it in the software, the software then being able
to buy the item being setup, properly, being able to record the inventory in units and
track that in the subledger. So that takes a
little bit more work, will get an idea of what
that might look like, kind of behind the scenes as we, as we do it manually
here in Excel. But the journal entry would be, it's just a straightforward
same journal entry inventory. And just realize that you could, if you use this one down here, you could use the
expense tab and just post it to inventory. But if you did that,
you'd mess it up because then you're subledger, which is your inventory
units wouldn't tie out to what's on the balance
sheet in inventory. That's why you have to use the items in order to in order to have
that subledger work. Kind of like you have to assign a customer if you're
hitting the accounts receivable or you have to assign a vendor when you're dealing
with accounts payable. I'm going to indent this one. We're going to go assign
alignment indent. The inventory then is up top. Let's post it. We're gonna put that right
here in the inventory. 100 thousand. It goes up from 4375 by one hundred ten hundred one hundred ten
hundred to 5,375, a debit to a debit
balance account, increasing it the
credit to cash. So cash has something in it, that's where our focus is on. So I'm gonna double-click on it, go to the end of it, say plus, point to
that one hundred, ten hundred dollar credit, taking it further
down to 935 hundreds. So we've got these
three items that are hitting the cash account, which will be able to see in more detail with
the general ledger. But now let's first
post the inventory, which is like the third to last. Green assets accounts with finite like the third Douglas
right there. There it is. I found it. I found it. It's right there. It's in so S5, it's gonna
be 125, we're going to say. And then in T5 we're
going to post it. This is going to be equal to holding the left arrow
till we hit the wall. We're going to go to
that one hundred, ten hundred dollars and enter. So it goes from 4,375 up in
the debit direction to 5,375. That 5,375 also being on the trial balance
and we're out of balance on the general
ledger until we record the second half of
the journal entry to it. But that's easy because it's in the checking account
which is right there. So we're gonna go into checking account in our GL in case seven, which is on 125125. And then in cell L7, L7, this equals, and we're going to point
to that one hundred, ten hundred dollar credit, bringing it down from
the 945 down by 1093005. That non D35 also being
on the trial balance, 9354 back in balance, indicated by the green zeros. But we're not quite done because that inventory account 5,375. We also want to know
what kind of inventory we have and how much
we have on hand so we can determine if we
need to purchase anymore and or know what we can sell at this point
in time for that, we need to go to the
inventory subledger. So the subledger is way
on the right-hand side. Note that if you run a different report and
like accounting software, it might call it an inventory summary account or
something like that. But it's a subledger
in that it's subordinate to the
general ledger and The total of the subledger
needs to match up to what's on the general ledger and the trial balance
and the balance sheet. Otherwise, something's
wrong as there is something wrong at this point until
we manually update it. So it's gonna look
something like this. I'm gonna say that we have,
this is what we have so far. We've got these many units, this unit cost, and so we've got 2500 of this inventory item, widget a, whatever we're
selling, widget B, or widget number to
75 widgets at the 25, that's how much we have. The sum of these 2,
two thousand five hundred eighty one
thousand eight hundred seventy five is what this
inventory amount comes out to. Now we're gonna say
we purchased more. I'm gonna say we
purchased the first one, the first item here, we'll say on 125,
we had a purchase. Now we've got the
three columns up top we're gonna be using. You could use this
same flow assumption. These are flow assumptions. Instead of specific
identification, I won't get into a lot of
detail on this because it gets a lot of
information there. But you've got lifo, FIFO. And the weighted average. We're gonna basically be using a weighted average
calculation here, we're gonna say that
we purchased more. So I want the detail
of what we purchased. We purchased 20 units, we're gonna say at $50. If I multiply that out 20 times 50 gives us
that one hundred, ten hundred, then I'm gonna
put that down below here. I'm going to just pull that
same information here. I'm gonna say this
equals the 20. And then I'm not
going to pull in the y. I'm gonna pull in the 50. This equals the 50. And then I'm going to
delete this item for now and just do
this one line item. This is gonna be
equal 20 times 50. Now this is what we had before, and now we added 20 to it. So I'm going to sum this up
equals the sum of these two, equals the sum of these two. And then I'm going to
sum these two up to give me the total
over here as well. This equals the
sum of these two. That gives us the total
now of the 3,500. And then I'm going to
calculate the unit cost. I'm going to back
into the unit cost, which is simply going
to be 50 again, which is gonna be the
3,500 divided by 70 is 50. Now why did I do that? Why did I do it that way? Because if the cost was
something different, like if it went up to $60, then, then this will give me my average cost, which is 53. So we're gonna assume
that next unit we sell on a weighted average method
is sold at $53 under the, under this worksheet that we're going to
use now we're not focusing in on inventory
valuation methods right here. But just notice you
need some kind of flow assumption in order to, in order to calculate it or some kind of specific
identification. And that then that kind
of information will depend heavily on what industry you're in and how much
specialization you might need depends on what
kind of software you'll use. But just as just an idea of how you'd need to support
this and back this up with some kind of
subsidiary ledger that should tie out to
your general ledger. So now we've got that 5,375, which is that 3,500 of item a, the 1875 of item B, that ties out to what's on the trial balance
indicated by that green 0. But let's double-check it. 5375, going back to the right. If we go back to the right, we're gonna say, yeah,
there's the 5375. Now this also will all feed
into the trial balance is now being used to create
all the way to the right, the financial statements which have been constructed
automatically. And if you want to
see how that works, we set where you can look at the presentation on
how we set this up. But now the inventory
items have been adjusted, checking account
has been adjusted, the fixed furniture and
fixture has been adjusted. And then the item that we purchased utilities
here has been adjusted. We're back in balance
with the balance sheet. Normally if you're an
accounting software, what you would do
then is look at your balance sheet
and then say, okay, now I'm gonna drill down on the balance sheet like
DoubleClick for example, on the checking
account to look at the activity by transaction, by date, which would
be the general ledger. But in accounting software
might be cold like transaction detailed report
or something like that. And you would find
something like this given you the information for the checking account by
dates of transaction, if you were to
further drill down on one of these
checks, for example, to get the more detail you'd
get to the source document, which typically wouldn't be the journal entry because we would be doing it with forms. It would be the check
form down here. The check form then being what
drives the journal entry. The fact that it's a check for means checking account
is going down. The other side is
being allocated by the account down below, which indicates and allows the system to create
the journal entry. We typically wouldn't
be looking at the trial balance or
you may not many. When you work in accounting
software, you may not. Some people would. I think it's a good it's
a good report to look at, but the trial balance is something that is
traditionally used to create then the financial,
financial statements from. So oftentimes, when we
work with software, we don't look at that trial
balance unless you'd like to. I like to because
it's cleaner and shorter sometimes depending on what you're doing
without the subtotals. But the trial balance is
still there because it's what It's the middle
point that's there to make the financial
statements. And then of course, you
could drill down on any sub ledgers you want. So if you drill down on
inventory, for example, you might find that
GO of inventory, but that's not going
to give you all the information you want. You might run a
subsidiary report, some kind of inventory report, an inventory summary report
or something like that, which would be subsidiary to the inventory general
ledger account because it's going to
add up to or should add up to the same amount
and that report, but order it in a different way. In this case, ordering
it by inventory items.
5. Void Check Prior Period Adjustment: Excel accounting
practice problem, void check, and prior
period adjustment. Get ready because we're
about to excel with Excel. Here we are in our
Excel worksheet. If you have access to
the Excel worksheet, would like to follow along. We've got two tabs down below, example and practice
the example to have in essence
being an answer key. Let's take a look at it now, also note that in
prior presentations we put this worksheet
together from a blank sheet, which is a great exercise. If you want to put
that together, you can look at
prior presentations. Now, we're gonna be using
this worksheet to look at and analyze the
transactions related to the process of avoiding A-Check. A process that can actually
be quite confusing, especially if you're avoiding
a check in a prior period, which is a transaction
you want to be very careful of as we think
about this process, it'll give us a better
idea of the idea of being careful about changing
things in a prior period. Hopefully giving us
a behind the scenes look at why that is the case. We'll go through transactions,
stay down below. If you do avoiding
of a check say, in accounting software, then you'll recall from
prior presentations, the process of entering a
check is the form that will be used oftentimes for any kind of decrease in the
checking account. A similar form, even
if it's a transfer or some other way that the
cash account is going down. The check form will
usually be used as the data input form to
facilitate that transaction. The check form typically
having then the person you're going to pay and then
the accounts down below that will be impacted. Now if you go through
the voiding of a check in the
accounting system, you can void the check, which in essence will
reverse the transaction that you put in place when
you enter the check, which might be necessary. For example, if you
enter the check incorrectly or you've
doubled entered a check, or if a check simply
doesn't clear after some point in time
it got lost in the mail or something and you're
gonna have to void the check instead of deleting
it, you should avoid it. The idea then being that
you're going to have an audit trail that will show you the
activity that happens. You wrote the check
and then void it. So if anything happens
in the future, you'll have that audit trail as opposed to deleting the check, in which case you don't have the audit trail will tell
you what has happened. If you avoid the check
and the current month or the current year, then you can reverse it. Pretty straightforward. But if you avoid a check in a prior period which
has been closed, for example, a
prior month or even more significantly a prior year, then you have this
problem that you are doing something to
a prior period and you've gotta be very careful and we'll try to get
into why that would be the case if you were trying to avoid something in
a prior period, say, in accounting software, it might then give you an option to basically make things work out
properly saying, hey, look, if you avoid that, check what we'll
do is we'll 0 out. This check will tell you
that it has been voided. So you have the audit trail and then we'll enter
a journal entry. This is a journal
entry that's basically putting the check back in place. I wrote a very large checks
so that we could see it. That would be the
100 thousand here, decrease in the
checking accounts. And then it goes back
to the test account, which is an expense accounts. It's an example account. And then it's going to make
another journal entry. This was as of the prior period, in this case, 2023
was the current year. Prior year was 2022. So it did that reversed it
in the same prior year and then it recorded
this transaction again in the current year, which has increased in
the checking account, and then recording
a negative amount or decrease in essence, the expense account to void the check to avoid
the check completely, to avoid the check-in
prior period. In other words, it voided
the check, zeroed it out, and then it compensated
for the fact that it was a prior period adjustment by making an entry that
basically put the check back in place as
a journal entry. So now you can see that
it had been voided, but then it basically
replaced it. So your retained earnings or your balance doesn't still
rolls over correctly. And then it entered
another journal entry to kind of do the voiding
process in the current period. That can be quite complex. Let's, let's do that with journal entries up
top and see what, see why this is the case. I'm gonna go to the
practice tab now, the first check we're
going to think about is one where it's in
the same period, pretty straightforward
kind of voiding process. So if I was to say for example, we had a check and the check
was for miscellaneous. Let's say the checking
account went down. We're just going to record
a check that we paid for some miscellaneous expense. I'm gonna say all right, the checking accounts
can go down. That's what I'll think
about first because I know the checking account
goes down with a credit. I'm gonna say it was for one hundred, ten
hundred dollars. So there's the debit and
the credit for $1000. The other side we're
gonna say went to the account of
miscellaneous expense. We put it to
miscellaneous expense, our favorite account, we just put everything to
miscellaneous expense. And then we're gonna
go home tab Alignment. Don't put everything. That's not a good idea.
I'm just joking there. But in any case, now we're
going to record that. We're going to say Alright,
so we record that it's gonna go down to
miscellaneous expense. That's going to go up
when we write the check. That's gonna say that
went up and then that went up from
600 to buy 10021600. Other side go into
the checking account, equals the check-in account, one hundred ten hundred checking
account then goes down. If I post this to
the general ledger, we're gonna go all the
way to miscellaneous, which is like somewhere
in the middle, right in the middle
of the expenses. So it's in the same
order of accounts. We've got assets and
green liabilities and orange than the blue
for the equity revenue and expense accounts
we're looking for miscellaneous right down
here in cell am 17. So I'm gonna say this
happened on 115. I'm putting my cursor in 17, selecting equals, holding the left arrow down
until we hit the wall. There were at the
wall scrolling up last transaction,
there's the one hundred, ten hundred or you can
memorize that attends cell C2 and just
simply type equals C2. Now we went from
600 up by 10021600, that 1600s should then
be on the trial balance. Let's check it out. It is indeed there, that's good. And we're out of balance
on the G L tilde. We record the second half, which is two, the
checking account, which is our first account
right here in K5 115, this is going to be equal to that 10000 decrease to
the checking account. The 100 thousand going
down by one thousand, two hundred and ninety
nine thousand at 99 thousand and matching
what's on the trial balance, the general ledger
back in balance. Now, if we're going
to avoid that one and it was in the
same time period, then we can say, okay, now the check didn't clear or
I shouldn't have wrote it, got lost in the mail and so on. So I got to avoid the check. Then we can say, okay, well 115, the journal entry for it, if you did this in
accounting software, what you would do
is you would go to the check and you'd
select void the check. And if it was in
the same period, then you don't have a big
problem with it as long as it was a legitimate voiding
of the check was checked, that needed to be voided and it would simply reverse
the transaction, but do so by giving you
an image of the check. So you could still basically
go into that check and see that that
check was voided. And so you have an audit
trail instead of deleting it devoid the check then would
be another journal entry. We would do it with a
second journal entry. In other words, I'm
not just going to delete this journal
entry up top. Usually I would want to do another journal entry to kind
of show the audit trail. If I'm looking at this
from a journal entry by journal entry standpoint. So I'm gonna say that
we've avoided it. And I'm gonna put the void. I'm gonna say the
void is going to go back to the same day
we wrote the check. So even if it's like
I'm voiding it a month like or like
five days later. It's gonna basically void it
and reverse the transaction. Basically I have
today the check was written because because it
was erroneously written. So we're gonna say I'll reverse this checking
account up top. It's going to have to go back
up because it never really went down because the
checks not gonna clear. And that's gonna
be for the 100000. And then the other side is
going to go to miscellaneous. This is the rare event where
we're going to be crediting an expense account because we incorrectly debited
the first time. And that's when we're
going to credit the account just to basically reverse it out,
posting this out. And I'm gonna I'm gonna
call this the void transaction for the memo void. And so we'll record this. So now I'm gonna go check an account is going
to go back down, double-click in the
checking account, go into the end of
it plus that 10000, it goes back up to 100 thousand. And then the other side
is just going to match out against the
miscellaneous expense, double-clicking it,
go into the end of it plus pointing to that 100000. And that then goes back to
what it was at the 600. Let's record that in
the GL and the GL, we're gonna go to the
checking account on 115. I'm gonna say this equals than the one hundred,
ten hundred debit. You can see now we're back
to the place we started at. So why didn't I just delete it? Because I want to
see that I wrote the check and then
voided the check. And we could do that in the
accounting software by having the forum indicate that that
particular check was voided. And then we're going to say that the other side is gonna
go to miscellaneous. We're gonna go to miscellaneous
assets, then liabilities, equity, and then the
income and expenses. Here's miscellaneous. So I'm down in AM 18115, we're in an 18, I'm gonna say equals hold down the left arrow till
we hit the wall. And then we'll scroll up
to the last transaction. We're looking at
that miscellaneous at the 1000, It's in D6. You could also simply
type in equals D6. So once again, we're back
to the starting 0.600. It went up to 1600, down back to 600. That 600 should match what's
on the trial balance now? There it is, and it
should match up top. We're back in
balance on the G, L. Okay, now let's do the
more complex situation. We're gonna say this
is the transaction we're saying that
happened last year. I'm going to indicate that
by prior year because I don't have my years
and my date ranges. So I'm gonna say this
happened prior year. I'm not going to record it
right now because I'm going to assume it was
recorded last year. And I'm gonna say it was
recorded to office supplies. We're gonna say office supplies
and the checking account. Office supplies and
the checking account. And we're gonna say it was for $2 thousand. I'm not
going to record it. I'm going to indicate
that it was prior to your transaction by making
this a different color. And I'm not gonna
post it over here because it's not a
current year transaction. That's the thing we need to
avoid in the prior year. So I'm gonna hit
the colors up top. I'm gonna make that like this is what I made
it on the example. Something like how about
this color like that. Okay, we'll make it that color. Okay, so there it is. Now we've got to
avoid that, but now that happened last year. It happened last year. And I'm gonna I'm
gonna indent this. If I avoid the check. Like if I wrote this check
and the check was in there for last year and I avoided then the system is gonna avoid
it as of when the check was written because
it's going to say that the check was
basically wrong. And it's gonna it's gonna
avoid it as a prior period. Why is that a problem? Because this supplies account was recorded in the
income statement in the prior year and then it rolled into basically
the equity, which would be like
retained earnings with a capital account if it's
a sole proprietorship. And so if I delete it, I'm going to mess up last, I'm gonna mess up
the equity account. My equity is not going
to rollover properly. So that's gonna be
basically the problem. I can't just simply reverse it, but I want to avoid it, but I can't simply reverse it. So how do I deal with that? Well, one way you could
do it with journals with journal entries would be
fairly straightforward if it was if it was a journal
entry transaction, I'm going to say undo that. What did I do? I don't know. I'm gonna say then we're gonna say we're in
the current year, let's say 115 and
the current year. Now we're at the current
year and I need to basically board void that
prior year transaction. It would simply be then that the checking account is going to go up by that 2 thousand. Then the office
supplies is gonna go down by the 2 thousand. And I'm going to say
alignment indent, but this has happened in
the current year here. And with journal entries, I could record that in
the current current year because I'm recording in the
current year's time period. But in the accounting system, like if I was to do this in QuickBooks and I
avoided the check, I can't void the check
as of the current year. So that's the problem. If I did this with journal entries, it'd
be straightforward, I could say, Okay, I'm
just going to record this as of the current year. And I'm gonna say that
the checking account then is going to go up
by that 2 thousand. So it's gonna go up
by the 2 thousand. Here. I'm not going to mess with anything
in the prior year. I'm not going to mess
with the equity account, but I'm gonna record the negative amount to office supplies in
the current period, which is gonna be down
here, and office supplies, which makes a negative
office supplies. Which looks funny. How could it be a
negative office supplies? When I increase the
office supplies with this check because
this 2 thousand cleared into the equity
section and is no longer represented in the current year's
numbers down here. So I end up with actually
a negative amount, which is correct because
what I'm doing is compensating for the fact
that the prior year was off. But instead of going back in and adjusting the prior year, because if I was to do so after having finalized the
financial statements, my retained earnings
account won't roll forward. So I have to make
the adjustment to the temporary accounts in the current period
because that's going to be the easiest thing to do to basically move
forward without having to reissue prior financials
or something like that. Then we'll talk about how
would we will record this. And then we'll do one more step, a little bit more complex to see what happens in QuickBooks or an accounting
thoughts are as to why it would record these
multiple transactions. This one then we're
gonna say, okay, if I reverse this, then I'm gonna say it reversed
as we're gonna say 115. Again. This is going to be equal to the checking
accounts gonna go back up. And then the office supplies, we said was an office supplies
is over here, assets, liabilities, equity, and
then expense accounts, office supplies on 115. I'm still a R5. We're going to say equals hold the left arrow down
until we hit the wall. And then we're going
to scroll down to that office supplies, that 2 thousand again, the office supplies only had four hundred four
hundred ninety in it. We took it down Two thousand
and making it negative, which is funny for an expense. That's because of that
timing difference that we're reversing something happened that happened
in the prior period. Okay, let's do it one more. Now. We're gonna do one more. And we're going to do
it and say, Well, why, why does QuickBooks do this? Like if I did this in
an accounting software, why does it do this funny
journal entry type of process? Let's see if we can simulate that and see what's
going on there. Now let's say, let's say
that this happened on 215 or 225 in the prior year. So this is a prior
year check again. Prior year check and
let's say this one was for the telephone company. The other side is the
checking account. Now I'm not going to record
this again because this is something that
we're going to say happened in the prior year. We're gonna say it's for
$400. There we have it. There's our transaction.
I'm gonna make it. I'm gonna make it this color. I'm going to indent it and make it that funny color again, to indicate that that's a prior year transaction that we're trying to void now,
when I avoided, we're gonna say like
if I avoid it and I used the forum to avoid it, then I don't have the option like I did up top with
a journal entry of just entering the
journal entry in the current year to fix it. Because notice if I do that, even with a journal entry, I can't really see that that last that last transaction
in the detail was wrong. Object. But I am going to fix it
in the current period. What I'd like to do
is indicate that this transaction is
basically wrong. It's not clear that it was input and properly and I
need to avoid it even though I'm gonna
compensate for it in the current period and not go back and adjust it in
the prior periods. So this check, if
I was to avoid it, then it's going to avoid
as of the prior period. I've got to compensate for that. So in other words, if I was
to say, let's void this, I'm gonna do this as of
as of the prior period, which would be 225225. But I'm going to record the
transaction that would be resulting that you would
see in the current period, what would be the impact on our current trial balance
if I was to record this in the current period just to show what's going on here. So we're gonna say that would
mean the checking account. The checking account
would then would then be going up and the
other side would be going to I'm going to call it equity and then I'm gonna call it telephone, telephone expense. In other words, I'm
going to indent this. In other words,
what's happening? This happened last
year, prior year. If I was if I was to reverse it, but the impact on the current
year would be that the that the permanent account would still be impacted because
it's not changing. It's not closing
out as we call it. The telephone account, this expense account now
have rolled into the equity. This transaction that we reversed in the last
year would have the impact on the current
period's trial balance of this. I'm gonna I'm gonna record
it so we can see it here. Double-clicking
the cash account. The cash would be going up by
that 400, which is correct. But the other side would
be going to equity. It would be going
to equity here. Why? Because it would have hit the telephone
company account with a telephone expense
account last year. And then last year's
information would've rolled into equity, resulting in my
beginning balance in equity not matching anymore the ending balance
of the prior year. I'm not going to
record that into the general ledger because this is actually
something that would happen the prior year. I'm just trying to show you
how the current year would be thrown off because the
permanent account, which would be correct. But the temporary account
would have rolled into equity, so the impact would
be on equity. That's the problem. Then if I avoided this check, then we'd have to reverse it. I want to reverse it
as of the same date as of the prior year so that
I can see the audit trail, but still reverse it
as of the prior year. Therefore, I have these two
transactions right next to each other to show the
voiding of the check. And then I'm gonna record
the reversing of it, which is gonna be as
I'm going to save the same date too, 225225. And I'm going to
reverse this exactly. So I'm gonna say
this is gonna be the equity account and this is gonna be the
checking account. It's going to be
for the same $400, the same $400 to reverse it, I'm going to indent this. So I'm going to
reverse this exactly. So I'm going to
record the checking the equity account,
double-click in here, and then go plus, and then record that
$400 and the equity, getting our equity back
to where it should be. Because because we
closed out the equity, we don't want the
equity to change because we already closed
out the prior period. But now I'm going to
record the other side to the checking account, which is gonna make
it wrong again. So I'm gonna record That's the checking account
now, it is wrong. These are two transactions that would happen in the prior year, but this is the impact, in essence in the
current time period. That's why I'm not
recording it to the GL because it's really
happened in the prior year. I'm just trying to
show the impact of it. Then I and then I'm going to
record another transaction, which would be this final
transaction over here in the software that might do
that automatically for you, which will record it in
the current time period. So now I'm gonna say
in the current year, Let's say it's 315 that we determined that this check
needed to be voided. I'm gonna record it again, which is in essence gonna be the checking account
for the $400. The other side is going to go now to the telephone expense. Actually in the
current time period, negative $400
indenting this item. Now we're going to say
the checking account, scrolling up, we're
gonna fix it again, double-clicking
on it, end of it, plus the $400 in the
checking account. Now it's back to
what it should be. The telephone expense now is reversing negative going down, which is unusual and the
current time period to adjust for the error and
the check last period, this equals to $400. So it goes from set
2007 down by 401,205. Let's post this to our GL. This one we're going
to post to the GL, these two I'll
indicate that are not posted to the GL by
making them green. That I'm trying to
indicate that those are prior period
adjustments that we're trying to just see
what the impact was and why we're doing
what we're doing. And then we're gonna say
that this is going to go to the checking
account on the GEO, which is going to be I
said that was on 315315. This is going to be equal the checking account
for that 400. Then the other side go into
the telephone expense. Telephone expense, which
is second-to-last account. So it's in order assets, then liabilities
than equity income and expense want to go into the second to the last account. On 1315, we said
315 were in a v5. This equals scrolling
all the way to the left. Scrolling down,
we're going to be picking up that $400 Enter. Now we've got the 2007
going down to compensate for the voided check
that what happened in the prior period to 2003, that 2003 should be what's on the trial balance as we
could see down here. And we're back in
balance up top here. So let's see if we
can recap this now. This was the original
check that was entered into the prior period. We would like to
have the voiding of that check because it
was improperly input, even though we
didn't discover that until a later accounting period, we would like to have the
transaction right next to two when the actual
check was entered. So we're gonna enter
this transaction below. This transaction
then would actually be entered in the prior
period if we were to avoid the check in the accounting
software and it would be going to telephone expense. However, that's a problem because when it goes
to telephone expense, it rolls into equity. So the impact on
the current year is the fact that our
equity is now incorrect. We can't change equity
because we already finalize the financial statements
and the prior period. That's where the problem is. And this is accomplished and giving us the audit trail
when we void the check. So if I was just to
avoid the check, then we would have to
check still there, but voided given us the fact that we have
that kind of connection between the check and the
void that took place, but we've got this problem that it messed up now
our equity account. And so therefore, what
we're going to do is reverse it with basically
a journal entry. Silver will reverse it as of the same time period,
which you might say, well, that's redundant because now
I just put the check back in place and we're back to the
same place we were at before. We are. But we have this audit trail and
that's why we did this. We avoided the check
to show what's voided. Then we put the
journal entry putting his right back into
the same place as of the same date when we
put it back in place here. But then it gives
us the audit trail and then we're going to enter another journal entry
as of the current date, as of the current time
period in the current year, which will basically then reverse it as if the
current time period, which is what we want to do. And that would be increasing
the checking account and then having this negative
expense account. And so that would
mirror, in essence, the CEC being voided, which would reverse the
transaction in the prior period, then putting the check in
essence back in place but with a journal entry to
show the audit trail, then put in another
journal entry in the current time period so that we could
then record it back, back on the books in the current time
period instead of a prior period adjustment. Now also note that when you do this, you're gonna say, Okay, well wait a second
because now I've got if I was to look at
the checking account, I've got this this this and this going into
the checking account. And these these items would net out basically on
the checking accounts. So when you actually do
the bank reconciliation, you're going to have these
multiple netting transactions that are going to have
to net out so that you reconcile the bank
the bank statement at the bank
reconciliation process.
6. Invoice Form: Excel accounting practice,
problem, invoice form. Get ready because we're
about to excel with it. So here we are in
our Excel worksheet. If you have access to
the Excel worksheet, would like to follow along
two tabs down below. Example tab, practice tab example tab in essence
being an answer key. Let's take a look at it now
in prior presentations, we put this worksheet
together from a blank sheet. So that's a great exercise
that you want to look at it. We're going to use
this worksheet now to record sales transactions. Sales transactions, which if you're using
accounting software, will typically be done with invoices and or say a scanner. If we go on down below, this is a kind of
invoice that you would typically see in
accounting software. The invoice usually being fairly straightforward to do the
data input and you can do the data input if it has
been set up properly without knowing
too much on how to set up the process to
do the data input, or knowing how much it's
going to have an impact on the financial
statements, which is nice. But what we want to do is drill down and see what
the setup process it's going to require and what the impact on the financial
statements would be. The fact that it's
going to be an invoice means that accounts
receivable is going up. It's not we're not
getting cash or money or payment at
the point in time, the goods and service
that we're providing to the customers are happening, we're gonna get paid later and therefore must track the
accounts receivable. The other side then is
going to go to sales. And the sales transaction or the sales item will be not
just an account down here. In other words, we're
not going to just record sales account that
it will be going to, but it will be driven
typically buy items, service items or
inventory items. That's the more complex part
that we have to set up with the accounting system
to name the item. And then when we
select the item, it should pick up the
proper amount here that we're going to be
charging if it was simply a service item, the general transaction would be more straightforward
than inventory, simply increasing the
accounts receivable. The other side then go into
some type of sales account, typically on the
financial statements, the item then allowing us to basically group and
package the types of things that we sell
into the items and give a standard price
so that the person that's doing the data
input can pick that very easily and then do the
data input related to it. It's gonna get a little
bit more complex when we get to
selling inventory. Because inventory, we're going
to have the same process with the invoice means accounts receivable is gonna go up. But we're also
typically going to have to deal with sales tax. In the US, sales
tax or usage tax is not usually done
for service items. It's usually a state
and local type of tax which could be applicable
to inventory items, which complicates the
situation a little bit. So in that case,
for example here, if we chose these items
and we had sales tax related to it would sum up
the amount of the total. The data input could be
fairly straightforward. Here they have a
tax of the 7.5%, increasing the amount
that is owed by the tax. That means that we are owed
now 5,418 from the customer. So we would increase
accounts receivable by, in this case that 5,418. But the other side go into sales would not
include the sales tax, but it going up or increasing
revenue by the amount less the sales tax
because that's the amount that we
actually charged. The idea of sales tax bean, that the tax is not
actually on us, is the concept of the idea from an economic standpoint,
we could debate that, but the tax is not
really on the business, it's to the customer. They're just using us as the collection arm
for the government. So that means that we're
not going to record this amount as revenue,
the full amount, and then an expense
related to the tax because it's not really
our expense or our charge. It's the government
charged to the customer. Therefore, we're going to have a payable that's gonna go to that 3,378 sales tax, increasing the payable and then we'll pay it off
to the government. It never hitting than
the income statement. And then the other side
is going to go to sales. So we've got accounts receivable
going up by the 5,418, we've got sales go up by the amount not included
in the sales tax. And then the tax payable
going up for the sales tax. And we're going to have the fact that we need
to track the inventory, these being inventory items. If we're tracking
that into the system, we're going to have
to decrease the amount of the inventory, which is four and a mountain
not on the invoice. In a similar way as if you're in a grocery store and you're
scanning your items, you only see the sales price. You don't see the
cost to the store. But when you scan at
the scanner knows, in this case, the system
knows because of the items. As we do the data input in the item's inventory
would then decrease. And then we would also have a transaction to
the cost of goods sold related to the expense of a spirit of selling
that inventory item. Okay, so let's go
ahead and do that. We're gonna do two
transactions up top one as if it's
a service item, and then take a look at that
more complex inventory item. Let's go to the practice
tab to get it done. We're going to start
off, we're gonna say, we're gonna imagine we
sold a service item. And note when we
make the invoice, even when you do a service
item By the way down here, you'd still want to have items. You could do it on an
hourly rate method. But if you can package your
service items together, like if it was bookkeeping
and you were able to say, I'm gonna charge you by
so many transactions. If I do more than
25 transactions, I'm going to have a set price, then you can create
an item for it which will not be
dependent on your billing, but on your time. And that can make it
a little bit easier if you could
standardize that way, just a suggestion,
but in any case, the service items fairly straightforward.
We're gonna say 315. And it would be
increasing the accounts receivable because we're
gonna have to bill for it. If you're a bookkeeper or
a lawyer, for example, you're going to have to count your hours or figure out how your billing
structure will be and then bill for it,
invoice for it. Remember that the invoice represents in terms of
accounting software, it's the same thing as a bill, but the invoice represents the item or the bill
that we're sending out. A bill for the
accounting software usually represent something that we're going to be
receiving for goods and services that we
received from a vendor. Okay, So that's gonna be And we're gonna say
that's for 1600, just making up that amount. The other side is
simply going to go to revenue down below. We're recording
revenue even though we have not yet got the cash, because we're on
an accrual basis. Accounts receivable is
an accrual account. If you aren't a cash basis, you wouldn't even record the transaction tells
you receive the money. If you're in a type
of industry where you need these transactions, then you're gonna have
to have the accounts receivable and use some
kind of accrual method. Let's go ahead and record this. This one's gonna go to
accounts receivable. So accounts receivable
is not cash yet, but still an asset
increase in the asset. This is going to be
equal to that 1600. It's going from 2 thousand
up by 1602, that 3,600. Then we're looking, sales. Sales is going to be down
here on the sales items were in H 15 equals. And then I'm going to scroll
back up and we're gonna be pointing to that credit 1600. Sales is a credit
balance account. It goes up in the
credit direction to that two hundred and one
hundred and six hundred. Let's record this then
on the general ledger. Here's our accounts receivable. That's gonna be
the third account. Accounts receivable. And I have this
beginning balance red because if you used
a prior worksheet, I had set this up prior to go to be taken that beginning
balance from the end here, which is going to throw us off. So make sure that
that first formula, which it should be
in your example tab, is being picked up from the
beginning balance here. And as you go forward, so
it shouldn't be a problem. I'm going to modify that, making a green, okay? So this is going to
be on, We said 315. This will then be equal
to scroll into the left, scrolling up or picking
up that one hundred, ten hundred and six hundred, the debit balance of 2
thousand going up by one thousand six
hundred and three thousand six hundred that 3,600 matching what is
on the receivables, on the trial balance? We're out of balance now by
that 1600 up top on the GL, the sales are gonna
be on the 1600. That's the first income account. The GL accounts are in order, so we're gonna go to the right. We've got the assets, we've got the liabilities, we've got the equity, then we got income and expenses. We're looking for
sales that's in AI, so we're going to
stay on 315315. I'm in cell a, J5, AJ F5. We're gonna say equals
I'm going to hold the left arrow down
until I hit the wall. Hitting the wall, we're
gonna be picking up that 1600 in cell D3. Enter, double-clicking
on it. There it is. D3. We went from 200 thousand
credit balance up by 1600 to 201,600. Scrolling back to the right
that 2016 should then be on the trial balance
as it is here. And then we could
see that we are in balance up top on
the GL as well. Also, we hit the accounts
receivable account here. So that means that this account shows that people owe us money. I want to know who
owes us money. I can't get that information
sifted from the GL, therefore need a
subsidiary ledger breaking our data
out by customer. So I'm gonna go all the
way to the right to get to the accounts receivable
subsidiary ledger, which you've got to do
every time you hit the AR. And I'm going to assume
it's going to be customer for down here
that we sold money too. That's a generic name. Customer for 315. I'm gonna say on BD 17
is gonna be equal to, I'm going to hold the left arrow down until I hit the wall, all the way to the left
till I hit the wall, scrolling up to the
last transaction looking for the accounts receivable to 1600 and enter, you could also just
sit equals C2. And it goes from 0 up by one thousand six hundred and twenty one thousand six hundred. Now. If we take our four
customers here, add them up, they add up to 3,600, which does match what's on the trial balance
indicated by this 0. Let's double-check it though. 3,600 going back over
to the GL and or trial balance is what's on the trial balance
as well as the GL. Okay, now let's do the
more complex one will say, what if we're selling
inventory and we have sales tax that we're gonna be dealing with. Let's do this. We're gonna say 450, and this is the transaction
that will be built behind when you
create an invoice or when you say scan something at the grocery
store or something like that, what they're gonna
be recording in essence, with that transaction. We're going to say the
accounts receivable is gonna go up still. And I'm going to
have sales go up, but I'm not going
to record the sales yet because I need to
figure out the sales tax. So what I'm gonna
do is say, Okay, then I'm also going
to have sales tax. I know that's going to be
impacted as a liability. And then I'm going to have the
revenue account down here. The revenue account. I'm gonna try to store it
off with the revenue because that's what we actually
charge for the item. Now how will I know
what I charged? Let's say we're going to
be selling we're gonna be selling 40 units of inventory. So let's say they brought
up the inventory, they got 40 units of something. If it was an invoice, I would simply select
the item here and say 40 units of that item and it would give
me the unit price. And that's how it
would be figured out. How would we do
that in our system? We'd have to say, okay, well, what we're going
to pull that from, It's going to pull that from
our inventory register. So if I go all the way to
the right and I say, okay, let's take a look at
my inventory register. This is the cost
of the inventory. So let's say we're
selling this item one again now we're
selling it on 415. And I'm going to go to
the cost of goods sold area because it's gonna be
decreasing the inventory, recording the expense related
to that cost of goods sold. We're gonna say
we're gonna sell. I think I said What did I say? 40. We're gonna sell 40 units. 40 units at $50. Multiplying that out,
that means we're gonna, it's gonna be 40 times 50. That's not the sales price. That's the cost.
That's the cost. That means that the
ending inventory after we're done with this means we're gonna
sell 40 units at $50. At $50. And that's going
to mean, I'm gonna multiply this out 40 times 50. Let's underline this font
group and underline. And then I'm gonna say
that means that we have 50 minus 40 or ten units left. And this I'm gonna
say is equal to 2500 minus two thousand, five hundred, five hundred
dollars worth of units. And then I'm going to
back into the unit price, which will still
be 50 because we didn't change any of the prices, is going to be 5,500 thousand
divided by ten or 50. Once we're done, we'll have, we'll have 50 will have
50 units left at $50, ending balance at $500 worth. And I'm also gonna say that
there's an 80% markup. So that's gonna be the cost. And then there's an 80% markup. So the 2 thousand
is the costless go all the way back to the right. And I'm gonna say this
then the sales price. I'm gonna do it this
way with a formula just so you can kinda see
it when you go back to it, even though this is
a tedious formula, I'm gonna say equals, I'm gonna go all the
way to the right. I'm gonna be picking up that 2 thousand
that we calculated. And then I'm gonna divide it by 80.82 thousand divided by 0.8. The reason I'm doing that
is because we're gonna say 2500 times 0.8 would be 2080%. So 2080%, the cost is
80% of the sales price. We're gonna say
there it is 2500, then we're going
to have sales tax. I need to make that also a negative because sales is
gonna go up with a credit. So I'm gonna say negative
in front of that. So it's negative 2500 sales tax, we're just going to
imagine it's 5%. That's going to
depend on your state and local if you're
in the United States. So it might be a usage tax that will be dependent
on where up, whatever government's
charging you the tax on it. I'm gonna say this is
gonna be equal to the 2500 times 0.05 or 5%. And assume that we
have to pay another 125 or the customer
has to pay that. Remember, that's the
customers tax in essence, that means we're charging for the customer on the
accounts receivable. This is what I call
the plug formula, meaning I want to sum this up
and then reverse the sign. So I could do this
with a negative and then SUM these items. And there's the plug. So we need to be collecting
from the customer 2625. So that's kind of what we did
down here in the invoice. If we look at the invoice
down here, you say, okay, this is what we
charged over here. And then they had to calculate
the sales tax on it. Therefore, the customer
is going to give us the amount plus
the sales tax, but we're only charging
the amount that we actually charge go into sales. That difference
is going to go to the sales tax payable then
will deal with inventory. So same concept here. This of course,
bow ties out to 0. We're also going to
have the inventory, which I'm gonna do with
a separate transaction. Not because it happens
at a separate time, but because it's often
easier to think about these two things
separately because this is balanced up here. And then you can think about
what happens to inventory. Inventory is gonna go down. Inventory is gonna go down. I'm going to put
that on the bottom. And then cost of goods
sold is the other side. That's the expense of us basically using upper
consuming the inventory, moving it from the asset
down to the expense. It's going to be for
the amount of I'm gonna pick up that 2 thousand all
the way to the right again, I'm going to try to
tie it together. So you see where the 2
thousand comes from? It comes from that ledger. From the subsidiary ledger, there's the 2 thousand. Then I'm gonna say negative
of that 2 thousand. I'm gonna do some
indentations on the credits. There we have it.
So you can have two kind of things that
you're thinking about here. Accounts receivable is gonna
go up by the amount that we were selling the units
for plus the sales tax. Then we're going to have sales, which is gonna be just
for the amount that we charge for the units
which were not getting from the
inventory subledger. But we might have a standard
markup that we're going to be using sales price
different than the cost. And then we're gonna have the sales tax that's going to be applicable and inventory is
going to have to go down. This is coming directly
from the subledger, which is tracking the cost and the related
cost of goods sold, the expense of us now
spending or using up our acid of inventory in order
to generate the revenue. Okay, let's record it out. So we're gonna say inventory
is up to, I'm sorry, accounts receivable, somethings
in accounts receivable. So I'm gonna double-click on it, go to the end of it
and say plus 0.2, that 2625 and enter it goes
up again to that 62 to five. The sales tax payable is
going to be down here. In sales tax payable is going
to be equal to the 125. Notice that sales
tax payable does not hit the income
statement at all. We're not gonna have any
sales tax expense either. Because because it's going
directly to the balance sheet, because it's not us
charging it is the idea. And then we're
gonna go to sales, double-clicking on sales,
go into the end of it, plus picking up that 2 thousand, it goes up or
increases the sales, increasing the net income, putting us back in balance. I'm going to record this now to the general ledger to do so, I'm gonna try to
highlight these as we go so I don't get confused. This is something you might
practice doing if you get confused on which
account you're working on, you might say I'm just gonna
make that green for now. I'm looking for
accounts receivable. That's the second
account in the GL. Scroll into the GL.
So there it is. That's not the second accounts, a third account and the
geo here it is an AO 18. We're gonna say 415. Put our cursor in APA. This is going to be equal to go into the left. Scrolling up. There's that 2625
increase indeed receivable from 3,600 by
265 to 6 thousand to 25. That's 625 matching then what's on the accounts receivable
here on the trial balance, GL out of balance now. So now I'm going to ungroup, and this is going to
ungroup this green a phi. The next one going to make
that blue and green of Phi, the next one we're on this
one now making that green. And I'm gonna say that's
the sales tax payable, which is the last
orange account. It's in-order up top, we've got assets in green,
liabilities in orange. We have our sales tax payable, so we're down here, I'm gonna say 415
were in Cell AB 17 equals holding the left
arrow till you hit the wall, scrolling up, we're picking
up that one to five. So there we have it going from 0 up by 100 to five to 125. That one-to-five should also
be on the trial balance. It is. Here. We're still out of balance till we record
that last part. I'm going to green this one. I'm going to ungroup this one. And I'm gonna make
this last one green, right-click and make it green. And say that's the
one we're on, sales, That's the first account that's
on the income statement. It's in order. So
we're gonna say assets in green,
liabilities in orange, equity and dark blue, and then sales is the first income statement
account in AI 6415. We got our cursor in a J6. Now this is gonna be equal to hold into the left
arrow till we hit the wall and we're picking
up that 2500 Enter. So that increases because it's a credit to a credit to one
hundred and six hundred, increased by 2500 to 204100. That 204100 should then
be on the trial balance. Which it is right there. And we're back in
balance up top, even though we haven't
recorded the inventory side. That's why we can
kind of think of these as two transactions, although they happen
at the same time with typically the same
form, the invoice. Now we'll look at the
cost of goods sold. So the cost of goods
sold represents the expense related to the
inventory we consumed. I'm going to record
that first to the trial balance
right down here. This is gonna be equal to
the 2 thousand and enter. So that means that
it's going to go up. So 160 thousand plus the 2
thousand to 162 thousand. And then the inventory
up top is gonna go down. We're gonna be in H7. It equals the inventory
of the 2 thousand, taking the 4,375 down
by 2 thousand to 2375. Note the net effect on
the income statement is the increase in the revenue
minus the cost of goods sold. We only see on an invoice that the impact on
in essence revenue. We're not gonna see,
we're gonna have sales tax that even
muddies that up a bit. But really if you look at the
transaction in net impact, it's going to be the
revenues going to go up, but then whatever the cost is, also going to go up in terms of the expense which
will bring down the net income and net impact then being the difference
between the two, in this case, a net
increase of 2100. Okay, so now let's record
this to the general ledger. Cost of goods sold
is going to be like the second income
statement account. It's in the same order. Assets in green,
liabilities in orange, we're looking then
equity dark blue, we're looking for
cost of goods sold, which is down here
in AI 17 on 115, putting our cursor in AJ 17, hitting equals hold into left arrow down until
we hit the wall. There's the wall. And then we're going to
go, don't hit it too hard, but we're going to say 2
thousand. There we go. We got the 10006160 thousand plus the 2 thousand
increase in to 160 to that 162 should be on then the trial
balance there it is. The 162. We're out of balance
up top by the 2 thousand until we record the other
side that being inventory. Inventory is an asset, so it's right here. We're gonna say that
happen on 115 as well. Put in our cursor and T5, this is going to be equal to then the inventory going
down by that 2 thousand. So now we've got the 4,375 going down doing the
opposite thing to it, a credit it's a debit
balance account, the 2375 that 2375 also matching what's on
the trial balance 2375, GL back in balance as well. That looks good. Now also, we impacted the accounts receivable
account here. So we also need to see that
by customer who's gonna owe us the money so we can track who's gonna
always the money. We've got to go all
the way over to the subsidiary ledger
because we need something cluster or added
on to the general ledger tracking it by customer
and not simply by date. I'm going to assume
that customer one is who we sold this one to. That already owes us one
hundred, ten hundred dollars. Now on 415, I'm putting
my cursor on ASI F5, I'm gonna say equals
holding the left arrow down until we hit the wall,
hitting the wall. And there's the 2625 that
they're going to owe us, that includes the sales tax. And that's the 10000 plus the 201625 brings us up
to the 32253,625. If I add up all four of
the customers we have, then that adds up
to 6,002 to five. And that ties out to the trial balance
indicated by that green 0. But let's double-check
it to 600 in 25, going all the way back over. We've got the sixth, two
to five, there it is. And the inventory should
now tie out as well because we already adjusted at the start, the
inventory subledger. So if at 2375 then
should tie out. If I go all the way over to
the inventory subledger, we've got the ending
balance of the inventory, which is this item,
which if we include, if we add up the two inventory
items were left bins with 2375 left after having sold
40 units at a cost of 50, which was that $2
thousand that we sold. That's the cost, not
the sales price. So that ties out to, and then if we look at
our financial statements, we could see that we're
still in balance over here. We can see that the accounts
receivable has gone up. We can see that the inventory
account has been impacted. Then on the revenue side, we have a change in the revenue and the
cost of goods sold. If you were to enter
this transaction or enter an invoice into an accounting system or
scan something or something like that and then had
access the financials, you would probably then go to the financials and then start drilling down on
the accounts that would be impacted, impacted, such as accounts receivable
and sales, for example, if you drill down on
accounts receivable, double-clicking on it,
and a lot of softwares. It would then take you to an
essence to general ledger which might be called some kind of transaction detailed report. But it's basically a
ledger that's breaking the information out by date, which would look like this, which is in essence
a general ledger given you the detail there. If you further drill down on it, it would take you to
the source document, which generally wouldn't
be the journal entry, but would be the
invoice that was used to basically create
that transaction, you typically wouldn't be
going to the trial balance and software unless you'd
like to use in the software, which I typically do. And a lot of times because it could be a little bit
cleaner without the subtotals. And then if you needed any
other supporting information beyond what's in
the general ledger, you go to the supporting
documentation switch I would call sub ledgers because their subsidiary and that
they have to add up or should add up to the same
amount on the general ledger, but which might be
called something else in the software such as
accounts receivable, aging reports, accounts receivable balance,
detailed reports. They're all basically just
sort in the same data but configure in
it in other ways. In this case, by customer, simply needed to go
to the same balance. And then you might also look
at an inventory subledger, which it might call
an inventory summary, inventory detailed report
or something like that. But in essence, its subsidiary to the inventory amount
on the balance sheet and the general ledger because
it should add up and total dollar amount to the amount
on those other ledgers.
7. Receive Payment Form: Excel accounting
practice problem, receive payment for or get ready because we're
about to excel with xo. We are in our Excel worksheet. If you have access to
the Excel worksheet, would like to follow along
two tabs down below. Example practice example tab in essence being an answer key. Let's take a look at it now. Prior presentations we put this worksheet together
from a blank sheet. Now we're gonna be
using it to analyze a transaction related to
a receipt of a payment. Or in other words, we're imagining a
situation where we had done work in the past
issue in an invoice, increase in the
accounts receivable, and now we're gonna
record the receipt of the payment
from the customer. If we were to compare this
to accounting software, we might put a form such as this customer
payments type of form, which would follow along
after we have the invoice. The invoice would be the
transaction that would be increasing the accounts
receivable and then sales, possibly then having
inventory involved. However, we're not focused on the inventory at this point in time because we're really just focused on the
accounts receivable, tracking the
accounts receivable. Now thinking about when
we got payments on it, the customer then
being referenced for the payment that we
have been received, we would then be
referencing and time to the invoice and say
accounting software. And this form then would create the journal entry of
decreasing the accounts receivable applying
than the amount usually to either
the checking account going directly into the
checking account or we might have some kind of
clearing account that it goes into first. And that clearing account is something that from an
accounting standpoint, when you're learning
just debits and credits often isn't included. That's an added step
that you need to think about with an actual
accounting software. Because you need to think about the bank reconciliation process. So we'll talk about
that a little bit more as we go as well. So that's what we'll record
the transactions up top. We will record an invoice
type of transaction, then the receipt of the
payment due in two formats. One and more of a traditional accounting
setup where we're gonna receive the payment and
deposited directly into cash. And the second being
that we're gonna deposit into an deposited funds, which you might use an
accounting software. And we'll talk a little bit more about why that
might be the case. We're going to go to the
practice tab then enter this in. First, we're gonna
imagine we're entering an invoice for service
that was done. We're not gonna be dealing with inventory at this point in time because we really
just want to get the accounts receivable
on the books. So we're going to say
then that let us say on 315 we issued an invoice. Typically what would
happen there is receivables would go up. We would increase the
accounts receivable. We're going to debit
that to increase it. We're gonna say it's for 1600. The other side would be going to some type of sales account. Will pick up the sales
account down below sales. Notice it's indented
here, alignment indent, and the sales goes up with a credit which I'm gonna put in D3 as negative of
the cell above it. There is our debit and the credit for the
invoice, no inventory. Are we gonna be dealing with
this particular transaction? Let's record this item now. Accounts receivable. Here's the accounts
receivable account. It's going to be going up. I'm in. So H6 gonna say equals scroll on over
to that one hundred, ten hundred and six
hundred increasing 2 thousand up by 1600 to 3,600. The second half then go into the sales item
that's down below, we're out of balance now by
the 1600s, were in sales. Sales is a credit. That's why it's a
negative number. The negative numbers
representing credits, it's going to go up into
credit direction that will increase then the net
income down below as well, equals pointing to that 1600. And it goes up from 200 thousand up in the credit
direction to 201,600. The net income, this not
being a lost but income, the brackets representing
credits 24710 going up by 10,602, the 26th, 310. Let's go ahead and post this
now to the general ledger. So we're gonna go on over to the GL looking for
accounts receivable, the third account on
the trial balance, third account on the
GL then as well. That'll be down here in
delete this item down here. It's going to be down here in 017017, we're gonna say 315. And then in P 17
going to say equals, hold down the left
arrow till I hit the wall, scrolling back up. And we're going to be
picking up that one hundred, ten hundred and six
hundred and enter. It went up from 2 thousand up by 1600 to 3,600. That's 3,600. Vin is on the trial
balance as well. We're out of balance on
the general ledger by the 1600s until we record the
other side go into sales. That's gonna be the first
income statement account. It's an order in the GL. I'm going to go to the
right till we find IT assets in green
liabilities and orange, equity dark blue and then the
sales and the lighter blue, I'm still I95. Three 1115. We're now in a J5, I'm gonna say equals
scroll all the way to the left until I hit the wall
and then pick up that 1600, go into the sales account, going from 200 thousand up by 10,600 in the
credit direction, doing the same thing to it, increasing it to
the two hundred, one hundred six hundred
that two hundred, one hundred six hundred
also then being of course, on the trial balance, as we saw here, back in balance
up top on the GL, given the green zeros. Okay, So that's gonna be
the invoices in place. We also need to
record the accounts receivable in the
subsidiary ledger. So the subsidiary ledger should add up to this
the same amount, but it's gotta be
broken out by customer. That's all the way to the right. We're looking at accounts
receivable subsidiary ledger. I'm gonna put that in
customer for here. And so on. 315 were in cell B, D7 equals holding, holding the left arrow down all the
way till we hit the wall. We're looking for the debit
to the accounts receivable. So there it is, the debit
of the 1 sixth Enter. There it is. And we have then you could just
type in there equals D2 if you so choose increase in the balance
for customer for, by the 1600s to the 1604 customers now
then add up to that 3,600, breaking out the
accounts receivable balance by who owes us money, it is imbalanced to the trial balance and
GL given a 0 here. But let's double-check that 3,600 going all the way
back to the left is also, of course the amount
here in the receivables. Now we're gonna say the
received payment type of form. So now we're going to connect the received payment form
to this transaction. In practice, when you're
tracking this information, you want to tie basically
the received payment, as you can see here
too, in essence, that particular invoice and that allows you when you drill
down on the invoice to see that that's connected to a received payment and
therefore paid off the journal entry that would be supporting this
received payment. If we then took that money and deposited directly into
the checking account, we're imagining here,
instead of going to an deposited funds first, which is traditionally
how you see it in normal accounting. When you just learn accounting, because we usually just
work with cash and not an deposited funds and thinking about the
bank reconciliation. And then we'll add in the added wrinkle of
and deposited funds. So first let's just, let's just imagine
we got paid now. And we're gonna say, okay,
that happened on 325. We will imagine we're getting money and I'm gonna put it directly into the
checking account. So we're gonna say we
got it, we're gonna put it directly into the
checking account. Now, why wouldn't I put it directly into the
checking account sometimes if I got
cash, for example, and I got multiple different
amounts of cash and I'm going to deposit it into the
bank account at one time. Then my goal is to
put it into my books in the same format
that it's going to go into the bank account. That's why I might have
a two-step process. If on the other
hand, I'm getting paid for a particular
invoice and it's going directly into my bank account for
that particular amount. Same amount. Then I can put it directly
into my checking account. And when I reconcile my
books to the bank account, I shouldn't have a problem
and that'll work fine. So that's what we'll do here. We're gonna say, All right,
That's gonna be the 1600. And then the other
side is going to go to the accounts receivable
now going down, going to indent that alignment, indent, there's the
debit and the credit. So if we were to post
this, we're gonna say, okay, we debited the
checking account. The checking account
would be increasing. Accounts that are assets
have debit balances, they typically go
up with a debit. We're gonna say this will be
equal to that one hundred, ten hundred and six hundred, increasing the 100 thousand
by one thousand six hundred, one hundred, one
hundred six hundred. Then the accounts receivable
is going back down. There's something in
accounts receivable. So I'm going to
double-click on it, go to the end of my formula, say plus, plus that negative
number or the credit. So now I have debited
and credited it. That means the difference or
change goes back down to 0. We've got the 2 thousand
back to the 2 thousand here. Notice there's no impact on the income statement for
that second transaction, even though that's the point in time that we got the cash, because we've recorded the
income when we did the work, which on an accrual
basis would make sense because that's when
we invoice declined. And usually the invoice will be closer to the point in time that we actually did the work, which is when we usually
want to record the revenue. So now we're gonna record
that to the general ledger. Here's the checking account. Let's go on over to the
GL general ledger on 325. We're gonna say this is
gonna go up by the 1600s. That goes up from
100 thousand by 1600 to one hundred and
one hundred and six. That one hundred
and one hundred six matches the trial balance. We're now out of
balance on the GL by the 10006 accounts receivable, third account on
the trial balance, therefore also third account on the GL, the general ledger. There it is. So this happened on 325. We're saying
we're in cell P 18. I'm gonna say equals hold
the left arrow till we hit the wall and then scroll back up and we're
picking up that 1600. This should be the normal trend with the accounts receivable. And you see it started
at 2 thousand it went up with an invoice
in this case to 3,006 and then it's
going to go down with a received payment
when the customer pays us off that 2 thousand, then also now on
the trial balance, we also need to do that, however, with the
subsidiary ledger to show that which
customer paid us off. So we're gonna go all
the way to the right. The subsidiary ledger,
the accounts receivable, subsidiary ledger being
subsidiary because it needs to be adding up to the primary account of the accounts
receivable, 3325. We are in sale BD 18 equals going all the way to the left till we hit the wall. And then scrolling
backup and we're picking up that accounts
receivable in D6. If I double-click on that
there it is on D6 right there, then this is the
normal trend we would see it would be
and you would see this trend even more clearly
in the subsidiary ledger, meaning it goes up
with an invoice. And if you're looking at
like QuickBooks data, it would show you
that the type of form would generally be
an invoice and then it would go down with a
received payment type of form when we got paid. And you should be
able to go through and tick and tie these off. Those are the only
kind of transactions typically that'll be there in the accounts receivable
ledgers and sub ledgers. The total now of our four
customers back to 2 thousand. That matches the trial balance
indicated by the green 0. But let's double-check it. Going on back over,
Does it match? It does. Indeed. Let's do it again this
time on this second half. When we get the payment, we're going to put it
into an deposited funds, imagining that we've
got like cash, let's say, and we're gonna get cash from multiple
different people. And therefore we
want to put it into and deposited funds and then later roll it from an deposited funds into
the checking account. Grouping the deposits in the same format in our accounting system
as they will appear on the big statement
so that we can reconcile our books to the
bank as easily as possible. So same starting point. We're going to make
another invoice. Let's say it's on for
ten. Same invoice. We're not gonna be dealing with inventory because we're focused on the accounts receivable. So same service type of invoice, we're gonna say this
time it was 1400s just to make things different,
change things up. Same starting point. We're going to go over
here to sell six. Now I'm going to, instead
of double-clicking, I'm gonna select F2
on the keyboard, which is a lot faster if you
want to start using that. If you wanted just to show you. And then you can say plus. And then when I use the arrows
without using the mouse, I can say F2 on the keyboard. And that allows me
to use the arrows. That actually is a lot faster. It's more geeky, which is, that's what we're
going for here. That's the goal. Now we got those two things
that happened in what we got. Well now we increase it by 1400, basically 2 thousand
plus 1400 to 3,004, then down below and
the sales area, I'm gonna do the same thing,
something that's in it, F2 on the keyboard, plus, and then I have to
F2 so that I can move the mouse again and not have it move
within the cell. Then we're gonna
pick up that 1400. So that brings it up. So we got a total
increase now between the two sales items
of the 3 thousand, bringing it up to 203 thousand, were back in balance
with the green zeros. It brought net income up in
the credit direction from 24710 by 3 thousand to 27710. Let's record this then to our general ledger
accounts receivable is our third account on the
G L third account on the GL. There it is. We're on 310310. So we're in p equals going
left till we hit the wall. Scrolling up to that 1400. Here we had it went 2 thousand and then it went up with
like an invoice for work. We did build the client or
invoice the client to 3,600. Then we got paid. It went back down to 2 thousand. That's a received
payment type of form. Then we invoiced again, another kind of invoice
or we did work in charge the client
invoicing them 3,400. That's 3,400 should then be
what is on the trial balance. Our general ledger is out of balance till we record
the other side, sales, which is our
first income account, go into the right
till we find it. Green assets,
orange liabilities, dark blue is going to be the equity sales in
AI C6H12O6 on 410. And now we're in a J6, we're gonna say equals hold down the left arrow until
we hit the wall. And then we're gonna pick
up that 1400 and enter. I keep on running my
head into the wall here. This is not healthy. 200 thousand plus the 1600, that's the 201 sixth
plus the 10004203. That 203 should be also what's on the trial
balance over here. And it is indeed we're
back in zeros on the GL. So whenever things tying out, we also need to go to the subsidiary ledger because we hit the accounts
receivable again. We got to know who
owes us the money. We can call them and
hassle them daily until they pay us what they owe us all the way to the right. Let's say this happened for
the customer one this time. On 415 customers, they
already owe us $1000. Whatever, A-Z, F5, F5. We shouldn't sell them anything
else until they pay us. I feel like there's the 1400. So now it increased from
one hundred ten hundred by ten hundred four hundred and twenty two thousand
four hundred. Adding up then all of the four customers
that owe us money now, except that last one doesn't
owe us any money anymore, but that adds up to the 3,400. Where should be matching the trial balance
indicated by the green 0? Let's check it out. 3,400, is that what's on the TB? It is. Okay. So then we got the
received payment. We're gonna do the same
thing as we did here. Because now we have
this receivable amount we're imagining they're
going to pay us. But this time we're
gonna say, hey, look, they're gonna pay us with
cash or something like that. I don't want to put it directly into the checking account. Because if I put it directly
into the checking account, then it's not going to match up on my books to the groupings. That's actually going to go into the bank account because I'm going to group
these deposits differently when I go to the actual bank and deposit
them into the bank. And this is something,
again, in normal accounting, like if you were to use
debits and credits, you wouldn't have
these two accounts. Oftentimes they would
just call it cash. And therefore, you
don't have this issue of what am I holding
onto the cash? Or is it a check, or
do I have to group? But in practice, you always
want to be thinking, what's it hits the
checking account? Is it hitting the
checking account in the same format as the bank will be hitting
the checking account. Because that's my
huge internal control called the bank reconciliation. If I'm going to be depositing differently than they're going to be hitting the
checking account with. Then I want to use some
kind of method to allow my bookkeeping system to deposit in the same grouping
that's gonna be on the bank. That could happen and it's most likely to be a
problem if you get like cash payments and then you deposit in a group amount, or if you get payments
from credit cards. And the credit card
company, for example, is grouping your
deposits together in some grouping format and then putting it to
your checking account. And you're trying to track
the actual customers, then you've got to come
up with some kind of system which might
include another account, which you might
call it a clearing account, called deposited funds, putting it into and deposited funds so that you can figure out the grouping of the funds as they go into the
checking account, then make the checking account
deposit in our books and the same format as will
be the bank statement, so that you can then reconcile our books to the bank statement. The reconciliation being
a huge internal control and something that we
want to make sure to do. The software down below. That's the default setting. If you weren't to make
any set any changes, the QuickBooks would deposit it into this and
deposited funds. Now just realize that
that and deposited funds is partially because
of this reason because no one really learns it in normal accounting because
they just call it cash up top is the thing that
gets messed up a lot. People don't understand what that is and why you
would use it and so on. And then it gets,
causes problems. So you want to get
an understanding there is a purpose for it. If it's necessary to do, you've got to know
when it would be necessary and when
it wouldn't be. So we're going to
be the same thing. We're gonna say this
has happened on, let's say 415, except we're putting it
into a cash account, but we're colon that cash
account and deposited funds also note that if you're using QuickBooks
specifically software, then they don't actually
call it a cash account, they call it an other
current asset account. But it's really a cash account. The reason they put it into
other current assets is because it doesn't behave
like a cash account equip. It doesn't have the same kind of settings you need
for a cash account generally to be qualified
as a cache type of account. Which is really from a
software standpoint, they're designing
the account to do certain things for
a cash account. Therefore, from a
functionality standpoint, it actually works better as an other and other
current asset accounts. But if you were to
group it together, it would in essence
be a cash account because it represents
holding onto actual physical dollars that hasn't gone into the
checking account yet. Okay. Then the other side is
going to go to the accounts receivable, accounts
receivable account. And this is going to be
for that 1000400 were imagining we are getting
paid by this now. Alignment indent. Here, same thing except
that we're going into a different
different cash account. So we're going into
and deposited funds, that's an h5 equals that 1400 and then the
receivable is going down. So here's the receivable. We're just moving it from
here up to the cash account, double-clicking on it,
go into the end of it. Plus, we're going to pick
up that 1400 credit, taking it back down, that puts us back in balance. Once again, there's no impact
on the income statement. And therefore net income. The second transaction
because we're going to even though we
got money because we recorded the income when we issued the invoice
because that's closer to the point in time we did the
work and allows us to track the receivables with the use
of an accrual basis account, which is accounts receivable. Now let's post it to the GL. Here's the and deposited funds. That's gonna be like
the second account here, second account. Notice again in
QuickBooks it might be further down because they categorize it as
an other account, but just from
grouping standpoints, it should be a cash account. Okay? So this is gonna be 415. Then on p5, this is going
to be equal to the 1400s, so it's going up now 1400. And then the other side go
into the accounts receivable, the third account over
here, third account. There were all the
actions that that's where the action is happening for 15. So we're on this one I think
should have been 4410. Sorry about that.
And so we're on 415. This is gonna be
equal then left to the wall and we're picking up then the accounts
receivable going down. So here's our trend. We've got the 2 thousand
starting point. It went up with
basically an invoice or work that we
did that we build or invoice decline
by 1006 to 3,006. Then we got paid. It goes down when we
received a payment by 1006, back down to 2 thousand, then we did more
work invoice again, Bill into client
goes up to 3,004, then we got paid. It goes back down
to the 2 thousand. This is the trend
you would expect to see in the actual accounts
receivable account. It might not be this
easy to see of course, because there'll be a lot
of transactions happening. But if you were to
sort through the data, it would be increasing with an invoice, decreasing
with payments. And you should be able to
take from tie them off. Although they won't be right next to each other like this. If you had a lot of other
transactions happening on the transaction
subsidiary ledger, they will be pretty
much right next, very close to each other. As we go. If I go back to the left, Let's now posted to the subsidiary ledger which
is all the way to the right, going all the way to the right till we find the subsidiary, not all the way, but it's
way over here to the right. Now we're on a Wi-Fi AY six
and this happened on for 15. And this last one should
have been for ten. I messed up the dates
horribly sorry about that. This is going to then be equal to scrolling all the
way to the left. Then we're gonna be picking
up that one hundred, ten hundred, four hundred Enter. Same kind of activity here. This is what you would expect. 1000 starting point went
up with in essence and invoice did work build
the client to 2400, then we got paid, bringing
it back down to 10000. The total of the four customers
adds up to 2 thousand. That should tie out to the TB given a 0 here, Let's
double-check it. The TDB in the trial balance. It's not tuberculosis in this
case, it's a good thing. It's the trial balance. We like TB. Here it is the 2 thousand. So that's gonna be
the general idea. And then this amount in and deposited funds should
represent money that we're holding onto or money that is
being grouped in some way, possibly by the
credit card company or something like that. And then we're just using
that as a clearing account to then either go to the bank
at the end of the day, taking all the stuff
that we collected, all the cash that we have and deposited into the
bank in one lump sum, in which case we would
then move it from an deposited funds to the checking account
in that lump-sum, noting that that's the amount
that we expect to appear on the bank statement and we want it to match so that we
can reconcile with it. Or if it was like a
credit card situation, then we're moving it from the deposited funds to
the checking account in accordance with
the grouping that the credit card company
is grouping them in. So that once again, it'll show up on
the bank statement in the same grouping
as on our books. If this was just
one single payment that was an electronic transfer or something and it was going to appear on our bank
statement in the same format that that 1400 that that's when it would make
sense as we did up top, just to put it directly into the checking account and skip that second step
because it will be okay there because
really that and deposited fund is designed
to help us to group the deposits to appear
in our books the same way as it would on
the bank statement. Then if we go all the way to
the right, then of course, this information would
be used to create the balance sheet if
you were to do this in QuickBooks and the income statement, the
financial statements, if you were to do
this in QuickBooks, most likely you would
enter the transactions and then you would go directly to your financials
and say, okay, there's the checking account and there's the
accounts receipt, there's the and deposited funds, there's the accounts receivable. We're imbalance here, of course, QuickBooks will force
you to be imbalanced. And then I could drill down on say like the
accounts receivable, which would take me
to the general ledger or the transaction detailed
report, they might call it. So that would take
me all the way to something like the GL. And we'd say, okay,
there's the GL showing me the activity. And then you might say
Yeah, but I'd like to see that activity by date. Rather this is by date. I'd like to see it by customer
making another report, which might be some kind
of AR aging report, or it might be the
customer balance detail or summary report, which would be similar to our accounts receivable
subsidiary ledger. Those reports are
subsidiary reports to the general ledger due to the fact that
the total of them should add up to the
same amount on the GL.
8. Deposit Form: Excel, accounting practice,
problem, deposit form. Get ready because we're
about to excel with it. So here we are in
our Excel worksheet. You have access to the Excel
worksheet would like to follow along two
tabs down below, example and practice example tab in essence being
an answer key. Let's take a look at it now. The worksheet Kia is
something we put together from a blank worksheet
in prior presentations. Now it being used to record transactions related to a deposit type of transaction, meaning we got money
that's gonna be going into directly the checking
account as opposed to other cash accounts like
the and deposited funds. If you were to use
accounting software such as QuickBooks, it might be done or you might
see then a form like this, which would in essence
be a deposit type form, which typically would be
assigned to a checking account, meaning it's going to go into the checking account
as opposed to other cache type accounts like possibly and deposited funds. And then the other side here
might be coming out of, in this case, an
deposited funds, or there could be other things, of course, that we're
using the deposit for, Meaning that deposits usually, hopefully ultimately are
coming from the customers. And we're finally getting
the inflow of cash from the customers through
the revenue cycle. But it could be that we, the owner putting money in, in which case we would have to choose an account down here, being the other side, like an equity investment
account or something like that. Or we might have
gotten money, say, from a loan or
something like that. In which case we would choose
another account that down here for that instance as well. Also note that when we have
the received payment form, this is another type of form. The second form, as we've
seen in prior presentations, related to the accounts
receivable cycle or revenue cycle after the invoice receiving repayment
on the invoice, we by default, put this
into and deposited funds, usually as we saw in
the prior presentation, and then try to use that
and deposited funds to group the deposits in
the deposit transaction going into the checking account in the same format
that they will be seen or grouped on the bank statement
helping us to reconcile. However, we can use this form to make the deposit directly
into the checking account. Therefore, if you're looking at checking account increases, they might be done
usually should be done generally with
the deposit form. However, you might also see that received
payment form that is using the direct deposit into the checking
account as well. Now, if I was doing this process in QuickBooks
usually also one more thing to point out that
if these deposits were from the deposit form or going through and
deposited funds, I then would generally use this deposit form to help
me with my grouping of the and deposited
funds to put them into the checking account in the same grouping as they will appear on
the bank statement. However, if there
are other deposits or if I don't have this
problem of grouping properly, combining deposits
together, then you might just enter the deposits
directly into a register. As you can see down here, looking similar to
if you've ever used a standard kind of check
register information. You might have a register like this and you can also enter the deposits directly
into the register, into the deposit column. Okay, so now understanding
that we are gonna go up top and we're going to
record some transactions. And one of them is gonna be similar to what we saw before, the accounts receivable cycle, making the deposit
with the deposit form. And then we'll think about going through and deposited funds. And then we'll think
about other kinds of deposits will do
this fairly quickly. Because we're gonna
repeat some stuff. We got a lot to do. And because we're getting hopefully better at
this Excel thing. Alright, so we're
gonna start off with a full accounts
receivable type of cycle. The transaction related to us entering an invoice and then we're going to have the
received payment which we're gonna deposit directly
into the checking account. This is gonna be 315.
We're gonna say 315. We're gonna start off
and we're gonna say that we've got accounts receivable. We're imagining an invoice, we did work, we're
building the clients. That means the accounts
receivable is gonna go up, sales is gonna go up. We're imagining it's not inventory but simply
Service items. This would be our
invoice going out. So this would be the
1 sixth zeros 0. We saw this in
prior presentation. So I'm gonna do it
fairly quickly here. We're then going to go to the
Home tab Alignment indent. That would increase the
accounts receivable. So I'm in H6, this is going to equal then the 1600s other side go into sales, which is gonna be
down here in sales. This equals the 10006
accounts receivable going up, sales going up, net
income then going up. Due to the sales going up, even though cash has
not yet been affected. Let's bring this on over
to the general ledger. We're going to record the
accounts receivable in the GL, which is the third account
in the general ledger. We've got on 315 and sell P7 equals left till
we hit the wall up. To get to that
accounts receivable, we're increasing that
2 thousand to 3,600. That then matching the trial
balance out of balance by the 1600s until we
record the other side, the sales, which is our
first income account, same ordering in the G, L. So we got our assets in green
liabilities and orange, dark blue, the draws,
there's our sales. So we are here, we're on 315, were in cell a J5, which is going to be equal to a scroll and all the
window left to the wall. The sales of the 1 sixth 00, bringing it up to
that two hundred, one hundred and six
as we saw before. Matching out back in balance. Looks good. Now, we're going to record
this accounts receivable. Also did the subsidiary ledger, which is going to be subordinate
to the general ledger for accounts receivable way
to the right over here, imagining it's
customer for 315 were in cell BD 17 equals
left to the wall. Left to the wall. Scrolling up. And we're picking up
that debit of 1 sixth, which is in cell, hit equals C2. And if we add up our
four receivables, that adds up to the 3,006 that matches what's on
the trial balance. So that looks good. Now we're going to say
the receive payments. So this is gonna be a
deposit kind of activity, but we're not using
a deposit form or wouldn't be in QuickBooks. This is one instance where
you'd use something else, like a customer payment that would then be assigned
to make the deposit. Therefore, when you look
in the checking account, increases in the
checking accounts usually being indicated with deposits may be seen through
this form customer payments. If instead of going through
and deposited funds, they're taken directly
to the checking account. We're gonna say
that would happen. Let's say 325, we're
gonna get paid now. We're not going to put it
into an deposited funds because we don't have a
grouping problem or issue and we're just going to
put it directly into the checking account because we expect in the
checking account, this thing to show up on the bank statement as
a deposit of 1600, therefore, no added
other grouping is necessary for our
bank reconciliation. There's our debit and credit. The credit is going to
be accounts receivable. And so there it is. Straightforward transaction, Most likely the transaction
that you would see in normal accounting courses
that aren't software courses. But this item up stop
top simply being cash, usually generic cash instead
of checking account. This is going to be an increase in a checking account 1600. We've got that increase into the one hundred and one
hundred and six accounts receivable, something's in it. So I'm gonna double-click on it. I'm going to say plus, we're
gonna post that 10006. Now in accounts receivable
went up and it went back down. Let's post this to the GL now to the general ledger
we're going to go to the right to do that. We are in cell K5,
this is on 325. And then in cell L5, this is gonna be
equal to the 1600, increasing it to one hundred,
one hundred and six. Matching the trial balance, we're out of balance
on the GL by 10006 tilt record the
accounts receivable, third account on the GL. Scroll into the third account
on the GL there it is. 325 were in pH. It's going to be equal to left
to the wall scrolling up. And we're picking up
that 1006 credits now, bringing us back to that 2
thousand that we started with, that matching accounts
receivable here, we're back in balance here. We also have to record that transaction on
the subsidiary ledger, breaking it out by customer. So we're gonna go all the
way to the right to do that. We're down here in
customer numeral quatro. That's my Spanish for number four right there. Impressive. We're gonna say this equals and BD 1800s all the
way to the left, to the wall again, hidden our head on
the wall again. And then we're gonna
basically pick up that 1600. So we're going to say there
it is, back down to 0. These four customers
now adding up to 2 thousand matching what's
on the trial balance. And there's our first,
our first round. Now this one I'm gonna
highlight this one because this isn't
really a deposit form. Again, that's going into
the checking account. I'm going gonna make
it a different color, but we're using a another
form for the deposit, which is a little bit
different than a deposit form, but it's still an increase
to the checking account. Now we're gonna do the same
process but put it into and deposited funds and then use the deposit form to put
it into the checking account, which might be used if you have multiple deposits that are
gonna be grouped together, or multiple payments that needs to be grouped together
in some way other than just the one payment so that you can match out with
the bank statement. We're imagining this happens on for ten, same starting point, we got the accounts receivable, we got the sales, but this time the
amount we're gonna say is one thousand
four hundred, one thousand, four hundred
and dent into sales. Same starting points. So we'll record
this fairly quick. I'm going to start using F2 on the keyboard to
practice the keyboard. So here's the
accounts receivable. There it is somethings in
it even though it's 0. So I'm gonna say F2
on the keyboard, That's like double-clicking
on it then plus and then F2. So I can then scroll down. Wow, I didn't even use
the mouse like et al. Amazing. That brings it up to 3,400. And then we're down here
on the sales and revenue. Let's see if we can repeat
the F2 magic were on sale age 15, F2 plus F2. And people are like, you're not even using the mouse
and you're going rapidly. So then if we look down, then we're back in
balance down here. And then that's going to have an impact increasing, of course, our net income as sales
or revenue was impacted. Now let's record it in the GL
accounts receivable first, go into the good old
AR third account. We are in 019. This is going to be
410 Tab equals and P9, left to the wall. Boom hit the wall again. 1400 went up again from
the 2000s by the 1400s, imagining an invoice in
essence transaction, that 3,400 also being what's
on the good old TB out of balance by the 1400s till we record the
second half to revenue, which is our first income
statement, account, GLN order assets,
green liabilities, orange, equity, dark blue
revenues over here in AI. We're looking for,
we're looking for Ten. Is that what we
were doing for ten? I think. So. Hopefully I got my dates right. This is gonna be equal on age. Left to the wall. Boom hits the wall. And then we're on
that 1400 Enter. There it is, bringing it
up to 203 on the sales. Gl back in balance with the green zeros up top
sales two O three. We also need to hit the
AR subledger for that 10004 all the way to
the right to do so, this is by customer, looking for this
stuff by customer. Let's say it's
customer one this time already owes us $1000, but we sold them more stuff. I trust customer one. They're gonna pay us. We
could sell them more stuff. Where an A-Z y5 equals we're going to
go left to the wall. Then we're gonna be picking
up that 1400. Once again. We increase it
from 1000 to 2004, total of R for
customers adding up to 3 thousand for that ties
out to the trial balance. Looks good to go. So let's do that. Let's go then. Because we're good to go. I'm not going to just
sit here when we're good to go. I'm going to go. Okay? Then the next thing that happens is we're
going to say that we got the received payment form, which would look
something like this. But this time we're
gonna do what the default is kind of
like an a QuickBooks. We're going to use this item to receive the payment
from the customer, putting it into and
deposited funds, meaning we're holding
onto light cash. Then we're gonna
deposited into the bank, possibly grouping
multiple receive payments at the same time. It then show it on
the bank statement as a grouped number, which we want reflected
on our books so that we can reconcile with
the bank reconciliation. This happens on 415. And so we're not going to put it into the
checking account, but on deposited funds, which is a type of cash account, even though QuickBooks
puts it under a type like other current assets for various reasons we'll
talk about later. I won't get into it now,
but it's an essence of cash account holding account clearing account
you might call it, should be back to 0 shortly
after you make the deposit. The other side
then it's gonna go to the accounts receivable. And this is going to be for
the same 14014. Here we go. And then we're going
to the Home tab Alignment indent and
deposited funds. Now going up, Here's an
deposited funds on the TB. This equals the one hundred, ten hundred four hundred. That increases here. That's indicates like
a clearing account. We should either be
holding onto the checks or having credit cards that are grouped that
we got the money. But now we want to
group it in some way as we've been transferred into
the checking account so that we can then group it in the
proper format that it will be matching the bank statement,
accounts receivable. Let's do this with
our F2 magic trick. Up top, we're in cell F2
on the keyboard plus F2. And then scrolling
down no mountain, no mouse usage, no mouse usage, no impact down here
on the net income of that transaction
because we recorded the income when we
had the invoice. Let's go ahead and put this
in post it posting it. It's sometimes called to
the GL and deposited funds. Second account,
scrolling to the right. And deposited funds
is going to be here. This is on what was
the date on this one? This is on 415, I think. Equals left to the wall and deposited funds
going up by that 1400. Now we're out of
balance by 1400 and we've got 1400 matching
the TB trial balance, not tuberculosis trial balance. We like TB. They stand for trial balance. Accounts receivable then is the third account now
accounts receivable. Third account is gonna be 415. And then we're
gonna say in P 20, this equals left to the wall. Boom. Then we're going
up to that 1400. That brings us back
to that 2 thousand. That looks good. And then we're
back in balance up top 2000s matching the
accounts receivable, But we also need to
take it on out to the sublet subsidy ledger,
subsidiary subsidiary ledger. So we're gonna go all the
way to the right to do that. This customer number
one paid us that 1400. They still haven't paid
us that one hundred, ten hundred up top the
beginning balance, but whatever, they
paid us this one. So we'll put that here. 115 were in cell AZ six equals left all
the way to the wall. We got a good head
of steam and boom, head right into the wall. Then we're picking up that 1400. So there we have 10000. And then it went up by 104, down 104 back to 1000, a total of four customers
adding up to 2 thousand. That then matching the trial
balance back to the TB. Tb now has that 2
thousand in it. Now we got this money
in and deposited funds. And if that was the
only check that we got, then we could have really put it right into the checking
account and it wouldn't be a problem because then you
would expect that to be the amount that matches
on the bank statement. But if you've got multiple
amounts like that was cash or credit cards or something that you
then grouped together, then walked over to the bank and deposit it as one lump sum. Then the bank statement is
going to show a one lump sum. And we want to match
that to our books. That's, that's what this
clearing account does. Note. Again, this clearing
account often a point of problems for people because they've never
worked with before, because they've learned
just a cash account and not having to deal with this
kind of grouping issue. So you want to, you want to have a handle on that
because that's gonna be something that
most likely people will not fully understand. Oftentimes they're not 410. We're gonna we're gonna say
that we made the deposit. This probably would happen basically on the same day, 415, because we should get all of
our deposits and then put them right into the
to the bank account. So let's just make it the same. This should be like 415, same day in essence, we'd walk to the bank at the end of the day
and make the deposit. And so now we're
going to increase the checking account and take it out of the
and deposited funds. This is the moment
we've been waiting for. This is the deposit
transaction in QuickBooks. This is going from this customer transaction that went into and deposited funds when we receive the payment
after invoicing. And now we go over
to the deposit form. Where did the deposit
form go here? Which would look
something like this. Making a deposit and the other side go into these and deposited funds accounts, which would be
decreasing the end deposited funds accounts. Now we're gonna go to
that deposit form and use kind of like the transaction
behind the deposit form, which would mean that
the checking account is gonna go up now by that 10004 and deposited funds
going down on deposited funds, really a clear and account, not just a temporary account, it's a clearing account
in that it should go to 0 quite quickly. After it goes up, it should
go up and go right back down. These accounts down
here are called temporary accounts
because they will wash out at the end of the
year with our closing process. So this is gonna be cash checking account then
going up, something's in it, I'm going to double-click on it, go to the end of it
and say plus 0.2, that checking account and other side and deposited funds,
something's in it, double-clicking on it, plus
taking it on down to 0, so that goes back down to 0. It has cleared out. There's
the clearing account goes up, goes down really quickly. So now we're gonna say
checking account on the GL. Let's take a look at the GL, otherwise known as the
general ledger 415. It's going up with the check. Then deposited funds
over here is going down. That's in cell O6 also on 415. So we're in t equals
left to the wall, were taken that down
by the one for back, down, back in balance. That's gonna be the
second kind of process. Now that's the normal
kind of sales-type of processes that she would have if you were to
receive payments. You also might have a situation
where you basically make the sale at the same time that
basically you do the work. That's that's a sales receipt. Type of transaction. We'll talk about that in
a future presentation, but that's another thing
that can basically increase the deposit account for
now I want to think about other two other kinds
of deposits that we might have other than ultimately
coming from customers. One might be that we put money
into the checking account. Let's say on 515 that we, the owner, are going
to put more money in the checking account to
invest in our business. That means the checking account
is gonna go up directly. The other side is going to go to something like an
equity account. And this is the
confusing component because we don't do
that that often. You might ask, well, where
should the other side go? It shouldn't go to income
because if it went to income, that would increase the
income we would be paying taxes on it might maybe if
you're having an income tax, it should be going to some
kind of equity account. We might make another
equity account called Owner investments. But sometimes people
just put it straight to the equity account. And so that's what
we'll do here. We'll just increase the
equity account for it. And once you do that,
you've got a note, you can have to look at the
GAO and see that you actually posted something to equity which represents an owner investment. Okay, so we're gonna put it
into the equity account. And we're going to
imagine that we put in $10 thousand into
our own business. What would be the impact there? We'd say, okay, checking
account is gonna go up. So I'm gonna put my cursor
on the checking accounts, something's in it,
double-clicking on it, go into the end of it and
I'm going to say plus pointing to that 10 thousand increase in the
checking account. The other side then go
into owner's equity. So here's owner's equity. This is gonna be equal
to the 10 thousand, increasing it in the
credit direction. Notice there's no impact
on net income there. That's the key. It should be
going to an equity account. If you put it to
an income account, then your income's
going to look like you earn $10 thousand,
you didn't earn it. We put the money in and we certainly don't want
to be paying taxes on that 10 thousand note
that you could do that transaction with just
simply a deposit form here, which would not be connected
to the sales cycle, just making a deposit the
other side then being manually input to the account
of that equity accounts. However, if I'm using it personally in QuickBooks
and I have a deposit that I don't need to be
connecting to say and deposited funds in this
way. It might be easier. That's when I would
just go directly to the check register. Oftentimes enter the deposit
manually into an essence, the check register
in this format in the outer item here go into directly into
the deposit side. If you were to
drill down on it on a transaction that you entered
into the check register, it might still be
generating a deposit form. So this is just
another quicker way that you can do a data input. This happens to be
a journal entry, but in any case it could
be deposit type form. Then the next, the
next and final way we'll take a look at is
that you might get alone. You might get a loan
and get a deposit. So on 620 you can say
I need more money, I don't have the money, I
can't put the money in, but I'm gonna get a loan. So I'm gonna say,
alright, I got a loan. Sell, want to get
cash is gonna go up, let's say by 20 thousand, then I need a loan. Now, I'm also going
to add an account here because I don't
have a loans payable. Let's practice
adding an account. How could I add an account
here and to my general ledger, This will just be
Excel formatting. I could select these
cells right here, just these cells and I
want to push them down and add the account right above it and make it
that orange color. I'm going to select those
cells, right-click on it. I'm going to say we want to insert and then I want
to shift these cells down and there's nothing underneath these cells so it
shouldn't mess anything up. So I'm gonna say
shift them down. And there it is, there's
my next yellow line. I'm going to call
it loans payable. And I'm gonna say 0. And then this one's
going to sum up across just the same way. And so there we have it there. The other side go
into loans payable. Loans payable. There's our debit
and our credit. I can enter this into the
checking account up top. Double-clicking the
checking account, I'm gonna say plus that loans, that checking account 20. The other side now going
into loans payable in age 13 because we
got money from alone. Notice once again,
there's no impact on the net income down below of
that transaction as well. It wasn't income
even though we got money because we got to
have to pay it back. So it's a liability. Let's record these
two then to our GL, I'm gonna do this 1 first. The second one will
have to add an account. So I'm gonna make this green
so we can focus on it. Checking account
here. That's on 415. Also 415. I think I wanted
to make this five. Well, hold on a second. I made the wrong one green. I want to make this one green. We're gonna say, All
right, now it's on 515515. This equals the 10 thousand. And then the equity account here is gonna be the
first or second equity. Assets, liabilities, equity on 515 equals scrolling all
the way to the left, picking up that 10 thousand, it's at one hundred two
hundred sixty five now, one hundred two
hundred sixty five. So that matches out up top. Then we're gonna also on green a phi that
record this last one. I'm gonna make that blue again. Checking account going
up by the 20 thousand, GL on 620 equals
the 20 thousand. Then finally, I've got
that loan payable. I'm gonna have to
add that GL account. How am I going to do that? This is gonna be another
kind of formatting issue. What I'd like to do is add a whole nother kind of
my liabilities here. Now this is gonna be
a little bit tricky, so we'll focus in on this. We're gonna say
this 12345 columns. What I wanna do is
add five columns. First I'm gonna put my cursor
on this column, 12345. I want to add those
columns there. And I want to do so by pushing everything basically
to the right. So what I'm gonna
do is right-click on this now and insert. It's going to push
everything to the right. It tried to copy the formatting of the cell right to the left. I don't want it to do that. I hit this little
paintbrush and I say clear the formatting, don't format it
like that please. But instead, I'm going to copy these cells because that's the
formatting that I do want. Control C, paste
that right here, Control V. And there we have it. And I added one to many columns. So I'm gonna remove this column, right-click and
delete that column. So now I've got these
two other accounts. I'm gonna change
this account to be equal to this loan payable. And I can post to that
loan payable now. So now I'm gonna say
this is on the 620, were in cell AF five
equals left to the wall, scrolling down, gonna pick up then that loan payable.
So that increased. But I'm still out of balance on my on my GL
because I don't have those two new accounts in this huge formula
that's just picking up the ending balance
of all of them. So I'm gonna save, go
to the end of this ad then the ending balance
of that GL account, which is gonna be that 20. And then I'm going to add
this other blank one. Just in case I populated later, I've got this random account down here that I could delete, but I'll just add it in case I add something else to it later. And so there we have it. And that should then put us back into the green items up top. And that's posting that out. And you can see how you kind of shuffle around the worksheet. Now we're also going
to have an issue with the financial statements
for two things. One is we got this
loan payable and two, we entered something
to the equity item, which often is done, but it can throw things off when we focus in
on the financials. Let's take a look
at that real quick. So to do that, I'm going to hide all the cells so we can see the trial balance right
next to our financial. So I'm gonna put my
cursor on column j all the way then to the
financial statements on over to column CB, Right-click. Don't delete but
hide those items. And you can see my financial
statement is out of balance now by that 20 thousand. Now that 20 thousand represents this new account that
we created right there, I'm gonna assume that's
a long-term liability. So let's say it's a long-term
liability and adjust this. We're gonna say let's make this then total current liabilities. And then we've got
long term liabilities. And that's just gonna
be this one item here which is going to be
this loan payable. I'm going to put that
in the outer column negative to flip the sign of that 20
thousand indenting this, we're going to
indent that there. And then we can indent these
two maybe for the full way. That gives us then
total liabilities. Liabilities. Okay, I'm gonna spell
check it equals the sum of these two underlying in here. So there's our total
liabilities and now our equity. And liabilities and
equity should equal the sum of just these
two numbers now, and that puts us
back in balance. Let's spelled before
I spell check it. I've got to fix that,
but hold on a second. We also have the equity. It's coming out to
the right balance, but it's not exactly
right because this beginning equity
is actually wrong now, because we posted
something to equity, So it's still in balance. But it won't tie out
to the prior year because the prior year
ending equity was 9265. And now we've got one hundred
and two hundred sixty five. If we looked at the GL, we'd see the difference being that 10 thousand that we put in
as an owner investment. So we'd have to say, okay,
this beginning balance is not quite right. It should be negative of, and you'd get this from the GL if you ran into this problem, it should be that it rolled over from the ending
balance of last year, which is normally
the same number over here because we don't
usually post anything to it, but we did this time because
there was an investment. Then instead of having draws, I'm just going to change
this one to owner, invest, investment, investment. The owner put more money in, which is equal to, I'm just going to pull this from this middle column right now. You might pull it properly
from the general ledger, but I'm gonna say the
owner put money in that 10 thousand and I'm gonna
make that a positive, flipping the sign
negative in front of it. Owner investment, making
this a little larger. And in this now is an increase. It's gonna be this plus this. That gives us two are 129775. There's the 129775 which now matches up and ties into the
equity section over here, which then puts us
back in balance. I know that that was
probably more than just working on the deposit. But you could see
how you can kind of figure these worksheets out. And hopefully that was
useful in that overwhelming. So now let's unhide these cells. Putting our cursor on column I, dragging over to CD,
right-click and unhide. These cells, unhide there. So there we have it.
9. Sales Receipts Form: Excel accounting
practice problem, sales receipt form, and get ready because we're
about to excel with it. So here we are in
our Excel worksheet. If you have access to
the Excel worksheet, would like to follow along
two tabs down below, example and practice example tab in essence being
an answer key. Let's take a look at it now, in prior presentations, we put together this worksheet
from a blank sheet. Now using it to
record transactions, journal entries related to
a sales receipt type form, or in other words, a transaction in which we're doing work at the
same point in time, we're receiving the money. You might imagine a
situation where you have a cash register
in front of you. You're doing work at getting
paid at the same point, possibly like a restaurant
type of situation, creating them the sales receipt. If you were to take a look at
QuickBooks with regards to or accounting and salt where
this type of transaction, then you can imagine that the sales receipt
will basically be populated in such a way that the data input should
be easy to do. So you can calculate the
payment that needs to be made for the sales receipt. It's gonna look a
lot like an invoice, similar process except
for the fact that an invoice represents us doing work and then
building the client, whereas the sales receipt
represents us getting paid at the point in time
that the work is done. The most simple type of
sales receipt would be a situation where we do
service type of work, in which case the
sales receipt would be increasing either the
checking account, depositing it directly
into the checking account, or increasing and
deposited funds, which might likely be the
case if we had something like a restaurant because
we would have multiple different receipts
that we would be getting. We'd have cash then
that we would then want to deposit at
the end of the day, being able to use that
and deposited funds allowing us to group the
deposits in the same format as they will be shown in on the bank on the bank
statement so that we can do our reconciliation
process the other side then go into the
revenue account, increasing income at
that point in time. It gets a little
bit more confusing if we have inventory as we saw in a similar fashion
as with the invoice. Because inventory, it means
that we need to track the inventory as well
as the dollar amounts. And it means that we're going
to need to have sales tax possibly or more
likely are going to if you're in
the United States. So this is gonna be driven by the items down below in terms of a sales receipt items being something that
we need to set up beforehand to help us determine
what the price will be. Making the data input
as easy as possible on the person that's entering
the data into the system. And if there's
inventory involved, then you'll also have the cost of the inventory
so that you could track the subsidiary ledger
related to the inventory. This transaction, for example, would be increasing because
it's a sales receipt. The checking account, if
it's going directly into the checking account
or you might put it into and deposited funds cash. In other words,
it would be going up in one way or another. That would be for
the full amount. If there were sales
tax involved, that would include
the sales tax amount for the ten hundred sixty
seven eighty eight. The other side then it's gonna
go to the revenue account, but it's not going
to include the $50.38 of the sales
tax in this case, y. Y. In other words, don't we
put it on the books as revenue of ten
hundred sixty seven eighty eight in this case. And then record the sales tax as an expense decrease in it. Because the sales tax in
theory isn't tax on us. We're just being used as the collection agency
for the government. Therefore, the tax in
theory is on the customer, I say in theory because from an economic standpoint,
you can debate that. But that's the idea. So that means that
we don't want the $50.38 to be increasing sales are hitting the
income statement at all. We're just collecting it on
the government's behalf. Therefore, that
5038 is going to be increasing a liability account sales tax payable or
something like that, which we will then pay
at some future point. And if we have inventory, we're tracking the
item will also know and determine the cost
of that inventory. The inventory will
then be decreased by an amount not on the
sales receipt because we don't want it on
the sales receipt because we might give this to the actual customer and we
don't want the cost on there, but the system knows that amount will decrease the
inventory as well as the subsidiary ledger
for the amount of inventory and record the
related cost of goods sold, the expense related to us
consuming the inventory. Alright, let's see
how that works out with journal entries. And we'll do two types of
journal entries up top. We'll do one that's
going to have a service item and then we'll do the second one with
the inventory, which will be a bit
more complicated. Go into the second tab on over. First one, we're gonna
say this happens on 615. Let's just say that we did work and get paid at the
same point in time. You can imagine a cash
register type of situation, but it's service work doesn't include inventories,
so we're gonna say, All right, let's
deposit this one directly into the checking
account this time, which would be appropriate
in the event that we're not, we don't have this
grouping problem in that this amount is gonna be seen on the bank
statement as well. So the amount that
we're receiving is the amount of the deposit
we expect to be on the bank statement as opposed
to us needing to group that deposits together in
some special way. Groups so that they are in the same grouping as will be
shown on the bank statement. We're going to
imagine this at 1200. We're gonna credit
then the same amount. The other side is going
straight to the sales or revenue account at the point in time we make the sales receipt. There it is fairly
straightforward. We're gonna go to the alignment
and indent the credit. Posting this out goes directly
into the checking account here in H4 equals that 1200, bringing the 100 thousand up by the one
hundred, ten hundred, two hundred to one
hundred and one hundred two hundred directly into
the checking account. Then the sales account here, credit balance account, going down to the sales
items down below, we are in cell H2 in 15 equals we're gonna scroll
on over to that one hundred, ten hundred, two hundred increase in the sales on
the credit direction. This is an increase in sales, 200 thousand plus the credit
of 1200 to two hundred, one hundred, two
hundred net income. Then of course, increasing. This is a credit, not a
negative or not a loss. In other words, 24710 plus
1200 going up to 25910. Now let's suppose this
to the general ledger, the first account checking
account straightforward. That's the first item
on the right-hand side. So let's go on over here. We are on the date of 615. We're gonna post this in L5. So we'll just say equals
scrolling on over to that 1200 increase
in the balance, 100 thousand by
1200s to the 101200. Note that this checking account, this is another way that we have in essence and
increase kind of like a deposit without
using a deposit form. And we went over the
deposits last time. We didn't look at
this particular form, which has also could possibly
be a form that increases the checking account
because we had too much to do without it. But this is another thing
that you might see when you look at the
transaction detail as in essence an increase to the checking account
without it being a deposit form because
we use this form to go directly into the
checking account, then the other side's
going to go to sales. That's gonna be our first
income statement account that GO is in the same order
as the TB trial balance. We've got the assets
in green liabilities and orange, dark blue equity. The sales over here
in I95 were on 615. Now we are in a J5, age A5 equals holding left arrow down until
we hit the wall, foam hitting the wall. And then we're gonna
be getting that 1200 taken it from 200
thousand up by 1202001221, two hundred two hundred two
hundred also then of course, being on the trial balance back in balance with regards
to the general ledger. Now let's do another
one here and just imagine the same situation. But now we're gonna put it
into an deposited funds. Now, you would be
backing up or having the same kind of
transaction you're imagining yourself
at the register, but the system, instead of putting it into the
checking account, puts it into and
deposited funds. If you were to have cash
sales, for example, that you have
throughout the day, it would be useful
to put them into an deposited funds because
at the end of the day, you'd be taking that
information and then deposited into the bank in one group deposit and want that one group deposit to be reflected on our books
so we can match, It's gonna look like
on the bank statement. So in practice, you
might see it this way. This is something
often not seen when you do normal kind
of accounting in a school setting
because they don't typically break out the
different kinds of cash. And think about what
you need to do to make that deposit happen. It's usually just called cash. We're gonna say, all
right, we're going to say, let's make this on, let's make this on 630. And this, we're just
gonna do the same thing, but now we're gonna say
it's going to go into and deposited funds the other side then go into
the sales line item. And let's make this. Let's make this then the, let's make it 900 for this one
just to make it different. And then we'll indent
this so same transaction. But now we're putting it into and deposited funds representing the fact that we're
holding on in essence to the cash in H5, we're gonna say equals
point to that 900. The other side go into sales. So something's in
sales down here, double-clicking on it,
go into the end of it. Plus we're gonna
pick up that 900. So now we've got the two items go into sales for
this time period, bringing it up to o to 100. Let's look at the detailed
in the general ledger first, looking at that and deposited funds second account to the
right and deposited funds on seven what do we say? 630. 630. This went up equals
on over to the left to the wall, $900 increasing. This is a temporary not
only a temporary account, this is like a clearing account
that we expect to go down immediately after 900 out of balance on the trial balance, we're matching out to what's
on the trial balance, 900 out of balance on the
general ledger, I should say, sales then second half sales
is way down here again, in order on the geo assets, then liabilities, then
equity than sales. So in AI six, we have 630. We are now in AJ six equals left arrow till we
hit the wall. Boom. And then we've got that
900 increase in D6. We've got the 200 thousand
went up by 1200 to 2012, up by 900 to 202100. Notice sales always goes
up until we close it out in the closing process generally that
generally speaking. And then next, so that's that's gonna be the two ways
that we can do it now next, we'll do the same thing, the same kind of
sales item down here, sales receipt, but
now with inventory. With inventory, if the data
input is done properly, the transaction is
quite easy to set up because the items
are doing the work. But the journal entry behind it is actually a little
bit more complex. So let's take a look at it now. We'll break it out
into 22 halves. Will say, Okay, one, I know the checking account
is going to go up this time. I'm going to put it into
the checking account again instead of into
and deposited funds. So checking account Let's put just checking account goes up. The other side. We're going to have sales tax. So I'm just gonna say I know sales tax is gonna be an issue. And then there's
the sales price. And the sales price is gonna be going into the sales amount. So first let's think
about the sales price. To think about the sales price, I might use my cost to
kind of figure it out. If I went to my subledger, I'm going to go all the
way to the right to the inventory subledger
that we have. Here's the two items
that we have here. And we're gonna say that the
cost is we got 5050 units. We're going to sell
this unit, a unit. The unit cost is 50. And we're going to assume
here that we're going to sit, let's say we're going
to sell 20 of them. I'm gonna figure out, I'm
gonna say that there's a markup of 80%. So what I'm gonna do
is I'm gonna say, okay, let's go all
the way to the right. We're gonna say the sales,
I'm gonna put it negative. Now I'm gonna say negative because it's gonna
be a credit and I'm gonna take 20 times, let's actually say we
sold 40 of them times 50. So we're going to
sell 40 times 50. That means the cost is
gonna be the 200 thousand. But let's, let's assumed that the cost is gonna be
80% of the sales price. So what I'm gonna
do is take that, then I'm gonna take
that whole thing and divide it by 0.8. And that's going to give us
our 2500 just to give us an example to differentiate
the cost and the sales price. So in other words, if
I took the sales price 2500 times 0.8, we get the cost of
the 2 thousand, just to give us an idea
of the difference. Remember that when
you're looking at the subsidiary ledger, you're looking at the cost
because you're tracking what we purchased the
inventory for over here. And the sales price is gonna
be something above that, which you'd have to
figure out whatever, whatever your markup or
however your theory is to figure out what the sales
price is and apply that to it. Once we know what
the sales price is, which would be driven on the actual sales
receipt by the invoice, then we can figure out the sales tax that
would be applied to it. Let's say the sales tax
for our example purposes, just 5% of the sales price. I'm gonna say, all right,
this is going to be equal to the 2500 times 0.055%. We're charging 2500. The state or whatever the local government is
making us charge another 125, 5% on top of that
to the customer. Therefore, the amount that
we're going to receive after taxes is going to
be the sum of those two. But a debit, I call
this the plug function equals the negative
some of these cells, which is added those two up, flip the sign to six to five. There's the 260 to five. We're going to indent this. And here we have it. Notice, you might
be saying, well, why don't I just increase
basically revenue by the 260 to five and then have an expense
related to the sales tax. And again, the reason
we're not gonna do that is because
this amount right here that we're
collecting as part of the 2625 is supposedly
attacks on the customer. It's not a tax on
us to business. They're just making us
be their collection arm for the state or the
local government, whoever is making us do that. Then we've got the
cost of goods sold. Cost of goods sold that we're
going to have to deal with. I'll make this a
separate journal entry, even though it's happening at the same time as we
create the sales receipt. It's not actually on
the sales receipt. But these items no, through the items being set up, what the cost is in our case. Similarly, it's being pulled
from the subsidiary ledger. There's the inventory. And we saw the calculation
when we went to the subledger, it was equal to we're selling 40 units and they cost us 50. So that's that 2 thousand. So there's that 2 thousand. So there's our full
kind of transaction. Let's go ahead and post it now. Starting with this top half, I'm gonna make it
green to focus in on it's specifically checking account is gonna be going up. So there's something in
the checking account here. So I'm gonna say F2 on the
keyboard to go into it, or you could double-click
on it plus F2, then scroll on down
to that 201625, increasing it up to
that one hundred three hundred twenty-five
sales tax now, liability account
because we're going to owe it to the state
in the future, equals that one to five. That increases to one to five. Then the sales or
revenue account is going up just by the amount
that we actually charged, not by the sales tax, F2. F2. Or you could double-click
on it, adding the 2500. And there we have it. So we have revenue increasing, increasing in this case
by that 2500 were back in balance even though
we haven't recorded the rest of the amount that would be on the sales receipt. Yet, this top half
kind of would be seed on the sales receipt because that's what we want to
show with the customer. In essence, this bottom half, not because we don't
want to show the costs, but we do want the sales
receipt to record the cost. Now let's record it to
the general ledger. We're going to bring this
on over to the GAO were in the checking account on 715 in L L6 equals the 260 to five, increasing the
one-on-one to buy the 262523825 that matches the TB. We're out of balance on the G, L. Now we're looking on
the sales tax payable, that's our last
liability accounts, same order on the GL
assets, liabilities. And then we're
looking at that last liability there it is. We're in APA 7th. This is on 715. We are in Cell AB 17 equals holding the left
arrow till we hit the wall, Boom, wall has been hit. And then we're picking up
that one to 51 to five, increasing it to one-to-five. That one-to-five also should be then on the trial
balance, the good old TB. There it is. Then we have the sales item that then is our first income
statement accounts. So the GL is in the
same order assets and green liabilities and
orange equity dark blue, there's the sales, there's
the revenue account. We are in AI seven on 715. We're now on age, age seven equals left. We're getting a head
of steam up and then we're gonna hit the wall. And now we're going
to take that 2500. And so there we have
it. So we've been increasing revenue
notice revenue only goes up in the credit
direction until we close it out unless there's
some kind of weird exception. Two hundred, four hundred,
six hundred now in revenue. That's what's also on the
trial balance right there. And we're back in balance, but we still need to record the other half because now
inventory is affected. Inventory went down by the
dollar amount of 2 thousand. The other side,
cost of goods sold, which is an expense account, special expense
account relating to the inventory that we
have now consumed. Let's record the cost
of goods sold here. There we have it equals the
160 plus the 2 thousand. So now we have an increase
of 160, the 2162. That means the net impact on the income statement is the
net of these two items. So we had an
increase of the 2500 and then we had an
increase in the expense of 2 thousand, the net increase, then it's really only
the 500 difference between the two on the income statement
down here on net income from that
particular transaction. We obviously have this
other two transactions that are involved are three as well, two other ones, three total. Then the inventory
is gonna be up top. Here's the inventory. It's gonna go down
by the 2 thousand, down by the 2 thousand
with a credit, it goes from 4,375 down by the 2 thousand to two hundred
three hundred seventy five. Let's go ahead and post it. I'm going to ungroup
and Phi this one. This one doesn't need
to be gratified. That's not what
we're on anymore. Now we're gonna post
the cost of goods sold. That's the second income
statement accounts. So I'm gonna go to the
right. Assets in green and liabilities and orange,
equity dark blue. And then the GLM income
statement down in AI 17, we're looking 715
transaction date AJ 17 equals holding the
left arrow down wall, Hit going up top looking
at that to 2 thousand, there we have it going
from 160 thousand up by 2 thousand
to 162 thousand. That 162 thousand
also on the TB, the GL off by 2 thousand till
we record the inventory, which is the middle or
fourth asset account. Looking over here, there
it is, right in F5. F5 on 715 were in T5 now equals left to the wall down
to the last transaction, we're looking 2
thousand decreases with the credit for 375 down 2002 to 375 GL backend balanced
trial balance in balance. The inventory account matching the GL Account on
the TB GLP-2 375. But we also have the subsidiary ledger because I need to know the quantity
of inventory as well. It's not enough just to
know the dollar amount. Which inventory
units did we sell? Can I count those
inventory units? Can I recalculate this for that? Let's go to the
subsidiary ledger, which in the software might
be called something like a Inventory summary report
or something like that. Looks a little bit different
than the other sub ledgers, but we got the item one, inventory item
we're selling we're selling inventory item two. Then we're going to
say that on this date, we said on 7157015, we purchased know,
we sold we sold 40 units and they cost us $50 because we're
selling these units $50. That's where we came
up with the 40 times 50 or 2 thousand of the cost. I'm gonna pull that into the Indian inventory
to figure out the Indian inventory
and that kind of mirrors where we got the cost
of goods sold number from. Then we're gonna
say that the units now we're pulling 40 units sold. The unit costs I'm
gonna calculate after, and this is going to be
equal to the 2 thousand. I'm going to underline here. So if I calculate this, I was at 50, we sold 40. We only have ten of these particular
inventory units left. And we were at $2500 amount. Now we're down by
2 thousand to 500. The unit cost is equal to
500 divided by ten or 50. Now you might be saying, why in the world didn't I
just didn't I just, and I could put the 50 here as well and calculate this
would be 40 times 50. Why didn't I just bring
down the 50 here? Because we're using some
kind of flow assumption. You might use FIFO, you might use lifo, you might use specific
identification. You might use average, weighted average is what the desktop version
generally uses. So in other words, if if this first unit, if there were different
costs of the unit, we'd have to figure out what the average cost would be
at the end of the day. It's the same right now
because we happen to have the same cost throughout. We haven't had a
change in the cost. So it's a straightforward
calculation. I won't get into that in detail. But to understand these fully, you got to understand
the inventory cost flows depending on what kind of software you're using and so on. And as you get more complex
in terms of inventory, those kind of questions
can get more complex. But in essence, what we're
looking at is the fact that we want a subledger That's going
to give us the total units, adjusting the units
to the proper number of units that we
currently have on hand, which is ten at
this point in time. And give us the dollar amount
that we could tie out to, which is gonna be the 500 of these first Inventory
types and the 1875 for the second
Inventory types. And that then 2375 should match the trial balance
given by the green 0 here. Let's double-check it to 375. All the way back to the TB. There's the 2375 here. Now, once you have
this kind of setup, then of course we
can look at this and create the balance sheet. Normally when you enter this and the income statement
into software, you will enter the data
and then you'll bounce on over to the balance sheet and the income statement and
you'll check your numbers. You can say, Okay, the
checking account was affected and deposited
funds with effected. And then we also have
the other side go and say to the revenue
account was impacted. You can drill down
on those accounts, drill down on the
cost of goods sold. Once you drill down, Let's say we drill
down, for example, on the inventory, then you can double-click on it
or something like that. That'll take you closer
to the source document, taken you to a transaction
detailed report, otherwise known as a
form of general ledger. The general ledger account
would look something like this inventory account given
you the activity by date. And that activity would
be including increases from from if it's inventory, it would be decreases
from things like the sales receipts or the invoices and increases when we purchase the inventory. And you're gonna say,
okay, that looks good, but I'd also like to see
it by inventory type. What kind of inventory
types we have left and the inventory
count for that, you'd run a different
report which would be something like a
subsidiary ledger, possibly called an
inventory summary, inventory balance report
or something like that, which would be similar
to this type of report that will give you a subsidiary type of report
because the total of it should tie out to what's on the general ledger
or parent ledger, the GL, and on the
balance sheet.
10. Credit Memo Refund Form & Bad Debt Expense Service Item: Excel accounting practice
problem, Credit Memo, Refund form and bad debt
expense with a service item. Get ready because we're
about to excel with Excel. Here we are in our
Excel worksheet. If you have access to
the Excel worksheet, would like to follow
along two tabs down below, example
and practice. Let's take a look
at the example tab, in essence, being an answer key. In prior presentations, we put this worksheet together
from a blank sheet. Now we're entering transactions or journal entries that would be supporting a credit memo
type of documentation, credit minimum being issued
in the event that we're basically negating or reversing a sale that happened
in the past, either through an invoice
type of transaction or sales receipts
type of transaction, either then providing money
back or in the event that we had not been yet paid and
have an accounts receivable, then reversing the
accounts receivable. So scrolling down, this is going to be the invoice type of form. If you're looking at basically
accounting software, initially, we're thinking about an invoice taking place
or a sales receipt. The invoice meaning
that accounts receivable has then
going up the other side, recording to sales as we've
seen in prior presentations. Now in the event that we, for whatever reason
need to reverse the invoice without
receiving payments on it, then we may go through
the credit memo type of type of form that would
reverse the invoice. In essence, if we were to
reverse the invoice exactly. Then basically, if the invoice was increased in the
accounts receivable, the other side go into revenue. We would decrease the accounts
receivable and we would basically reduce or have a
negative revenue amount, which is one area that we
could then adjust possibly to a bad debt expense or
returns and allowances, which we'll talk about in
future in this presentation. We also might have a situation where inventory was involved. If inventory was involved, then we might have the return of the inventory at that point in time that would
initiate the credit memo. We'd have to deal
with the inventory. We'll think about that in
a future presentation. Here we're just thinking
about a service item that we're basically going
to reversing it out. And then we also could
have a situation where the customer already
paid the accounts receivable or they had a sales receipt transaction paying at the time
the service was done. At that case, if we have to
reverse the transaction, we would have to
actually give them money back or get some kind of store credit or something like that in order to reverse
the transaction. So there's a couple
of different variants we have with this credit memo. Credit memos are often one of the more complex types of
transactions to understand. And that's because you're
basically reversing. It's not something we
do every day and we're reversing a normal transaction. So it's kind of unnatural
when you think about the debits and credits related to it because they're
all backwards. Because if you have the
inventory involved, then it's going to add a level below level of
confusion to deal with that inventory will think about inventory in the
following presentation. Let's go to the
practice tab and let's first think about the transaction
related to an invoice. We're gonna record an
invoice transaction not including inventory
to start off with. Let's start it off on 315. We're gonna say that accounts
receivable is going up. So we got a are going up the other side then
go into revenue. We will say sales
on, down below. We're gonna say
the amount is for 1 sixth zeros, 016, zeros, 0. There's the debit,
there's the credit. I'm going to indent the sales. We've seen this in the past. I'm gonna do it a little
bit more quickly. We've got the
accounts receivable. We're gonna record that
in the AR account, the accounts
receivable H spot H5, H6 equals that item. So it goes up from 2
thousand up to 3,600 sales. Then down here on the credit, it is a credit balance account. It's gonna go up
with a credit of 1600 to a one
hundred six hundred. We see that also
increases the net income, the brackets
representing credits. It is net income not a net loss. Then we'll record this to the GL accounts
receivable go into the GL general ledger
right here in 017. We're going to
record this as what did we say 315, I
believe it was. And we're gonna say
this equals then in P7 equals we're going to be increasing it
by the one thousand, six hundred, two thousand
increased by the 1600s, 3,006. That matches what's on
the trial balance or GL dow out of balance. I'm going to record the
other side to sales, which is our first
income account, same order in the GL assets then liabilities in orange or
yellow or whatever that is, dark blue for the equity. Here's our sales
amount and AI I6, this is going to be as a 315. We're in a J5. We're gonna say this
equals hold down the left arrow till
we hit that wall. And then we'll scroll
up to that 1600, increasing it to two hundred
and one hundred and six that should match what's
on the TB trial balance. It sure does. We're back in balance
up top with the G L. Now we need to record it as well to the subsidiary ledger. For the accounts receivable, which is way over to
the right here after the GL ordered by the customers. This happened on 315 and sell BD 17 equals holding
the left arrow down, getting a head of steam up
and boom hits the wall. The whole Excel sheet
shook for a second there. I don't know. I'm not sure if you saw it, but it shook there. We have it the 1
sixth and then R for customers add
up to that 3,600, which ties out to the trial
balance given by the green 0. If we go over to
the trial balance, then there's the 3,600. Now, we're going to have the situation where
we reverse it. The first thing we
can think about, if I was to reverse
a say an invoice, I was take this invoice,
reverse it exactly. We don't have the same numbers, but if I reversed it exactly, it would reverse
exactly the transaction that I put into place. So that's what we'll start off with and I'll
tell you what kind of a problem is without which
we may then what to adjust. So we're gonna say, okay, credit memo three will
say this happened on 320. We issue the credit memo. What's that gonna do
with a transaction? Well, it's going
to then say that the sales is going to be
reversed and go down. And then the other side is going to be accounts receivable. And it's going to go down. Now for not gonna get a payment, the accounts receivable
makes sense that it's going to go down even
though we didn't get paid. Reversing sales is often the
point where you're like ad that's true because we made a sale that
didn't really exist, so we should reverse it out. But oftentimes we
only make sales go up in the credit direction. So we might then say, and we'll do this on another transaction that I
don't want it to go to sales, maybe I would rather have
it go say to returns and allowances if it was like an inventory return or
possibly too bad debt. If for whatever reason we
did whatever service we do, bookkeeping or
something, the client was not satisfied with the
work or whatever oral state. We just couldn't find him.
They're not going to pay us and we're not gonna get paid, then we might put it
to bad debt expense. Those are our two
kind of options. We can either reverse the sales exactly or we could put it to sales returns
and allowances or possibly bad debt expense
with a service item, more likely to be bad debt
expense in our situation here. So I'm going to indent
this now we'll record it this way and then
we'll see another, another option. Next time. We're going to say now we'll
just reverse it exactly. So we'll say the
sales is down here. I'm gonna put F2 in
the keyboard plus, and then F2 again. I can then pick up
that sales item goes back down to 0, no change. We're now at the 200 thousand. Realize that this
reversal could happen another year in the
future because we might determine we can't collect on it at some point in the future, for example, then we have
this timing difference making the sale in the
past that we had to negate or reverse in the future. And that timing difference
can be kind of an issue. And it's why we might want to break it out into
another account to represent the fact that it's bad debt expense or
something like that, which will do the
following transaction. And then over here
we're gonna say accounts receivable plus F2. Taken that down, so it goes
back down to where it was. Let's record this on the GL. There's the sales
account, it's down here. It's the first account on
the income statements. Same ordering in
the general ledger, assets in green, liabilities, yellow or orange,
equity dark blue, There's our sales, and here's where we have
that funny transaction. It's unnatural. It's unnatural that
sales goes down. So we're in a J6 and I'm
going to scroll over here. And I might, I might be
tempted to pick up the credit because I feel like sales only goes up and the
credit direction, but no, it's going down. That's weird, That
shouldn't happen. Sales doesn't go down. That's why we might use some other account and
we'll do that next time. But here we're
going to reverse it back down to the 200 thousand. Scrolling back over. There, we have it. And then the other
side is going to go to the accounts receivable. Accounts receivable over here. That's gonna be
our third account. Accounts receivable. It's in, it's in 320, were in cell ph equals
left to the wall. Then we're going to go
up to that 16 credit. It brings it back
down to where it was. We also need to record that in the subsidiary ledger
for accounts receivable, which is way over to the right. Looking for the
subsidiary ledger. Here we have it on we're going to say
this happened on 320, were in cell B2 equals
left to the wall. And then we're going to go up to that accounts receivable, 1006. So we brought it back
down, we increased it. Then we brought it back
down to 0, up top, we're back at the 2
thousand ties out to the TB trial balance given
by the green zeros there. The general ledger looks
like it's tied out. There's the 2 thousand back
on the accounts receivable. Let's do it again. This time. We're going to switch
it up a little bit with that second transaction
now recording it to bad debt expense will say, okay, at the same starting
point, we'll say 425. We'll pretend we're
entering an invoice, which would be this
type of form here, and then we'll credit memo it, reversing it without receiving
payment from the client. So we're going to
sit in and out. Accounts receivable
is going up again. And sales is the
other side this time, let's say it's for $2000.2000
debit and a credit. Let's do an indentation
of the sales item. This accounts receivable
is gonna go right there. We're going to
double-click on it, post it to the TB trial balance 2 thousand goes up to 4
thousand sales down here, the zeros in it, I don't want
to just delete the zeros. I'm gonna say F2 on
the keyboard plus F2. So I can use my arrows, scrolling over to
that 2 thousand, that increases the sales. So sales increased even though
we didn't get money and we're not gonna get the money because they're
not gonna pay us. I'll tell you how
the story ends. We don't get our money. Accounts receivable then
is going to be over here. In accounts receivable. This happened on 425. This is gonna be equal to we'll bring that arm back
over to the two thousand. Two thousand, that's
up to 4 thousand matching what's on
the TB trial balance, GL out of balance, tilde, record the sales revenue. First income statement accounts. So we'll find that all the
way to the right assets, then liabilities, then equity. There's our income account doing the natural thing
that it should do. It goes up the credit
direction in AJ seven equals left
to the wall, wall. And we'll pick up that 2
thousand. There it is. So now it's back
up to 2200 again. And so then we have that
and we need to record it to the AR subledger to scrolling all the way to the right to get
to the subledger. Let's say this happened
for customer one. This is going to be on,
we're gonna say 425. Customer one, y5 equals
left to the wall, all the way over there. Pick it up that 2
thousand and enter. So there we have it,
bringing it up to 3 thousand total receivables for the four customers at 4 thousand balances out to the trial balance
given by the green 0. Going over to the TB, There's
the 4 thousand we see. Alright, now we're going
to reverse it out. Same kind of thing. We issued kind of
like an invoice here, transaction related
to an invoice, now the credit memo, but this is a tricky we
were getting tricky here. I'm gonna make this
a little smaller so it fits on the screen. And this is something that
if I reversed it exactly, you'll see that it would
reverse to that income number. We can play a little game with the items and we'll show you how to do
this in QuickBooks. But to get it to basically
reverse out too bad debt, we had to add another
item here that's going to bad debt in order to kind
of reverse that out. So we won't go over
that in detail here, but just note that it
can be a little bit more tricky and the software to kind of figure out
what's going on, because you've got
to use these items to figure out exactly what's going to be recorded
as you use the forms, like the credit form. So just like with the
inventory and the invoices, the items are kind of
driving the transaction so that it gets a
little bit more tricky to do that if
it's a journal entry. Of course we have
complete control to do whatever we want
to do and we want to, we want to put this
to bad debt expense. Now we're going to reverse it. Let's say 430. Instead of reversing into sales, I'm going to reverse it
to bad debt expense. Someone's going to call
it bad debt expense of the 2 thousand. The other side go into
accounts receivable. So accounts
receivable goes down. That makes sense. But now we're going to say
accounts receivable goes down, but we didn't get paid either because the person got lost. We can't find them, we
can't collect on it or possibly they're upset. They just say they're
not gonna pay us and we don't think it's worth going into collections
over and so on. Therefore, we'll
just write it off. But instead of reversing the
sale to a negative sale, which is unnatural, especially if it's in
a different period because we'd like to see the bad debt in a
separate account. We're gonna post
it too bad debt. So to do that, I'm
going to add an account because we didn't start
off with bad debt in art. Excel worksheet. We will practice some Excel
skills here, excel skills. And we're gonna practice
putting bad debt. I'm gonna put it right
at the bottom here so I could put it down below. And you might ask, why
would I put it down? As long as it's somewhere
in the expense area, that would be okay. And then you can determine which accounts you want to be up or lower based on what you think the preferences best should be. But I'm gonna put it
down here at the bottom. I'm going to select
from here to here. I want to put something above
it, right above utilities. So I'm gonna right-click on those cells and we're
gonna say Insert. And I'd like to select
these and copy them down, meaning move everything
underneath it, making room for the
sales above it. Okay. There we have it. And I'm just gonna type
in bad debt expense. Then I'm going to put a
0 at the starting point. It populates the
ending point for us, which is simply summing up. That looks correct. There we have it. Let's move that down a bit. Okay, so now we can post it out. We're going to have
to add that to the GL as well, by the way, is gonna be probably more
difficult, but not too bad. We can do it. We can do it. Pretty sure. We have the talent,
we've got the skills, we've got the determination
and the will. So there's the 2 thousand goes up from 1102 thousand
to 2 thousand, we're out of balance now
brings down the net income, but it doesn't
reverse the sales. Same net effect on net income as the other method
if we reversed sales, but sales doesn't go down. Sales typically only goes
up and we get to see that activity happening
in another account. Then we're gonna go to
the accounts receivable up top and that's gonna
be reversed out to, of course, F2 plus F2. We're going to pick
up that 2 thousand. That puts us then
back in balance, bringing us back down to
the original 2 thousand. Then we'll record this out. I got to record that
bad debt expense. I don't have one. So I'm gonna go to
the expense like right before the utilities, I want to try to add
another blue account. We're going to go way over here. And it's like, I want another
blue account like that. You should be
utilities right there. I need some room to the right. I need some room. I need, I need like 1234 cells. So I'm gonna go put my cursor on that
skinny one right there. If I can't and
won't let me 1234, right-click, and
then I'm going to insert and move
those to the right. It tries to copy the
formatting to the left, but I don't want it to
copy that format in. So I'm gonna say Clear that formatting because I'm
just going to copy this whole thing over
right there from the skinny column
on Over Control C, tasting that right here
and Control V. There, we have it. There, we have it. And then what I would like
to do is swap out these two. I want, I want this
utilities to be over here. I'm gonna say let me
just, I'm gonna cut it. I'm gonna take that and
I'm gonna say cut that, which is the same as just moving it and I'm gonna
paste it right there. Paste it right there. Then I'm going to move this one. Cut that right-click and cut, and put that one right there. Now you may not have to do this because it'll be in
your example tab. But just to show you how
you can kind of move this stuff around
if you need to. In the event that
you need to do that, then I'm gonna go up top. I'm gonna say that
this one right here, it's going to be
equal to the name, which is bad debt expense, which is going to
be way over here. Bad debt expense, bad deck. I only liked good debt. I've never, I don't know what
good debt is but whatever. So now we're an AV 17
equals left to the wall. We're gonna pick up
bad debt. There it is. And then we're also
going to have to add that bad debt to our, our items up top and the GL. So I'm gonna double-click
on this one. That bad debt, that
new thing that we added isn't in the total. So right there everything
else's but that one isn't. So I'm gonna go to the end
of it and say plus that 2 thousand total down
there and enter. And now we're out of
balance by that 2 thousand until we'd record
the other side. Accounts receivable. There it is accounts receivable. This is going to be down here in cell, what did we call it? We called it this one for
accounts receivable is on for 30 in P20 equals
left to the wall. Picking up that 2 thousand
there we have it going back 2 thousand matching
what's on trial balance, and we're back on
green zeros up top. This second one also
is not including, or this number is not
including the bad debt too. So I gotta go over to the bad
debt on that one and say, okay, bad debt is not
included there as well. Go to the end of it and
plus that 2 thousand. Okay, So now we're tired out. Everything looks good,
everything looks good. Then we got to go to
the subsidiary ledger and put the decrease in
the accounts receivable. That's all the way to the right. All the way to the right. And the subsidiary
ledger here on BC E6, we're going to say this
happened on for 30. And I'm gonna say this
equals all the way to the left till we hit the wall. And then we're
gonna say this one, accounts receivable 2
thousand goes back down. It goes up, and then it went back down to
the one hundred, ten hundred that was
originally there. Adding up the four
customers comes out to 2 thousand matches
the trial balance given by the green, 0. Back to the TB. Is that the case? 2 thousand
right there, there it is. Let's do it one more time. This time we're gonna say that you can think
of it as if we already got paid by the customer and then we got
to give them money back. And or in the event
that we issue a sales receipt
instead of an invoice, the form which indicates
at the point we do work, we get paid at that
same point in time. If the customer then comes
back and we have to give them their money back or have
a credit memo situation, we'd have to give
them their money back or to issue some kind of credit for store purchase in the future or
something like that. We're gonna say this is on 515. And let's start this out again. And let's say this time, we don't issue an invoice, but we issue a sales receipt, we get paid at the same
point in time the work was done for a service item. So that would be a
checking account goes up. We could put it to
an deposited funds, but I'm gonna go directly
to the checking account. And then we're gonna say the
other side goes to sales, will say this is for $800. For the purposes of
the example purposes. Then I'm going to
indent this one. So let's post it out. We're gonna say
checking account. Checking account goes up by
$800 with the debit from 100 thousand plus 800 to 108 thousand sales on
down below an age 15, F2, F2 goes up by the 800. So we're increasing the sales straightforward
sales-type transaction. Let's record this to the GL, the general ledger cash
first first account, easiest 1515 and cell K5, then an L and L5 equals
sounds like a Tolkien. L5. It was, I don't know
what I'm talking about here. We're gonna say, there it is, 100 thousand plus 800 to 108 matching up what's
on the trial balance. Then we're gonna go
to the sales item, which is the first income
statement accounts. So we're gonna go all the
way to the right assets, liabilities, equity. And then here is our sales. It then increasing
on 515 and sell AJ equals left till you
hit the wall, boom. And then we're gonna go
down to that sales item increasing with the 800. That's what naturally
happens with sales that only goes up
into credit direction. By the 802, the 202, It's eight. Then let's go on
back to the left. Then if we issue the credit memo and let's
say let's say at this point, we've got we've got
to reverse this, but we already got paid and for whatever reason
we have to give them money back at that point in
time or something like that. So we're gonna say,
all right, well now I can't reverse
accounts receivable. The credit memo in essence then and this would
be this document in accounting
software would then indicate whether
you want to enter. It will probably ask you, Do you want us to apply
it to an invoice, meaning accounts receivable
still outstanding? Or do you want us to
actually issue a credit, a payment of some form or
some kind of store credit. So we're gonna say that
we're gonna pay it back. And this time I'm
going to reverse the sale entirely again, because this is kind of
a reversal of the sale. In essence, we've got
that unnatural thing with a reversal of the sale. You might have bad debt instead. You might have sales returns
and allowances instead. But I'm going to
reverse it exactly. The other side, go into
the checking account because we're gonna
have to pay it back out with a check or
some other kind of payment transfer or
something like that, something that decreases
the checking account. In other words, we'll indent
the checking account, posting it out pretty
straightforward, easy transaction once there, although it's unnatural
on the sale side because sales is going
down with a debit. That doesn't happen. Like almost never, sales should only go
up in the creditor. Many case 800 checking account on the credit
side of things, double-clicking that
go into the end of it, plus picking the
credit up of the 800, posted it now to the GL, sales on the G L is going to be the first income
statement account. We're gonna go all the way to
the left until we find it. There it is with all the
activity, all the action. Sales is where the action is. That's where I want to be because that's where
stuff's going on. We're in AJ nine equals
left to the wall, picking up then that credit
of 800 into sales and Enter. There we are at the two hundred and three
hundred six hundred that two hundred and three
hundred six hundred matching what is on and it doesn't
match what is on. And that's because I picked
up the credit notice. I did that on purpose
because it's unnatural. So that's what you'll
do oftentimes. But now let's fix it.
I'm gonna fix it now. It should be a debit. We shouldn't be
picking up a debit. So I'm gonna delete AJ dime equals all the way to the left. This is the funny one. It's the 800 debit to the sales. Debit to the sales. There it is. Okay. That makes sense. So now we're back down to
the two O2. That to O2. Tie out to the trial balance. There. Now at ties out,
we're out of balance by the 800s. So
that looks right. Checking account is
gonna go down by that 800 will be over
here in this cell, we're going to say
520 were in L, six equals checking account. Then going down, you're going down checking account by $800, went from 100,800 down by
the 800 to 100 thousand, we'd have to pay it back. So that would be, that
would be the general idea. Now will deal with
inventory next time and add that other level of
complication with regards to the inventory. But now let's take a look at the balance sheet because we're gonna have to
do something to it and the income statement because we need to
add this account to the income statement or else we're gonna
have a problem. I'm gonna try to put the
income statement right next to the financials, right next to our trial balance. So we can analyze it and
add what we need to do. For Excel formatting purposes, hiding these cells, I'm putting my cursor
on the skinny J, skinny j all the way on over
to the financial statements. So we'll put our cb, let go, right-click the
selected area and hide it. So it's hidden. Now we're gonna go over
to our income statement. We need another account
right there right above the utilities that is
going to be bad debt. So I'm going to select these
cells, right-click on it, insert, shift them
down, shift them down. And then we'll just put
our bad debt right there. That's where the bad debt goes. Put bad debt in its place. We're gonna put the bad
debt right in its place. It's right between
telephone and utilities. That's what you'd get
bad debt for being bad. Gonna be here right in the
two thousand, two thousand. Then that would add
up summing up here. Net income 24710 should tie out to the net
income down here, 24710, balance sheet
back in balance. So I think that's good. Then if we did this in practice, of course we'd issue
the credit memo, then we would drill down
on the impact of it, possibly looking at say, the accounts receivable
unhide in these cells from CD to AI or AI to CD,
right-click unhide. If we drill down
on it in software, say the accounts receivable, we would then find the general
ledger type of report, which would be like a
transaction detail report given us the activity
happening here. And that might be good. But then we'd say, Hey, I went to know about
this one person that shouldn't always
any money anymore because we've reversed
it with a credit memo. For that, you might run
another report which would be like a
subsidiary report, which would look
something like this, breaking stuff out or
the information and accounts receivable
out by customer. And then you'd see these
items that you could see the credit memos
that would then be applicable to the proper
customer and seeing what they're balanced might be that might be running with
an accounts receivable, aging, accounts
receivable detail or summary type reports.
11. Credit Memo with Inventory: Excel accounting
practice problem, credit memo with
inventory transaction. Get ready because we're
about to excel with it. So here we are in
our Excel worksheet. If you have access to
the Excel worksheet, would like to follow
along two tabs on down below example and practice example tab in
essence being an answer key. In prior presentations, we put this worksheet together
from my blank sheet, which is a good
exercise that you want to go through that process. This time, we're using this sheet to record
the transaction related to a credit memo
involving inventory. We're gonna look at
the forms on down below first will
enter the invoice, then we'll take a look at
the credit memo in terms of the transactions that
in essence would be supporting the forms. So the invoice form, as we've seen in
prior presentations, will increase the accounts
receivable by the invoice, the other side then increasing
sales by the amount that we charge the difference
go into sales tax. If we have sales tax, which we're likely to have
in the United States, if we're selling inventory, then we're also going to have
the inventory going down by an amount that is the sales tax payable will go
up as well by the 775. Then we'll also have
the inventory going down by an amount that's not on the invoice but driven
by the item and the invoice other side then going to the cost of goods sold, then if we think about a
return of the inventory, we would have a credit
memo type of situation which in essence would
reverse the transaction. However, possibly we would want to make one little
tweak to that, not reversing the
sales line item, but instead putting it
to another account. We're gonna say this time
sales returns and allowances. That could be a little
bit of a twist, twist or tricky situation with the use of the items
in the credit memo, which you can get more detailed on looking at a
QuickBooks course on how to do that with
these inventory items. But that's gonna be the general
idea and we're gonna look at that from a journal entry
standpoint at this point. So first we're gonna
imagine we put the invoice on the books and voice
on the books first. And so we're gonna
do this a little bit more quickly because we've
seen this in the past, that transaction related to it, we're selling
inventory on account, have not yet received the
cash and voice in essence, going out on 415, we're going to say accounts
receivable would go up. We'd also are going to
have the sales tax payable that's going to be involved
in this transaction, which is gonna be here,
sales tax payable. And then we're going to have then the sales price down below as well,
which is the 200. Hold on a second sales dollar
amount or sales account. Here, sales account. First, I want to think
about what we charge. This would be of course
be determined by the sales item on the invoice. I'm going to try to
pull this information from our subledger, noting that the subledger
generally attracts the cost, which we could use to then
calculate the sales price. In QuickBooks or some
kind of software, it will typically
have the sales price and generated or that would be input as part of the process for the items that
would be used. So I'm gonna say equals and
go all the way to the right. And I'm looking at this
subledger for inventory. I'm assuming we're selling
inventory item two. And I'm going to take just the
cost which is 25 times 15. I'm assuming we sell
15 units of it. So that would give us then
the cost of the 7375. Then I'm going to mark it up and I'm going to assume
we mark it up 30%. I'm going to go back in
there and say I'm gonna take that amount and then
multiply it times 1.3130%. Multiply that times 1.3, and that'll give us our 488. Then I want to make
this a negative number. I'm going to double-click
on it again, just put a negative in front
of one of these items. And so we've got
the negative 488. I'm going to assume
that's the sales price. We're gonna have to
pay sales tax on it. And that's going to be
determined by the state. I'm gonna say it's 8% here. So I'm gonna say this
equals the 488 times 0.08%. We're gonna have to pay
another thirty-nine dollars. That means that
we're gonna charge the client or the customer. The 527, which
I'll put here with a negative sum or plug formula, as I call it, negative SUM
of these items down below. That'll give us the 527, the sum of these adding up
to 0 or in other words, debits equaling the credits. Then we'll pick up the
amount for the decrease in the inventory and the cost of the goods that were selling. Cost of goods sold on the debit side because
it is in essence and expense inventory going
down on the credit side. Same calculation for
the cost of goods sold. Pulling that in
from our subledger. Way over here, I'm just going to pick up and you
could type this in. You don't have to do
the formula like this, but I'm just trying to tie
out where it's coming from. 25 times 1515 units
we sold at $25. Then I'm gonna say that's
the debit and credit here. Let's do some indentation
here and here, alignment and dent and then
indent the credit down below. There's our transaction
related to the invoice. Let's record it first
to the trial balance. Here's the accounts receivable. I'm going to record that cure. The accounts receivable
equals that 527, bringing it up from
2 thousand up by the 52072527 out of balance on down below next to
sales tax going up by 39. Here's the sales tax
payable were in cell H2. This equals then
pointing to that 139, bringing the sales
tax up to 139. Finally, the revenue
then going up, The 488 revenue on down below, we are in age 15 equals we're
going to pick up that 488. That then puts us back in
balance with the green zeros, the net income calculated as the revenue minus the
expenses that being income, not a loss, 24710 increases
by the sales amount. The 488225, Nine or 198, recording the second side
or half of the transaction. The sales or cost
of goods sold down below equals the 375375, that has an impact
on the net income. So the net impact
on income is 103, net increase to net income given the fact that
sales went up. But so did the expenses, the cost of the
thing that we sold. And then we've got the
inventory going down, inventory going
down by that 375, bringing the inventory
down to the 4 thousand. Let's go ahead and record this out onto the general ledger. Now I'm gonna I'm gonna
highlight the first half, making it green so I can
focus my eyeballs on it. Here's going to be the
accounts receivable. That's the third account down, the GLN, the same order. So we're going to go
over to the AR accounts receivable 415 in cell P 17, I'm gonna say equals
left arrow until I hit the wall up to the
accounts receivable, the five to seven, bringing it up from
20055272 to five to seven. That's the same amount
on the trial balance. We're out of balance
on the general ledger, so we're going to then go
to the sales tax payable. That's the last
liability accounts. Same ordering in the GL, assets and liabilities
and orange, there's the last one
on the liabilities. Date for 15 were in Cell AB 17 equals left arrow
till you hit the wall, scrolling on up
to then the sales tax payable at the
thirty-nine dollars, it goes up to $39. That thirty-nine dollars also
on the TB trial balance, still out of balance on the GL. We're then going to
the revenue account. That's the first the
first income statement accounts same order on the GL assets in green liabilities and
orange equity in blue. The revenue way out
over here in AI F5, F5 on the 5415, were in age A5 equals left
arrow all the way overhead. Esteem hit the wall. Boom, the whole Excel shakes. And then we're going
to pick up that 488. We brought it up
from 200 thousand by 488 to two hundred and four
hundred and eighty eight. Now we're gonna be back
in balance on the GL, but we're not matching what's on the trial balance with
regards to net income and that two hundred and
four hundred eighty eight matches what's on the TB. Let's record the second half. I'm going to ungroup this one. Green or blue of phi it, however you like
to think about it under Indian or blue, fine. Then we're gonna go to this
one cost of goods sold. That's gonna be like the
second income statement accounts same order on the GL, acids and dreamy liabilities
in orange, dark blue equity. And then the income
statement we're down here in 17 were on 415 as
of the date a j or j. J 17 equals left arrow hidden the wall
Boehm scrolling backup. And then we're gonna say 375375. So we got these one hundred
and sixty three seventy five to the one hundred sixty
three hundred seventy five. That then should tie out to
what's on the TB it does. We're out of balance on the GL. Let's record the last one, inventory that's gonna be the fourth account
in the assets. In green, fourth account. There we go. We're gonna go 415. And then in T5 equals left
till you hit the wall. I'm gonna burst through that
wall one of these times. I'm going to burst
right through it. That's what I'm
gonna do. Any case. We got the 4375 down by the
375 credit to the 4 thousand, that puts us back in
balance on the GL. That same amount is
on the TB as well. That is that now, let's go to the
subsidiary ledgers for accounts receivable
because we got to break that out by customer. Now, That's all the
way to the right, the subledger for AR down here, we're going to save
this as customer for, this is customer four on 415. And we're gonna
save in and BD 17, I'm going to get a head
of steam equals on burst and through that
wall, left arrow, boom. That's a hard wall. I should tough wall. I'll get at one of these times. I'm going right
through that wall. 375375. That's the wrong number. Hold on a second. Here we go. Let's try it again.
We're picking up the accounts receivable. Accounts receivable of a 527, you could just type it in there. You could just say that
you're on equals C2. And then if you add
up the four items on the customers
that adds up to the 2527 matching the TB given
by the green 0 here. So the 25272527 on the
TB is that what we have? We do have that indeed, legality, the same thing
for the inventory. Now inventory needs a
subledger breaking it out by the stuff that we sold. So let's go all the way over
to the inventory subledger. Way over here, we're gonna
assume we sold item two. These are two inventory items. Now we sold item to this time. I'm gonna say we sold down
here in the subledger for 20. We're not gonna get
into too much detail on how the flow
assumptions work, but you're using either
specific identification, first-in, first-out
FIFO, last-in, first-out lifo, or
weighted average, which is the default for
QuickBooks Desktop, generally, QuickBooks Online,
we'll typically use FIFO as the default. So we're gonna say that
we sold 15 units at 25. And that's gonna be
the 15 times 25. That's where we got to that
375 on the decrease in cost of goods sold and the
inventory that leaves us left. Then I'm going to
subtract this out to 15. At the 25. This is going to
be equal to the 15 times 25. Let's put an underline
here and there, I hold Control down
to highlight two non-adjacent font
group underline. Then we're gonna
subtract this out. This equals to 75 minus 15. This equals the one hundred, eight hundred seventy
five minus the 375. So that means we have equals the 150 divided by the 6025 units. So we have 60 units left
at twenty-five dollars, which of course it is
the same Twenty-five no matter what flow
assumption we use, because we haven't
had any changes in the dollar amount of
the cost of them. Therefore, that 60 times
25 gets us to that 1500 for the inventory
unit two plus the 2500 for inventory unit
one adds up to the 4 thousand total
dollar amount ties out to the TB given the fact
that we have the green 0. But let's double-check it. Going back to the left, to the TB and the GL, tying it back out again. Looks perfect. Toe, just like moon
DO would do it. And munoz a perfectionist. Perfect DO moon dough. That's what I call them because he does everything perfect. Now we're gonna say on 420, we're gonna reverse this. Now this is gonna be the
credit memo transaction. That would be this
form down below, which would tie out
to the invoice. You can kind of think of the
invoice and linking to it. We're going to be reversing it, assuming at this point that the account receivable had
not yet been paid back, return to the inventory. And so now we're
going to have to reverse this whole
transaction up top. When you do this transaction, you've got to
consider Did we get paid or not at this
point in time, if they paid us, then we got to give them money back or
some kind of store credit. If they hadn't paid us, then we got to reverse
the receivable. We also have to
consider the inventory. Are they given the inventory back and is it worth anything? Do I need to put it back on the books or not at
that point in time, we're gonna, we're
gonna assume it is, we put it back on the books. So that means that we're going to just
reverse this whole thing. Now, note that this is
where people get messed up. Another place they
get messed up first, they tried to think
about the credit memo without first thinking about the the first invoice entry, which is a mistake because
most people can't just imagine the credit memo on its own without first
imagining the invoice, write down the transaction
for the invoice, and then reverse it. Second. They tried to basically
reverse what happened up top. By then putting the debits on tops and the
credits on the bottom. Because traditionally
that's the way. Debits and credits
are supposed to go, but that's just a convention. It's not really what
you have to do. What you want to do is just
reverse this thing Exactly. That's the easiest thing to do. I'm just gonna take it
from top to bottom. The credits and debits
will be looked funny. It'll also look funny just
because it's backwards. The whole thing is
backwards because the credit memo is unnatural. It's going in reverse order to what normally should happen. But this is the
easiest way to do it. I'm gonna say this equals the accounts receivable
and that's a credit. So I'm going to indent it and then just reverse
this whole thing. I'm going to say
negative of that 527. It's on the credit side, but the credits on top,
you can't do that. You can totally do that. You can do that. If
people get mad at you. If you want to rework it later, then rework it later, but you can do that. Then we're gonna say the
sales tax payable is gonna be a debit negative of that 39. Then we're gonna
say that the sales, sales or revenue here
is gonna be a negative. You could use to plug function or you could say
negative of that for 88 negative sum plug funk
Shaun of that there's the 488. And then on the
cost of goods sold, I'm gonna say this is
the cost of goods sold, but this is the credit on top. Again, you can't put
the credit on top. You totally can. You totally can't do
it just to **** people off, to anger. People. Here we go. It'll still work.
It'll still work. So there we go. So now, now that you've
reversed it that way, if you want to rewrite it so that it looks nicer to
have the debits on top. Then you can rewrite it
basically down below. And that'll take you a lot less time than either trying to imagine this in
your head without first doing the first entry, or trying to imagine
how to do it, put in the debits on top. Just for formatting sake, for formatting sake, do it the easy way for
formatting sake. So in any case, let's
reformat it in a second. Let's record this first. I'm going to then reverse this. Let's make this green
so we can do that easy, like putting our eye right
to where we want to go. Here's the accounts receivable. We're gonna go right here. There's something in it,
this is going to reverse it. And there is gonna be one kind of kind of twist to this one. That's gonna be the sales. Let's look at that now. That sales account
I'll get I'll start to post it and then I'll
get to that in a second. So I'm gonna double-click
on the accounts receivable, go to the end of it, say plus. And then we're going to
point to that five to seven, reversing it, taking
it back down to the original point because
we reversed it out. The sales tax is
going to reverse out right here,
double-clicking on it, gonna say plus, and then go
to that 39, reversing it out. And then we could
post this one here, revert, reversing the sales. And that's what you would do. That's what a credit
memo would do down here if we didn't do
something special to it, which we might want to do, because maybe I don't
want to reverse sales. It's unnatural for
sales to go down. Maybe I want to record it in some other accounts such as
sales returns and allowances. Let's imagine that we do want to record it in another account. I would like to. This is the 100 tweak
we want to make. I'm gonna change this to
sales returns and allowances, which I probably misspelled. I misspell it. Did I
misspelled it at all. No. Spellcheck says it's okay. If it's good enough
for spellcheck, it's good enough for me. So I'm going to put
this right under here and add a new account. So we'll get into some
formatting issues here because we've been building
this worksheet as we go. So I'm going to try to build
this worksheet in here, meaning I got to add an
account here and it put it on the GL as well. So I'm gonna select the ones
below it and insert above. So I'm going to right-click
on those items. Insert. And I want to push
these cells down. Notice I didn't select
the whole row because I don't want to mess up everything to the right and left of it, but there's nothing below it. So I'm gonna say shift
those cells down. There, we have it. I'm gonna call that sales
returns and allowances. I'm gonna copy this,
paste just the, just the number and the
name started out at 0. And then sum it up equals SUM. There we there we have it. The sales returns
and allowances is in essence a contra sales account. So it's gonna be kinda
like a net sales. We'll get to another
net sales were breaking out contra sales account in a similar way as the
relationship between the fixed assets and the
accumulated depreciation. So we're going to then say
this is going to be equal to the sales being reversed. So it's always going to go
up in the debit direction. And the net of those
is the net sales, in essence of the 200 thousand reversing back out
to the 200 thousand, The cost of goods sold,
double-clicking it. Plus we're going to
be picking up that 375, reversing it out. And that's kind of an
unnatural thing to do as well. Their cost of goods
sold is going down and then inventory
is going back up, assuming we got the
inventory back, assuming it's still good, we're gonna put it back on
the books for the inventory, which can be somewhat
confusing with regards to our inventory
tracking and so on. But inventory back on
the books for the 200, for the 2 thousand, that puts us back in balance down below. Let's record it out now
it's recorded to the GL, noting that we're
going to have to add this account as we go. So we'll do that, that'll
be fun, That'll be fun. Let's go to the accounts
receivable first. That's gonna be the
third account on over the third
account is the AR, otherwise known as the
accounts receivable. We're saying this happened
on four hundred twenty. Four hundred twenty. This is gonna be
equal to NP eight equals we're picking
up then that 527, reversing it back down to 1000, going up by the five 27th to five to seven going
down by the 527, back down to the
2 thousand taken us out of balance on the GL. Secondly, sales tax payable, which is the last liability
account we got in the GL assets in green liabilities and
orange last liable bill, last liable, Latino on 420. I'm in cell AB's 18. Ab 18, we're gonna say
equals left to the wall, picking up then that
39 reversing the 39. So it went up and then it
went back down because we reversed it with the credit
memo back down to 0, just like it shows on the
good old TB trial balance. Now we're going to do the
sales returns and allowances. We got to add a
new GL to do that. How do we do that? That seems
like really complicated. Let's try it out. We're going to add a new GL. It's going to be over here. We want to somewhere like we want it to be like right there. What I'm gonna do
is add a whole, another couple of columns here. So I'm gonna say this is
how many count 12345. So I'm gonna add 1234. We'll just add those. I'm going to right-click and insert. Ended. I went to non format thes by hidden little paintbrush. I want to say clear that formatting because I wanted
to do my own formatting. And then we're going to copy these cells and just
copy that over. Copy and paste it, pasting it. There we have it. And I
added one-to-many columns. So I'm gonna delete this
one, right-click and delete. There, we have that. And then I want this one. This one down below shouldn't
have any numbers in it. And I want this one
to move up there. And then I want this
one down there. So what I'm gonna
do is move this, I'm going to cut this. I'm gonna select this
control X cutting it. And I'm gonna paste
it right there, Control V pasting it. Then this blank one. I'm gonna grab that and
Control X that one. And then Control V. Control V at right there. And this is our new
one right there. And then I'll leave this poll. I'm gonna leave
the whole for now. I'll just leave that. Then this is going to be equal to sales returns and
allowances there we have that. And then the beginning
balance is 0. This happened on 420. This is gonna be increased, increased equals the 420
is going up by that 488, which would put us back in balance now on the
general ledger except that the new thing
isn't in our totals over here. So I got to add it. So I'm going to double-click
here with this huge formula. And I'm going to add that
last cell right there. I'm going to add that
one by saying plus. And then we're going
to add that one here. That should put us in balance. I'm going to do the
same thing to this one. This formula right there. I'm going to say plus and then we're going
to add that last one. So this one will already
be done on your worksheet. But if you're just building
the worksheet as you go, you can see how you can kind
of adjust this worksheet hopefully as you go
Hold on a second. Okay. The second one is
still off until we record the second the second
part of our transaction. So I'm going to Green
of Phi, this top part, I'm gonna keep that one yellow because that's gonna
remind me hopefully that I do have to adjust some other things included
in the financial statements, the cost of goods sold, then it's gonna be the third
income statement accounts. So we got assets, liabilities,
income statement accounts. We're looking for the cost
of goods sold here on a M6. This happened on for 20. This is gonna be equal to an
ANC equals left to the wall, hitting the wall, we're
going to that cost of goods sold the 375 and enter. So there we have it
going back down, back on over to the
left-hand side. The inventory then is going to be our fourth asset account. Scrolling on over to
the inventory on for 20 Equals left to the wall, picking up the inventory
at that 375 and enter. The inventory has been reversed. Let's do this to the accounts receivable on the subledger now. So now we got to go all the
way to the sub ledger for accounts receivable because
we hit accounts receivable, this particular customer
needs to go back down. This would be done
automatically in QuickBooks because the
credit memo would force us to give them the customer
information as we enter it and be HAT left or equals
till we hit the wall. I'm going through it, I'm
going through it this time. Boom, Dang it. This is going to be then accounts receivable
going back down, 527, goes up, goes back down, adding up the four
customers still adds up to the 2 thousand matches the
TB given by the green 0. Then we got the subledger to for the inventory because
we got the inventory back again all the way back to the subledger
for inventory. And so now we've got inventory. It wasn't really a purchase, but it's kind of
like a purchase. So I'm gonna do it in the
purchase area here and say On this last one happened on
415 out 420 now it's 420. Now it's 420. So let's
let's put it right. Let's put it right
here on for 20. We're going to say now we've
got 15 units at 25, at 25. So this equals to 15
times 25 that are back. So that's gonna be ending
inventory 15 units at 25. That's the 375. So if I add this backup, we got the 60 plus 15. We had then the 1005 plus 375. This equals the one hundred
eight hundred seventy five divided by the 75 or the 25. We're back to where we started putting some underlines here. I know I did that quickly, but we're going kind of long. We're just basically getting
back to where we started. My main point being here that the subledger needs to
track the inventory which would be done in
the computer system with the help and use of
the items if used properly, we're back at the one
hundred eight hundred seventy five down below for item 22500, item one, adding up to the
4,375 for the both of them tying out to the TB
given the green zeros. If we go back on over
there, we have that. Notice that you might
recalculate this or might rewrite this
with the debits on top. Once you have done that, just to see what that
would look like. You could just say, all
right, what if I just rewrote this with
the sales on top? That would be the 488. Sales tax payable then is
going to be the 39 we got the accounts receivable would
be the credit which I'm gonna deal with a negative
sum or plug form, you know. And then you can put your
credits on the bottom. Then you can just reverse
then this one as well. Saying that the inventory
is gonna be the debit, the credit being
cost of goods sold. And then you've got
to look in more proper with debits on top, but not doing that first because it'll be more
confusing to do that first, but rather doing that later, just so you can
make people happy. So you don't upset people
with having a credit on top. Then we can then take a
look at the financials. We're going to have to
add this one account to the financial because
we added that account. So I'm gonna hide
everything till we get to the financial
so I can add that, putting my cursor on column j, going all the way on over to the right till we get to
the financial statements. There's our balance sheet,
it's out of balance. Right-click and hide
all that stuff. Then on the income
statement over here we got to add an account. I'm going to say we
need an account, we need another
account over here. We could add it.
It's really kind of a net, net revenue accounts. So let's add two lines
up top and adjust it. Thusly, I'm gonna
select two things, or two sets of rows in
there, those cells, those cells, and then insert
and shift those cells down. This will already be done
for you on your worksheet, but just to show you
how you could do it or if you're building this
worksheet from scratch, if you're following along
with this from scratch, then we're gonna have the
sales returns and allowances, which is gonna be equal to the sales returns and allowances which are kind of
contrast sales accounts. And then this is
going to be equal to the amount of the sales returns and allowances of the 488. And then we might
call this net sales. Net sales, not net
income, but net sales. And then this would
be equal to the two hundred and four hundred
eighty eight minus the 488. You might have one more
row that you would add. Insert another shifting
down, which would be cold. And then I'm going to format
it like the one below it, format like the one below it. And I'm gonna call
this one a net sales. We're gonna calculate, bringing that to the left-hand side. And then formatting this
one over underlining here, put in some brackets around it. I know I'm doing this quick, but just to give you an idea
how you can format this. And so there we have it. And then down below, we'd have to recalculate
the net income, which is going to be equal. Then we have to recalculate
the gross profit, which would be equal to
the 200 minus the 160. That then brings us to
the 3110 match into 24710 on the TB, 24710. And that puts us back in
balance on the balance sheet. Then once you did the
credit memo and a software, you've probably then go to
your balance sheet and check something like the
AR possibly and say, There's my ARs back to 2
thousand. That looks right. I'm going to unhide
myself from I to C, D, right-click unhide. You might say let me
drill down on that too. Like a transaction
detailed report. I think I hit instead of
unhide, I'm going to unhide. Unhide. That's what
I wanted to do. Let's go back to my
transaction detailed report for accounts
receivable and you'd get something like this
would be like a GL, you could say, all right, there's the activity
that looks right, but I want to see
it by customer now. Then you might go, okay, now I need a subledger which would be like an AR
accounts receivable, like detail report or summary
or aging, you could say. Okay, yeah, that looks good. What happened to my
inventory subledger? You can then look at your
inventory subledger, which would be like an
inventory summary type of report or
something like that.
12. Pay Employees Form: Excel accounting
practice problem. Pay employees form and get ready because we're about
to excel with Excel. Here we are in our
Excel worksheet. If you have access to
the Excel worksheet, would like to follow along
two tabs down below, example and practice example tab in essence being
an answer key. And prior presentations
we put together this practice worksheet
from a blank worksheet, which is great practice. And you can look at putting that together if you so choose. Now, we're going to be using
this worksheet to record the transaction behind the
processing of payroll, which if you're looking
at accounting software, would look something like this. We are going to be
processing the payroll for our employees. We would have to
do this and this would go fairly smoothly. Hopefully, if we had the proper setup with
the Payroll Settings, really the complexity behind
payroll is often setting up the payroll process
and then adding the employee
information properly. Then hopefully the system will help us record the
payroll properly. So we might record the payroll in essence, or in other words, on a weekly basis,
bi-weekly basis, semi monthly basis, and
so on and so forth. Once we process each
of the employees, then hopefully it
will calculate then the gross pay and then the withholdings
from the employee, including the federal income
tax, the state taxes, and then the Social
Security and Medicare and calculate our Social Security
and Medicare and so on. We'll get a general
idea of this, noting that payroll is
a whole topic in and of itself with its own complexities and has a lot of
differences and nuances. They could happen from
location to location, four different states as well. And localities will mainly be focusing on the
Fed kind of taxes, the federal income taxes, The Social Security
and Medicare, which in the United States would then be
blanketing in essence, the entire United States. There could be differences, of course, with the
state taxes and so on. We'll get a general idea of how the withholding system works. We're gonna go back on over,
we're up to the worksheet. I'm going to increase
now backup to 141st thing to understand
the journal entries to get a general idea of how the payroll calculation will
be put in place for that, Let's go all the way to the
right and do kind of like a subledger type of
calculation over here, processing basically the payroll for just two employees
in an example. If I'm going all the
way to the right, I'm going to create a
new worksheet over here, and I'm just going to
build this thing out. I'm gonna say we have
our employees in. If we're going to
have the gross pay, I'm going to call it gross pay. I'll put the pay on down below. And then we're going to
have what are called FIT. That's the federal income tax,
social security, Medicare. Medicare. If I misspell
anything, I apologize. And then benefits,
which might be like health insurance that
were takeout or so on. And that'll give
us our net check. This is just a
general idea here. I'm gonna do my formatting. I'm going to bring this on
down and then format this. Let's make that our black and white as has been our tradition. This will already be
there on your worksheet if you're using this worksheet, but if you're building
this with us, this is us building it. We're then going to center this. Check the spelling on it. Did I spell in an
employee's is misspelled. Still employees, right? For goodness gracious. Then I'm going to make this a little wider
and I'm just going to call it employee
one, employ one. This would be their names,
by the way, of course. And in fluid implode E2, Let's say that employee earns, let's say they
earn for the year. Let's say they earn 60 thousand a year and we pay them monthly. So this is gonna be a monthly
payroll divided by then 12. So hold on a second. 60 thousand divided by 12
is gonna be 5 thousand, that would be the growth pay. Then we're going to take from them that we're not going
to give them 5 thousand. We're gonna take out
of their paycheck, their federal income taxes, which would be based on the
information we got from the W4 social securities, which is more of a flat type
of tax easier to calculate. Same with Medicare
for the most part, although there's a cap and other things we
won't get into here. We do have a payroll class
if you want to look at that. And then the medicare added the benefits which are voluntary
things that are kind of, we're doing them a
favor in that case, instead of acting as the
government's collection agency. In that case, the
federal income tax is something that's
going to have to use tables to basically calculate. It won't be based directly on the gross pay because they're
going to have to give us other information such as the marital status and
so on and so forth. So I'm going to just
assume at this point that the federal income tax is
going to be about 15%. So I'm gonna take the gross
pay 5 thousand times 0.15. That's not a
standardized amount. That's just an amount that
we're going to be using here. It would be dependent on
basically the W4 information. That's gonna be one of the more complicated taxes
to be dealing with. Because that is. Dealing with a
progressive tax system, That's the amount
that will add up and we'll be reporting to them that we took from them on their
W2 at the end of the year, it all washes out once they
filed their Form 1040, and then figure out
what they actually o and then they get a refund or the amount due at that point, the social security is
more of a flat tax. So at this point in time, it would be the 5
thousand times they 0.06 to 6.2%, that could change. They're always arguing about it, but it's usually
more of a flat tax, although there's a cap on it and they keep on doing
funny things to it, making it more complicated, but That's usually
easier to calculate. Same with the Medicare, which would be the 5 thousand
currently at times 0.145. That's more of a flat tax, 1.45%, fairly easy to calculate. That's just the employee
half of it however. But there are other
things like a plus up a mountain so on that we won't deal
with at this point. Then the benefits which
would be something that we provide that were
not forced to provide. In theory, we might, they
may be in some cases, but health insurance or something or impossibly
a four or 1k, we're just going to call
them benefits, 0.035, we're gonna say 175
on the benefits. This is just an
example of the things that would come out
of their check. These three were forced to
take out by the government. They're going for taxes. This then is a benefit that were taken out of their check
and pain on their behalf, which is kind of
nice so they don't have to basically
worry about doing it. That means that their net
check would be equal to this number 5 thousand minus FIT minus the Social Security minus the Medicare
minus the benefits. This is what you
would see in essence on their paycheck stub. Let's do it again for
the second one here. And let's say that the employee
earned 75 thousand year, 75 thousand divided by 12. I'll do the same thing, 15% on the FIT, even though FIT is
not a flat tax, that would be dependent on their particular
circumstances. What they told us to
withhold basically with their W4 social security
is more of a flat tax, which we can calculate
more easily. The 6250 times the 0.062. And this would be Medicare, which is more of a flat tax. Again, there's changes. They do funny things to it. But in any case, It's more
of a flat tax, 0.145. And then we've got Let's
do that one more time. This is this amount times
0.145. That looks better. And then benefits we
said is going to be this amount times 0.035, which we're just making
up the benefits of mount it would be whatever the
actual benefits cost, whatever the benefits may be
for 16k deduction, possibly. The benefits, the employee
health insurance and so on. And then the net check, I'll calculate it a little
bit differently this time. This equals the gross pay
minus the sum of these items. So that's a little bit
cleaner of a formula, a little bit more fancy,
same kind of thing though. That's gonna be
the general idea. Now note, you could see this. You can say, well that's
not too bad to calculate, but you do have other
taxes involved too, like the state taxes
that could be involved. And note that any one of these type of things
isn't too bad. But when you start to increase
the number of employees, that just the sheer number
of items that you're calculating gets tricky when you're looking at
different states. That can also get tricky because the state taxes will change
from state to state. And then when you also
have to report this stuff, not only on aggregate but
on an employee by employees basis on every paycheck per employee giving not only
the current paycheck, but also the year
to date numbers. It starts to get
quite complex and that's why it becomes a
specialty in enough itself. There's no one piece that really is too complex in and of itself. But when you start to say How am I going to compile all of this information in
these different, in various ways that
people want them in. That becomes complex
just after awhile. So let's then say that
the total down here, if I total this up now, note when you're recording
this into the system, you can think about this
total as if they're one employee and recorded
as one journal entry using this then this information as a sub ledger in a
similar fashion as we do an inventory account kind of backing up
your transaction. But if you were to process
this in the system, like in a QuickBooks or
something like that. Then it would record each
individual paycheck because it would have to track each individual paycheck
in the system. So for example, if
you did this outside the system had a third party
calculate the payroll. Outside of your
accounting software, you could enter this into
your accounting software using basically
one transaction as if the entire all
your employees were wood employee making your
financial statements, correct. But not having the detail
in the system using the outside resources
reports like this to provide you
with the detail. If you do it within the system, then you're going to
have these kinds of reports that are in the system. And every check is
going to be recorded individually because these will each be
inch individual check. Again, we get into
that in a little bit more detail in the
practice problem. Right now we just want to
look at the journal entry. If I sum this up. All the way across. I could sum this across this
way and copy it across. We can also recalculate
it this way. For a double-check, this equals aggregate the gross pay for my two employees minus the sum of everything that was taken out
for both of them. And that double checks
our number here. Let's put an underlying
on this one. We're going to go fought
group and underlined, let's put some brackets
around this and make it blue. If we want a blue, a phi it because that's what, that's our colors that
we're using here, just like Excel is fun guy
used to do blue and brackets. And then we'll go to the then we also have the employer taxes. So employer taxes, these are the ones
we're not taken out of the IEEE checks, but that we're gonna
have to pay on top of, which includes our portion as the employer of Social
Security, Medicare. And then I'm just going
to call that the total. There we have that. Let's bring this on down. Gonna make this
black and white too. We're going to make
this black and white. Black and white with the headers center,
that employee 12. We can think of this for
each employee again, we're gonna have to match which you could calculate again, but what's gonna be
the same amount? Kind of like you could
think of it kind of like a four or 1k kind of thing. We got to match up
these two amounts. We also have the federal
unemployment tax, which I'm not going to
deal with now because it's usually not as
significant of attacks, although it can be
somewhat complicated, we get into that more in the payroll course if you
want to look at that. But we just want to get a general idea at this
point of the payroll. Then I'm going to
then total this up. Total. Let's sum this up equals
the sum of these two. These are the taxes
that we pay over and above underlining this item, fonts group and underline it. And then we can total that up
this way, equals the tote. Like so that looks,
that looks good. That looks good. And then we can also, let's total up this way, equals the sum of these two, sum it up this way,
underlying it. Then we can talk, we can double-check our
total sum in this way. Then we can sum it this way. Double-checking the totes. Then we're going to then
put our blue and border of this blue and
border blue boards. And then that's gonna be
then I'm going to call this a total increase to
payroll liabilities. The total increase
is going to then be Social Security, Medicare. This is going to be the
increase to the liabilities, meaning both the employer
and employee sides that we're dealing with here
and that's gonna be a total. Then we're also going
to have the benefits, the benefits that we're
going to increase. And then we're gonna
say total blue, make this, make this black
and white, black and white. Let's center just these
ones this time, Center. And so this is the
total increase to pay roll pay roll liabilities. I bill T's, which I'm going to group into one account
when we actually record it, which we'll talk more about when we do the actual transaction. It would be the employer
and employee portion. Now of the Social Security is what we're going to owe
to the government after we take it from the employee and have our portion saying
with the Medicare. So we had to double that up. And then the benefits
the benefits are just the benefits that
we're going to have to pay that we took out of their check that we're
going to have to pay as well and I'm
missing the FIT. So the Federal I'm gonna
pull this over to the right. And I'm going to also add
the FIT federal income tax withheld that we're going to
have to pay black center, quite black and white up top. The liabilities, once we're
done processing, a payroll, is going to result in a total liability that we're
going to be owing of the, the 38 O three for the
total liabilities. I'm going to make
this black and blue on this one, black or not black. And we're going to make
it black and blue. We're going to hit Blue and
bordered. There we have it. So now we can think about this
then these two employees, one-by-one or an aggregate, we're going to report
it now in aggregate, but you can also think about it and a check by check basis. So let's see what it looked.
So let's just recap this and we'll do the journal
entry next time. If we have two employees, we're going to have their
actual this is what you're probably used to seeing
on a paycheck stub, we'd have the pay that
they would receive minus what's was taken out
and then the net check. That net check is what is
actually coming out of the bank account for us as the employer at
this point in time. Same thing for employee to, you can think about that as
if all employees were like one employee and record the
transaction in that way, which is what we'll
do next time. Meaning you could say, well, total gross pay was this. And then we had withholdings
that were taken out. And here's the net check. There's a little bit
of a problem with that in debt in practice, of course, the net check that's
coming out of the account will be two
checks at that point in time. So if we were to record it
aggregate as one check, we need to make sure that
we can tie it out to the actual amount
that's coming out of the bank statement when we
do our bank reconciliation. And then we're
also going to have at the same time an increase to our payroll taxes
over and above what we owe Social Security
and Medicare. These are not going
to be paid at the point in time we
process the payroll, but they will be increasing
at that point in time, therefore, will have a
liability on the books. Once we process the payroll
of the federal income tax, that's all coming from
the employee's paycheck. We just took their money. That's in theory and held it because the
government made us do so. But we don't owe any, that's not our
federal income tax. It will have to pay
that separately depending on our taxes. This is their
federal income tax. Then we have the
social security, which includes our portion
and their portion, Medicare, which includes our portion
and their portion, the benefits that we took from them that are going to
increase the liability. These are the liabilities
that will then be increasing and be payables on
the balance sheet, which we could put as one
lump number as payables, or we could break them out
to different accounts. So next time we're going to take this general information
and see, okay, how could we actually
take this and put it into a journal entry, recording it into our
accounting system.
13. Pay Employees Form Part Two: Excel accounting
practice problem, pay employees form
part number two, get ready because we're
about to excel with xo. We are in our Excel worksheet. If you have access to
the Excel worksheet, would like to follow along
two tabs on down below, example and practice example tab in essence being
an answer key. And prior presentations we put together this worksheet
from a blank sheet, which is a good practice
problems to do it. Now, we're going to be using
this worksheet in order to record the transactions
journal entries behind the payroll process. When thinking about recording
the payroll process, which if done in QuickBooks, might look something like this. Processing the payroll
for our employees. Hopefully the generation of the checks being
somewhat automatic. If we have set up the
payroll process properly, then generating
those checks provide the pay stub for
calculating the gross pay, the withholdings from
it, the net paycheck, as well as the employer
withhold dance posting all this information to the trial balance and
the balance sheet and income statement,
financial statements properly. Going back up top, last time, we're going to increase
the size up again to 140. We thought about the
supporting worksheet, which you could think
about as an essence, a type of substance,
a subsidiary ledger, which will record
or help support the transaction that we
will be recording now, we did that all the way
on the right over here. So all the way on the right, we've got this subsidiary
ledger way out here. We're going to use
this information now to be constructing
our journal entry. To do that, I would
like to hide a bunch of columns all the way back till we get to the
trial balance. I'm gonna put my cursor on
this column right to the left, and then select
all the way back, highlighting all the
columns till we get back to the trial
balance, the TB. Here we are at the TB. Let's leave column J open. Right-click on
that selected area and hide that information. Now we've got our information right next to where
we're going to be entering the journal entries
on the left-hand side. When we did this calculation, we're thinking
about two employees to keep it fairly simple, noting that increasing
the employees adds to the complexity greatly. If you think about each
individual employee, you could probably
imagine this on your paycheck stub or have
seen something similar. Gross pay we're
gonna say 5 thousand for employee one, FIT, federal income tax withheld 750 Social Security
and Medicare withheld, and then benefits withheld, net check then being something
less than what was earned. Same for employee e2 with different payroll
numbers, of course, we can think now when we record this to the system
of it as being recorded as basically
two checks which would be done in the
accounting software if you're processing payroll
within the software because it would have to generate the two
separate checks. Or you can think of this as a subledger that's kind of backing up and supporting
the information. Possibly you could think about that situation happening
with a third party like a paychecks or an ADP helping you to
process the payroll, entering it then
into the system with one transaction making
it kind of like a one check or one journal
entry transaction. Thinking of in essence, all the employees as if
one journal entry or one employee recording it that way, simplifying
the recording, getting the financial
statements correct, and not having as much detail like in the accounting system, the more detailed
being provided by these ledgers that
would be provided possibly by third-party
employee or payroll providers if you
were to use that method, however, you do have to
recognize that these are checks that would be
coming out of your account. And when you reconcile, then you'd have to reconcile to what's going to happen
on the bank side, which is going to be an
a check by check basis. So you've got to make
sure that you can do that reconciliation
process and that format. Then we have our taxes that we're gonna have
to pay over and above, which at the point in time
that we process the payroll, we're not paying them. We are incurring the taxes
at that point in time, not based on our income, but based on the
employee's income, which is gonna be the matching of Social Security, Medicare. And so we could have federal
unemployment tax as well. But I'm gonna keep
it somewhat simple. Federal unemployment
tax is not as big of a tax and wouldn't just get an idea of what is
happening on here. We also, of course, could
have state taxes too. That means that the
total liabilities that are gonna happen as a result of this is gonna be the federal income
tax for employees, the Social Security
and Medicare for both employee and
employer portion. The benefits that are
for the employees, total liabilities is going
to be at the 38 O three. Let's think about how
we can construct this. I'm going to make
this first part as if we're gonna construct
this journal entry as if we only have one journal entry instead of doing
two journal entries for each employee
as if, in a sense. We only have one employee with
the totals on down below. And then we'll think
about the employer taxes. Let's go back to
the left-hand side. We're gonna say, let's imagine
this happens on 630 here. We're also going to be adding
some accounts as we go because we don't have these
accounts in our sheet yet. So I'm going to add
them too as we go. I'm gonna go up top and first let's add the payroll items. So I'm gonna call this
payroll expenses. We do have payroll
expenses here, so we're gonna add that. That's going to be the
debit for payroll expenses. And the payroll
expenses is going to be for the gross amount
that they got paid, even though they didn't
actually get paid that amount. In theory, they're going to get that amount and we're gonna
pay these other people on their behalf. They got then our expense
is gonna be the 11250. The 11250 and the
payroll expense. So in essence, that
payroll expense represents their portion of the taxes already we're
recording the expense, but note we're not recording
these expenses that we're taking from them in
another account. That would be some
other account of expenses like payroll
taxes, expenses. We're recording this gross
amount which includes those things in it as
the payroll expense. And the reason for that is because these other
things that we will be paying for taxes and benefits are not in
theory our money. They're the employees money, which is pain that to these
other people on their behalf. That's the idea of it. Then we're gonna say then the other side is going to
be a liability account, which I'm just going to
call payroll liabilities. The payroll liabilities. You do have some more options
with payroll liabilities, you might break out
payroll liabilities depending on the different
types of liabilities you have. For example, you might want different payroll
liability accounts for the FIT Social
Security and Medicare. It could possibly break out the employee and
employer portion, although that's not as common, you might break out another
liability for the benefits, which is quite common as well. I'm just going to put
all the liabilities into one account that we're, that we're just going
to say these are the all the liabilities that
are being generated as we, as we process the payroll that
we're going to have to pay later to these entities including the Fed
and the benefits. Whoever the benefits
are going to possibly the four
or 1k or the or the medical Medicare medical
expenses or something like that. Insurance. I'm trying to say That's what I'm
trying to spit out. I'm gonna say this
is the negative. If I am the items that were withholding from the
paycheck in aggregate, which is going to be the
FIT Social Security, Medicare and the benefits. Closing that up. So I'm just adding those
up and flipping the sign. And then the difference is the check that is going
to be coming out. This is gonna be the
checking account. We'll call it the
checking account. And I'm going to have this to negative sum or plug
formula, negative SUM. Pulling out the
checking account that 8308 should match
the net checks, which is going to be the
8308. There we have it. Note that this 8308
now is being recorded. We're going to record that
as one transaction in this format when in actuality
on the bank statement, it's going to be two checks. That means when we reconcile
our bank reconciliation, we got to make sure
that we can account for that adds a little bit of a
complexity in that sense. Then we're going to debit this item or increase
the alignment, alignment and increase
the indenting of it. Then we're gonna have
another transaction happening at the same time, cash not being impacted for our portion of the
payroll taxes. I'm gonna call this a different
kind of expense payroll, which we're going to have to
add payroll taxes expense. And then it's going to also be increased in the liability
for our portion. This first one is the
tricky component right here because it's gonna be different than the
payroll expense, which represents basically the
earnings of the employees, even though that includes their taxes that
they're going to pay. We're going to pay. But
out of their money, we took their money to pay it. These are our taxes
based on their wages that were gonna put into
another expense account called payroll taxes. That's going to be for the total Social Security and Medicare for our portion over and above
what we pay the employees. There's the debit and
the credit there. Of these two transactions. Then, then we're gonna have that are happening in
essence at the same time. These two are hitting
the liabilities. That's the total liabilities
of the 3,008 O three that we calculated down
here that we expect to be an increase in
the total liabilities. That is, in part what we're taking away from the
employees that we have to then pay on their behalf and our taxes that we owe
not based on our income, but based on the
employee income. And that's going to be
the difference there. And these two accounts here are often people get confused. This breakout of the
expenses between the payroll expenses
and the taxes because you start to think
that this taxes expense. Should include all the taxes
that we're going to pay, which is going to be these
taxes and these taxes. But no, because these
taxes were paying up top, we've recorded as an
expense when we recorded the pay of the employees because the employees earned the 11250, these taxes that were forced to take from them and
pay are actually their earnings that were
just required to act as a collection agent and pay on their behalf
by the government. These taxes are taxes to us, meaning these are taxes that we actually
owe over and above their gross pay because we
have to pay the payroll taxes. So that's why their
payroll taxes, these taxes on the expense side represent payroll
expense because they're part of the gross pay. That's going to be the idea. If we go back on over
here and record this out, then let's record this out. And also just realized
that this is a little bit, this is clearly simplified for a couple of
different reasons. One, we're recording
this in aggregate. If you did this in
QuickBooks that we would record check by check as we looked at and to note that we're not adding the payroll
taxes for the state taxes, which in the United States could differ from
state to state. We're also not looking at the
federal unemployment taxes. Note that also when you
process the payroll, you're going to have to be
providing this information to each employee on the pay stub given both year-to-date numbers as well as the payroll for the current time periods so
that all of that reporting kind of information might
you can think of it be done outside as a third party or be done within the
QuickBooks system. Having helped within
the QuickBooks system, our focus here is to get an idea of kind of like
the accounts that would be affected within the payroll from a journal entry standpoint. Okay, so let's post this out. We're gonna say the
payroll expense first. Let's make this
green so we could focus in our eyeballs
on that one. Payroll expense. Down below we got payroll
expense is going to be equal to getting that 11250. Bringing it up, then we've got the payroll liabilities is
going to be the liability. We don't have that yet. So I'm gonna add all we do have payroll liabilities,
it's right here. Payroll liabilities
is right there. So I'm gonna say this equals
the payroll liabilities. Then that's going to
be in the middle. This equals then the 2942 and then the checking
account is going down, checking account going down. And so there we have
that liabilities go up, checking account goes down, and we're back in balance
with the green zeros. That expense made
the net income. This is income not a loss. Go down by the 11250 to 13460. Now let's record the
other journal entry which happens at the same time. But it's nice to break them out into entries since
they both balance. So we're going to then
say the next one. I don't need to make it green. The payroll taxes, this is the one we have to add down
here. We don't have it. You could put them into the payroll expenses
as well and say, Hey, they're all payroll
expenses, taxes and expenses. But typically you
want to break out the taxes if you're
going to break out the two categories. So that's what we'll do here. And it's also the category that causes the most confusion. Oftentimes, It's also
nice to understand this because at the
end of the year when you're trying
to verify payroll, you'd like to tie this
information on your books out to the payroll tax
forms included the 941940, the W2 and W3 forms. So let's go ahead
and I'm gonna select these four cells and then right-click on them and inserts so I can put another, another row underneath him and
shift those cells on down. And then this is going
to be the payroll taxes. Payroll taxes starting at 0, I'm gonna start it
at 0, even though there's something in it
from the prior period, but we're gonna start it at
0 for the example problem, pull this down, summing across, and then we'll say, okay, so that means payroll
taxes is going to be equal then to the payroll tax up top. And then the liabilities
also going up again. Liability is going up again
for our portion which we have not yet paid but are incurring
as payroll is processed. We'll double-click
on it plus the 861, increasing it, putting
us back in balance here. So that means if
we compare this to basically our data on
the right-hand side, the checking account
went down by the net check for the
two employees over here, which is the eighth 308. So that makes sense. And then we had the payroll expense go
up not for the net pay, but for the gross pay because that's what the actually earned. And then we're gonna be paying their payroll taxes and
benefits on their behalf. That's why we didn't give them the growth TE because we're gonna pay these other people on their behalf with their money. And that's the theory of it. And then we had our taxes. That we had we paid. So these are the
payroll taxes going up for our taxes over and above and the total amount of
liabilities that are going up for the FIT
Social Security, medicare, and benefits
that have been incurred which have
not yet been paid. They've been incurred
by either us taking them from the employee. Social Security FIT for example,
half of Social Security, a half a medicare and the
benefits and our portion, which is the Social Security
Medicare in this case, given a liability of the 3803
that we're going to have to pay to the government and the
benefits into the future, which we grouped into just
one payroll liability account at the 38 O three, Let's go ahead and
post this out. So I'm going to
unhide some cells here so we can see
the general ledger. Then we will post it out. Putting my cursor on I column I, dragging over to CPE, letting go, right-clicking
the selected area. And we want to unhide now. So we're going to unhide
and then post this out. So back on over to the left, I'm going to make
the first one green so we can see it a
little bit more clearly, focus our eyes on it as
we posted to the GL, we're looking payroll first, which is one of the
third to last liability. It's in the same order. On the GL, the acids in green liabilities and
orange equity dark blue. And then we're in
the income state. It we're looking for the
payroll expense down here on AQ 17, this is on 630. We're in AR 17 equals holding the left
arrow until we hit the wall. Boehm, and then
we're going up to that payroll expense
and C2 and enter. So there we have it. 8 thousand goes up by the
11250 to the 1990's to 50, that 19250 should also be
on the TB trial balance. It is indeed we're
out of balance on the GL till we record the rest of it,
payroll liabilities, then second liability
account going on over the GL assets in green
liabilities and orange, we're looking for that
second liability account, AA five is on 630. Date we're in a B5
equals left to the wall. Pick it up that 2942 and enter bringing the
liability up to 2942. That 2942 matching
the TB will not yet, doesn't match yet the TB because we have to
record the second side. We're still out of
balance on the GL up top. Let's record then the
checking account going down. Notice that two checks
are actually happening. We're making the
checking account go down by one transaction, 630. And so we got to be
careful when we reconcile. In that case, we might want a different checking account
in practice for the payroll to make that a
little bit easier to deal with. But here we go. Checking account going
down by the eighth, 308 from 100 thousand down
by A30 eight to the 91,692. So there is that there could
be rounding involved here, by the way, but we'll
keep that there. That's what's
matching on the TV. We're in balance, but
we're not matching on the net income tilde we
record the other side, including the payroll expenses. So now we want the
payroll taxes expenses, which we don't have on the GL. Yes. We've got to
add that to the GL, which it will be
on your worksheet. But I'm gonna
practice adding it if you're building your
worksheet from scratch, we'll add will practice
adding the payroll. That's right here.
So let's do it. I need to go like right
after the payroll, the GL, we're gonna go over to
the Geo and say we've got payroll over here. And here is the payroll expense. I want one right
after it right there. I want that to be payroll taxes. What I'm gonna do
is put my cursor on little skinny cell and
drag on over to AW. And then I want to insert
some, I'm cells there. Right-click, insert
some columns, put in some columns in place. And then I want to get
rid of the formatting. So that little
paintbrush format, I don't want any formatting yet because I'm doing
my own formatting. And then we're gonna put
our cursor on this cell, the skinny cell to AS. And then we're going to
right-click and copy, copy those and paste that in 80. Right-click and paste it. Right-click and paste it. Well, I'll just going to
say Control V because the right-click and
pasting is the stubborn. There we have that. I don't want anything down here. The second one is blank, so I'll just leave
that one blank. I'll leave that
blank thing there. And then up top, this
is the one we want. I'm gonna say this equals
scroll all the way on over to the trial balance, picking up the payroll taxes. Payroll taxes. There we have it. And so there we have it. And then we're gonna save
the date here is on 630. This is gonna be
equal to scrolling on over to the amount
payroll taxes 861. We have that, but we
also have to add it to our check numbers
up top in the GL. This GL needs
double-click in here, go into the end of it. Plus we got to add our new GL accounts and
that giant formula. So we're gonna say
that this one, payroll taxes right there. And then I'd haven't added
anything to this one. But if I add anything there, I want to add that one to you. I'll just add that 02. And so there we have it. It's out of balance till
we record the other side. This one also needs
to be included. So we got this long
formula that needs to be this same cells need
to be included there too. So we're gonna go back on over. This is comparing
the income statement for the payroll taxes, this one and this one. That should do it.
Now let's go back on over record the liability
side of things, which is gonna be the second the second liability account. So we're scrolling
over green assets, liabilities, yellow
liability account on 630. This is gonna be equal to an AB E6 equal to
left to the wall. Picking up the
eight-sixths, one. There it is. So it goes up by those two journal entries that both increase the liability, bringing it up to that 3803, that matching what's on the TB. Also matching the TB, put this back in
balance on the G, L. Let's identify this item right here on green
or blue of phi it, whichever way you like to
think about it and green, a fire bloom I right-click
is not working properly, which is frustrating. Okay, So then let's fix the financial statement
because we're gonna have to add this account down here to the financial statement
for the payroll taxes. To do that, we're going
to put our cursor on column J and then select all the way over to our financial statements,
the balance sheet. We can see them side-by-side and make some
adjustments to them. We're going all the way to CB. Right-click on that
selected area. The right-click worked very
nice and hide that stuff. There, we have it then
on the income statement, we're gonna have to
add that one account. I thought we were gonna
have to add a few, so this isn't too bad. It's got to go under
the payroll expenses. So I'm just going to select these cells, right-click on it. It doesn't want to right-click. The other way you could do
it is I can just cut these, cut that stuff Control X and
paste control V right there. And then I'm just going
to save this is gonna be equal to payroll taxes. Payroll tax, payroll
taxes expense. That is going to
be the number that 861 that we're off
by 861. There it is. Let's do some format painting. Select the one above it. Go to the Home tab, clipboard,
paintbrush, paintbrush. He crushed that paint down. Paint, blue paint. We made it blue. Summing that up,
That looks good. Now that 12599 matches the
12599 on the TB as well, back in balance on
the balance sheet. So if we were to look at this in the system
and QuickBooks, then we would probably
enter the payroll. Then we would go in
here and say, Okay, did everything worked the way
we think it should possibly looking into something like
the payroll liabilities, let's say double-clicking on the balance sheet account of payroll liabilities
to see the detail, I'm going to unhide
some sales to do that. That would be going to
the general ledger, going from I to C, D, left-click unhide
payroll liabilities. Then over here we drill down. This would be similar to the transaction by account or transaction detailed report, seeing that detail,
then we might say, I'd like to see more information about this account
breaking out that payroll, breaking up the payroll
for that period, then we might go to other payroll reports that would be kind of like
subsidiary ledgers, although quite detailed
because the payroll can be quite detailed for it. And or we might go to Reports if we were to do the
payroll outside of the accounting system to a third party like an
ADP or paychecks and look up the reports that
would be supporting that that transaction number, the schedules that
we put together, which would give us things
like the total net check, the gross pay that
we can tie out, the employer, payroll taxes and the liabilities
that were calculated.
Robert Steele
