When each item in a primary list is associated with a different collection of items in a group of secondary lists, you can use cascading lists boxes to manage those secondary lists.

You could use a primary-secondary list structure like this in many ways. For example…

  • The primary list could be names of departments and the secondary lists could be the names of people who work in each department.
  • The primary list could be the names of competitors and the secondary lists could be the regions in which each competitor is active.
  • The primary list could be the names of vendors and the secondary list could be the products you buy from each vendor.
  • Etc.

(Click here to continue.)

Most Excel reports, forecasts, and analyses I’ve seen in my career could serve as excellent examples of what NOT to do in Excel.

These reports fail for at least one of three reasons: bad strategy, bad structure, or bad technique. In this article, I’ll cover best practices in the general design strategy that you should use to create your reports. In future articles, I’ll cover report structure and technique. And in all articles, for simplicity, I’ll use “report” as the general term for all reports, analyses, forecasts, models, and so on.

In my experience, few Excel users actually think about their basic Excel reporting strategies. This a shame, because bad strategy puts many users on the path to bad reports before they even launch Excel.

Here’s how to avoid the worst reporting strategies…

[…click to continue]

Introduction to Probabilistic Simulations in Excel

by Charley Kyd on March 22, 2016

(This is the first article in a series. The second article is, How to Create Monte Carlo Models and Forecasts Using Excel Data Tables.)

Back when I created models and forecasts for employers, I KNEW that my results were going to be wrong, and I had no practical way to express any degree of uncertainty in my results.

For example, if I were forecasting profits for a period, my spreadsheet would use one number for my forecast of sales in a period, another number for my forecast of operating expenses, another number for my cost-of-goods-sold percentage, and so on.

I knew that each predicted number in my forecast probably would land somewhere between a best-case and worst-case value…probably as determined by a normal distribution as shown in this Excel chart.

[…click to continue]

(This is the second article in a series. The first article is, Introduction to Probabilistic Simulations in Excel.)

During more than three decades of working with spreadsheets in business, most of the spreadsheet models and forecasts I’ve seen have used what statisticians call a deterministicmethod.

To illustrate, if I were forecasting profits for a period, a deterministic model would use one number for my forecast of sales, another number for my forecast of operating expenses, and so on.

Instead, a more useful method would take a probabilistic approach, supported by the Monte Carlo method.

In the past, without thinking much about it, I wrote macros to support the Monte Carlo method.

But I was wrong back then. There’s a much easier way.

[…click to continue]

When the Kindle DX was introduced in 2009, a writer from the Wall Street Journal emailed me to ask whether I thought the new Kindle DX was worth its $489 price.

I replied that it could be, but not necessarily for the reasons that Amazon suggested in its ads. I told him, in fact, that Kindle could transform management reporting.

Because my response missed his deadline, he didn’t include it in his story. Even so, I thought the reporting idea was too important to drop. So I posted an article about how intriguing the Kindle DX potential was for management reporting with Excel.

[…click to continue]

How to Save Multiple Excel Reports to One PDF File

by Charley Kyd on October 26, 2015

A great way to distribute your Excel reports is to save them in a PDF file and then distribute the file.

People who receive your PDF file can read it on their desktops and mobile devices, and they can print it easily.

The only limitation is that without using a third-party add-in, all reports must be in the same workbook.

How to Prepare Your Reports for PDF

To prepare your reports for PDF, you take the same steps that you would to print your reports to a printer. That is, you assign print areas to all areas of your workbook that you want to print or save as PDF.

[…click to continue]

Five Ways to Make Your Excel Reports Incredibly Popular

by Charley Kyd on October 26, 2015

Years ago, I was the young CFO of a small company that was purchased by a Fortune-200 company. Within days of the sale, I found myself at corporate headquarters, so I could learn how big companies operate.

Early in the visit, I found myself in the office of the Chief Technology Officer, who droned on and on about the power of their new information system. “We can give our managers any information they need,” he told me several times.

Later, I visited the Vice President of Manufacturing, who had a tall stack of computer printouts on his desk. “That’s quite a stack of reports,” I said. “Do they give you the information you need?”

“No damn way,” he said. “They’re a complete waste of my time.”

In recent years, Excel users working for my coaching and consulting clients have had similar problems with their reports.

[…click to continue]

It all seems so simple…

To improve your forecasts of sales or other measures, you simply need to find leading indicators…measures that are highly correlated with your key measures, but with a time lag. And then you use those leading indicators as the basis of your forecast.

But when you try to make it all work, that simple idea can become a huge challenge.

Suppose, for example, that the blue Data1 line in the following chart shows what you spend in advertising. And suppose that the red Data2 line shows the amount of your sales.

At first glance, it looks like you REALLY need to change your ad strategy, because…

[…click to continue]

In Part 1 of this discussion about automated cross correlations, which are time-shifted correlations, I explained how to set up the cross-correlation Data Table shown below, in columns J and K.

Now it’s time to set up the actual report that’s shown in the range A3:H17.

(You can download the workbook at the link.)

[…click to continue]

Excel’s Five Annuity Functions

by Charley Kyd on October 26, 2015

“Help!” the message said. “I know the payment, interest rate, and current balance of a loan, and I need to calculate the number of months it will take to pay it off. How do I do it in Excel?”

Honestly, I haven’t calculated the number of periods for quite a while. So here’s how I quickly found the worksheet functions for all five of Excel’s annuity calculations, including the one that my visitor needed…

Most anyone who works with loans and investments in Excel knows about the PMT function. So that’s where I started.

[…click to continue]