A Precedent Drop-down list, like a dependent drop-down list, is a robust tool to incorporate in a best practice financial model, because it will restrict user inputs based on a previous drop-down list.

The following business planning spreadsheet requires users to select a product, from a standard drop-down list, but then select a precedent Business Unit.

However, contrary to a dependent drop-down list, the product drop-down list will alter the precedent drop-down list of input variables of Business Units – as opposed to the dependent list of inputs.

Strategize Financial Modelling – Precedent Drop-down list.xls

 

Business Modelling applications of a Precedent Drop-down list

A financial model might require a precedent drop-down list for a list of products, which the holding company sells across its universe of business units; however not all the business units will sell the same or all of these products.

A precedent drop-down list will restrict the financial model’s subsequent drop-down list, to simply the list of precedent business units that sell the specified product based on the input from the first drop-down list. The precedent drop-down list will prevent model users from erroneously selecting a business unit, even though this business unit does not sell the stipulated product .

 

 

The precedent drop-down list will prevent model users from erroneously selecting a business unit, even though this business unit does not sell the stipulated product.

 

Creating the Precedent Drop-down list

Unlike a dependent drop-down list, which merely requires defining each dependent list based on the drop-down list range, and applying the INDIRECT function in the Data Validation window. A precedent drop-down list requires a little more work.

Firstly, specify the products applicable for each business unit in the following.

 

Then rank the non-blank, products across the spectrum of possible inputs, because this will ensure the precedent drop-list itemises the business units alphabetically and flexes to show simply non-blank, business unit names.

Next, the use of VLOOKUP will list based on ascending order each business unit, and drive the correct working of the defined name’s formula for each product.

Subsequently, the defined name for each product must apply the below formula or alternative, which enables the seamless flexing or updating of the precedent drop-down list. 

 

Note the INDIRECT function entwined with the Data Validation function, which is the best approach for creating dependent drop-down lists, will not work for a precedent drop-down list because its requires a dynamic named range.

 

The functioning of the Precedent Drop-down list

As the above video demonstrated, the precedent drop-down list will flex seamlessly, whenever the source product list (by business unit) is updated or if model users change the precedent input product.

The precedent drop-down list is another great element of a best practice financial model; which is the establishment and operation of a financial model to “enables executives to safely rely on it as a corporate governance tool, and to better manage the company’s future financial direction”.

 

In Summary

Remember the INDIRECT function that is appropriate for a dependent drop-down list, will not function for a precedent drop-down list – thus the CHOOSE function is adopted instead.

The CHOOSE function will be determined by the MATCH output in cell O28; which will influence the ensuing precedent drop-down list of Business Units that is available to the model user.

As we can see, once we change the output of the Product drop-down list; it will alter the resultant precedent drop-down list of Business Units presented to the model user in a financial model.