## MODELING TO PREDICT & EXPLAIN

"Modeling links classroom mathematics and statistics to everyday life, work, and decision-making. Modeling is the process of choosing and using appropriate mathematics and statistics to analyze empirical situations, to understand them better, and to improve decisions. Quantities and their relationships in physical, economic, public policy, social, and everyday situations can be modeled using mathematical and statistical methods. When making mathematical models, technology is valuable for varying assumptions, exploring consequences, and comparing predictions with data."

Source: Introductory paragraph from CA Common Core standards for Modeling published in August 2010 (Pages 60-1).

CA Common Core Standards for Mathematical Modeling: View the full two-page discussion.

EDUCATIONAL STRATEGY: Business Statistics teaches students to use real world data to model functions for a variety of business applications.  Excel software allows students to quickly derive regression models for linear, polynomial, logarithmic and exponential functions.  Recent advances in statistical software have reduced the priority of teaching the computational mechanics used in deriving these mathematical models themselves.  Even weak math students can obtain these best fit models in a matter of seconds using Excel.  With several functions in hand, educational emphasis shifts to teaching students how to select the most reliable mathematical model for prediction.  Up until the turn in the millenium, such analysis was only taught in upper division college courses.   Now this content represents the next logical step in preparing students to thrive in the data driven 21st Century.

Correlation of Stock Price & Time: Description to follow.  This new evaluation tool provides a good opportunity to review how Excel quantifies time.

Deriving Least Squares Regression Line (LSRL):   Description to follow.

Using Logarithms To Derive The Exponential & Power Models:  You' ll have another reason to love Excel.  Check out how we had to derive the exponential and power mathematical models in the olden days (yes, I'm talking the 1980s).

Evaluating The Correlation Between Volume & Price Changes: Description to follow.

Stock Price Forecasting Assignment:  Students select a stock and research its month-end closing price for the last 24 months.  Usng Excel software, students create a scatteplot, generate six mathematical models for forecasting purposes, and each of the six functions are evaluated to predict a price for the stock three months in the future (i.e. evaluating the functions for X=27).  The best models for predicting near-term price trends are identified through an examination of coefficients of determination.  During this assignment, specific Excel skills taught include: the equation editor, use of the EXP and LN formulas, deriving the various mathematical models and their associated coefficient of determinations (R-squared) in the scatterplot editing windows.

The results of the student work are assembled in a table that lists the equation, predicted price and coefficient of determination for each mathematical model.  The Excel workbook below shows a worksheet with the calculations and another worksheet displaying the finished product.  These one-page presentations are assembled into the portfolio files for each stock group.

Excel Workbook With Presentation & Worksheet Used To Generate Results 2011-12

Six Functions of a Dollar Materials

Forecasting Using Stock Prices Using Linear, Power  & Exponential Models:

Forecasting Using Stock Prices Using Linear, Quadratic  & Logarithmic Models:

Evaluation of Mathematical Models To Forecast Stock Prices: Alfac Inc Example EXCEL FILE 2012

Evaluation of Mathematical Models To Forecast Stock Prices: Aflac Example PDF FILE 2012