Strategic Scenarios: Enhancing Prescriptive Analytics with Excel's What-If Analysis

Download the Excel template for this article.

In the dynamic terrain of business strategy, the ability to anticipate and prepare for a range of possible future scenarios is essential. Prescriptive analytics provide actionable insights to shape decisions and drive outcomes.

Central to prescriptive analytics is the toolset of "What-If" scenario analysis, an approach that leverages data to foresee and plan for various business conditions.

Excel offers a powerful suite of What-If Scenario Analysis tools that can transform uncertainties into well-defined outcomes, enabling data-driven professionals to deliver sophisticated business intelligence.

Excel’s What-If Analysis Tools

Excel's What-If Analysis tools provide a sophisticated yet user-friendly platform for simulating business scenarios and predicting their outcomes. By focusing on business factors instead of crafting formulas, these tools allow users to engage deeply with the strategic elements of business planning.

Advantages of What-If Analysis in Prescriptive Analytics

The prescriptive nature of What-If Analysis tools like Data Tables and Scenario Manager empowers people to move from reactive data interpretation to proactive strategic planning.

With these tools, we can:

• Simulate the effects of market changes on company performance.

• Explore the potential impact of strategic decisions before they are implemented.

• Create compelling visual narratives that bring data-driven stories to life for stakeholders.

• Drive decisions based on quantifiable data scenarios rather than gut feelings or assumptions.

The Role of What-If Analysis in Understanding Business Sensitivities

Understanding the sensitivity of financial outcomes to variable changes is crucial for any business operation. We can simulate such a business operation with an imaginary company, Rob's Robots, where fixed costs are high, and profitability is sensitive to sales volume and cost fluctuations.

Using What-If Analysis, decision makers at Rob’s Robots can quickly discern which variables have the most significant leverage on financial success and focus business strategy efforts.

Modeling Growth and Discount Rate Scenarios with Data Tables

Data Tables allow us to input a series of potential values and see how those changes affect business outcomes. This is particularly useful for analyzing the robustness of Rob's Robots' financial future under various conditions.

Impacts of Growth Rates and Discount Rates for Rob’s Robots

Imaginary company Rob’s Robots is going to raise capital based on their projected valuation. The founders estimate that they can sell 500 robots in their first year and grow those sales aggressively the first few years as they break into the market. Then they believe growth will slow down as they approach a growth rate of approximately 1% each year thereafter.

The founders build a model assuming that they will grow the number of units sold by 20% for the first five years. They use a sales price of $1,500 per robot, variable costs of $900 per robot, and fixed costs of $500,000 per robot.

Model Assumptions

That model provides them negative cash flows for the first three years before achieving positive net cash flows. They believe investors are looking for about 10% return on capital, and they determine to use a long-term risk-free rate of 4%. With these assumptions, the net present value (NPV) of future cash flows is just over $1 million.

Net Present Value of Cash Flows

Setting Up a Data Table for Rob's Robots

The founders know that different investors will use different discount rates. Also, the founders believe that they may be able to grow their unit sales volume by an even larger rate early on if they can create a product that has a broad enough customer base.

They decide to prepare a What-If Data Table that reflects the potential future outcome scenarios for Rob's Robots with two variables: the growth rate of units sold and the discount rate.

The founders want to explore a range of values for the growth rate of units sold that includes some lower growth-rate scenarios and higher growth rate scenarios than their original assumption of 20% growth per year. They define range from 5% to 55%, increasing by increments of 5%.

In their modeling worksheet, they put a reference for the NPV further down the sheet where they have some room to work. Then they create a row of the values from 5% to 55% by increments of 5% and a column of various discount rates from 5% to 20%, moving by increments of 1%.

Preparing for a What-If Data Table

They select the range that includes their Growth Rate rows and Discount Rate columns and insert a What-If Data Table (Data > What-If Analysis > Data Table). Rather than spending time to manually set up a complex table of formulas, this approach allows them to focus on the business impacts of the Growth Rate and Discount Rate.

Inserting a What-If Data Table

Analyzing the Outcomes

Once the Data Table is populated with the NPVs corresponding to each scenario, they can analyze the outcomes:

• Sensitivity to Growth Rate: By observing the changes in NPV as they vary the growth rate, the founders can identify how sensitive the company's valuation is to its ability to expand sales volume. This sensitivity analysis informs them about the potential return on investment in marketing or sales force expansion.

