Management 285 - STATS II

Business Problem: Stock Analysis and Allocation

Term Project – Due: April 10th, 2025 @ midnight

Goal: To develop analytical skills for practical analysis of capital markets in pursuit of economic indicator correlation and risk management when it comes to asset allocation.

Method: Using Microsoft Excel as our main project software, we will be using its data analytics tools and also their spreadsheet to conduct all parts of this project.

There will be many steps to this running project. Our goal as a class will be to complete each of the steps in 1-2 week increments and by April 10th have a project that is completed and ready for presentation and submission.

 

STEP 1 - Collect all of the data for the indicators below. Then run a multiple regression analysis numerous times to pick three economic indicators that correlate to indexes that have been provided below.

Use the process that we have been developing in class, through the global test and the individual regression tests.

To finish this step - please find all of the sectors that make up the S&P 500.

 

Economic Indicators:

Data Resources:

Most of the raw data can be found in a basic search with Google, or on the Federal Reserve economic data website (FRED)**

https://www.economy.com/united-states/indicators

Retail Sales Figures

Building Permits & Housing Starts

Level of Manufacturing Activity (PMI - purchasing managers index)

Inventory Balances

Incomes & Wages Growth

Unemployment Rate

Real GDP (Quarterly Data)

CPI (consumer price index)

Interest Rates (

5 year

government bond)

Corporate Profits

Your task? To collect the data for the fiscal year 2021, 2022, 2023, and 2024 input it into Excel and run a multiple regression numerous times to find the three economic indicators/variables that correlate the most to the S&P 500 index below.

Reminder:

-make sure you calculate the percentage changes of your variables**

-for the quarterly data, use the same data point for 3 months consecutively.

Benchmark Indices:

S&P 500 (

Broadbased

US Index)

Other indices if you wish, must be approved.

 

 

 

 

 

 

 

STEP 2 - The beginning of this step will be to complete a correlation matrix to help aid you through the process of eliminating independent variables and also making sure that your final 3 independent variables are not strongly correlated (multicollinearity).

Take your remaining variables from Step 1 (each sector) and dive deeper into the analysis of your index.

1. Do research into what sectors comprise the S&P 500. The general stock market is broken into 11 different sectors: financials, utilities, consumer discretionary, consumer staples, energy, healthcare, industrials, telecom, technology, materials, real estate.

 

2. Find 8 ETFs (exchange traded funds) that represent the S&P 500 index. There are 11 sectors within the S&P, so there will be 3 left out. Find the data (closing prices) of how they performed at the beginning of each month - aligned with the data you have for your variables from the previous step. You’re going to want to create a table in a new spreadsheet for this step. You’ll need the monthly closing prices for the last three years, similar to the first step.

Here below are a list of ETFs for every sector in the general stock market:

https://seekingalpha.com/etfs-and-funds/etf-tables/sectors?utm_source=google&utm_medium=cpc&utm_campaign=14049528666&utm_term=127926794296^aud-1457157706519:dsa-1427142718946^^547566878389^^^g&external=true&gclid=CjwKCAiAvaGRBhBlEiwAiY-yMIMCU9kMYJX3C115aNH-2xfd6ah4hG15iOpi2JA9DOcMos3x1fZtwRoCs2cQAvD_BwE

 

3. List the best performing ETFs in order of best performing to worst performing over the past year to give you an idea of which sectors performed the best. From there we are going to start comprising our portfolio by selecting equities from the top 5 performing sectors. You can determine which ETF is performing best by calculating the percentage change from January (first data point) - December (last data point).

 

**Percentage change = ((Current Price (most recent price) - Base Price (Jan 2021 price) / Base Price )) * 100.

 

4. Choose THREE equities from the top 5 ETFs - they have to be a company in the chosen ETF - and run a single regression for each equity where the dependent variable (y variable) is the individual equity and the independent variable is the S&P 500 index. List them from most correlated to least correlated within each sector. ie) 3 equities and 5 ETFs, you need to run 15 single regressions.

 

