Trendlines and Regression
Analysis
Jaypee Business School, Jaypee Institute of Information Slide - 1
Modeling Relationships and Trends in
Data
• Create charts to better understand data sets.
• For cross-sectional data, use a scatter chart.
(Cross-sectional data is a type of data collected
at a single point in time from a sample of
individuals, objects, or entities. It provides a
snapshot of a population or sample at a
particular moment.)
• For time series data, use a line chart.
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 2
Common Mathematical Functions
Used n Predictive Analytical Models
Linear y a bx
Logarithmic y ln( x)
Polynomial (2 order)nd y ax 2 bx c
Polynomial (3 order)rd y ax 3 bx 2 cx d
Power y ax b
Exponential y ab x
(the base of natural logarithms, e 2.71828 is often
used for the constant b)
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 3
Excel Trendline Tool
• Right click on data series
and choose Add trendline
from pop-up menu.
• Check the boxes Display
Equation on chart and
Display R-squared value
on chart.
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 4
R Squared
• R 2 (R-squared) is a measure of the “fit” of the
line to the data.
– The value of R 2 will be between 0 and 1.
– A value of 1.0 indicates a perfect fit and all
data points would lie on the line; the larger the
value of R 2 the better the fit.
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 5
Example 8.1: Modeling a Price-
Demand Function
Linear demand function:
Sales 20,512 9.5116(price)
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 6
Example 8.2: Predicting Crude Oil
Prices (1 of 3)
• Line chart of historical crude oil prices
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 7
Example 8.2: Predicting Crude Oil
Prices (2 of 3)
• Excel’s Trendline tool is used to fit various functions to
the data.
Exponential y 50.49e0.021x R 2 0.664
Logarithmic y 13.02ln( x) 39.60 R 2 0.382
Polynomial 2° y 0.13 x 2 2.399 x 68.01 R 2 0.905
Polynomial 3° y 0.005 x 3 0.111x 2 0.648 x 59.947 R 2 0.928*
Power y 45.96 x 0.0169 R 2 0.397
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 8
Example 8.2: Predicting Crude Oil
Prices (3 of 3)
• Third order polynomial trendline fit to the data
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 9
Regression Analysis
• Regression analysis is a tool for building
mathematical and statistical models that characterize
relationships between a dependent (ratio) variable
and one or more independent, or explanatory
variables (ratio or categorical), all of which are
numerical.
• Simple linear regression involves a single
independent variable.
• Multiple regression involves two or more
independent variables.
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 10
Simple Linear Regression
• Finds a linear relationship between:
– one independent variable X and
– one dependent variable Y
• First prepare a scatter chart to verify the data has a linear
trend.
• Use alternative approaches if the data is not linear.
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 11
Example 8.3: Home Market Value
Data
Size of a house is
typically related to its
market value.
X = square footage
Y = market value ($)
The scatter chart of
the full data set (42
homes) indicates a
linear trend.
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 12
Finding the Best-Fitting Regression
Line
• Market value
a b square feet
• Two possible lines are shown below.
• Line A is clearly a better fit to the data.
• We want to determine the best regression line.
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 13
Example 8.4: Using Excel to Find the
Best Regression Line
• Market value
32,673 $35.036 square feet
– The estimated market value of a home with 2,200 square feet
would be: market value 32,673 $35.036 2, 200 $109,752
The regression model
explains variation in market
value due to size of the
home.
It provides better estimates
of market value than simply
using the average.
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 14
Simple Linear Regression with Excel
Data > DataAnalysis > Regression
Input Y Range
(with header)
Input X Range
(with header)
Check Labels box
Excel outputs a table
with many useful
regression statistics.
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 15
Home Market Value Regression
Results
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 16
Example 8.6: Interpreting Regression
Statistics for Simple Linear Regression
Jaypee Business School, Jaypee
Copyright Institute
© 2021 Pearson Educationof
Ltd. Information Slide - 17
