Statistics for Business and Economics

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

Instructions/Syntax for Term Paper Portfolio Analysis

In this project, you will apply the tools learned in EC-300 to a financial portfolio of your selection to determine whether it would be a good purchase.  These need to be done on PC’s; MAC’s do not have sufficient capabilities for performing advanced statistical testing.  This is a good learning lesson for students that they should become accustomed to; the ‘real world’ uses PC’s, not MAC’s.

In this project, you will make the following calculations:
●    Mean
●    Variance
●    Standard Deviation
●    Z-Score and Associated Probability
●    Confidence Interval
●    One Sample Hypothesis Test
●    Two Sample Hypothesis Test

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

Scenario

Suppose that you work for a consultancy group that provides advice on the stock market.  A client has asked you to provide an analysis of a particular stock for his/her company (you are actually going to pick the stock, see below).  What you will do is amass data from a financial website, utilize the various statistical techniques from the course, and author a ‘financial report’ no shorter than seven pages but no longer than twelve (double spaced, Times New Roman, size 12 font) that contains the statistical analysis you have performed, why you would perform that specific test, and your recommendation for the client.  In particular, the report should discuss:

●    Why you selected the particular company
●    Financial and non-financial characteristics of the company
●    How you calculated the various statistics and what they are
●    The significance of the statistics (both numerically and what relevance the test has)
●    Conclusions based on research – Is the company a good one to purchase
FAQ’s/Concerns

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

1.    Do I have to submit my excel work with the paper? No.
2.    Are there any successful examples of this project? Yes, look on blackboard.
3.    Do you want me to describe what I did step by step? Absolutely not. You are to discuss the relevance and significance of each statistical test and how it applies to your particular scenario.  Then, you are to analyze what that test and its results say about your particular company.
4.    What do you mean by significance of statistical tests?  You need to discuss under what conditions and/or scenarios you might think to apply that particular test. In other words, discuss why is it relevant to the world.  Simply stating “I decided to run a confidence interval…” is not acceptable; that does not actually happen.  Instead, you would decide to run, say, a confidence interval because you “wanted to create a range of value likely to contain the population mean…”.  Get into the liberal arts aspects of statistics where you become aware of when and why a specific tool should be used as well as what its limitations are.

Instructions for Data Collection

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

1.    Pick a company that you are curious about/really like; you are going to work with this company’s stock for the next several weeks so make sure it is something you are interested in.
2.    Go to Google.com and type in the company’s name, followed by “Ticker Symbol.”  For example, if you wanted to look up Apple, you would type in “Apple Ticker Symbol”, which would come up at AAPL.  The ticker symbol is the company’s identification code on the various financial markets.
3.    Go to Morningstar.com.  Where it says “Quote”, type in the ticker symbol.  The company’s profile should then appear.
4.    Click Performance, which is located in the toolbar in the middle of the screen.
5.    Click Price History, which will be right underneath Performance.
6.    Underneath “Historical Prices”, it should say “Date Range” and “Frequency”.  For “date Range”, click on 1Y (stands for One Year) and under “Frequency” click on “daily”.
7.    To the right of where it says “Frequency”, click on “Export”.  This will download the data into Excel, which is the program we will be using to make calculations.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

Organizing the Data

1.    Once the data has been downloaded into Excel, hold down on the “CTRL” button and click on Columns B, C, D, and F.
2.    In this order, hit the buttons “Alt”, then the letter “E”, then the letter “D”, then “Enter”.  You should be left with only the Dates in Column A, and the Column that says “Close” (which is the price of the stock when the market closed) in Column B.

The Arithmetic Mean

1.    In Cell D3, type in “Arithmetic Mean” and hit “Enter”.  Then type in “=average(” and use the Arrow Directional Keys to move the cursor over to Cell B3.
2.    Once you have done that, hold down the “Shift” key while you hit “End” (it’s over by the “Backspace” key), and then hit the “Down” arrow.  Type in a “)” (the symbol for a Closed Parenthesis), and hit “Enter”.  That is the average price of the stock.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

The Standard Deviation

