Category: s22-busad360

Lesson 16: Stock Market Simulation

Review:

  • Coin Flip and Dice simulation (discrete)
  • Stock Market Simulation (continuous)
  • Project 3

Presentation:

  • March Madness Challenge
    • BracketOdds
    • Basic Simulation
    • Bracket
    • Extra Credit Opportunity (up to 40 points)
      • Read BracketOdds materials
      • Write a 2-4 page paper explaining the approach/methodology and how it can improve bracket selection
      • Create a bracket and email with paper
      • Due Thu, Mar 17 at 10:30am

Activity:

  • Add a “fixed income” (bonds) component to your stock market simulation
    • Assume bond market returns are normally distributed
      • mean = 7.88%
      • standard deviation = 6.86%
    • Your “portfolio” allocation should be 60% stocks and 40% bonds
    • Stock market and bond market returns should be combined each year and then reallocated in the following year.

Assignment:

More

Project 3: Retirement Portfolio Simulation

Due Fri, Mar 18 at 5pm

Instructions:

  • Build a Stock + Bond Market Simulation
  • Simulation Requirements
    • 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
  • Run 4 simulation scenarios
    • Scenario 1
      • Beginning balance = 10,000
      • Annual contribution = 1,000
      • Stock/Bond allocation = 60%/40%
      • Target = $1,000,000
    • Scenario 2
      • Beginning balance = 10,000
      • Annual contribution = 1,000
      • Stock/Bond allocation = 80%/20%
      • Target = $1,000,000
    • Scenario 3
      • Beginning balance = 3,000
      • Annual contribution = 3,000
      • Stock/Bond allocation = 60%/40%
      • Target = $2,000,000
    • Scenario 4
      • Beginning balance = 3,000
      • Annual contribution = 3,000
      • Stock/Bond allocation = 80%/20%
      • Target = $2,000,000
    • For each scenario, answer the following questions:
      • What is the Max, Min, Mean, Median and Standard Deviation of end balances
      • What is the probability of reaching the target?
  • Prepare a Spreadsheet file
    • You’ll use a Google Sheets file to document and explain your simulation
    • Make it easy to understand and navigate
  • Create a Video
    • You will provide a brief tour of your spreadsheet and explain your simulation and the 4 scenario runs.
    • Your video should be approximately 3-4 minutes in length. It’s okay if it’s a little longer or a little shorter.
    • It doesn’t need to be fancy. Just you speaking clearly and explaining your simulation + results.
    • You can use Zoom or some other screen recording setup OR you can simply use your phone’s video recorder.
    • Recently, some students in other classes have used PowerPoint’s video recording with very good results.
    • You need to appear on camera, at least for a few seconds.
  • Submit links to your Spreadsheet and your Video
More

Lesson 15: Simulation of Continuous Random Variables

Review:

  • Project 2
  • Simulation automation
  • Chuck-a-luck

Presentation:

  • Random Variables
    • Discrete Random Variables
      • Probability distribution table
      • Dice Simulation
      • =RANDBETWEEN()
    • Continuous Random Variables
      • Stock Market simulation
      • Assume normal distribution
      • =norminv(rand(),$B$2,$B$3)

Assignment:

  • Stock Market Simulation in Sheets
  • Modifications
    • Generate a 40-year simulation (you’ll need to modify the automation script)
    • Modify the simulation to add $1,000 to the balance at the end of each year
    • Make a histogram of your ending balances
    • Press run to make your histogram “dance” as it generates new ending balances
More

Lesson 14: Index Variables and Automated Simulation

Review:

  • Simulation
  • Project 2

Presentation:

Assignment:

  • Replicate the “Lucky 7” simulation with automation
    • 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
    • Don’t worry about the financial component
    • Only simulate the game to determine wins and losses
More

Lesson 13: Intro to Simulation

Review:

  • Simulation
    • Intro to Random Numbers
    • Coin flip

