+ Accounting Without Tears

Accounting Without Tears

"What one fool can do, another can."
- Sylvanus Thompson

Introduction

Double-entry bookkeeping is one of those subjects where you learn lots of terminology, read descriptions of how the parts work, get confused, and then suddenly have an "ah ha" experience. The trick is to have the "ah ha" before a "why bother?" The goal of this introduction is to get the key ideas across quickly so you're motivated to continue.

I've run several businesses and even a tiny corporation with the most informal accounting methods you can imagine. To survive and stay out of jail, I paid accountants and bookkeepers. They were always glad to see me come in with my plastic bag full of receipts, invoices and statements. My exposure to real accounting methods was limited to hearsay and some historical reading. I came away with the impression that debits and credits had something to do with uneasiness about negative numbers. Double entry bookkeeping, I thought, was concerned with error checking so clarks and scriveners in counting houses had a way to verify manual calculations.

These impressions, along with being intimidated by Schaum's Outline of Accounting kept me clear of understanding elementary bookkeeping for thirty years. And I was wrong: double-entry bookkeeping is actually a very simple and cool idea. It provides organization to the aggregates and flows of money that keep a business running. It not only tells you how much money you're making, it shows you how much you can afford to spend, how fast you're growing, what products are moving, how much you should set aside for taxes, what bills are unpaid, and host of other insights. It can even balance your checkbook.

When I decided to deal with my ignorance once and for all, I spent several weeks surfing the internet reading articles and tutorials. I read all the applicable "Dummies" books and paid a real CPA to get me through the fine points. Next, I looked at entry level bookkeeping systems like Quicken and some free alternatives like "GNU Cash." I even went out to dinner with a SAP goddess to learn about the high end. (SAP costs more to install than I earn in a year.)

The problem I had with most books and articles about accounting is that they're often written by people who sell software or services as accountants. They don't want things to look too easy. It's also the case that accountants are temperamentally meticulous people. They don't like to leave any detail or definition unstated. They present every conceivable term and contingency before showing how to handle the most elementary accounting events.

I tried several practical methods: first leather-bound ledgers and journals so I could literally see how things worked. (Quill pens, by the way, are really messy.) Next, I made a spreadsheet so complex I couldn't explain it to myself or my accountant. He very politely thanked me for all my work and quietly threw the whole thing away. I got a big bill me for "bookeeping services" on top of his regular tax preparation fees. I finally hit on the right tool, a popular free database. The light came on and I ended up with only one page of code that did everything I needed. (A symptom, no doubt, of my limited imagination.) You can read it in five minutes and understand everything in one hour.

To be honest, this article explains what would be only a small part of a commercial accounting system: the journal and the general ledger - terms we will define shortly. And even that part I'm sure, will be condemned by the professional accountants as oversimplified, dangerous, immoral, and inappropriate even for the tiny business it describes. Please keep the limited goals in mind: This article is written for fools like me who want to completely understand, design, and operate their own accounting system, keep their own books, and gain insight into how their business is running. It is not a guide to help you do accounting for a public company.

The Big Picture

"What advantages does the merchant derive from Book-keeping by double-entry?
It is amongst the finest inventions of the human mind."
- Johann Wolfgang von Goethe

Virtually everything in the foundation of modern accounting was first described by the Italian Rennaissance genius Luca Pacioli. (Pronounced "pot-CHEE-oh-lee") His book Particularis de Computis et Scripturisi, published in 1494 explains journals, ledgers, debits, credits, account types, and every detail of double entry bookkeeping along with a depressingly modern understanding of security measures. Our first steps will be introducing the terms and account types identified by Pacioli more than 500 years ago.

Fra Luca Pacioli

An account is a named pile of money along with a record of the increments and decrements to the pile. The amount of money in the pile is called the balance. Accounting is the body of knowledge connected with the art of organizing money into piles and orchestrating the flow of money between them. The purpose of all this shuffling is to make better business decisions. Like "How much should I set aside for taxes?" Or "How many weevil spritzers should I stock next year?

The flow of money between accounts occurs in discreet units. These units are the "bytes" of accounting. Because we don't want to lose money, every time a "byte" leaves one account, it must go into another. This movement is called a transaction. One or more transactions are triggered each time an accounting event occurs. You are very familiar with these events because they occur every day: Every time you buy something, write a check or place an order. Every time you borrow money, pay the phone bill, or reconcile a bank statement. All these are accounting events.

So an accounting system is a state machine: The state is the set of all accounts and their balances. The transitions are the movements of "bytes" (actually called credits and debits) between the accounts. The events that trigger them are inputs and outputs from the business world: orders, bills, statements, etc.

An accountant chooses the set of accounts and the rules that determine what transactions should occur for each kind of accounting event.

1903 F. W. Woolworth ledger

Aside: In the days of paper and pen bookkeeping, accounts were kept in big physical volumes called ledgers. These were often massive hand-bound tomes with tooled leather spines, gold stamping, heavy cover boards, and high quality paper. The vertical lines to mark off rows and columns were drawn (not printed) with colored ink. The drawing was done by special ruling machines, which are manufactured and used today. The name General Ledger is still used to describe the set of all accounts no matter how they are represented.

A page from the Woolworth ledger

What about double-entry bookkeeping? That's a simple concept that follows naturally from these ideas: Each transaction involves two accounts: A sum of money leaves one account and enters another. This is the "conservation of matter" law of accounting. Entry number one is the sum that leaves the first account. Entry number two is the sum that enters the second account.

We glossed over and over-simplified a great deal at this point, but if you quit reading now, you're better off than when you started.

Shaw pen ruling machine

Accounts

"Debitore ala sinistra e credito ala dextra."
- Pacioli

We defined an account as a named pile of money. Now, we refine the definition: An account is a named sequence of transactions. Each transaction either adds or subtracts a sum of money. The balance is a computed by adding up all the transactions. An account named "Liabilities/Checks" might look like this:

Liabilities/Checks
DebitsCredits
500.00
100.00
250.00
500.00
100.00

In this example, the Liabilities/Checks account is used to keep track of checks the business has written but not yet reconciled. They are liabilities because someday, when the check clears, they will be paid out of our checking account.

Each line in the account shows either a debit or a credit. By ancient convention, debits are always listed on the left, credits on the right. For now it's sufficient to know that the transactions occur sequentially in time. Later we will refine the definition to include a date.

The first three lines reflect writing a series of checks for $500, $100, and $250. At this point, the account would have a balance of $850 - That's how much will eventually be deducted from the bank balance. On the last two lines, we see debits of $500 and $200. That happens when the bank statement arrives and shows that the first two checks have cleared. The balance of the account is now $250. When that check clears, the balance will be zero.