1.    In Cell E3, type in “Population Standard Deviation” and hit “Enter”.  Then type in “=stdev.s(” and use the Arrow Directional Keys to move the cursor over to Cell B3.  Once you have done that, hold down the “Shift” key while you hit “End” (it’s over by the “Backspace” key), and then hit the “Down” arrow.  Type in a “)” (the symbol for a Closed Parenthesis), and hit “Enter”.  That is the population standard deviation of the price of the stock.
2.    It is the number that corresponds to the Greek Letter sigma.

Variance

1.    In Cell F3, type in “Population Variance” and hit “Enter”.  Then type in “=var.s(” and use the Arrow Directional Keys to move the cursor over to Cell B3.  Once you have done that, hold down the “Shift” key while you hit “End” (it’s over by the “Backspace” key), and then hit the “Down” arrow.  Type in a “)” (the symbol for a Closed Parenthesis), and hit “Enter”.  That is the population variance of the price of the stock.
2.    It is the number that corresponds to the Greek Letter sigma squared.

Assignments

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

1.    Now that you have the stock’s return and mean, standard deviation, and variance of its price, we have the required data to perform various statistical tests as we learn them.
2.    Z-Scores and Probability
a.    Click on the Little Tab in the Bottom Left corner of Excel that says Sheet 2.  Double Click on it, Delete “Sheet 2” and Rename it as ‘Prob Calculations’.
b.    Go back to your first Sheet, Click on Cell B1.  Next, while holding down the ‘Shift’ button, hit the ‘End’ Key, then hit the arrow key that points down.  Your column should be highlighted in blue now.  Hit CTRL and ‘C’ at the same time, to copy, then click on your new ‘Prob Calculations’ tab (bottom left), then click on Cell A1, and Hit CTRL and ‘V’ at the same time to paste the prices into a new sheet.
c.    We are going to determine three cases of probability: Case 1, Case 2, and Case 3.
i.    Before we begin, copy over your mean and standard deviation from the first worksheet and Enter them in Cells B1 and B2.  The mean should be in the first worksheet’s Cell D4 and the Standard Deviation should be in the first worksheet’s Cell E4.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

ii.    Case 1:
1.    We want to find out the probability that your stock’s price will be between the mean and a 5% increase.  To do so, first we need to find out what price corresponds to a 5% price raise.
a.    Place the cursor in Cell C1.  Next, we are going to calculate what the value of the stock will be if it is to rise 5%, such as if you weigh 100 lbs and wanted to know how much you’d weigh if you gained 10% of your weight, the difference being we are not going to weight but instead we are going to do price increases.  To do so, type ‘=1.05*B1’ (but without the quotation marks obviously).  For example, let’s say your mean was 45.02.  Then, in Cell C1, you are going to type ‘=1.05*45.02’, then hit ‘Enter’ (but again, obviously without the quotation marks).  Here in this example I used 45.02. You are instead going to use whatever your mean is from Cell B1- which will not be 45.02.
b.    This new number you obtained is the price the stock would be if it was to rise 5% in value. (Similar to if you weigh 100 lbs and want to know how much you’d weigh if you gained 10% of your weight you’d do 100*1.10.)
2.    Now that you have the value your stock will increase to if it rises 5%, we are going to calculate the probability that it will in fact have a price between its mean and this 5% increase.  To do so, we need to find a z-score.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

a.    Place your cursor in Cell C2.  Hit ‘=C1-B1’ and then hit ‘Enter’.  You have subtracted the mean of the stock’s price from the value it would be if it rose 5%.  This is the same as X-Mu in the Z-score formula.  Next, in Cell C3 hit ‘=C2/B2’ and hit ‘Enter’.  This is your z-score.  It is telling you how many standard deviations your observation of a 5% increase in the value of the stock’s price is from the mean.
b.    To get the probability of this occurring, open up your Z-distribution chart in the back of the book or from the blackboard handout (Looking for the one Labelled Standard Normal Probability Distribution) and find the Area/Probability associated with your Z-score. (If your Z-score is not in the chart [if the z-score is greater than 3.09], then in Cell C4 below enter ‘>0.4990’ and proceed from Part c. below.) That is your answer; it is the probability of the stock’s price being between its mean and a 5% price increase based upon past data.
c.    Enter that probability in Cell C4 and in Cell C5, in Bold Font, enter in the information that above is the probability of Case I happening (so you don’t lose it or for easier reference when you return to it later).
iii.    Case 2:
1.    Next, we are going to find out the probability that your stock’s price will drop by more than 7%.  To do this, like above, we need to identify what price corresponds to a 7% drop of the stock’s price.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

