Category: f25-busad360

Lesson 13: Final Exam

Review:

  • DataCamp Assignments
  • Google Sheets Fundamentals Skill Track
  • Simulation Files

Presentation:

  • Take Home Final
    • You will receive a final exam in Google Sheets format
    • The Sheets file will contain all necessary data and instructions
    • There will be several data analysis tasks for you to complete
  • Video
    • After finishing the exam problems, you will create a video
    • Provide a video tour of your final exam answers, explaining your approach to each problem
    • Verify your identity! I’d like to both see (and hear) you talking for at least a few seconds
    • Speak clearly! If I can’t follow your commentary throughout it may negatively impact your score
    • Don’t read from a script! Use your own words as if we’re having a conversation
    • Check the video quality! If I can’t see the screen clearly it may negatively impact your score
    • Your video should be no longer than 6 minutes in length
    • Create a shared link to your video from Google Drive or YouTube or similar
  • Submission
    • The shared spreadsheet will contain everything necessary
    • You will insert a link to your video inside your Sheets exam file
    • Email when you’re ready to be graded
  • Due Date
    • Your exam spreadsheet and video are due next week no later than Fri, Dec 19 @ 5pm
    • Submit early and you’ll be rewarded with up to +10% bonus (the earlier you submit, the larger the bonus)

Activity:

  • Complete a course evaluation
  • Take a look at this Practice Final Exam to get an idea what to expect
  • Expect to receive your Final Exam sometime tomorrow (Tue, Dec 9)
More

Lesson 12: Bootstrapping

Review:

  • Vendor Sim grading components
    • Business Idea Clarity
    • Use of Vendor Simulation
    • Spreadsheet modification and customization
    • Executive Summary Quality
    • Writing Mechanics
  • Schedule

Presentation:

  • What is Bootstrapping?
    • Bootstrapping is a simulation method
    • Use your actual data (downloaded, collected, observed).
    • Randomly select values from the sample.
    • Each row of data must have an equal chance of selection.
    • Repeat many, many times (hundreds or thousands) to represent a wide range of outcomes.
    • Use the resulting distribution of simulated values to estimate likely outcomes and uncertainty.
  • Why use it?
    • Works well when
      • Distribution is unknown
      • Distribution is not Normal (e.g., skewed)
      • Sample size is large
    • Problematic when
      • Data isn’t representative
      • Sample size is small

Key idea: “Since we don’t know the true population, we use our sample data set as if it is the population and repeatedly draw new random samples (with replacement) to simulate variance.”

Demonstrate Bootstrapping in Google Sheets

Activity:

  • Create a bootstrapping simulation with your assigned commodity
    • Download commodity data (1 year daily or 5 years weekly)
    • Use the bootstrap method to simulate changes in commodity price
    • Replicate Bootstrapping in Google Sheets, replacing Bitcoin data with your commodity data.
  • Share your simulation files with Rylee, preferably in one Sheets file. Include the following:
    • Coin Toss
    • Roll 2 Dice
    • Lucky 7
    • Simple Craps
    • Vendor
    • Commodity

Assignment:

More

Lesson 11: Intermediate Sheets and Data Visualization Dashboards

Review:

  • Vendor Simulation
  • Schedule
    • Nov 24 – No class (Fall break)
    • Dec 1 – Bootstrapping
    • Dec 8 – Review for Final Exam
    • Dec 15 – Final Exam

Presentation:

Activity:

Assignment:

More

Lesson 10: Vendor Simulation

Review:

  • Discrete vs Continuous probability/simulation
    • This week – discrete
    • Next week – continuous
  • Lucky 7

Presentation:

Activity/Assignment:

  • Create your own copy of the Vendor Simulation (File>Make a Copy)
  • Create a business idea for selling a product(s) to students/faculty/staff in the HSB entrance/atrium.
  • Use the Vendor Simulation to estimate average daily profit for your business idea.
  • Modify the Vendor Simulation to accommodate additional criteria you need to take into account.
  • Customize the spreadsheet so that you could demonstrate to potential investors (or HSB and CSUP Admin for approval).
  • Write a 1-page Executive Summary presenting your business idea and illustrating the profit potential.
More

Lesson 9: Intro to Monte Carlo Simulation

Review:

  • Multiple Regression exam results
  • Exam solutions
  • Used car pricing model submissions

Presentation:

  • 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
    • Discrete and Continuous (we’ll start with discrete)
  • Coin Toss Simulation
  • Dice Roll Simulation

Assignment:

  • Replicate the Coin Toss simulation
    • 200 coin tosses (instead of 32)
    • reproduce the graph (showing 200 tosses)
  • Create a “Roll 2 Dice” simulation
    • 100 rolls (instead of 32)
    • Roll 2 Dice instead of only 1
    • Be sure the probability distribution is the same as actually rolling 2 Dice.
  • Create a “Lucky 7” simulation
    • Roll 2 Dice
    • If you roll a 7, you win
    • If you don’t roll a 7, you lose
    • Track the number of wins and losses
  • Create a “Simple Craps” simulation
    • Roll 2 Dice
    • If you roll a 7 or 11, you win $10
    • If you roll a 2, 3 or 12, you lose $5
    • If you roll anything else (i.e., 4, 5, 6, 8, 9, 10), it’s a “push” or a tie and you don’t lose or win $$.
    • Set up a scoring system to keep track of how much $$ you win or lose.
More

Lesson 8: Residual Analysis and Forecast Error