At this point you may ask yourself, "Self, why would making a deposit be a debit, while writing a check is a credit? That seems backwards..." The answer gets at a very basic concept in bookkeeping: The interpretation of the balance depends on the type of account. In this case, we are considering a liability: larger balances mean we have a greater liability, smaller values, less liability. Later, when we consider asset accounts, the opposite interpretation is needed.

Burroughs 1906 Model 9

Account names

Accounts are organized in a hierarchy, just like the files and directories on your computer. The "files" are the actual accounts that have balances, credits and debits. The "directories" are just names used to organize the tree. So it's reasonable to use path notation. Here are some typical account names:

		Assets/Cash
		Assets/Checks
		Assets/Account Receivable
		Assets/Office Building
		Assets/Bonds/North American Weevil Flitzer 2018
		Expenses/Airfare
		Expenses/Lodging
		Expenses/Meals
		Expenses/Phone
		Liabilities/Checks
		Liabilities/Cards/Visa 2231/Charges
		Liabilities/Cards/AMEX 50003/Charges
		Liabilities/AT&T
		Equity/Capital
		Equity/Retained Earnings
		Revenues/Sales
		Revenues/Interest

The top-level (leftmost) account names and their purposes are conventional.

Assets
Money and property the company controls.
Expenses
Money paid to others.
Liabilities
Money owed to others.
Equity
Money owned by the company.
Revenues
Money received from others.

As we go deeper in the tree, the names become less a matter of convention. They are chosen by the accountant who sets up the system for a particular type of business. The entire set of accounts is called the Chart of Accounts.

When talking about the balance of an account, we can specify the full path, for example "Liabilities/Cards/Visa 2341/Charges", or we can specify a higher point in the tree such as "Liabilities/Credit Cards." In that case, we mean "the sum of the balances of all the lower level accounts." In this example, it would be the balance on all credit cards. We can use this concept for the top-level accounts, so there is a well-defined balance for "Assets" - the sum of all accounts with "Assets" as their left-most name. For that reason, we call all the nodes of the tree accounts, even though only the leaves of the tree contain lists of credits and debits.

NCR Class 32 posting machine

Double entry bookkeeping

"Without mathematics, there is no art."
- Pacioli

In the introduction, it was stated that accounting events satify a "conservation of matter" law: What is removed from one account must be added to another. This is the basic idea of double-entry bookkeeping. It allows us to accomplish three goals:

  1. Find out if a bookkeeping error has occured.
  2. Find out where money is located as it moves from one account to another.
  3. Understand the financial health of the business.

To accomplish these goals, the top level account balances are designed satisfy:

The fundamental equation of accounting

Assets + Expenses = Liabilities + Equity + Revenues

When an accounting event occurs, we peform one or more transactions. In each transaction one account is debited and another account is credited. Because we want the accounting equation to remain true no matter what pair of accounts is selected, the following rules determine how debits and credits should change the balance of an account:

The fundamental rules of bookkeeping

AccountDebitCredit
AssetsIncreaseDecrease
ExpensesIncreaseDecrease
LiabilitiesDecreaseIncrease
EquityDecreaseIncrease
RevenuesDecreaseIncrease

The rules are easy to remember if you already know the equation: Accounts on the right side of the equation have the normal, everyday, right interpretation:

balance = credit - debit

Accounts on the left side of the equation, (think, "left-hand path") have the sinister, wrong, backward, evil interpretation:

balance = debit - credit

To see how all this works, consider two accounts, A and B. We will debit A and credit B. There are only two cases: A and B are on the same side of the equation or they are on opposite sides of the equation. In the first case, the debit and credit will have opposite effects on the balance. Since A and B are on the same side, the balance is maintained. In the second case, A and B are on opposite sides. The debit and credit will each have the same effect on the balance of A and B, again maintaining the balance.

Let's do a literal example: pretend that we only have the five top level accounts and do some double-entry transactions. These cases are representative of all the situations that occur. Please do the math in each case to verify the equation:

		0) Initial conditions:

			Assets(100) + Expenses(20) = Liabilities(50) + Equity(50) + Revenues(20)

		1) Debit Liabilities 10 and credit Assets 10: (opposite sides)

			Assets(90) + Expenses(20) = Liabilities(40) + Equity(50) + Revenues(20)

		2) Debit Liabilities 10 and credit Equity 10: (both on the right)

			Assets(90) + Expenses(20) = Liabilities(30) + Equity(60) + Revenues(20)

		3) Debit Assets 10 and credit Expenses 10: (both on the left)

			Assets(100) + Expenses(10) = Liabilities(30) + Equity(60) + Revenues(20)
	

"The cosmic balance is maintained!"

Another principle, easy for the computer to check, is that the sum of all credits in all accounts must equal the sum of all debits in all accounts. If they don't, you've made a mistake. Pacioli reminds us that "A businesman should never sleep until the credits equal the debits."

Financial reports

At the end of each year, we want to know if the business made or lost money. We also want to know how much the business is worth. Financial reports provide this information along with other useful metrics. Here are some typical reports:

  1. Income Statement
  2. Balance Sheet
  3. Cash Flow Statement
  4. Finanical ratios

The income statement

The Income Statement is the easiest to understand: It shows how much money you've made: That's just the difference between your Revenues and Expenses.

Income = Revenues - Expenses

Revenues, of course, are the sums of money people give you for your products or services. Expenses include things like rent and wages you pay along with all the materials and services you purchase to make your product.

Real and nominal accounts

The Equity and Revenues accounts get reset to zero at the end of each business year. The net income, if any, is credited to Equity/Retained Earnings. If there is a loss, it is debited from Equity/Capital. (The details of this are presented later.) This is done so we can evaluate relative business performance year to year and also to calculate our taxes. Because these accounts are "reset", they are called nominal accounts as opposed to real accounts. Real accounts preserve their balances year to year. In the days of pen and paper bookkeeping, it was conventional to start a new book (a physical book) each year. The real accounts were sometimes called carry over accounts because their balances had to be copied into the new account book. They are also called balance sheet accounts because they appear in the balance sheet report.

The balance sheet

After closing out Revenues and Expenses, the accounting equation has only three non-zero components. The Balance Sheet shows the the balances of Assets, Liabilities, and Equity and affirms that they satisfiy the equation:

Assets = Liabilities + Equity

One way to understand this expression is to say that assets (all the money the business has on hand) is the sum of liabilities (money owen to others) and equity (money owned by the business.)

At other times of the year, we can check the whole equation for balance:

Assets + Expenses = Liabilities + Equity + Revenues

The fun had just begun

In the era of software accounting, it's fairly difficult to get the books out of balance, but the balance sheet accounts still tell us a lot about the overall size and performance of the business. This is done by examining financial ratios.

Assets may include illiquid items such as real estate, bonds, or machinery. A company without liquid short-term liquide assets has no way to operator or pay the bills. To deal with this, the balance sheet report may classify certain sub-accounts as short-term or long-term. This help investors assess the viability of the business.

