Prescriptive Analytics
Lesson 8
Introduction
Prescriptive Analytics in Excel refers to the use of
advanced analytical techniques to recommend actions that
can optimize decision-making processes.
Unlike descriptive and predictive analytics, which focus
on understanding historical data and predicting future
outcomes, prescriptive analytics goes a step further by
suggesting the best course of action to achieve desired
objectives.
In the context of Excel, a widely-used spreadsheet software,
prescriptive analytics empowers users to leverage its
capabilities for strategic decision-making and problem-
solving.
How It Works in Excel:
Users can employ Excel's Solver to set up optimization
problems by defining decision variables, constraints, and
the objective function.
What-If Analysis tools like Scenario Manager and Data
Tables allow users to explore different scenarios and assess
the impact of changes on outcomes.
Mathematical formulas and functions in Excel are utilized
to represent relationships between variables, constraints,
and objectives.
Key Principles of Prescriptive Analytics:
1. Optimization:
Prescriptive analytics seeks to optimize decision-making by identifying the best
possible actions to achieve specific goals.
It involves the use of mathematical models and algorithms to explore various
scenarios and recommend the most efficient and effective course of action.
2. Incorporating Constraints:
The optimization process considers constraints, limitations, and business rules to
ensure that recommended actions are feasible and align with practical
considerations.
Constraints could include budgetary constraints, resource limitations, or regulatory
requirements.
3. Decision Variables:
Decision variables are the elements that the optimization model can control or
change to achieve the desired outcome.
Prescriptive analytics defines and manipulates these variables to find the optimal
solution.
Key Principles of Prescriptive Analytics:
4. Scenario Analysis:
Prescriptive analytics often involves scenario analysis to assess the impact of
different choices on outcomes.
Users can evaluate multiple scenarios to understand the trade-offs and risks
associated with various decisions.
5. Continuous Feedback Loop:
Prescriptive analytics is not a one-time process; it involves a continuous feedback
loop where decisions are made, outcomes are observed, and the model is refined
based on real-world results.
How Prescriptive Analytics Works in Excel:
❑ Solver Tool:
Excel's Solver is a key tool for implementing prescriptive analytics. It is an
add-in that performs optimization by adjusting selected cells according to
specified constraints and goals.
Users can define decision variables, set constraints, and specify the
objective function, and Solver will find the optimal solution.
❑ What-If Analysis:
Excel provides What-If Analysis tools like Goal Seek, Scenario Manager,
and Data Tables that allow users to explore different scenarios and assess
the impact of changes on outcomes.
This functionality is crucial for understanding how variations in input values
affect the recommended actions.
How Prescriptive Analytics Works in Excel:
❑ Mathematical Formulas and Functions:
Excel supports the implementation of mathematical formulas and functions
that can be used to model and solve optimization problems.
Users can create complex models using Excel functions and expressions to
represent decision variables, constraints, and objectives.
Goal Seek, Scenario Manager, and Data in
excel applying prescriptive analytics.
Goal Seek, Scenario Manager, and Data Tables are powerful
features in Excel that play a crucial role in applying prescriptive
analytics.
These tools allow users to perform what-if analysis, simulate
different scenarios, and optimize decision-making processes.
Let's discuss each of these tools in the context of prescriptive
analytics:
1. Goal Seek
2. Scenario Manager
3. Data Tables
Goal Seek:
Functionality:
Goal Seek is an Excel tool that allows users to find the input value
needed to achieve a specific goal or outcome.
Application in Prescriptive Analytics:
Example: In a financial model, you might use Goal Seek to
determine the required sales volume to achieve a targeted profit
margin.
Prescriptive Aspect: It helps in optimizing decisions by identifying
the input values necessary to meet predefined objectives.
Scenario Manager
Functionality:
Scenario Manager enables users to create and analyze different
scenarios by changing multiple input values and storing them for
comparison.
Application in Prescriptive Analytics:
Example: In a project schedule, you can use Scenario Manager to
evaluate the impact of variations in resource allocation on project
completion time.
Prescriptive Aspect: It aids in decision-making by allowing users to
explore and compare various scenarios, facilitating optimal
decision selection.
Data Tables
Functionality:
Data Tables provide a way to perform sensitivity analysis by
systematically changing multiple input values and observing the
resulting impact on calculated values.
Application in Prescriptive Analytics:
Example: You can use Data Tables to assess how changes in
interest rates and production costs affect the net present value
(NPV) of an investment project.
Prescriptive Aspect: It helps in understanding the sensitivity of
outcomes to changes in input variables, supporting better decision-
making under different conditions.
Steps for Applying Prescriptive Analytics:
1. Define the Problem:
Clearly articulate the problem, the decision to optimize, and the business
constraints.
2. Set Up the Spreadsheet:
Create a well-organized spreadsheet with cells for decision variables,
constraints, and the objective function.
3. Activate Solver:
Enable the Solver add-in in Excel.
4. Define Objective Function and Constraints:
Identify the objective function and constraints in your spreadsheet.
Steps for Applying Prescriptive Analytics:
5. Configure Solver Parameters:
Open Solver (Data -> Solver).
Set the objective cell, decision variable cells, and constraints.
6. Run Solver:
Click Solve, and Solver will use optimization algorithms to find the optimal
solution.
7. Review Results:
Examine the Solver results to understand the optimal values for decision
variables and the achieved objective.
Important Considerations:
Validate results against real-world knowledge and business
rules.
Be mindful of the limitations of optimization models, such as
linearity assumptions.
Document your model and solution for transparency and
reproducibility.
Key Discussion Points:
1. Decision Optimization:
Prescriptive analytics focuses on decision optimization by utilizing
mathematical models and algorithms to recommend the most effective
actions.
Excel's Solver add-in plays a crucial role in implementing decision
optimization, allowing users to define decision variables, constraints, and
objectives for the system to find the optimal solution.
2. What-If Analysis:
Excel's What-If Analysis tools, including Scenario Manager and Data
Tables, enable users to explore different scenarios by changing input
values and observing the impact on outcomes.
This capability is valuable for assessing the sensitivity of decisions to
changes in variables and understanding potential outcomes under
various conditions.
Key Discussion Points:
3. Application to Business Problems:
Prescriptive analytics in Excel finds application in diverse business
problems such as supply chain optimization, financial planning, inventory
management, and marketing budget allocation.
Through optimization techniques, businesses can make data-driven
decisions to improve efficiency, reduce costs, and achieve strategic
objectives.
4. User-Friendly Interface:
Excel's user-friendly interface makes prescriptive analytics accessible to a
broad audience, including users who may not have extensive analytical
or programming backgrounds.
Users can leverage Excel's formulas, functions, and built-in tools to create
and analyze prescriptive analytics models without the need for
specialized software or programming skills.
Application of
Optimizations to Business
Problems
Steps to Apply Optimization in Excel:
1. Define the Problem:
Clearly articulate the problem, identify decision variables, and define the objective
function and constraints.
Example: Optimize production schedules to maximize profit within the constraints of
available resources.
2. Set Up the Excel Spreadsheet:
Create a structured Excel spreadsheet with cells for decision variables, the
objective function, and constraints. Use labels and formulas to organize the
information.
Example: Create columns for product quantities, a cell for the profit function, and
rows for capacity constraints.
3. Activate Solver Add-In:
Enable the Solver add-in in Excel if it's not already activated.
Steps: File -> Options -> Add-Ins -> Manage Excel Add-ins -> Solver Add-in.
Steps to Apply Optimization in Excel:
4. Define Solver Parameters:
Open Solver (Data -> Solver).
Set the objective cell (the cell containing the objective function), decision variable
cells, and constraints.
Choose the optimization method (e.g., Simplex LP for linear programming
problems).
5. Run Solver:
Click Solve, and Solver will use optimization algorithms to find the values for
decision variables that maximize or minimize the objective function while satisfying
the constraints.
6. Review Results:
Examine the Solver results to understand the optimal values for decision variables
and the achieved objective.
Adjust the model or constraints as needed based on the results.
Examples of Optimization in Excel:
1. Production Planning:
Objective: Maximize profit.
Decision Variables: Quantity of each product to produce.
Constraints: Production capacity, resource availability, and budget.
2. Inventory Management:
Objective: Minimize holding costs.
Decision Variables: Order quantities for each product.
Constraints: Storage capacity and demand requirements.
3. Financial Portfolio Optimization:
Objective: Maximize return on investment.
Decision Variables: Allocation of funds to different assets.
Constraints: Budget constraints and risk tolerance.
Benefits of Application of Optimizations to
Business Problems in Excel
1. Cost Reduction:
Benefit: Optimization in Excel helps identify the most cost-effective solutions,
minimizing expenses in areas such as production, logistics, or resource allocation.
Example: Optimizing the supply chain to reduce transportation costs and
warehousing expenses.
2. Resource Efficiency:
Benefit: Optimal allocation of resources leads to increased efficiency in operations,
ensuring that resources are utilized effectively to achieve business objectives.
Example: Optimizing workforce schedules to meet demand without excess labor
costs.
3. Improved Decision-Making:
Benefit: Optimization models provide data-driven insights that contribute to better
decision-making, helping organizations choose the most strategic courses of
action.
Example: Optimal project portfolio selection based on available resources and
expected returns.
Benefits of Application of Optimizations to
Business Problems in Excel
4. Enhanced Productivity:
Benefit: Optimizing workflows and processes in Excel leads to increased
productivity by streamlining operations and minimizing bottlenecks.
Example: Optimizing production schedules to maximize output without overloading
machinery or staff.
5. Risk Mitigation:
Benefit: Optimization models in Excel can account for uncertainties and risks,
providing businesses with robust solutions that consider potential challenges.
Example: Optimizing inventory levels to account for demand fluctuations and
supplier uncertainties.
6. Strategic Planning:
Benefit: Optimization supports long-term strategic planning by helping
organizations align their resources and activities with overarching business goals.
Example: Optimizing marketing budgets to achieve maximum impact on strategic
marketing objectives.
Benefits of Application of Optimizations to
Business Problems in Excel
7. Competitive Advantage:
Benefit: Businesses that effectively apply optimization techniques gain a
competitive advantage by operating more efficiently and adapting to changing
market conditions.
Example: Optimizing pricing strategies to remain competitive while maximizing
profits.
8. Supply Chain Optimization:
Benefit: Optimizing supply chain processes in Excel improves overall efficiency,
reduces lead times, and enhances responsiveness to market demands.
Example: Optimizing order quantities and delivery schedules to minimize inventory
holding costs.
9. Financial Performance Improvement:
Benefit: Optimization contributes to financial performance improvement by
identifying opportunities to increase revenue, reduce costs, and maximize
profitability.
Example: Optimizing product pricing to maximize profit margins while staying
competitive.
Benefits of Application of Optimizations to
Business Problems in Excel
[Link] to Change:
Benefit: Optimization models in Excel can be easily adjusted to adapt to changes
in market conditions, business strategies, or external factors.
Example: Quickly adjusting production schedules in response to changes in
customer demand.
[Link] of Implementation:
Benefit: Excel's user-friendly interface and optimization tools make it accessible for
a wide range of users, simplifying the implementation of optimization models.
Example: Business analysts using Excel's Solver add-in to optimize resource
allocation.
[Link] Savings:
Benefit: Optimization in Excel allows for rapid scenario analysis, enabling users to
quickly assess various options and find optimal solutions.
Example: Swiftly optimizing project schedules to meet tight deadlines with minimal
resource constraints.
Video Guide about Prescriptive Analytics
[Link]
[Link]
F1WF4Xj21Ap_pN18
[Link]
[Link]
References:
Winston, W. L. (2020). Microsoft Excel Data Analysis and Business Modeling
(6th ed.). Microsoft Press.
Cao, L., Yu, P. S., & Zhang, C. (2021). Data Science: An Introduction. CRC
Press.
Chen, J. (2023). Practical Business Analytics Using R and Excel. Springer.
Hill, R., & Hill, T. (2023). Excel for Business Professionals: The Essential Handbook
for Solving Business Problems with Excel. Apress.
Pohl, E. A. (2021). Business Analytics: Turning Data into Insights. Wiley.
Alexander, M., & Kusleika, D. (2020). Excel 2019 Power Programming with
VBA. Wiley.
Buser, S. (2020). Mastering Financial Modeling: A Professional’s Guide to
Building Financial Models in Excel. Wiley.
Thank You!!!!
☺☺☺