EXCEL TOOLS

FOR SOURCE CLICK HERE
Excel Scenario Manager to Project Various Outcomes

Excel's Scenario Manager is a tool that can be used to determine different projected outcomes of data by changing different cells within a Worksheet model.

A scenario is a specific set of values that Excel can save for you and automatically substitute into your Worksheet. This means that you could have a spreadsheet displaying numerical data that is relevant to a certain date, month, topic or whatever and using the Scenario Manager you can enter different values into the worksheet to forecast the outcome of the data. These values (or Scenarios) can be retained for future use and are stored in a hidden part of the workbook which can be retrieved by asking the Scenario Manager to show the Scenario that uses those specific values.

Remember, all that Scenarios are are just a different version of the same worksheet with each different version using a different set of input values in the nominated cells.

For Scenarios to work correctly, you should first set up a base or default Scenario, on a worksheet in Excel. It is from this default Scenario that all other Scenarios are defined.

1. Go to Tools>Scenarios to activate the Scenario Manager. You will see a message telling you "No Scenarios are defined".
2. Choose Add to add your default Scenario.
3. Give your Scenario a name, Base, Original or Default or a name you can easily identify will be fine.
4. Click in the next box Changing cells: You will notice here that the cell that was your active cell in the Workbook will be referenced here.
5. Click the collapse dialog button to collapse this dialog box up so that you can easily select the cells from the Worksheet that you wish to reference.
6. Click on the collapse dialog button again to expand the full Scenario Manager box.
7. If you wish to type a comment in, then click into the Comment: box and do so.

There are two options at the bottom of this dialog box. They are Prevent changes and Hide, with Prevent changes being the default. If you select Prevent changes, then all your Scenarios will be locked and will be unable to be edited. It is important to note here that you MUST also protect your Worksheet via the Tools>Protection>Protect sheet option for this option to take effect. If you then with to edit your Scenarios, you must first unprotect your Worksheet, then de-select the Prevent changes option to proceed. The Hide option when selected will do exactly as said and Hide your Scenarios. This option also requires sheet protection for it to take effect.

8. Click the OK button. Once you click on the OK button the Scenario Values dialog box will appear. This is where you must enter values into the scenario cells. As the first scenario is your default Scenario, the values in the cells that we specified in the Changing Cells: box have been picked up so we need to make no changes here, we need only click the OK button. This will now take us back to the Scenario Manager and you will see the name of your Scenario.

Adding Scenarios

There is no limit to the number of Scenarios that you can apply to your worksheet model. Adding a scenario is done in basically the same way as creating a default scenario.

1. Go to Tools>Scenarios to display the Scenario Manager dialog box and select Add to display the Add Scenario dialog box.
2. Under Scenario name: give your new Scenario a name. You need make no changes to the Changing cells: box as the cells we nominated in your Default scenario show here.
3. Click OK. This will show the Scenario values dialog box and this time you will need to change the values.
4. Click OK.

You will now have two Scenarios available you.

Displaying Scenarios

Now you can display your Scenarios to show how they change the outcome of your data by asking the Scenario Manager to show a particular scenario.

1. Select Tools>Scenarios
2. Click on the Scenario name you want to see
3. Click Show.
4. Drag the dialog box out of the way and have a look at the values in the worksheet. One you have done this, click on the next Scenario name and then Show, and again peruse the values in the worksheet.
5. Click on Close when you no longer wish to view your Scenarios.
Summary Reports

Excel will allow you to create a Scenario Summary Report or a Scenario PivotTable Report of your scenarios with relative ease. The Summary Report is always created on a new Worksheet within the Workbook and will list all of the input cells and their values. To do this:

1. Select Tools>Scenarios
2. Click on Summary to display the Scenario Summary dialog box
3. Ensure that Scenario summary is selected under Report type
4. Click OK

You will be presented with a Scenario Summary on a separate Worksheet within your workbook. Excel may or may not display the Result cells for you, depending on which version of Excel you are using.

Creating a Scenario PivotTable report works in the same way, except you MUST nominate your result cells for this type of report. As a tip, the quickest and easiest way to do this is:

1. Go to Tools>Scenarios>Edit
2. Highlight any Scenario
3. Select the cells under Changing Cells
4. Select Ctrl + C
5. Select Cancel, then Summary, then select Scenario PivotTable Report
6. Click in Result Cells, then select Ctrl + V to paste in the values of the result cells.
7. Select OK

Your Summary PivotTable Report will be presented to you on a separate Worksheet within the workbook.

Merging Scenarios

If you wanted to, you could bring together similar scenarios that you have set up in different Workbooks into your current Worksheet. This will only work however if the input cells are exactly the same as those nominated in your current Worksheet. Merging scenarios can be great for recycling and analysing a previous years set of figures with the current year, or you may have different areas within a company that can make use of the same scenarios. This can be done easily by following the following steps.

