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

BY STANLEY ZAROWIN

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.

 

SPONSORED REPORT

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.