Excel Project: Estimating Beta for GameStop Corp (GME). Due Time:

Excel Project:  Estimating Beta for GameStop Corp (GME). 

Due Time: Please have your excel file uploaded via Canvas under this page by Midnight of May 15, 2022  Sunday). For more instruction: please refer to the Week 7 Lecture Video clips (CH 13 – Part 2  Time: 31:50 mins); Or the following Youtube Video clip will give you the same info: https://youtu.be/vr1lQeKX8Mc (Links to an external site.)

Following are the steps you can take to complete your project. 

  1. Collect historical GameStop price data.

You will begin by collecting historical stock price data for the company.  Historical stock prices are available on the internet through at the http://finance.yahoo.com (Links to an external site.) website.  At this website you will put the ticker symbol (GME) for GameStop in the symbol box and press “go”.  At this point you will see a page that has financial information about your company.  On the left-hand side of the screen there will be a series of option which you can choose to get more information about your company.  At this point you want to look under “quotes” and choose “historical prices”. 

At this point, you will be given some options about the time period and frequency of the data you are collecting.  You want monthly data beginning January 1, 2017 and ending Apr 30, 2022.  Once you have specified this time period, push the “get prices” button.  After the historical price information appears, you will see an option “download to spreadsheet” at the bottom of the historical price table.  Select this option to bring the information into an excel file.

2. Collect historical S&P500 data.

We would like to compare the activity of an individual company’s stock price to how stock prices are doing in the market overall.  We will use the S&P500 as our benchmark for overall stock market performance.  Just as we gathered historical stock prices, we can gather historical S&P500 data using the http://finance.yahoo.com (Links to an external site.) website.  To do this, use the same procedure as above, but instead of using your company’s ticker symbol, type  “^GSPC” in the symbol box.  Collect monthly S&P500 data for the January 1, 2017 and ending Apr 30, 2022 time period and save the information in an excel file.

Now that you have the information in an excel file, you can use your excel skills to manipulate it as you need.  We will be using the “adjusted close” column for both of your company stock and S&P500.

3. Calculate monthly returns.

Monthly returns report gains or losses in percentage terms.  For example, we will look at our sample company Valero Energy, VLO.  Assume that I bought VLO stock at the beginning of January for $23.09 per share.  If I wanted to sell the same share one month later, at the beginning of February, I would have been able to sell it for $22.76.  Thus, I would have lost $0.33.

Looking at percentage returns gives us an ability to compare how poorly (or well) this VLO investment did relative to other investments.  For example, let’s take a hypothetical company, Roadrunner Enterprises.  Assume that Roadrunner’s stock was selling for $200 a share at the beginning of January.  By the beginning of February, the stock was selling for only $199. If you had bought one share of Roadrunner stock over this time period, you would have lost $1.00.  In absolute (dollar) terms, you would have lost more on the Roadrunner investment.

However, looking at percentage returns, we get a different (and more accurate) picture.

VLO Returns:

                (P2-P1)/P1 = (22.76-23.09)/23.09 = – 0.01429 or -1.429%

Or P2/P1 -1 =22.76/23.09 – 1 = -1.429%

Roadrunner Returns:

                 (P2-P1)/P1 = (199-200)/200 = -0.005 or -0.5%

This allows us to compare different investments relative to the amount of money we have invested in the stock.  For example, if we had had $10,000 at the beginning of January and invested it all in VLO, we would have lost ($10,000)*0.01429 or $142.90.  If, instead, we had invested the money in Roadrunner, we would have lost only ($10,000)*.005 or $50.

Using excel, calculate monthly returns for both your stock and the S&P500 for each month beginning January 1, 2017 and ending Apr 30, 2022.   Calculating these returns will require the “Adjusted Close” for each month-end from beginning January 1, 2017 and ending Apr 30, 2022. 

4.  Manipulate Data and Regression Analysis.

There are several methods for calculating a regression in excel.  For this assignment, we will use the charting capability to calculate beta for our stock.  You will begin by choosing the “insert” option above the excel spreadsheet.  You will choose “chart” and then “XY (scatter)” for the type of chart.  The chart wizard will guide you through the process.

You will want to choose the “series” option to tell excel what data you want to use.  Be sure to use your S&P500 return column for your “X value” and your stock return column for your “Y value”. 

This process will give you a scatter graph that plots your stock’s return and the S&P500 return.  However, it does not give you a regression line or slope.  To get a regression line, you will need to click your left mouse key to make several of the data points turn yellow.  With your mouse pointing to one of these yellow data points, right click your mouse.  Choose “add trendline”.  We will use the default (linear) trend line.  However, you will want to choose “options” so that you can place “display equation on chart”.

You may find that your regression equation is difficult to read if it is placed over the data points.  You can move this equation to the side of the graph (it is in a text box) so that it will be easier to read.

Make sure that you have properly labeled your graph and that your graph has a title.

Share This Post


