Chris Webb does some work in Excel:
Sometimes, when you’re analysing data, you need to be able to change variables and see what the impact is: for example you might want to see what your profit margin looks like if tax rates are set at different levels. Power BI’s what-if parameter feature can do this but it has the limitation that you can’t enter any value you like – you need to create a table containing all possible variable values in advance. The advantage the Excel Data Model/Power Pivot has over Power BI for this type of what-if analysis is that you have the Excel worksheet available, which is not only a place to display your report but which can also be used as a data source for tables in the Excel Data Model, making it easy for users to enter whatever variable they want. Up until recently, though, I assumed that if you were importing data from the worksheet into the Excel Data Model you would need to take some form of manual action, for example clicking a refresh button, to load the new data from the worksheet into the Excel Data Model when the data there changed. In this blog post I’ll show you how you can use VBA to solve this problem and build an elegant what-if analysis solution in Excel where no extra clicks are needed.
Read on for that demonstration.