This article shows how to create a simple Waterfall Chart in Excel. It will apply the bar or column chart template with “Stacked Column” properties, which will produce an aesthetically pleasing graph of financial performance; based on a specified key performance indicator (KPI) over a selected time period i.e. EBITDA.
How to create a dynamic VLOOKUP
Excel’s VLOOKUP function is undoubtedly one of the most popular Excel functions. It can readily interpret and report values in large data arrays; unlike a PivotTable, which needs to be manually updated to reflect source data changes. Often this Excel function finds it difficult to reflect changes to source data. A dynamic VLOOKUP that incorporates the COLUMN function will overcome this challenge of changing source data.
How to sum across optional column ranges using SUMPRODUCT
The Excel SUMPRODUCT function is one of the most powerful and flexible aggregation functions. This example demonstrates how SUMPRODUCT can discriminantly sum cells in either column H (“Invoice Amount”) or column I (“Amount Confirmed”), based on whether there is a corresponding amount in column I.
How to use Excel Data Validation to protect cells
Excel’s Data Validation is one of the more underutilised and understated features in Microsoft’s spreadsheet application.
Quite simply it is a great, VBA macro-free tool, in which to improve the internal controls and corporate governance surrounding the management of a financial model.
Excel INDEX MATCH for two dimension lookups
The VLOOKUP function is a highly useful Excel function in financial analysis and business planning. It is a valuable one-dimension lookup tool – along with HLOOKUP. However in some cases, a financial analyst will require a two-dimension lookup tool, in order to reference the exact value from a data table, which a vanilla VLOOKUP function will be unable to perform.
The combination of the INDEX function with two MATCH functions can solve this matter.
How to create a dynamic drop-down list in your financial model
A dynamic drop-down list is a great way to force spreadsheet users, to select a specific array of inputs for a cell in a financial model. This dynamic approach combines the Defined Name function, OFFSET and COUNTA functions to provide a drop-down list, which flexes whenever there are additions or deletions to the data array of inputs.