INST 4 – DATA SCIENCE AND ANALYTICS
Module 6: Predictive Analytics Using Excel
Module 6: Predictive Analytics Using Excel
This module introduces learners to Predictive Analytics, emphasizing how to use Excel to
forecast future outcomes based on historical data. Students will learn the concepts and practical
implementation of Simple Linear Regression and Multiple Regression, enabling them to predict
trends, relationships, and business metrics using Excel’s built-in tools and functions.
1. Learning Outcomes
By the end of this module, students should be able to:
● Explain the principles and applications of predictive analytics.
● Perform simple linear regression to analyze the relationship between two variables.
● Conduct multiple regression analysis to model and predict outcomes involving multiple
independent variables.
● Interpret regression outputs such as coefficients, R-squared, and p-values.
● Apply predictive models in Excel to support data-driven decision-making.
2. Lesson Content
A. Overview of Predictive Analytics
Predictive Analytics uses statistical techniques and machine learning to forecast future trends
based on historical data. It identifies patterns and relationships to make informed predictions.
Key Concepts:
● Descriptive Analytics explains what happened.
● Diagnostic Analytics explains why it happened.
● Predictive Analytics predicts what will likely happen next.
● Prescriptive Analytics suggests what actions to take.
Applications in Business:
● Forecasting sales, revenue, or demand.
● Predicting customer churn or satisfaction.
● Estimating project costs or time completion.
● Anticipating market trends.
Excel Tools for Predictive Analytics:
● Trendlines (Insert → Chart → Add Trendline)
● [Link] and [Link] functions
● Regression analysis via Data Analysis ToolPak
B. Simple Linear Regression
Definition:
Simple Linear Regression models the relationship between a dependent variable (Y) and a
single independent variable (X).
Equation:
Y = a + bX
Where:
INST 4 – DATA SCIENCE AND ANALYTICS
Module 6: Predictive Analytics Using Excel
● ( Y ) = Dependent variable (predicted value)
● ( X ) = Independent variable (predictor)
● ( a ) = Intercept (value of Y when X = 0)
● ( b ) = Slope (rate of change in Y for every unit increase in X)
Steps in Excel:
1. Prepare your dataset (e.g., Sales vs. Advertising Cost).
Dataset:
Advertising Sales ($)
($)
200 1200
300 1500
400 1600
500 2100
600 2400
2. Prepare the Data:
o Enter the dataset into Excel:
1. Column A: Advertising ($)
2. Column B: Sales ($)
3. Go to Data → Data Analysis → Regression.
o Input Ranges:
1. Input Y Range: (Dependent Variable) Select the Sales column (B1:B6).
2. Input X Range: (Independent Variable) Select the Advertising column
(A1:A6).
4. Check “Labels” and choose an output range or new worksheet.
5. Click OK to generate regression statistics.
Key Outputs to Interpret:
● R Square (R²): Indicates how well X explains Y (values closer to 1 = stronger fit).
● Coefficients: Intercept and slope values forming the prediction equation.
● p-value: Determines statistical significance (p < 0.05 = significant relationship).
Example:
Predicting sales based on advertising expenses.
Regression output might yield:
Sales = 3x + 600
Sales = (3 * 200) + 600
Sales = 1,200
Visualization:
Add a Trendline to a scatter chart and display the equation and R² value.
C. Multiple Regression
INST 4 – DATA SCIENCE AND ANALYTICS
Module 6: Predictive Analytics Using Excel
Definition:
Multiple Regression analyzes the relationship between one dependent variable and two or more
independent variables.
Equation:
Y = a + b1X1 + b2X2 + ... + bnXn
Example:
Predicting Sales (Y) based on Advertising (X₁) and Price (X₂).
Steps in Excel:
1. Organize data into columns:
Sales Advertising Price
10500 1500 20
9500 1300 25
12300 1700 18
2. Go to Data → Data Analysis → Regression.
3. Set Input Y Range (Sales), and Input X Range (Advertising + Price).
4. Check “Labels” and “Confidence Level 95%” if needed.
5. Review outputs:
o Coefficients table → Shows impact of each predictor.
o R² and Adjusted R² → Measure model fit.
o p-values → Assess variable significance.
Interpretation Example:
Sales = 30,000 + 6.5(Advertising) - 2.1(Price)
→ Advertising increases sales by ₱6.5 per peso spent, while higher price reduces sales by ₱2.1
per peso.
Best Practices:
● Avoid multicollinearity (high correlation among predictors).
● Remove insignificant variables (high p-values).
● Validate predictions using new data.
Summary / Key Takeaways
● Predictive Analytics uses historical data to forecast future trends.
● Simple Linear Regression predicts outcomes using one independent variable.
● Multiple Regression involves several predictors to improve accuracy.
● Excel’s Data Analysis ToolPak simplifies regression modeling.
● Interpreting coefficients, R², and p-values is crucial for valid predictions.
Watch videos on Linear and Multiple Regression as supplementary modules.
[Link]
INST 4 – DATA SCIENCE AND ANALYTICS
Module 6: Predictive Analytics Using Excel
[Link]
References
● Microsoft (2023). Use the Analysis ToolPak to perform complex data analysis. Microsoft
Support.
[Link]
ata-analysis-6d2e3f6b-57f7-4f26-b9e9-7b5a2fdc8f5e
● Albright, S. C., & Winston, W. L. (2022). Business Analytics: Data Analysis and
Decision Making (8th ed.). Cengage Learning.
● Montgomery, D. C., Peck, E. A., & Vining, G. G. (2021). Introduction to Linear
Regression Analysis (6th ed.). Wiley.
● Harvard Business Review (2020). Predictive Analytics and Data-Driven Decisions.
● Winston, W. (2021). Microsoft Excel Data Analysis and Business Modeling (7th ed.).
O’Reilly Media.