My Financial Modelling approach
Creative & Robust business solutions using Excel
A deep and broad understanding of Excel and spreadsheeting helps me find the right approach to a client’s project.
My approach to Financial Modelling and Excel consulting solutions is professional and always strives to find the right balance between simplicity, functionality, automation, longevity, performance, file size, user friendliness, error trapping, protection and NOT to lock myself into future work as the consultant.
It’s just common sense that the client will not be too happy if they have to phone me to resolve little problems too often because they can’t adjust and adapt the spreadsheet themselves as circumstances change over time.
The client also can’t have a result that takes too long to open or recalculate. If the client’s staff are not that Excel literate, more buttons to kick off macros, dropdown lists to ensure consistency of data and more use of protection will be required. Some spreadsheet solutions are quick and dirty because they are needed urgently and the client is Excel savvy so a simpler and quicker job is done with less energy put into the above issues.
If the Excel solution is needs to be more enduring, then the above issues have to be balanced as outlined below.
My Approach to Financial Modelling includes these aspects
Native functionality including Power Query over VBA or UDFs
More maintainable for the client. Power Query is more robust and more maintainable by more people.
Interactivity
Financial Models should be as interactive for the user as possible & make use of slicers and timelines as well user friendly drop downs list where ever possible
If VBA is used, code needs to be efficient, modularized, and re-used in function models where possible.
A future user cannot realistically adjust or maintain 1000s of lines of someone else's unique code easily.
Use Pivot Tables where ever possible OVER formulas to summarize data
Pivot Tables are easier, quicker and allow for user interactivity and instant charting with Pivot Charts
Tables and structured reference formulas where ever possible
A model based on Tables is more dynamic and structured formulas more readable and so less error prone.
Minimize non volatile formulas and only use as a last resort
Too many volatile formulas make your workbook slow and unstable and this can be hard to fix if left to the end.
Test calculation time and workbook size as you go
Its too late at the end of the project to fix bloated & slow spreadsheets
Automation
Not only does automation save time & reduce stress & leave more time for analysis, automation, once set up with whether it's using Power Query or VBA programing with adequate error trapping, it's more reliable & accurate.
Use a template approach where ever possible for reports, data entry areas, tables, graphs, pivot tables for easier maintenance
Its goes without saying it's easier to maintain and improve one thing rather than many.
Build in robustness with error trapping, unobtrusive checks and warnings.
Higher reliability and lower risk of user errors causing failure or and user corruption is the goal using check calculations and conditional formatting.
Formats, conditional formatting or styles are minimalist & prominent colors reserved for highlighting what's important
Too much formatting and the effect is lost as well as having an effect onerformance
Re-usability of data is a priority in 'database like' structure of facts and transaction tables
If data is organised with this in mind, you open up the workbook to use pivot tables and pivot charts and future yet to be specified requirements are easier.
Contact me
For initial enquiry please use my contact form or email [email protected] with examples or screenshots to give me a better idea on what you are after.
Build in robustness with error trapping, unobtrusive checks and warnings.
Higher reliability and lower risk of user errors causing failure or and user corruption is the goal using check calculations and conditional formatting.
Formats, conditional formatting or styles are minimalist & prominent colors reserved for highlighting what's important
Too much formatting and the effect is lost as well as having an effect onerformance
Re-usability of data is a priority in 'database like' structure of facts and transaction tables
If data is organised with this in mind, you open up the workbook to use pivot tables and pivot charts and future yet to be specified requirements are easier.
My Approach to
Professional Financial Modelling
includes these aspects ….
Native functionality including Power Query over VBA or UDFs
More maintainable for the client. Power Query is more robust and more maintainable by more people.
If VBA is used, code needs to be efficient, modularized, and re-used in function models where possible.
A future user cannot realistically adjust or maintain 1000s of lines of someone else's unique code easily.