a.    In Cell D1, type in ‘0.93*B1’ and hit ‘Enter’ (multiplying something by 0.93 is the same as losing 7% of value since 0.93+0.07=1).  This is the price that the stock would have to fall to if it was going to lose 7% of its value.
b.    Next, in Cell D2, type ‘D1-B1’ and hit ‘Enter’. That is the difference between the price of the stock if it dropped 7% and its current average.
c.    Next, in Cell D3, type ‘=D2/B2’. That is the z-score corresponding to the price of the stock falling 7%.
d.    Next, open up your Z-distribution chart in the back of the book or from the blackboard handout (Looking for the one Labelled Standard Normal Probability Distribution) and find the Area/Probability associated with your z-score. Enter it in Cell D4.  (If your Z-score is not in the chart [if the z-score is greater than 3.09], then in Cell D5 below enter ‘<0.001’ and proceed from Part f. below.)
e.    Finally, to get the answer, in Cell D5, type ‘0.5-D3’ and hit ‘Enter’. This is your answer; it is the probability that the stocks’s price will drop 7% or more.
f.    In Cell D6, in Bold Font, enter in the information that above is the probability of Case II happening (so you don’t lose it or for easier reference when you return to it later).
iv.    Case 3:
1.    Next, we are going to find out the probability that your stock’s price will drop by more than 3% or rise by 4%.  To do this, like above, we need to identify what price corresponds to a 3% drop and a 4% rise of the stock’s price. This will require 2 different z-scores.
a.    Start in Cell E1 and type ‘0.97*B1’ (If the stock’s price loses 3% of its value it will still have 0.97 of its value since 0.97+0.03=1). Hit ‘Enter’. This is the price it would be if the stock lost 3% of its value.
b.    Next, in Cell E2, type ‘E1-B1’ and hit ‘Enter’.
c.    Then, in Cell E3, type ‘E2/B2’. That is the z-score corresponding to the price of the stock falling 3%.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

d.    Next, open up your Z-distribution chart in the back of the book or from the blackboard handout (Looking for the one Labelled Standard Normal Probability Distribution) and find the Area/Probability associated with your z-score. Enter it in Cell E4.  (If your Z-score is not in the chart [if the z-score is greater than 3.09], then in Cell E4 below enter ‘0.5’ and proceed from Part i. below.)
e.    Next, we need to find out the same information, but for a 4% raise of the price of the stock.  To do this, start in Cell F1 and type ‘1.04*B1’ (If the stock’s price gains 4% of its value it will have 1.04 of its value since 1+0.04=1.04). Hit ‘Enter’. This is the price it would be if the stock gained 4% of its value.
f.    Next, in Cell F2, type ‘F1-B1’ and hit ‘Enter’.
g.    Then, in Cell F3, type ‘F2/B2’. That is the z-score corresponding to the price of the stock increasing 4%.
h.    Next, open up your Z-distribution chart in the back of the book or from the blackboard handout (Looking for the one Labelled Standard Normal Probability Distribution) and find the Area/Probability associated with your z-score. Enter it in Cell F4.  (If your Z-score is not in the chart [if the z-score is greater than 3.09], then in Cell F4 below enter ‘0.5’ and proceed from Part i. below.)
i.    Next, in Cell E5, type ‘=E4+F4’ and hit ‘Enter’.  This is the combined probability of both of the Z-scores.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

j.    Next, in Cell E5, in Bold Font, enter in the information that above is the probability of Case III happening (so you don’t lose it or for easier reference when you return to it later).

