Accounting, Financial, Taxation

There are several tools used to conduct financial analysis. One is a database of accounting information, in which an analyst can roam for days, tracking down the details regarding when specific transactions have taken place, why they occurred, and the likelihood of their happening again. However, analysts rarely descend straight into the depths of the accounting database without first using some more simple means for determining what problem has arisen, which yields clues regarding where in the database to search. This higher-level information is obtained by using ratio and trend analysis to pinpoint the issue. To get this information, a calculator, pencil, and paper are sufficient, but also very time consuming and prone to error. Instead, an electronic spreadsheet is the best method.

For you who are well financed, you may purchase such instant Excel spreadsheet analysis tools. But for you who are willing to invest a lil time and learn, you may save some bucks. In this post, we review how to use such a spreadsheet—in this case, the Microsoft Excel spreadsheet.

The formulas presented in this post are by no means difficult. I will do my best to confin to the simplest and most understandable spreadsheet commands, and avoids the use of complicated macros. The discussion focuses on using spreadsheets for: financial statements. In each case, it is noted how Excel can be used to solve a problem, and then a sample situation is provided.

A key issue that is noted throughout this post is the difference between a spreadsheet and a worksheet. In Excel, a spreadsheet can have a number of interlinked layers known as worksheets. When an entry is made in one worksheet, it can be referenced by other worksheets in the same spreadsheet. This is a preferable approach to using Excel for financial analysis, since one can separate the data being analyzed in one worksheet, ratios in another, and graphics in yet another worksheet – but with formulas linking all of them together. In the examples used, nearly all of the analysis is done on one spreadsheet that contains a half-dozen worksheets.

Financial Statement Proportional Analysis

Proportional analysis is simply converting all of the numbers in an income statement and balance sheet into percentages, so that they can be compared over time to see what differences arise. By conducting this analysis, one can see if there are trends in revenues, costs, assets, or liabilities that may require further analysis or investigation.

When using Excel to conduct a proportional analysis of a financial statement, one must first input the income statement for each period into the worksheet, so that the proportional analysis calculation will appear below it or on a separate worksheet. Below figure, a simplified income statement has been entered in the cells at the top of the worksheet.

For each line item in this top section, there is a formula entered in the replicated income statement at the bottom of the screen that divides each expense line item by the revenue figure, resulting in a percentage of sales for each item. For example: the materials cost proportion for the month of January is calculated with the following formula, which is entered in cell B15:

B5 / B$4

Since the spreadsheet contains the income statement for multiple months, the resulting proportional analysis becomes very useful for finding any trends in the expenses being incurred over the course of the year.

The income statement proportional analysis used in the preceding example would be of great use to management in determining why its profits are not increasing along with its evident sales growth. In the example, sales increase from $1,200 in January to $1,400 in August, but profits drop by $9. Why? By perusing the proportional analysis, it is an easy matter to see that the cost of materials has dropped as a percentage of sales, which may reflect excellent purchasing, design, or production work to lower these costs. For the answer to why profits have dropped, we must look lower in the spreadsheet. The direct labor cost as a proportion of sales has risen, so this is an obvious target area for further analysis. However, the overall gross margin percentage has only dropped by one percent over the time period being analyzed, so there must be more trouble further down in the income statement.

Sure enough, the administrative expenses line item reveals a three percent jump in costs. Accordingly, anyone using this analysis would conclude that the trouble has arisen in the direct labor and administrative areas, and that the materials expense requires no further analysis.

Though this type of analysis is an excellent way to hone in on key areas, it is rarely the final analysis conducted, since it does not reveal enough information. Also, it is not sufficient if there are many operating divisions rolled into the income statement. In these cases, it is best to create a number of separate spreadsheets, one for each division, and conduct the analysis on each one, thereby yielding a greater level of detail regarding problem areas.

The same proportional analysis can be applied to the balance sheet. In the next figure, one can manually enter a simplified version of the balance sheet at the top of the spreadsheet, which produces a set of percentages at the bottom. The asset percentages sum to the grand total of all assets, while the percentages for liabilities and equity sum to the total for those two categories.

