Accounting System on Excel - journal Entries to Financial Statement - for small Business
Hello. A lot of people are looking for an accounting system for a small business on Excel. And many ask: How can the journal entries be written and posted to the ledger? How can the trial balance and financial statements be prepared? Should I use formulas, or macros, or pivot tables? In this video I will answer all of these questions. We will prepare a small accounting system, without using macros. This system is for a small service company, as well as you can use it for commercial companies, But without inventory control.
we will define the chart of accounts and write journal entries with an example, and create a sheet for general ledger, then will get the account balances to use them later in the trail balance and financial statements. Are you ready? Let’s begin. First, we'll open a new Excel file, and we'll rename the sheet: "chart of accounts". Then we'll write the following data in it, knowing that there are accounts for the income statement and the balance sheet. In the main type will put assets, Liabilities, and equity for the balance sheet, and revenues, and expenses for the income statement.
In the account type, the assets will be subdivided into current assets, long term investments, property, plant and equipment, etc. Liabilities also will be subdivided. In the statement, we'll put income statement, retained earnings or Balance sheet. Finally, we have balance.
We’ll use Excel equations to get the balance for each account. Now we'll enter some accounts. For example, cash is an asset, current asset, in the balance sheet. And then we'll format the cells. Fill the cell with color.
Change the row height. Bold. Change font size. Center and middle align. Then we will make conditional formatting to the cells to have borders if we insert data.
Let’s see what conditional formatting can do. If I write anything in this file without conditional formatting, for example, “cash”, nothing happens. With conditional formatting. If I write “cash”, there will be borders in the row from A2 to E2. Let's see how we can make conditional formatting for this. To make conditional formatting, we have to select from column A to column E and then, conditional formatting.
New rule. Use a formula to determine which cells to format. the formula here: =A1.
We want A1, A2, and A3, etc. So, we make absolute reference for A and relative reference for 1. To do that, we have to press FN with F4 or F4. Here we have absolute reference for A and 1. Again, FN with F4 relative reference for A, and absolute reference for 1. We don't want it. Again, FN with F4 we have absolute reference for A and relative reference for 1.
That’s it A1 <> 0. If that's true, in the format there will be borders. Let's select this border, and select these, and press ok. Ok. Here we done.
Finally, we will freeze the first row. From the view tab, choose freeze panes and then choose freeze top row, To keep titles visible on the screen even if there is a lot of data. Now we will make the journal entries sheet. In typical accounting, we have two accounts at least in the journal entry, one of them is debit and the other is credit. Here we will use one account only and the other will be the cash account, by default.
If the account is debit, we will put negative number. If the account is credit, we will put positive number. If we have non-cash transaction, we have to make two journal entries For example; if I have credit sales by $1000 the entry will be Dr. Account Receivable $1000 Cr. Sales $1000 In our file we’ll make two entries Dr. Account Receivable $1000 Cr. Cash $1000 And then Dr. Cash $1000 Cr. Sales $1000 In summary, there will be no impact on cash.
Let's see how we are going to form the journal entry sheet. Let’s create a new sheet and rename it: journal entries. first, let’s input data Date, amount, account, and explanation. we will also create two automatic fields to separate the debit and credit. then, we will repeat the same steps we’ve done before to format the cells and conditional formatting in the chart of accounts sheet, or we can simply use Format Painter to apply the same look of the chart of account sheet.
To do that, we will select column A in chart of accounts sheet. then, press on Format Painter in the Home Tab and go to Journal Entries sheet and select from column A to column F to automatically apply the formatting. Let’s do some formatting to the sheet. For example, we can change the columns width, and change the number format to date for column A, from here and choose date and then we choose any type from here.
For column B, we will use accounting format by pressing on the comma after selecting the column and we can remove numbers after decimal point from here. For the account field we have to do data validation to ensure that we use only the accounts listed in the chart of accounts sheet. To do that, we will select column C and go to data tab, select data validation, list, then go to chart of accounts sheet and select column A, ok. Now if we put for example “cash”, it’s ok. But if we put “common stock”, it will give an error, so, we have to define this account in the chart of accounts sheet before we can insert it here.
In fact, we do not need the cash account to appear within the accounts here, but we need all accounts except cash. So, we need the range from A3 to A12 in the chart of accounts sheet. So, we’ll repeat the same steps in Data validation but we will select the range A3:A12. But here the number 12 is a variable because we will need other accounts.
So, I will copy these characters and then initially press ok, then go to any cell and write apostrophe to show the formula and then paste. We need these exact characters from after equals to dollar sign, so, we will select and copy them. Then press Enter Write equals then paste them between two quotation marks to take them as a text.
Now we need the variable 12 To do that we will use COUNTA function. The COUNTA function counts the number of cells that are not empty in a range. Equals COUNTA the column A in the chart of accounts sheet.
We have to use absolute reference for column A. The value here is 12. If I write any word in A13 the value will become 13. Now I have to combine the text above with COUNTA function. Double click Select and copy without equals Then press Enter Double click Write “and” symbol at the end of formula then paste.
To make this text as a reference we want to use the INDIRECT function. INDIRECT returns the reference specified by a text string. References are immediately evaluated to display their contents.
We just put INDIRECT Open parentheses And then Enter In a single cell this formula will give an error, but it refers to the range A3:A12 in “Chart of accounts” sheet, and we can use it in a name. A name: is a meaningful shorthand that makes it easier to understand the purpose of a cell reference, constant, formula, or table, each of which may be difficult to understand at first glance. To use it, we’ll go to Formula Tab Select define name The name mustn’t include spaces, we can Write any name refers to our data. For example: Acc, underscore, name. Here we’ll select this equation and paste our formula. If I select any range and write equals, Acc, underscore, name, and press enter, it will give me the values in my source range.
If I insert any new account in column A, the list will be updated. Now we can use our new name in the data validation. We’ll go to “journal entries” sheet. From data Tab. Data validation. Clear our old reference.
Press FN with F3, or F3. Select our name. Ok. Here we done.
If I insert any new account, the list will be updated. Now I will delete these items because I don't need them. And save the file.
Let’s move to an example of journal entries. We will use an example from the Intermediate Accounting book in the third chapter. We have previously explained this example in one of the channel’s videos, and we will put the link for you in the description. For speed we have defined the necessary accounts in the Chart of Accounts sheet and will only enter the journal entries.
Let’s begin October 1: Stockholders invest $100,000 cash in an advertising venture to be known as Pioneer Advertising Agency Inc. The entry is: Dr. Cash $100,000 Cr. Common stock $100,000 First, we’ll write the date, then. In the amount: positive 100,000 because the account is credit. In the account: Common Stock.
In the explanation: Issued shares of stock for cash. October 1: Pioneer Advertising purchases office equipment costing $50,000 by signing a 3-month, 12%, $50,000 note payable. The entry is: Dr. Equipment $50,000. Cr. Notes Payable $50,000 Here we will put the both accounts. After inserting date In the amount: negative 50,000 because the account is debit. In the account: Equipment.
In the explanation: Issued 3-month, 12% note for office equipment. Repeat date by press Ctrl, and apostrophe. In the amount: positive 50,000 because the account is credit. In the account: Notes Payable. Then press Ctrl, and apostrophe to repeat the explanation. After finishing all transactions we’ll separate debit and credit amounts with MAX and MIN functions Let’s see how? In the debit we’ll use MIN Equals MIN, B2, comma, zero The result will be zero In the credit we’ll use MAX Equals MAX, B2, comma, zero The result will be 100,000 Double click on cell handle to copy formulas down.
The numbers in the debit are negative, to avoid that we’ll put minus after equals in the equation Fill series Here we done. Now let’s fill the balance field in the chart of accounts sheet. We need to edit the title to show which balance is debit and which is credit. To force wrap text press Alt, and Enter.
Then write “Debit” between two parentheses. Slash, “Credit”. That means the negative number is debit and the positive one is credit. Then let’s let Excel calculate the balance for each account. For the cash it’s the opposite number of sum transactions we’ve input in the journal entries sheet. So, we’ll write equals, minus, sum, Column “B”, in the journal entries sheet. For other accounts we need SUMIF function.
You use the SUMIF function to sum the values in a range that meet criteria that you specify. Equals, SUMIF. The range that meets the criteria is column “C” in the journal entries sheet. Comma The criteria we need is the cell “A3” in the chart of accounts sheet.
Comma The range we need to sum is Column “B” in the journal entries sheet. To fill a formula down, Drag the fill handle across the cells. Now we can use format painter to make accounting formatting for the numbers. Then we can choose wrap text to turn it back.
Now, let’s create a new sheet and rename it: “assumptions” for financial statements presentation. We’ll input these data Company name: in our example: Pioneer advertising agency Inc. Fiscal year begin date: typically, at January, 1st. Fiscal year end date: typically, at December, 31. Period: it will be month, quarter, or year. Report date: typically, December 31, 2022.
Finally, we can move the sheet to be the first. Before we go to the general ledger, let's create a trial balance sheet. Here we will input titles In “A1” cell we need the company name from the assumptions sheet.
Simply, Equals “B1” in the assumptions sheet. In “A2” cell we’ll write: “Trail balance”. In “A3” cell we need the Report Date. Equals “B5” in the assumptions sheet. Then we have to change the number format to date if necessary.
To center text across columns without merging cells, use the “Center Across Selection” command, available from the dialog launcher in the Alignment group of the Home tab. Let's input these data: Account. Debit. Credit.
Then add borders to cells. In A5 cell it must countian the first account in the chart of accounts sheet. Simply, I can write: equals cell A2 in the chart of accounts sheet. If I fill series, Excel will display zeros after finishing all accounts. To avoid this, I have to use IF function. If cell A2 in the chart of accounts sheet equals 0.
If that’s true give me two quotations, to look like empty cell. If that’s false give me the value of the cell A2 in the chart of accounts sheet. If I fill series, Excel will display empty cells after finishing all accounts. To fill the debit field, we want the opposite number of all negative balances in the chart of accounts sheet. we can use MIN function, as we did before but here I’ll use if function Equals if. If “E2” cell in the chart of accounts sheet Less-than zero, give me the opposite number of its value, else give me zero.
We will do the opposite in the credit. Equals if. If “E2” cell in the chart of accounts sheet Greater-than zero, give me the its value, else give me zero.
Again, if I fill series, Excel will display zeros after finishing all accounts. To avoid this, I have to use IF function. We can use ALT + Enter after equals to split the formula to make reading and understanding it easier. Drag and drop to expand the Formula Bar.
And then write: If A5 cell equals tow quotations, then give me two quotations. Comma. Enter. Fill series. Again, the same steps in the credit field. Here we done.
Now we can make accounting formatting like we did before. Then we want the totals for debit and credit. To avoid circular references and complicated formulas, we will use an excel camera. The Camera allows users to cut and paste dynamic pictures of data ranges that can then be arranged in any layout for display or printing. Let’s see how? Copy the range from column A to column C. Go to any cell in the row one, for example “G1”.
Right click on the mouse. Paste special. Column widths. We can remove gridlines from view tab In “G4” for example we’ll write: Totals. In “H4”. AutoSum from home tab.
Select column “B”. Fill series to “I4”. Double underline for both Cells. Write space in “H3” and “I3”. Format Painter. Then Replace the Double underline with underline in “H3” and “I3”.
Now let’s add the camera to Quick access bar. Just click on the drop-down arrow at the far-right end of the Quick access bar and select More Commands. In the Excel Options dialog box, select Commands Not in the Ribbon.
Scroll down in the list of commands and select the Camera tool. Click Add and then OK. Now, we select the range from “G3” to “I5”. Click on the camera. Click in “A18” cell.
Any edit in the cells will appear in the picture. To remove the picture borders. Picture format.
Picture border. We’ll choose the white color or no borders. One of the most important advantages is that we can move the image however we want. Now we can set a print area to avoid printing any unimportant data. Select range from column “A” to column “C”.
Page layout Tab. Print Area. Set Print Area. Ctrl and “P” Now, the trial balance is ready to print.
Let’s make General Ledger or Statement of Account sheet We are going to make a new sheet and rename it "statement of account" and then we are going to put these data: Date Explanation Debit Credit Balance, debit or credit Account name: Account summary: Opening Balance: Debit Credit Ending balance Beginning Date Ending date Then we are going to see how to fill it up with data. In cell A1 we're going to put the company name in it which we take from the sheet "assumptions" from cell B1. Like we did before. In account name we must use Data validation, as we did before. Now we have a list of accounts and choose from any account we want. As for beginning date and ending date we choose any two dates we want such as October 1, 2022 and October 31, 2022.
now in order to filter on any account we want to use INDEX and MATCH functions but we need to do a little trick by using ROW and COUNTIFS functions. now, temporarily let's move to cell F7 and write: Equals ROW, F1. The result is number 1 because the cell F1 is in the first row. If we fill series down, we will get 2,3,4…. etc. Now let's move to the journal entries sheet and put data we can use it in other equations for example: in cell G1 we can put the value of cell H5 in the statement of accounts sheet in order to take the account name which, we need to look for.
in cell H1 we need the value of cell h13 in the statement of accounts sheet to take the report Beginning Date in cell I1 we need the value of cell h14 in the statement of accounts sheet to take the report ending date. The report must take dates Greater-than or Equals the Beginning Date and less-than or equals the ending date. So, we will put greater than or equals in H2 and less than or equals in I2 now in cell G3 we write the equation: Equals COUNTIFS the first range is from C2 with absolute reference to C2 with relative reference to make the range expands when we fill series down.
The first criteria is cell G1 is with absolute reference. The second range is from cell A2 with absolute reference to A2 with relative reference. The second condition is greater than or equals opening date in H1 and for that we are going to add the two cells H2 and H1 with absolute reference with the "&" symbol between them because excel considers them as a text. The third range is from cell A2 with absolute reference to A2 with relative reference.
The third criteria is less than or equals to ending date it’s the value of cell I1 and for that we are going to add the two cells I2 and I1 with absolute reference with the "&" symbol between them. If we make a series down excel will give us number 1 in front of the first value that match, and then repeat the number 1 until the second value match. If we write a date such as November 1, 2022 and purchase office equipment costing $60,000 cash The first condition didn’t change because the date isn’t in the range required If we change the ending date to November 1, 2022 we will find the number will become 2 Now let us move to sheet statement of accounts Remove these items, we don’t want them.
We’ll let cell A7 Empty now for opening balance later. In cell A8, we are going to insert an equation temporarily, it’s MATCH function. To Look for cell A6 with relative reference to the Column and absolute reference to the row Comma Look for the value in the range from A1 to F1 in the journal entries sheet with absolute reference Comma We put the value 0 for exact match the value here is 1 which means that the date comes first in the required range If we fill a series to the right, it will give us 4 which means that explanation comes in 4th in the range and like that Now let us open an office clipboard to make it easier to copy and paste. let us copy the equation without the equal sign, we find that it appeared in the office clipboard this equation shows the column number. Now let us delete that equation and insert a new one that express the row number = MATCH(ROW( A1) which value is 1 Comma We look for the value in column G in the Journal Entries sheet with absolute reference Comma We put the value 0 for exact match. it will give us 3 which is the number of the row required value.
Now we will copy the equation without the equal sign, it will appear in the clipboard then we'll delete it. Now we need INDEX function Equals INDEX The matrix that we are looking for is from column A to column F in the Journal Entries sheet with absolute reference The row number is the last equation Comma The Column number is the earlier equation Just I click on them in the clipboard We fill a series to the right Change the format of the numbers to date and accounting Then a series down we will find that excel will give us error for every empty value To avoid that we have to add IFERROR function in the beginning of the formula after an equal sign And at the end of the equation, we put comma then put two quotation marks for excel to replace any error with an empty space. Fill series to the right Fill without formatting. Fill series down Here we done. In cell A7 we have to put the Beginning Date Simply equals H13 we change the format of the cell if necessary. In cell B7 we have to put Opening Balance Simply equals G8.
We want to get the opening balance of with SUMIFS function. = SUMIFS The range we need to sum is column B in the journal entries sheet with absolute reference Comma the first criteria range is column C in the Journal Entries sheet with absolute reference Comma The first criteria is cell G1 in Journal Entries sheet with absolute reference, which express the account name. The second criteria range is column A in the Journal Entries sheet with absolute reference The second criteria is that the date have to be less than the beginning date and for that we write the symbol < (less than) between two quotation marks then an and symbol (&) then we select cell H1 in the Journal Entries sheet with absolute reference Fill series to the right to take the same value for credit Like we did before, we’ll separate debit and credit with MAX an MIN functions. We can open the clipboard.
Copy the SUMIFS formula without equals In the debit equals MIN, paste the formula, comma, zero. We have to put minus before MIN to let the number be positive. In the credit equals MAX, paste the formula, comma, zero.
The first balance in cell E7 = - C7 + D7 The second balance in cell E8 = E7 - C8 + D8 If I fill series the excel give me errors, so, we have to put IFERROR function after equals Put our formula between two parentheses. Comma Two quotation marks Let's complete the report data In A2 write Statement of Account. In A3 we want dynamic expression of report start and end date Equals from space between two quotation marks then we put "&" symbol then TEXT Function The value of the cell H13 Comma The format of the number between two quotation marks Here we want date To display the month like December we have to put 4 m then d To give us the day number then comma and a space And then 4 y In order to show the whole year then an and symbol (&) Then Space, to, space between two quotation marks and then again TEXT The value in cell H4 with the same format In cell A5, we combine the content of cells H5, and G5 =G5, "&" symbol, space between two quotation marks, "&"symbol, H5 Then let’s fill this summary in Opening Balance equals cell e7 In Debit we take the sum of column C and subtract cell C7 from To make the value negative, after equals we put minus and put the formula between two parentheses. In Credit we take the sum of column D and subtract cell D7 from.
In the Ending Balance, we take the sum of the range from H8 to H10 We used the camera to take a picture of this summary and put it at the top of the report. We can use Center across selection as we did before And set print area also to avoid printing unnecessary data. Now, we’ll create financial statements sheets The easiest one is the retained earnings. I will start with it.
Let’s put these data in: Retained earnings statement Retained earnings, January 1 Add: net income Less: cash dividends Retained earnings, December 31 In A1 I want a company name the value of cell B1 in the assumptions sheet. In A3 I want a dynamic expression from assumptions sheet. I will write it in the assumptions sheet and then cut and paste it here. this way is easier In any cell write equals Quotation, for the, space, quotation And symbol, B4, and symbol Quotation, space, ended, space, quotation, and symbol, text B5, with date format Between two quotation marks mmmm d, comma, yyyy. to cut the formula Ctrl and x Turn to retained earnings sheet In A3 Ctrl and v Then do some formatting To look for beginning balance for retained earnings We have to look for retained earnings account in the chart of accounts sheet Like I did before I will cut and paste the formula.
We need VLOOKUP function In any cell in the chart of accounts sheet =VLOOKUP The lookup value is Retained earnings between two quotation marks, comma The table array is the range from A to E columns The column index number is 5. Then we must put false for exact match. Then cut Go to cell C4 in retained earnings sheet Paste. The net income value is revenues minus expenses.
But here the revenues are with positive numbers and the expenses are negative All of them are in the income statement. So, we can use SUMIF function. Equals SUMIF The range is the column D The criteria is income statement between two quotation marks.
The sum range is the column E Cut And paste in the cell C5 in the retained earnings sheet. Also, dividends we have to look for in the chart of accounts sheet Equals VLOOKUP Lookup value is dividends between two quotation marks The table array is from column A to column E Column index number is 5 False for exact match Cut and paste Then format painter Retained earnings at December 31 is Sum range C4:C7 Some formatting to the cells Here we done. Now let’s create the income statement sheet.
But here I will make a single step income statement I will make a multiple step income statement in another video. I explained the income statement in a video before To make an income statement sheet I will insert pivot table from the chart of accounts sheet Go to insert tab Pivot table Here we have a new sheet. Let’s rename it Income Statement Now let’s select main type Because we have single step income statement, we don’t need the account type Then accounts We have to put the statement in the filter field. And the balance in the value field. Then we have to make filter and select income statement.
Let’s put the revenues before the expenses. Just by Drag and drop Now change the format of cells to accounting. Delete sum of balance and write space from formula bar And row labels also We want to replace grand total with net income from the formula bar Double underline for the net income Go to design tab Subtotals Select show all subtotals at bottom of groups Replace revenues total with Total revenues Underline for total revenues and total Expenses After doing some formatting Select row 1 Insert three new rows for the tittles Go to retained earnings sheet Copy the tittles then paste here We will replace the statement of retained earnings with the income statement. And hide row 4 Add dollar sign to the net income. Now I will create a help sheet for balance sheet presentation. I will copy the chart of accounts sheet and edit its data First, right click on the sheet tab Move or copy Create a copy Ok Move the sheet before the chart of accounts sheet.
Rename it to help Let’s fill data depends on the chart of accounts sheet. We just need the balance sheet accounts. In A2 =If If cell D2 in the chart of accounts sheet with absolute reference for column and relative reference for row Equals Balance sheet between two quotation marks If that’s true give me the value of A2 in the chart of accounts sheet If that’s false give me two quotation marks. Fill series down Then fill series to the right without formatting.
Here we have problem with retained earnings account. We want its balance from the retained earnings sheet. Alt + enter after equals to split the formula. If A2 in the chart of accounts sheet equals Retained earnings between two quotation marks. If that’s true give me the value of the cell C8 in the retained earnings sheet with absolute reference Comma Enter The value is updated.
All assets have negative balances and we want them positives Drag and drop the formula bar Alt and enter If b2 = assets between two quotation marks then Let’s cut our old formula If that’s true, we put minus and paste the formula to have the opposite number If that’s false Comma Paste the formula to have the original number. Here we done Now let’s create the balance sheet, “sheet” From help sheet insert pivot table Select main type Then account type Then account name Move the balance to the value field Here we have to change the formula Press on count of balance Value field setting Select SUM Replace row label with space and sum of balance also. Move liabilities to be above the equity After do some formatting Go to design tab Subtotals Select show all subtotals at bottom of group Replace, for example, current assets total with Total current assets All totals will be updated From design tab Grand totals Select off for rows and columns Now we want to add new calculated field which refer to total liabilities and equity. Press on the cell that countian assets or liabilities or equity Go to pivotable analyze Fields, items and sets Calculated item We’ll change the name Total liabilities and equity Press on liabilities, write plus, press on equity Add Ok click the minus sign to collapse the detail.
Now let’s make some formatting And insert three rows for titles Copy the titles from income statement sheet Replace income statement with balance sheet We need the date only from the expression So, we delete all text before the text function Hide row 4 Now we want some links to make the navigation between sheets easier We go to assumptions sheet From insert tab Select illustrations Shapes Any shape you want We’ll write chart of accounts in it Make some formatting Right click on the shape Link Place in this document Select chart of accounts If I click on the shape, it will take me to the chart of accounts sheet Repeat the same steps for all sheets Then create a new shape in the chart of accounts sheet And add a link to the assumptions sheet We can copy it to all sheets After finishing We can hide gridlines in the assumptions sheet And freeze panes to fix the shapes in this sheet. That's all for today, if you like this video, I will make another one for multiple step income statement. Waiting for your comments. See you