3.    As an FYI, should you encounter problems going forward understanding these directions or if you are unsure of yourself, YOUTUBE.com has videos on how these tests can be done in EXCEL.  All you need to search on YOUTUBE.com is the name of the test follow by the phrase “in Excel”.  For example, to learn how to run a Confidence Interval in Excel on YOUTUBE.com, simply search this website with the entry ‘Confidence Interval in Excel’ and a multitude of videos will be presented to help you.
4.    Confidence Intervals
a.    Now we are going to calculate a 95% Confidence Interval for the stock’s price.
i.    In Excel, click on the Little Tab in the Bottom Left corner of Excel that says Sheet 3.  Double Click on it, Delete “Sheet 2” and Rename it as ‘Confidence Interval’.
1.    Next, return to Sheet 1 with the original Morningstar data and place the cursor in Cell B1, where it says ‘Close’.  While holding down the ‘Shift’ key, hit the ‘End’ key, followed by the arrow that points down.  It should highlight the entire column in Blue.  Hit CTRL and ‘C’ at the same time, to copy, then click on your new ‘Confidence Interval’ tab (bottom left again), then click on Cell A1, and Hit CTRL and ‘V’ at the same time to paste the company’s name and prices into a new sheet.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

2.    Next, we have to make sure your computer is ready to run statistical testing.  In the top left corner, click on ‘File’, then click ‘Options’, then click ‘Add-Ins’, then click on ‘Analysis Tookpak’, then click OK.
3.    Then, once again, click ‘File’, then click ‘Options’, then click ‘Add-Ins’ and then, in the bottom middle of the pop-up box, next to where it says ‘Manage Excel Add-Ins’, click ‘Go’.
4.    A new screen should pop up and the top of it should say ‘Analysis Toolpak’ with a check-mark next to it.  Make sure that there is a check-mark next to it (there most likely already is), then click OK.
5.    What you just did was activate an additional data tool software package in Excel that MAC’s do not come with nor are compatible with.  Now let’s run the Confidence Interval.
ii.    Click on Cell A1.  Delete the word ‘Close’ and instead replace it with the name of your company.  This will come in handy later with other statistical testing.
iii.    Place your cursor in Cell C3.
iv.    To run the Confidence Interval, in the toolbar at the top click on Data, then Data Analysis, and then scroll down and click on ‘Descriptive Statistics’ and hit OK.  A pop-up screen will appear.
v.    Where it says ‘Input Range’, click on Cell A1.  Next, while holding down Shift, hit the ‘End’ key, followed by the arrow key that points down.  This will highlight the whole column with your data.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

vi.      Your data is grouped by columns, so leave that option alone.  Next, click on the button that says ‘Labels in First Row’ since in Cell A1 your data is labelled with the name of the company.
vii.    Next, click on the circle next to ‘Output Range’ and then, immediately after, click in the text box to the right of it.  Scroll up to the top of your spreadsheet (if you’re not there already) and click in Cell C3.  This is where your output chart is going to start.
viii.    Next, check off the box next to ‘Summary Statistics’ in your pop-up box.  Do the same for ‘Confidence Level for Mean’ and change the confidence level to whatever level you want to use.  For our project, we are going to do a 95% confidence level, so simply leave that unchanged/as is.  Finally, hit ‘OK’.  This will run the data package.
ix.    You should now have a rather large data result pop-up box that appeared in your spreadsheet, starting at Cell C3.  Here’s how to get the Confidence Interval.
1.    Now, at the top, place your cursor on the dividing line between Column C and Column D until you see a cursor that is two arrows, with one point to the left and one to the right.  Double click at that point, to expand the columns (FYI that is how you always expand columns.)
2.    To make the confidence interval, you need to calculate the lower bound and the upper bound.  In your data chart, Cell D5 is your mean ( ) and Cell D18 (next to where it says Confidence Level 95%) is the right half of the equation for a confidence interval ( t[ ]).  Thus, to make a confidence interval, click on Cell F18 and type ‘Lower Bound’ then Hit Enter.  This will put you in Cell F19 and type ‘Upper Bound’ there.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

