Analysis using Waterfall Charts in Microsoft Excel

When analysing two or more scenarios in a financial model, it is often important to distinguish which variables have caused the most significant changes to financial outputs. However, complex financial models can often conceal the finer movements within the model.

Using 'Waterfall Charts' an analyst can determine exactly how a variable's movement has impacted upon financial outputs regardless of the model's complexity and the quantity of variables that have changed between scenarios.

The 'Waterfall Chart' can be altered for use in complex financial models where multiple revenue & cost streams require separation and graphical illustration to demonstrate their relative impact on the overall financials.

FMDA's free Waterfall Chart Template can be downloaded here.

Dynamic, Dependent, Validated Lists in Microsoft Excel

FMDA's free 'validated lists' template spreadsheet demonstrates how to drive lists in a dynamic fashion. Not only that, but the template demonstrates how lists can be populated dependent upon input information. 

Validated lists are an absolute must when developing a spreadsheet intended for third-party use. This functionality blocks a user from entering incorrect information in an input form, removing the possibility of input-driven errors.

However, developers are often unaware as to just how flexible these lists can be - partly because it takes a bit of tinkering to get it right!

The first lesson to learn is how to create 'dynamic named ranges'. These ranges grow larger or smaller in their row-column dimensions dependent upon input information. These are demonstrated in the free template.

The second lesson to learn is slightly less intuitive; by using the 'sumproduct()' function in a rather odd fashion validated lists become a whole lot more flexible! Again, these techniques are demonstrated in our free template. You can download the free template here.

FMDA Consultancy provide training and consultancy services for Financial Modelling and Data Analysis. Contact us at fmdaconsultancy@gmail.com.

Spreadsheet Structure and Formatting with Cell Styles

Creating an efficient, transparent and consistent spreadsheet structure and format is crucial when delivering a professional spreadsheet. These attributes provide managers and clients with the reassurance that work is of the highest quality and ensures that users of these spreadsheets quickly understand, gain confidence in, and ultimately edit or add additional layers to the work with ease.

The easiest and most efficient way to achieve this in Microsoft Excel is to develop structures and formats using 'Cell Styles' before you begin developing your spreadsheet. But don't worry, if you have already created your spreadsheet, these structures and formats can be added retrospectively.

At FMDA we use a very similar structure and format with all of our Financial Models and Data Analysis spreadsheets. You can download a free example of this structure here.

FMDA Consultancy provide training and consultancy services for Financial Modelling and Data Analysis. Contact us at fmdaconsultancy@gmail.com.