DATA MANAGEMENT

In a Nutshell

By Donald P. Crivellone © 1997

How well do you know your business?
The good, the bad, and the ugly

The amount of data you maintain is your choice, however, remember....it’s your business or your responsibly as a manager.

In addition to maintaining a wide variety of data required to manage a profitable and successful business (two separate issues), others use data to make decisions about your business .. bankers, vendors, insurance companies, private lenders, non active partners and shareholders. You certainly want to know the data to avoid being embarrassed by not having answers or worst, not understanding the questions. Smart providers of credit to your business will proceed beyond the initial financial statements (balance sheet and earnings statement) to truly analyze your financial performance. Many providers of credit have computer programs that quickly analyze many factors of your business from basic financial statements. However, they need additional data to analyze the aging of accounts receivable, accounts payable, inventory and data that falls under periodic review.

This article is principally directed at businesses with sales up to $5,000,000, yet there are well documented cases of Fortune 500 businesses that may have benefited by paying greater attention to data management and some financial institutions that may have benefited by applying some of the basics of data management to their loans prior to charging them off.

No one would expect businesses with sales up to $5,000,000 to maintain all the data and spreads suggested in this article, plus customize data for their specific business. The most responsible person in an organization must make the decision of not enough, enough or too much data. This ability is certainly a skill, along with many other skills that determine the success or failure of a business.

It is common wisdom that many business with sales under $5,000,000 have grown because the owner has excellent knowledge of the products or services; is intelligent and hands on. However, as the business grows, one must rely on new tools to stay abreast of the trends of the business. If you are uncomfortable with your knowledge of these tried and true methods of analyzing your business there are solutions. For instance: :

• Talk to your accountant
• Spend time with a knowledgeable business consultant
• Talk to your ”commercial lending” banker.
• Self educate yourself by reading and attending courses.

It is preferred and strongly recommended that the data is “spread” at least monthly at a minimum, some information is critical enough to spread weekly and a few key items are important enough to be known daily I like to keep at least two or three years of information within a specific computer spread sheet.

Balance Sheet

Spread the normal balance sheet accounts, with specific detail that you feel is important. For instance, overall Inventory may be satisfactory or several categories may be spread such as - finished goods, raw materials, work-in-process.

I like to list several key balance sheet ratios on this spread sheet for quick reference.

· Working Capital
· Debt to Net Worth

Earnings Statement

Spread the normal Earning Statement accounts, with specific detail that you feel is important. For instance total sales may be satisfactory or several categories may be used such as - retail store, mail order, wholesale or it may be more favorable to list sales b products or services. Expenses - total insurance or general insurance, vehicle insurance, product liability or large insurance categories separately and other insurance.

Key Ratios.
· Gross Profits
· Return on Sales
· Return on Assets

Cash From operations

If you want to know what happen to your cash, this is the schedule that one should prepare monthly, rather than wait for the outside accountant to prepare it annually or semi-annually. The only possible way to understand your cash position is to understand that the balance sheet and the earnings statement are irreversibly linked. Most accounting systems do not automatically prepare this data, but it is very simple to create the data on a spread sheet.

Aging

Accounts Receivable - - - - - - - Jan Feb Mar Apr

Current
1-30 days past due
31-60 days past due
61-90 days past due
over 90 days past due
Total Receivables

Inventory

Purchased with 3 months
91 days to 180 days
180 to 165 days
over 1 year old

Accounts Payable

Sane as Accounts Receivable

All reports should be backed up by listing of individual customer, vendor, product . Other alternatives are by branch, region, division, subsidiary, product, person.

Standard Ratios:

These ratios and their interpretation are from the Robert Morris Associates Annual Studies, which can be found in most libraries. RMA is an organization, principally of senior bank lending officers. The studies include average ratios of many companies in many different industries by SIC Code (Standard Industry Classification) A very worthwhile tool for business owners. Another source is Dun and Bradstreet industry studies.

It is important to remember ratios will vary from industry to industry and companies within industries, so while it is smart to compare your business against the industry and generally accepted standards and your own businesses history. For instance a generally accepted working capital ratio is 2 to 1 and debt to worth of 4 or 5 to 1 as a minimum - regardless of industry. Spreading ratios will answer the question - are trends getting better or worse for your business?