Review:

  • Categorical data and Indicator (aka “dummy”) variables
  • Extended multiple regression Quiz next week on Mon, Oct 27
  • Used car model with video presentation (see below) due Mon, Nov 3

Presentation:

  • Residual Analysis
  • Measurement of forecast error
    • MAD – mean absolute deviation
      • MAD = ∑|Actual – Forecast|/(Number of Forecasts)
    • RMSE – root mean square error
      • RMSE = √(∑(Actual – Forecast)^2/(Number of Forecasts))
    • Demonstrate

Activity:

  • Use your Used Car multiple regression model
  • Generate residuals
  • Create an Actual vs Predicted scatterplot

Assignment:

  • Use this more extensive Used Car sales dataset
  • Choose a particular Make and Model, e.g., “Honda Fit” or “Ford F-150” (sign up before end of class)
  • Build a multiple regression model
  • Generate residuals and make an Actual vs Predicted plot
  • Produce a video describing your model and showing the Actual vs Predicted plot.
  • Submission
    • Your model spreadsheet file or link
    • Video presentation (1-3 minutes, use YouTube or PowerPoint)
    • Send via email to me
    • Due before class on Mon, Nov 3
More

Lesson 7: Categorical Data and Indicator Variables

Review:

  • Multicollinearity
  • Interaction Variables

Presentation:

  • Pivot tables to aggregate by category
  • Indicator Variables (aka “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 to the estimate
  • Demonstration with Sample Real Estate data
    • Use a Pivot table to summarize price by neighborhood
    • create a dummy variable for a neighborhood
    • create a dummy variable for multiple neighborhoods

Activity:

  • Use the Pueblo Real Estate model (from last lesson)
    • Incorporate one or more Indicator variables
    • Can you improve R^2 and/or F with an indicator variable?

Assignment:

  • Download Used Cars data and utilize Excel (dataset is large enough to slow Sheets)
    • Create a correlation matrix for all numeric variables.
    • Do you see evidence of multicollinearity?
    • If so, incorporate one or more interaction variables
    • Incorporate one or more Indicator variables to include categorical data elements (e.g., Make, Model, Trim or Type)
    • After experimenting with Interaction and Indicator variables, make a model with whatever variables you think work best, to estimate used vehicle sales prices.
More

Lesson 6: Interaction Variables in Multiple Regression

Review:

Presentation:

  • MLB Baseball model
  • Applied Regression Analysis
    • Demonstrate with Sample Real Estate data
    • Problem: Multicollinearity
    • Solution: Interaction Variables
      • combine separate variables mathematically
      • add, multiply, etc
      • another way to avoid problems with multicollinearity
    • Estimate selling price for a 1500 sqft, 3 bd, 2 ba home

Activity:

  • Pueblo Real Estate with Interaction
    • Use this larger Pueblo real estate data set
    • Build a regression model to estimate selling price
      • You must use 1 interaction variable
      • Experiment with multiple combinations of variables to find the best model, maximizing R^2 and the F Statistic
    • Use your regression model to estimate selling prices for these 4 hypothetical properties
      • 3 BR, 2 BA, 1600 SqFt
      • 4 BR, 3 BA, 2400 SqFt
      • 3 BR, 1 BA, 1200 SqFt
      • 2 BR, 1 BA, 800 SqFt

Assignment:

  • MLB rebuild
    • Watch this video where I build a MLB model in Excel
    • Rebuild your 2013 MLB model in Excel (or if you’ve been working in Excel, rebuild in Sheets)
    • Repeat with your 2025 MLB data
    • Attempt to use an Interaction variable with the MLB data (either 2013 or 2025 model)
More

Lesson 5: Multiple Regression Variable Selection

Review:

  • Intro to Multiple Regression

Presentation:

  • Variable Selection Procedures
  • Modeling Objective:
    • Achieve highest R^2 possible
      • For some data this may be below 0.50
      • Don’t sacrifice too much significance for small gains in R^2
    • Achieve high F-statistic
      • Fewer variables = generally preferable (keep it simple)
      • Significance of F (p-value) should be <0.05, lower is better
      • Individual variables should have p-value < 0.05
      • Occasionally it’s appropriate to make exceptions
    • Find a good balance between F and R^2

Activity:

More

Lesson 4: Intro to Multiple Regression

Review:

  • Food commodity research
  • Time Series Analysis

Presentation:

  • Intro to Multiple Regression
  • Regression Output Interpretation
    • t-statistics
    • p-values
    • R² measures “goodness of fit” (explains % of variance)
    • p-value measures “significance” (probability of result by chance)
    • rank by significance

Activity:

  • Use the “XL Miner” Add-on
  • Complete Problems 1 and 2 using Sheets data
    • Prob 1: Use “y” as dependent variable, use “x1”, “x2” and “x3” as independent variables
    • Prob 2: Use “Dividend Payout” as dependent variable, use “Insider Ownership” and “Debt Ratio” as independent variables
    • Highlight R-squared, coefficients, t-statistics and p-values
    • Write the linear equation for y-hat
    • List, in rank order, the variables from most significant to least significant
  • Run Multiple Regression using East Pueblo RE Sales 2014-2016
    • Use “Selling Price” as dependent (y) variable, use “Bedrooms”, “Bathrooms”, “Total SqFt”, and “Days on Market” as independent (x) variables
    • Highlight R-squared, coefficients, t-statistics and p-values
    • Write the linear equation for y-hat
    • List, in rank order, the variables from most significant to least significant
More