As was the case for the proportional analysis of the income statement, the cell formula is extremely simple. In the above figure, the percentage for accounts payable in April is calculated by dividing the total accounts payable dollars, located in cell E10, by the total of all liabilities and equity for that month, which is located in cell E14.

What does the proportional analysis of the balance sheet tell us? To use the example, there is a clear increase in the fixed asset investment, which requires the use of all cash, as well as an increased debt load, which reaches its height in May, after which cash flow from operations is used to gradually draw down the level of debt. The only other trend of note is that inventory levels are declining, which indicates either excellent logistics practices or a decline in sales that no longer requires such a large supporting base of inventory. Consequently, a great deal can be discerned by reviewing a proportional balance sheet analysis.

Financial Statement Ratio Analysis

Perhaps the most common use of an electronic spreadsheet is to conduct a ratio analysis of the income statement and balance sheets. Typically, a summary form of the income statement and balance sheet are located at the top of the worksheet, with ratios located at the bottom that are derived from these two reports. By using this approach, one can quickly enter the summary-level financial information for the current reporting period and then see the related ratios appear at the bottom of the worksheet. In a few moments, he or she has access to a rough analysis of company operations. If there are entries for the financial results of previous months, then one can also see trend lines in ratio results that extend through to the current reporting period.

As an example of the types of ratio analysis one can use in a worksheet, we will use the “income statement and balance sheet” shown earlier, in the first and second screenshot above. A series of ratios are noted in below figure that are derived from those statements.

In the above figure, there are several tabs itemized at the bottom of the worksheet. Each one represents another spreadsheet that is clustered into the same workbook. The first tab, entitled “IS“, contains a spreadsheet version of the income statement.

The second tab, entitled BS, contains a spreadsheet version of the balance sheet. The ratios shown in the Figure are compiled by referencing the cell locations in these two spreadsheets and listing the result on the current Ratios spreadsheet.

The formulas behind the ratios in the above figure are not shown, so the same spreadsheet is laid out differently in below figure to provide this information. In this example, we have eliminated the formulas for all but the month of January, and then listed each formula in full. For example, the first ratio is the Quick Ratio, which compares easily liquidated assets to current liabilities. To obtain this information, the cell entry goes to the “BS” spreadsheet and adds together cells B4 and B5, which contain the cash and accounts receivable figures for the month of January. The formula then divides the sum by the accounts payable and accrued liabilities amounts, which are located on the same spreadsheet in cells B10 and B11.

Further down in the list of ratios are ones that are built upon the income statement. For example: the Return on Sales percentage is derived by referencing the profit figure for January, which is located in cell B11 in the IS spreadsheet and dividing by total sales, which is located in cell B4 in the same spreadsheet. Finally, we can mix references to both the IS and BS spreadsheets in the same ratio formula.

For example: mto arrive at the return on equity, the formula takes the profit for January, which is located in cell B11 in the IS spreadsheet, annualizes it by multiplying by 12, and divides it by the equity figure, which is located in cell B13 in the BS spreadsheet. Thus, we can mix cell references from a variety of spreadsheets in order to arrive at a centralized set of ratios that can be stored in a single spreadsheet location.

Automated Ratio Result Analysis

If there are a great many ratios linked to a set of financial statements, one may want to save time in reviewing them by having the spreadsheet issue a warning message for those ratios that fall outside a preset parameter. Another reason for using this approach is when a lending institution places constraints on a company by requiring minimum levels for certain ratios, such as a current ratio of at least 2:1, or a debt/equity ratio of no higher than 30%. In either case, a formula that presents a YES/NO or GOOD/BAD result can save some time.

A simple IF formula will create an automated ratio result. To continue with the example used previously in the above figure, we will add three rows to the analysis. Under the Balance Sheet Ratios section, add a row entitled “Meets Quick Ratio Covenant” This is a YES/NO determination based on the quick ratio being greater than 0.9, and will appear in row 8. The formula for the month of January will be:

IF(B5>.9,”Yes”,”No”)