Common Sized and Percentages
Spreading will show shifts in assets, liabilities, income and expense. And an excellent tool for industry comparisons.

Balance Sheet and Earnings Statement
Use entire balance sheet and earnings statement, these are condensed samples.

Liquidity Ratios

Current Ratio

Total current assets / total current liabilities
$456,000 / $211,000 = 2.17
An indication of a business’s ability to service its obligations.

Quick Ratio

Cash and equivalents + trade receivable / total liabilities
Ability to liquidate the business

Sales - Receivables

Net sales / trade receivables
1,350,000 / 275,000 = 4.9
The number of times trade receivables turn over in a year.

Days’ Receivables

365 / sales - receivables ratio
365 /4.9 = 74.49
Average time receivables are outstanding

Cost of Sales - Inventory

Cost of sales / inventory
The number of times inventory turns over in a year

Days Inventory

365 / Cost of sales/inventory ratio
The average length of time items are in inventory

Cost of Sales/Payables

Cost of sales / Trade payables
The number of times trade payables turn over during a year.

Days Payable

365 / Cost of sales/payables ratio

Sales - Working Capital

Net Sales / Working Capital
Measures the effective use of working capital.

Coverage Ratios

Earnings before interest and taxes - Annual Interest Expense

Earnings before interest and taxes / Annual interest expense
A business's ability to meet interest expense

Principal Debt Repayment

Net profit + Depreciation, Amortization / Current portion Long Term Debt
Measures ability to service principal debt.

Leverage Ratios

Fixed Assets - Tangible * Net Worth

Net fixed assets / Tangible net worth
Measures owners investment in fixed assets

Debt - Tangible * Net Worth

Total liabilities / Tangible net worth
750,000 / 200,00 = 3.75
Measure who is investing in the business, owners or other

* Tangible Net Worth - eliminates assets that have no value in liquidation, such as obsolete inventory and goodwill.

Non Standard Leverage Ratios, but worth knowing.
Real Net Worth:

Shareholder/owner loans to business + tangible net worth
Shareholders and owner’s can subordinate their loans to lenders
.

Market Net Worth:
Always be aware of the true market value of all your assets and how they may affect your net worth. Real estate, inventory and equipment may be worth more or less than book value. I would not include goodwill, difficult to prove.

Operating Ratios

% Profits before taxes - Tangible * Net Worth

Profit before taxes / Tangible net worth x 100
Measures the rate of return on employed capital

% Profits before taxes - Total Assets

Profit before taxes / Total assets
Measures the rate of return on employed assets

Sales - Net Fixed Assets

Net Sales / Net Fixed Assets
Measures the productivity of the business’s fixed assets

Sales - Total Assets

Net sales / Total assets

Measures the productivity of the business’s total assets

Each business , industry and management team is unique. It is important that custom ratios and informative data are created and maintained to monitor and help you manage the success of “your” business.

Trade associations, franchisers or businesses that license outlets (even if you are not a franchisee or licensee) are excellent sources to obtain proven measurements of their industry. Automobile dealers for instance are required to forward monthly a very standardized, detailed report, which includes financial statements, new model sales & margins, used cars sales, parts, service, insurance and warranty sales, and much more.

A very, very, very brief sample of some customized ratios and important information::

Basic Labor to Sales

Loaded Labor to Sales Basic labor plus FICA, FUTA, SUTA, W/Comp
A service business where loaded labor is 59% of sales.

Overhead to Labor
Used in the bidding process for the above business since labor is the critical factor in bidding

Cash + Accounts Receivable + Inventory vs. Accounts Payable

Cash-1,000 + Account Rec -25,000 + Inventory- 50.000 / Accounts Payable-52,000 = 68%
To control an owner who loves inventory - Accounts Payable set as a maximum of 50% or an amount that works for your business, based on its specific factors. A 68% ratio in this specific business creates stress for owner as vendors press for payment.

Investments to market value

Real Net Worth and Market Net Worth - Previously discussed.