1. Activate your current or default workbook
2. Select Tools>Scenarios to display the Scenario Manager.
3. Click on the Merge button and the Merge Scenarios dialog box will appear. If you are using the current workbook, there are no changes to make in the first box.
4. Click in the second box Sheet: and highlight the name of the Scenario you wish to merge with.
5. Click OK, but note before you do this that you have a message at the bottom of this dialog box telling you how many scenarios are in the source sheet that you selected.

You will be returned to the Scenario Manager and you should see both scenarios added together.

TIP: As an added tip, you can easily create Names for your cells and have them appear in lieu of cell addresses. This will make your scenarios easier to read and understand and is a simple process.


Goal Seek
FOR SOURCE CLICK HERE
Goal Seek is used when you know what answer you want, but don't know the exact figure to input for that answer. For example, you're quite certain that 8 multiplied by something equals 56. You just not sure what that missing number is. Is it 8 multiplied by 6? Or Is it 8 multiplied by 7? Goal Seek will tell you the answer.

We'll test that example out right now. So start a new spreadsheet, and create one the same as in the image below:

Before you can use Goal Seek, Excel needs certain things from you. First it needs some sort of formula to work with. In the image above we have the simple formula =B1 * B2. We've put this in cell B3. But the answer is wrong for us. We had a Goal of 56 (8 times something). We want to know which number you have to multiply 8 by in order to get the answer 56. We tried 8 times 6, and that gave the answer of 48. So we have to try again.

Instead of us puzzling the answer out, we can let Goal Seek handle it. So do the following:

*
From the Excel menu bar, click on Tools
*
From the drop down menu, click on Goal Seek
*
A dialogue box pops up

The dialogue box needs a little explaining. "Set cell" is the answer you're looking for, this is the Goal. Set cell needs a formula or function to work with. Our formula is in cell B3, so if your "Set cell" text box does not say B3, click inside it and type B3.

"To Value" is the actual answer you're looking for. With "Set cell", you're just telling Excel where the formula is. With "To Value" you have to tell Excel what answer you're looking for. We wanted an answer of 56 for our formula. So click inside the "To Value" text box and type 56.

"By Changing Cell" is the missing bit. This is the part of the formula that needs to change in order to get the answer you want. In our formula we have an 8 and a 6. Clearly, the 6 is the number that has to go. So the cell that needs to change is B2. So go ahead and enter B2 in the "By Changing Cell" text box.

Enter the values in the boxes

Click OK when your dialogue box looks like the one above. Excel will then Set the cell B3 to the Value of 56, and change the figure in cell B2. You'll also get a dialogue box like the one below:

Click OK on the dialogue box.

The new value is in cell B2

So Goal Seek has given us the answer we wanted: it is 7 that when times by 8 equals 56.

Increase your profits with Goal Seek

To give you a more practical example of what Goal Seek does, consider this problem. You have a business that generates 25 thousand pounds worth of profit. You currently sell 1000 items at 25 pounds each. You want to increase your profit to 35 thousand pounds. Assume that you're still going to sell 1000 items. By how much does the price of each item have to increase by in order to generate the new profit total?

We'll work it out together using Goal Seek. And then you can have a try yourself with an exercise. First, here's a new spreadsheet for you to download:

Download the Goal Seek Spreadsheet

When you open the spreadsheet, you'll notice that the Current Sales Figures and the Future Sales Figures are exactly the same. The formula in Cells B4 and E4 are = B2 * B3 and = E2 * E3

We can use Goal Seek to solve our problem. What we want to know is, What should be the new Price Per Item in order to generate Profits of 35 thousand?

*
So, from the Excel menu bar, click on Tools
*
From the drop-down menu, click Goal Seek
*
The Goal Seek dialogue box appears

The Goal Seek dialogue box

This time, our formula is in cell E4. So we want to Set the cell to the cell that has our formula. So type E4 into the "Set cell" text box.

The "To Value" text box will hold our new Profits. The Goal we are aiming for is 35 thousand. So type in 35000 in the "To value" text box.

The cell we want to change is the Price Per item figure. So in the "By changing cell" text box, type in E3.

Click OK when you're done.

The dialogue box is telling you that Goal Seek has found a solution. Click OK. Your spreadsheet will already have changed. The new Future Sales Figures will be displayed.
Goal Seek has given us the answer of 35 pounds. So the cost of each item has to increase by 10 pounds if we want a profit of 35 thousand.

And now it's your turn. Try this exercise.
Exercise

You've had a board meeting. It has been decided that the Price Per Item will remain the same - 25 pounds. But you still want to generate Profits of 35 thousand. Use Goal seek to work out how many Items will now have to be sold in order to reach your target.