Problem solve Get help with specific problems with your technologies, process and projects.

Being prepared for anything: Using the scenario feature in Calc

Save time in figuring out the bottom line with the scenarios feature in OpenOffice's Calc tool.

I've been there. We've all been there. You're sitting there in the third hour of a boring meeting, nibbling on the last doughnut and trying to figure out what your income and expenses would have to be to move to a small island in British Columbia and support yourself with your macrame skills. Or you're giving a presentation to the director and a couple veeps and one of them just asked you, "OK, I like those numbers, but what if we sold the routers for $20,000 and outsourced all the work to Budapest?"

The common thread here is multiple scenarios. You want to see the effect of different values for the items that affect your bottom line: gross income, expenses, macrame yarn costs (buying in bulk saves you a lot of money), etc.

More OpenOffice tips:

Bridging the gap between Office and OpenOffice

Smart formatting for better compatibility between and Microsoft Office

Easing migration with the menu and toolbar configuration tools

You can copy and paste, retyping the values each time, or you can tell your director that you'll, um, get back to her this afternoon. But the clever, slick, timesaving approach is to use the Calc scenarios feature.

Let's say you're trying to figure out what kind of car to buy. You might buy a cheap used car with iffy mileage and high repair costs, and you'd probably need to buy two to get you through the next ten years, but heck, it's only $5,000. Other choices would be a cheap new car or a more expensive new car. Over ten years, which will end up costing you the least?

You have different values for cost of the car, the mileage and the monthly repair costs. Your calculation for what the car will cost you over ten years is based on those three variables. Scenarios let you provide different sets of those values, so that you can see the effect in the calculation.

Here are the scenarios I set up for the car example. You can see the different values for a cheap used car, a cheap new car and mid-price new car, as well as the effect on the calculations.

Click here if you'd like to download the file.

Creating scenarios

Set up the spreadsheet as usual with one of the set of values. Just be sure to leave at least one blank row above the set of values that will vary. Set up all the information, the headings, the calculations, etc. I've included just one calculation here to keep the example simple, but you might easily have many calculations based on these variables.

Then it's time to create the actual scenarios. Select the values that you want to vary.

Choose Tools > Scenarios. In the window that appears, type the name that you want to appear in the spreadsheet and select the color that you want.

The Copy Back option is marked by default. This means that when you select a scenario (there'll be at least two), you can type in new values and those will be saved, overwriting the old ones. Let's say you set up the scenario and later realize that you want 100 instead of 150 for Repairs in the Cheap Used Car scenario. If Copy Back was marked when you originally created the scenario, then you can do this and 100 is now the new value for Repairs in that scenario. If Copy Back wasn't marked, you can certainly still type in 100, but the next time you switch scenarios, Repair will revert to the original value of 150. What you select just depends on how you think you and others will be using the value.

There's also a Protect From Changes checkbox that I suggest you ignore, since Copy Back already gives you a lot of value. Protection from changes is either not working or far more complicated than it should be for human use -- protection seems to be on only if you protect the cells and the sheet, and if you do that, you can't switch between scenarios.

When you're done, click OK. You'll see the same values, but with a dropdown list as shown. The value you typed appears above the values. If necessary, widen the column to make room for the title of the scenario and the dropdown list button.

What you do now depends on what you selected for the Copy Back checkbox for the previous scenario. (Copy Back is marked by default.)

* If you left Copy Back marked, select the same cells and choose Tools > Scenarios again. Type the name of the next scenario, such as Cheap New Car or whatever title ties this set of variables together. Select a color, be sure to leave Copy Back marked again, and click OK. Now, type the new values for this scenario in the fields, overwriting the old values.

* If you unmarked Copy Back, then type the new values for the next scenario in the cells, overwriting the old values. Now choose Tools > Scenarios again. Type the name of the next scenario, such as Cheap New Car or whatever title ties this set of variables together. You can mark Copy Back or not, but it's best to be consistent within your scenario set just to prevent confusion. Select a color and click OK.

The old values were saved with the other scenario. Click on the dropdown list button and choose the first scenario you set up -- you'll see that those values are still there.

Continue setting up as many additional scenarios as you need. If necessary, you can go back and change any of the values in any of the scenarios. Each change and each additional scenario will be reflected in any calculations based on these values.

Scenarios let you easily show multiple outcomes

I like scenarios, despite the confusing Prevent Changes option and that I haven't figure out yet how to modify them once they're created. And not just because I occasionally think about what it would take to live on a small island in British Columbia. Scenarios let you be prepared in meetings, make comparisons easily and generally make better decisions by looking at all your options.

Solveig Haugland has worked as an instructor, course developer, author and technical writer in the high-tech industry for 15 years, for employers including Microsoft Great Plains, Sun Microsystems,and BEA. Currently, Solveig is a StarOffice and instructor, author, and freelance technical writer. She is also co-author, with Floyd Jones, of three books: Staroffice 5.2 Companion, Staroffice 6.0 Office Suite Companion and OpenOffice.Org 1.0 Resource Kit, published by Prentice Hall PTR. Her fourth book, on 2.0, is coming this summer. For more tips on working in OpenOffice, visit Solveig's OpenOffice blog.

Dig Deeper on Linux servers

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.