Under the Income Statement Ratios section, add a row entitled Meets Gross Margin Covenant. This is a YES/NO determination based on the gross margin being greater than 43% and will appear in row 14. The formula for the month of January will be:

IF(B18>21,“Yes”,“No”)

All of these new formulations are shown in the ratios Figure on the next screenshot. In the Figure, one can quickly skim through the various months of results to determine the occasions when covenants have been violated. Setting up the IF statements that drive these automated ratio results are quite simple, and can help to some extent in the task of sorting through large quantities of ratios.

Accounting extends into virtually every walk of life. You’re doing accounting when you make entries in your checkbook and when you fill out your income tax return. When you sign a mortgage on your home, you should understand the accounting method the lender uses to calculate the interest amount charged on your loan each period. Individual investors need to understand accounting basics in order to figure their return on invested capital. And it goes without saying that every organization, profit-motivated or not, needs to know how it stands financially.

Here’s a quick sweep to give you an idea of the range of accounting:

1. Accounting for organizations and accounting for individuals
2. Accounting for profit-motivated businesses and accounting for nonprofit organizations (such as hospitals, homeowners’ associations, churches, credit unions, and colleges)
3. Income tax accounting while you’re living and estate tax accounting when you die
4. Accounting for farmers who grow their products, accounting for miners who extract their products from the earth, accounting for producers who manufacture products, and accounting for retailers who sell products that others make
5. Accounting for businesses and professional firms that sell services rather than products, such as the entertainment, transportation, and healthcare industries
6. Past-historical-based accounting and future-forecast-oriented accounting (budgeting and financial planning)
7. Accounting where periodic financial statements are legally mandated (public companies are the primary example) and accounting where such formal accounting reports are not legally required
8. Accounting that adheres to historical cost mainly (businesses) and accounting that records changes in market value (mutual funds, for example)
9. Accounting in the private sector of the economy and accounting in the public (government) sector
10. Accounting for going-concern businesses that will be around for some time and accounting for businesses in bankruptcy that may not be around tomorrow.
11. What else?

Accounting is necessary in a free-market, capitalist economic system. It’s equally necessary in a centralized, government-controlled, socialist economic system. All economic activity requires information. The more developed the economic system, the more the system depends on information. Much of the information comes from the accounting systems used by the businesses, institutions, individuals, and other players in the economic system.

Some of the earliest records of history are the accounts of wealth and trading activity. The need for accounting information was a main incentive in the development of the numbering system we use today. The history of accounting is quite interesting (but beyond the scope of this book). Taking a Peek into the Back Office. Every business and not-for-profit entity needs a reliable bookkeeping system.

Keep in mind that accounting is a much broader term than bookkeeping:

Accounting encompasses the problems in measuring the financial effects of economic activity. Furthermore, accounting includes the function of financial reporting of values and performance measures to those that need the information. Business managers and investors, and many other people, depend on financial reports for information about the performance and condition of the entity.

Bookkeeping refers to the process of accumulating, organizing, storing, and accessing the financial information base of an entity, which is needed for two basic purposes:

1. Facilitating the day-to-day operations of the entity
2. Preparing financial statements, tax returns, and internal reports to managers

Bookkeeping (also called recordkeeping) can be thought of as the financial information infrastructure of an entity. Of course the financial information base should be complete, accurate, and timely. Every recordkeeping system needs quality controls built into it, which are called internal controls or internal accounting controls.

Accountants design the internal controls for the bookkeeping system, which serve to minimize errors in recording the large number of activities that an entity engages in over the period. The internal controls that accountants design are also relied on to detect and deter theft, embezzlement, fraud, and dishonest behavior of all kinds. In accounting, internal controls are the ounce of prevention that is worth a pound of cure.

I have discussed about internal control (financial and operation) a lot. Here (in this post), I want to stress the importance of the bookkeeping system in operating a business or any other entity. These back-office functions are essential for keeping operations running smoothly, efficiently, and without delays and errors. This is a tall order, to say the least.