3.    Next, click on Cell G18 to put the cursor there.  Type in ‘=d5-d18’ and hit ‘Enter’.  That is the lower bound of the Confidence Interval.
4.    Next, click on Cell G19 to put the cursor there.  Type in ‘=d5+d19’ and hit ‘Enter’.  That is the upper bound of the Confidence Interval.
5.    You now have both the lower and upper bounds of a Confidence Interval.  You’re done.
5.    One Sample Hypothesis Test
a.    Now we are going to run a one sample hypothesis test in Excel.  Unfortunately, Excel does not strictly perform a one sample hypothesis test.  Instead, what we are going to do is run a two sample hypothesis test and ‘trick’ it into thinking it is doing a one sample hypothesis test.  Might sound complex, but this is actually quite simple.
b.    In the bottom left of your spreadsheet, next to the tab that says ‘Confidence Interval’, there is a little tab to its right with an orange color coming out of a spreadsheet. That is the icon for adding a new worksheet.  Click on it, double click on where it now says ‘Sheet 4’, delete that, and rename it as ‘One Sample Hypothesis Test’.  If that doesn’t fit then just choose something you’ll remember.
c.    Now, click on the tab for your Confidence Interval, and click on Cell A1, which is where your company’s name should appear and your data should start below.  After clicking on A1, while holding down ‘Shift’, hit the ‘End’ key, then the arrow key pointing down.  This should highlight the entire column in blue.  Hit CTRL and ‘C’ at the same time, to copy, then click on your new ‘One Sample Hypothesis Test’ tab (at the bottom left again), then once in the new tab, click on Cell A1, and Hit CTRL and ‘V’ at the same time to paste the company’s name and prices into a new sheet.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

d.    Now, we are going to test whether it is reasonable to expect that the ‘inherent’ or ‘fundamental’ price of the stock could be 3% more than its current average.  Use this time to think about what your null and alternative hypotheses will be, as well as if you have a 1 or 2 tailed test.
e.    To do so, we need to find out what 3% more of its current average actually is.
i.    To calculate this, go back to Worksheet 1 and place your cursor in Cell D4, which should contain your stock price’s mean.  Hit CTRL and ‘C’ at the same time, to copy, then click on your new ‘One Sample Hypothesis Test’ tab (at the bottom left again), then once in the new tab, click on Cell D1, and Hit CTRL and ‘V’ at the same time to paste the company’s mean into the new sheet.
ii.    In Cell E1, now we are going to find the price corresponding to a rise of 3% of the price. In Cell E1, type ‘=D1*1.03’ and hit enter (If the stock’s price gains 3% of its value it will have 1.03 of its value since 1+0.03=1.03). This is the value you are going to work with, when attempting to find out whether or not your stock’s intrinsic value can be that number.
iii.    This is the time to make a note of what the null and alternative hypotheses are. What you want to know is whether that number can be the ‘intrinsic’ or ‘fundamental’ value of your stock, not whether it will be more than that value.
iv.    We now need to copy this mean all across Column B (every price in Column A needs to have a matching mean price next to it in Column B.  This is simple to do, just follow the instructions.)

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

v.    To do so,  place your cursor in Cell E1 and Hit CTRL and ‘C’ at the same time, to copy. Now we are going to do a function called ‘pasting special’ (this removes all formulas behind the scenes). After copying, leave the cursor in E1 and hit ‘Alt’, then ‘E’, then ‘S’, then ‘V’. That is pasting special. Now hit CTRL and C once again to copy, then put your cursor in Cell A1. Hit ‘End’, then the arrow key that points down (this time you’re not holding shift!) and it should take you to the bottom of your data. Then hit the arrow key pointing right one time to move your cursor into Column B. Once there, while holding ‘Shift’ (this time you do it), hit ‘End’ and then the arrow key that points up. All of Column B should highlight in blue and you should be at the top of the spreadsheet. If that happens, hit CTRL and then ‘V’. You should paste the same number over and over all the way up Column B.
vi.    Once that works, hit the left arrow key one time, and the blue highlighting should fade away. Scroll up and put your cursor in Cell B1 again. In Cell B1, write in ‘Null Hypothesis Test Number’.
vii.    You should have your original price data in Column A with the name of the company in Cell A1 and in Column B you should have a list of our new price data with each number being the same that has just as many entries as Column A with Cell B1 saying ‘Null Hypothesis Test Number’.
viii.    Next, we have to make sure your computer is ready to run statistical testing.  In the top left corner, click on ‘File’, then click ‘Options’, then click ‘Add-Ins’, then click on ‘Analysis Tookpak’, then click OK.
ix.    Then, once again, click ‘File’, then click ‘Options’, then click ‘Add-Ins’ and then, in the bottom middle of the pop-up box, next to where it says ‘Manage Excel Add-Ins’, click ‘Go’.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