Financial ratios

Financial ratios get at the heart of how accounting is used to guide business decisions. There are dozens of these things, some only of interest to those running a public company who need to befuddle investors. Here's a sample along with very basic interpretations. Books are written on these topics.

Liquidity measurement

Liquidity tries to get at the question: Can this company pay the bills and remain solvent? Because Assets includes Cash (the bank account) as well as things like buildings and machinery that aren't very liquid, we define two ratios. Values greater than one are a Good Thing.

Current ratio
balance("Assets") / balance("Liabilites")
Cash ratio
balance("Assets/Cash") / balance("Liabilites")

Profitability measurement

Here, profit margin is the most basic. Numbers greater than one are Good. The other ratios describe efficiency. It is easy to make money if you have piles of Assets and Equity, but it's efficient to do more with less. Large profits almost universally indicate exposure to risk. Small profits imply poor management (when only your company has poor numbers) or a downturn in the economy (when everybody in your business has poor numbers.)

Profit margin
balance("Income") / balance("Revenue")
Return on assets
balance("Income") / balance("Assets")
Return on equity
balance("Income") / balance("Equity")

Debt measurement

Debit gets at the question of long-term survival and stability. When a company has large debit ratios, it is said to be leveraged. Numbers less than one are generally a Good Thing, and numbers less than 0.5 are considered safe. Numbers greater than one, for profitable companies, are exciting and usually short-lived.

Debt ratio
balance("Liabilities") / balance("Assets")
Debt-equity ratio
balance("Liabilities") / balance("Equity")

Because we are free to make accounting transactions that preserve the fundamental equation, it is sometimes useful to move things around to improve some ratios, usually at the expense of others. This is playtime for CFOs right before the end of the quarter.

Accrual-basis vs cash-basis accounting

When you set up a business and accounting system, a basic decision is the choice of accounting basis. The basis determines when revenues and expenses are recognized.

In accrual accounting, you recognize (post to the ledger) revenues as soon as you do the work. Expenses are recognized as soon as you place an order. The goal of accrual accounting is to keep expenses and revenues aligned in time with the work done to generate them. This makes it possible to use cash-flow analysis, a topic we'll cover later. Public companies and business that maintain inventories are required to use accrual accounting.

A complication of accrual accounting occurs at the end of the year, when adjustments are peformed to shift any wayward revenue and expense postings so they line up better in time. After creating financial statements, it may be necessary to "unadjust" these postings so the books preset a more accurate picture of what actually happened.

In cash-basis accounting, revenues are recognized when you get the check and expenses are recognized when you pay the bill. In many respects, using the cash-flow basis is easier and usually more appropriate for single proprietorships.

A complication of cash-basis accounting is keeping track of the work you've done before you get paid and expenses you've incurred before the bill arrives. These needs can be satisfied by introducing clearing accounts to hold money until it can be recognized as a revenue or expense.

Cash flow statements

An interesting phenomenon in business is finding out that you can't pay your bills even though you have a positive net income every month - possibly far more than enough to cover them. Lenders can be quite testy about this sin and small firms are sometimes liquidated when it happens.

To stay out of this kind of trouble, we use the Cash Flow Statement. It is usually presented aa a time series showing the value of the following expression at the end of each month or quarter. To make cash flow analysis work properly, the business must use the accrual basis so the components of cash flow occur inside the period being measured:

		Cash flow = balance('Assets/Cash') - [ balance('Revenues') - balance('Expenses') ]
	

We are calculating the difference between spendable cash and current net income. When cash flow is consistently larger than your typical monthly bills, you're in good shape. When it's less, you need to more agressive with collections or cut your expenses.

If cash flow is much larger than your typical bills, you are wasting capital: you should be investing the extra cash in growth or revenue generating securities.

So you're wrong if it's too small and wrong if it's too large. This is why CFOs are seldom happy.

A large company's cash flow statement usually calculates three different cash flows: One from operating activitives, one for financial activities, and one for investment activities:

Operating activities
What your business actually manufactures or performs to make money, i.e. the old-fashioned way: by working.
Financial activities
This method of creating cash flow involves issuing new company stock, buying it back, organizing splits or swaps, announcing or retracting dividend payments, and other Wall Street shenanigans.
Investment activities
Making (or losing) money by trading or investing your company's cash in the stock of other companies. The simplest case is having a savings account. If it's big enough, you don't need to fuss with the rest of the business.

The Journal

Quanto alor debito e anche credito.
- Pacioli

It's possible to keep track of a business by directly entering credits in debits into ledger accounts, but it would be difficult to understand when, why, how these transactions occurred. It is also the case that some accounting events require several ledger entries to happen as a group (called a transaction.) Finally, we need a place to add extra information such as the purpose of the transaction and references to supporting documentation to keep the auditors happy. These requirements are fulfilled by an accounting document called the journal.

The journal records a time series of accounting events. Each event has a date and statement of purpose, followed by a list of transactions. To make this idea clear we will now introduce some formal notation for describing transactions. This notation will closely correspond to software you can actually use. The following example is for a lemonade stand. We will create a chart of accounts, start the business and run it for 4 days. Then the transactions for going out of business are shown. Read through the script and get a feel for what's happening. The more obscure points will be discussed in the sections that follow. Because the lifespan of this business is brief, we will do accounting every day that would normally occur once a month.

The script uses three commands:

account(name) Create a new account
journal(date, purpose, document_id) Describe a journal transaction
entry(amount, debit, credit) Modify two accounts: Debit one and credit the other.
All entry statements that follow a journal statement are attached to the same transaction