Economic Data - CPI, new housing starts, foreign exchange, conssumer confidence

Market Prices for Products - Feeder and finished cattle, eggs at ranch, cwt corn.

Speeds of your favorite horses.

Project profitabllity - estimate vs actual

Average Sale

Sales by product

Sales by person Balance responsibility - bank tellers serve customers 1st, sell 2nd.

Hours billed by professionals - be careful of fictitious billing to "look good."

Cost of raw materials, key purchases
Grain for cattle feeding - parts - sugar for candy manufacturing

Quality control
Rejects, timely delivery or installations - complaints

Amortization of Debt Schedule
What principal reductions on debt obligations do you have each month.

Profit by division or product Transfer pricing is a key to fair analysis.

Personnel turnover

Loans by category: Outstanding, new, delinquent, losses, liquidated losses

Businesses with multiple stockholders or partners
Earning per share and Price - Earnings Ratio

Sales Calls, by person total
First are the sales calls being made... then measure quality, success.

Sales - Number of Transactions Size, product
1,000 $1.00 items or 1 $1,000 item
10 $1,000 loans or 1 $1,000,000 loan

Cross Sales New sales to existing customers
Add-0n and Upgraded Sales
Sell a trailer hitch - sell a hitch cover.
Sell a computer - sell software
Sell a stereo - additional speakers, or bigger speakers

Historic Monthly Sales

MONTHLY SALES (a real chart used for a small business)

Product profitability
A must for any business with multiple products and services. Important for individual relationships.

Customer profitability This can must be performed if your business has relationships with other businesses, large groups, multiple users of products and services.

Pricing With the knowledge learned in the above exercises pricing products is easier, although pricing is not always a factor of the facts, but can be of perception.

Competitive analysis Large organizations have professional product managers who maintain data and files on competitors and even visit “shop” their competitors, buy their products, etc. Owners and managers of all businesses should “shop” their competitors to stay abreast of their products, services, service levels, pricing, quality, etc.

Industry comparative Stay active in industry activities. Attend conventions, subscribe and read magazines, compare your individual business - financial statements and ratios to industry statistics, utilized other data such as Dun & Bradstreet, Robert Morris Associates.

Goals vs. actual
If you prepare budgets - and you should.
If you prepare personal goals - and you should’
Check them against actual performance at least quarterly.
Projedt estimates vs actual results.

Particularly those that show comparative history. Spot graphs are as good as spot financial statements, few of us humans possess memories that can utilize spot data to help us run our businesses.

A former Fortune 500 company - in the recreational vehicles business, utilized a practice of booking the preponderance of its overhead during winter months into inventory, and then it wrote it off to sales as inventory during the heavy sales months in the spring and summer. One summer the sales did not materialize. The lending institution had no idea of this practice, it obviously did not perform audits or request detailed inventory agings and they based their loan on inventory values. The bank suffered a $26 million dollar loss. Not much value in “paper” expenses utilized in the winter months.

A former Fortune 500 company - in the discount retail business (similar to Wal Mart and Target) crashed when financial institutions ceased to provide them with cash upon the discovery the company’s cash was always generated from borrowings and not from its operations. They once were profitable and then one day they had no profits, no cash, but lots of assets. Financial institutions did not perform detailed Cash From Operations analysis monthly or did not carefully read the annual Sources and Uses of Funds Statement, Financial institutions lost hundreds of millions of dollars in losses.

A former furniture store with sales of $25,000,000 was well managed, as testified by the owner, “I know everything going on in this business and have the data at my finger tips.” He was seeking a working capital loan due to a shortage of funds. A consultant insisted on seeing the accounts receivable agings among other data as he needed them to prepare the loan request. Funny thing, he did not have the data.... at his finger tips. The next day the controller delivered the receivable spread to the owner, the owner was shocked. The over 90 days delinquent category was over $500,000 concentrated in “contracts” that should have been delivered and sold to a finance company for face value. A loan was not required after the “I know everything going on in this business manager” sold the $500,000 worth of contracts to the finance company. The business survived this management glitch, but in the long term it did not survive.

Fact: Cash is tight when sales are rising, cash is better when sales are declining.