Most people don’t realize the importance of the accounting department in keeping a business operating without hitches and delays. That’s probably because accountants oversee many of the back-office functions in a business—as opposed to sales, for example, which is front-line activity, out in the open and in the line of fire. Go into any retail store, and you’re in the thick of sales activities. But have you ever seen a company’s accounting department in action?

Folks may not think much about these back-office activities, but they would sure notice if those activities didn’t get done. On payday, a business had better not tell its employees, “Sorry, but the accounting department is running a little late this month; you’ll get your checks later.” And when a customer insists on up-to-date information about how much he or she owes to the business, the accounting department can’t very well say, “Oh, don’t worry, just wait a week or so and we’ll get the information to you then”.

Typically, the accounting department is responsible for the following 5 (five) main areas:

Payroll

The total wages and salaries earned by every employee every pay period, which are called gross wages or gross earnings, have to be calculated. Based on detailed private information in personnel files and earnings-to-date information, the correct amounts of income tax, social security tax, and several other deductions from gross wages have to be determined. Stubs, which report various information to employees each pay period, have to be attached to payroll checks. The total amounts of withheld income tax and social security taxes, plus the employment taxes imposed on the employer, have to be paid to provincial (state) and the national (federal) government agencies on time. Retirement, vacation, sick pay, and other benefits earned by the employees have to be updated every pay period. In short, payroll is a complex and critical function that the accounting department performs. Many businesses outsource payroll functions to companies that specialize in this area.

Cash Collections

All cash received from sales and from all other sources has to be carefully identified and recorded, not only in the cash account but also in the appropriate account for the source of the cash received. The accounting department makes sure that the cash is deposited in the appropriate checking accounts of the business and that an adequate amount of coin and currency is kept on hand for making change for customers. Accountants balance the checkbook of the business and control who has access to incoming cash receipts. (In larger organizations, the treasurer may be responsible for some of these cash flow and cash handling functions).

Cash Payments (disbursements)

In addition to payroll checks, a business writes many other checks during the course of a year — to pay for a wide variety of purchases, to pay property taxes, to pay on loans, and to distribute some of its profit to the owners of the business, for example. The accounting department prepares all these checks for the signatures of the business officers who are authorized to sign checks. The accounting department keeps all the supporting business documents and files to know when the checks should be paid, makes sure that the amount to be paid is correct, and forwards the checks for signature.

Procurement and Inventory

Accounting departments usually are responsible for keeping track of all purchase orders that have been placed for inventory (products to be sold by the business) and all other assets and services that the business buys — from postage stamps to forklifts. A typical business makes many purchases during the course of a year, many of them on credit, which means that the items bought are received today but paid for later. So this area of responsibility includes keeping files on all liabilities that arise from purchases on credit so that cash payments can be processed on time. The accounting department also keeps detailed records on all products held for sale by the business and, when the products are sold, records the cost of the goods sold.

Property Accounting

A typical business owns many different substantial long-term assets called property, plant, and equipment — including office furniture and equipment, retail display cabinets, computers, machinery and tools, vehicles (autos and trucks), buildings, and land. Except for relatively small-cost items, such as screwdrivers and pencil sharpeners, a business maintains detailed records of its property, both for controlling the use of the assets and for determining personal property and real estate taxes. The accounting department keeps these property records.

The accounting department may be assigned other functions as well, but this list gives you a pretty clear idea of the back-office functions that the accounting department performs. Quite literally, a business could not operate if the accounting department did not do these functions efficiently and on time. And to repeat one point: To do these back-office functions well the accounting department must design a good bookkeeping system and make sure that it is accurate, complete, and timely.

What am I going to talk about through this post? It is not about calculating (measuring), nor recording transaction (debit and credit), neither about how to construct accounting/financial report in easy way. Instead, it is about: realizing how accounting is relevant to you, grasping how all economic activity requires accounting, watching an accounting department in action, shaking hands with business financial statements, and answering a question “Mama, should you let your baby grow up to be an accountant?”

