11 Scenario Modeling and Decision Support

11.1 Introduction to Scenario Modeling

Operations rarely happen in steady conditions. Customer demand can spike or fall, suppliers can delay shipments, and costs can shift with little warning. Scenario modeling gives managers a way to prepare by asking “what if” and then seeing how those answers play out.

At Pemi Coffee Roasters, leadership often wonders what will happen if wholesale orders grow faster than expected or if café sales soften. A simple model in Excel lets them adjust demand up or down and watch the effect on roasting schedules, staffing, and delivery. A 15 percent jump in wholesale demand may show the need for an extra roasting shift and added drivers. A 10 percent drop highlights unused capacity and rising unit costs. In both cases, the team can talk through trade-offs before the situation actually happens.

The goal is not to predict the future exactly but to understand how different paths would affect the business. Scenario modeling makes those conversations concrete. It shows risks and opportunities clearly, and it helps leaders explain why one course of action makes more sense than another.

11.2 Building Scenario Models

Once the concept of scenarios is introduced, the next step is to design models that can capture them. A scenario model is essentially a framework that allows managers to ask “what if?” and receive structured, data-based answers. At Pemi Coffee Roasters, this might involve using Excel to estimate how changes in wholesale demand affect both café operations and warehouse capacity. By building a flexible model, leaders can test not only their expectations but also unexpected shifts that may arise in the market.

The process usually begins with identifying the variables that matter most. For a coffee business, these could include the volume of beans purchased, café sales, labor hours, and delivery costs. Each variable is then linked through formulas so that a change in one input automatically adjusts the outputs. If bean prices rise by 15 percent, the model recalculates margins; if wholesale orders fall, the impact on inventory and staffing becomes clear. This structure allows managers to test different scenarios without having to rebuild their calculations each time.

Equally important is the choice of assumptions. Models are only as reliable as the estimates they are built on. Using historical sales patterns, industry benchmarks, or supplier quotes provides a stronger foundation than rough guesses. Students should practice challenging their assumptions by asking how sensitive their results are to small changes. A forecast that collapses when assumptions shift slightly is not robust enough for real-world use.

Scenario modeling is most effective when the outcomes are presented clearly. Instead of a long spreadsheet full of numbers, a simple chart or table that shows best-case, middle-ground, and worst-case results side by side makes the trade-offs obvious. Managers can quickly see how a decision might play out if demand grows, levels off, or declines, and they can discuss actions for each situation. In practice, scenario models serve as both a decision-making aid and a communication tool, helping leadership teams compare alternatives and align on strategies under uncertainty.

11.3 Building What-If Scenarios in Excel

Operations often involve decisions made with incomplete information. Managers may not know if costs will rise, whether customers will buy more or less than expected, or if supplies will arrive on time. What-if analysis in Excel gives them a way to test possible changes before those changes happen in real life.

Scenario Manager is one of the tools that makes this possible. It allows several sets of assumptions to be saved and compared side by side. At Pemi Coffee Roasters, one scenario might assume steady demand, another a sharp increase in sales, and a third a slower market. Switching between these versions shows how each situation affects production schedules, staffing, or inventory levels.

Data Tables are another option. They work well when you want to test one or two variables repeatedly. If Pemi Coffee is considering expanding roasting capacity, a Data Table could show profits at different combinations of sales volume and cost per pound. This type of grid makes it easier to see where growth is realistic and where expenses outweigh benefits.

Goal Seek takes the process in a different direction. Instead of asking “what happens if,” it answers “what input is needed to hit a goal.” If the target is a two-day order lead time, Goal Seek can identify the maximum number of orders the system can manage while still meeting that commitment. This provides a clear limit for managers.

Used together, these tools help operations teams think ahead without pretending they can predict the future. The value comes from seeing possible outcomes in advance and planning for how to handle them.

11.4 Scenario Planning with Power BI

Excel is often the first stop for scenario planning, but Power BI changes how those scenarios are shared and understood. Instead of scrolling through spreadsheets, managers can see outcomes directly on a dashboard. Filters let them compare regions, product lines, or time periods in real time, and the charts update as assumptions shift.

At Pemi Coffee Roasters, a Power BI dashboard could show profits under several sales forecasts. Leaders might look at one view where wholesale sales grow quickly, then switch to another where café traffic slows. Having those pictures side by side makes it easier to decide whether to hire more staff, delay a store opening, or adjust pricing.

Another benefit is accessibility. People who are not building the models themselves can still interact with them. A regional manager can click through the dashboard, ask “what if” questions, and see the answers without waiting for a new spreadsheet. This keeps scenario planning active and part of regular conversations rather than something that happens in the background.

11.5 Using R for Advanced Scenarios

R brings another layer to scenario analysis by allowing more advanced statistical and predictive modeling. Instead of only testing predefined changes, R can uncover relationships in the data and simulate a range of outcomes.

Take customer demand. In Excel, Pemi Coffee Roasters might assume demand rises or falls by 10%. With R, managers could run a regression to see how demand changes with season, weather, or promotions. They could also cluster customers into groups based on ordering habits. These approaches reveal patterns that simple “increase or decrease” assumptions can miss.

R also makes it easier to automate scenario runs. A script can generate dozens of versions—different cost structures, delivery times, or growth rates—without manually adjusting cells. The output can then be linked back into Excel or Power BI for visualization. This keeps the analysis flexible while grounded in data.

11.6 Bringing the Tools Together

Each tool—Excel, Power BI, and R—adds a different strength. Excel provides an easy entry point for exploring scenarios. Power BI turns scenarios into interactive visuals for quick interpretation. R allows for deeper, more technical exploration of the data. Together, they give a fuller picture of what could happen in an uncertain environment.

For Pemi Coffee Roasters, using all three tools means leadership can explore “what if demand spikes,” see the financial impact clearly, and check the results against patterns in past data. The company moves from reacting to surprises toward preparing for multiple possibilities.

Scenario planning is not about predicting the future with certainty. It is about being ready for several possible futures and choosing the best path when the time comes.

Chapter 11 References

Camm, J. D., Cochran, J. J., Fry, M. J., Ohlmann, J. W., & Anderson, D. R. (2021). Business analytics (4th ed.). Cengage Learning.

Delen, D. (2020). Prescriptive analytics: The final frontier for evidence-based management and optimal decision making. Decision Support Systems, 131, 113246. https://doi.org/10.1016/j.dss.2020.113246

Hyndman, R. J., & Athanasopoulos, G. (2021). Forecasting: Principles and practice (3rd ed.). OTexts. https://otexts.com/fpp3/

Microsoft. (2023). Introduction to Power BI. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/fundamentals/power-bi-overview

OpenStax. (2023). Introductory business statistics. OpenStax. https://openstax.org/books/introductory-business-statistics