advanced excel – STL

Excel has some powerful tools that you can use to carry out a What-if analysis.

We sat down with one of our Excel Experts, Jens, to ask him about three particular data analysis tools and to see whether he would demonstrate how to use them for us.

Good morning Jens, you talk a lot about “what-if analysis”. To the beginner like me, could you explain what that means?

Excel offers three What-If Analysis tools that allow users to discover different scenarios and evaluate the effect of changes on their data. These tools help in analysing the possible outcomes based on different input variables values.

What if

Thank you, that’s clear enough. Would you mind starting by telling us what those three tools are?

The Scenario Manager

The first one, the Scenario Manager, allows users to create and save different sets of input values that can be applied to a worksheet. By defining multiple scenarios, users can quickly switch between different sets of data and observe the resultant changes in calculations, formulas, and charts.

Great, and thank you for the detail

Goal Seek

Next, Goal Seek is a modest sensitivity analysis tool in Excel. It allows users to determine the input value needed to succeed a desired result. The Goal Seek tool can work with changes in one variable. By specifying a target value, Goal Seek will find the input value that will produce the desired outcome based on a formula or calculation.

I can certainly see the value in that too!

Data Tables

The final tool, the Data Table, is also a sensitivity analysis tool in Excel enable users to perform multiple calculations simultaneously by changing one or two input variables. By creating a data table, users can explore different combinations of input values and observe the resulting calculations. Data Tables are specifically useful for analysing the effect of changing variables on complex models or large data sets.

So we can start to see a difference between the three tools from your explanation there. Particularly around when each one would be the most useful. Could I ask you to run through the three tools in a bit more detail, perhaps you could demonstrate how to use them in an example context?

What if Example 1 – The Scenario Manager

Of course. For the Scenario Manager, imagine that you have projected how many projects our company is going to run next year. Also, you have estimated the profit. But there are many uncertainties you want to consider. Your sales department has provided you with both good news and bad news. One of your VIP clients may be going to one of your competitors, but good prospects of getting some huge new clients can also affect your projections.

A planned marketing campaign end of this year can also have an impact on your projections.

What if

In this example the projections are 100 projects, with estimated average revenue for each project of £25,000 which results in £2,500,000 estimated total revenue – if you look at row 4 here on my spreadsheet.

20 employees are needed for the 100 projects with an average salary of £50,000 a total of £1,000,000 – this data is in row 7.

There is a fixed cost of £500,000 in row 10.

All projects are overseas, and it is not decided yet if the employees are going first class or economy class so both options are in the model. The profit is calculated to £510,000 if the employees are going first class and £750,000 economy class, as you can see from the £2.5m total revenue – the salary cost – fixed project costs – travel/ accommodation costs.

To build What if scenarios based on this model, you need to open the Scenario Manager.

Click Add and name the scenario. You will then need to tell the Scenario Manager which cells you would like to be able to change.

In this example you would like to change number of projects, average revenue, number of employees, average salary, ticket cost, hotel cost, and food cost.

The numbers in the model are the best guess scenario and it is very important that you keep the numbers you have in the model unchanged in a scenario otherwise you cannot get back to the scenario again later. When you click OK, you get the Scenario Values dialog box in which you change the changeable cells in your model.

What if

To create a new scenario just click Add:

Once you’re here, name the scenario click OK and enter the new values.

You can create any number of scenarios. In this example a Best Guess, a Best Case, and a Worst Case scenario have been built. To show the different scenarios select the scenario and click Show, and the model will show the values entered in your model.

What if

That’s great! Thank you for breaking it down as you have for us. It makes perfect sense. And thank you for demonstrating it in a way which aligns with how I would use this tool. Shall we move on to Goal Seek?

what if Example 2 – Goal Seek

Okay. So using the same scenario, you have decided to use the economy class ticket option because your target profit next year is £2m.

The Goal seek tool can help you to understand what you will require to reach this target.

First, how many projects are needed. If I open the Goal Seek tool. The Set cell is the cell reference of the cell with the formula calculation the profit. Here it is B20. Enter the target (£2,000,000) in the “To value box”. In the “By changing cell” box, you can enter the cell reference of the cell you want goal seek to change. Here it is A4 where the number of projected projects is entered.

Now I just click OK, and the model shows in this example, that 156 projects are needed to reach the target.

What if

Instead of looking at number of projects required, you could also find out how much the average revenue needs to increase to reach the target. You will just have to change the “By changing cell” reference.

And the model shows that an average revenue of £37,500 will get you to the target.

What if

Amazing how much you are able to affect the data with such small changes, I can really see the value of this one too! Can we look at the third and final tool?

What if Example 3 – Data Tables