STEP 3 - Now that we have the best performing sectors and have the most correlated equities from those ETFS - step 3 is going to look at the variation among those ETFs and also the BETA in the equities you have run regressions for. You should have a separate column for each equity that displays the beta and the standard deviation.

**Remember that Beta is simply the B coefficient from your regression.

1. You now have the ETFs of each sector ranked from best performing to worst performing. Use the 5 ETFS and run an ANOVA to see if there is a significant difference between the best performing sectors and the worst. Please use 0.05 for your ANOVA significance level.

**For those that are running ANOVA tables make sure to use the actual prices for the data rather than the percentage changes. Using percentage changes does not work well for ANOVA data.

 

 

STEP 4

· Was there any significant difference in the ETF variation from the ANOVA test?

· If there was a difference between means, you will weigh the four sectors differently rather than equally. More will be explained below:

Step 4 will consist of you creating 3 different “portfolios”, you can also think of them as mini mutual funds. You are going to build a defensive, conservative, and aggressive portfolio, based around beta and correlation based on 12 of the 15 stocks that you have collected information for in step 3.

Because all of you have chosen different stocks may have different variation (beta, standard deviation) numbers, how you will gage the thresholds for the three above portfolios will be RELATIVE - meaning that your aggressive portfolio will have greater values than your conservative, and the same goes for the conservative portfolio over the defensive. Please refer to me if you are having a tough time understanding this.

 

In class we introduced a template that you were to fill out after you have completed this step. The goal being that you find 4 stocks for each “portfolio” representing a different style of portfolio. The aggressive portfolio should have high beta, high standard deviation, and high correlation. The defensive portfolio should have low beta, low variation, and low correlation.

***The stocks provided are random stocks simply to be provided as an example, not for you to use.

I have filled the template (formulas) out only for the first portfolio. You should be able to see how I completed the formulas, and you will need to fill out the final 2. The four different colors represent the sectors that need to be represented in your selections.

· If your ANOVA table resulted in not equal means, then you will weight the sectors higher in terms of which ones performed the best in the past 2 years.

· If there is no difference then you will weigh the sectors equal.

In the above points we are not referring to sector representation, but rather dollar allocation of each individual stock. You will still have 3 stocks from each sector, but how much of your 1,000,000 dollars you have allocated to each stock will completely change the weighted and the average beta, standard deviation, and correlation of each portfolio.

STEP 5

This step will require your group to do some research. Before we get into the next step we need to try and figure out what the US economy is going to look like in the next 2 years. For most people, investing is a long-term game. The portfolio we are going to choose is going to account for a long-term timeline. You and your team will need to investigate the long-term prospects for the United States (think economic factors). Please choose three factors to focus on and write a paragraph response justifying your position for each factor. This will lead into what we will be doing for the final steps. Please put this explanation in a separate spreadsheet in your excel document.

 

STEP 6 (Final Step)

Now that you have decided (with your research) where you believe the economy is going in the long-term. We need to validate your prognosis with what-if analysis. In chapter 17 we learned about decision-making theory. Use your knowledge of EMV (expected monetary value) to predicate the performance of each of your portfolios.

Use the information that you gathered in your previous step to decide on how you believe the market will perform over the next year (ie. 5% up, 5% down, etc.) This is your optimal outcome, but make sure that you have an outcome that represents an adverse scenario (opposite of what you believe). You will also want to associate a probability with that percentage as well! Much like the assignment that we covered in-class. Using your fixed percentage (market prognosis) find a probability where EACH portfolio offers the highest EMV.

Deliverables

· Market performance estimate

· Outcome of the adverse scenario

· Probability of each of the above scenarios

· Calculate the EMVs by taking the beta of the portfolio and multiplying it by the market increase or decrease.

· Find a percentage where EACH portfolio has the highest EMV.