Unraveling Uncertainty: Excel Your Way through Business Modeling

In a dynamic business, uncertainty may be the only certainty.

Managers, advisors, and analysts face a daily challenge: making informed decisions in a world where variables change as quickly as market trends.

This is where the power of statistical modeling comes into play, transforming uncertainty into a navigable roadmap for business strategy.

One tool stands out in its accessibility, flexibility, and power: Microsoft Excel.

The Role of Distributions in Business Analysis

Statistical distributions are more than just data analysis tools; they are the keys to unlocking the patterns and trends hidden within business data.

At their core, distributions help us understand how variables behave and how they are likely to behave in the future. This understanding is crucial in a business environment where data drives decisions, and uncertainty can be costly.

Every business decision is a gamble against uncertainty. Whether it's predicting sales, assessing risk, or allocating resources, business leaders constantly grapple with questions that have no definitive answers. Distributions provide a structured way to analyze this uncertainty, offering a glimpse into the probable outcomes of different business scenarios.

We can explore distributions through the lens of a fictitious company, Rob's Robots. This imaginary robotics firm faces a variety of challenges and opportunities, from launching new products to optimizing its supply chain.

Binomial Distributions

Binomial distributions are perhaps the simplest form of statistical distributions used in business analytics. They represent a series of scenarios with only two possible outcomes, often labeled as "success" and "failure," or "yes" and "no." Think of it like flipping a coin multiple times, where the outcomes are just heads or tails, but applied to business decision-making.

For Rob’s Robots, binomial distributions can model a multitude of either/or scenarios. From choosing whether to expand into a new market, to deciding on adopting a new technology, these distributions help in quantifying the probabilities of success or failure in such binary choices.

Excel, with its versatile formulae and functions, is perfectly equipped to handle binomial distribution models. Functions like BINOM.DIST can be used to calculate probabilities in binary scenarios, providing a clear mathematical basis for decision-making.

Binomial Distributions for Rob's Robots

Rob's Robots is facing a crucial decision: choosing which of its two shipping services to use for delivering its robots to a new retail store that will sell its newest robot product. This retail store is going to place about twenty orders per month to be shipped to its various locations.

If the shipments of product are on time, Rob’s Robots can charge $75 for on-time delivery, in addition to the wholesale price of the products. If the shipments are late, Rob’s Robots will have to refund (or credit) the $75 delivery charge. If more than three shipments are late per month, then the refund increases from $75 to $100 per instance, a progressive penalty.

The two shipping services, ABC Shipping and Sally’s Shipping, have historically performed fairly well with on-time delivery. ABC Shipping has delivered on time for 80% of orders and charges $50 per delivery. Sally’s Shipping has delivered on time for 90% of orders and charges $60 per delivery.

In this scenario, each order is either on-time or late, so this is an either/or scenario. Using a Binomial Distribution, how can Rob’s Robots build a model to help decide which shipping service to use?

Data Input: Managers at Rob's Robots input data for the refunds to the retailer and data on each supplier, including historical on-time percentage and cost per shipment.

Data Input for ABC Shipping

Applying the Binary Distribution Model: Using the BINOM.DIST function, the company calculates the probability of success of on-time delivery for each possible scenario with each supplier (one on-time delivery, two on-time deliveries, etc., all the way up to the twenty expected deliveries per month).

Applying the BINOM.DIST Function

• Number_s: This refers to the number of successes, or the number of times the desired outcome occurs. In our model for ABC Shipping, each row is used to calculate the probability that ABC Shipping delivers that number of orders on time out of the average of 20 orders (1 on time, 2 on time, 3 on time, etc.).

• Trials: This is the number of independent trials or events. In this context, ABC Shipping is making this number of deliveries per month (20), each with the opportunity of being on-time or late, independent of other orders.

• Probability_s: This is the estimated probability of success. Historical data suggests that ABC Shipping has an 80% chance of on-time delivery, so the probability would be 0.80.

• Cumulative: This is a logical value where you choose FALSE to find the probability of exactly that row’s number of successes (or alternatively TRUE to find the probability of that number or fewer successes). To build this model, we choose FALSE to assess the probability of exactly that row’s number of successful deliveries.

• Then Rob’s Robot’s uses the probability of the those on-time deliveries to calculate the expected total of refunds per scenario (row) and adds up the scenarios to get an aggregate total expected refunds per month. The total of expected refunds is added to the total shipping costs to get the total cost of that vendor relationship.

Binomial Distribution Model for ABC Shipping

Outcome Analysis: The model reveals that Sally’s Shipping, despite being more expensive per delivery, offers a lower total cost per month when factoring in refunds, making them the statistically favorable choice.

Comparison of ABC Shipping to Sally’s Shipping

Insights and Decision: The binomial distribution model in Excel allows Rob's Robots to quantify the risk and make an informed decision. The model's insights lead them to choose Sally’s Shipping, as the total expected cost of the relationship is lower. Also, it’s worth noting that the qualitative factor of fewer late deliveries may result in higher satisfaction for the new retailer, a consideration that also favors choosing Sally’s Shipping.

