MULTI IN ONE: Scenarios in Excel

<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="leftMargin" style="-webkit-text-stroke-width: 0px; background-color: white; color: black; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; margin-left: 1em; margin-right: 1em; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px;">
</div>
<br />
<div class="leftMargin" style="-webkit-text-stroke-width: 0px; background-color: white; color: black; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; margin-left: 1em; margin-right: 1em; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px;">
<div align="left">
Scenarios come under the heading of "What-If Analysis" in Excel. They are similar to tables in that you are changing values to get new results. For example, What if I reduce the amount I'm spending on food? How much will I have left then? Scenarios can be saved, so that you can apply them with a quick click of the mouse.</div>
An example of a scenario you might want to create is a family budget. You can then make changes to individual amounts, like food, clothes, or fuel, and see how these changes effect your overall budget.<br />
<div align="center">
<br /></div>
We'll see how they work now, as we tackle a family budget. So, create the spreadsheet below:<br />
<div align="center">
<img alt="A Family Budget Spreadsheet in Excel 2007" height="299" src="http://www.homeandlearn.co.uk/excel2007/images/2Scenarios.gif" width="488" /></div>
<div align="left">
The figure in B12 above is just a SUM function, and is your total debts (=SUM(B3:B10). The figure in D3 is how much you have to spend each month (not a lot!). The figure in D13 is how much you have left after you deduct all your debts. In cell D13, then, enter =D3 - B12</div>
With only 46 pounds spending money left each month, clearly some changes have to be made. We'll create a scenario to see what effect the various budgets cuts have.<br />
<ul>
<li>From the top of Excel click the <b>Data</b> menu</li>
<li>On the Data menu, locate the <b>Data Tools</b> panel</li>
<li>Click on the <b>What if Analysis</b> item, and select <b>Scenario Manager </b>from the menu:</li>
</ul>
<div align="center">
<img alt="The Data Tools panel in Excel 2007" height="138" src="http://www.homeandlearn.co.uk/excel2007/images/2Scenariosmenu.gif" width="431" /></div>
<div align="left">
When you click <b>Scenario Manager</b>, you should the following dialogue box:</div>
<div align="center">
<img alt="The Scenario Manager dialogue box" height="359" src="http://www.homeandlearn.co.uk/excel2007/images/2ScenariosDB.gif" width="363" /></div>
<div align="left">
We want to create a new scenario. So click the <b>Add</b> button. You'll then get another dialogue box popping up:</div>
<div align="center">
<img alt="Add Scenario" height="325" src="http://www.homeandlearn.co.uk/excel2007/images/2ScenariosAddDB.gif" width="390" /></div>
<div align="left">
The J22 in the image is just whatever cell you had selected when you brought up the dialogue boxes. We'll change this. First, type a Name for your Scenario in the <b>Scenario Name</b> box. Call it <b>Original Budget</b>.</div>
<div align="center">
<ins style="background-color: transparent; border: none; display: inline-table; height: 60px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 468px;"><ins id="aswift_1_anchor" style="background-color: transparent; border: none; display: block; height: 60px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 468px;"><iframe allowtransparency="true" frameborder="0" height="60" hspace="0" id="aswift_1" marginheight="0" marginwidth="0" name="aswift_1" scrolling="no" style="left: 0px; position: absolute; top: 0px;" vspace="0" width="468"></iframe></ins></ins></div>
Excel now needs you to enter which cells in your spreadsheet will be changing. In this first scenario, nothing will be changing (because it's our original). But we still need to specify which cells will be changing. Let's try to reduce the Food bill, the Clothes Bill, and the Phone bill. These are in cells B7 to B9 in our spreadsheet. So in the <b>Changing Cells</b> box, enter B7:B9<br />
Don't forget to include the colon in the middle! But your Add Scenario box should look like this:<br />
<div align="center">
<img height="325" src="http://www.homeandlearn.co.uk/excel2007/images/2Scenarios2.gif" width="390" /></div>
Click OK and Excel will ask you for some values:<br />
<div align="center">
<img alt="Excel 2007 Scenario Values" height="168" src="http://www.homeandlearn.co.uk/excel2007/images/2Scenarios3.gif" width="310" /></div>
We don't want any values to change in this first scenario, so just click OK. You will be taken back to the Scenario Manager box. It should now look like this:<br />
<div align="center">
<img height="360" src="http://www.homeandlearn.co.uk/excel2007/images/2Scenarios4.gif" width="347" /></div>
Now that we have one scenario set up, we can add a second one. This is where we'll enter some new values - our savings.<br />
Click the Add button again. You'll get the Add Scenario dialogue box back up. Type a new Name, something like <b>Budget Two</b>. The Changing Cells area should already say B7:B9. So just click OK.<br />
You will be taken to the Scenario Values dialogue box again. This time, we do want to change the values. Enter the same ones as in the image below:<br />
<div align="center">
<img height="168" src="http://www.homeandlearn.co.uk/excel2007/images/2Scenarios5.gif" width="310" /></div>
<div align="left">
These are the new values for our Budget. Click OK and you'll be taken back to the Scenario Manager. This time, you'll have two scenarios to view:</div>
<div align="center">
<img height="360" src="http://www.homeandlearn.co.uk/excel2007/images/2Scenarios6.gif" width="347" /></div>
<div align="left">
As you can see, we have our Original Budget, and Budget Two. With Budget Two selected, click the <b>Show</b> button at the bottom. The values in your spreadsheet will change, and the new budget will be calculated. The image below shows what it looks like in the spreadsheet:</div>
<div align="center">
<img border="1" height="660" src="http://www.homeandlearn.co.uk/excel2007/images/2Scenarios7.gif" width="423" /></div>
<div align="left">
Click on the Original Budget to highlight it. Then click the Show button. The first values will be displayed!</div>
Click the <b>Close</b> button on the dialogue box when you're done.<br />
<br />So a Scenario offers you different ways to view a set of figures, and allows you to switch between them quite easily.<br />
<div align="center">
<br /></div>
<h3 align="left">
How to Create a Report from a Scenario</h3>
<div align="left">
Another thing you can do with a scenario is create a report. To create a report from your scenarios, do the following:</div>
<ul>
<li>Click on <b>Data</b> from the Excel menu bar</li>
<li>Locate the <b>Data Tools</b> panel</li>
<li>On the Data Tools panel, click <b>What if Analysis</b></li>
<li>From the What if Analysis menu, click <b>Scenario Manager</b></li>
<li>From the Scenario Manager dialogue box, click the Summary button to see the following dialogue box:</li>
</ul>
<div align="center">
<img alt="Scenario Summary in Excel 2007" height="179" src="http://www.homeandlearn.co.uk/excel2007/images/2ScenarioSummaryDB.gif" width="228" /></div>
<div align="left">
What you're doing here is selecting cells to go in your report. To change the cells, click on your spreadsheet. Click individual cells by holding down the CTRL key on your keyboard, and clicking a cell with your left mouse button. Select the cells D3, B12 and D13. If you want to get rid of a highlighted cell, just click inside it again with the CTRL key held down. Click OK when you've selected the cells. Excel will then create your Scenario Summary:</div>
<div align="center">
<img alt="A Scenario Summary in report format" border="1" height="271" src="http://www.homeandlearn.co.uk/excel2007/images/2ScenarioSummary.gif" width="430" /></div>
<div align="left">
All right, it's not terribly easy to read, but it looks pretty enough. Perhaps it will be enough to convince our family to change their ways. Unlikely, but a nice diagram never hurts!</div>
<br />
We'll now move on to Goal Seek.</div>
</div>

Scenarios in Excel

Scenarios come under the heading of "What-If Analysis" in Excel. They are similar to tables in that you are changing values...