Export data to an SQLite database, and play with it

Do you want to try to execute an SQL query on the database of your firm?
Now you can: it is possible to export the data of a firm you own into a SQLite database.

You can easily manage the database offline with a program that manages SQLite databases, like, for instance, SQLiteBrowser, available as free / open source software and working for most platforms.

Possible exercises

1. Analyze the structure of the tables to find out the meaning of the fields.
2. Create an SQL SELECT query that shows the journal entries, with date, description, names of the accounts, and amounts.
3. Create an SQL SELECT query that shows the ledger of a specific account.
4. Create an SQL SELECT query that shows the trial balance.

How to prepare a Statement of Changes in Equity

This post has been edited after its first publication, to reflect some users' comments and changes in the application.

In this post, we will see how to get three of the statements required by the IFRS: the Balance Sheet, the Income Summary and the Statement of Changes in Equity.

We will also recap how to record transactions, how to see transaction analyses, how to have a look at the general ledger and at the trial balance, etc.

For our example here, we will refer to the online version of the book Introduction to
Financial Accounting, by Henry Dauderis and David Annand (ISBN 978-0-9936701-0-7), which is released under a Creative Commons License, Attribution–Non-commercial–Share Alike 4.0.

The second chapter of the book, The Accounting Process, explains the meaning of accounts, what is a transaction analysis, and how to use formal accouting records.

If we want to follow the example of the book, we can start by forking (duplicating) the provided "Basic Canadian Corporation", which already has the complete set of accounts and the configuration needed.

After confirming the creation of the new firm, we can change the settings (the name of the firm and the comment, for instance).

With our brand-new firm, we can have a look around. Of course, since the journal is empty (there are no transactions recorded), the statements will be empty, and so will be the general ledger and the trial balance.

If we are curious, we can have a look at the Chart of Accounts, just to check that there is one. We can notice that the accounts are grouped in categories and ordered by codes. We might guess that grouping is done by codes (Petty Cash is a child of Current Assets because the first part of its code, up to the last dot, is the same), and we would be right.

