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.
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.
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”.
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.