Accounting, Financial, Taxation

One is sometimes called upon to make sales forecasts or verify those made by the sales and marketing departments. One of the better approaches for doing this is to extend the past history of sales volume forward into the periods being projected. Though this method of prediction is like trying to drive a car by looking in the rear view mirror, it is still one of the best tools available, as long as it is supplemented by detailed conversations with the sales staff to see what is really happening in the marketplace.

There are two formulas provided by Excel that result in forecast information:

The first, and simplest, is the TREND command. This one superimposes a trend line on an existing set of time-sequenced data points to arrive at an expected sales level for a specified future period.

To illustrate the command, we return once again to the income statement shown earlier in my previous post [Financial Statement Proportional and Ratio Analysis with Excel Spreadsheet]. We will use a new worksheet within the same spreadsheet, called Trend, and reference in it all of the monthly sales figures from the previous income statement. This is shown in below figure, along with a graph that shows the added trend line.

In the above worksheet, we already know all sales data points from January through August, and want to calculate a trend line that extends an additional month to give us a prediction for September sales. Accordingly, in the table of months and historical sales figures noted in the Trend worksheet, there is an additional cell next to the “August” sales period. In that cell we enter the following formula:

TREND(B4:B11,A4:A11,A12)

Though it looks complicated, this is a relatively simple command. The trend line is based on the data points contained in cells B4 through B11 for the date ranges contained in cells A4 through A11. The date for the period to be forecast is noted in cell A12. The formula generates a number that is the extension of the trend shown by the previous data elements and will deposit this number in the B12 cell.

Another way to state the formula is to ignore the dates and just ask for the next number in sequence. The formula, based on the previous example, looks like this:

TREND(B4:B11, ,{9})

Under this variation, we are using the same set of data points, but ignoring the dates (hence the two commas in the formula with no data in between), and a number in brackets which represents the trend for the ninth number in the sequence of data elements. Since the original set of data only included eight data elements, this will be the next revenue figure after the last month of actual data. If the requested trend were for the month of December, the number in brackets would change to {12}, since this would represent the twelfth data point in the series.  However, to add a trend line overlay to the presented data, click on the completed graph, move the cursor to the revenue line on the chart, and press the right mouse button. Then click on the Add Trend line option, and pick from six available types of trend lines that can be added to the graph.

The second type of forecasting tool provided by Excel is REGRESSION ANALYSIS. This is a powerful tool for determining the trend line that best fits a disparate set of data, and is most useful when dealing with a set of numbers that are widely scattered, and show no apparent pattern.

In essence, the method determines the trend line that minimizes the sum of all squared errors between all data points and the line. Rather than delve into the formula for this method, it is easiest to plot the data elements and proceed immediately to a graph, on which Excel will superimpose a regression trend line.

In the above figure, the second half of the presentation includes the regression analysis. In it, we have plotted twenty data items for twenty periods that are wildly different from each other, and have no apparent pattern. The first step in the analysis is to create a graph. Then use the same steps just described for the TREND analysis to add a trend line to the chart. The result is shown in the bottom half of the above figure, where we find that there is a slight upward trend line to the data used to compile the regression trend line.

Of the two methods presented, the TREND formula is of the most use, for one will find that most data being analyzed in the financial arena has a lengthy and steady trend line of data. Only for the most unusual analyses, involving wildly disparate data items, will the regression analysis be necessary.

The following list shows some of the trend lines that you may consider and try when measuring a company’s operations using the TREND formula in excel spreadsheet:

1. Trend of amount of utilized storage space. This trend can spotlight many contributing problems, such as obsolete inventory, returned goods, scrapped parts, and excess finished goods.
2. Trend of cost of distribution channels. This analysis should include the net margins earned on each sales channel.
3. Trend of cost of freight. A company may extend into new geographical regions without considering the cost of shipping product into those areas. This analysis should be conducted by region to spot such problems.
4. Trend of cost of sales calls. In particular, one should compare the relationship between the sales received from high-volume customers and the cost of making sales calls to them. It is common to find a few high-maintenance customers who are not worth the sales effort from a cost-benefit perspective.
5. Trend of design cycle iterations required. Increases in the number of design iterations indicate serious problems in the design process, while a drop in the trend indicates good design management (though it can also indicate that an insufficient number of product reviews are being made).
6. Trend of direct labor rates. This can be used in comparison to a market survey to see if company pay rates are varying from those offered by other firms. After a layoff, it can change suddenly if people whose pay varies substantially from the mean have been laid off.
7. Trend of engineering change notices issued. A jump in the number of change orders can signal the presence of quality problems, as well as a likely increase in inventory, as some components are rendered obsolete.
8. Trend of gross margins. Investigate margins by both product and volume.
9. Trend of inventory, bill of material, and labor routing accuracy. These three items require very high levels of accuracy in order to operate a production planning system. Any drop in these trends will likely result in production snafus.
10. Trend of number of product options per product family. Changes in this trend can refer to a deliberate attempt at product proliferation as a marketing strategy (if it increases) or a rationalization of engineering tasks (if it declines).
11. Trend of overhead capitalization. If the amount of overhead shifted into a capital account, such as inventory, is changing, this may signal a deliberate attempt by management to alter the reported level of earnings.
12. Trend of pricing. Track the sensitivity of sales volume to changes in pricing.
13. Trend of product returns. A sudden change in this trend can signal a quality problem in the product, or that the distribution pipeline is full.
14. Trend of ratio of overhead to production labor. Overhead can balloon rapidly, and this is a prime early indicator of the problem.
15. Trend of return on shareholders’ equity and return on assets. A significant decline in these measures signals reduced cash flow, more difficult borrowing covenants, and a host of other problems, such as reduced margins and increased expenses.
16. Trend of sales quotas. This trend can be compared to actual sales per salesperson to see if the quotas are unrealistically high or low.
17. Trend of sales volume. Examine sales volume by both territory and product.
18. Trend of utilized plant capacity. This is a good indicator of the need for additional production shifts, increased maintenance, or more facilities.

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.