Presentation:

  • Simulation
    • More RANDBETWEEN()
    • Rolling 2 dice
    • RANDBETWEEN(1,12) ≠ sum of two dice
    • Histogram comparison
    • Probability distribution
  • Demonstrate Dice simulation in Sheets

Assignment:

  • Create your own Dice Simulation
    • Lucky 7 game
      • Roll a 7, you win!
      • Roll anything else (other than a 7), you lose. 
More

Lesson 12: Intro to Random Numbers

Review:

  • Multiple Regression
  • Project 2
    • Sheets issues
    • Deadline extended to Thu, Mar 3.

Presentation:

  • Probability
    • Topic worthy of its own course
    • Discrete vs Continuous
  • Random Numbers
    • Demonstrate RANDBETWEEN() in Sheets
    • Demonstrate RAND() in Sheets
  • Simulation
    • Individual outcomes are uncertain
    • Distribution of outcomes over many iterations are predictable.
  • Demonstrate Coin Toss Simulation in Sheets

Assignment:

  • Create your own Coin Toss Simulation in Sheets
  • Replicate the chart above but with 100 coin tosses
More

Lesson 11: Applied Regression Modeling (2 of 2)

Zoom Meeting Link: https://csupueblo.zoom.us/j/6114536052

Review:

Presentation:

  • 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 (or subtracted from) the estimate
  • Demonstration with Sample Real Estate data
    • create a dummy variable for a neighborhood

Activity:

  • Use this larger Pueblo real estate data set
  • Build a regression model to estimate selling price
    • You must use 1 interaction variable
    • You must use 1 or more Dummy variables to incorporate neighborhoods
    • Experiment with multiple combinations of variables to find the best model, maximizing R^2 and the F Statistic

Assignment:

More

Project 2: Multiple Regression Modeling

Due Tue, Mar 1 at 11:15am

Instructions:

  • Build a Multiple Regression Model
    • Use this UsedCarsRegression data set
    • Build a regression model to estimate used car selling price
    • Use 1 or more interaction variables
    • Use 1 or more Dummy (indicator) variables
    • Experiment with multiple combinations of variables to find the best model, maximizing R^2 and the F Statistic
  • Estimate Selling Price
    • Use your model to estimate selling price for the following 2 vehicles
MakeModelTrimTypeLiterCylinderCruiseSoundLeatherDoorsOptionsMileage
CadillacDevilleSedan 4DSedan4.68101426,447
BuickLacrosseCXL Sedan 4DSedan3.661004125,245
  • Prepare a Spreadsheet file
    • You’ll use a Google Sheets file to document and explain your model and your price estimates
    • Make it easy to understand and navigate
  • Create a Video
    • You will provide a brief tour of your spreadsheet and explain your regression modeling process and the price estimates.
    • Your video should be approximately 2-3 minutes in length. It’s okay if it’s a little longer or a little shorter.
    • It doesn’t need to be fancy. Just you speaking clearly and explaining your multiple regression model.
    • You can use Zoom or some other screen recording setup OR you can simply use your phone’s video recorder.
    • You need to appear on camera, at least for a few seconds.
  • Submit links to your Spreadsheet and your Video
More

Lesson 10: Applied Regression Modeling (1 of 2)

Review:

  • MLB Baseball model
  • Next video assignment on Thursday, due the following Tuesday

Presentation:

  • Applied Regression Analysis
    • Demonstrate with Sample Real Estate data
    • Multicollinearity definition
    • 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

Assignment:

  • 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
More

Lesson 9: Multiple Regression Variable Selection

Review:

  • Intro to Multiple Regression
  • Project 1 grading

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

Assignment:

  • Produce a model to estimate MLB winning percentage
    • Produce a Final Model, aiming to maximize both R^2 and F-Statistic
    • Be prepared to answer:
      • Which variables will you keep in the final model?
      • Explain your reasoning.
More