1 / 17

Solution to Homework #1

Solution to Homework #1. MGS 8110, Regression & Forecasting Summer 2012. READING: Review the material in Lectures L00A, L00D, L00E & L00I. Read Chapters 2 of the textbook.

ted
Download Presentation

Solution to Homework #1

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Solution to Homework #1 MGS 8110, Regression & Forecasting Summer 2012 MGS 8110

  2. READING: Review the material in Lectures L00A, L00D, L00E & L00I. Read Chapters 2 of the textbook. Read Chapters 3 through 5. This is a lot of reading and you have 7 weeks to complete this reading assignment. Please step up and take a big bite out of it this week because you have minimal calculations to do this week (see below). Submittals for this HW. The last worksheet in DATA HW01.xls is the Answer Sheet. Fill in the blank cells and submit this sheet. Present 3 significant digits when appropriate. Hints are shown in Blue on the Answer Sheet. Eliminate the color coding before printing the Answer Sheet. Look over the Answer sheet before doing any calculation so that you know what is required. MGS 8110

  3. Part A Access Class Notes Download the class notes, homework, etc. from our Web page. http://www.gsu.edu/~mgtrks/ Click on the MGS 8110, then Lecture 00. The downloads are PowerPoint presentations and can be printed 2 or 6 per page (in Power Point 2003 go to FILE/PRINT, then “Print what = handouts” and “Slides per page = 6”) (in Power Point 2007 go to View on toolbar, then / Handout Master / Slides Per Page /). MGS 8110

  4. Part B MS Equation Practice using MS Equation by duplicating the equations shown at the right. Change the x's to y's to verify that you used MS Equations and did not just cut-&-paste the box shown at the right. Submittal: Paste you three equations in one box on the right side of your Answer Sheet. MGS 8110

  5. Part C Basic Excel Calculations C0. Access the Osyter Data in the first worksheet of DATA HW01.xls. The definition of the variables is given in the second worksheet. C1. Create an exact copy of the date in the first worksheet. That is, make a duplicate copy of the 1st worksheet. Label the tab for the first worksheet "DATA - original". Label the tab for the third worksheet "DATA - Analysis #1". C2. Create an "Index Column" at the far left of the data array. Each row of data should be given a sequential number from 1 to 30. C3. Make a duplicate copy of the data in the third worksheet and label the tab of the fourth worksheet "DATA - Analysis #2". Until told different, you should be doing the subsequent calculations in the worksheet, DATA - Analysis #1. This is the first of the two new worksheets. C4. Create four cells at the bottom of the column of data and calculate the Mean, Standard Deviation, Skewness and Kurtosis for Weight, Volume, 3D & 2D. Which of variables appears to be Normally Distributed. C5. Create a column to the right of the 2D column and classify the oysters by volume. Label the column "Class". Less than 10 cc is "S" (Small), 10 to 13 cc is "M" (Medium) and greater than 13 is "L" (Large). Use a nested IF command. C6. Add three cells at the bottom of the Class column and state the number of Small, Medium and Large oysters in the sample. Use the COUNTIF command in Excel. C7. Add two columns to the right of the “Class” and convert the Weight in grams to Weight in ounces and convert the volume in cc to volume in fluid ounces. Label the columnsWt_oz and Vol_oz. C8. Add another column to the right of Vol_oz column and calculate the Ratio of Weight to Volume. State these values as percentages. Title the column "Ratio WtVol". Does it matter if you use the two columns with metric units or the two columns with US units? C9. Create three cells at the bottom of the Ratio WtVol column and calculate the 1st Quartile, 2nd Quartile and 3rd Quartile. Use the Excel command QUARTILE. C10. Create a fourth cell at the bottom of the Ratio WtVol column and calculate the 90th Percentile. Use the Excel command PERCENTILE. C11 Add another column to the right of the Ratio WtVol column. Print the rank of each oyster in terms of volume. The oyster with a rank of 1 has the highest volume of the 30 oysters. Use the RANK function. Title the column "Rank.Vol". C12. For the variable "Pixels_3D", create three cells at the bottom of the column: 1) Median, 2) Percentage for the Mean, 3) Percentage for the Median and 4) the Difference between the Percentage Mean and Percentage Median. HINT: use the PercentRank function in Excel to calculate the two percentage rows, that is for 2) and 3). C13. Based on the difference between the % Mean and the % Median, is Pixels_3D normally distributed? Base on the calculated values of Skewness and Kurtosis is Pixels_3D normally distributed? MGS 8110

  6. Part C Basic Excel Calculations C0. Access the Osyter Data in the first worksheet of DATA HW01.xls. The definition of the variables is given in the second worksheet. C1. Create an exact copy of the date in the first worksheet. That is, make a duplicate copy of the 1st worksheet. Label the tab for the first worksheet "DATA - original". Label the tab for the third worksheet "DATA - Analysis #1". C2. Create an "Index Column" at the far left of the data array. Each row of data should be given a sequential number from 1 to 30. MGS 8110

  7. Part C Basic Excel Calculations C4. Create four cells at the bottom of the column of data and calculate the Mean, Standard Deviation, Skewness and Kurtosis for Weight, Volume, 3D & 2D. Which of variables appears to be Normally Distributed. MGS 8110

  8. Part C Basic Excel Calculations C5. Create a column to the right of the 2D column and classify the oysters by volume. Label the column "Class". Less than 10 cc is "S" (Small), 10 to 13 cc is "M" (Medium) and greater than 13 is "L" (Large). Use a nested IF command. C6. Add three cells at the bottom of the Class column and state the number of Small, Medium and Large oysters in the sample. Use the COUNTIF command in Excel. C7. Add two columns to the right of the “Class” and convert the Weight in grams to Weight in ounces and convert the volume in cc to volume in fluid ounces. Label the columnsWt_oz and Vol_oz. MGS 8110

  9. Part C Basic Excel Calculations C8. Add another column to the right of Vol_oz column and calculate the Ratio of Weight to Volume. State these values as percentages. Title the column "Ratio WtVol". Does it matter if you use the two columns with metric units or the two columns with US units? C9. Create three cells at the bottom of the Ratio WtVol column and calculate the 1st Quartile, 2nd Quartile and 3rd Quartile. Use the Excel command QUARTILE. C10. Create a fourth cell at the bottom of the Ratio WtVol column and calculate the 90th Percentile. Use the Excel command PERCENTILE. C11 Add another column to the right of the Ratio WtVol column. Print the rank of each oyster in terms of volume. The oyster with a rank of 1 has the highest volume of the 30 oysters. Use the RANK function. Title the column "Rank.Vol". MGS 8110

  10. Part C Basic Excel Calculations C0. Access the Osyter Data in the first worksheet of DATA HW01.xls. The definition of the variables is given in the second worksheet. C1. Create an exact copy of the date in the first worksheet. That is, make a duplicate copy of the 1st worksheet. Label the tab for the first worksheet "DATA - original". Label the tab for the third worksheet "DATA - Analysis #1". C2. Create an "Index Column" at the far left of the data array. Each row of data should be given a sequential number from 1 to 30. C3. Make a duplicate copy of the data in the third worksheet and label the tab of the fourth worksheet "DATA - Analysis #2". Until told different, you should be doing the subsequent calculations in the worksheet, DATA - Analysis #1. This is the first of the two new worksheets. C4. Create four cells at the bottom of the column of data and calculate the Mean, Standard Deviation, Skewness and Kurtosis for Weight, Volume, 3D & 2D. Which of variables appears to be Normally Distributed. C5. Create a column to the right of the 2D column and classify the oysters by volume. Label the column "Class". Less than 10 cc is "S" (Small), 10 to 13 cc is "M" (Medium) and greater than 13 is "L" (Large). Use a nested IF command. C6. Add three cells at the bottom of the Class column and state the number of Small, Medium and Large oysters in the sample. Use the COUNTIF command in Excel. C7. Add two columns to the right of the “Class” and convert the Weight in grams to Weight in ounces and convert the volume in cc to volume in fluid ounces. Label the columnsWt_oz and Vol_oz. C8. Add another column to the right of Vol_oz column and calculate the Ratio of Weight to Volume. State these values as percentages. Title the column "Ratio WtVol". Does it matter if you use the two columns with metric units or the two columns with US units? C9. Create three cells at the bottom of the Ratio WtVol column and calculate the 1st Quartile, 2nd Quartile and 3rd Quartile. Use the Excel command QUARTILE. C10. Create a fourth cell at the bottom of the Ratio WtVol column and calculate the 90th Percentile. Use the Excel command PERCENTILE. C11 Add another column to the right of the Ratio WtVol column. Print the rank of each oyster in terms of volume. The oyster with a rank of 1 has the highest volume of the 30 oysters. Use the RANK function. Title the column "Rank.Vol". C12. For the variable "Pixels_3D", create three cells at the bottom of the column: 1) Median, 2) Percentage for the Mean, 3) Percentage for the Median and 4) the Difference between the Percentage Mean and Percentage Median. HINT: use the PercentRank function in Excel to calculate the two percentage rows, that is for 2) and 3). C13. Based on the difference between the % Mean and the % Median, is Pixels_3D normally distributed? Base on the calculated values of Skewness and Kurtosis is Pixels_3D normally distributed? MGS 8110

  11. Part C Basic Excel Calculations C12. For the variable "Pixels_3D", create three cells at the bottom of the column: 1) Median, 2) Percentage for the Mean, 3) Percentage for the Median and 4) the Difference between the Percentage Mean and Percentage Median. HINT: use the PercentRank function in Excel to calculate the two percentage rows, that is for 2) and 3). C13. Based on the difference between the % Mean and the % Median, is Pixels_3D normally distributed? Base on the calculated values of Skewness and Kurtosis is Pixels_3D normally distributed? MGS 8110

  12. Part D Population Differences Now go to the worksheet, DATA - Analysis #2. D1. Sort the data in terms of the Class.. You are creating three sub populations: Small, Medium and Large. In Excel click on the Data icon on the uppermost toolbar, then click on Sort. D2. Calculate the Average, Standard Deviation and the Coefficient of Variation for "Volume in cc" for each of the three sub-populations. D3. How good are these estimate of the Means? Calculate a 95 % confidence interval for each of the three means.. MGS 8110

  13. Part E Data Analysis Add-in E0. Load the Add-in "Data analysis" to your Excel (see slides 44 thru 50 of L00D). Access the Oyster Data. E1. Use / Data Analysis / Descriptive Statistics to calculate basic statistics for th four variables: Weight, Volume, 3D and 2D. MGS 8110

  14. Part E Data Analysis Add-in E2. Calculate the correlation between 4 variables using / Data Analysis / Correlation / in Excel. MGS 8110

  15. Part F Normal Distribution The transaction time at an ATM is approximately normally distributed with a mean of 20 seconds and a standard deviation of 8 seconds. F1. What is the probability that a transaction takes more than 30 seconds? F2. What is the probability that a transaction takes more than 35 seconds? F3. What is the probability that a transaction takes less than 15 seconds? F4. What is the probability that a transaction takes between 15 and 25 seconds? F5. Ten percent of the customers will have a transaction time greater than what value (in seconds)? MGS 8110

  16. Part F Normal Distribution The transaction time at an ATM is approximately normally distributed with a mean of 20 seconds and a standard deviation of 8 seconds. F1. What is the probability that a transaction takes more than 30 seconds? F2. What is the probability that a transaction takes more than 35 seconds? F3. What is the probability that a transaction takes less than 15 seconds? F4. What is the probability that a transaction takes between 15 and 25 seconds? F5. Ten percent of the customers will have a transaction time greater than what value (in seconds)? F6. Calculate a so-called 95% Confidence interval for transaction times, that is calculate the 2.5 percentile point and the 97.5 percentile point. F7. In class you were told that you could frequently approximate a 95% Confidence Interval by multiplying the standard deviation by 2 and then adding and subtracting that from the average. The actual formula is shown below. Calculate this approximate 95% CI. MGS 8110

  17. Answer Sheet MGS 8110

More Related