EC-300Statistics for Business and EconomicsProfessor RaffertyInstructions/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:MeanVarianceStandard DeviationZ-Score and Associated ProbabilityConfidence IntervalOne Sample Hypothesis TestTwo Sample Hypothesis TestANOVAComparison of Two Population VariancesLinear Regression 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 companyFinancial and non-financial characteristics of the companyHow you calculated the various statistics and what they areThe 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/ConcernsDo I have to submit my excel work with the paper? No.Are there any successful examples of this project? Yes, look on blackboard.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.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 CollectionPick 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.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.Go to Morningstar.com. Where it says “Quote”, type in the ticker symbol. The company’s profile should then appear.Click Performance, which is located in the toolbar in the middle of the screen.Click Price History, which will be right underneath Performance. 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”. 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. Organizing the Data Once the data has been downloaded into Excel, hold down on the “CTRL” button and click on Columns B, C, D, and F.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 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. 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. The Standard Deviation 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. It is the number that corresponds to the Greek Letter sigma. Variance 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. It is the number that corresponds to the Greek Letter sigma squared. Assignments 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.Z-Scores and ProbabilityClick 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’.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. We are going to determine three cases of probability: Case 1, Case 2, and Case 3.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.Case 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. 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. 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.)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.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. 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. 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).Case 2: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.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.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. Next, in Cell D3, type ‘=D2/B2’. That is the z-score corresponding to the price of the stock falling 7%.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.)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.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).Case 3: 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.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. Next, in Cell E2, type ‘E1-B1’ and hit ‘Enter’. Then, in Cell E3, type ‘E2/B2’. That is the z-score corresponding to the price of the stock falling 3%.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.)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. Next, in Cell F2, type ‘F1-B1’ and hit ‘Enter’. Then, in Cell F3, type ‘F2/B2’. That is the z-score corresponding to the price of the stock increasing 4%.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.)Next, in Cell E5, type ‘=E4+F4’ and hit ‘Enter’. This is the combined probability of both of the Z-scores.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). 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.Confidence IntervalsNow we are going to calculate a 95% Confidence Interval for the stock’s price.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’.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.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. 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’. 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.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.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.Place your cursor in Cell C3.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. 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. 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.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.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.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.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.)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. 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.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.You now have both the lower and upper bounds of a Confidence Interval. You’re done.One Sample Hypothesis TestNow 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.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.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.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.To do so, we need to find out what 3% more of its current average actually is. 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.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.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.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.) 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. 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’.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’.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. 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’. 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.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.Now click on Cell D4. This is where we will ultimately put the Output table.At the top of the toolbar, Click on Data, then click Data Analysis. Scroll down and select ‘T-Test: Two Sample Assuming Unequal Means’.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.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.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’. Next, leave alpha as 0.05.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.Click OK.This will make the test results appear. Here’s how to read it:In Cell D12 your test statistic will appear.In Cell D13, you will have your P-value for a 1 tailed test.In Cell D14, you will have your critical value for a 1 tailed test.In Cell D15, you will have your P-Value for a 1 tailed test.In Cell D16, you will have your critical values for a two tailed test.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. 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.Two Sample Hypothesis TestNow 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. 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. 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.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.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.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.Go to Morningstar.com. Where it says “Quote”, type in the ticker symbol. The company’s profile should then appear.Click Performance, which is located in the toolbar in the middle of the screen.Click Price History, which will be right underneath Performance. 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”. 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. 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. Now we will run a Two Sample Hypothesis Test, but first we must make sure your computer’s software package is ready to run. In the top left corner, click on ‘File’, then click ‘Options’, then click ‘Add-Ins’, then click on ‘Analysis Tookpak’, then click OK. 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’. 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.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.Now click on Cell D4. This is where we will ultimately put the Output table.At the top of the toolbar, Click on Data, then click Data Analysis. Scroll down and select ‘T-Test: Two Sample Assuming Unequal Means’.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.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.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 Next, leave alpha as 0.05.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.Click OK.This will make the test results appear. Here’s how to read it:In Cell D12 your test statistic will appear.In Cell D13, you will have your P-value for a 1 tailed test.In Cell D14, you will have your critical value for a 1 tailed test.In Cell D15, you will have your P-Value for a 1 tailed test.In Cell D16, you will have your critical values for a two tailed test.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. 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.Perform an ANOVA TestNow we are going to run an ANOVA test to determine whether your company, the one you selected for the two sample hypothesis test, and another (a 3rd) company all have the same stock price. Use this time to think about what your null and alternative hypotheses will be. If you’ve been keeping up with the readings, you should know that ANOVA tests are always right tailed (1 tailed) tests.In the bottom left of your spreadsheet, next to the tab that says ‘Two 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. Click on it, double click on where it now says ‘Sheet 6’, delete that, and rename it as ‘ANOVA’. Go to the previous worksheet labelled ‘Two Sample Hypothesis Test’. Click on the name of your company in Cell A1, and while holding down ‘Shift’, hit the arrow key pointing to the right one time, then hit the ‘end’ key, followed by the arrow pointing down. This should highlight both columns in blue. Now hit CTRL and ‘C’ to copy the data, click on the Tab for your new ‘ANOVA’, and place your cursor in Cell A1. Hit CTRL and ‘V’ to paste the data into the new worksheet.Next, we need to get data on another company from Morningstar again. Pick a company that is similar to the other two, such as another potential rival. (If you’re having trouble finding one just pick a well-known US company, such as IBM or Google).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.Go to Morningstar.com. Where it says “Quote”, type in the ticker symbol. The company’s profile should then appear.Click Performance, which is located in the toolbar in the middle of the screen.Click Price History, which will be right underneath Performance. 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”. 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. 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 ‘ANOVA’, and place your cursor in Cell C1. Hit CTRL and ‘V’ to paste the data into the new worksheet. In Cell C1, delete the word ‘Close’ and replace it with the name of the third company you chose.Now we will run an ANOVA Test, but first we must make sure your computer’s software package is ready to run. In the top left corner, click on ‘File’, then click ‘Options’, then click ‘Add-Ins’, then click on ‘Analysis Tookpak’, then click OK. 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’. 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.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 ANOVA Test.Now click on Cell D4. This is where we will ultimately put the Output table.In the toolbar at the top, click on Data, then Data Analysis. A popup box will appear. Click on the first option down, entitled ‘ANOVA: Single Factor’. A new popup box will appear. Click into the box next to Input Range, and then click on Cell A1 in your ANOVA spreadsheet containing the price data of all three companies. While holding down ‘Shift’, hit the arrow pointing right two separate times, then hit the ‘End’ key, followed by the arrow pointing down. This should highlight all three columns in blue to the bottom of your price data (If it doesn’t contain all the data, just hit the arrow keys accordingly until it is all covered in blue. Also, if there are blank spaces because there is not enough price data this is ok.)Next, make sure that the circle next to ‘Columns’ has a dot in it and click the box next to ‘Labels in the First Row’ since in Cells A1, B1, and C1 we have placed the names of the companies. Leave Alpha as 0.05 and then click the circle next to ‘Output Range’ and then immediately click in the textbox to the right of it. Next, click on Cell E4; this is where we are going to put the output table. Then Click OK. A new output table will appear.Here’s how to read it:Underneath where it says ‘Source of Variation’, Between Groups Stands for Between Treatments, while Within Groups stands for Errors. Hence, the box matching up for SS and ‘Between Groups’ is SST, below it is SSE, and two boxes to its right is MST. We are concerned with F, P, and the Critical Value (labelled as F crit). F is your test statistic.F Crit is the Critical Value.If F>Critical Value, you reject the Null; if F<Critical Value you fail to reject. P is your P-Value; if it is greater than alpha (here alpha is 0.05) you are confident in failing to reject the Null, while if it is less than alpha you are confident in rejecting the Null.Upon doing this, you need to decide whether to reject or fail to reject the null hypothesis and state a conclusion of your ANOVA test.Perform a Comparison of Two Population Variance TestNow we are going to perform a comparison of Two Population Variance tests, which are used to determine if two population variances are equal or if one is greater than the other. Here we are going to test whether two population variances are equal. Use this time to think about what your null and alternative hypotheses will be.In the bottom left of your spreadsheet, next to the tab that says ‘ANOVA’, 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 7’, delete that, and rename it as ‘Comparison of Two Variances’ or whatever will fit. Go to the previous worksheet labelled ‘Two Sample Hypothesis Test’ (Yes, you want the one for 2 sample hypothesis test; this is not a typo on my part). Click on the name of your company in Cell A1, and while holding down ‘Shift’, hit the arrow key pointing to the right one time, then hit the ‘end’ key, followed by the arrow pointing down. This should highlight both columns in blue. Now hit CTRL and ‘C’ to copy the data, click on the Tab for your new ‘Comparison of Two Population Variances’, and place your cursor in Cell A1. Hit CTRL and ‘V’ to paste the data into the new worksheet.This time we do not need to worry about activating the software package Analysis Toolpak. We can do this test right in EXCEL with the tools it comes with.In Cell C3, type ‘=var.s(’ and then click on Cell A2. Once you have done that, while holding down ‘Shift’, hit the ‘End’ key followed by the arrow pointing down. Finally, hit the close parentheses key ‘)’ and hit Enter.Next, we have to do the same for Column B. In Cell D3, type ‘=var.s(’ and then click on Cell B2. Once you have done that, while holding down ‘Shift’, hit the ‘End’ key followed by the arrow pointing down. Finally, hit the close parentheses key ‘)’ and hit Enter.Now we need to figure out which Company will be used in the denominator and which will be used in the numerator. As per your book, whichever sample variance is larger becomes the numerator and the smaller one becomes the denominator. In this way, the test statistic is guaranteed to be greater than 1. Now we need to get out critical value from the F distribution at the 5 percent level of significance (refer to the Z,T, and F handouts for which chart you should be looking at; it’s the one on the left of the F-distribution, which is the 3rd page you’ve been provided). To do this, you need to find the degrees of freedom for the numerator and the degrees of freedom for the denominator.Here’s how to do this quickly. In Cell D5, type ‘=count’ and then click on the first Cell (but not the name) of whatever company has the higher sample variance.For example, if column A had a sample variance of 2, and Column B had a sample variance of 3, then in Cell D5 you’d type ‘=count(’ and then click on B2. Once you click on the appropriate box of the appropriate column, while holding down ‘Shift’, hit the ‘End’ key followed by the arrow key pointing down. This should highlight that entire column in blue. Close the parenthesis ‘)’ and hit enter. That is the number of observations in the entire column.To get the degrees of freedom for that column, subtract 1 from the count you obtained. For example, if the count came back as 254, you have 253 degrees of freedom.Repeat this exercise for the other column and do it in Cell E5 (meaning type ‘=count(’. In my example, where column A had a sample variance of 2, and Column B had a sample Variance of 3, then in Cell E5 you’d type ‘=count(’ and then click on A2, while holding down ‘Shift’, hit the ‘End’ key followed by the arrow key pointing down. This should highlight that entire column in blue. Close the parenthesis ‘)’ and hit enter.Next, take these two sets of degrees of freedom with you to the F distribution at the 5 percent level of significance.Remember, whichever sample variance is larger becomes the numerator and the smaller one becomes the denominator. Thus, now look up the larger sample variance’s degrees of freedom as the numerator, and look up the smaller sample variance’s degrees of freedom as the denominator. This will give you the critical value. Enter it in Cell D10.Now we are going to calculate the test statistic in Cell D9.We are going to divide the large sample variance by the smaller sample variance to get our test statistic. This will require a little thinking on your own. In Cell D9, type ‘=’ and then click on the Cell corresponding to the larger sample variance, which will be either Cell C3 or Cell D3 (whichever one is larger is the one you want to go with). Then hit the ‘slash’ button (‘/’), which is to the left of the ‘shift’ on the lower right hand side of the keyboard. Then click on the Cell with the smaller sample variance, which will be either Cell C3 or D3 (whichever one is smaller is the one you want to go with). Close the parenthesis ‘)’ and then hit enter.That number, the number in Cell D9 is your test statistic. Compare it to the critical value in Cell D10. If it is greater than the critical value, then reject the null hypothesis; if it is smaller than it you will reject the null hypothesis.Upon doing this state a conclusion of your Comparison of Two Population Variance test. Perform a Linear RegressionNow we are going to perform a Linear Regression, the final exercise in this paper. We are going to attempt to find out what effect, if any, the volume traded of your stock (how many units of it are sold in a single day) has on its price. Our Model will be Price = a + b(Volume). In the bottom left of your spreadsheet, next to the tab that says ‘Comparison of Two Population Variances’, 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 7’, delete that, and rename it as ‘Linear Regression’.Next, we need to get data on your company from Morningstar again. In case you forgot your ticker’s symbol, 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.Go to Morningstar.com. Where it says “Quote”, type in the ticker symbol. The company’s profile should then appear.Click Performance, which is located in the toolbar in the middle of the screen.Click Price History, which will be right underneath Performance. 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”. 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. Place your cursor in Cell B2, where it says ‘Open’. While holding down ‘Shift’, hit the ‘End’ key, followed by the arrow key 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 ‘Linear Regression’, and place your cursor in Cell A1. Hit CTRL and ‘V’ to paste the data into the new worksheet. Now return to the new, temporary worksheet you just downloaded from Morningstar. Place your cursor in column F (specifically Cell F2) in the new file where it says ‘Volume’. 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 ‘Linear Regression’, and place your cursor in Cell B1. Hit CTRL and ‘V’ to paste the data into the new worksheet.Next, rename Cell A1 from ‘Open’ to the name of your company and leave Cell B1 unchanged (named as Volume). Next, we need to make sure that there are no glitches in your data that could hinder us. Hit CTRL and ‘F’ at the same time to initiate the ‘Find Search’, then click on the ‘Replace’ tab. In the textbox to the right of ‘Find What’ type in (with no quotation marks obviously) ‘0,000’. Then, below it, to the right of the textbox that says ‘Replace With’, type (without quotation marks obviously) ‘1,000,000’. Next, hit ‘Replace All’. Next, we need to make sure the software package is activated. In the top left corner, click on ‘File’, then click ‘Options’, then click ‘Add-Ins’, then click on ‘Analysis Tookpak’, then click OK. 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’. 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.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 ANOVA Test.Now click on Cell D4. This is where we will ultimately put the Output table. Next, click on Data, then Data analysis, then scroll down to Regression and click OK.Where the popup box says ‘Input Y Range’, click in the box, and then click on Cell A1. After doing so, while holding down ‘Shift’, hit the ‘End’ key, followed by the arrow pointing down. This should highlight the entire column blue.Next, where the popup box says ‘Input X Range’, click in the box, and then click on Cell B1. After doing so, while holding down ‘Shift’, hit the ‘End’ key, followed by the arrow pointing down. This should highlight the entire column blue.Click the check mark next to the box labelled as ‘Labels’, then click the circle next to the left of ‘Output Range’ and then immediately click inside that box. Next, click Cell C3 and hit OK.A lot of things have happened! There is certainly a lot of information to process here now, isn’t there? While we could go over everything in detail, we’ll save that for econometrics. For now, we are concerned with the ‘3rd’ chart down where the column says coefficients and the rows say ‘intercept’ and ‘volume’. Let’s make our model.In the model Price = a + b(Volume), ‘a’ will be the value corresponding to the coefficient and the intercept, which is Cell C20. ‘b’ is the value corresponding to coefficient and volume, which is Cell C21. In Cell G2, write this formula out. For example, if my ‘a’ was 3 and my ‘b’ was 4, in Cell G2 I would write Y = 3 + 4(Volume).Now, in Cell G3, we are going to estimate the price of your stock based upon a volume of 8.7 million shares traded on a daily basis. To do so, you need to use the values for ‘a’ and ‘b’ that you received from the Regression earlier and just typed into Cell G2. Now, in Cell G3, type ‘=(the value of ‘a’ + (the value of ‘b’*8700000)) and then hit Enter. This will give you an estimate for the price of the stock based upon 8.7 million shares of the stock being traded in a day. Record this stock price estimate, this is the answer you were interested in estimating when performing a regression.The Limitations of Inferential StatisticsNow, briefly, we will examine when and where (if at all) the inferential statistics we created above may not be applicable because of the limits to statistics’ capabilities. After reading Rafferty’s document “The Limitations of Inferential Statistics” on Blackboard, use this knowledge to analyze what the nature of your observations are and how this might have affected your calculation of the various statistical tests. In other words, how might various external events impacted your observations, and what does this say about your data set? What about for how financial markets operate in normal time and in volatile times? Include this analysis of at least a paragraph in a separate section of the paper before the conclusion.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.

Don't use plagiarized sources. Get Your Custom Essay on

Statistics for Business and Economics

Just from $13/Page

The price is based on these factors:

Academic level

Number of pages

Urgency

Basic features

- Free title page and bibliography
- Unlimited revisions
- Plagiarism-free guarantee
- Money-back guarantee
- 24/7 support

On-demand options

- Writer’s samples
- Part-by-part delivery
- Overnight delivery
- Copies of used sources
- Expert Proofreading

Paper format

- 275 words per page
- 12 pt Arial/Times New Roman
- Double line spacing
- Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Delivering a high-quality product at a reasonable price is not enough anymore.

That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more