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.
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.