Dynamic Scenario Driven Financial Model
Dynamic Financial Model
Laying down a financial model is no less then carving a piece of art from a raw unstructured pile of data. The artist are you my dear friend and you are expected to have the skills to extract out the true diamonds from the haystack, present it in a structured and a meaningful manner. A simple and crude financial model will always be highly static with a high degree of manual hardcoding requirements whenever an edit needs to be made. The mark of a high end and a well laid model is in the fact that it is pretty dynamic in nature with minimal manual data hard coding. Of course, you will need to hard plug your historical financials and assumptions in your model but once these two areas are covered, there should be limited areas in the model with hard coded data and everything should be fetched by excel formulas. I understand that this is easier said than done, but definitely there's way to do it.
A good model has the provision to create multiple scenarios. For example, your model can have a a Base Case, Management Case and Aggressive Case, each case giving different set of assumptions with the growth rates, margins etc. varying amongst them.
Offset & Combo Box
A great technique to create a dynamic, scenario based model with a sturdy dashboard is through the use of Combo Box, a macro element and an offset function. Consider the below table which shows 3 cases where sales, a certain delay in the start of the operations, potential EBITDA Margins along with EBIT Margin and PAT margins
We can create a dashboard where the user has the flexibility to choose a case and the set of
assumptions only for the selected case would flow into the model as shown below
The above arrangement is created through a combo box which can be seen as the drop down in the above image. So on selection of a given case all the parameters of a given case feeds in the dashboard creating a dynamic environment. The combo box can be easily fetched from "Developer" Tab in your excel ribbon. You would need to get follow the path inside the Developer Tab i.e. Developer>>Insert>>Form Control>>Combo Box. Select the Combo Box and carefully place it on the cell where you want the drop down. Stretch the combo box horizontally for proper sizing of the box. Once placed, right click on the combo box and go to Format Control and reach out for the Control tab inside. In the Input range, select the cells where you have put the names of the 3 cases; in the Cell Link select the cell on the left of your combo box lets say its the cell "A4". Keep 8 in your drop down lines as shown below
Assuming that I have named the tab containing the cases as "Case" and the tab containing the dashboard as "Dashboard" the offset function can be written as
OFFSET(Case!C56,Dashboard!$A$4,,) The reference cell here would be the blank cell above the cell having the word "Base Case" in the tab: "Case". The row input would be the cell A4 which contains the serial number of the case that has been selected through the combo box in the tab: "Dashboard". The column input for the offset function can be kept blank as the cases lie on the column as that of the reference cell. The offset function would work as follows>> The row input to the offset function corresponds exactly to location of the metrics of the respective case. For Ex: If Base case is selected in the dashboard it will reflect 1 in A4 which in turn reflects the exact position of the word Base Case from the reference cell (Base case lies 1 row below the reference cell C56, Most likely case lies 2 rows below C56, Optimistic case lies 3 rows below C56). The offset formula created above can be dragged to the right by using the paste special pasting only the formula (Shortcut: Alt +ESF). This functionality helps you achieve a dynamic dashboard. This dashboard can serve as the funnel to inject case based assumptions into your model with the entire model feeding on the assumptions for the case selected in the dashboard. To learn more about valuations, financial modeling, pitch books and research report writing skill sets to answer the basic question of how to start investment banking career, checkout the Platinum Bundle offered by Wizenius, one of the best investment banking courses.
Contact Us (For Free Demo, discount offers etc.)