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

Send me an email at enqu[email protected] or call me on 0488039960 or better still complete the form below which will give me a better ideas as to what you are after.

Excel Expert John from the Excel Factor

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.