Descriptive
Analyzes historical data to understand past business performance (e.g., describe sales from last three years).
Excel tools (examples)
INSY 2299 · Information Systems
Session 5: Simulation Models & Prescriptive Analytics
Forecast quality · What-if models · Goal Seek, Scenario Manager, Data Tables & Solver
Dominik Molitor · Fall 2026 | Session 5
Session 5
Sign-in via Blackboard
Today’s code
0000
Click the number to set it for today.
Session 5
Agenda
Click a line to jump straight to it.
Forecasting Cont’d
How good were our forecasts?
Updated sales quarters allow us to calculate the quality of our forecasting approaches.
| Approach | Forecast (row 46) | Abs. % error (row 46) | Mean (row 62) | Mean abs. % error |
|---|
Business Analytics
Three different types of business analytics (in Excel)
Last week was predictive. This week moves one step right. Reveal the Excel tools for each.
Predictive
Uses (statistical) techniques to forecast future outcomes (e.g., predict next year’s sales based on historical data).
Excel tools (examples)
Prescriptive This week’s focus
Recommends actions to optimize business outcomes (e.g., find the profit-maximizing price for a new product).
Excel tools (examples)
Business Decision Making: Pricing Mini Case Study
Pricing a new cold brew
Background
Our student startup wants to launch a bottled cold brew, “Ram Roast”, sold from a cart on campus. Each morning we brew a batch; bottles not sold by closing are poured out.
Questions
How should we choose a price? Which pricing strategies are you aware of?
Which price should we choose?
Take a minute with the person next to you: name one way to set the price, and what you would need to know to use it. The next slide collects the answers.
Business Decision Making: Pricing Mini Case Study
From pricing strategy to simulation model
1 of 4 shown
Possible pricing strategies:
(1) Match competitor, (2) Cost-based, (3) Measure willingness to pay*
We also need to know our business objective:
(1) Maximize profit or (2) Maximize market share
Considerations: price elasticity of demand (sensitivity of demand to price changes)
We could make predictions of the price by
(1) Collecting survey data, (2) Participating in a test market or (3) Conducting a real-world experiment
But we don’t; these methods either have a downside (biased responses and/or too risky/time-consuming) or are too expensive and thus not feasible for us.
By building a mathematical simulation model and manipulating the model instead of the real world (by taking the price elasticity into account).
*advanced
Simulation Modeling
Simulation (“What if?”) models
We build and use simulation models so that we can ask “What if?” questions — without manipulating or experimenting with the real world (because that can be difficult, expensive, time-consuming, dangerous, or impossible — remember, we are a student startup that sells cold brew from a campus cart).
Experiment with the real world
Change the price on the cart, wait a few weeks, count the bottles. One answer per try, and every wrong try costs real money.
Experiment with the model
Change the price in a cell and the outcomes recalculate instantly. Try a hundred prices before lunch; undo anything.
Put differently, we manipulate the model instead of manipulating the real world.
Simulation Modeling
Simulation model — “What if” model (in business analytics)
Simulation Model: Definition
A mathematical representation of the essential characteristics of a business problem or situation used to ask “What if” questions for the purposes of making prescriptive (or predictive) decisions.
Click a box to see what goes in it.
Simulation Modeling
Simulation model — stock portfolio example
| Decision Variables | Uncontrollables | Outcomes | |||
|---|---|---|---|---|---|
| Stock | Units | Purchase Price | Current Price | Value ($) | Gain (Loss) |
The model
VALUE= UNITS * CURRENT PRICE
GAIN (LOSS)= VALUE − (UNITS * PURCHASE PRICE)
Simulation Modeling
Simulation model — Ram Retail example
Last week’s planning model is a simulation model too. Which inputs does management decide, and which does the market decide?
Click a box: the next slide highlights the same cells in the workbook.
Simulation Modeling
The same three parts, in the workbook
Prescriptive Analytics
Which Excel tool fits the question?
Choose the tool based on the question you want to answer.
| Business question | Excel tool |
|---|---|
| What admin growth rate gives us $50,000 in net income? | Change one input to reach a target output. |
| How do our chosen price-and-quantity plans compare? | Save and compare named sets of inputs. |
| What happens across a range of prices and quantities? | Compare a grid of one or two varying inputs. |
| Which price and quantity maximize profit within our limits? | Search for the best outcome within constraints. |
Click a tool to jump to its slide.
Goal Seek
Meeting an income target with Goal Seek
Data ▸ What-If Analysis ▸ Goal Seek. Set a target for Net Income in 2028, pick the input Excel may change, press OK.
Goal Seek ?
Set cell: E24
Business Decision Making: Pricing Mini Case Study (Recap)
Back to Ram Roast
Background
We want to launch a bottled cold brew: “Ram Roast”.
Questions
Which price should we choose? How do we choose a price/quantity?
Solution: Simulation Model → Data Table
Decision VariablesPrice, Quantity
The Model
OutcomesSales, Market Share, Profit
Simulation Model Demo
Go to Blackboard / Datasets to download the Simulation file
Datasets
- Sales Forecasting
- Sales Forecasting Updated
- Lookup Example
- Simulation
Simulation Model
DEMO
Open it beside the deck. Two inputs, three formulas — and four different ways to ask it for the best price.
Simulation Model Demo
Starting file: two inputs, three formulas
Sales · C5
=MIN(B5,ROUND(MAX(10-A5,0)*123,0))
Market Share · D5
=C5/2500
Profit · E5
=A5*C5-B5*0.75
Scenario Manager
Scenario Manager
Allows us to create alternative scenarios where multiple variables can be changed (we will stick with 2, given that we can only influence the price and quantity).
Scenario Manager ?
Scenarios:
Changing cells: $A$5:$B$5
Pick a scenario and press Show: Excel types its values into A5:B5.
Scenario Manager
Scenario Manager: summary table
Summary… writes this report on a new sheet. Current Values are whatever A5:B5 hold when you run it — here, what the previous slide last showed.
Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray.
Data Table
Data Table — allows to find optimal prices and quantities
Aim: Maximize profit by optimized combination of price and quantity
Discuss first. Then look inside the formulas: the model treats these as fixed.
10in D5 — the price at which demand drops to zero123in D5 — bottles of demand lost for every $1 of price2500in E5 — everyone who passes the cart: the whole market0.75in F5 — what it costs to brew and bottle one unit
Beyond the model: competitors’ prices, ingredient costs, tastes.
Optional: Excel Solver
Excel Solver — find the best possible solution based on a set of conditions
Press Solve. Solver tries price/quantity pairs inside the constraints and keeps the best one.
Solver is an add-in: File ▸ Options ▸ Add-ins ▸ Manage Excel Add-ins ▸ Solver Add-in. It then appears under Data ▸ Analyze.
Solver Parameters ×
Set Objective: $E$5
To: MaxMinValue Of
By Changing Variable Cells: $A$5:$B$5
Subject to the Constraints:
$A$5 <= 7
$A$5 >= 4
$B$5 <= 700
$B$5 = integer
$B$5 >= 400
$A$5 >= 4
$B$5 <= 700
$B$5 = integer
$B$5 >= 400
Solving Method: Evolutionary
Business Analytics
The analytics spectrum
| Type | Questions answered | Time focus | Example |
|---|---|---|---|
| Descriptive | What happened? | Past | Monthly sales report |
| Predictive | What could happen? | Future | Sales forecast |
| Prescriptive | What should we do? | Future | Optimal pricing strategy |
Assignment & Logistics
Reminder & assignments
Reminder
- Assignment #2 is due tonight (day of class), 11 PM.
- Tutorial Module #7 is due next week (day of class), 11 PM.
Assignment & Logistics
Assignment #2: two different “averages”
Try any three values:
Example numbers, not the assignment’s.
Average Annual Percentage Change
…That percentage change is equal to the Average Annual Percentage Change from 2023 to 2025. Calculate this value by averaging the percentage change from 2023 to 2024 and the percentage change from 2024 to 2025.
(((Values 2025−Values 2024)/Values 2024)
+((Values 2024−Values 2023)/Values 2023))/2
Average Annual Change (in dollars)
…assume that Advertising will change by the same dollar amount (not the same percentage) from one year to the next — e.g., from 2024 to 2025. That amount is equal to the Average Annual Change (in dollars) between 2023 and 2025.
(Values 2025−Values 2023)/2