That would be the Data Tables. These can work with either one or two variables. We will be using 2 variables in this example.

We want to see how changes in the number of projects and changes in the average revenue will affect the profit.

To create a Data Table, you will have to enter the values you want to investigate in the cell next to the formula and across (here it is number of projects), and in the cell just under the formula and down (here it is average revenue). Select the whole range and open the Data Tables tool.

The Row input cell here is A4 and Column input cell B4.

Click OK and the Data Table tool will now show the profit based on all the values entered.

What if

Conclusion

It sounds to me Jens, that these are tools which many people will use on a daily, weekly, monthly, quarterly and/ or annual basis depending on industry and scale. I can certainly see the benefit of them when we are looking at data and trying to think holistically about our impact on the business!

Yes, the What-if analysis tools are very useful. Especially in forecasting models. Every time you have a calculation or a model in Excel and you have thoughts or questions like:

What if we raise our prices by 5%?

And what if we were to employ more people?

What if the fuel prices increase?

Whatever question which should popup when you look at your Excel models, the What-if analysis tools can help you gaining clarity of how changes can affect your models.

If you’ve used Excel for some time, you may want to learn more about the different logical functions such as IF, AND and OR. This post describes how these functions can be combined to perform very useful composite logical operations.

IF Function

The IF function will carry out one of two actions.

One action is carried out if a specified test is true.
The other action is carried out if the specified test is false.

If the function is typed into a cell, the function starts with an equals symbol and then the letters IF, then an open bracket, followed by the three elements test, true action and false action separated by commas and followed by a close bracket.

The function looks something like =IF(TEST,TRUE,FALSE).

For example, suppose the TEST part is A1>100 where cell A1 contains a number of sales.

The TRUE part is “WELL DONE”, and the FALSE part is “Try Harder”. So the complete function looks like =IF(A1>100,”Well Done”,”Try Harder”).

The functions displays either “Well Done” or “Try Harder” in the cell, depending on whether the number in A1 is more than 100 or not. If you type the IF function into, for example, cell A2, you can then try different numbers in cell A1, above or below 100, and you’ll see either of the messages displayed in cell A2.

AND Function

The AND function will display either TRUE or FALSE in the cell containing the AND function.

The function starts with the usual equals sign and then AND followed by brackets which contain one or more tests separated by commas. If all the tests are true, then the cell shows the value TRUE. If any of the tests are not true, then the cell shows the value FALSE.

Suppose in our above example cell B1 contains number of customers. Suppose you type in this formula to cell B2 =AND(A1>100,B1>5) and press the Enter key and look at the result. Provided the number in cell A1 is more than 100, and the number in cell B1 is more than 5, then the function shows TRUE. Try different values in A1 and B1 to confirm that cell B2 only shows TRUE if both tests are true, and FALSE if either or both tests are not true.

OR Function

The OR function will also display either TRUE or FALSE in the cell containing the OR function.

The function starts with the equals and then OR followed by brackets which contain one or more tests separated by commas. This time if any one test is true, then the cell shows TRUE. If all tests are not true, then the cell shows FALSE.

So suppose you type this formula in cell C1 =OR(A1>100,B1>5) and press the Enter key. If you try different values in cells A1 and B1 you’ll find that cell C1 which contains the OF function shows TRUE if either or both cells contain numbers over the set values.

Combining IF with AND function

Suppose we modify our IF function. We now want to show “Well Done” only if sales in A1 are more than 100 and customers in B1 are more than 5. In other words our IF function will need to have two tests true to show “Well Done”.

We can do this by combining an IF with an AND function. So in cell D1 type in the formula =IF(AND(A1>100,B1>5),”Well Done”,”Try Harder”) and press the Enter key. You can see that the AND function is now the test for the IF function, and for the test to be true both numbers in cells A1 and B1 need to be more than the set amounts.

Combining IF with OR Function

Suppose we again modify our IF function. This time we want to show “Well Done” if sales in A1 are more than 100 or customers in B1 are more than 5. In other words our IF function will now need to have only one test true to show “Well Done”.

We can do this by combining an IF with an OR function. So in cell E1 type in the formula =IF(OR(A1>100,B1>5),”Well Done”,”Try Harder”) and press the Enter key. Now you can see that the OF function is the test for the IF function, and for the test to be true either the number in cell A1 or the number in cell B1 needs to be more than the set amount.

In conclusion the IF function is one of the most important logical functions in Excel but you can combine it with AND and OR functions in different ways to create even more powerful logical tests.

A really effective way to learn more about advanced Excel and it many very useful features is to attend a training course. Then you can really boost your skills in using Excel. https://www.stl-training.co.uk/excel-2010-advanced.php