Hi! I want to know if it is possible to build a dynamic, interactive dashboard that can:
1. Take in user input, then
2. Make Changes to the underlying data (financial model), and finally
3. Show the updated charts / analysis.
The problem I am trying to solve right now is creating a way to analyze certain outputs (interest carry, construction cost, etc.) in a financial model (projection of future cash flows) that are reliant on certain inputs (number of units started per month, average cost per unit, construction months per unit, etc).
I don't think it should be TOO difficult for me to figure out how to write a macro / several macros to pull the right data from the model into another sheet based on these user inputs, create (a) table(s), then create the pivot charts, but I want to know if this is possible, and how I should go about this.
Can VBA see changes to an interactive dashboard in this way and make changes to the underlying model (which I can then copy paste into a new/updated table that feeds the dashboard) ?
If anyone has another suggestion on how I should go about this, it would be incredibly appreciated!
I am new, like I said, so I hope this doesn't come off like an amateur asking the professional how to go pro in under 24 hours, but I think I know enough to figure most out on my own, if anyone's able to point me in the right direction!!!!
Thanks!
Hi Tom!
I believe what you are asking is answered by tying your VBA in a worksheet change event. This post should help. Hope that's what your looking for! 🙂
Hi Tom,
I think VBA is probably overkill for what you want to do. I would have thought this is easily done with formulas. e.g. user chooses inputs which are referenced in formulas that build the financial model. The data referenced by the dashboard is always the same range, it's just the underlying figures that change depending on the inputs selected. Once the user makes their selections, they refresh the dashboard and the PivotTables pick up the updated data in the financial model values.
You may want/need to use Power Query gather the various parts of the financial model into a table/s for the dashboard, but again, this could be updated with a single click of the refresh button. Another option is to have Power Query filter the data based on user inputs, which I cover in my Power Query course.
Hope that points you in the right direction.
Mynda