x.    A new screen should pop up and the top of it should say ‘Analysis Toolpak’ with a check-mark next to it.  Make sure that there is a check-mark next to it (there most likely already is), then click OK.
xi.    What you just did was ensure that your additional data tool software package in Excel was activated.  FYI, MAC’s do not come with nor are compatible with this tool.  Now let’s run the Hypothesis Test.
xii.    Now click on Cell D4.  This is where we will ultimately put the Output table.
1.    At the top of the toolbar, Click on Data, then click Data Analysis.  Scroll down and select ‘T-Test: Two Sample Assuming Unequal Means’.
a.    Click on the text box next to ‘Variable 1 Range’ and then place the cursor in Cell A1.  While holding down ‘Shift’, hit the ‘End’ button, followed by the arrow key pointing down.  This should highlight the entire column in blue.
b.    Next, click on the text box for ‘Variable 2 Range’ and then place the cursor in Cell B1.  While holding down ‘Shift’, hit the ‘End’ button, followed by the arrow key pointing down.  This should highlight the entire column in blue.
c.    Next, where it says ‘Hypothesized Mean Difference’, type in 0.  Additionally, click the box next to labels because in Cells A1 and B1 you do have the names of the company and the words ‘Null Hypothesis Test Number’.
d.    Next, leave alpha as 0.05.
e.    Next, click the circle next to the words ‘Output Range’ and then click inside the textbox to the right of those words.  Click on Cell D4 which is where we will start our t-test readout sheet.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

f.    Click OK.
g.    This will make the test results appear. Here’s how to read it:
i.    In Cell D12 your test statistic will appear.
ii.    In Cell D13, you will have your P-value for a 1 tailed test.
iii.    In Cell D14, you will have your critical value for a 1 tailed test.
iv.    In Cell D15, you will have your P-Value for a 1 tailed test.
v.    In Cell D16, you will have your critical values for a two tailed test.
vi.    It is up to you to decide whether you have a one or a two tailed test.  Remember, you are looking for whether the ‘Null Hypothesis Test number’ can possibly be the ‘intrinsic’ or ‘fundamental’ value of your stock, not whether it will be more than that value.
vii.    Upon doing this, you need to decide whether to reject or fail to reject the null hypothesis and state a conclusion of your one sample hypothesis test.
6.    Two Sample Hypothesis Test
a.    Now we are going to run a two sample hypothesis test in Excel (which we technically did above but are going to run it for two companies).  What we want to find out is whether your company and a potential rival have the same stock price, not whether one is greater than the other.  Use this time to think about what your null and alternative hypotheses will be, as well as if you have a 1 or 2 tailed test.
b.    In the bottom left of your spreadsheet, next to the tab that says ‘One Sample Hypothesis Test’, there is a little tab to its right with an orange color coming out of a spreadsheet. That is the icon for adding a new worksheet.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

Click on it, double click on where it now says ‘Sheet 5’, delete that, and rename it as ‘Two Sample Hypothesis Test’.  If that doesn’t fit then just choose something you’ll remember.
c.    Go to the previous worksheet labelled ‘One Sample Hypothesis Test’.  Click on the name of your company in Cell A1, and while holding down ‘Shift’, hit the ‘end’ key followed by the arrow pointing down.  This should highlight the column in blue.  Now hit CTRL and ‘C’ to copy the data, click on the Tab for your new ‘Two Sample Hypothesis Test’, and place your cursor in Cell A1.  Hit CTRL and ‘V’ to paste the data into the new worksheet.
d.    Next, we need to get data on another company from Morningstar again.  Pick a company that is similar to the one you just chose, such as a potential rival.
i.    Go to Google.com and type in the company’s name, followed by “Ticker Symbol.”  For example, if you wanted to look up Apple, you would type in “Apple Ticker Symbol”, which would come up at AAPL.  The ticker symbol is the company’s identification code on the various financial markets.
ii.    Go to Morningstar.com.  Where it says “Quote”, type in the ticker symbol.  The company’s profile should then appear.
iii.    Click Performance, which is located in the toolbar in the middle of the screen.
iv.    Click Price History, which will be right underneath Performance.
v.    Underneath “Historical Prices”, it should say “Date Range” and “Frequency”.  For “date Range”, click on 1Y (stands for One Year) and under “Frequency” click on “daily”.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

