Achieving a best practice financial model is about bigger picture aspects. A number of elements come to mind; spreadsheet structure, error-free, flexible, consistent and so forth. However often implementing such a disciplined approach, with an existing financial model is not easy. There are always simpler approaches or tactics, which will achieve instant added value. The humble drop-down list is one tool, which can achieve this for the financial modelling professional.

The Drop-down list explained

A drop-down list is a fixed and pre-defined list of input parameters, which will force a user to select an input from a specified list of parameters. It is a great Excel function for a best practice financial model, because it improves the spreadsheet controls surrounding financial computations and the overall management of the financial model. A drop-down list restricts data inputs to the confines of a list of parameters. Clearly in the illustrated example, the model developer only wants users to change the graphs outputs based on the following financial variables:

• Selected Costing Type – Full Costing or Incremental Costing
• Selected Financial Item – Gross Profit, EBIT, NPAT or Operating Cash Flow
• Selected Sensitivity Item – Actuals or Sensitivity Analysis

Improving the controls of the best practice financial model

A comprehensive financial model has a myriad of inputting features. Actual financial numbers are sourced from accounting system files or hard-coded in assumption worksheets. Forecast or projected financial amounts are calculated from percentage growth figures or absolute numbers. Whereas qualitative inputs such as depreciation methods or place of operation (for corporate tax purposes), are required for the computation of depreciation and tax amounts respectively in the financial model.

A drop-down list is the perfect solution to restrict the inputting parameters in a best practice financial model, because it will prevent spreadsheet errors occurring from incorrect data inputs for items like depreciation or tax calculations.

Advanced applications

There are some advanced, yet simple functionality a best practice financial model will realise in an effortless fashion. Just as a drop-down list limits the use of an array of inputs, a dependent drop-down will force the user to choose from a subsequent, pre-arranged list of inputs. An example is selecting from a list of states or provinces, which is based on selecting a specific country in the following.

[youtube]http://www.youtube.com/watch?v=G1VdgMlPOWA[/youtube]

Evidently, a robust and error-free financial model will limit the computation of the following company’s Canadian business unit’s corporate tax rate to a choice of a Canadian province, and not a US or Australian state. A drop-down list can deliver such seamless sophistication.

Instead of creating multiple versions of a financial model, which is not only time-consuming and risky, a simple drop-down list will permit the creation of different versions of one model. The financial model may need to flex between an all-encompassing model for the finance executive, and a more simplified model for clerical or operational personnel i.e. data input purposes.

Perhaps you have stakeholders in various languages? A simple drop-down list will enable a seamless translation between French and English or any other language; provided the relevant financial model infrastructure has been created, in order to facilitate this value-adding functionality

Final word

A simple drop-down list restricts the use of inputs to a specific data array, and more importantly in best practice financial modelling it is an effective approach to increasing spreadsheet controls around data inputs. Additionally, the dependent drop-down list takes this one step further; it’s array of data is determined by the value selected from a precendent drop-down list, which provides a further layer of added value to a best practice financial model.