No denying that Excel is one of the best things happened to financial analysis world. Excel makes financial modeling and reports for decision making possible in all sorts of way. Excel is as able as you are capable to using it. Period!

We have seen how Excel data models can be used to some of the most tedious jobs for accountants and analysts alike. For example:

  1. FIFO inventory valuation using Excel data tables
  2. Sensitivity analysis using Excel data tables
  3. Break-even analysis using Excel goal seek
  4. ABC or Pareto Analysis using Excel
  5. Aging analysis

And number of other analysis techniques that can carried out using Excel. One of such analysis is scenario analysis. In simple words, scenario analysis determine how change in multiple variables will effect a certain financial property e.g. profit. Following is an example of scenario analysis model made in Excel that:

  1. updates every time new scenario is selected from the drop-down menu
  2. gives comparative variance analysis of currently selected scenario with others

dynamic scenario analysis

Download Fully Worked Excel files bundle
Fully worked file give access to all the techniques used to make this scenario analysis work + exclusive support on understanding the model via email.

Got a question? Contact details are here

Each variable of the financial model can be changed by changing values in the scenario table. This example is usually an initial step to make targeted reports for further analysis.

scenario table

Like it? Its easy to make. Notable ingredients used to cook this template are:

  1. Data validation drop down lists
  2. Conditional formatting based on formula
  3. Excel tables and utilizing their power of structured referencing system
  4. INDEX and MATCH functions
  5. Defined cell names using name manager or address box.
Download Fully Worked Excel files bundle
Fully worked file give access to all the techniques used to make this scenario analysis work + exclusive support on understanding the model via email.

Got a question? Contact details are here

So what type of analysis are you conducting using Excel? Does it involve charts or conditional formatting? Let us know in the comment section!

LEAVE A REPLY