Module 8: Final

Review:

• Module 7
• Grades

Presentation:

• Video Lecture
• Please ignore references to the “Data Science for Business” assignment. It’s not required this term.

Final Project:

• Advanced Retirement Simulation
  • Expand and refine your spreadsheet simulation of annual stock+bond returns to generate estimates of retirement savings.
  • Simulation Features:
    • 40-year time horizon
    • Simulation count, n=100
    • Ability to set/modify simulation parameters by changing cell values (not by editing formula)
      • initial balance
      • annual contribution
      • stock/bond % allocation
      • financial target, e.g., $1,000,000
    • Functional start button to launch automation of simulation
    • Display at end of simulation
      • probability (%) of reaching financial target
      • distribution of end balances on a histogram
      • calculation of Max, Min, Mean, Median and Standard Deviation of end balances
  • Video Presentation:
    • Each student will create a video in one of 2 formats
      • Zoom style screen recording (like my lectures), or
      • Homemade video (use your phone)
    • Provide a tour of your simulation spreadsheet
    • Demonstrate each of the “features” listed above
    • Explain what you learned about both simulation methods and retirement savings
    • Post your video on YouTube or upload and share on Google Drive (or use your preferred video platform).
    • Send me a link to your video via email or Bb.

Final Exam:

1. You must first complete the Final Project!
2. The sooner you complete the project, the sooner you’ll be able to begin your Final Exam.
3. Once I have received your Final Project and verified completion, I will send a confirmation email.
4. In the same confirmation email, or shortly thereafter, I will send materials and instructions for the Final Exam.
5. Exam materials will be unique for every student.
   • This creates a lot of extra work for me but it’s the best way I know to prevent “sharing” of answers.
   • If your exam submission doesn’t match the unique exam materials provided, you may receive a 0 on the final.
   • You should also note that I may be able to trace plagiarized work back to the original author. So, if you share your work with another student you may also receive a 0 on the final.
   • Whoa, dude. Pretty harsh. How can I save myself from this horrible fate? Easy: do your own work and don’t share it!

Module 7: Simulation Part 2

Review:

• Module 6
• Here’s a video I recorded while helping a student: Chuck-a-Luck Help Session (28 min)

Presentation:

• Video Lecture (38 min)
  • In the beginning I refer to the Chuck-a-Luck Help Session video
• Random Variables
  • Discrete Random Variables
    • Probability distribution table
    • Dice Simulation
  • Continuous Random Variables
    • Stock Market simulation next week

Assignment:

• Stock Market Simulation in Sheets
  • Use Annual Stock Market Return data as a starting point
  • Assumptions
    • stock market returns are normally distributed
    • mean = 11.88%
    • standard deviation = 19.76%
• Modifications Part 1
  • Generate a 40-year simulation (instead of 30)
  • Modify the simulation to add $1,000 to the balance each year
  • Make a histogram of your ending balances
• Modifications Part 2
  • Add a “fixed income” (bonds) component to your simulation
  • Assumptions
    • bond market returns are normally distributed
    • mean = 7.88%
    • standard deviation = 6.86%
• Your “portfolio” should be 60% stocks and 40% bonds
  • How does the distribution of ending balances change?
• Submit your Sheets link or Excel file here

Module 6: Simulation Part 1

Review:

• Module 5

Presentation:

• Video Lecture
• Probability
  • Topic worthy of its own course
• Simulation
  • Random phenomenon
    • Individual outcomes are uncertain
    • Regular distribution of outcomes in large number of trials
  • Random number generation and stochastic processes
• Coin Toss Simulation
  • Demonstrate Coin Toss Simulation in Sheets
• Dice Roll Simulation
  • Demonstrate Simulation of rolling a pair dice in Sheets
  • Lucky 7 game
    • Roll a 7, win bet
    • Roll anything else (other than a 7), lose bet

Assignment:

• Replicate the Coin Toss simulation
  • 200 coin tosses (instead of 32)
  • reproduce the graph (showing 200 tosses)
• Replicate the “Lucky 7” simulation
  • 100 rolls (instead of 32)
  • Experiment with different Payoff Odds values (in cell K2)
  • What payoff odds would you suggest the Casino owner use to maximize revenue?
• Simulate Chuck-a-luck where the player rolls 3 dice
  • Here are the rules
  • Apply the following simplifying assumptions
    • If sum of 3 dice >10, player wins the “Big” (1 to 1 odds)
    • If the roll is three of a kind, player wins “Any Triple” (30 to 1 odds)
    • All other outcomes, player loses the bet
  • Will the “House” win money over time with these rules? Explain.
• Upload to this Google Form
  • If Google Sheets, provide a Shared link
  • If MS Excel, upload your .xlsx file

Module 5: Regression Modeling Part 2

Review:

• Regression Modeling Part 1
• MLB
• East Side Real Estate

Presentation:

• Video Lecture 5.1
• Variable Creation
  • Demonstrate with Sample Real Estate data
  • Multicollinearity
  • Interaction Variables
    • combine separate variables mathematically
    • add, multiply, etc
    • another way to avoid problems with multicollinearity
  • Dummy Variables
    • incorporate categorical variables
    • binary – value is either 1 or 0
    • if 0, then the coefficient has no effect on y-hat
    • if 1, then the coefficient is added

Assignment:

• Multiple Regression Analysis
  • Select the worksheet corresponding to the last digit of your PID from this file: PuebloRealEstate_Exam2Data_ByPID
  • Save a copy of the data and convert to .xlsx format
  • Use Excel with the Analysis Toolpak Add-in
  • Build a regression model to estimate selling price
    • You must use 1 interaction variable
    • You must use 1 or more Dummy variables
    • Experiment with multiple combinations of variables to find the best model, maximizing and balancing R^2 and the F Statistic
  • Generate selling price forecasts for the following 4 Pueblo properties (with neighborhood location)
    • 4 bed, 3 ba, 2,931 sqft (Belmont)
    • 2 bed, 1 ba, 1,650 sqft (Central High School)
    • 3 bed, 2 ba, 1391 sqft (Northside/Avenues)
    • 5 bed, 3 ba, 3070 sqft (Regency and Meadows)
  • In Bb your assignment description you are asked to Measure Forecast Error. This is my mistake.
  • Submit your Excel spreadsheet (.xlsx) here.

Module 4: Regression Modeling Part 1

Review:

• Housekeeping
  • Discussion Boards
  • Grading
  • Uploading Spreadsheets
• Time Series Analysis

Presentation:

• Video Lecture 4.1

• Intro to Multiple Regression
• Multiple Regression Variable Selection
• Applied Regression Analysis (1 of 2)
• Applied Regression Analysis (2 of 2)

Assignment:

• Build a Regression Model to predict Real Estate prices
  1. Use East Pueblo RE Sales 2014-2016
  2. Test all variables and select the best combination
  3. Produce a Final Model, aiming to maximize R^2 and F-Statistic

• Build a Regression Model to estimate MLB winning percentage
  • Use 2013 MLB Team statistics
  • Test all variables and select the best combination
  • Produce a Final Model, aiming to maximize R^2 and F-Statistic

• Submit Your Assignment
  • Put your final models on two separate worksheets (maybe label one tab “Real Estate” and the other “MLB”) within *one* single Microsoft Excel file (.xlsx).
  • Upload your Excel file here

Module 3: Time Series Analysis

Presentation:

• Time Series Analysis
  • Video Lecture 3.1 (start at 1:20)
  • Scatter plots
  • Line plots
  • Trend lines
    • Linear
    • Polynomial
  • Forecasting
  • Oil Prices
  • Yield Curve

Module 2: Data Visualization

• Review
  • Discussion Board
  • DataCamp

• Watch this Video first!

• DataCamp: Introduction to Pivot Tables for Google Sheets

• Part 1
  • Video 2.1: Data Assembly
    • Compile a Colorado County Data Set
    • Download population data for all US counties
    • Download County GDP from the Bureau of Economic Analysis
    • Look up Congressional Districts for each Colorado County
  • Video 2.2: Pivot Tables
    • Complete Pivot Tables in Spreadsheets
    • Chapter 1 ONLY
    • Use a Pivot Table to aggregate GDP and Population by Congressional District
  • Video 2.3: Visualizing frequency distributions
    • Make a histogram
  • Video 2.4: Visualizing bivariate relationships
    • Make two scatterplots

• Part 2
  • Download US County Dataset
  • Create a Pivot Table
    • 49 rows and 4 columns
    • Each row corresponds to 48 States and Washington DC (Alaska and Hawaii are excluded)
    • Each column corresponds to four variables: Population, GDP, Median HH Income, Poverty %
    • Calculate the “Sum” for Population and GDP by State
    • Calculate the “Average” for Median HH Income and Poverty % by State
  • Create graphs (use Insert > Chart)
    • Use the resulting State aggregation (from the Pivot table)
    • Make four (4) histograms
      • (1) Population
      • (2) GDP
      • (3) Median HH Income
      • (4) Poverty %
    • Make four (4) scatter plots:
      • (1) Population (x) vs GDP (y)
      • (2) GDP (x) vs Poverty % (y)
      • (3) GDP (x) vs Median HH Income (y)
      • (4) Median HH Income (x) vs Poverty % (y)
    • All graphs must have axis labels
    • All scatter plots must include a “Trendline” along with labels for the “Equation” and “R^2 value” 

To submit your assignment in Bb, “Share” your Google Sheets file and copy/paste the link.

Module 1: Intro to Data Analysis with Spreadsheets

In week 1 you will begin learning to analyze data in spreadsheets.

Assignment 1:

• Watch this video
• Sign up for an account on DataCamp
• Complete these two courses
  • Introduction to Spreadsheets
  • Data Analysis in Spreadsheets

Introduction to Advanced Statistics Online

This course will consist of eight one-week modules, each module beginning Monday and ending the following Sunday. Each week you are responsible for contributing to Discussion forums in Bb and for completing an Assignment. I will provide descriptions and resources here each week and I will make an Announcement on Bb each time a new module post appears. Typically, I will also post one or more video lectures each week.

Here’s an introductory video.

Here’s a link to the course syllabus and below I’m providing some general guidelines for the asynchronous online format.

No Textbook! Don’t buy one. I use my own material.

Discussions:

• Each week you will earn up to 20 points by contributing to the discussion forums on Bb.
• Ask a question or share something you learned that saved you time or helped you understand the material. The idea is to be curious and promote understanding.
• Don’t regurgitate or summarize the material unless it’s part of a question or helpful insight.
• Submit comments or questions during the week, no later than Friday. If you wait until Sat or Sun your input won’t help you or anyone else. (That said, better late than never for scoring).
• Don’t login only once a week, make a few comments and logout 10 min later. I’ll notice if you’re doing the bare minimum.
• Don’t skip Discussion all together. If I see nothing, you’ll get 0/20.

Assignments:

• Follow instructions.
  • If I ask you to submit something via Bb or via Google Forms don’t email it instead. Grading is time consuming and handling special cases wastes my time.
  • Pay attention to format. If I ask you to submit an .xlsx file, don’t send a .pdf or other format.
• Make it easy for me to identify where you are answering a question. If I can’t find it you may not get credit.
• If you’re not sure what to do to complete an assignment, post a question on the discussion board! That’s the whole idea! Or you can send me an email if you prefer not to broadcast. Of course, this requires that you think about the assignment before 10p on Sunday.