For each account, there is a position (P for Permanent Accounts, T for Temporary Accounts, etc.) and an ordinary Outstanding Balance, that can be used for some checks (for instance, it wouldn't be normal that Petty Cash has a credit outstanding balance, would it?).

On the right side of the page, we find a menu called Operations, which lists some things we can do with the Chart of Accounts:

We can create a new account, we can import accounts (copying and pasting from a spreadsheet, for instance), we can export our accounts list, we can synchronize our chart of accounts with the one of the firm we forked our one from. By the way, this is why when we create a firm we don't "duplicate" it but "fork" it: a link to the parent firm is kept so that, if the chart of accounts of the parent firm is modified, we can easily import the changes.

The last operation, Configure, takes us to a section of the program where we can have a look at how the accounts are, not surprisingly, configured.

Basically, this section allows us to:

1. define which kind of accounts we can have in our chart of accounts (it is here that we define the P code for permanent accounts, for instance);
2. define which statements we want to be able to prepare.

Now that we have explored a bit around, let's move to the journal (which is empty) and prepare a new journal entry to record the first transaction, like the following (see page 48 and 58 of the book):

After saving the entry, we can have it analyzed, by clicking on the icon Analyze the transaction:

We can also have a look at the General Ledger, at the Trial Balance and at the Statements, which are prepared with the available data.

If everything looks right, we can proceed with the other transactions, described at pages 48–52 of the book (and formalized at pages 58–59). The public view of our firm should show our journal like in this screenshot:

The Trial Balance shows us the values of page 54 of the book:

Most of the data show on page 55 of the book is now available, and in fact we can get auto-generated Income Statement and Balance Sheet from it (don't look at the Statement of Changes in Equity yet). They are not perfect yet (you may notice that there is a line stating Difference Yet Unexplained for $2,200), but we may be satisfied as we are finished with Chapter 2.

The third chapter of the book, Financial Accounting and the Use of Adjusting Entries, deals with the operating cycle and with the concepts of revenue recognition, accrual accounting, matching principle and adjusting entries.

If we go through pages 103–112 of the book, we find a detail explanation of why adjusting entries are needed and how they look like.

In our journal, it is easy to record the corresponding journal entries, such as follows:

The interesting thing at this point is that we can have a look at the accounts to see the effects of the asjusting entries. For instance, here's the account Unearned Repair Revenue, that shows an ending balance of $100:

If we look at the Statements now, we see that we are closer to what we want to obtain. The Income Statement, for instance, shows that the difference between revenues and expenses is $1932, and that amount is indicated above, in the Balance Sheet, as a difference yet unexplained:

Why is it unexplained? Simply enough, because there is no journal entry that explains it explicitly. In other words, there is a missing link between the Income Statement and the Balance Sheet (through the Statement of Changes in Equity, as we'll soon see).

But that's an easy task. What we need to do is:

1. to close all temporary accounts to an account called Income Summary;
2. to close the Income Summary account to the Retained Earnings account;
3. to close the Dividends account to Retained Earnings.

This process is well described in §F, The Closing Process, of the book (pages 116-118).

Luckily, the application helps us, because in the Journal section we can choose to prepare a Closing Entry:

and then select the appropriate closing option:

We are presented a new journal entry with all temporary accounts closed and the Income Summary accounts selected as destination of the difference:

(The Income Summary account is automatically chosen because it is the only account that has a position code of lowercase t, and we are closing all the accounts with a position code of uppercase T).

In the book there are two separate entries, one for revenues first, and one for expenses later. The net effect is the same, of course. Anyway, if we want, we can delete the rows concerning expenses, save the entry, and prepare a second entry for expenses.

When we look at the journal entries, we see that the closing entry we just added is shown in green.

This indicates that this particular journal entry does not have any effect on the statements that are shown in the Statements section. Why is that? Because the application needs the amount of the balances of temporary accounts to show the statement (the Income Statement in this case) and, if the accounts are closed, the amount is zero; so it just excludes journal entries with this special status.

The second and third entries are not automatically generated (but we will see in an upcoming blog post that we can have them automatically generated, don't worry), so we must prepare them in the traditional way (but we can press the ? key on the keyboard, when we have the amount field selected, to automatically retrieve the amount that closes the Income Summary account).

Now that we have journalized all the transactions and the adjusting entries and have completed the closing process, let's have a look at the statements.

The Balance Sheet and the Income Summary do not present any surprise, because they look like they were before (but we may observe that there is not anymore a "difference yet unexplained", since the Retained Earnings are part of Equity and are shown in the appropriate place).

How does the Statement of Changes in Equity look like?

You may have noticed that, before the completion of the closing process, the statement was like this:

Now it is better, because the Income Summary is taken into account:

The amounts shown are not taken from account balances, like for the Balance Sheet and the Income Statement, but straight from the journal entries. You might recognise that the first column is filled with their descriptions, with the "To record" part stripped away. How is it so?

If we get back to the configuration of the Chart of Accounts we can see how the item for the Statement is configured:

The main point here is that the format is set to "Statement in analytic format": this means that the journal entries are analyzed to compute the needed data. The other two kind of formats (pancake format and two separate sections) are used for the Income Statement and for the Balance Sheet, respectively.

In the comment field, you can see that there is:

1. a caption, which is the text that appears after the name of the statement, in the title
   (the "{date}" part is automatically replaced by the date of the last journal entry found);
2. a strip tag, in form of a regular expression used to replace some text from the journal entry description (you don't need to know how regular expression work to use them: it is enough to kwow that this particular one means that the application should replace every text that begins with "To" + a word, like in "To record", "To close", etc.).

As children of the item Statement of Changes in Equity there are two other items, Share Capital and Retained Earnings, that represent the needed columns in the statement. Each of them, in the comment field, has the list of account to analyze (hence the name of the format of the statement). So, for instance, the first one has the following "analyze" tag, that tells the application to consider accounts with codes 320 (Share Capital) and 325 (Preferred Shares).

Last thing, now. If we want to obtain better values for the first column, we can add extra comments in the journal entries involved, in the lines of the single postings (just put a hash sign after the account name, with a comment afterwards). This way, the comment is taken into consideration, instead of the journal entry description.

Ok. Now the statement looks nicer (and similar to the one on page 114 of the book):

We are done. If you have comments, corrections or hints, just write a comment here below in the blog or contact us through the form on the website.



How to prepare a Statement of Changes in Equity on LearnDoubleEntry.org by Loris Tissino is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
Based on a work at https://business.athabascau.ca/assets/Introduction-to-Financial-Accounting-online-text.pdf.

Sample exercise 03

In this third sample exercise, we are going to illustrate some other features of LearnDoubleEntry.org. The example is taken, with authorization, from AccountingCoach.com. We won't get into details about the why's of things (that you'll find on their website, with a deep explanation), but we'll concentrate on how to do this kind of exercises with a virtual firm that you "own" on LearnDoubleEntry.org.

Let's begin. We are given the following preliminary balance sheet, which reports the account balances prior to any adjusting entries:

Preliminary Balance Sheet - courtesy AccountingCoach.com

To start the exercise, you need to:

1. login to LearnDoubleEntry.org
2. go to the Bookkeeping and Accountancy page
3. click on "Fork an existing firm" menu item
4. click on "Standard Firm"
5. choose to duplicate the Chart of Accounts
6. click on the "Yes, fork this firm" button
7. set a name and a description for the firm
8. click on the "Save" button, and you are done.

After firm creation, you can click on the "Journal" menu item, and then choose to create a new "journal post" (aka journal entry, aka transaction). You will be shown a form like the following:

Instead of writing the single lines here, you can:

1. switch to raw input mode

2. load all accounts from the chart of accounts

3. copy the account names to a spreadsheet (first select the whole text of the box, then copy, then paste on the spreadsheet)

4. find the accounts in the spreadsheet, and write the amounts of debits and credits (if you don't find an account with exactly the same name, choose a one that has the same meaning and don't worry: you can edit the name later; but if you really don't find an account, just add a line with the missing name with an exclamation mark at the end, like we did here for Prepaid insurance); pay attention to the fact that the totals of debits and credits must balance; you can, if you want, delete the lines of unused accounts

5. copy the selection from the spreadsheet back to the web form, and click on the "Switch to text fields mode" icon

6. since you probably want the accounts with the debits first, just click on the "Sort postings, debits first" icon

7. save the journal entry, and you are done. Easier done than said, isn't it?

If you look at the journal now, it looks ok. We used Supplies Inventory instead of Supplies, Bank Checking Account instead of Cash, Bills Payable instead of Notes Payable, Payments on Account from Customers instead of Unearned Revenues, Employment Expenses Payable instead of Wages Payable, and Common Stock instead of Mary Smith, Capital.

If this is fine for you, well. Otherwise, you can edit the Chart of Accounts with the names that you prefer.  The Trial Balance looks like this now:

As you can see, the Prepaid Insurance account has been added on the fly, and you find it marked in yellow at the bottom of the table. To fix the problem, you just have to assign a correct code to the account. Since you already have a Prepaid Expenses account, you can choose to add another account as its sibling with a code like 00.03.06.03. This way, the account will be a child of the Accrued Income and Prepaid Expenses account, which has code 00.03.06.

Other choices would be possible. For instance, you can make Prepaid Insurance a child of Prepaid Expense, by entering a code like 00.03.06.02.01. This way, Prepaid Expense would not be usable as such, because it would have some children.

It's up to you how to organize your Chart of Accounts, and which names to give to the accounts themselves. Just consider that:

• once you edited the Chart of Accounts of one firm, you can use it in other firms that are forked from it;
• if you spot any problem in the Chart of Accounts provided for the system-provided Standard Firm, you can add a comment in this blog or drop us a message, and we'll try to fix it.

Edit the account names as you like. We only changed Common Stock to Mary Smith, Capital.

When you are done, you can start entering the journal entries of the example exercise provided by AccountingCoach.com. (We added an extra journal entry with some amounts for Income Statement accounts at the beginning.)

For each journal entry, you can click on the "Analyze the Transaction" icon, that shows a little explanation of what is going on. For instance, when you make the adjusting entry about bad debts valutation, you can see that you have an increase in Expense (Bad Debt Expense) and an increase in an Asset Contra Account (Allowance for Doubtful Debts).

When you want, you can have a look at the T-Account (called Ledger in the application) of any account. It's not actually T-shaped, but the meaning should be obvious anyway.

You can find the complete exercise in its public page, and you can fork the firm by using the slug "sample-exercise-03".

Sample exercise 02

Here's another sample exercise that explains how to use LearnDoubleEntry.org. We'll use the example provided by prof. Joost Impink at learn-accounting.com (on the site you'll find more accurate explaination than here), that kindly autorized us to do so.

Just to show something different, this time we will create a firm from scratch, so we'll have an empty chart of accounts.

Transaction 1
The firm is incorporated on the 1st of January. The owner, Betty, pays 40,000 cash for 10,000 shares.

Since we do not have any account yet, we can create a new journal post and put an exclamation mark after the name of the account (e.g. "Cash!").

Transaction 2
ABCD Inc. buys a Grasshopper lawn mower for 8,000 cash.

Since the account "Cash" has already been created, we won't need to add an exclamation mark for it. When typing the first few letters of the name of the account, we'll have a list of accounts matching the name. Don't care about the code, for now.

Transaction 3
ABCD Inc. pays 500 cash for advertising in the local newspaper.

Transaction 4
ABCD Inc. receives 3,000 cash for services delivered in January.

Transaction 5
ABCD joins the Association of Landscapers, and receives an invoice of 400 to be payable in February.

This time, the account Operating expenses already exists, and will show up when you type its name.

Transaction 6
The company pays a 1,000 cash dividend.

After recording the above transactions, your journal will look like this:

The little bells are there to warn you that the accounts have not been yet correctly positioned in the chart of accounts. The same goes for the trial balance:

Notice that the codes are generated randomly, and have a ~ sign at the beginning. This goes for all accounts created with the exclamation mark trick.

If you look at the chart of accounts now, you'll find that there is actually no position and no ordinary outstanding balance set for any of the accounts:

We can easily fix that. Not only we want to correctly position the accounts, we also want to group them in clever ways.

We can start from creating a new account, named Assets, that will group together some of the accounts (Cash, Equipment, etc.). Let's give this account a "01" code, a name, and choose the right position and ordinary outstanding balance (the latter is used for some checks, warning if you make mistakes):

Then, we want to "move" the Cash account under the Assets account. It's as easy as changing the Cash account's code to "01.01". The dot here stays for a parent-child relationship between accounts. You can do something similar with the Equipment account (say you give it a "01.02" code).

The same goes for Liabilities, that will contain Accounts payable, and Equity, that will contain Dividends and Paid-in capital.

You can group together also accounts that express expenses and revenues, and you have multilevel groups. At the end of the process, you could arrive to something similar to this:

The "position" means that the net amount of the account will be shown up in the Balance Sheet (BS) or in the Income Statement.

Actually, we can already see how these two statements look like so far:

You can find the complete exercise in its public page, and you can fork the firm by using the slug "sample-exercise-02".