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.