Need to Create an Assortment of Financial Scenarios? It’s a Snap With Excel


In my business I have to assemble a wide range of scenarios so we can prepare negotiations on pricing certain projects, and since the scenarios usually contain many variables, that job can take hours. Any ideas for speeding the process?

Sure enough, Excel has a perfect tool for that job, and you probably won’t be surprised to learn it’s called Scenario. It lets you create an ensemble of basic circumstances and then, as you make adjustments in any of the parameters, you can save the calculated scenarios and call them up from a menu in your toolbar—just perfect to provide the key numbers at your fingertips during presentations.

But before I walk you through the setup steps, I suggest you add Scenario to your toolbar. To do that, go to Tools, Customize, click the Commands tab and under Categories, go to Tools, and under the Commands tab, go to the Scenario: icon that contains the dropdown menu (see screenshot below) and drag it into your toolbar.

Now we’ll create a short scenario, from which I’ll show you how to easily build a menu of variations that you can then access with a single click. Open a new Excel spreadsheet and, using the text in the screenshot below, fill in the information from A2 to A8. In B6, enter =B2*B3; in B7 enter =B4*B5; and in B8 enter B7-B6.

For this example, we’ll create a menu of scenarios in which the Number of attendees will rise from a minimum of 12 to a maximum of 100, and the Scenario will display the financial impact of those changes in Total profit.

To do that, highlight cells B2 to B5 (the variables in this scenario) and go up to the Scenario: icon you just created in your toolbar, typing in Minimum attendees and press Enter (see screenshot below).

Now we’ll change one variable, increasing Number of attendees to 100 and again select B2:B5, and in the Scenario: box this time type Maximum attendees and press Enter (see screenshot below).

Clicking on the down arrow in toolbar’s Scenario lets you select which scenario you wish to view. And if you go to Tools, Scenarios (see screenshot below), you can edit your selection.



Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.