The language used in the script is the MySQL dialect of SQL: a standard in the database world. Why use a database? There are many alternatives, but the high-level constructs in SQL are a close fit to the way accounting questions are framed. We will see how that works later when we show how reports are constructed.

		# Lemonade Stand.sql - A small business cradle to grave

		# Create the database

			drop database if exists LemonadeStand ;
			create database LemonadeStand ;
			use LemonadeStand ;

		# Load the software

			source OnePageAccounting.sql

		# Create the chart of accounts

			call account('Assets/Cash') ;
			call account('Assets/Change Bowl') ;
			call account('Assets/Deposits') ;
			call account('Assets/Prepaid Taxes') ;
			call account('Assets/Prepaid Interest') ;
			call account('Liabilities/Checks') ;
			call account('Liabilities/Groceries') ;
			call account('Liabilities/Taxes') ;
			call account('Liabilities/Principal') ;
			call account('Liabilities/Interest') ;
			call account('Liabilities/Payoff') ;
			call account('Revenues/Sales') ;
			call account('Revenues/Interest') ;
			call account('Expenses/Groceries/Sugar') ;
			call account('Expenses/Groceries/Lemons') ;
			call account('Expenses/Taxes') ;
			call account('Expenses/Interest') ;
			call account('Equity/Capital') ;
			call account('Equity/Draws') ;
			call account('Equity/Income Summary') ;

		# Get some money

			call journal('2014-06-02', 'Startup', 'Promissory note for $100 to dad' ) ;
			call entry(80.00,	'Assets/Deposits',    'Liabilities/Principal') ;
			call entry(20.00,	'Assets/Change Bowl', 'Liabilities/Principal') ;
			call entry(10.00,	'Expenses/Interest',  'Liabilities/Interest' ) ;

		# Buy groceries

			call journal('2014-06-02', 'Pay for groceries: 65 lemons, 10 lb sugar', 'Check 0001') ;
			call entry(65.00,	'Expenses/Groceries/Lemons', 'Liabilities/Groceries') ;
			call entry(6.00,	'Expenses/Groceries/Sugar',  'Liabilities/Groceries') ;
			call entry(71.00,       'Liabilities/Groceries', 'Liabilities/Checks') ;

		# Start the daily routine

			call journal('2014-06-02', 'Monday sales 32 cups', 'Sales book page 1') ;
			call entry(25.00,	'Assets/Deposits', 'Revenues/Sales') ; # Cash sales
			call entry(7.00,	'Assets/Deposits', 'Revenues/Sales') ; # Check

			call journal('2014-06-02', 'Set aside 5% sales tax', '') ;
			call entry(1.60,	'Assets/Prepaid Taxes', 'Assets/Cash') ;
			call entry(1.60,	'Expenses/Taxes', 'Liabilities/Taxes' ) ;

			call journal('2014-06-03', 'Tuesday sales 27 cups', 'Sales book page 2') ;
			call entry(27.00,	'Assets/Deposits', 'Revenues/Sales') ; # Cash sales

			call journal('2014-06-03', 'Set aside 5% sales tax', '') ;
			call entry(1.35,	'Assets/Prepaid Taxes', 'Assets/Cash') ;
			call entry(1.35,	'Expenses/Taxes', 'Liabilities/Taxes' ) ;

			call journal('2014-06-04', 'Wednesday sales 26 cups', 'Sales book page 3') ;
			call entry(22.00,	'Assets/Deposits', 'Revenues/Sales') ; # Cash sales
			call entry(4.00,	'Assets/Deposits', 'Revenues/Sales') ; # Check

			call journal('2014-06-04', 'Set aside 5% sales tax', '') ;
			call entry(1.30,	'Assets/Prepaid Taxes', 'Assets/Cash') ;
			call entry(1.30,	'Expenses/Taxes', 'Liabilities/Taxes' ) ;

			call journal('2014-06-05', 'Thursday sales 23 cups', 'Sales book page 4') ;
			call entry(20.00,	'Assets/Deposits', 'Revenues/Sales') ; # Cash sales
			call entry(3.00,	'Assets/Deposits', 'Revenues/Sales') ; # Check

			call journal('2014-06-05', 'Set aside 5% sales tax', '') ;
			call entry(1.15,	'Assets/Prepaid Taxes', 'Assets/Cash') ;
			call entry(1.15,	'Expenses/Taxes', 'Liabilities/Taxes' ) ;

			call journal('2014-06-05', 'Take some money home', 'Raid the change bowl') ;
			call entry(13.00,	'Equity/Draws', 'Assets/Change Bowl') ;

		# Begin shutdown

			call journal('2014-06-06', 'Pay taxes', 'Check 0003') ;
			call entry(5.40,	'Assets/Cash', 'Assets/Prepaid Taxes') ;
			call entry(5.40,	'Liabilities/Taxes', 'Liabilities/Checks' ) ;

			call journal('2014-06-06', 'Pay investors', 'Checks 0004 & 0005') ;
			call entry(10.00,	'Assets/Cash', 'Assets/Prepaid Interest') ;
			call entry(10.00,	'Liabilities/Interest',  'Liabilities/Payoff') ;
			call entry(100.00,      'Liabilities/Principal', 'Liabilities/Payoff') ;
			call entry(balance('Liabilities/Payoff'), 'Liabilities/Payoff', 'Liabilities/Checks') ;

			call journal('2014-06-06', 'Liquidate assets', 'Empty the change bowl') ;
			call entry(balance('Assets/Change Bowl'), 'Assets/Deposits', 'Assets/Change Bowl') ;

		# Bank statement arrives

			call journal('2014-06-30', 'Reconcile checking account', 'Bank statement 2014-06-31') ;
			call entry(100.00,      'Assets/Cash', 'Assets/Deposits') ;     # Startup capital
			call entry( 32.00,	'Assets/Cash', 'Assets/Deposits') ;     # Revenues day 1
			call entry( 27.00,	'Assets/Cash', 'Assets/Deposits') ;     # Revenues day 2
			call entry( 26.00,	'Assets/Cash', 'Assets/Deposits') ;     # Revenues day 3
			call entry( 23.00,	'Assets/Cash', 'Assets/Deposits') ;     # Revenues day 4
			call entry( 20.00,      'Assets/Cash', 'Assets/Deposits') ;     # Change bowl contents
			call entry(  0.05,      'Assets/Cash', 'Revenues/Interest') ;   # Interest from bank
			call entry( 71.00,      'Liabilities/Checks', 'Assets/Cash' ) ; # Pay grocery bill
			call entry(  5.40,      'Liabilities/Checks', 'Assets/Cash' ) ; # Pay taxes
			call entry( 10.00,      'Liabilities/Checks', 'Assets/Cash' ) ; # Pay interest
			call entry(100.00,      'Liabilities/Checks', 'Assets/Cash' ) ; # Pay principal

		# Perform closing transactions

			call journal('2014-07-01', 'Closing transactions', '...') ;
			call entry(balance('Revenues/Interest'),         'Revenues/Interest',     'Equity/Income Summary') ;
			call entry(balance('Revenues/Sales'),            'Revenues/Sales',        'Equity/Income Summary') ;
			call entry(balance('Expenses/Groceries/Sugar'),  'Equity/Income Summary', 'Expenses/Groceries/Sugar') ;
			call entry(balance('Expenses/Groceries/Lemons'), 'Equity/Income Summary', 'Expenses/Groceries/Lemons') ;
			call entry(balance('Expenses/Taxes'),            'Equity/Income Summary', 'Expenses/Taxes') ;
			call entry(balance('Expenses/Interest'),         'Equity/Income Summary', 'Expenses/Interest') ;
			call entry(balance('Equity/Income Summary'),     'Equity/Income Summary', 'Equity/Capital') ;
			call entry(balance('Equity/Draws'),              'Equity/Draws',          'Equity/Capital') ;

		# Show the balance sheet before going out of business

			select balance('Assets'), balance('Liabilities')+balance('Equity') ;

		# Write the final check

			call journal('2014-07-01', 'Take the money and run', 'Check 0006') ;
			call entry(balance('Equity/Capital'), 'Equity/Capital', 'Liabilities/Checks') ;

			call journal('2015-07-31', 'Reconcile final checking account statement', 'Bank statement 2014-07-31') ;
			call entry(8.65, 'Liabilities/Checks', 'Assets/Cash' ) ;

		# Show final balance

			select balance('Assets'), balance('Liabilities')+balance('Equity') ; 