Accounting is all about financial information — capturing it, recording it, configuring it, analyzing it, and reporting it to persons who use it. I won’t say much about how accountants capture, record, and configure financial information in this post. But I am going to talk a lot about how accountants communicate information in financial statements, and I explain the valuation methods accountants use — ranging from measuring profit and loss to putting values on assets and liabilities of businesses.

As you go through life, you come face to face with accounting information more than you would ever imagine. Regretfully, much of this information is not self-explanatory or intuitive, and it does not come with a user’s manual. Accounting information is presented on the assumption that you have a basic familiarity with the vocabulary of accounting and the accounting methods used to generate the information. In short, most of the accounting information you encounter is not transparent. The main reason for studying accounting is to learn its vocabulary and valuation methods, so you can make more intelligent use of the information.

People who use accounting information should know the basic rules of play and how the financial score is kept, much like spectators at a football or baseball game.

Let me point out another reason you should know accounting basics. A lot of people out there in the cold, cruel financial world may take advantage of you, not necessarily by illegal means but by withholding key information and by diverting your attention from unfavorable aspects of certain financial decisions. These unscrupulous characters treat you as a lamb waiting to be fleeced. The best defense against such tactics is to know some accounting, which can help you ask the right questions and understand the financial points that con artists don’t want you to know.

Is Accounting Just for Accountants?

One main source of accounting information is in the form of financial statements that are packaged with other information in a financial report. Accountants keep the books and record the financial activities of an entity (such as a business). From these detailed records the accountant prepares financial statements that summarize the results of the activities.

Financial statements are sent to people who have a stake in the outcomes of the activities. If you own stock in General Electric, for example, or you have money in a mutual fund, you receive regular financial reports. If you invest your hard-earned money in a private business or a real estate venture, or you save money in a credit union, you receive regular financial reports. If you are a member of a nonprofit association or organization, you’re entitled to receive regular financial reports.

In summary, one important reason for studying accounting is to make sense of the financial statements in the financial reports you get. I guarantee that Warren Buffett knows accounting and how to read financial statements.

Accounting Affects both Insiders and Outsiders

People who need to know accounting fall into two broad groups: “insiders” and “outsiders“.

Business managers are insiders; they have the authority and responsibility to run a business. They need a good understanding of accounting terms and the methods used to measure profit and put values on assets and liabilities. Accounting information is indispensable for planning and controlling the financial performance and condition of the business. Likewise, administrators of nonprofit and governmental entities need to understand the accounting terminology and measurement methods in their financial statements.

The rest are outsiders.

The Outsiders not privy to the day-to-day details of a business or organization. They have to rely on financial reports from the entity to know what’s going on. Therefore, they need to have a good grip on the financial statements included in the financial reports. For all practical purposes, financial reports are the only source of financial information we get directly from a business or other organization.

By the way, the employees of a business — even though they obviously have a stake in the success of the business — do not necessarily receive its financial reports. Only the investors in the business and its lenders are entitled to receive the financial reports. Of course, a business could provide this information to those of its employees who are not shareowners, but generally speaking most businesses do not. The financial reports of public businesses are in the public domain, so their employees can easily secure a copy. However, financial reports are not automatically mailed to all employees of a public business.

In our personal financial lives, a little accounting knowledge is a big help for understanding investing in general, how investment performance is measured, and many other important financial topics. With some basic accounting knowledge, you’ll sound much more sophisticated when speaking with your banker or broker. I can’t promise you that learning accounting will save you big bucks on your income taxes, but it can’t hurt and will definitely help you understand what your tax preparer is talking about.

Overcoming the Stereotypes of Accountants

Maybe you’ve heard the joke that an accountant with a personality is one who looks at your shoes when he is talking to you, instead his own shoes.

Like most stereotypes, there’s an element of truth in the preconceived image of accountants. Even I am not an accounting professor, I have met and known a large number of accountants. Most accountants are not as gregarious as used-car sales people (though some are). Accountants certainly are more detail-oriented than your average person. However, you don’t have to be good at mathematics to be a good accountant. Accountants use very little math (no calculus and only simple algebra). Accountants are very good at one thing: They want to see both sides of financial transactions: the give and take. Accountants know better than anyone that, as economists are fond of saying, there’s no such thing as a free lunch.

