Paul Newton

Director of Business Analytics

Business Analytics Micro-Case #3: Better Decisions with Monte Carlo Simulation

Background

A recent Reuters article discussed how US Retailers have purchased, and are subsequently storing, goods that would be subject to an anticipated 25% tariff on Chinese imports. This in turn has driven up transportation and storage costs which is “adding pressure to quarterly results for retailers”.

In our 3rd Analytics Micro-Case, we put ourselves in the shoes of the retailers and ask if we can expect to make sufficient incremental margins to justify accelerating our (pre-tariff) purchases, given four principal unknowns:

  1. Tariff Amount: Although expected to be 25%, it is possible a deal may be announced and no tariff is implemented, or it could end up a different amount altogether.
  2. Expected Retail Price Increases: We know that the tariff of 25% would be applied to our purchase price (i.e. our Cost-Of-Goods-Sold), but we also know that i) different retailers have different COGS to which the tariff will be applied and ii) some of these retailers may be willing to absorb some of the tariff rather than pass it on to customers and iii) some manufacturers may be willing to absorb some of the tariff in order to maintain market share against local (US) manufacturers thereby dampening some of the price increase.
  3. Increased Storage Costs: If we suspected we would not be the only retailer to increase their inventory levels, we should also expect storage costs to increase by some indeterminate amount.
  4. Duration of Storage: The 25% tariff was originally expected to effective Jan 1, 2019, but was subsequently pushed back to March 2, 2019. Thus, it is not clear how long the goods need to be in storage before the tariff-free advantage can be realized.

For simplicity, we ignore shipping cost increases, cost of capital and other items that might be included in a more comprehensive model.

Even with just 4 unknowns, this is a fairly difficult problem or which to derive a quantitative answer. In this Micro-Case we contrast a traditional approach (where our decision model is based on average estimates), with a Monte Carlo (MC) simulation approach (where we simulate all potential outcomes based on their respective probabilities). We thereby show how MC simulation safeguards against unwarranted optimism in decision making.

Baselining

To model this, assume our pre-tariff price is $100, with a corresponding 40% COGS (which would be subject to the tariff). Assume also, that storage costs add another $1/unit/month in costs in the baseline model.

Therefore, our pre-tariff margin can be seen to be $59.

This basic margin formula is deployed in all subsequent calculations, however the relevant Price, COGS and Storage is adjusted according to our model specifications.

Now, assume that we have duly conducted our research and concluded that we expect:

  1. The tariff is most likely to be implemented at 25%
  2. Retail prices will increase by 10% on average.
  3. Storage costs to increase by 20%.
  4. Stockpiling averages 4 (total) months of inventory carry.

As such, our expected post-tariff margin without any inventory build-up is expected to be $59:

In our simplified example, the expected price increase exactly offsets the impact of the tariff and margins are unchanged. Our decision on whether to build up inventories depends on whether we believe we will earn higher margins than our $59 baseline margin.

Traditional (Single-Point Estimate) Approach

To calculate our expected return using the Traditional Approach, we use the average expected increases in Price and in Storage costs, plus the expected four months storage costs. (The tariff can be ignored as we are importing goods before the tariff takes effect.) Which can be calculated as:

It seems that with our average calculations approach, we expect to make a $65.20 margin on each unit compared to our baseline, which is $6.20 more than our expected margin without the stockpiling effort. This sounds impressive, it is a healthy 6.2% higher margin than could be expected without the stockpiling effort.

However, this approach does not recognize the risk or variability inherent with each variable, and so produces a relatively uninformed result.

Monte Carlo Simulation Approach

Monte Carlo simulations are a broad class of computational algorithms that rely on repeated random sampling to obtain numerical results (Wikipedia). Specifically, our MC model randomly selects a value from each of our 4 unknown variables consistent with the assigned probability of that variable, and calculates the corresponding improved margin for that combination of inputs. We do this 100,000 times to generate a probability distribution of our improved margin.

Input Distributions

We augment the research conclusions for each of our 4 unknowns as above, with a probability distribution that maintains the average as stated. Defining a probability distribution requires a level of subject matter expertise to be applied to the distribution, which we assume has been applied below:

Tariff Distribution

Tariff Amount: We believe that the 25% tariff has an 80% chance of being implemented and a 20% of a deal being struck which keeps the existing tariff value of 0% in place. To represent this numerically, we draw samples (with replacement) from the set {0%, 25%} with a probability of drawing each as {20%, 80%}.

 

Price Increases

Expected Price Increases: A 25% tariff only applies to our COGS, which is 40% of our Price. Therefore, if we wanted to fully pass the tariff on to our customers, our Price would need to increase by 10% (.4 * .25). We assumed a normal distribution with a 10% mean increase, and 4% standard deviation. Thus, we are 95% confident our realized price increase will approximately fall between 2%-18% (10% +- 1.96 * 4%).

Storage Costs

Increased Storage Costs: Here we assume a normal distribution with a 20% mean and 5% standard deviation (so a 95% CI of 10%-30% in storage increases).

Duration of Storage

Duration of Storage: The duration (in months) that the tariff-free purchases needs to be held. Here we use a Poisson distribution as we want duration bounded by zero and a small probability of a large duration. We use a mean of 4 months.

Calculating Likely Margin

With the assumptions and probability distributions laid out, we randomly selected one each of the 4 input variables and then calculated the associated improved margin, 100,000 times. We now count the instances of each level of margin improvement.

Based on the approach, we can expect a per unit, mean incremental margin of $3.42 with a 95% confidence interval ranging from $-7.5 to $9.097. This is a fairly large plausible margin range within which our results are expected and it should be viewed as a fairly risky proposition.

The median, per unit incremental margin is $5.058.

This mean incremental margin of $3.42 is considerably less than the $6.20 increase expected using the single-point estimate approach and combined with the range of possible margin changes (~$16.60 vs $0) it a considerably riskier undertaking than previously understood. A large part of this is due to us considering the (20%) possibility of no tariff being implemented at all, but a model that did not consider the possibility of such an outcome is surely insufficient.

Safeguarding Against Unwarranted Optimism

Using average values to determine an expected output value does not consider the range of likely values and such decisions are therefore made without regard to the risk involved. By using MC simulation, we were not only forced to consider the range of possible input values, but our output variable was considerably more information-rich, which leads a more informed decision made.

Using average values to determine an outcome tends to be overly optimistic as the models rarely consider the disproportionately high impact that negative events have. In our case, the non-imposition of a tariff would have a negative impact on margin improvement vs baseline – ~20% of our simulations – , as would the combination of lower-than-average price and higher-than-average storage duration – ~18% of our simulations. Both of these subtleties are lost with single-point estimates but fortunately, with MC simulation we can guard against such unwarranted and inadvertent optimism.

More From Paul

More in Business Analytics