by Christopher M. Wright
Mr. Wright is a D.C. ASBPE Board Member and freelance writer
specializing in business and technology topics for national
and international clients. [www.sinewaveinvestor.com]
Using Excel to calculate corporate financial ratios was the
focus of ASBPE's first-ever teleseminar on December 14,
2006, jointly sponsored by ASBPE's New York and Washington,
D.C. chapters. The presenter was Steve Ross, author of 19
books and a former associate professor at the Columbia
University Graduate School of Journalism. Ross currently
edits Broadband Properties (www.bbpmag.com) and teaches
business writing at the Harvard Extension.
Two additional sessions on how to analyze company financial
statements are planned for January and February 2007 (dates
TBA). Plans call for a DVD of the sessions to be sold
through the ASBPE website.
During the first session, Ross walked the participants
through a pre-populated Excel spreadsheet containing 1987
profit and sales figures for 15 large US companies.
Participants, who received the spreadsheet and explanatory
material in advance, calculated key business indicators,
with Ross providing step-by-step instructions how to enter
formulas and display results. At the end, participants had a
ready-made spreadsheet for use in assessing companies they
The key business indicators included profit margin, earnings
per share, price-earnings ratio, and return on investment
(ROI). "Sales is a bit of a slippery number," Ross said.
"Profits is also a slippery number." Sales includes more
than product revenue. "It's also the profits and losses
[IBM] made on currency fluctuations and buying and selling
assets," he said.
As for profits, "are they the same numbers that are reported
to the tax people? No, they are not. Can you see what they
report to the Internal Revenue Service? No, you can't," Ross
said. Profits divided by sales gives you the company's
profit margin, he explained.
Ross then moved into calculating the total worth of a
company's stock, which he termed 'market value'. "Here is
where the misreporting happens," Ross said. Just because
IBM's stock was worth $68 billion (in 1987) doesn't mean
that somebody could buy the entire company for that amount.
"That's impossible because, what would happen as soon as
they started buying shares?," Ross asked. "The share price
would go up, the market value would increase."
After calculating earnings per share (EPS), Ross discussed
the price-earnings (PE) ratio, which is the price of one
share of a company's stock divided by the company's earnings
per share. The PE ratio is "perhaps, next to profits and
profit margin, really the most important key to finding a
story," Ross said. "The higher the price-earnings ratio, the
higher the expectations of the stock that it's going to do
well in the future and expectations are always a story. Find
out why such expectations are happening - is it hype or is
He then contrasted General Electric with a PE of 18.8 (in
1987) with Ford at 4.8. The market was predicting a better
future for GE than Ford and was right in that case, but is
not always infallible. The old AT&T had an average PE in
1987, signaling average investor expectations, but the
company steadily declined until its progeny SBC
Communications moved to take it over in 2005.
There is no PE if the company is losing money, Ross said. In
that case, the PE is generally shown as 'n/a' or 'n/c' (not
applicable or not calculable). "You're not supposed to be
able to calculate a price-earnings ratio when there are no
earnings," Ross said. That sometimes happens when the
company "takes a haircut," i.e., when the company knows it
will lose money in a particular quarter, it will sometimes
pack all the bad news and possible writedowns from
money-losing activities into one quarter since the stock
price is going to take a hit anyway. Investors recognize
this is being done and mute their reaction accordingly. The
point for journalists, Ross said, is not to write wild
stories (about Ford, for example) about how the company is
close to bankruptcy when noncash writedowns don't threaten
the company's viability as a going concern.
Return on investment is another key profitability indicator,
the inverse of the PE ratio. "It's what you earn divided by
the price," Ross said. This earnings yield is not the same
as cash dividends or dividend yield, he noted. "Remember,
most of the money [the company] is using to reinvest and
grow the company. Only a little bit comes back in your
dividend," he said.
Ross next showed how Excel can calculate the average profit
margin for an entire group of companies. If a company's
margin is below the average, journalists can look for
explanations as to why the company compares unfavorably to
others - tougher competition? government regulation?
supply-demand imbalance affecting the price for the
company's products? Oil companies tend to be doing better
today than they were in 1987, Ross said, because oil
supplies are tighter now relative to demand than they were
at that time. "The demand is far higher," he said.
Ross wound up the presentation by showing how journalists
can sort the information in the spreadsheet (from highest to
lowest PE, for example) and generate corresponding charts
that can be exported to photo editing software or web pages.
# # #