Excel is great at looking at data in different ways, something that is known as 'what-if' analysis. In Excel this type of analysis can be obtained by physically changing the values on the sheet, or by setting up controlled scenarios, which allow you to look at the data in all sorts of controllable ways and even report the results on a separate worksheet or pivot table.

You can technically have as many scenarios as you need in complicated models, but the summary function will only show the first 255. You can have up to 32 changing cells in a scenario.

To keep it simple we have built a very small loan table showing Total Cash Price, Deposit, Amount of Loan, Interest Rate, No. Payments and Payments.

We set up scenarios for a change in interest, no. payments and deposit, using the dialogs below. The resulting scenarios can be seen in the 'Scenario Manager' dialog. Selecting a scenario from the list and selecting 'Show' will make the changes to the cell(s) contained in the scenario on the worksheet so you can see 'what-if'.

To add or edit a scenario, click the relevant button and fill in the dialog below. The text for the comment is inserted automatically and added to each time a change is made in the scenario, giving the user and date. This is also featured in the scenario summary (not shown in our example as they are hidden by the outline). You will give the scenario a meaningful name, enter the changing cell(s) and decide if users should be able to edit the scenario when the worksheet is protected. If you select this box, be sure that you uncheck the ability to change scenarios when you protect the sheet. If, when the worksheet is protected you wish to remove scenarios from the list in the 'Scenario Manager' check the 'hide' box.

After you click ok in the dialog above you will be asked for the value of the changing cell(s). Click OK and your scenario will be added to the list.

If you have already built scenarios, say from a previous budget or similar, then you can merge the scenarios from another workbook, by selecting 'Merge...' from the 'Scenario Manager' and selecting the open workbook from the dialog below.

A great feature of scenarios is to automatically build a summary report or pivot table from the changing cells in the scenarios. An example of all of our scenarios applied to a new worksheet is shown at the bottom of the page. In the 'Scenario Summary' dialog you select a worksheet summary or pivot table and select the result cells in your scenario. I our simple example it is the payments that is the result cell of the formula that we are looking for. (B8)

The automatically created report for our simple example is shown below, without modification. Showing the different payments based on the different scenarios.