Using Scenarios in OpenOffice Calc spreadsheets to figure out what to do

My friend Stephanie posted on Facebook that she was using a spreadsheet to figure out which car to buy. My mind immediately flew to scenarios. Here's an example of how you could use two scenarios to really get serious with pros and cons and the amount of money you'd be spending.

Let's say that you're not sure yet which job you'll take, a job with IBM or whether you'll continue to work at home. And you're also considering various cars, and what the cost of ownership will be depending on whether you work at home or have to drive to the IBM site and (in this case, just for fun) pay for parking. Typically, or at least some of the time, you only have one scenario. But I'm showing two in this situation that both affect that big bottom line calculation, just because it shows the power of using them together.

Here's the file Download Scenario_start as it is when I start the example, and here's the file Download Scenarios_done finished.

First thing you do, set up the spreadsheet with the data for one of the scenarios (a job scenario) and another scenario (a car scenario). So far you're just doing a normal spreadsheet. Put in the data, label it, then do a big complicated field that uses data from all over the spreadsheet to figure out how much it's going to cost, per year, to get the data for the car that's currently showing.

Here's how it looks in normal view.

 

Here's a closer look at the big complex Annual Cost of Use field. It brings in all the fields.

 

You've already typed in one version of each scenario but where it gets interesting is overlaying a different version of the data. You're going to do a different version of the job scenario.

So select the data (not the label) in one scenario, the job scenario here.

 

Choose Tools > Scenarios.

In the window, give the scenario the title that you want to be displayed in the spreadsheet, any notes, and select a color.

Click OK. You'll see the scenario, exactly the same data but with a dropdown list and the color you gave it. You've saved the first set of data as a set that you can go back to.

 

Now that that set of data is saved, retype a new set. You don't need to do a title here, just retype what the mileage to work would be, parking, and any other changed data, for a different situation: working at home.

 

Now, select that data again as you did last time.

Choose Tools  > Scenarios.

Give the scenario a name that you want displayed in the spreadsheet, plus notes optionally, and a different color.

Click OK. The new data is saved in this scenario, and of course it affects the bottom line.

Now you can switch between the scenario names, and the data you specified  for that scenario is displayed.

 

You can keep going as long as you need to--if you have five job offers with five different driving distances and parking costs, you would enter them all.

Typically, or at least some of the time, you only have one scenario. But I'm showing two in this situation that both affect that big bottom line calculation, just because it shows the power of using them together.

To create multiple scenarios for different cars, you do exactly the same thing. You've already got the first set of data, so select it.

 

Choose Tools > Scenarios and enter a name, notes, and a color, just like before.

 

Click OK. You see the saved scenario with the name you gave it in the window, and the color.

Now change the data for what it would be for another car.

 

Select that data, create another scenario like Subaru, Echo, Bike, etc.

 

Click OK when creating it, and you have two different ones to switch between.

Create additional car scenarios if necessary, then switch among car and job scenarios to see what the cost of annual ownership of the car would be in each permutation.

Note: Deleting the scenario isn't obvious. Here's how. Click the Navigator button or press F5 to bring it up. Click the blue-circled Scenarios button, then right-click on the scenario name and choose to delete.

---

---

Comments

[Steve]

When I set up OpenOffice 3, I must have done something wrong. Now when I hit the Indent Button in Writer nothing happens. How do I turn it back on?

[Solveig]

Hi Steve,

Try Tools > Options > Writer > General and see what your default tab stops are set to. The indent icon indents your margin as much as one default tab.

[sandmannc40]

I sure do like Open Office. I play with MS Office 2007 and Open Office 3 and there are some things Open Office can't do but that is for more in the stats area. If you want to use it for just simple and very eye opening pages like this DONE Example. It was great.

The more you play the more you learn.

Most important take notes. You can't remember everything. Cheat sheets work great.

[ezander26]

Using the above spreadsheet and clicking show grid lines, when printed only about the first 5" (five inches) of the print out have the grid lines. Is there a solution to having the entrie spread sheet print out with grid lines?

[Solveig]

I think Print Grid Lines just has the grid lines print where there's content. Does that make sense for what printed?

[Earle Zander]

To Soleig -- thanks for your response of 11-05-2009 to the question about grid lines. No, The full spreadsheet page has numbers or alpha and as stated only the first 5 inches or so will print out grid lines. This situtation is true whether in portrait or landscape. Also this situation was not a problem with Open Office Version 2.+ but surfaced with OO 3.+.

[Paul]

Hi Solveig,
Thanks for this quite interesting explanation about the scenarios ! Let's push the things a bit further now:
a) Is it possible to have a table/list displaying all the resulting values of the outcome formula for a scenario and this for each value (label) of that scenario, so that I could for instance compare side by side all the results of the car choice ?
b) Even further, would it be possible to use the various drop-down possibilities of the two independent scenarios and build a sort of pivot table with scenario 1's as row labels, scenario 2's as column labels and the data of the table being the result of the combining formula ? Such a table should tremendously help in identifying optimal solutions !
Any suggestion ? Thanks in advance.