by Charley Kyd on October 21, 2011
Excel dashboard reports typically show internal data. But you can easily add public data that managers need.
Here, for example, is a modified version of Report 5 from IncSight DB. In the original version, the entire report showed internal data. But in this version, the nine small charts show web data of interest to Acme’s managers.
- Top Competitors: Row 1 shows actual revenue for Acme’s top competitors: Apple, Dell, and HP.
- Key Economic Data: Row 2 shows key economic data: the key currency exchange rate, the number of employees in its target market, and the Leading Index of Economic Indicators.
- Prices of Key Resources: Row 3 shows trends in the prices of key resources: gasoline, cardboard shipping containers, and electricity.
The data for these nine charts comes from [click to continue…]
by Charley Kyd on October 20, 2011
These days, it’s important to keep a close watch on the rate of inflation. This is because there’s a strong likelihood that inflation will sky-rocket when the economy starts to improve.
This chart tracks a company's unique rate of inflation. Because Excel formulas are linked to data maintained by the Bureau of Labor Statistics, the chart updates automatically after the data changes and the workbook is opened.
But which inflation rate should you monitor? The one for consumer purchases? The one for producer purchases? Perhaps you should monitor both.
But also, you certainly should monitor your own company’s private rate of inflation.
That’s right. Because your company buys different stuff than most other companies, and in different proportions, it has a unique rate of inflation. And that rate could be significantly higher or lower than the average rates of inflation discussed in the news.
It therefore makes sense to [click to continue…]
by Charley Kyd on October 19, 2011
When I worked for manufacturers, I always wanted to be able to analyze the long-term history of the financial statements of public companies. This information would have allowed me to answer at least five kinds of questions for my managers:
First, I had questions about the direction our largest customers and competitors were heading.
If their short-term financial history is weak or strong, has it always been that way? Or is this a new condition? Are their customers paying their bills? Is their inventory growing too fast? How’s their cash flow?
Second, I had questions about benchmarking our performance against our industry, or about our customers’ performance in their industries.
Do our own financial ratios [click to continue…]
by Charley Kyd on October 18, 2011
The FRED database is my favorite source of economic data.
Even though FRED (sometimes called FRED2) is offered by the Federal Reserve Bank of St. Louis, most of their data has nothing to do with banks. Instead, FRED offers a wide variety of economic data.
FRED has data about prices, employment and unemployment, exchange rates, banking, leading indexes, gross domestic products, interest rates, international data, US regional data, and more. It’s a great place to begin your exploration of economic data.
Although the FRB offers the ability to download workbooks with any of their data, I seldom use that feature. The workbooks take a lot of effort to download, manage, and use. And we must repeat that effort each time we need to update the data.
That’s why I [click to continue…]
by Charley Kyd on October 17, 2011
If our rotting economy causes most of our business problems these days, why is it that so few Excel reports and analyses include data about the economy?
These days, the road to business success seems to lead through a maze. Relevant economic data is like a photo taken from above the maze; it offers a view that helps managers find a path to safety. So it’s a mistake to deprive them of this information.
All the economic and business data your managers need is available on the web, probably for free. And before the end of the month, you’ll be able to use spreadsheet formulas to display that data in your reports, analyses, and forecasts.
Here are ten ways that spreadsheet data could help to improve your company’s performance… [click to continue…]
by Charley Kyd on July 7, 2011
The Excel 2010 help file provides more information about the CLEAN function than earlier versions:
“Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.
“Important: The CLEAN function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set … there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters.”
Actually, CLEAN is more powerful than this information describes. [click to continue…]
by Charley Kyd on July 1, 2011
This morning’s mail included this note:
Dear Sir,
My boss recently purchased an electronic copy of your “Dashboard Reporting With Excel” book. He asked me to do some reporting that involve times series plots with multiple trend lines on the same data series.
I have searched on the internet to learn how to do it but with no success. Could you kindly explain how I can do this, PLEASE?
By “multiple trend lines” I assume my reader wants to see trends for multiple periods that are a subset of the total number of periods available. So let’s see how to do this…
For the past several weeks I’ve been working on a bonus offer that will allow Excel users to create dashboards of stock data and economic data from the web. So I happened to have the perfect data series to answer this question: Apple’s revenue history for the past 50 quarters.
This image illustrates what I think my reader wants, and probably also illustrates why he wants it.
[click to continue…]
by Charley Kyd on May 25, 2011
by Charley Kyd on May 25, 2011
My previous post described a macro that finds the total time necessary to calculate Excel a specified number of times. This post describes the workbook I set up to test various lookup methods. The next post will explain what I learned from these tests.
The testing workbook needed to meet two criteria.
First, it needed to have a database large enough to cause the lookup formulas to work fairly hard. Second, it needed to be volatile.
The first criterion is self-explanatory. But what’s volatility? [click to continue…]
by Charley Kyd on May 23, 2011
I recently exchanged email with an Excel expert who had concluded that VLOOKUP was Excel’s fastest lookup method. It was even faster, he said, than INDEX-MATCH. To support this surprising conclusion, he sent me his testing macro.
The macro was cleverly written, but had two fatal flaws. First, his testing method had so much overhead that it overshadowed the calculation times he was trying to test. Second, the overhead varied between calculation methods.
This is why his conclusions were incorrect.
In this post, I’m going to show you my macro that tests calculation times for alternate report designs. The macro is simple, but it works. [click to continue…]