vi.    To the right of where it says “Frequency”, click on “Export”.  This will download the data into Excel, which is the program we will be using to make calculations.  This should open up a new spreadsheet, which we will not be using for long.
1.    Place your cursor in Column E in the new file where it says ‘Close’.  While holding down ‘Shift’, hit the ‘end’ key followed by the arrow pointing down.  This should highlight the column in blue.  Now hit CTRL and ‘C’ to copy the data, and return to the file we have been working with and made calculations in.  Once there, click on the Tab for your new ‘Two Sample Hypothesis Test’, and place your cursor in Cell B1.  Hit CTRL and ‘V’ to paste the data into the new worksheet.  In Cell B1, delete the word ‘Close’ and replace it with the name of the second company you chose.
vii.    Now we will run a Two Sample Hypothesis Test, but first we must make sure your computer’s software package is ready to run.
1.     In the top left corner, click on ‘File’, then click ‘Options’, then click ‘Add-Ins’, then click on ‘Analysis Tookpak’, then click OK.
2.    Then, once again, click ‘File’, then click ‘Options’, then click ‘Add-Ins’ and then, in the bottom middle of the pop-up box, next to where it says ‘Manage Excel Add-Ins’, click ‘Go’.
3.    A new screen should pop up and the top of it should say ‘Analysis Toolpak’ with a check-mark next to it.  Make sure that there is a check-mark next to it (there most likely already is), then click OK.
4.    What you just did was ensure that your additional data tool software package in Excel was activated.  FYI, MAC’s do not come with nor are compatible with this tool.  Now let’s run the Hypothesis Test.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

5.    Now click on Cell D4.  This is where we will ultimately put the Output table.
a.    At the top of the toolbar, Click on Data, then click Data Analysis.  Scroll down and select ‘T-Test: Two Sample Assuming Unequal Means’.
i.    Click on the text box next to ‘Variable 1 Range’ and then place the cursor in Cell A1.  While holding down ‘Shift’, hit the ‘End’ button, followed by the arrow key pointing down.  This should highlight the entire column in blue.
ii.    Next, click on the text box for ‘Variable 2 Range’ and then place the cursor in Cell B1.  While holding down ‘Shift’, hit the ‘End’ button, followed by the arrow key pointing down.  This should highlight the entire column in blue.
iii.    Next, where it says ‘Hypothesized Mean Difference’, type in 0.  Additionally, click the box next to labels because in Cells A1 and B1 you do have the names of the companies
iv.    Next, leave alpha as 0.05.
v.    Next, click the circle next to the words ‘Output Range’ and then click inside the textbox to the right of those words.  Click on Cell D4 which is where we will start our t-test readout sheet.
vi.    Click OK.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

vii.    This will make the test results appear. Here’s how to read it:
1.    In Cell D12 your test statistic will appear.
2.    In Cell D13, you will have your P-value for a 1 tailed test.
3.    In Cell D14, you will have your critical value for a 1 tailed test.
4.    In Cell D15, you will have your P-Value for a 1 tailed test.
5.    In Cell D16, you will have your critical values for a two tailed test.
6.    It is up to you to decide whether you have a one or a two tailed test.  Remember, you are looking for whether the two companies have the same average stock price, not whether one is more than the other.
7.    Upon doing this, you need to decide whether to reject or fail to reject the null hypothesis and state a conclusion of your two sample hypothesis test.
7.    You’re done! Let’s Type it up (if you haven’t as you’ve been doing these tests)!!  See above on page 1 for guidelines on how to do this and how a paper like this should be structured.

Statistics for Business and Economics

Order 100% Plagiarism Free Essay Now

By admin in Essays on August 6, 2017