If you walked down a busy street in metropolis city (Chicago, London, Tokyo, New York, or Los Angeles), I doubt that you could pick out the accountants. I have no idea whether accountants have higher or lower divorce rates than others, whether they go to church more frequently or if they generally sleep well at night. I do think that accountants are more honest in paying their income taxes than other people, although I have no proof of this.

Relating Accounting to Your Personal Financial Life

I’m sure you know the value of learning personal finance and investing fundamentals. Well, a great deal of the information you use in making personal finance and investing decisions is accounting information. One knock I have on oversee in these areas is that they often don’t make clear that you need a basic understanding of accounting terminology and valuation methods in order to make good use of the financial information.

You have a stake in the financial performance of the business you work for, the government entities you pay taxes to, the churches and charitable organizations you donate money to, the retirement plan you participate in, the businesses you buy from, and the healthcare providers you depend on. The financial performance and viability of these entities has a direct bearing on your personal financial life and well-being.

We’re all affected by the profit performance of businesses, even though we may not be fully aware of just how their profit performance affects our jobs, investments, and taxes. For example: as an employee your job security and your next raise depend on the business making a profit. If the business suffers a loss, you may be laid off or asked to take a reduction in pay or benefits.

Business managers get paid to make profit happen. If the business fails to meet its profit objectives or suffers a loss, its managers may be replaced (or at least not get their bonuses).

Your investments in businesses, whether direct or through retirement accounts and mutual funds, suffer if the businesses don’t turn a profit. I hope the stores I trade with make profit and continue in business. The government depend on businesses making profit to collect income taxes from them.

Accounting Focuses on Transactions

Accounting focuses on transactions. A good bookkeeping system captures and records every transaction that takes place without missing a beat. Transactions are economic exchanges between a business or other entity and the parties with which the entity interacts and makes deals. Transactions are the lifeblood of every business, the heartbeat of activity that keeps it going. Understanding accounting, to a large extent, means understanding how accountants record the financial effects of transactions. The immediate and future financial effects of some transactions can be difficult to determine.

A business carries on economic exchanges with six basic types of persons or entities:

1. Its customers, who buy the products and services that the business sells
2. Its employees, who provide services to the business and are paid wages and salaries and provided with benefits, such as: a retirement plan, medical insurance, workers’ compensation, and unemployment insurance.
3. Its suppliers and vendors, who sell a wide range of things to the business, such as legal advice, products for resale, electricity and gas, telephone service, computers, vehicles, tools and equipment, furniture, and even audits.
4. Its debt sources of capital who loan money to the business, charge interest on the amount loaned, and are due to be repaid at definite dates in the future.
5. Its equity sources of capital, the individuals and financial institutions that invest money in the business and expect the business to earn profit on the capital they invest.
6. The government, or the federal, state, and local agencies that collect income taxes, sales taxes, payroll taxes, and property taxes from the business.

Even a relatively small business generates a surprisingly large number of transactions, and all transactions have to be recorded. Certain other events that have a financial impact on the business have to be recorded as well. These are called events because they’re not based on give-and-take bargaining—unlike the something-given-for-something-received nature of economic exchanges.

Events such as the following have an economic impact on a business and are recorded:

1. A business may lose a lawsuit and be ordered to pay damages. The liability to pay the damages is recorded.
2. A business may suffer a flood loss that is uninsured. The waterlogged assets may have to be written down, meaning that the recorded values of the assets are reduced to zero if they no longer have any value to the business. For example, products that were being held for sale to customers (until they floated down the river) must be removed from the inventory asset account.
3. A business may decide to abandon a major product line and downsize its workforce, requiring that severance compensation be paid to the laid-off employees.

At the end of the year the accountant makes a special survey to make sure that all events and developments during the year that should be recorded have been recorded, so that the financial statements and tax returns for the year are complete and correct.

The talk is getting more seriously eh? But let’s take a break for some drinks. If you love this kind discussion, you may want to check out my next topic: Range Of Accounting: What Accounting Department Mainly Responsible For?