What-if in Excel and Business Central Data

What if you could combine the features in Business Central and Excel? Well that is already possible.
In Business Central you can export your data into Excel reporting and you can use the standard Excel features!
In this example I’m using the What-If-Analysis inside Excel combined with data from Business Central.

What is “What-if-analysis”

First what is this feature inside Excel? There are three tools around that (Scenarios, Goal Seek and Data Table). In this blog I will only cover the first two (Scenarios and Goal Seek).
With scenarios you can easily create separate scenarios to calculate an outcome.
With Goal seek you have set a goal and found out how to get there.

Microsoft resource:
Introduction to What-If Analysis – Microsoft Support

Example – Scenarios

For example you want to increase or decrease your Unit Price on an item to look what the forecast will be in next year. In Business Central you can easily export your item values (like description and unit price) together with your forecast. Then in Excel you can create your own report lay-out:

After you have finished your lay-out you can start using implementing the scenarios. Just go to Data -> What-if Analysis:

There you can set the value that you want to change and after that you can set the value:

If you then click on “Show” your values will be changed:

In this case you can easily look what the different scenario’s will be.
With the summary button you can easily create a summary for each scenario:

NB: the scenario’s will be saved in the lay-out so if you import it in Business Central every user can have the benefit working with your scenario’s!

Example – Goal seek

On the same example you can to have a turn over for about € 280.000 for next year. How much you have to increase your Unit Price? With “Goal Seek” that is very easily to do:

In this case you enter the value “280.000” by changing “Increase Unit Price” field.
If you hit OK it will calculate it for you.

And after a couple of seconds you know that you have to increase your price with 33%.