What are the benefits and limitations of what-if analysis in Excel for corporate FP&A?

1 What is what-if analysis?

What-if analysis is a technique that involves changing one or more input values in a spreadsheet and observing how the output values change as a result. You can use what-if analysis to answer questions such as: What if sales increase by 10%? What if the interest rate changes by 0.5%? What if we add a new product line? By using what-if analysis, you can explore different possibilities and compare their outcomes.

2 What are the types of what-if analysis?

Excel offers three main types of what-if analysis: data tables, goal seek, and scenario manager. Data tables let you create a table of values that show how one or two variables affect a formula. Goal seek lets you find the value of an input that makes a formula equal to a specific goal. Scenario manager lets you create and save multiple sets of input values and switch between them easily.

3 How to use data tables in Excel?

Data tables are useful for creating a summary of how different values of one or two variables affect a formula. For example, you can use a data table to show how different sales growth rates affect your net income. To create a data table, you need to have a base formula in a cell and a range of values for the variable(s) in adjacent rows or columns. Then, you need to select the entire table, go to the Data tab, and click on What-If Analysis > Data Table. In the dialog box, you need to specify the cell reference for the row input or the column input, depending on whether you have a one-variable or a two-variable data table.

4 How to use goal seek in Excel?

Goal seek is useful for finding the value of an input that makes a formula equal to a specific goal. For example, you can use goal seek to find out how much sales you need to achieve a target profit margin. To use goal seek, you need to have a formula in a cell and a goal value for the formula. Then, you need to go to the Data tab and click on What-If Analysis > Goal Seek. In the dialog box, you need to specify the cell reference for the formula, the goal value, and the cell reference for the input that you want to change.

5 How to use scenario manager in Excel?

Scenario manager is useful for creating and saving multiple sets of input values and switching between them easily. For example, you can use scenario manager to compare different budget scenarios based on different assumptions. To use scenario manager, you need to have a worksheet with input cells and output cells. Then, you need to go to the Data tab and click on What-If Analysis > Scenario Manager. In the dialog box, you can add, edit, delete, and show scenarios. Each scenario has a name, a set of input values, and a comment. You can also create a summary report that shows the output values for each scenario in a separate worksheet.