ALL THE QUESTIONS ARE USING EXCEL
HW4 - THE PROFESSOR'S FRIEND Everyone who teaches large classes at a university faces the same challenge - how to deal with the mountain of clerical work involved. This model is one that many of us use to calculate and record exam grades. Instructions: 1) Calculate a percentage grade for each student based on 90 total points. 2) Use VLOOKUP to assign a letter grade to each student based on the percentage grade. 3) Complete the grade distribution table using the COUNTIF function. Pctg Letter Letter Grade 56 Percentage 0.5 0.6 0.7 0.8 0.9 88 L C B 66 65 Section 1120 Final Exam Grades Student ID Total Score 9583444 55 4794526 8121978 1792449 72 5868818 0571087 63 8592289 0797400 84 4097487 1622007 6156744 70 3216215 64 3437122 3610687 77 9341519 67 5759174 80 5575636 6738775 65 5204964 66 2867078 77 69 Grade Distribution Number LA Percentage 64 C 83 58 The price of a share of Northwest Industrial Products listed on the New York Stock Exchange is currently $103. The following probability distribution shows how the price per share is expected to change over a three-month period: 1 Stock Price Change ($) -22 1 1 -12 10 1 Probability 0.05 0.102 0.25 0.20 | 0.20 0.10 0.10 1 +12 +2 1 1 +32 +4 Do the following on an Excel spreadsheet: a. Set up intervals of random numbers that can be used to generate the change in stock price over a three-month period. b. With the current price of $103 per share and the random numbers 0.7895, 0.4522, 0.1801, 0.0039, and 0.8531, simulate the price per share for the next five 3-month periods. What is the ending simulated price per share? The Wreck-It Ralph Auto Insurance Company developed the following probability distribution for automobile collision claims paid during the past year:- ? Payment ($) ? 0 ? ? Probability 0.83 0.06 0.05 0.02 0.02 0.01 0.01 500 1,000 2,000 5,000 8,000 10,000 ? ? ? a. Set up intervals of random numbers that can be used to generate automobile collision claim payments. b. Using the following 20 random numbers, simulate the payments for 20 policyholders. How many claims are paid and what is the total amount paid to the policyholders? Random numbers: .3389 .4449 .1679 .8191 .7753 .4998 .2225 .8454e .8769 .1386 .5847 .6899 .8497 .532 .0112 .6246e .2346 .9955 .7330 .3572 tttt Develop an Excel worksheet simulation for the following problem. The management of Paragon Household Products is considering the introduction of a new product. The fixed cost to begin the production of the product is $25,388. The variable cost for the product is uniformly distributed between $15 and $20 per unit. The product will sell for $42 per unit. Demand for the product is best described by a normal probability distribution with a mean of 1200 units and a standard deviation of 300 units. Develop a spreadsheet simulation that uses 500 simulation trials to answer the following questions: a. What is the mean profit for the simulation? b. What is the probability that the project will result in a loss? C. What is your recommendation concerning the introduction of the product? ***NOTE: the exact numbers will change every time you change the spreadsheet. The assignment will be graded on the integrity of the model. Fuji Tire Company has produced a new tire with an estimated mean lifetime mileage of 40,000 miles. Management also believes that the standard deviation is 3900 miles and that tire mileage is normally distributed. Use an Excel worksheet to simulate the miles obtained for a sample of 500 tires. a. Use the Excel COUNTIF function (see Appendix A for a description of the Excel COUNTIF function) to determine the number of tires that last longer than 40,000 miles. What is your estimate of the percentage of tires that will exceed 40,000 miles? b. Use COUNTIF to find the number and percentage of tires that obtain mileage less than 35,000 miles. Then find the number and percentage of those with less than 32,000 miles and of those with less than 30,000 miles. ***NOTE: the exact numbers will change every time you change the spreadsheet. The assignment will be graded on the integrity of the model. HW4 - THE PROFESSOR'S FRIEND Everyone who teaches large classes at a university faces the same challenge - how to deal with the mountain of clerical work involved. This model is one that many of us use to calculate and record exam grades. Instructions: 1) Calculate a percentage grade for each student based on 90 total points. 2) Use VLOOKUP to assign a letter grade to each student based on the percentage grade. 3) Complete the grade distribution table using the COUNTIF function. Pctg Letter Letter Grade 56 Percentage 0.5 0.6 0.7 0.8 0.9 88 L C B 66 65 Section 1120 Final Exam Grades Student ID Total Score 9583444 55 4794526 8121978 1792449 72 5868818 0571087 63 8592289 0797400 84 4097487 1622007 6156744 70 3216215 64 3437122 3610687 77 9341519 67 5759174 80 5575636 6738775 65 5204964 66 2867078 77 69 Grade Distribution Number LA Percentage 64 C 83 58 The price of a share of Northwest Industrial Products listed on the New York Stock Exchange is currently $103. The following probability distribution shows how the price per share is expected to change over a three-month period: 1 Stock Price Change ($) -22 1 1 -12 10 1 Probability 0.05 0.102 0.25 0.20 | 0.20 0.10 0.10 1 +12 +2 1 1 +32 +4 Do the following on an Excel spreadsheet: a. Set up intervals of random numbers that can be used to generate the change in stock price over a three-month period. b. With the current price of $103 per share and the random numbers 0.7895, 0.4522, 0.1801, 0.0039, and 0.8531, simulate the price per share for the next five 3-month periods. What is the ending simulated price per share? The Wreck-It Ralph Auto Insurance Company developed the following probability distribution for automobile collision claims paid during the past year:- ? Payment ($) ? 0 ? ? Probability 0.83 0.06 0.05 0.02 0.02 0.01 0.01 500 1,000 2,000 5,000 8,000 10,000 ? ? ? a. Set up intervals of random numbers that can be used to generate automobile collision claim payments. b. Using the following 20 random numbers, simulate the payments for 20 policyholders. How many claims are paid and what is the total amount paid to the policyholders? Random numbers: .3389 .4449 .1679 .8191 .7753 .4998 .2225 .8454e .8769 .1386 .5847 .6899 .8497 .532 .0112 .6246e .2346 .9955 .7330 .3572 tttt Develop an Excel worksheet simulation for the following problem. The management of Paragon Household Products is considering the introduction of a new product. The fixed cost to begin the production of the product is $25,388. The variable cost for the product is uniformly distributed between $15 and $20 per unit. The product will sell for $42 per unit. Demand for the product is best described by a normal probability distribution with a mean of 1200 units and a standard deviation of 300 units. Develop a spreadsheet simulation that uses 500 simulation trials to answer the following questions: a. What is the mean profit for the simulation? b. What is the probability that the project will result in a loss? C. What is your recommendation concerning the introduction of the product? ***NOTE: the exact numbers will change every time you change the spreadsheet. The assignment will be graded on the integrity of the model. Fuji Tire Company has produced a new tire with an estimated mean lifetime mileage of 40,000 miles. Management also believes that the standard deviation is 3900 miles and that tire mileage is normally distributed. Use an Excel worksheet to simulate the miles obtained for a sample of 500 tires. a. Use the Excel COUNTIF function (see Appendix A for a description of the Excel COUNTIF function) to determine the number of tires that last longer than 40,000 miles. What is your estimate of the percentage of tires that will exceed 40,000 miles? b. Use COUNTIF to find the number and percentage of tires that obtain mileage less than 35,000 miles. Then find the number and percentage of those with less than 32,000 miles and of those with less than 30,000 miles. ***NOTE: the exact numbers will change every time you change the spreadsheet. The assignment will be graded on the integrity of the model
