The objective of the Homework assignment is to provide an opportunity to apply or practice the tools and techniques covered in the course and to provide an opportunity to articulate key concepts addressed in the course. The homework is not a test. Any resource (eLecture, web-based, content, study partners, and the Solutions Workbook) may be used to to help answer the problems in each worksheet. The Homework problems generally correspond to the types of problems you will encounter in the Applications Assignment, which will be made available at the beginning of Unit 11. Consequently, working on the Homework problems will be good practice for the Applications Assignment. The worksheets in this workbook correspond to the Units in the course. In two cases, there are two worksheets for a Unit (Units 4 & 10). Each worksheet contains several problems keyed to the Unit's Learning Objectives, the Unit's eLecture, and the text content covered in the Unit. Work through the problems for a Unit. Generally, answers to the problems are to be placed in a specific cell. Typically, the target cells will be "boxed" or highlighted. See cells R3 & R5 on this worksheet for an example. In some cases, graphs & charts for example, a boxed space is provided near the problem's directions. In most cases, directions for a problem are provided in a cell above problem's workspace. However, as you work through the worksheets, look for the red comment triangle in the upper right-hand corner of worksheet cells that are highlighted in yellow. The comments will reveal specific instructions or guides to solving the problems. See the red comment triangle in the upper right corner of this instruction box. Simply hover the cursor above the cell having a comment, and the comment will be displayed. When finished with the problems for a Unit, submit the entire workbook to the Homework Dropbox for the Unit. To receive credit for the Homework, it should be submitted before the close of the Unit (Tuesday @ midnight) the week that the Unit is open. Homework items are numbered and highlighted. Some of the problems are displayed far down the worksheets or far to the right of the worksheets. Be sure to scroll all the way down the highlighted column to find all the problems in each worksheet to scroll to the right to find any problems located there. There is a Homework Solutions workbook in the Homework Doc Sharing folder. You may check your homework against the Solutions worksheets, but you should not simply copy the answers. Actually performing the calculations required in the problems will be invaluable practice for the Applications Assignment, which you will receive in Unit 11. Homework Grade Homework is 10% of the final course grade. There is a Homework assignment in each of the 12 Units. Homework will be graded according to the rubric below, and the the Top 10 Homework grades will be summed to get the Homework contribution to the final grade. Homework Grading Rubric 0 .10-.25 Homework not Up to 25% of the submitted, problems submitted after the completed close of the unit, or correctly. submitted homework omits the majority of the problems in the worksheet. Points per Unit .26-.50 26-50% of the problems completed correctly. 51-.75 51-75% of the problems completed correctly. .76-1.0 76-100% of the problems completed correctly. 4 2 xxx Business Analytics concerns not only the analysis of data, but also the systems that collect, store, assure data integrity, and manage the data. It is impossible to conduct a "good" analysis if the data to be analyzed is not of high quality--primarily meaning secure and accurate. Answer the questions that follow as you learn about the process of transforming data into information. 1 What are data? Provide some examples. 2 How are data and information different? 3 What are the three benefits that a strong information system will bring to an organization? 4 What is the Productivity Paradox? 5 What is an extranet? What are some advantages of Extranets? 6 What is an intranet? 7 What are the three decision-making levels within an organization and describe the type of information needed at each level. 8 What is an ERP? Describe. 9 What is SCM? Describe. 10 What is CRM? Describe. 11 What are relevant issues related to information accessibility, privacy, and the ethical use? 12 Describe typical security attacks and ways to defend against them. 1 List and define five optical illusions that can effect one's perception of graphed data. 2 List and define five components of effective graphing. 3 PartyAffiliation Democrat Democrat Democrat Democrat Independent Republican Independent Republican Independent Republican Independent Republican Independent Democrat Democrat Republican Democrat Democrat Democrat Republican Republican Independent Republican Democrat Republican Republican Independent Republican Republican Republican Republican Republican Independent Independent Republican Republican Democrat Republican Democrat Democrat Democrat Democrat Democrat Democrat Independent Republican Independent Republican Independent Republican Independent Republican Independent Democrat Democrat Republican Democrat Democrat Democrat Republican Republican Independent Republican Democrat Republican Republican Independent Republican Republican Republican Republican Republican Independent Independent Republican Republican Democrat Republican Democrat 4 Compute PartyAffiliation frequency using the COUNTIF function. Democrat 1 Compare and contrast variables and values. 2 Define Qualitative data. 3 Define Quantitative data. 4 What is variability in a distribution of data? 5 In the following distribution of home prices, which is a better measure of central tendency: $210,000, $215,000, $215,000, $220,000, $645,000? Select answer from the Dropdown choice. 6 Why is the choice you made for Item 5 the better choice? Days 70 64 99 55 64 89 8 How many bonds are in the list? 9 Create a frequency distribution of the day values that are grouped. Place in cell G5. 87 65 62 38 67 70 60 69 78 10 7 What is bias in a statistical measure? Compute the mean number of days to maturity. 11 Compute the median number of days to maturity. 39 75 56 71 51 99 68 95 or 12 Calculate the Variance of the Days to Maturity sample. 13 Calculate the Standard Deviation of the Days to Maturity sample. 86 57 53 47 50 55 81 80 98 51 36 63 66 85 79 83 70 or 14 Calculate the Interquartile Range of bond maturity. Median Mean sex org ini indi inf prwe preq prwd prwc prwco icad icim icimo icpa icpy su tr cptw itl 1 4.92 4.50 5.00 4.75 4.67 5.00 4.56 3.92 4.50 4.86 5.00 5.00 4.50 4.33 4.56 4.75 4.80 5.00 1 3.67 4.00 3.67 2.00 4.33 4.25 3.22 3.00 3.00 3.43 4.40 4.00 2.50 3.67 2.50 3.75 3.70 5.00 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 3.25 3.17 3.67 3.75 4.42 3.67 4.67 3.75 4.08 3.83 4.00 4.00 2.92 3.92 3.17 4.08 3.75 3.58 3.42 4.67 4.00 4.33 3.42 2.25 4.42 4.33 4.00 3.83 2.50 4.75 3.58 4.67 4.17 2.83 2.50 4.75 3.50 2.58 4.42 4.00 3.67 4.25 3.50 3.92 3.33 3.00 3.00 3.50 3.50 4.00 4.00 3.50 4.00 3.00 4.00 2.50 3.50 3.00 2.50 3.00 4.50 4.00 4.00 3.50 4.00 4.00 4.00 2.50 3.00 3.50 3.50 3.50 3.00 3.50 4.50 3.50 4.50 4.00 3.50 3.00 2.50 3.00 4.00 4.00 3.50 4.00 3.50 3.50 3.00 3.50 3.50 3.00 3.83 3.33 4.00 3.83 4.00 3.83 4.50 3.67 2.50 3.17 3.00 3.33 3.67 4.00 3.67 3.67 3.33 4.67 3.50 4.33 3.50 2.00 4.00 4.00 3.50 3.00 3.50 4.67 2.50 4.67 4.00 2.67 3.00 4.50 3.40 1.83 4.17 3.83 3.67 3.80 3.17 3.33 3.17 2.75 2.25 2.75 3.75 3.33 3.75 2.75 4.00 3.75 2.75 3.50 3.00 3.00 3.75 2.25 3.50 3.75 3.75 3.75 4.50 3.50 4.50 2.25 2.00 4.00 3.50 3.75 2.50 3.25 4.50 2.75 4.75 4.25 3.75 2.50 4.25 3.25 3.00 3.00 3.75 4.25 4.00 2.50 3.25 2.75 4.33 4.00 4.00 3.33 4.67 4.00 4.00 4.00 4.00 4.00 3.33 4.00 4.00 3.67 4.00 4.00 4.00 4.33 3.67 4.33 4.33 4.00 4.67 2.33 4.00 4.67 4.00 4.33 4.00 5.00 4.00 4.67 4.33 3.67 3.33 4.00 3.67 4.33 4.33 3.67 3.33 4.00 3.67 3.33 4.33 4.50 4.00 3.75 2.75 3.75 4.00 3.25 3.75 3.75 4.00 4.00 4.00 3.50 3.75 3.25 4.00 3.75 3.75 3.75 4.00 3.75 4.00 2.50 3.25 4.00 4.00 3.75 4.00 4.00 4.50 3.25 3.75 4.00 2.75 3.25 4.25 4.00 4.50 4.75 4.00 4.50 3.50 4.25 3.25 4.00 2.38 3.56 3.44 3.56 3.11 3.11 3.67 3.00 2.78 3.67 2.33 3.33 2.67 2.89 3.11 3.67 2.78 2.56 2.67 2.89 2.67 3.78 2.67 3.00 3.56 3.56 3.25 3.11 3.11 4.00 2.33 3.67 2.56 1.89 2.44 4.67 2.67 2.00 3.75 3.11 2.89 3.33 3.33 3.11 2.56 2.33 2.92 3.25 3.00 3.50 2.75 3.91 2.25 1.92 3.08 2.17 3.42 2.44 2.33 2.92 2.92 1.92 2.67 2.58 2.00 2.92 3.17 1.67 2.25 3.00 2.92 2.58 2.75 2.25 2.42 2.58 2.92 2.50 1.92 1.83 2.58 2.42 2.25 4.17 2.67 3.08 3.42 2.33 2.50 2.50 2.00 2.50 2.00 3.50 4.50 3.00 5.00 2.50 3.00 3.00 2.50 3.00 2.50 3.00 2.50 3.00 2.00 2.00 2.50 2.50 2.50 3.00 1.50 1.50 3.00 2.50 3.00 2.50 2.00 2.50 2.00 2.50 2.50 2.50 1.50 4.50 2.50 1.50 4.00 2.50 3.00 4.00 2.00 3.00 3.00 3.14 2.86 2.00 3.43 4.14 3.43 3.71 3.71 3.29 3.57 3.71 2.86 3.60 2.71 3.00 3.71 3.00 3.86 3.14 3.71 3.29 3.57 2.57 2.71 4.71 3.71 3.71 3.57 3.14 4.29 2.86 4.00 4.71 2.71 2.57 4.14 2.00 2.71 3.83 3.29 3.14 3.86 3.14 3.57 3.29 3.80 4.20 4.80 3.80 3.60 4.00 4.20 4.60 4.80 4.60 4.80 4.60 4.00 4.60 3.40 4.60 4.20 4.60 4.60 4.80 4.20 4.20 4.40 4.60 4.80 5.00 4.00 4.60 4.20 5.00 4.00 4.40 5.00 4.40 4.60 5.00 4.80 4.80 4.80 4.20 3.80 4.20 4.40 4.40 4.60 3.00 3.00 5.00 4.00 4.50 4.00 4.50 4.50 4.00 4.00 4.50 4.50 4.00 4.00 3.50 4.00 4.00 5.00 4.50 4.50 4.00 4.50 3.50 5.00 4.50 5.00 4.00 3.50 3.00 5.00 4.00 5.00 4.50 3.00 3.00 5.00 5.00 4.00 5.00 3.50 4.00 5.00 4.00 4.00 4.50 3.00 3.50 2.50 4.00 3.50 4.50 4.00 5.00 4.50 2.50 2.00 4.50 3.00 3.50 3.00 2.50 3.50 3.00 3.50 4.50 3.00 4.50 3.00 3.00 4.50 2.50 3.50 3.00 3.50 4.00 3.00 4.00 3.00 3.50 3.50 5.00 4.00 4.00 3.00 4.50 1.50 4.50 4.00 4.50 3.00 1.33 3.00 2.00 3.00 4.33 3.33 4.33 3.33 3.33 3.00 1.33 2.67 3.67 2.00 3.67 3.67 2.33 3.00 2.67 3.33 2.67 3.67 2.33 3.00 4.00 3.67 4.00 2.33 2.67 3.67 2.67 4.00 3.00 2.67 2.00 3.67 4.00 3.33 4.33 3.33 3.33 3.67 3.00 3.67 3.33 2.75 3.00 1.63 3.50 4.21 3.50 3.88 3.88 3.31 3.50 3.69 2.94 2.71 2.31 2.50 2.56 3.25 2.94 3.47 3.00 3.25 4.00 2.50 1.69 4.31 2.93 3.44 3.56 3.06 4.00 2.13 4.06 3.81 3.25 3.06 4.00 3.23 1.56 3.73 3.94 3.06 3.75 3.06 3.38 2.88 3.50 3.75 3.75 3.25 4.00 4.00 3.00 3.00 3.25 3.75 2.75 3.50 2.33 3.00 3.00 4.50 3.25 3.00 3.25 3.50 3.00 3.00 1.25 3.00 4.00 3.75 3.25 3.50 3.25 4.50 2.50 4.25 3.25 3.00 1.50 2.50 2.00 3.25 3.67 2.75 3.00 3.75 4.00 3.50 3.00 3.50 3.20 3.80 4.00 4.40 3.40 4.20 3.90 3.80 4.20 3.90 4.00 3.38 4.00 3.30 3.50 3.40 3.60 3.50 3.70 4.00 4.30 3.00 2.60 4.30 4.20 4.00 3.70 2.60 4.60 3.30 4.60 4.10 3.00 3.00 4.40 3.90 2.70 4.30 3.50 3.50 4.30 3.80 3.60 3.60 3.25 4.00 4.00 4.75 3.00 4.75 5.00 5.00 4.75 4.50 4.00 4.50 4.00 3.50 5.00 5.00 4.00 4.00 4.00 5.00 5.00 5.00 3.00 2.75 5.00 5.00 4.75 4.75 4.25 5.00 3.50 5.00 5.00 4.00 3.75 5.00 3.50 3.50 4.25 3.50 4.25 5.00 4.00 5.00 5.00 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 3.92 3.92 3.92 4.67 4.00 3.08 3.58 4.42 4.33 4.00 3.92 4.08 3.67 3.67 2.42 3.17 3.50 4.25 2.92 3.75 3.50 3.42 4.42 3.58 3.91 4.42 3.50 4.00 4.00 4.00 3.50 3.50 3.50 4.00 3.50 2.00 4.00 4.00 2.00 4.00 3.00 4.00 4.00 3.50 4.00 4.00 3.50 3.50 4.00 3.50 4.50 3.00 3.67 3.33 3.50 4.00 4.00 2.33 3.33 4.17 4.50 3.50 4.00 2.50 3.33 2.67 3.83 3.00 3.33 4.33 3.50 3.67 3.17 3.00 4.17 3.00 3.33 2.83 3.75 3.75 3.75 3.50 4.00 1.00 3.50 4.25 3.75 3.75 3.25 2.75 3.00 2.25 2.50 2.75 1.00 4.00 4.00 3.50 3.50 2.00 4.25 3.25 3.25 3.00 3.67 3.33 3.33 4.67 4.00 4.00 4.00 4.00 4.67 4.67 4.33 4.33 4.00 4.00 3.00 3.67 4.00 4.00 4.33 4.00 4.00 4.00 4.00 3.67 4.00 4.00 3.00 4.25 3.75 3.25 3.75 2.50 4.00 3.50 4.00 3.75 3.25 3.75 4.00 4.00 1.75 4.50 3.75 4.00 2.50 3.25 3.50 4.00 3.75 3.25 4.25 4.00 3.11 3.44 3.75 3.00 3.67 2.67 3.33 3.11 3.11 3.44 3.56 2.78 2.78 3.11 2.44 2.33 2.44 3.00 2.67 2.78 2.67 3.11 4.00 3.56 2.89 2.33 2.83 3.00 3.33 2.33 3.08 3.08 2.67 2.75 3.08 1.92 2.92 2.58 2.91 3.00 2.08 3.08 3.08 2.75 2.83 2.67 2.67 3.17 3.83 2.83 2.50 3.08 3.00 3.00 3.00 2.50 3.50 3.50 4.00 2.50 2.50 2.50 4.00 3.00 3.00 3.00 2.00 2.00 1.50 3.50 3.50 3.00 1.50 3.00 3.00 3.50 2.50 2.50 2.57 4.14 3.86 3.86 3.57 2.29 2.86 3.14 2.71 3.57 3.86 3.71 3.00 3.00 1.57 1.86 2.57 3.86 3.29 3.57 2.14 2.14 4.00 3.71 3.57 3.71 4.60 4.60 4.80 4.60 4.40 4.80 4.40 4.40 5.00 4.80 4.60 4.80 4.40 4.00 4.00 4.80 4.20 4.40 4.60 4.40 4.40 4.00 4.40 3.60 4.80 3.80 3.50 4.00 4.00 5.00 4.00 5.00 4.00 4.00 4.50 5.00 5.00 5.00 4.00 5.00 4.50 4.50 4.00 4.00 4.50 4.00 4.00 4.00 4.00 4.00 5.00 5.00 4.50 4.50 4.00 4.00 2.50 3.00 4.00 4.00 3.00 3.00 3.00 3.00 4.00 4.00 2.00 4.00 1.50 3.00 4.00 3.00 2.00 3.50 4.00 4.00 3.00 4.50 2.67 3.67 3.00 4.00 2.67 1.67 3.00 3.33 2.33 3.00 2.33 2.67 3.33 2.33 1.00 2.67 2.00 1.67 2.67 2.00 1.67 2.00 3.00 3.00 4.33 4.00 3.44 4.06 4.00 4.00 3.69 3.63 3.25 3.63 4.63 2.75 4.06 2.88 3.33 2.50 2.25 2.38 2.00 3.56 2.31 3.19 2.94 2.38 4.31 3.63 3.63 3.06 3.75 3.75 3.75 3.50 3.50 3.75 3.00 3.75 4.00 4.25 4.00 1.75 3.00 3.00 3.25 2.00 2.50 3.00 2.75 2.75 3.50 2.50 4.00 2.50 3.75 3.25 3.80 3.90 4.30 4.30 4.00 3.70 3.50 3.80 4.30 3.60 4.10 4.20 3.90 3.70 3.00 3.50 3.20 3.90 2.90 3.60 3.70 3.50 4.30 4.00 4.11 4.30 3.50 3.50 4.50 5.00 4.50 4.00 5.00 3.50 5.00 4.50 4.75 5.00 4.50 4.00 3.75 5.00 3.75 5.00 5.00 4.75 4.75 4.50 5.00 4.75 5.00 5.00 1 2.25 2.50 2.00 2.50 3.00 3.25 3.22 2.75 1 4.00 4.00 3.33 4.00 4.67 2.75 4.11 2.67 1 3.75 4.00 4.00 3.25 4.00 4.25 3.33 3.00 2.00 2.00 2.80 1.50 3.50 4.00 2.75 2.25 2.10 2.50 3.50 3.71 4.80 4.50 5.00 3.33 3.44 3.75 4.20 4.50 2.50 4.00 4.20 3.00 3.50 4.33 2.19 4.00 3.60 5.00 PR 1 Define correlation and describe how it's typically used. 2 Calculate the correlation between cptw & itl using the CORREL function. 3 Calculate the correlation between cptw & itl using the Correlation analysis tool in Data Analysis found in the Data tab on the Ribbon. Pl 4 What does the term best linear combination mean? 5 Use the TREND function as an ARRAY function to predict an itl value for each of the observations from the variables org to tr. Place the results in the column headed PR (for predicted). 6 Use the LINEST function to compute a basic regression analysis predicting cptw for the variables icpy to tr. Select the cells in the box below to place the results. Note LINEST is an ARRAY function. Place in the box below. 7 Create a one-way Pivot Table tabulating the frequency of the variable sex. Place in X42. 8 Create a Scatterplot (Scatter Diagram) of itl and the predicted value of itl (PR). Place in the box below. Insert a Trendline and the regression equation, including R2. The correlation of the Actual values with the Predicted values is the Multiple Correlation, R, when the Predicted values is the linear com variables. Using the CORREL function, verify this by calculating the correlation between itl and PR. Then, square the correlation. You s 9 R2 as displayed in the Scatterplot. R 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3.25 3.92 3.08 3.33 3.83 3.83 3.67 3.75 4.00 3.17 3.67 4.42 3.08 3.67 4.67 4.33 4.00 3.92 3.75 4.70 2.64 4.67 3.92 3.83 3.75 3.83 4.33 2.58 2.92 3.42 3.73 4.25 4.70 3.67 4.17 3.58 2.67 4.09 2.92 3.64 4.17 3.00 4.50 3.00 4.00 4.00 2.00 5.00 3.50 3.50 4.00 2.50 3.50 4.50 3.00 3.50 4.00 4.00 3.00 3.50 4.00 2.50 3.50 2.50 4.00 3.50 3.50 3.50 2.50 3.00 4.00 3.50 3.00 3.00 4.00 3.00 3.50 3.00 4.00 4.00 2.50 4.00 3.00 3.17 3.50 3.67 4.00 2.50 3.33 3.83 3.50 2.67 3.50 3.67 4.00 3.83 4.00 4.33 3.67 2.67 3.67 4.00 1.83 4.50 3.83 4.17 3.83 4.17 3.83 2.83 2.83 3.00 4.00 3.83 3.50 3.67 3.83 3.83 2.67 3.50 3.50 3.50 3.50 2.75 2.00 3.25 3.75 4.00 3.25 5.00 3.00 4.25 3.00 3.25 3.00 4.00 3.50 4.00 3.50 4.00 2.75 3.75 2.25 2.25 4.00 4.25 3.75 3.50 4.00 4.00 1.75 2.00 3.33 4.00 3.75 4.00 4.00 3.50 1.75 2.75 5.00 4.25 3.50 2.75 3.33 4.33 3.67 3.33 4.00 2.67 5.00 4.00 4.00 3.67 4.33 4.33 4.33 3.67 3.33 4.00 4.33 3.50 5.00 5.00 3.67 4.00 3.67 4.00 4.00 3.67 4.33 3.33 3.00 4.00 4.00 4.33 5.00 4.00 4.33 2.67 3.67 3.67 3.00 4.00 4.67 sex Gender: 1=Male; 2=Female org The Organization ini Information indi Direction inf Feedback prweWork Environment preqEquipment & Supplies prwdWork Disorganization prwcWorking Conditions prwcWorking Conditions Outcome icad Advancement icim Impact icimo Impact Outcome icpa Pay Administration icpy Pay & Benefits su Immediate Supervisor tr Training cptwCompelling Place to Work itl Intention to Leave 3.75 4.00 4.00 3.25 4.00 3.50 3.50 3.25 4.75 4.25 3.25 4.00 4.50 4.00 4.50 3.50 3.50 3.50 4.00 4.50 2.00 4.25 3.25 4.00 3.25 3.50 4.25 1.25 2.75 3.50 3.00 4.00 3.75 3.50 3.00 4.00 3.50 4.25 4.25 3.00 4.00 1.44 2.89 2.44 3.50 3.89 1.33 2.89 3.22 3.00 3.78 2.11 3.89 3.11 2.56 3.67 2.67 2.78 2.29 3.56 3.78 2.78 3.44 4.22 3.56 3.56 3.50 4.22 1.56 2.56 3.11 3.00 2.89 4.33 3.78 3.22 1.89 3.00 2.67 3.00 2.78 2.78 2.08 2.75 2.17 2.75 3.58 1.25 1.75 2.67 2.08 2.75 2.25 3.00 2.33 2.42 3.00 3.08 2.33 2.90 3.33 3.38 1.58 3.92 2.33 2.67 2.75 2.58 3.08 1.50 1.92 2.42 2.75 2.92 3.67 3.27 2.50 2.25 2.33 3.08 3.08 3.08 2.92 1.50 4.00 1.50 4.00 4.00 1.50 1.50 2.00 1.50 3.00 2.00 4.00 1.50 2.00 3.00 4.00 2.50 3.00 3.00 3.00 2.00 4.00 2.50 3.50 3.00 2.50 4.00 1.50 2.50 3.00 2.50 3.50 4.50 4.50 4.00 1.50 2.00 2.00 3.00 4.00 4.00 3.29 3.43 2.83 2.57 2.86 2.43 3.57 3.57 3.43 3.00 3.14 3.71 3.00 3.43 3.57 3.43 3.14 2.33 3.43 3.67 2.71 3.71 3.29 4.00 4.14 4.00 3.14 1.86 3.43 3.00 3.14 3.00 3.86 2.71 3.57 2.43 3.14 3.29 2.86 3.14 3.71 4.40 4.80 4.60 4.80 4.20 5.00 4.20 4.20 4.80 4.40 4.80 3.80 5.00 4.20 4.20 3.60 4.80 4.60 4.40 5.00 3.80 4.20 4.80 4.40 4.60 4.20 4.60 4.60 4.20 4.60 4.00 4.00 4.80 4.20 4.20 3.80 4.80 4.60 4.60 4.00 4.60 4.00 4.00 4.00 4.50 4.00 4.00 5.00 4.00 5.00 3.50 5.00 5.00 4.00 2.00 4.50 4.00 5.00 4.00 4.50 5.00 4.50 4.50 5.00 4.00 4.00 4.00 4.50 3.00 4.00 4.50 4.00 4.00 5.00 4.50 2.50 4.50 2.00 3.50 4.50 4.50 4.50 4.50 3.50 3.50 2.00 3.00 1.00 5.00 2.50 5.00 5.00 3.50 3.50 3.50 3.00 3.50 3.50 3.00 4.50 3.00 3.00 3.50 4.00 3.50 2.50 3.50 4.00 4.00 3.00 2.00 3.50 3.00 3.00 3.50 4.00 4.00 1.50 4.00 2.50 3.00 2.00 3.00 3.67 3.00 3.00 3.33 3.00 3.00 3.00 2.33 3.67 2.33 2.33 3.67 2.00 3.33 3.00 2.00 1.67 2.33 3.33 4.00 3.33 3.67 2.33 2.67 2.67 3.67 2.33 2.00 3.00 4.00 3.00 2.33 4.33 3.33 4.33 3.67 2.33 2.67 2.67 2.33 3.00 2.69 3.50 2.56 3.47 3.94 2.31 3.25 3.31 2.69 3.06 2.56 3.50 3.44 2.00 3.75 3.69 4.00 2.77 2.75 3.71 1.44 4.19 2.63 3.63 3.63 4.06 3.38 2.13 2.81 2.50 3.63 3.69 4.31 3.81 4.19 2.69 2.56 3.06 3.50 3.00 3.33 3.25 4.25 2.75 3.00 4.00 1.25 2.50 3.50 2.75 3.00 3.25 3.75 4.00 3.75 3.75 2.75 3.25 3.25 3.00 3.25 2.50 3.50 3.00 4.00 3.25 2.75 4.00 3.00 4.25 3.75 4.00 3.00 3.50 3.50 2.75 3.75 2.75 2.50 3.75 2.75 3.00 3.00 3.50 3.50 3.60 4.10 3.40 4.20 3.50 3.60 3.60 3.50 4.10 2.90 3.00 4.20 4.30 4.20 3.90 3.70 4.86 2.89 4.50 3.60 3.70 4.00 3.80 4.20 2.70 2.90 3.50 3.78 3.90 4.75 3.90 3.70 3.20 2.80 4.00 3.70 3.89 3.80 3.50 5.00 2.75 3.25 5.00 5.00 5.00 4.00 3.50 5.00 4.50 5.00 4.50 4.50 5.00 3.50 3.50 4.75 4.00 5.00 1.25 5.00 4.25 5.00 4.75 4.50 5.00 3.25 4.25 5.00 4.00 4.50 5.00 5.00 5.00 1.75 2.00 2.75 2.50 5.00 5.00 R2 Place the results in cell X13. mbination of two or more should get the same value for RandomNum Gender Female Female Female Female Female Female Male Female Male Female Male Female Male Male Female Female Female Female Male Male Female Male Female Female Female Female Male Female Female Male Female Female Female Female Female Male Female Male Male Female Female Female Female Female Male Male Female Female Female Female Female Female Female Male Female Female Male Female Female Female Female Female Female Male Male Female Female Male Female Male Female Female Female Female Male Female Female Male Female Race Black/African American White White Black/African American Black/African American White White Black/African American White White White Black/African American Black/African American White White White White White White White White White White White Black/African American White White White White White White White White White White White Black/African American White Black/African American White White Black/African American White White White White White White White White White Black/African American White Black/African American White White White White White White White White White White White White White White White White Black/African American White White White White White White White White The data columns B & C constitute the population of individuals in a retail clerk job in a large company. The data were collected for EEO (Equal Employment Opportunity) reporting purposes. See the comment in cell A1 for instructions. 1 Using Pivot Tables create Gender by Race crosstabulation of the data in Columns B & C. Place the table in cell F7. 2 Is the association between Gender and Race statistically significant? Use one of the statistical calculators located on the web to make this determination. http://vassarstats.net/newcs.html Chi Square p-level Statistically significant? 3 Assume that you did not have access to the population of data in Column A, but had to select a random sample on which to calculate your association statistic. Assign a random number to the observations, then select the 30 lowest random numbers for your sample. Calculate the association statistics on this sample. Chi Square p-level Statistically significant? Fisher's Exact Probability 4 Create a gender by Race crosstabulation for the sample identified in Item 3. Place in F54. 5 Define the Yule-Simpson effect and show an example of it. Female Female Female Female Male Female Female Male Female Female Female Female Female Female Female Female Male Male Female Male Female Male Female Female Female Female Female Female Male Male Female Male Female Female Female Female Female Male Female Male Male Male Female Male Female Female Male Female Female Female Male Male Male Female Female Male Male Male Male Female Female Male Female Female Female Female Female Female Male Male Female Male Male Female Male Female Female Female Female Male Female White White White White White White Black/African American Black/African American White White White White White White White Black/African American White White White White White White White White White White Black/African American White White White White White White White White White White White White White Black/African American White Black/African American White White Black/African American White White White White White Black/African American White Black/African American White White White White White Black/African American White White White White White White White White White White White Black/African American White Black/African American White White White Black/African American White White White Male Male Male Male Male Female Male Female Female Female Male Male Female Male Male Female Female Female Male Female Female Female Female Male Female Female Female Female Female Male Male Female Female Male Male Female Male Female Female Female Female Female Male Female Male Female Female Male Female Male Male Female Female Male Female Female Female Female Female Female Female Female Female Female Male Female Female Female Female Female Male Male Male Female Female Male Female Male Female Female Female White White White White White White White White White White White White White White White Black/African American White White Black/African American White White White White White White White White White Black/African American White White White White White White White Black/African American White White White White White White White White Black/African American Black/African American White White White White White Black/African American White White White White White Black/African American Black/African American Black/African American White White Black/African American White White White White White Black/African American White White Black/African American White White White White White White White Black/African American Female Female Female Male Female Female Female Female Female Male Female Female Female Female Female Female Female Female Male Female Female Male Female Female Male Male Male Male Female Male Female Male Female Female Female Female Female Female Male Male Female Female Female Male Male Male Male Female Female Female Female Female Female Female Female Female Female Female Female Female Female Female Male Female Male Female Male Female Male Female Female Female Male Female Male Female Female Female Female Male Male White White Black/African American White White White White White White White White White White White White White White White White White White Black/African American White White White Black/African American White Black/African American White White White White White White White White Black/African American White White Black/African American White White White Black/African American White White White Black/African American White White White White White White White White Black/African American White White White White Black/African American Black/African American White White White White White White White White White White White White Black/African American White White Black/African American White White Female Male Female Female Female Male Female Female Male Male Female Male Female Female Female Male Male Female Male Male Female Female Female Female Male Male Female Male Male Female Female Female Male Female Male Female Female Male Female Male Female Female Female Female Female Male Female Female Female Male Female Male Female Female Female Male Female Male Male Female Male Female Male Female Female Female Black/African American Black/African American White Black/African American White White White White White White Black/African American White White Black/African American White White White White White White White White White White White White White White White White White White White White White White White Black/African American White Black/African American White White White Black/African American White White White White White White White White Black/African American White Black/African American White White White White White White Black/African American White Black/African American Black/African American White 1 What are the characteristics of the normal distribution? 2 What is the unit normal or standard normal distribution? 3 Describe the function NORM.DIST. Provide an example in H28. 4 Describe the function NORM.S.DIST. Provide an example in H43. 5 Describe the function NORM.INV. Provide an example in H52. 6 Describe the function NORM.S.INV. Provide an example in H66. 7 What is a confidence interval and how does it relate to the normal distribution? 8 Calculate a 95% confidence interval for a sample size=30 when the population mean (50) and the population standard deviation (10) are known. 9 Calculate a 99% confidence interval for a sample size=30 with mean of 50 and a standard deviation of 10, but the population mean and the population standard deviation 10a What is a controlling factor in the range of the confidence interval? 11 Define the Central Limit Theorem 12 Explain the rationale for testing the difference between means. 13 Compute a z-test. Prices $ 45.23 $ 35.48 Sample Mean $ 36.57 Null hypothesis (H0:) $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 43.22 42.94 37.11 44.05 44.96 42.99 40.23 50.93 36.26 51.91 37.03 40.12 41.59 40.18 61.40 40.51 40.17 49.93 61.61 36.93 45.39 41.56 40.93 50.49 43.03 40.13 52.97 Alternative hypothesis (Ha:) p-value z of p-value 10b Compute the upper and lower confidence interval limits for a sample with a mean =50 for the following two sample sizes. N=30 N=100 Upper limit Lower limit See the following video for an explanation of the Central Limit Theorem. https://www.youtube.com/watch?v=JNm3M9cqWyc $ $ $ $ $ $ $ $ $ $ 42.10 30.31 54.16 46.67 43.32 31.88 64.60 45.71 58.27 31.94 14 Compute a t-test comparing the two samples. State 1 State 2 283 229 254 267 328 326 292 309 315 231 336 283 378 344 314 310 312 258 328 316 272 241 307 281 348 218 233 284 354 311 400 254 341 217 313 267 309 299 308 266 340 264 300 264 316 290 268 312 259 298 276 305 271 244 362 303 340 299 339 285 300 308 333 260 204 291 242 329 315 246 322 293 15 What is the decision rule? 16 What is statistical power? ation are unknown. =50 and a sample standard deviation = 10 1 What's the difference in directional (one-tailed) and non-directional (two-tailed) hypotheses in statistical tests? 2 Compute a t-test comparing the two samples. State 1 State 2 283 229 254 267 328 326 292 309 315 231 336 283 378 344 314 310 312 258 328 316 272 241 307 281 348 218 233 284 354 311 400 254 341 217 313 267 309 299 308 266 340 264 300 264 316 290 268 312 259 298 276 305 271 244 362 303 340 299 339 285 300 308 333 260 204 291 242 329 315 246 322 293 3 Discuss the impact of unequal group (sample) sizes on hypothesis tests. 4 Use the Data Analysis t-test tool to compare the mean for Males vs. Females. Male Female 4.80 4.86 Specify the null and alternate hypotheses. 3.70 2.89 3.50 4.50 3.20 3.60 3.80 3.70 4.00 4.00 4.40 3.80 Are the Male and Female means 3.40 4.20 significantly different? 4.20 2.70 A for Yes 3.90 2.90 B for No 3.80 3.50 4.20 3.78 3.90 3.90 4.00 4.75 3.38 3.90 4.00 3.70 3.30 3.20 3.50 2.80 3.40 4.00 3.60 3.70 3.50 3.89 3.70 3.80 4.00 4.30 3.00 2.60 4.30 4.20 4.00 3.70 2.60 4.60 3.30 4.60 4.10 3.00 3.00 4.40 3.90 2.70 4.30 3.50 3.50 4.30 3.80 3.60 3.60 3.80 3.90 4.30 4.30 4.00 3.70 3.50 3.80 4.30 3.60 4.10 4.20 3.90 3.70 3.00 3.50 3.20 3.90 2.90 3.60 3.70 3.50 4.30 4.00 4.11 4.30 2.10 4.20 3.60 3.00 3.50 3.50 3.60 4.10 3.40 4.20 3.50 3.60 3.60 3.50 4.10 2.90 3.00 4.20 4.30 4.20 3.90 3.70 5 What is multiple regression versus single regression? sex 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 org 4.92 3.67 3.25 3.17 3.67 3.75 4.42 3.67 4.67 3.75 4.08 3.83 4.00 4.00 2.92 3.92 3.17 4.08 3.75 3.58 3.42 4.67 4.00 4.33 3.42 2.25 4.42 4.33 4.00 3.83 2.50 4.75 3.58 4.67 4.17 ini 4.50 4.00 3.00 3.00 3.50 3.50 4.00 4.00 3.50 4.00 3.00 4.00 2.50 3.50 3.00 2.50 3.00 4.50 4.00 4.00 3.50 4.00 4.00 4.00 2.50 3.00 3.50 3.50 3.50 3.00 3.50 4.50 3.50 4.50 4.00 indi 5.00 3.67 3.50 3.00 3.83 3.33 4.00 3.83 4.00 3.83 4.50 3.67 2.50 3.17 3.00 3.33 3.67 4.00 3.67 3.67 3.33 4.67 3.50 4.33 3.50 2.00 4.00 4.00 3.50 3.00 3.50 4.67 2.50 4.67 4.00 inf 4.75 2.00 2.75 2.25 2.75 3.75 3.33 3.75 2.75 4.00 3.75 2.75 3.50 3.00 3.00 3.75 2.25 3.50 3.75 3.75 3.75 4.50 3.50 4.50 2.25 2.00 4.00 3.50 3.75 2.50 3.25 4.50 2.75 4.75 4.25 prwe 4.67 4.33 4.33 4.00 4.00 3.33 4.67 4.00 4.00 4.00 4.00 4.00 3.33 4.00 4.00 3.67 4.00 4.00 4.00 4.33 3.67 4.33 4.33 4.00 4.67 2.33 4.00 4.67 4.00 4.33 4.00 5.00 4.00 4.67 4.33 preq 5.00 4.25 4.50 4.00 3.75 2.75 3.75 4.00 3.25 3.75 3.75 4.00 4.00 4.00 3.50 3.75 3.25 4.00 3.75 3.75 3.75 4.00 3.75 4.00 2.50 3.25 4.00 4.00 3.75 4.00 4.00 4.50 3.25 3.75 4.00 prwd 4.56 3.22 2.38 3.56 3.44 3.56 3.11 3.11 3.67 3.00 2.78 3.67 2.33 3.33 2.67 2.89 3.11 3.67 2.78 2.56 2.67 2.89 2.67 3.78 2.67 3.00 3.56 3.56 3.25 3.11 3.11 4.00 2.33 3.67 2.56 prwc 3.92 3.00 2.33 2.92 3.25 3.00 3.50 2.75 3.91 2.25 1.92 3.08 2.17 3.42 2.44 2.33 2.92 2.92 1.92 2.67 2.58 2.00 2.92 3.17 1.67 2.25 3.00 2.92 2.58 2.75 2.25 2.42 2.58 2.92 2.50 prwco 4.50 3.00 2.00 2.50 2.00 3.50 4.50 3.00 5.00 2.50 3.00 3.00 2.50 3.00 2.50 3.00 2.50 3.00 2.00 2.00 2.50 2.50 2.50 3.00 1.50 1.50 3.00 2.50 3.00 2.50 2.00 2.50 2.00 2.50 2.50 icad 4.86 3.43 3.14 2.86 2.00 3.43 4.14 3.43 3.71 3.71 3.29 3.57 3.71 2.86 3.60 2.71 3.00 3.71 3.00 3.86 3.14 3.71 3.29 3.57 2.57 2.71 4.71 3.71 3.71 3.57 3.14 4.29 2.86 4.00 4.71 icim 5.00 4.40 3.80 4.20 4.80 3.80 3.60 4.00 4.20 4.60 4.80 4.60 4.80 4.60 4.00 4.60 3.40 4.60 4.20 4.60 4.60 4.80 4.20 4.20 4.40 4.60 4.80 5.00 4.00 4.60 4.20 5.00 4.00 4.40 5.00 icimo 5.00 4.00 3.00 3.00 5.00 4.00 4.50 4.00 4.50 4.50 4.00 4.00 4.50 4.50 4.00 4.00 3.50 4.00 4.00 5.00 4.50 4.50 4.00 4.50 3.50 5.00 4.50 5.00 4.00 3.50 3.00 5.00 4.00 5.00 4.50 icpa 4.50 2.50 3.00 3.50 2.50 4.00 3.50 4.50 4.00 5.00 4.50 2.50 2.00 4.50 3.00 3.50 3.00 2.50 3.50 3.00 3.50 4.50 3.00 4.50 3.00 3.00 4.50 2.50 3.50 3.00 3.50 4.00 3.00 4.00 3.00 icpy 4.33 3.67 1.33 3.00 2.00 3.00 4.33 3.33 4.33 3.33 3.33 3.00 1.33 2.67 3.67 2.00 3.67 3.67 2.33 3.00 2.67 3.33 2.67 3.67 2.33 3.00 4.00 3.67 4.00 2.33 2.67 3.67 2.67 4.00 3.00 su 4.56 2.50 2.75 3.00 1.63 3.50 4.21 3.50 3.88 3.88 3.31 3.50 3.69 2.94 2.71 2.31 2.50 2.56 3.25 2.94 3.47 3.00 3.25 4.00 2.50 1.69 4.31 2.93 3.44 3.56 3.06 4.00 2.13 4.06 3.81 tr 4.75 3.75 3.50 3.75 3.75 3.25 4.00 4.00 3.00 3.00 3.25 3.75 2.75 3.50 2.33 3.00 3.00 4.50 3.25 3.00 3.25 3.50 3.00 3.00 1.25 3.00 4.00 3.75 3.25 3.50 3.25 4.50 2.50 4.25 3.25 cptw 4.80 3.70 3.50 3.20 3.80 4.00 4.40 3.40 4.20 3.90 3.80 4.20 3.90 4.00 3.38 4.00 3.30 3.50 3.40 3.60 3.50 3.70 4.00 4.30 3.00 2.60 4.30 4.20 4.00 3.70 2.60 4.60 3.30 4.60 4.10 itl 5.00 5.00 3.25 4.00 4.00 4.75 3.00 4.75 5.00 5.00 4.75 4.50 4.00 4.50 4.00 3.50 5.00 5.00 4.00 4.00 4.00 5.00 5.00 5.00 3.00 2.75 5.00 5.00 4.75 4.75 4.25 5.00 3.50 5.00 5.00 1 1 1 1 1 1 1 1 1 1 1 1 2.83 2.50 4.75 3.50 2.58 4.42 4.00 3.67 4.25 3.50 3.92 3.33 3.50 3.00 2.50 3.00 4.00 4.00 3.50 4.00 3.50 3.50 3.00 3.50 2.67 3.00 4.50 3.40 1.83 4.17 3.83 3.67 3.80 3.17 3.33 3.17 3.75 2.50 4.25 3.25 3.00 3.00 3.75 4.25 4.00 2.50 3.25 2.75 3.67 3.33 4.00 3.67 4.33 4.33 3.67 3.33 4.00 3.67 3.33 4.33 2.75 3.25 4.25 4.00 4.50 4.75 4.00 4.50 3.50 4.25 3.25 4.00 1.89 2.44 4.67 2.67 2.00 3.75 3.11 2.89 3.33 3.33 3.11 2.56 1.92 1.83 2.58 2.42 2.25 4.17 2.67 3.08 3.42 2.33 2.50 2.50 2.50 1.50 4.50 2.50 1.50 4.00 2.50 3.00 4.00 2.00 3.00 3.00 2.71 2.57 4.14 2.00 2.71 3.83 3.29 3.14 3.86 3.14 3.57 3.29 4.40 4.60 5.00 4.80 4.80 4.80 4.20 3.80 4.20 4.40 4.40 4.60 3.00 3.00 5.00 5.00 4.00 5.00 3.50 4.00 5.00 4.00 4.00 4.50 3.50 3.50 5.00 4.00 4.00 3.00 4.50 1.50 4.50 4.00 4.50 3.00 2.67 2.00 3.67 4.00 3.33 4.33 3.33 3.33 3.67 3.00 3.67 3.33 3.25 3.06 4.00 3.23 1.56 3.73 3.94 3.06 3.75 3.06 3.38 2.88 3.00 1.50 2.50 2.00 3.25 3.67 2.75 3.00 3.75 4.00 3.50 3.00 3.00 3.00 4.40 3.90 2.70 4.30 3.50 3.50 4.30 3.80 3.60 3.60 4.00 3.75 5.00 3.50 3.50 4.25 3.50 4.25 5.00 4.00 5.00 5.00 6.00 Using the Data An After the analysis GO TO CELL 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 sex org ini indi inf prwe preq prwd prwc prwco 3.92 3.92 3.92 4.67 4.00 3.08 3.58 4.42 4.33 4.00 3.92 4.08 3.67 3.67 2.42 3.17 3.50 4.25 2.92 3.75 3.50 3.42 4.42 3.58 3.91 4.42 2.25 4.00 3.75 3.25 3.92 3.08 3.33 3.83 3.83 3.67 3.75 4.00 3.17 3.67 4.42 3.08 3.67 4.67 4.33 4.00 3.92 3.75 4.70 2.64 4.67 3.92 3.83 3.75 3.83 4.33 2.58 2.92 3.42 3.73 4.25 4.70 3.67 4.17 3.58 2.67 4.09 2.92 3.64 4.17 3.50 4.00 4.00 4.00 3.50 3.50 3.50 4.00 3.50 2.00 4.00 4.00 2.00 4.00 3.00 4.00 4.00 3.50 4.00 4.00 3.50 3.50 4.00 3.50 4.50 3.00 2.50 4.00 4.00 3.00 4.50 3.00 4.00 4.00 2.00 5.00 3.50 3.50 4.00 2.50 3.50 4.50 3.00 3.50 4.00 4.00 3.00 3.50 4.00 2.50 3.50 2.50 4.00 3.50 3.50 3.50 2.50 3.00 4.00 3.50 3.00 3.00 4.00 3.00 3.50 3.00 4.00 4.00 2.50 4.00 3.67 3.33 3.50 4.00 4.00 2.33 3.33 4.17 4.50 3.50 4.00 2.50 3.33 2.67 3.83 3.00 3.33 4.33 3.50 3.67 3.17 3.00 4.17 3.00 3.33 2.83 2.00 3.33 4.00 3.00 3.17 3.50 3.67 4.00 2.50 3.33 3.83 3.50 2.67 3.50 3.67 4.00 3.83 4.00 4.33 3.67 2.67 3.67 4.00 1.83 4.50 3.83 4.17 3.83 4.17 3.83 2.83 2.83 3.00 4.00 3.83 3.50 3.67 3.83 3.83 2.67 3.50 3.50 3.50 3.50 Gender: 1=Male; 2=Female The Organization Information Direction Feedback Work Environment Equipment & Supplies Work Disorganization Working Conditions Working Conditions Outcome 3.75 3.75 3.75 3.50 4.00 1.00 3.50 4.25 3.75 3.75 3.25 2.75 3.00 2.25 2.50 2.75 1.00 4.00 4.00 3.50 3.50 2.00 4.25 3.25 3.25 3.00 2.50 4.00 3.25 2.75 2.00 3.25 3.75 4.00 3.25 5.00 3.00 4.25 3.00 3.25 3.00 4.00 3.50 4.00 3.50 4.00 2.75 3.75 2.25 2.25 4.00 4.25 3.75 3.50 4.00 4.00 1.75 2.00 3.33 4.00 3.75 4.00 4.00 3.50 1.75 2.75 5.00 4.25 3.50 2.75 3.67 3.33 3.33 4.67 4.00 4.00 4.00 4.00 4.67 4.67 4.33 4.33 4.00 4.00 3.00 3.67 4.00 4.00 4.33 4.00 4.00 4.00 4.00 3.67 4.00 4.00 3.00 4.67 4.00 3.33 4.33 3.67 3.33 4.00 2.67 5.00 4.00 4.00 3.67 4.33 4.33 4.33 3.67 3.33 4.00 4.33 3.50 5.00 5.00 3.67 4.00 3.67 4.00 4.00 3.67 4.33 3.33 3.00 4.00 4.00 4.33 5.00 4.00 4.33 2.67 3.67 3.67 3.00 4.00 4.67 3.00 4.25 3.75 3.25 3.75 2.50 4.00 3.50 4.00 3.75 3.25 3.75 4.00 4.00 1.75 4.50 3.75 4.00 2.50 3.25 3.50 4.00 3.75 3.25 4.25 4.00 3.25 2.75 4.25 3.75 4.00 4.00 3.25 4.00 3.50 3.50 3.25 4.75 4.25 3.25 4.00 4.50 4.00 4.50 3.50 3.50 3.50 4.00 4.50 2.00 4.25 3.25 4.00 3.25 3.50 4.25 1.25 2.75 3.50 3.00 4.00 3.75 3.50 3.00 4.00 3.50 4.25 4.25 3.00 4.00 3.11 3.44 3.75 3.00 3.67 2.67 3.33 3.11 3.11 3.44 3.56 2.78 2.78 3.11 2.44 2.33 2.44 3.00 2.67 2.78 2.67 3.11 4.00 3.56 2.89 2.33 3.22 4.11 3.33 1.44 2.89 2.44 3.50 3.89 1.33 2.89 3.22 3.00 3.78 2.11 3.89 3.11 2.56 3.67 2.67 2.78 2.29 3.56 3.78 2.78 3.44 4.22 3.56 3.56 3.50 4.22 1.56 2.56 3.11 3.00 2.89 4.33 3.78 3.22 1.89 3.00 2.67 3.00 2.78 2.78 2.83 3.00 3.33 2.33 3.08 3.08 2.67 2.75 3.08 1.92 2.92 2.58 2.91 3.00 2.08 3.08 3.08 2.75 2.83 2.67 2.67 3.17 3.83 2.83 2.50 3.08 2.75 2.67 3.00 2.08 2.75 2.17 2.75 3.58 1.25 1.75 2.67 2.08 2.75 2.25 3.00 2.33 2.42 3.00 3.08 2.33 2.90 3.33 3.38 1.58 3.92 2.33 2.67 2.75 2.58 3.08 1.50 1.92 2.42 2.75 2.92 3.67 3.27 2.50 2.25 2.33 3.08 3.08 3.08 2.92 3.00 3.00 3.00 2.50 3.50 3.50 4.00 2.50 2.50 2.50 4.00 3.00 3.00 3.00 2.00 2.00 1.50 3.50 3.50 3.00 1.50 3.00 3.00 3.50 2.50 2.50 2.00 3.50 2.50 1.50 4.00 1.50 4.00 4.00 1.50 1.50 2.00 1.50 3.00 2.00 4.00 1.50 2.00 3.00 4.00 2.50 3.00 3.00 3.00 2.00 4.00 2.50 3.50 3.00 2.50 4.00 1.50 2.50 3.00 2.50 3.50 4.50 4.50 4.00 1.50 2.00 2.00 3.00 4.00 4.00 2.57 4.14 3.86 3.86 3.57 2.29 2.86 3.14 2.71 3.57 3.86 3.71 3.00 3.00 1.57 1.86 2.57 3.86 3.29 3.57 2.14 2.14 4.00 3.71 3.57 3.71 2.00 3.71 4.00 3.29 3.43 2.83 2.57 2.86 2.43 3.57 3.57 3.43 3.00 3.14 3.71 3.00 3.43 3.57 3.43 3.14 2.33 3.43 3.67 2.71 3.71 3.29 4.00 4.14 4.00 3.14 1.86 3.43 3.00 3.14 3.00 3.86 2.71 3.57 2.43 3.14 3.29 2.86 3.14 3.71 4.60 4.60 4.80 4.60 4.40 4.80 4.40 4.40 5.00 4.80 4.60 4.80 4.40 4.00 4.00 4.80 4.20 4.40 4.60 4.40 4.40 4.00 4.40 3.60 4.80 3.80 2.80 4.80 4.20 4.40 4.80 4.60 4.80 4.20 5.00 4.20 4.20 4.80 4.40 4.80 3.80 5.00 4.20 4.20 3.60 4.80 4.60 4.40 5.00 3.80 4.20 4.80 4.40 4.60 4.20 4.60 4.60 4.20 4.60 4.00 4.00 4.80 4.20 4.20 3.80 4.80 4.60 4.60 4.00 4.60 3.50 4.00 4.00 5.00 4.00 5.00 4.00 4.00 4.50 5.00 5.00 5.00 4.00 5.00 4.50 4.50 4.00 4.00 4.50 4.00 4.00 4.00 4.00 4.00 5.00 5.00 1.50 4.50 3.00 4.00 4.00 4.00 4.50 4.00 4.00 5.00 4.00 5.00 3.50 5.00 5.00 4.00 2.00 4.50 4.00 5.00 4.00 4.50 5.00 4.50 4.50 5.00 4.00 4.00 4.00 4.50 3.00 4.00 4.50 4.00 4.00 5.00 4.50 2.50 4.50 2.00 3.50 4.50 4.50 4.50 4.50 4.50 4.00 4.00 2.50 3.00 4.00 4.00 3.00 3.00 3.00 3.00 4.00 4.00 2.00 4.00 1.50 3.00 4.00 3.00 2.00 3.50 4.00 4.00 3.00 4.50 3.50 5.00 3.50 4.50 3.50 3.50 2.00 3.00 1.00 5.00 2.50 5.00 5.00 3.50 3.50 3.50 3.00 3.50 3.50 3.00 4.50 3.00 3.00 3.50 4.00 3.50 2.50 3.50 4.00 4.00 3.00 2.00 3.50 3.00 3.00 3.50 4.00 4.00 1.50 4.00 2.50 3.00 2.00 3.00 2.67 3.67 3.00 4.00 2.67 1.67 3.00 3.33 2.33 3.00 2.33 2.67 3.33 2.33 1.00 2.67 2.00 1.67 2.67 2.00 1.67 2.00 3.00 3.00 4.33 4.00 4.00 3.33 4.33 3.67 3.00 3.00 3.33 3.00 3.00 3.00 2.33 3.67 2.33 2.33 3.67 2.00 3.33 3.00 2.00 1.67 2.33 3.33 4.00 3.33 3.67 2.33 2.67 2.67 3.67 2.33 2.00 3.00 4.00 3.00 2.33 4.33 3.33 4.33 3.67 2.33 2.67 2.67 2.33 3.00 3.44 4.06 4.00 4.00 3.69 3.63 3.25 3.63 4.63 2.75 4.06 2.88 3.33 2.50 2.25 2.38 2.00 3.56 2.31 3.19 2.94 2.38 4.31 3.63 3.63 3.06 2.75 3.44 2.19 2.69 3.50 2.56 3.47 3.94 2.31 3.25 3.31 2.69 3.06 2.56 3.50 3.44 2.00 3.75 3.69 4.00 2.77 2.75 3.71 1.44 4.19 2.63 3.63 3.63 4.06 3.38 2.13 2.81 2.50 3.63 3.69 4.31 3.81 4.19 2.69 2.56 3.06 3.50 3.00 3.33 3.75 3.75 3.75 3.50 3.50 3.75 3.00 3.75 4.00 4.25 4.00 1.75 3.00 3.00 3.25 2.00 2.50 3.00 2.75 2.75 3.50 2.50 4.00 2.50 3.75 3.25 2.25 3.75 4.00 3.25 4.25 2.75 3.00 4.00 1.25 2.50 3.50 2.75 3.00 3.25 3.75 4.00 3.75 3.75 2.75 3.25 3.25 3.00 3.25 2.50 3.50 3.00 4.00 3.25 2.75 4.00 3.00 4.25 3.75 4.00 3.00 3.50 3.50 2.75 3.75 2.75 2.50 3.75 2.75 3.00 3.80 3.90 4.30 4.30 4.00 3.70 3.50 3.80 4.30 3.60 4.10 4.20 3.90 3.70 3.00 3.50 3.20 3.90 2.90 3.60 3.70 3.50 4.30 4.00 4.11 4.30 2.10 4.20 3.60 3.00 3.50 3.50 3.60 4.10 3.40 4.20 3.50 3.60 3.60 3.50 4.10 2.90 3.00 4.20 4.30 4.20 3.90 3.70 4.86 2.89 4.50 3.60 3.70 4.00 3.80 4.20 2.70 2.90 3.50 3.78 3.90 4.75 3.90 3.70 3.20 2.80 4.00 3.70 3.89 3.80 3.50 3.50 4.50 5.00 4.50 4.00 5.00 3.50 5.00 4.50 4.75 5.00 4.50 4.00 3.75 5.00 3.75 5.00 5.00 4.75 4.75 4.50 5.00 4.75 5.00 5.00 2.50 4.50 5.00 3.50 5.00 2.75 3.25 5.00 5.00 5.00 4.00 3.50 5.00 4.50 5.00 4.50 4.50 5.00 3.50 3.50 4.75 4.00 5.00 1.25 5.00 4.25 5.00 4.75 4.50 5.00 3.25 4.25 5.00 4.00 4.50 5.00 5.00 5.00 1.75 2.00 2.75 2.50 5.00 5.00 icad icim icimo icpa icpy su tr cptw itl Advancement Impact Impact Outcome Pay Administration Pay & Benefits Immediate Supervisor Training Compelling Place to Work Intention to Leave 7 Recompute the multiple regression using the independent variables with a p-value <= .05 in the full regression model. Place the results in J339. nalysis Regression tool compute a multiple regression using all the variables org to tr to predict cptw. Place the results in V198. is complete go to cell B337 for further instructions. B337 1 What are the purposes of accounting? 2 How is the income statement typically used? 3 What is the relationship between the General Journal, the General Ledger and the Income Statement and the Balance Sheet? 4 How is accrual accounting used to manage financial analysis? 5 Contrast the Traditional and Contribution approaches to organizing income statements. 6 Arrange the following data for the ABC Company into a balance sheet. Begin the Balance Sheet in cell H73. Cash Inventory Accounts Receivable Prepaid Expenses Other Current Assets Equipment (less depreciation) Leasehold Improvements (less depr) Other Fixed Assets 209750 235750 190600 12000 0 403000 223500 Accounts Payable Notes Payable Line of Credit Payable Taxes Payable Other Current Liabilities Long Term Debt Other Long Term Liabilities Owner's Capital Contribution Less Owners Draw Retained Earnings 116300 0 51500 24000 0 280050 0 600000 93000 295750 7 How is a current asset cash balance determined? Demonstrate with the cash worksheet below by calculating the daily balance from the beginning to the end of the month. Date Explanation Debit 11/30/11 Closing balance, November 12/1/11 Purchase medical insurance policy 12/1/11 Purchase of office supplies 12/4/11 Cash Receipts $ 4,690 12/4/11 Check for returns to supplier $ 91 12/7/11 Cash Receipts $ 1,006 12/11/11 Cash Receipts $ 8,207 12/14/11 Cash Receipts $ 9,592 12/14/11 Purchase of books from Neal Publishing 12/14/11 Purchase of books from Lenny Distributing 12/18/11 Cash Receipts $ 4,663 12/18/11 Accounts Receivable payment fo $ 17,951 12/21/11 Cash Receipts $ 5,514 12/23/11 Cash Receipts $ 3,791 12/27/11 Telephone bill, November 12/27/11 Cash Receipts $ 9,050 12/27/11 Purchase of books from Neal Publishing 12/29/11 Salary check, Rodgers 12/29/11 Salary check, Rouse 12/29/11 Salary check, Tafoya 12/29/11 Advertising bill, November 12/30/11 Cash Receipts $ 6,841 Credit Balance $ 29,344 $ $ 6,864 3,194 $ $ 6,023 8,474 $ 1,835 $ $ $ $ $ 6,440 2,950 2,761 4,377 3,116 8 How is a current asset accounts receivable balance determined? Demonstrate with the data below by calculating the daily balance from the beginning to the end of the month. The accounts receivable balance for a given time is the balance from the previous time (e.g., month) plus debits to the account less credits to the account. Date Accounts Receivable: Explanat Debit 11/30/11 Closing balance, November 12/1/11 Credit sales 12/1/11 Credit sales 12/4/11 Credit sales 12/4/11 Credit sales 12/7/11 Credit sales 12/11/11 Credit sales 12/14/11 Credit sales 12/14/11 Credit sales 12/14/11 Credit sales Payment from service bureau, 12/18/11 11/11 charges 12/21/11 Credit sales 12/23/11 Credit sales 12/27/11 Credit sales Credit Balance $ $ $ $ $ $ $ $ $ $ 1,127 1,258 497 288 187 977 1,236 454 855 $ $ $ 882 789 1,337 $ 17,951 18,827 12/27/11 Credit sales 12/27/11 Credit sales 12/29/11 Credit sales 12/29/11 Credit sales 12/29/11 Credit sales 12/29/11 Credit sales 12/30/11 Credit sales $ $ $ $ $ $ $ 392 856 1,291 1,418 390 1,337 1,277 9 How are prepaid expenses handled on a balance sheet? Calculate the Current Balance for the data below. Asset Account Date Medical Insurance Explanation Debit 12/1/2011 Purchase of medical policy $ 12/31/2011 Expiration of one month coverage Credit 6,864 $ 1,144 Balance How are costs and revenues matched? 10 For the following data, how would the recording of expenses differ if Office Supplies were allocated over 12 months and the Personal Computer were depreciated (expense) over 3 years? What is the impact on net income? Income Statement 1/31/2010 Marble Designs, Inc. Before Sales After $10,000 Expenses Salary Office lease Telephone Office supplies Purchase: Computer $3,500 $900 $96 $2,000 $1,950 10a. Impact on net income: Percent increase/decrease? Total Expenses $8,446 Net Income $1,554 11 How do cash and working capital differ? 12 Calculate working capital for the data below. Income Statement $40,209 Revenues Sales Less Cost of Goods Sold Beginning Inventory Purchases Ending inventory Gross profit $0 $2,000 $1,500 Changes to Balance Sheet, 1/1 - 1/31 Assets $40,179 $10,000 Cash $9,000 Accounts Receivable Inventory Office Supplies $500 Computer Accumulated depreciation $9,500 $9,000 Operating Expenses Salaries Office Lease Telephone Office supplies Depreciation, Computer $3,500 Liabilities and Owner's Equity $900 $96 Notes payable $167 Jean Marble, equity $9,000 $54 Net income $4,783 $9,000 $40,209 $2,554 $8,000 $1,500 $1,833 $1,950 -$54 $15,783 $2,000 $13,783 $15,783 Working capital --> An analysis of cash flow can help determine how a company uses its cash assets. It is used to acquire assets, meet 13 liabilities, retire debt etc. The starting point of a cash flow analysis is a review of the income statement and balance sheet. Calculate the working capital for the two years shown. Income Statement: Jan '14 - Jan '15 Changes to Balance Sheet, 1/31/14 - 1/31/15 Assets 1/31/2010 Revenues Sales 1/31/2011 $2,554 $8,000 $1,500 $1,833 $1,950 $17,783 $5,500 $2,000 $1,833 $1,950 $2,320 -$1,091 $30,295 Gross profit $90,000 Cash Accounts Receivable Inventory Office Supplies $24,500 Computer Computer $65,500 Accumulated depreciation Operating Expenses Salaries Office Lease Telephone Office supplies Depreciation, Computers $42,000 Liabilities and Owner's Equity $10,800 $1,151 Notes payable $2,000 $2,000 Jean Martin, equity $13,783 $1,037 Beginning inventory Purchases Ending inventory Less Cost of Goods Sold $1,500 $25,000 $2,000 Gain on sale of office Net income 14 Considering the above income statement and balance sheet: What was the value of the materials in inventory at the beginning of the period? 15 How much additional inventory was purchased during the year? 16 How much inventory was used during the year? 17 Calculate the working capital for both month end periods. 18 What was the cash on hand at the end of Jan 2015? $5,000 $13,512 Working capital -$54 $15,783 $15,783 $3,000 $27,295 $446,711 1 What is statement analysis? The process of reviewing and evaluating a company's financial statements (such as the balance sheet or profit and loss statement), thereby gaining an understanding of the financial health of the company and enabling more effective decision making. Financial statements record financial data; however, this information must be evaluated through financial statement analysis to become more useful to investors, shareholders, managers and other interested parties. http://www.investopedia.com/terms/f/financial-statement-analysis.asp 2 What does it mean to common-size a report? A common-size financial statement is simply one that is created to display line items on a statement as a percentage of one selected or common figure. Creating common-size financial statements makes it easier to analyze a company over time and compare it with peers. Using common-size financial statements helps investors spot trends that a raw financial statement may not uncover. http://www.investopedia.com/articles/investing/111413/commonsize-analysis-financial-statements.asp 3 The following is a balance sheet displaying two year's data on a company. Common-size the report. Place results in the columns headed Common-sized. Calculate the Dollar Change and the Percent Change from 2012 to 2013. Place results in the Dollar Chg & Pct Chg columns. 2013 2012 260,000 240,000 60,000 120,000 60,000 100,000 440,000 400,000 Current assets Inventories Trade receivables 24,000 50,000 20,000 60,000 Cash and cash equivalents 16,000 20,000 90,000 100,000 530,000 500,000 Equity Share capital Retained earnings Revaluation reserve 200,000 100,000 30,000 200,000 80,000 20,000 Total equity 330,000 300,000 70,000 100,000 70,000 20,000 50,000 16,000 30,000 30,000 10,000 4,000 130,000 200,000 100,000 200,000 530,000 Common-sized 2013 2012 500,000 ASSETS Non-current assets Property, plant & equipment Goodwill Intangible assets TOTAL ASSETS EQUITY AND LIABILITIES Non-current liabilities Long term borrowings Current liabilities Trade and other payables Short-term borrowings Current portion of longterm borrowings Current tax payable Total current liabilities Total liabilities TOTAL EQUITY AND LIABILITIES 4 What is Variance Analysis? 5 The following shows budget figures and actual figures (profit & expense) for a month. Compute the percentages and variances indicated. Follow the instructions in the comment boxes. Budget Gross profit Actual Variance Jan 42,589 Jan 32,845 Jan Dollar Chg Pct Chg Salaries Payroll taxes Lease Phone Supplies Insurance Total OpEx EBITDA: Actual EBITDA: Plan 20,000 5,040 1,000 500 300 500 27,340 15,249 27,520 6,935 1,000 562 142 500 36,659 -3,814 Gross profit Salaries Payroll taxes Lease Phone Supplies Insurance Total OpEx EBITDA 6 Considering the variance figures in the above analysis, what expense seem most problematic? 7 The following table shows the headcount and profit & expense data for a department in a company. Compute the per-employee (common-sized) figures for the profit and expense items. Jan Feb Mar Apr May Jun Headcount 5 5 5 5 3 3 Gross profit 32,845 46,208 21,710 12,607 18,938 18,268 Salaries 27,520 22,701 25,984 22,618 16,761 16,783 Payroll taxes 6,935 5,721 6,548 5,700 4,224 4,229 Lease 1,000 1,000 1,000 1,000 1,000 1,000 Phone 562 737 608 678 486 259 Supplies 142 263 132 299 106 158 Insurance 500 500 500 500 500 500 Total OpEx 36,659 30,922 34,772 30,795 23,077 22,929 EBITDA: Actual -3,814 15,286 -13,062 -18,188 -4,139 -4,661 EBITDA: Plan 15,249 26,425 11,506 -8,996 -3,698 -2,997 Gross profit Salaries Payroll taxes Lease Phone Supplies Insurance Total OpEx EBITDA: Actual EBITDA: Plan 8 What is ratio analysis? 9 Describe issues related to interpreting industry averages and trends and to comparing ratios within industries. 10 Compare horizontal and vertical analysis. 11 What is a profitability ratio? How are they interpreted? 12 In analyzing a company's profitability, which ratios are commonly used? 13 Define financial leverage and list commonly used leverage ratios. 14 What is liquidity and what are the commonly used liquidity ratios? 15 Compute the Earnings per Share for the following: Net Income $ 658,964 Shares of common stock outstanding EPS 2,000 16 Compute the Gross Profit Margin for the following: Sales $ 2,984,077 Cost of Sales $ 1,523,477 Gross Profit Margin 17 Compute the Net Profit Margin for the following: Net Income $ 458,964 Sales $ 2,925,707 Net profit margin 18 Compute the Return on Assets for the following: EBIT $ 310,838 Total Assets $ 3,612,824 Return on Assets 19 Compute the Return on Equity for the following: EBIT $ 310,838 Total Equity $ 2,123,456 Return on Equity 20 Compute the Debt Ratio for the following: Total Liabilities $ 1,645,225 Total Assets $ 3,922,889 Debt Ratio 21 Compute the Equity Ratio for the following: Stockholders' Equity $ 2,999,425 Total Assets $ 4,665,668 Equity Ratio 22 Compute the Times Interest Earned ratio for the following: EBIT $ 645,001 Interest $ 28,792 Times Interest Earned 23 Compute the Current Ratio on the following: Current Assets $ 1,400,815 Current Liabilities $ 693,849 Current Ratio 24 Compute the Quick Ratio on the following: Current Assets $ 1,400,815 Inventory $ 666,748 Current Liabilities $ 693,849 Quick Ratio 1 What is a pro forma financial statement? Pro forma, a Latin term meaning "as a matter of form," is applied to the process of presenting financial projections for a specific time period in a standardized format. Businesses use pro forma statements for decision-making in planning and control, and for external reporting to owners, investors, and creditors. Pro forma statements can be used as the basis of comparison and analysis to provide management, investment analysts, and credit officers with a feel for the particular nature of a business's financial structure under various conditions. http://www.inc.com/encyclopedia/pro-forma-statements_pagen_3.html Using the two most recent years shown (2013 & 2014), create a two-year percentage of sales forecast for the items shown in the income statement. 2 Use the 2-year percentage of sales forecast to develop a pro forma budget for the following year. Assume a 6% sales increase over the previous year. Develop a quarterly sales budget for the following year. Percentage of Sales Income Statement for the year ended: 2012 2013 2014 2-year average 2015 Projection Sales $ 498,541 $ 367,450 $ 389,864 Cost of Goods Sold Inventory, 1/1 $ 116,081 $ 85,919 $ 112,969 Purchases $ 115,372 $ 147,970 $ 187,540 Available for sale $ 231,453 $ 233,889 $ 300,509 Inventory, 12/31 $ 85,919 $ 112,969 $ 129,125 Cost of goods sold $ 145,534 $ 120,920 $ 171,384 Gross profit $ 353,007 $ 246,530 $ 218,480 Expenses Advertising Office lease Insurance Office supplies Salaries Communications Travel Depreciation Operating expenses Operating income Taxes Net income $ $ $ $ $ $ $ $ $ $ $ $ 6,166 8,750 3,906 2,110 62,378 3,708 1,070 4,192 92,280 260,727 65,182 195,545 $ $ $ $ $ $ $ $ $ $ $ $ 5,915 9,110 3,754 2,680 72,924 5,507 6,310 4,192 110,392 136,138 34,035 102,104 $ $ $ $ $ $ $ $ $ $ $ $ 6,770 9,544 4,010 3,862 94,347 7,014 8,733 4,192 138,472 80,008 20,002 60,006 3 Sensitivity analysis measures the impact of changes. What would be percentage effect on net profit be of a 2% price increase of the COGS purchases)? 4 Define the term forecasting. 5 Define baseline data. 6 The following are weekly sales volume figures. Compute a three week moving average. Round the average to the nearest whole number. Use the TREND function as an ARRAY function to predict weekly sales for the time series and to predict sales for weeks 16-18. Round to the nearest whole number. Week Sales volume Moving Avg 1 748 2 660 3 814 4 693 5 572 6 374 7 297 8 418 9 451 10 825 11 770 12 792 13 770 14 660 15 704 16 17 Q1 Sales Budget Q2 Q3 Q4 18 7 Differentiate linear and nonlinear relationships. 8 Considering the sales volume data in the previous problem, plot Weeks by Sales Volume in a Scatter Diagram. Insert a linear trend line including R 2. Does a straight line fit the data well? Next, create a second Scatter Diagram using the same data points. Insert a 3rd order polynomial trend line. Does this trend line fit the data better? 1 Describe the key components of developing an effective business case. 2 The following represents key elements in developing a business case and show how Excel can be used in the development process. Year: 1 2 3 4 ($Millions) $14 $18 $7 $9 $7 $9 5 6 $28 $14 $14 $0 $0 New E-Reader: Sales Forecast Total sales, New E-Reader Cost of Goods Sold @ 50% of sales Incremental Gross Profit, New E-Reader $8 $4 $4 In this case the relevant benefit of the new product is the incremental profit. incremental value $4 $7 $9 $14 The relevant cost factors should be identified and estimated over the timeframe of the case. 3 Relevant costs: Lost value, Current Product $6.00 $6.00 $6.00 $6.00 Advertising $2.00 $1.00 $0.50 $0.50 New product management team $1.00 $1.00 $1.00 $1.00 Market research expenses $0.50 $0.00 $0.00 $0.00 Incremental maintenance $0.00 $0.50 $0.50 $0.50 4 Total costs $9.50 5 Calculate net income. EBITDA 6 Income before taxes 7 Net income Less: Depreciation 36% New equipment investment $8.00 $8.00 ($5.50) $1.00 ($6.50) ($2.34) ($4.16) ($1.50) $1.00 ($2.50) ($0.90) ($1.60) $1.00 $1.00 $0.00 $0.00 $0.00 $6.00 $1.00 $5.00 $1.80 $3.20 ($0.60) $1.00 $4.20 $10.00 8 Estimate cash flow Net cash flow 9 $8.50 ($13.16) Determine the cumulative net cash flow and undiscounted payback period. Cumulative Net Cash Flow ($13.16) Undiscounted payback period ($13.76) ($12.76) ($8.56) Years Usually there's a discount rate that needs to be figured into the calculations. Discount Rate 3.0% Discounted Cash Flow $ (12.78) $ $ (12.78) $ 0.92 $ 0.92 $ 3.73 $ 3.73 $ - Cumulative Discounted Cash Flow $ (12.78) $ (13.34) $ (12.43) $ (8.70) $ (8.70) $ Discounted Payback Period 10 (0.57) $ (0.57) $ $ $ (8.70) Years Review the fundamentals of using the Solver add-in by viewing the following video: http://youtu.be/K4QkLA3sT1o Then implement Solver in the problem on the Unit10b tab. A 1 2 3 4 5 6 B C D E F G H I 1 Enhanced E-Reader: Cash Flow Statement Year: 1 Relevant benefits: Incremental value, New E-Reader $4.00 2 J K L M $22.00 Enhanced E-Reader: Cash Flow Statement Year: Relevant benefits: Incremental value, New E-Reader $4.00 3 ($Millions) $7.00 $9.00 $6.00 $2.00 $1.00 $0.50 $0.00 $6.00 $1.00 $1.00 $0.00 $0.50 $9.50 3 4 5 6 ($Millions) $7.00 $9.00 $14.00 $19.00 Relevant costs: 1 $19.00 $22.00 $6.00 $0.50 $1.00 $0.00 $0.50 $6.00 $0.50 $1.00 $0.00 $0.50 $6.00 $0.50 $1.00 $0.00 $0.50 $7.00 Constraints $0.50 $1.00 $0.00 $0.50 $8.50 $8.00 $8.00 $8.00 $9.00 ($5.50) $1.00 ($6.50) ($2.34) ($4.16) ($1.50) $1.00 ($2.50) ($0.90) ($1.60) $1.00 $1.00 $0.00 $0.00 $0.00 $6.00 $1.00 $5.00 $1.80 $3.20 $11.00 $1.00 $10.00 $3.60 $6.40 $13.00 $1.00 $12.00 $4.32 $7.68 $1.00 $0.00 ($0.60) $1.00 $0.00 $1.00 $1.00 $0.00 $4.20 $1.00 $0.00 $7.40 $1.00 $0.00 $8.68 ($13.76) ($12.76) ($8.56) ($1.16) $7.52 ($11.96) ($0.50) $0.75 $2.87 $4.59 $4.90 Err:504 Err:504 Err:504 Err:504 Err:504 Err:504 5.87 ($11.96) ($12.46) ($11.71) ($8.84) ($4.24) $0.65 $6.00 $0.50 $1.00 $0.00 $0.50 $6.00 $0.50 $1.00 $0.00 $0.50 $7.00 $0.50 $1.00 $0.00 $0.50 $9.50 $8.50 $8.00 $8.00 $8.00 $9.00 Total costs ($5.50) $1.00 ($6.50) ($2.34) ($4.16) ($1.50) $1.00 ($2.50) ($0.90) ($1.60) $1.00 $1.00 $0.00 $0.00 $0.00 $6.00 $1.00 $5.00 $1.80 $3.20 $11.00 $1.00 $10.00 $3.60 $6.40 $13.00 $1.00 $12.00 $4.32 $7.68 Net Cash Flow $1.00 $10.00 ($13.16) $1.00 $0.00 ($0.60) $1.00 $0.00 $1.00 $1.00 $0.00 $4.20 $1.00 $0.00 $7.40 $1.00 $0.00 $8.68 Net Cash Flow $1.00 $10.00 ($13.16) Cumulative Net Cash Flow 11 12 13 14 15 16 17 18 19 20 ($13.16) ($13.76) ($12.76) ($8.56) ($1.16) $7.52 Cumulative Net Cash Flow ($13.16) Total costs EBITDA Less: Depreciation Income before taxes Less: Taxes @ 36% Net income Plus: Depreciation Minus: Investment 21 22 23 24 25 EBITDA Less: Depreciation Income before taxes Less: Taxes @ 36% Net income Plus: Depreciation Minus: Investment R $14.00 $6.00 $0.50 $1.00 $0.00 $0.50 10 Q 6 $6.00 $1.00 $1.00 $0.00 $0.50 8 9 P 5 $6.00 $2.00 $1.00 $0.50 $0.00 Lost value, Current Product Advertising New product management Market research expenses Incremental maintenance 2 O 4 Relevant costs: Lost value, Current Product Advertising New product management Market research expenses Incremental maintenance 7 N 26 Undiscounted payback period: 28 29 30 31 32 33 34 35 36 37 Undiscounted payback period: 0.1 Discount Rate: Discounted Cash Flow ($11.96) ($0.50) $0.75 $2.87 $4.59 $4.90 Discounted Cash Flow Cumulative Discounted Cash Flow ($11.96) ($12.46) ($11.71) ($8.84) ($4.24) $0.65 Cumulative Discounted Cash Flow Discounted payback period: 5.87 ($11.96) Discounted payback period: ($12.46) ($11.71) ($8.84) ($4.24) See comment in B1 for instructions regarding this box. 38 39 40 41 42 43 44 45 46 47 5.13 Discount Rate: 27 2 $0.65 0.1 A 48 B C D E F G H I J K L M N O P Q R 1 How is standard deviation used as a business analytic? 2 Describe the purposes of the Excel's standard deviation functions, STDEV.S & STDEV.P. 3 Define "confidence interval" and how it relates to business analytics. 4 Compute the 95% confidence interval for the following data. See instructions in the comment field. Passersby 544 468 399 759 526 212 256 456 553 259 469 366 197 178 5 Upper bound Lower bound CONFIDENCE.T Upper bound Lower bound The following are data showing the advertising budget and unit sales for a company. Create an X-Y Scatter Diagram (Scatterplot) for the data. Insert a trend line. Also include the regression equation and the R 2 on the chart. Place the scatterplot in the box. Advertising Budget $3,500 $10,073 $11,825 $33,550 $37,200 $55,400 $55,565 $66,501 $71,000 Unit Sales 16,523 6,305 1,769 30,570 7,698 9,554 54,154 54,450 47,800 $82,107 $83,100 $90,496 $100,000 $102,100 $132,222 $136,297 $139,114 $165,575 74,598 25,257 80,608 40,800 63,200 69,675 98,715 75,886 83,360 6 Given the strong linear relationship between the advertising budget and unit sales in the previous example, can it be said that the budgeted dollars caused the sales? A for Yes; B for No. 7 Use Excel's regression tool to perform a regression analysis for the budget and sales data in the above dataset. Treat the advertising budget as the independent variable. Place the results In B97. 8 Using the TREND function as an ARRAY function, for the data in the above item predict the unit sales for the following advertising budget dollar amounts. $200,000 $210,000 9 Use LINEST to perform a regression analysis on the budget and unit data above. Treat unit sales as the dependent variable. The following adds additional data (Sales Price) to the dataset the previous problems used. 10 Treating Advertising Budget and Sales Price as independent variables, use the Regression tool in Data Analysis to conduct a regression analysis with Unit Sales as the dependent variable. Specify F137 as the Output Range. Advertising Budget Sales Price Unit Sales $3,500 $88 $10,073 $110 $11,825 $85 $33,550 $28 $37,200 $101 $55,400 $71 $55,565 $7 $66,501 $82 $71,000 $62 $82,107 $24 $83,100 $91 $90,496 $40 $100,000 $45 $102,100 $21 $132,222 $40 $136,297 $8 $139,114 $63 $165,575 $5 11 Are both predictors statistically significant? 16,523 6,305 1,769 30,570 7,698 9,554 54,154 54,450 47,800 74,598 25,257 80,608 40,800 63,200 69,675 98,715 75,886 83,360 12 What is the Multiple Correlation? 13 Is the Multiple Correlation statistically significant? A=Yes; B=No. 1 What is the contribution margin? 2 For the scenario detailed below, compute total sales, total variable costs, contribution margin, and contribution margin ratio. Unit Sales Price: $11.25 USP Increase Number Sold: 1250 $ 11.25 1.00 Sales: 1250 DVDs @ $11.25: Less: Variable costs associated with production: Employee costs (1000 DVDs @ $0.50): $625 Materials costs (1000 DVDs @ $5): $6,250 Packaging costs (1000 DVDs @ $1): $1,250 Total variable costs: Contribution margin: Contribution margin ratio: 3 What is unit contribution? 4 Calculate the unit contribution from the data presented in the previous scenario. 5 Define breakeven analysis. 6 What is the breakeven point? 7 For the following data, calculate the breakeven point in sales and in units. Volume Fixed Variable Total Total (units) Costs Costs Costs Sales 1 $50 $15 $65 $20 2 $50 $30 $80 $40 3 $50 $45 $95 $60 4 $50 $60 $110 $80 5 $50 $75 $125 $100 6 $50 $90 $140 $120 7 $50 $105 $155 $140 8 $50 $120 $170 $160 9 $50 $135 $185 $180 10 $50 $150 $200 $200 11 $50 $165 $215 $220 12 $50 $180 $230 $240 13 $50 $195 $245 $260 14 $50 $210 $260 $280 15 $50 $225 $275 $300 16 $50 $240 $290 $320 17 $50 $255 $305 $340 18 $50 $270 $320 $360 19 $50 $285 $335 $380 Breakeven in Units Breakeven in Sales 8 For the data in the above example, create a graph showing the breakeven point. 9 Which of the following is not an assumption made in contribution analysis? A - Revenues and expenses are linear across the relevant range of volume. B - Costs can be accurately allocated fixed and variable cost categories. C - Sales mix is constant. That is from one period to the next, total sales are based on the same percent of each product line. D - Worker productivity is constant. E - None of A - D are assumptions of contribution analysis. F - All of A - D are assumptions of contribution analysis. Homework The objective of the Homework assignment is to provide an opportunity to apply or practice the tools and techniques covered in the course and to provide an opportunity to articulate key concepts addressed in the course. The homework is not a test. Any resource (eLecture, web-based, content, study partners, and the Solutions Workbook) may be used to to help answer the problems in each worksheet. The Homework problems generally correspond to the types of problems you will encounter in the Applications Assignment, which will be made available at the beginning of Unit 11. Consequently, working on the Homework problems will be good practice for the Applications Assignment. The worksheets in this workbook correspond to the Units in the course. In two cases, there are two worksheets for a Unit (Un