Download the Binomial Distribution Template

Normal Distributions

Normal distributions, often visualized as a bell curve, are fundamental in statistical analysis. This distribution is characterized by its symmetry around a mean value, with data points most densely clustered in the middle and tapering off towards the extremes. It's particularly useful in scenarios where data points tend to cluster around a central value, like average sales figures or typical customer spending.

For Rob’s Robots, normal distributions could be used for forecasting in business. They help in predicting future events based on past data, like sales, customer visits, or market trends, under the assumption that these events will follow a similar pattern as observed historically.

Excel's functions, such as NORM.DIST and NORM.INV, are powerful tools for modeling normal distributions. They allow businesses to analyze data and predict future trends based on historical patterns.

Normal Distributions for Rob's Robots

Rob's Robots has experienced plateaued sales for the past few years and is planning to change its sales strategy and bring in a new VP of sales. Given the company’s historical for the recent years, a normal distribution model is an ideal tool for modeling what would happen if they made no change to their sales approach.

Historical sales for Rob’s Robots have plateaued in recent years.

Data Input: Input historical sales data into Excel, calculating the mean and standard deviation. Calculate the mean and standard deviation for the past three years, the plateau period.

Applying the Normal Distribution Model: Utilize the NORM.DIST function to model the sales data, creating a distribution curve that represents the probability of various sales figures.

Applying the NORM.DIST function for Sales

• X: This is the specific sales figure you're interested in forecasting. For instance, if you want to forecast the probability of achieving $10,000,000 in sales, X would be 10,000,000. We are modeling multiple scenarios, so we choose the relative value for each row.

• Mean: This is the average (mean) sales figure from your historical data. The average monthly sales based on past three years' data was $10,854,544, so we refer to the cell with that calculation.

• Standard_dev: This is a measure of the variation or dispersion in your sales data. A higher standard deviation means more variability in sales figures. The standard deviation for Rob’s Robots’ historical sales was $282,127.

• Cumulative: This indicates whether you want the cumulative distribution function (TRUE) or the probability density function (FALSE). In this instance, we want to think about the range of possibilities, rather than a single exact sales amount. We use TRUE.

• From management’s perspective, it’s more intuitive to think about whether sales will be greater than a certain threshold, rather than less than a number, so in the cell formula, they decide to subtract the results of the NORM.DIST function from the number 1.

Forecast Analysis: The model predicts the probabilities that sales figures will exceed certain goal amounts if management makes no changes to its sales approach.

Forecasting the Probabilities of Exceeding Sales Goals

These exceedance thresholds can be visualized on a Sales Exceedance Curve. Management realizes that there is statistically less than a 5% chance of sales growing above $11.3, given the current conditions.

Sales Exceedance Curve

We can also use the NORM.INV() function in combination with a What-If Analysis Data Table to create a Monte Carlo simulation and create a normal distribution of simulated sales forecasts. The more scenarios we simulate, the smoother the normal distribution becomes.

Normal Distribution of Sales Forecasts

Gaining Insights for Strategic Planning: The forecast generated by the normal distribution model provides management of Rob's Robots with a probabilistic view of future sales if they make no changes. It helps management realize that there is less than a 5% chance of sales randomly exceeding $11.3 million given current conditions; therefore they determine that the new VP of sales should have a goal of exceeding $11.3 million through the implementation of a new sales plan.

Download the Normal Distribution Template

Integrating Models for Comprehensive Analysis

In the real business world, relying on a single statistical model to inform decisions may be limiting. Each distribution provides a unique perspective, but combining them can offer a more comprehensive understanding of business scenarios. This integration of different statistical models allows businesses to cross-validate insights and make more informed decisions.

By integrating different models, businesses can see how various aspects of their operations interact and impact each other. For example, how pricing strategies (lognormal distributions) might affect sales forecasts (normal distributions), or how supplier decisions (binary distributions) influence project timelines (beta distributions).

Excel's versatility in handling various statistical functions makes it an ideal platform for integrating different distribution models. Using Excel, analysts can create interconnected models that feed into each other, providing a dynamic and comprehensive view of business scenarios.

Conclusion

Statistical models, when properly implemented, can transform raw data into actionable insights. The cases of Rob's Robots demonstrate how understanding and applying different distributions can guide businesses through uncertainty, enabling more informed and strategic decisions.

While understanding the theory behind statistical distributions is important, the real value lies in their application. The scenarios encountered by Rob's Robots illustrate how theoretical models can be applied to real-world business challenges, providing insights that are both relevant and practical.

As the business landscape continues to evolve, the importance of data analysis and statistical modeling will only grow. Uncertainty doesn't have to be a roadblock. With the right tools and knowledge, it can be a path to new opportunities and greater success.