IT INST 3 – DATA SCIENCE AND ANALYTICS
Module 8: Prescriptive Analytics using Excel
Module 8: Prescriptive Analytics Using Excel
Learning Outcomes
By the end of this module, students should be able to:
1. Explain the concept, purpose, and value of Prescriptive Analytics in business
decision-making.
2. Use Excel’s What-If Analysis tools (Goal Seek, Scenario Manager, Data Tables) to
evaluate alternative decisions.
3. Apply optimization techniques using Excel’s Solver Add-in to recommend the best
decisions under constraints.
4. Interpret optimization results, including optimal solutions, binding constraints, and
sensitivity to changes.
5. Integrate prescriptive analytics outputs into dashboards or decision-support models
for business applications.
1. Lesson Content
A. Overview of Prescriptive Analytics
Definition
Prescriptive Analytics is the highest-level analytics technique that not only predicts future
outcomes but also recommends actions to achieve the best possible result.
It answers the question:
👉 "What should we do next?"
How it differs from previous analytics types
Analytics Type Question Answered Example
Descriptive What happened? Monthly sales reports
Diagnostic Why did it happen? Variance and root-cause analysis
Predictive What will likely happen? Sales forecasting using regression models
Prescriptive What should we do? Optimize production, staffing, pricing
Prescriptive analytics uses:
Optimization techniques
Simulation
Decision analysis
Scenario exploration
What-if models
Applications in Business
Optimizing production schedules to minimize cost
IT INST 3 – DATA SCIENCE AND ANALYTICS
Module 8: Prescriptive Analytics using Excel
Determining best pricing strategies
Allocating limited resources (budget, personnel, inventory)
Finding optimal product mix
Portfolio optimization in finance
Workforce scheduling in service industries
Transportation and logistics optimization
Excel Tools Supporting Prescriptive Analytics
What-If Analysis
o Goal Seek
o Scenario Manager
o Data Tables
Solver Add-in (Optimization Tool)
o Linear Programming
o Integer Programming
o Non-linear Optimization
These tools make Excel a powerful environment for decision modeling, even without
advanced programming.
B. What-If Analysis Tools
Excel’s What-If Analysis lets you manipulate variable inputs to observe their impact on
outcomes.
These tools support exploratory analysis and decision-making.
1. Goal Seek
Goal Seek finds the input value needed to reach a target output.
Structure:
Set cell: The formula or outcome cell
To value: Desired result
By changing cell: Input variable to adjust
Example:
A company wants to achieve ₱500,000 profit.
Formula:
Profit = Revenue – Cost
Use Goal Seek to find:
“How many units must be sold to achieve ₱500,000 profit?”
Steps:
1. Go to Data → What-If Analysis → Goal Seek
2. Set the profit cell
3. Enter target profit (500000)
IT INST 3 – DATA SCIENCE AND ANALYTICS
Module 8: Prescriptive Analytics using Excel
4. Select the cell for units sold
5. Click OK
2. Scenario Manager
Allows building multiple scenarios (e.g., Best Case, Worst Case, Expected Case).
Useful when:
You have several changing variables
You want to compare outcomes side-by-side
Example Scenarios:
High customer demand + low material cost
Low customer demand + high material cost
Steps:
1. Data → What-If Analysis → Scenario Manager
2. Click Add to create scenarios
3. Enter changing cells (e.g., price, demand, cost)
4. Enter values for each scenario
5. Generate a Scenario Summary Report
3. Data Tables
Used for sensitivity analysis.
Two types:
One-Variable Data Table
– Impact of one changing input on an output
Two-Variable Data Table
– Impact of two simultaneous changing inputs
Example:
“What happens to profit if selling price changes from ₱100 to ₱200?”
Steps:
1. Set up values for price in a column
2. Place the profit formula on top
3. Select the entire table
4. Go to Data → What-If Analysis → Data Table
5. Enter column input cell (the price cell)
Excel generates dynamic outcomes automatically.
IT INST 3 – DATA SCIENCE AND ANALYTICS
Module 8: Prescriptive Analytics using Excel
C. Application of Optimization to Business Problems
This section introduces Excel’s Solver, a powerful optimization tool.
1. Excel Solver Add-in
Purpose of Solver
Solver identifies the best possible outcome (maximum, minimum, or exact value) given:
Decision variables (values to determine)
Objective function (goal to optimize)
Constraints (limitations: budget, capacity, resources)
2. Key solver concepts
Concept Explanation Example
Objective Function Formula to maximize/minimize Maximize profit
Decision Variables Values Excel will modify Units of product A & B
Constraints Limitations in the model Budget ≤ ₱100,000
3. Types of optimization problems in business
1. Product Mix Optimization
– Which product combination yields highest profit?
2. Resource Allocation
– How to distribute limited materials or workforce?
3. Cost Minimization
– How to minimize total expenses under constraints?
4. Supply Chain Optimization
– Minimize transportation cost or lead time.
5. Staff Scheduling
– Assign minimum staff to cover all shifts.
4. Steps in Using Solver
Example Problem: Product Mix Optimization
A bakery produces:
Bread (profit ₱15 each)
Cookies (profit ₱10 each)
Constraints:
Labor hours: max 300 hours
Flour: max 200 kg
Bread uses: 2 labor hrs + 1 flour
Cookies use: 1 labor hr + 1 flour
IT INST 3 – DATA SCIENCE AND ANALYTICS
Module 8: Prescriptive Analytics using Excel
Goal: Maximize Profit
Step-by-Step Solver Setup
1. Set up the spreadsheet:
Product Units to Produce Profit per Unit Total Profit
Bread decision variable 15 =Units*15
Cookies decision variable 10 =Units*10
Total Profit: =SUM(total profits)
Constraint table:
Resource Bread Use Cookies Use Max Available
Labor Hours 2/unit 1/unit 300
Flour (kg) 1/unit 1/unit 200
2. Open Solver
Data → Solver
3. Configure Solver Parameters
Set Objective: Total Profit cell
To: Max
By Changing Variable Cells: Units of Bread and Cookies
Subject to Constraints:
o Labor_Use ≤ 300
o Flour_Use ≤ 200
o Units ≥ 0
o Optionally: integer constraint (no half-loaves)
4. Choose Solving Method
Simplex LP: For linear problems
GRG Nonlinear: For nonlinear functions
Evolutionary: For complex or discrete models
5. Run Solver
Click Solve → Generate the optimal plan.
5. Interpreting Solver Results
Solver provides:
Optimal Values
IT INST 3 – DATA SCIENCE AND ANALYTICS
Module 8: Prescriptive Analytics using Excel
– Best units to produce for maximum profit
Binding Constraints
– Constraints fully used up (these restrict the solution)
Non-binding Constraints
– Resources that still have remaining capacity
Sensitivity Information
(If reports are generated)
Useful for:
Managerial insights
What-if restructuring
Cost-benefit evaluation
6. Common Business Optimization Examples (Excel)
1. Inventory Optimization:
Minimize carrying cost while meeting service levels.
2. Transportation Problem:
Minimize shipping costs between warehouses and stores.
3. Marketing Budget Allocation:
Distribute funds to maximize total ROI.
4. Portfolio Optimization:
Balance risk and return using constraints.
5. Production Scheduling:
Determine optimal production levels for multiple products.
Summary / Key Takeaways
Prescriptive Analytics provides recommended actions to achieve optimal outcomes.
What-If Analysis (Scenario Manager, Goal Seek, Data Tables) helps explore
alternative decisions.
Excel Solver supports optimization for real-world business problems such as resource
allocation, costing, product mix, and scheduling.
A typical optimization model includes decision variables, an objective function, and
constraints.
Understanding how to interpret Solver results is crucial for effective data-driven
decision-making.
References
Microsoft (2023). Use Solver to optimize results in Excel. Microsoft Support.
Winston, W. (2021). Excel Data Analysis and Business Modeling. O’Reilly Media.
IT INST 3 – DATA SCIENCE AND ANALYTICS
Module 8: Prescriptive Analytics using Excel
Albright, S. C., & Winston, W. L. (2022). Business Analytics: Data Analysis and
Decision Making. Cengage Learning.
Montgomery, D. C. et al. (2021). Introduction to Linear Optimization. Wiley.