Mining the general ledger for financial analytics

To support financial analytics, IT staff must know its way around the general ledger. Here's how to read GL entries.

An earlier general ledger tutorial examined how GL entries are made and how they impact the balance sheet and income...

statement. Sure, the accounting department is legally required to produce these financial statements, but the same information can also provide useful insights into the health of a company. The general ledger can be a rich source of data for financial analytics.

IT professionals who support the finance function must not only understand how such insights are derived but create a business intelligence (BI) strategy to provide deeper analysis. That means understanding key financial areas and the software that ties into them, building cubes and data marts to enhance understanding, then shop these BI concepts to various departments.

To illustrate, I have reproduced The Coca-Cola Co. balance sheet and income statement for 2001. Have a quick look at these statements, see what they tell you, and then we will do a deeper dive together.

A balance sheet shows a company's assets, liabilities and owner's equity at a single point in time. Both sides must balance.

An income statement is also known as a profit and loss (P&L) statement.

Gleanings from the income statement

Coca-Cola had revenues of $20.4 billion and a cost of goods sold (COGS) -- manufacturing plus raw material costs -- of $6 billion, leaving a gross profit of $14.4 billion. (Gross profit is defined as revenue minus COGS.) This yields a gross margin of 70.6%: gross profit divided by revenues.

Financial BI exercises

Basic financial statements can provide the data needed for meaningful financial analytics. Try these four BI exercises with the Coca-Cola financial statements:

  • Build a simple database that shows the results by region (North America, for example) and quarter. Where are the major contributors to Coca-Cola profit?
  • Construct a multidimensional cube by product line, geography and time period. Rest assured that this analysis goes on daily at Coca-Cola. Where are the winners? Where are the laggards?
  • Build a multidimensional cube around Coca-Cola's sales, general and administrative expenses. What are the campaigns that make up SG&A? How much does each campaign cost, and how much does each contribute to the bottom line?
  • Build a database to analyze the property, plant and equipment (PP&E) expenses. Where are the major pieces of equipment? How much more will each piece be depreciated and when? What is its useful life?

-- Barry Wilderman

This is a high number. Imagine making something for 30 cents and selling it for a dollar. That's what Coca-Cola does, day in and day out. A useful exercise is to compare its gross margin number with those of other consumer-packaged goods companies and companies in other industries.

It is also interesting to note that Coca-Cola's selling, general and administrative expenses of $8.7 billion (called SG&A for short) exceeded its manufacturing expense on the income statement. The conclusion: It is more expensive to sell and market Coca-Cola products than to manufacture them. This is not a criticism, but a reflection of the realities of competing in the beverage business.

Such analysis leads to further questions. For one, who has the real power at Coca-Cola: the manufacturing executives or the marketing executives? The sidebar suggests other kinds of financial analytics available by constructing simple databases and multidimensional cubes in BI tools.

The income statement reveals another curious fact. Coca-Cola reported interest expenses of $36 million. In other words, the company made money on interest expense, as if it were a bank. This is strange and merits further analysis.

The trick, of course, is to keep as much profit as possible for every dollar earned, and Coca-Cola did well here. Adding in expenses for income tax, the company earned nearly $4 billion in 2001. This yields a net margin of 19.8% (net income divided by revenue).

An income statement is also useful for investment analysis. At the end of 2001, Coca-Cola had 2.5 million shares of stock outstanding and earnings per share of $1.60. What would you pay for a stock that yields $1.60 a share, year after year? Some "value" investors would say $16 a share, or about 10 times earnings. This price-to-earnings (P/E) ratio is a commonly reported metric in securities analysis. The actual Coca-Cola stock price was $47.15 at the end of 2001, for a P/E ratio of 29.5.

Understanding the balance sheet

Let's look at the statement of liabilities and shareholder equity. The assets of Coca-Cola are either owned by shareholders or essentially financed with borrowed funds. A commonly used analytic measure is the debt-to-equity ratio, which is approximately 1 for Coca-Cola -- a healthy number. Younger companies usually have to take on more debt than this. But over time, it is important for their equity to increase as the shareholders begin to own a higher percentage of the company than the banks.

Let's examine Coca-Cola's current assets and current liabilities. One commonly used measure is working capital, defined as current assets minus current liabilities. In Coca-Cola's case, the calculation is:

Current assets [$7,171 million] - current liabilities [$8,429 million]
= working capital [-$126 million]

Current assets are meant to pay off current liabilities. Will the negative figure be a problem for Coca-Cola? No. The company could pay off its current liabilities from short-term revenue, borrow money or take more time to pay its suppliers.

More on the general ledger

Read a general ledger tutorial

See a definition of general ledger

Learn about merging general ledger software

In such situations, the Coca-Cola treasury department is expected to step in and provide creative solutions.

Finally, what can be said about Coca-Cola's property, plant and equipment (PP&E), which had a book value of $6,888 million and an accumulated depreciation of $2,652 million? This seems like fairly steep depreciation. Will the equipment hold up, or will there have to be a large capital infusion to buy new equipment? How will that impact the company?

IT professionals who support the finance function must develop a comfort level with financial statements like Coca-Cola's. They can then translate their new understanding into a related BI and analytics structure and collaborate more effectively with line-of-business functions in the company.

About the author:
Barry Wilderman has more than 30 years of experience as an industry analyst, researcher and consultant at such companies as META Group, Lawson Software, SalesOps Analytics, and McKinsey & Company. He is currently president of Wilderman Associates. Contact him at [email protected] and on Twitter at @BarryWilderman.

Dig Deeper on ERP accounting software