Order a Similar Paper and get 15% Discount on your First Order

Related Questions

Prepare: The initial post in this discussion must be informed

 Prepare: The initial post in this discussion must be  informed by the required material for this discussion. Your preparation  should focus on three classical ethical theories that are fundamental in  any ethical analysis: utilitarianism, deontology, and virtue ethics.  Since the subject of your examination is the Starbucks case, make sure

UJ Symptomatic Impacts & Effects of Substance Abuse Nursing Assignment Help

research and then communicate through a presentation the following 2 elements for the chosen health topic are Substance abuse ( students or families)..The presentation should include a minimum of one slide for each element: Element*****Symptomatic impact of the health issue for individuals Element****** Impact of the health issue on academic

Benefits of PHRs Discussion Nursing Assignment Help

I’m stuck on a Health & Medical question and need an explanation. The military and the VA have been leaders in promoting PHRs. Why would PHRs be especially advantageous to these populations? Or, how can PHRs cut healthcare costs? Or, what about the patients who are at a disadvantage, those

How does prejudice and bias affect human services and the

 How does prejudice and bias affect human services and the delivery of these services to diverse populations? Please use examples from your ebook chapters to illustrate your discussion. What are some ways human services providers can remedy these situations and protect agencies/clients from bias? You must support your response with

Complete Parts 4 and 5 of your strategic plan. Note

Complete Parts 4 and 5 of your strategic plan. Note that you only need to submit these as part of the final completed plan. See Attached Parts 1 – 3 Part 4: Implementation 1. Write a 700- to 1,050-word report describing how you plan to implement your strategic plan. In your

Effective leaders must remain current within the early childhood field.

 Effective leaders must remain current within the early childhood field. Leaders need to understand changes and opportunities within early childhood education, as well as the influences of and fluctuations within the broader societal context. There are numerous resources available within the field to support leaders in remaining current; however, leaders

Music and Extra-Musical Forces

Music and Extra-Musical Forces—The Baroque saw some of the first works of program music (instrumental music depicting an extramusical idea, character, place, or story) and most famously a series of four concertos by Vivaldi called the Four Seasons. Chapter 4 discussed the first movement of the Spring Concerto. For this

Discussion Board Assignment-only one due date and one response, no

Discussion Board Assignment-only one due date and one response, no response to peer required The nursing manager of your department is starting to do the yearly employees evaluations. She has told everyone that she will ask you to do a self-assessment of your informatics/computer competencies. You would like to find

The Process of Two-Way Communication . . . . How

  The Process of Two-Way Communication . . . .  How will you keep the lines of communication open when a student disagrees with your grading? What is your approach? How do your own non-verbals send a message? What does the tone of your voice ‘say’ to the student? How

Refer back to the interview and evaluation you conducted in

  Refer back to the interview and evaluation you conducted in the Topic 2 Family Health Assessment assignment. Identify the social determinates of health (SDOH) contributing to the family’s health status.  Describe the SDOH that affect the family health status. What is the impact of these SDOH on the family?

What components necessary for an e-commerce business-to-consumer site

Description These are the assignment requirements What features of the different languages you’ve seen do you like best? Are all those features in the same language? Discuss why there are so many different languages, all with the same underlying approach. Why isn’t there just one “right” language? Compare the software

Access to Clean Water in Africa

Description You are a global health consultant hired by USAID to make evidence-based recommendations on the development of a program to address the critical health problem of access to clean water in Africa. You may select the country context. Section 1: Problem Description You must first describe the problem using

For this assignment we will use the Boston Public Library’s

For this assignment we will use the Boston Public Library’s Atlascope website (https://atlascope.leventhalmap.org) to investigate Boston’s social justice history. The goal is to get you to look closely at a few specific sites in the city, collect some data based on your observations, and begin to think about what that

In a conversation with a family member, you are told:

  In a conversation with a family member, you are told: “Bad people do bad things. Some people are just bad eggs.” For this discussion, you will incorporate what you have learned about antisocial behavior and its implications for promoting diversity, equity, and inclusion. First, title your post “Antisocial Behavior.”

The management team of PVSS is concerned that the team

The management team of PVSS is concerned that the team may be trying to audit too many areas and that things may be missed. They are also concerned that PVSS will not be able to finish solving all of the issues in a timely manner. With this in mind, they

Early learning experience and Development of the whole child

Go to FSCJ Online Library and Learning Commons Click Journals A-Z Choose YC Young Children Select Full Text Online option Choose a specific Year, Volume, and Issue to research your topic Click “Search” button Each student will review two peer-reviewed articles on one or two of the following areas: Development

Assignment 3 Cyril Potter College of Education Home To School

 Assignment 3 Cyril Potter College of Education Home To School Transition (EDU 112) Anthology of Transition Songs and Rhymes (40 marks) Assignment Type: Individual Title: Making Smooth Transitions Transitions occur not only from one setting to another, but also within the setting from one activity to another. Teacher candidates are