Clearing accounts

When buying groceries and paying off investors, the accounts Liabilities/Groceries and Liabilities/Payoff were used to accumulate payments so we could write one check. These are examples of clearing accounts. Clearing accounts are temporary accounts used consolodate sums taken from other accounts. They are temporary in the sense that after their balance is built up, it's immediately sent somewhere else.

		...
		# Buy groceries

		call journal('2014-06-02', 'Pay for groceries: 65 lemons, 10 lb sugar', 'Check 0001') ;
		call entry(65.00, 'Expenses/Groceries/Lemons', 'Liabilities/Groceries') ;
		call entry(6.00,  'Expenses/Groceries/Sugar',  'Liabilities/Groceries') ;
		call entry(71.00, 'Liabilities/Groceries', 'Liabilities/Checks') ;
		...

		# Pay investors

		call journal('2014-06-06', 'Pay investors', 'Checks 0004 & 0005') ;
		call entry(10.00,  'Assets/Cash', 'Assets/Prepaid Interest') ;
		call entry(10.00,  'Liabilities/Interest',  'Liabilities/Payoff') ;
		call entry(100.00, 'Liabilities/Principal', 'Liabilities/Payoff') ;
		call entry(balance('Liabilities/Payoff'), 'Liabilities/Payoff', 'Liabilities/Checks') ;
		...

Closing nominal accounts

When going out of business, we have to close the nominal accounts Revenues and Expenses. That would also be done at the end of every tax year. Because Revenues and Expenses are abstract accounts, we actually have to go in and close each individual account under Revenues and Expenses. This is done by first moving the balances to another nominal account: Equity/Income Summary. Then we move the entire contents of Income Summary to the final destination based on these rules:

  1. If the credits in Equity/Income Summary exceed the debits, we have a profit. (We do in this example.) We move the balance to a real account by debiting Equity/Income Summary and crediting Equity/Capital.
  2. If it turns out that debits exceed credits in the income summary (a loss) we move the balance by debiting Equity/Capital and crediting Equity/Income Summary.

If this little ritual seems unnecessarily complex, you have not see the look of abject horror on the face of an accountant if you mention a negative sum of money.

Owner draws

On the last day of business, the owner got greedy and decided to take out $13.00. This is done using the account Equity/Draws. This is a nominal account that must be closed like Revenues and Expenses at the end of the accounting year (or when going out of business.) When closing, the money is taken directly from Equity/Capital rather than the income summary because draws are not tax deductable.

Draws are our first example of a contra-account. A contra-account has a negative balance. The ancients abhored negative numbers and tried to design accounting systems to hide them along with the evils of subtraction. This is no longer a practical attitude.

Depreciation using contra accounts

Now we add a capital fixture to the business: a large cardboard box we found in the garage. Turned upside down, it makes a great table for our lemonade stand. Unfortunately, Dad has filled it up with his useless golf accessories. He objects to us taking it! Businessmen suffer depredations from every hand. After some tense negotiations, we buy him out for $4.00. Over the next four days of business, the box gets damp from dew and ends up in pretty bad shape. We account for this using the concept of depreciation. Three new accounts must be added to our example:

		Assets/Box/Cost
		Assets/Box/Depreciation
		Expenses/Depreciation

Because depreciating an asset requires extra accounts, it's only worth doing for items that retain their usefulness for a significant time. Most businesses set a price point: above that price equipment will be depreciated, otherwise it is simply expensed.

Before depreciation

There are various ways to handle depreciation and we will only touch on the simplest. It works like this: Divide the cost of the asset minus the salvage value by the number of accounting periods we expect it to be useful. The IRS has slightly different views on this topic, which are beyond the scope of this introduction.

Note: If the asset is acquired in the middle of the accounting year, only part the depreciation for remaining fraction of that year should be claimed. Since we are operatng for only four days, this refinement does not apply.

In our case, we assume the salvage value of a damp cardboard box is zero.

After depreciation

We start out buying the box directly from the cash bowl. Note that we do not expense the box at this time:

		call journal('2014-06-02', 'Buy a cardboard box for the stand', 'Small change') ;
		call entry(4.00, 'Assets/Box/Cost', 'Assets/Change bowl') ; 

During each day of operation, we depreciate the box by $1.00:

		...
		call journal('2014-06-xx', 'Depreciate the box', '') ;
		call entry(1.00, 'Expenses/Depreciation', 'Assets/Box/Depreciation') ;
		... 

Over the four day period, the box gradually contributes to expenses. Assets/Box/Depreciation is another example of a contra account because it has a credit balance. If your accounting software reports the balance of an asset account by printing (debits-credits), you will see a dreaded negative number. Accounts prefer to say that Assets/Box/Depreciation has a "credit balance" which is contra to the normal state of affairs where left-hand accounts have a "debit balance".

On any day the book value of the box is given by:

		book_value = balance(Assets/Equipment/Box)

On the 4th day, we have depreciated the entire cost of the box. The box is then said to have a book value of zero.

Please take a moment to study the above expression: It will yield a smaller value every day as the box depreciates. The two subaccounts, Cost and Depreciation, are canceling each other out.

To prevent ennui, book value is sometimes called carrying value. Note that this value may differ wildly from the market value. (What you could get for it on eBay.)

Accrual vs cash basis accounting

The use of a "prepaid" sales tax account allowed setting aside cash. That made Assets/Cash always show what we could safely spend without ending up short of tax money. By gradually expensing our sales tax payments, we also have a better measure of expenses per day. This idea can be extended. We can split up grocery expenses so they are paired up with daily revenues. This method of recording expenses and revenues in the period the transactions occur is called Accrual Accounting. In a real firm, it would be done monthly rather than daily. We can (and should) do the same thing with our interest expense for the $100 loan.

