Earlier this year, I asked members of the Financial Modelling Group for their best tips for reducing financial model file size and got some fantastic tips. I’ve edited my original list of personal favourites to include some of the best tips and tricks and credited the authors below:
Written by Danielle Stein Fairhurst and Lance Rubin
This article first appeared as part of the Financial Modelling Knowledge Hub
It’s never been more important to have a well built, flexible and robust financial model to use to predict outcomes and it’s at times like these a financial model really proves its worth. With a global pandemic unfolding and the situation changing daily, having a model that can you can quickly and easily update as the situation requires, is going to make it so much easier to make the quick decisions that are going to be necessary in the coming weeks or months.
One of the most frustrating things about using other peoples’ spreadsheets is that there is no obvious architecture or roadmap. How do you “drive” it and how does the logic get us from A to B?
(more…)
Automating repetitive tasks with Excel Macros can dramatically cut down the time you spend building financial models, and can significantly increase your productivity. A well-written (or recorded) macro enables you to repeat operations that you would normally do by hand but much more quickly and reliably. If you find yourself repeating the same action over and over again whilst building or editing your models then the use of VBA can increase speed and accuracy.
At our recent Melbourne Meetup, we heard from from Macro & VBA expert, Marcelo Mendonca from Rixena who specialises in Business Productivity Improvements, and has trained hundreds of professionals from engineering, management, finance, supply chain in Australia, India, South Korea and Brazil. Marcelo gave us a practical demonstration covering:
• Best practice for Financial Modelling with VBA
• When to use a formula and when to use a Macro
• Tricks for model-building such as finding broken links, or hard-coded numbers using VBA
• Case studies of when the use of macros saves time and increases reliability of financial models
Model design and structure can be one of the most difficult parts of building a financial model. Typically, modellers will work from back to front when building their model. The output, or the part they want the viewer or user to see, will be at the front, calculations will go in the middle, and source data and assumptions should go at the back. The image below shows an example of what your tabs in a well-structured model might look like.
The upgrade from Excel 2003 to Excel 2007 is probably one of the most significant changes for Excel users yet. There are many new functions, most of which are an improvement to 2003 but they do take some getting used to! As a financial modelling consultant and trainer, I’m often asked by my clients what are the advantages and disadvantage of upgrading to Excel 2007 and whether organisations should even bother.
