How do you avoid common pitfalls and errors when using what-if analysis in Excel for corporate FP&A?

1 Choose the right type of what-if analysis

There are three main types of what-if analysis in Excel: data tables, goal seek, and scenarios. Each one has its own advantages and limitations, depending on your situation and objectives. Data tables are useful for creating one- or two-variable sensitivity tables that show how changing one or two inputs affects one or more outputs. Goal seek is useful for finding the value of an input that makes a certain output reach a desired goal. Scenarios are useful for creating and comparing multiple sets of inputs and outputs that reflect different possible situations. You should choose the type of what-if analysis that best suits your needs and data structure.

2 Set up your data and formulas correctly

Before you perform any what-if analysis, you need to make sure that your data and formulas are accurate, consistent, and well-organized. You should use named ranges, tables, and cell references to make your formulas easier to read and maintain. You should also use absolute references, such as $A$1, to prevent your formulas from changing when you copy or move them. You should also check your data for errors, duplicates, outliers, and missing values, and use data validation and conditional formatting to ensure data quality and integrity.

3 Use separate worksheets and workbooks for what-if analysis

One of the best practices for what-if analysis in Excel is to use separate worksheets and workbooks for your original data and formulas, and your what-if analysis results. This way, you can protect your original data and formulas from being overwritten or corrupted by your what-if analysis. You can also easily switch between your original and what-if worksheets or workbooks, and compare them side by side. To create separate worksheets or workbooks for what-if analysis, you can use the copy and paste functions, or the save as and open functions in Excel.

5 Review and test your what-if analysis carefully

The final step for what-if analysis in Excel is to review and test your what-if analysis carefully and thoroughly. You should check your what-if analysis results for errors, inconsistencies, and anomalies, and use the audit and error checking tools in Excel to find and fix any problems. You should also test your what-if analysis results for reasonableness, reliability, and sensitivity, and use the what-if analysis tools in Excel to modify and refine your inputs, outputs, and scenarios. You should also compare your what-if analysis results with your original data and formulas, and with other sources of information and benchmarks.

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?