• Sensitivity to Discount Rate: The discount rate's impact on NPV informs them of the risk perception of future cash flows. A higher discount rate typically reflects a higher risk environment or opportunity cost of capital, which in turn reduces the present value of future earnings.

They also decide to visualize the impacts of rates on their predicted enterprise value using a column chart.

Impacts of Various Growth Rates at a 10% Discount Rate

Strategic Decision-Making

With this analysis, they can make strategic decisions such as:

• Investing in Growth: If the analysis shows that Rob's Robots' NPV is highly sensitive to the growth rate, they might consider investing in growth initiatives, such as R&D or market expansion.

• Cost of Capital Considerations: If the company's valuation is significantly affected by the discount rate, the founders may consider ways to optimize the company's capital structure to lower the cost of capital or hedge against interest rate risks.

Using the Scenario Manager for Exploring Price and Sales Volume

Having explored the versatile use of Data Tables for assessing the impact of growth and discount rates on Rob's Robots' financial forecasting, the founders now decide to use the Scenario Manager to explore their pricing strategy.

Excel’s Scenario Manager allows users to create and switch between different scenarios to evaluate their potential impact on the company's bottom line. In Rob's Robots' case, the founders focus on trade-offs between changing their sales price and the expected number of units sold.

Crafting Scenarios with Excel’s Scenario Manager

The founders of Rob’s Robots believe that raising the price will result in fewer sales and lowering the price will increase sales. Within Excel, they set up multiple scenarios that reflect different pricing strategies such as their original 'Base Case,' as well as a 'Low Price' and 'High Price' strategy.

Creating Scenarios in the Scenario Manager

After inputting the variables for each scenario, they can click ‘Show’ for each of the scenarios to review the impacts. They decide to view all the scenarios together so they click ‘Summary’ and select the NPV as the outcome that they would like to review, which automatically creates a Scenario Summary page in the workbook showing them the impacts of their pricing strategies.

Summary of Pricing Strategies

They realize that their low price and high price strategy both create outcomes with lower valuation than their current pricing approach, which leads them to believe they have an appropriate pricing strategy, based on their assumptions.

Seeing the impacts of pricing and unit volumes on the viability of their company, the founders determine to explore their breakeven volume of units sold.

Using Goal Seek for Breakeven Analysis

Breakeven analysis is a foundational tool in business strategy, determining when an investment or company will start to generate a profit. For Rob's Robots, understanding the sales volume and costs is pivotal.

Excel’s Goal Seek feature simplifies breakeven analysis. To perform a breakeven analysis with Goal Seek, the founders create a formula that calculates the total revenue (sales times the number of units sold) less total costs (variable costs times the number of units sold plus fixed costs). For the breakeven number of units, they enter zero as a placeholder. (They use Excel’s Name Manager so that they can write out readable names instead of cell references.)

Setting Up the Breakeven Analysis

Accessing Goal Seek via the 'Data' tab, the founders set the cell containing the Breakeven formula as the 'Set cell' and zero as the 'To value.' The 'By changing cell' is set to the ‘Breakeven Units’ cell, which has the placeholder value of zero.

Goal Seek Configurations

Upon running Goal Seek, Excel adjusts the breakeven value and finds the breakeven point.

Breakeven Results

The breakeven analysis offers several strategic insights:

• Breakeven Unit Volume: Understanding at what unit volume the company breaks even informs their sales goals to cover costs and target profitability as quickly as possible.

• Cost Control: By revealing the sales volume needed to cover current fixed and variable costs, Goal Seek prompts their brainstorming around reducing costs.

• Risk Assessment: Breakeven points highlight the risk associated with various business tactics, such as starting out focused on larger or smaller market segments and investing in advertisement campaigns.

Conclusion

By applying Data Tables, Scenario Manager, and Goal Seek, data-driven professionals can harness the power of prescriptive analytics to anticipate a spectrum of business outcomes for organizations.

The journey through Excel's What-If Analysis tools is more than an academic exercise; it equips data-driven professionals with tools for translating complex data into strategic action plans.

By employing these tools, people are better positioned to answer "what-if" questions with confidence, empower their teams with insights, and steer businesses through the uncertainties of market dynamics.