How do I calculate a "likely" cost based on a range of estimates?

April 16, 2024 10:11 AM

For anyone with financial modeling experience I'm wondering how you handle a situation where you need to combine a range of possible cost values into a single number for further analysis. See inside for more details.

Let's say I have a range of production costs calculated using a bottom-up approach and I want to collapse this down to a single number.

Taking a straight average doesn't seem appropriate because the higher end of the cost curve is unlikely to come into play, but it's also unlikely that the lowest cost will be the true value either.

I'm wondering if there's an accepted simple way of handling these situations especially when a detailed analysis of available volume at each point on the cost curve is not feasible.

I'm leaning towards just using the median value, but curious if there's a better way to approach this?

posted by 12%juicepulp to Work & Money (7 answers total)

Can you make, for each outcome, an estimate of its probability? You're already some of the way there, as you say the highest and lowest outcomes have low probability.

So multiply each outcome by it's probability percentage.

This gives you a weighted average, which is a good basic measure.

Example:
- cost £20m, p = 10%
- cost £5m, p = 80%,
- cost £1m, p = 10%
Weighted average = 0.1*20 + 0.8*5 + 0.1*1 = £6.1m

This is a better measure than the median (£5m). In this scenario, the worst-case scenario is very expensive, so it's important to factor that in.
posted by Klipspringer at 10:21 AM on April 16 [2 favorites]

It seems like what we’re missing is the purpose. What are you going to do with this number?
posted by lostburner at 12:29 PM on April 16 [1 favorite]

Conceptually it sounds like you are describing the expected value.... which is the average.
posted by oceano at 1:13 PM on April 16

Response by poster: Maybe a concrete example would help.

Let's say I'm trying to do a bottom up estimate of the cost of producing something that uses metal X. I also have a distribution of the cost to mine metal X in different countries based on how deep the metal X mineral deposits are in each location. But I don't have data on the total available amount of metal X in each country.

It seems reasonable to assume that a metal X mine will not be built in countries where the deposits are very deep and expensive to access. On the other hand it also seems unreasonable to just assume that metal X will be available at the lowest possible production cost.

My question is how best to choose a reasonable value for the cost of metal X?

It seems like moving forward with a range of possibilities is probably the best answer given the limited data available, but I was wondering if there's any kind of "best practice" for handling a situation like this.
posted by 12%juicepulp at 1:48 PM on April 16

Look at PERT estimates which is the fancy term for what Klipspringer mentioned above. This is widely used in software development estimation.
posted by crocomancer at 2:35 PM on April 16

If this is anything like the mining example you've given above, then I don't think there is a formulaic answer to this question, i.e. there is no formula you can plug your numbers in to and get a useful answer out. The question in your example is best answered by using domain-specific knowledge about where mines are likely to be built or not, how that will change market conditions and thus what prices will look like in your hypothetical. Just applying some averaging function won't help, because the distribution of costs doesn't really have much to do with what the actual costs would be.
posted by ssg at 5:18 PM on April 16

I was wondering if there's any kind of "best practice" for handling a situation like this.

I would say the answer to this sort of quantitative business decision-making-under-uncertainty question is a Monte Carlo simulation.

Crudely: You have a set of inputs that combine somehow to calculate an output. Each of your inputs is represented by a probability distribution. You draw one random value from each, combine them however your model defines and simulate one possible iteration of your final outcome. Repeat this thousands of times to get the range of likeliest outcomes. Here is a better explanation, albeit from someone trying to sell you on their product.

Most basic option for each input is a Triangular or PERT distribution, where you ballpark a minimum, maximum and most likely value. (This is a slightly more involved version of the already mentioned three-point estimation method).

If you have actual data to work with for some inputs, you can fit something more specific. Avoid doing anything you don't understand. Treat the result as a useful back of the envelope calculation, only as accurate as your initial inputs and modelling assumptions.

You can do this for free in any programming language, if you already know how to program. You can do it in Excel alone, but it's probably not worth the effort.

A lot of corporate analysts use Excel plugins. Licenses for these can cost thousands of dollars, because when you are committing several hundred thousands of dollars or more to a project, getting better results is extremely valuable.

You can see some examples laid out here. You won't be able to run them without that specific plugin (which has a free two week trial available), but the explanations should make sense on their own. At-Risk by Palisade also has a free trial.
posted by rollick at 2:05 PM on April 17