Here is the complete business journal again, this time incorporating accrual accounting, depreciation and the cardboard box:

		# LemonadeStand2.sql - Accrual accounting and depreciation for the lemonade stand

		# Create the database

			drop database if exists LemonadeStand ;
			create database LemonadeStand ;
			use LemonadeStand ;

		# Load the software

			source OnePageAccounting.sql

		# Create the chart of accounts

			call account('Assets/Cash') ;
			call account('Assets/Change Bowl') ;
			call account('Assets/Deposits') ;
			call account('Assets/Prepaid Taxes') ;
			call account('Assets/Prepaid Interest') ;
			call account('Assets/Box/Cost'
			call account('Assets/Box/Depreciation'
			call account('Liabilities/Checks') ;
			call account('Liabilities/Groceries') ;
			call account('Liabilities/Taxes') ;
			call account('Liabilities/Principal') ;
			call account('Liabilities/Interest') ;
			call account('Liabilities/Payoff') ;
			call account('Revenues/Sales') ;
			call account('Revenues/Interest') ;
			call account('Expenses/Groceries/Sugar') ;
			call account('Expenses/Groceries/Lemons') ;
			call account('Expenses/Taxes') ;
			call account('Expenses/Interest') ;
			call account('Expenese/Depreciation') ;
			call account('Equity/Capital') ;
			call account('Equity/Draws') ;
			call account('Equity/Income Summary') ;

		# Get some money

			call journal('2014-01-01', 'Startup', 'Promissory note for $100 to dad' ) ;
			call entry(80.0,	'Assets/Deposits',    'Liabilities/Principal') ;
			call entry(20.0,	'Assets/Change Bowl', 'Liabilities/Principal') ;

		# Get the stand

			call journal('2014-06-02', 'Buy a cardboard box for the stand', 'Small change') ;
			call entry(4.00, 'Assets/Box/Cost', 'Assets/Change bowl') ;

		# Buy groceries

			call journal('2014-06-02', 'Pay for groceries: 65 lemons, 10 lb sugar', 'Check 0001') ;
			call entry(71.00,       'Liabilities/Groceries', 'Liabilities/Checks') ;

		# Begin regular business

			call journal('2014-06-02', 'Monday sales 32 cups', 'Sales book page 1') ;
			call entry(25.00,	'Assets/Deposits', 'Revenues/Sales') ; # Cash sales
			call entry(7.00,	'Assets/Deposits', 'Revenues/Sales') ; # Check

			call journal('2014-06-02', 'Set aside 5% sales tax', '') ;
			call entry(1.60,	'Assets/Prepaid Taxes', 'Assets/Cash') ;
			call entry(1.60,	'Expenses/Taxes', 'Liabilities/Taxes' ) ;

			call journal('2014-06-02', 'Accrue 2.5% interest', '') ;
			call entry(2.50,	'Assets/Prepaid Interest', 'Assets/Cash') ;
			call entry(2.50,	'Expenses/Interest', 'Liabilities/Interest' ) ;

			call journal('2014-06-02', 'Accrue grocery expense', '') ;
			call entry(16.25,	'Expenses/Groceries/Lemons', 'Liabilities/Groceries') ;
			call entry( 1.50,	'Expenses/Groceries/Sugar', 'Liabilities/Groceries') ;

			call journal('2014-06-02', 'Depreciate the box', '') ;
			call entry(1.00, 	'Expenses/Depreciation/Box', 'Assets/Box/Depreciation') ;

			call journal('2014-06-03', 'Tuesday sales 27 cups', 'Sales book page 2') ;
			call entry(27.00,	'Assets/Deposits', 'Revenues/Sales') ; # Cash sales

			call journal('2014-06-03', 'Set aside 5% sales tax', '') ;
			call entry(1.35,	'Assets/Prepaid Taxes', 'Assets/Cash') ;
			call entry(1.35,	'Expenses/Taxes', 'Liabilities/Taxes' ) ;

			call journal('2014-06-03', 'Accrue 2.5% interest', '') ;
			call entry(2.50,	'Assets/Prepaid Interest', 'Assets/Cash') ;
			call entry(2.50,	'Expenses/Interest', 'Liabilities/Interest' ) ;

			call journal('2014-06-03', 'Accrue grocery expense', '') ;
			call entry(16.25, 	'Expenses/Groceries/Lemons', 'Liabilities/Groceries') ;
			call entry( 1.50, 	'Expenses/Groceries/Sugar', 'Liabilities/Groceries') ;

			call journal('2014-06-03', 'Depreciate the box', '') ;
			call entry(1.00, 	'Expenses/Depreciation/Box', 'Assets/Box/Depreciation') ;

			call journal('2014-06-04', 'Wednesday sales 26 cups', 'Sales book page 3') ;
			call entry(22.00,	'Assets/Deposits', 'Revenues/Sales') ; # Cash sales
			call entry(4.00,	'Assets/Deposits', 'Revenues/Sales') ; # Check

			call journal('2014-06-04', 'Set aside 5% sales tax', '') ;
			call entry(1.30,	'Assets/Prepaid Taxes', 'Assets/Cash') ;
			call entry(1.30,	'Expenses/Taxes', 'Liabilities/Taxes' ) ;

			call journal('2014-06-04', 'Accrue 2.5% interest', '') ;
			call entry(2.50,	'Assets/Prepaid Interest', 'Assets/Cash') ;
			call entry(2.50,	'Expenses/Interest', 'Liabilities/Interest' ) ;

			call journal('2014-06-04', 'Accrue grocery expense', '') ;
			call entry(16.25, 	'Expenses/Groceries/Lemons', 'Liabilities/Groceries') ;
			call entry( 1.50, 	'Expenses/Groceries/Sugar', 'Liabilities/Groceries') ;

			call journal('2014-06-04', 'Depreciate the box', '') ;
			call entry(1.00, 	'Expenses/Depreciation/Box', 'Assets/Box/Depreciation') ;

			call journal('2014-06-05', 'Thursday sales 23 cups', 'Sales book page 4') ;
			call entry(20.00,	'Assets/Deposits', 'Revenues/Sales') ; # Cash sales
			call entry(3.00,	'Assets/Deposits', 'Revenues/Sales') ; # Check

			call journal('2014-06-05', 'Set aside 5% sales tax', '') ;
			call entry(1.15,	'Assets/Prepaid Taxes', 'Assets/Cash') ;
			call entry(1.15,	'Expenses/Taxes', 'Liabilities/Taxes' ) ;

			call journal('2014-06-05', 'Accrue 2.5% interest', '') ;
			call entry(2.50,	'Assets/Prepaid Interest', 'Assets/Cash') ;
			call entry(2.50,	'Expenses/Interest', 'Liabilities/Interest' ) ;

			call journal('2014-06-05', 'Accrue grocery expense', '') ;
			call entry(16.25, 	'Expenses/Groceries/Lemons', 'Liabilities/Groceries') ;
			call entry( 1.50, 	'Expenses/Groceries/Sugar', 'Liabilities/Groceries') ;

			call journal('2014-06-05', 'Depreciate the box', '') ;
			call entry(1.00, 	'Expenses/Depreciation', 'Assets/Box/Depreciation') ;

			call journal('2014-06-05', 'Take some money home', 'Raid the change bowl') ;
			call entry(13.00,	'Equity/Draws', 'Assets/Change Bowl') ;

		# Begin shutdown

			call journal('2014-06-06', 'Pay taxes', 'Check 0003') ;
			call entry(5.40,	'Assets/Cash', 'Assets/Prepaid Taxes') ;
			call entry(5.40,	'Liabilities/Taxes', 'Liabilities/Checks' ) ;

			call journal('2014-06-06', 'Pay investors', 'Checks 0004 & 0005') ;
			call entry(10.00,	'Assets/Cash', 'Assets/Prepaid Interest') ;
			call entry(10.00,	'Liabilities/Interest',  'Liabilities/Payoff') ;
			call entry(100.00,      'Liabilities/Principal', 'Liabilities/Payoff') ;
			call entry(balance('Liabilities/Payoff'), 'Liabilities/Payoff', 'Liabilities/Checks') ;

			call journal('2014-06-06', 'Liquidate assets', 'Empty the change bowl') ;
			call entry(balance('Assets/Change Bowl'), 'Assets/Deposits', 'Assets/Change Bowl') ;

		# Bank statement arrives

			call journal('2014-06-30', 'Reconcile checking account', 'Bank statement 2014-06-31') ;
			call entry(100.00,      'Assets/Cash', 'Assets/Deposits') ;     # Startup capital
			call entry( 32.00,	'Assets/Cash', 'Assets/Deposits') ;     # Revenues day 1
			call entry( 27.00,	'Assets/Cash', 'Assets/Deposits') ;     # Revenues day 2
			call entry( 26.00,	'Assets/Cash', 'Assets/Deposits') ;     # Revenues day 3
			call entry( 23.00,	'Assets/Cash', 'Assets/Deposits') ;     # Revenues day 4
			call entry( 20.00,      'Assets/Cash', 'Assets/Deposits') ;     # Change bowl contents
			call entry(  0.05,      'Assets/Cash', 'Revenues/Interest') ;   # Interest from bank
			call entry( 71.00,      'Liabilities/Checks', 'Assets/Cash' ) ; # Pay grocery bill
			call entry(  5.40,      'Liabilities/Checks', 'Assets/Cash' ) ; # Pay taxes
			call entry( 10.00,      'Liabilities/Checks', 'Assets/Cash' ) ; # Pay interest
			call entry(100.00,      'Liabilities/Checks', 'Assets/Cash' ) ; # Pay principal

		# Perform closing transactions

			call journal('2014-07-01', 'Closing transactions', '...') ;
			call entry(balance('Revenues/Interest'),         'Revenues/Interest',     'Equity/Income Summary') ;
			call entry(balance('Revenues/Sales'),            'Revenues/Sales',        'Equity/Income Summary') ;
			call entry(balance('Expenses/Groceries/Sugar'),  'Equity/Income Summary', 'Expenses/Groceries/Sugar') ;
			call entry(balance('Expenses/Groceries/Lemons'), 'Equity/Income Summary', 'Expenses/Groceries/Lemons') ;
			call entry(balance('Expenses/Taxes'),            'Equity/Income Summary', 'Expenses/Taxes') ;
			call entry(balance('Expenses/Interest'),         'Equity/Income Summary', 'Expenses/Interest') ;
			call entry(balance('Equity/Income Summary'),     'Equity/Income Summary', 'Equity/Capital') ;
			call entry(balance('Equity/Draws'),              'Equity/Capital',        'Equity/Draws') ;

		# Show the balance sheet before going out of business

			select balance('Assets'), balance('Liabilities')+balance('Equity') ;

		# Write the final check

			call journal('2014-07-01', 'Take the money and run', 'Check 0006') ;
			call entry(balance('Equity/Capital'), 'Equity/Capital', 'Liabilities/Checks') ;

			call journal('2015-07-31', 'Reconcile final checking account statement', 'Bank statement 2014-07-31') ;
			call entry(8.65, 'Liabilities/Checks', 'Assets/Cash' ) ;

		# Show final balance

			select balance('Assets'), balance('Liabilities')+balance('Equity') ; 

An annoyance of accrual accounting is that quarterly taxes must be paid on revenue even if the customer hasn't paid their bill. The payoff is that we can do better planning. Based on accurate cash flow and net income estimates, we know how much sugar and how many lemons to buy. We might decide to switch to "just in time" grocery shopping, buying just a few lemons each day. That would lower interest expenses, but might result in higher prices per lemon. Or we might lock in our expenses by lemon hedging and writing sugar futures contracts on the CME. Be sure to ask your parents before writing commodity futures contracts.

There is another method of accounting, Cash-basis accounting, that doesn't recognize revenue until we receive a check. Similarly, expenses are not "expensed" until we write a check. This is nice because we don't have to pay taxes on earnings we might not have, but it makes it difficult to see how the business is doing because revenues and expenses may be spread far apart in time. We can't assume that the balance of Assets/Cash is actually availible to spend for other business purposes. Cash-basis accounting is often recommended for small businesses because it is declared to be "simpler." This simplicity is offset by the need to introduce holding accounts to keep track of revenues earned before they're received and expenses incurred before they are paid.

Investment activities

Investment activities refer to expenses and revenues from buy and selling business assets. If we sell a depreciated asset for more than the salvage value, for example, we will have income from an investment activity. Our lemonade stand collected 5 cents interest from the bank as investment income.

Financial activities most often involve manipulations of the company's own stock and dividends. They also include payback of debt principle - in our case, the $100 we paid back to dad.

Adjustments

"He who does nothing makes no mistakes; he who makes no mistakes learns nothing."
- Pacioli

Accounting software doesn't provide for editing or erasing entries. That would be open season for embezzlers and expense account cheats. When you make a mistake, is should be corrected using an adjustment.

The simplest adjustment is just pair of entries with a journal heading to explain the problems. Let's assume that you find the books out of balance and determine the cause was a mistaken revenue report. You recorded a revenue of $12, but it should have been $13. Here's a simple attempt to fix the problem:

		call journal('2014-06-30' 'Fix error in revenue', 'See journal #132') ;
		call entry(12.00, 'Revenues/Sales', 'Assets/Deposits') ;
		call entry(13.00, 'Assets/Deposits', 'Revenues/Sales') ; 

But doing things this way has a drawback: If the error occurred months ago, things like cash flow statements will be messed up because the correction is recorded today. And now we are recording a deposit, but the actual deposit occurred months ago and was offset partially by the old bank statement which correctly shows the $13.00 check deposited. There will be no $13.00 on the next bank statement, so things are very confusing. The right way to fix this is by backdating the individual credits and debits that reverse the mistake.

When using the entry statement, the dates associated with the ledger transactions are taken from the journal date. The adjust function works just like entry, but has an extra parameter for an alternative date. This date is used for the ledger account transactions. What about dates in the ledger? Those weren't shown before. Well, I simplified the description of accounts a bit. A more complete example looks like this:

Assets/Cash
DateJournalDebitsCredits
2014-06-043$2000.00
2014-06-054$1000.00
2014-06-057$500.00
2014-06-078$1000.00
2014-06-089$250.00

This ledger account has a data and a journal column. The journal column contains the entry number in the journal that created the posting to the ledger.

The journal function doesn't have a journal entry number parameter. Instead, journal entries are numbered automatically. You will see the the journal entry numbers when you learn how to create reports later. They are often useful when tracking down errors.

With all that, we can now show a better way to fix the mistake:

		call journal('2014-06-30' 'Fix error in revenue', 'See journal #132') ;
		call adjust( '2014-01-31', 12.00, 'Revenues/Sales', 'Assets/Deposits') ;
		call adjust( '2014-01-31', 13.00, 'Assets/Deposits', 'Revenues/Sales') ; 

We use the current date for the journal entry. The adjustment date is at the end of the month where the error occured. Cash flows and balances will now be correct for all subsequent months.

Adjustments are also useful when we get behind with our bookkeeping. The matching principle in accounting refers to the concept of recording expenses and revenues together in the accounting period where they occur. But mail piles up and sometimes we don't see an order until the next month has started. And if we get behind paying bills, we will find ourselves journaling about the payment in the following month. Adjustments allow us to straighten things out so the transactions are recorded in the correct month.

Dealing with late order handling:

		call journal('2014-07-02' 'Order for widgets', 'P.O. #234') ;
		call adjust( '2014-06-30', 120.00, 'Assets/Deposits', 'Revenues/Sales') ; 

Dealing with a late payment:

		call journal('2014-07-02' 'Pay phone bill', 'Check 231414') ;
		call adjust( '2014-06-30', 220.00, 'Expenses/Phone', 'Liabilities/Checks') ; 

We are not postdating checks and nothing deceptive is happening. We're actually making the books more truthful.

Another important use of adjustments is pre-posting the entries for depreciation. When you buy an asset you intend to depreciate, you have to remember to make the depreciation entry each period and you have to know when to stop, possibly making an irregular entry to start or end in the middle of a period. It's easier to do all this up front when you buy the asset. That can be done by posting a series of adjustments with future dates. As long as you qualify your financial reports by the appropriate range of dates, everything will work out without further attention.

Reporting - Knowing your business

"There is more to the making of a Good Businessperson than to the making of a Doctor of Laws."
- Pacioli

Commercial accounting package try to anticipate the questions you likely to have using an elaborate selection of reports. A nice thing about using a database directly (this approach) is that you don't need lots of complicated software to ask detailed questions. Instead, you have to learn a small specialized language called "SQL." But once you understand how to formulate SQL queries, you can ask all sorts of qustions and get precisly the information you need without printing reams of paper.

The following section contains a "Worksheet" of expressions you can execute from the commmand line to answer typical accounting questions.

		# Worksheet.sql - Useful expressions for use with OnePageAccounting

		# Show the top level account balances

			select 	balance('Assets'),
				balance('Liabilities'),
				balance('Equity'),
				balance('Revenues'),
				balance('Expenses') ;

		# Display the balance sheet

			select	balance('Assets'),
				balance('Liabilities')
				+ balance('Equity')
				+ balance('Revenues')
				- balance('Expenses') ;

		# Display all accounts with their associated debits and credits

			select entry.adate, debit, credit, account.name
			from entry, account
			where entry.account=account.id
			order by account.name ;

		# Display all the entries for an account

			select name, adate, journal, debit, credit
			from entry, account
			where entry.account=account.id
			and account.name='Assets/Cash' ;

		# Show the balance of a particular account

			select balance('Assets/Cash') ;

		# Show all accounts and their balances

			select account.name, balance(account.name) from account ;

		# Show a journal transaction

			select id, adate, identifier, purpose from journal where journal.id = 1 ;

		# Show all the entries for a journal transaction

			select entry.adate, debit, credit, account.name
			from entry, account
			where entry.account=account.id
			and entry.journal=1 ;

		# Show non-zero accounts

			select account.name, balance(account.name) from account
			where balance(account.name) > 0.0 ;

		# Display income

			select balance('Revenues') - balance('Expenses') ;

		# Display a detailed view of selected accounts

			select name, entry.adate, journal, debit, credit, purpose
			from entry, account, journal
			where entry.account=account.id
			and entry.journal=journal.id
			and account.name='Assets/Cash' ;

			select name, entry.adate, journal, debit, credit, purpose
			from entry, account, journal
			where entry.account=account.id
			and entry.journal=journal.id
			and account.name='Liabilities/Checks Outstanding' ;

			select name, entry.adate, journal, debit, credit, purpose
			from entry, account, journal
			where entry.account=account.id
			and entry.journal=journal.id
			and account.name='Assets/Checks for Deposit' ;

		# Show all checks written

			select journal.adate, journal.id, credit, purpose
			from entry,journal,account
			where account.name="Liabilities/Checks Outstanding"
			and entry.account=account.id
			and entry.journal=journal.id
			and entry.debit=0.0 ;

		# Show the entire journal

			select * from journal ;

		# Show the entire ledger

			select name, entry.adate, journal, debit, credit, purpose
			from entry, account, journal
			where entry.account=account.id
			and entry.journal=journal.id
			order by account.name ;

		# Show selected expenses

			select balance('Expenses/Groceries/Lemons') ;
			select balance('Expenses/Groceries/Sugar') ;

		# Show details of all expenses and date accrued

			select entry.adate, journal, debit, name, purpose
			from entry, account, journal
			where entry.account=account.id
			and entry.journal=journal.id
			and account.name like 'Expenses/%' ;

		# Show details of all revenues and date accrued

			select entry.adate, journal, credit, name, purpose
			from entry, account, journal
			where entry.account=account.id
			and entry.journal=journal.id
			and account.name like 'Revenues/%' ;

		# Show details of all liabilities paid and date paid

			select entry.adate, journal, debit, name, purpose
			from entry, account, journal
			where entry.account=account.id
			and entry.journal=journal.id
			and credit=0.0
			and account.name like 'Liabilities/%' ;

		# Show all journal details

			select id, journal.adate, debit, credit, purpose, identifier
			from journal,entry where entry.journal=journal.id ;

		# Show entries between selected dates

			select * from journal
			where journal.adate between '2014-01-01' and '2014-06-14' ;

		# Show the cash flow metric

			select balance('Assets/Cash') - balance('Revenues') + balance('Expenses') ; 

Download the software

It's a one-page script for MySQL. It can probably be adapted to other databases with SQL-like command line interface. Get your copy here.

Using the software

Go find, install, and work through a tutorial on MySQL. Next, download the script in the previous section. Try out the lemonade example and the associated queries in the worksheet. Then set up your business!

References

Questions and complaints

Send an email...