Build a mathematical model

Business Benefits

Evaluate the options, potential risks, and impacts, to use for complex decision-making.


Account for time sensitivity and complexity before you start building your model.

The lever that controls your model complexity is called the assumptions of your model. For example, if you have a very time-sensitive decision and modeling is appropriate, your model will contain stronger assumptions than if you had more time. This is because you do not have time to gather more known information, which leads to more unknowns, nor time to enhance accuracy and robustness in unknown information.

Time sensitivity and the maximum complexity of a model have a positive relationship. For example, if you have more time to sit down and build something, your maximum level of complexity also increases. However, a complex model does not always mean a reliable one. The chart below illustrates how reliability changes with time sensitivity and complexity:

There are three takeaways from this illustration:

  1. There is a point when you reach optimal complexity of your model and should not go further (see R2 in the chart above).
  2. Reliability is inversely proportional to the strength of assumptions. The stronger the assumptions, the less reliable the model is, for example, the more vulnerable to error it is.
  3. A robustness/sensitivity test determines where you are on the curve. A simple way to do a robustness proxy is to change your assumptions by a small and large percentage and compare them to the percentage change in your KPIs.

Determine objectives and KPIs for your project.

The objective is the defined goal of your situation. Your KPIs measure the performance of the defined goal and depend on your objective. Ask the following questions to help determine your KPI:

  • Is your KPI net present value of the project?
  • Is it the opportunity cost of investing?
  • Is it the return on investment?

For example, to determine whether your $1 million should be invested in the plot of a gold mining project, the next step is to determine your KPI. In this case, you choose profit for your KPI. Next, you set your investing threshold, your minimum profit for investing after three years is $2 million.

Create a spreadsheet to organize known and unknown information, to build the foundation of your model.

Models are built on known and unknown information. The foundation of the model is the aggregate of the known information. First, break down your KPI into parts, followed by a separation of those parts into information that:

  • You already know.
  • You will gather through research.
  • Will remain unknown.

Continuing the previous example, where the KPI is profit, the partitioning of this metric can be written as: Expected profit = Expected revenue – expected costs. It can be further broken down to: Expected profit = (Expected average price of gold x mined quantity of gold) – (machinery + labor + legal costs). With intense research, you are able to gather all costs, leaving expected average price of gold and mined quantity of gold, as unknowns.

To organize and structure your information in a spreadsheet, you can do the following:

  • Organize known information separately from unknown information in a table.
  • Check that the known information is relevant to calculating the KPI.
  • Add details about dates, country, and metric name.
  • Create a Notes column with an external source link or explanation.

Calculate informed estimates of your unknown components.

Fill out a separate table with the remaining unknown components. Each unknown component likely needs to be broken down into subsections to fill in informed parts and estimates. For each subsection, first ask, How do I calculate the unknown? Then ask, What proxies can I obtain? Lastly, make informed assumptions to fill in the missing information.

In our gold mine example, the primary need is to calculate, as best as possible, the expected average price of gold and the expected mined quantity of gold.

Link the objective, the known, and unknown information, using cell referencing in a spreadsheet to build your model.

Hardcoded information should be colored in black text, while formulas should be in blue text.

In our example, for simplicity, assume that your expected quantity of mined gold is 2,000 ounces, which you have referenced in cell F20 of your spreadsheet. Your KPI, three-year profit, can be written as the following, by using the known and unknown information mentioned in the previous sections:

= (Expected average price of gold x mined quantity of gold) – (machinery + labour + legal costs).

= (F14*F20) – sum(B3:B5)

= ($1,997*2,000) – $850,000

= $3,144,000

```The $3.14 million represents the total expected profit after three years, which is $1.14 million beyond the $2 million investment threshold set in Step 1.


## Determine your model inputs and incorporate varying estimates into your model.

Model inputs are the critical variables for your KPI. They often take on different values that affect the KPI. For this example, you have determined that quantity mined and 2023 gold price growth, are the key drivers because they are the most volatile. Here’s how to incorporate varying estimates into your model:

- List the model inputs you may want to change.
- Move this model input list through links from the existing ***Data*** tab, with known and unknown information, to a new tab. For example, first use Crtl+X to cut the cells, then Ctrl+V to paste the cells in your new tab. Here’s what it would look like for one of the model inputs, Quantity mined:

![Quantity minded table. ](https://cxl.com/wp-content/uploads/2020/10/image12-1024x514.png)

![Input tab excel. ](https://cxl.com/wp-content/uploads/2020/10/image6.png)

Set values for inputs, with either a hardcoded input or a dropdown. Dropdowns can be created by going to ***Data > Data Validation > List from a range or List of items.***

![set values for inputs. ](https://cxl.com/wp-content/uploads/2020/10/image4-1024x587.jpg)

![Data validation. ](https://cxl.com/wp-content/uploads/2020/10/image1-1024x530.jpg)

Shade model inputs cells yellow to indicate that changes are a part of the model.

![Quantity mined. ](https://cxl.com/wp-content/uploads/2020/10/image13.jpg)

Estimate uncertainty by creating scenarios; hypothetical situations based on varying assumptions about the future. Three default scenarios for key drivers influenced by luck or unknown externalities are:

1. Optimistic, a positive outlook on the future.
2. Neutral, the expected outlook.
3. Pessimistic, a risk-averse or conservative outlook.

Use ***Data Validation*** and ***Vlookups*** to transform static cells into dynamic options or model inputs based on those three scenarios.

![Optimistic, neutral, pessimsitc. ](https://cxl.com/wp-content/uploads/2020/10/image5.png)

![](https://cxl.com/wp-content/uploads/2020/10/image14-1024x474.jpg)

![Optimistic, neutral, pessimistic, excel sheet. ](https://cxl.com/wp-content/uploads/2020/10/image7.jpg)

![Quantity of Gold Mined (oz)](https://cxl.com/wp-content/uploads/2020/10/image9-1-1024x320.png)

Last edited by @hesh_fekry 2023-11-14T15:10:51Z