EXCEL PROBLEM SET 2 CHAPTER 13: roblem 13.1: Production Quantity Model eart A: Atlas Carpet Mills the exclusive manufacturer of the Cascade carpet brand. The mill operates 7 days per week, 50 weeks per year. Annual demand is 20,000 yards of carpet, which is produced at a rate of 400 yards per day. Annual carrying costs are $2.75 per yard. The cost of setting up the manufacturing process for a production run is $720. Determine the following 2. Optimal production run quantity (Q b. Total annual inventory costs c. Optimal number of production runs per year d. Optimal cycle time time between run starts) e. Run length in working days You are on the right track if the value of Tomin is 8239.28 art B Atlas would like to determine the safety stock level and reorder point that corresponds to numbers derived in Part A. Demand is variable and normally distributed, with an average of 57.14 yards per day. The standard deviation, which was determined using an Excel spreadsheet and historical data, is 7.5 units. The lead time required to produce an order and deliver it to the warehouse is 20 days. f Determine the safety stock and reorder point if the mill wishes to maintain a 90% service level g. Calculate the safety stock and the reorder point if a 95% service level is desired (Hint: Use the Normal Curve Areas table in Appendix 1 to get the Z score values. Also remember that you are looking for the area in one tail of the curve. To use the area table, convert the confidence interval to its decimal equivalent (90% puas 0.9; 95% equals 0.95). If one side of the curve equals 0.5, you would be looking for the Z score that corresponds to 6.5 plus the remainder needed to get the desired confidence interval (0.5 +0.4-0.9, or 90%; 0.5 +0.45 .95, or 95%). For a one-tailed test, you will want to find the value in the body of the table that comes dosest to the remainder. If the particular remainder happens to fall between two values in the table, than estimate the Z score that "splits the difference." For example, for a 95% confidence interval, you would want to find 0.45 ir the table. The closest values are .4495, which corresponds to Z-1.64, and 0.4505 which corresponds to Z 1.63. Since it is easy to split the difference on these, you can use Z-1.645 in the equation. Now, if you wanted the area under two tails, finding the Z score requires you to divide the confidence interval In two. For a 95% confidence interval, you would divide 0.95 by two to find the look-up value (0.95/2- 0.475), which gives you a Z score of 1.96.) You are on the right track if the ROP at the 90% service level is approximately 1186 units EXCEL PROBLEM SET 2 APTER 13: olem 13.1: Production Quantity Model A: Atlas Carpet Mills the exclusive manufacturer of the Cascade carpet brand. The mill operates 7 days per week, 50 weeks per year. Annual demand is 20,000 yards of carpet, which is produced at a rate of 400 yards per day. Annual carrying costs are $2.75 per yard. The cost of setting up the manufacturing process for a production run is $720. Determine the following: 2. Optimal production run quantity Q b. Total annual inventory costs c. Optimal number of production runs per year d. Optimal cycle time (time between run starts) e. Run length in working days You are on the right track if the value of Tomin is 8239.28 3: Atlas would like to determine the safety stock level and reorder point that corresponds to numbers derived in Part A. Demand is variable and normally distributed, with an average of 57.14 yards per day. The standard deviation, which was determined using an Excel spreadsheet and historical data, is 7.5 units. The lead time required to produce an order and deliver it to the warehouse is 20 days. f. Determine the safety stock and reorder point if the mill wishes to maintain a 90% service level. g. Calculate the safety stock and the reorder point if a 95% service level is desired. (Hint: Use the Normal Curve Areas table in Appendix 1 to get the Z score values. Also remember that you Mulgui l Wing Uays You are on the right track if the value of Iain is 8239.28 B Atlas would like to determine the safety stock level and reorder point that corresponds to numbers derived in Part A. Demand is variable and normally distributed, with an average of 57.14 yards per day. The standard deviation, which was determined using an Excel spreadsheet and historical data, is 7.3 units. The lead time required to produce an order and deliver it to the warehouse is 20 days. f. Determine the safety stock and reorder point if the mill wishes to maintain a 90% service level. g. Calculate the safety stock and the reorder point if a 95% service level is desired. (Hint: Use the Normal Curve Areas table in Appendix 1 to get the Z score values. Also remember that you are looking for the area in one tail of the curve. To use the area table, convert the confidence interval to its decimal equivalent (90% equals 0.9; 95% equals 0.95). If one side of the curve equals 0,5, you would be looking for the Z score that corresponds to 0.5 plus the remainder needed to get the desired confidence interval (0.5 +0.4 = 0.9, or 90%; 0.5 + 0.45.95, or 95%). For a one-tailed test, you will want to find the value in the body of the table that comes closest to that remainder. If the particular remainder happens to fall between two values in the table, the estimate the Z score that "splits the difference." For example, for a 95% confidence interval, you would want to find 0.45 in the table. The closest values are .4495, which corresponds to Z-1.64, and 0.4505 whick corresponds to Z 1.65. Since it is easy to split the difference on these, you can use Z-1.645 in the equation. Now, if you wanted the area under two tails, finding the Z score requires you to divide the confidence interval in two. For a 95% confidence interval, you would divide 0.95 by two to find the look-up value (0.95/2 = 0.475), which gives you a Z score of 1.96.) You are on the right track if the ROP at the 90% service level is approximately 1186 units Page 2 of 3 EXCEL PROBLEM SET 2 CHAPTER 13: Problem 13.1: Production Quantity Model Part A: Atlas Carpet Mills the exclusive manufacturer of the Cascade carpet brand. The mill operates 7 days per week. 50 weeks per year. Annual demand is 20,000 yards of carpet, which is produced at a rate of 400 yards per day. Annual carrying costs are $2.75 per yard. The cost of setting up the manufacturing process for a production run is $720. Determine the following 2 Optimal production run quantity (0) b. Total annual inventory costs c. Optimal number of production runs per year d Optimal cycle time time between run starts) e Run length in working days Fou are on the right track if the value of Tomin is 8239.24 Part B: Atlas would like to determine the safety stock level and reorder point that corresponds to numbers derived in Part A Demand is variable and normally distributed, with an average of 57.14 yards per day. The standard deviation, which was determined using an Excel spreadsheet and historical data, is 7.5 units. The lead time required to produce an order and deliver it to the warehouse is 20 days. Determine the safety stock and reorder point if the mill wishes to maintain a 90% service level Calculate the safety stock and the reorder point if a 95% service level is desired (Hint: Uw the Normal Curve Areas table in Appendix 1 to get the score values. Also remember that you are looking for the area is one tail of the curre To use the area table, consert the confidence interval to its decimal equivalent (90% quals 0.9; 95% equals 0.95). If one side of the curve equals 0.5, you would be looking for the Z score that corresponds to 6.5 plus the remainder needed to get the desired confidence interval (0.5 +0.4-0.9, or 90%; 0.5 +0.45 ,95, or 95%). For a one-tailed test, you will want to find the value in the body of the table that comes closest to that remainder. If the particular remainder happens to fall between two values in the table than estimate the z score thar "splits the difference. For example, for a 95% confidence interval, you would want to find 0.45 the table. The closest values are.1495, which corresponds to Z-1.64 0.4503 which corresponds to Z - 1.65. Since it is easy to split the difference on these, you CAR MWZ-1.615 in the equation. Now, you wanted the area under the tails, finding the Zscere requires you to divide the condence interval in twe. For a 95% confidence interval, you would divide 0.95 byte to find the look up wlue (0.95/2- 0.475), which gives you a Z score of 1.96.) You are on the right track if the ROP at the 90% service Level is approximately 1186 ni EXCEL PROBLEM SET 2 CHAPTER 13: roblem 13.1: Production Quantity Model eart A: Atlas Carpet Mills the exclusive manufacturer of the Cascade carpet brand. The mill operates 7 days per week, 50 weeks per year. Annual demand is 20,000 yards of carpet, which is produced at a rate of 400 yards per day. Annual carrying costs are $2.75 per yard. The cost of setting up the manufacturing process for a production run is $720. Determine the following 2. Optimal production run quantity (Q b. Total annual inventory costs c. Optimal number of production runs per year d. Optimal cycle time time between run starts) e. Run length in working days You are on the right track if the value of Tomin is 8239.28 art B Atlas would like to determine the safety stock level and reorder point that corresponds to numbers derived in Part A. Demand is variable and normally distributed, with an average of 57.14 yards per day. The standard deviation, which was determined using an Excel spreadsheet and historical data, is 7.5 units. The lead time required to produce an order and deliver it to the warehouse is 20 days. f Determine the safety stock and reorder point if the mill wishes to maintain a 90% service level g. Calculate the safety stock and the reorder point if a 95% service level is desired (Hint: Use the Normal Curve Areas table in Appendix 1 to get the Z score values. Also remember that you are looking for the area in one tail of the curve. To use the area table, convert the confidence interval to its decimal equivalent (90% puas 0.9; 95% equals 0.95). If one side of the curve equals 0.5, you would be looking for the Z score that corresponds to 6.5 plus the remainder needed to get the desired confidence interval (0.5 +0.4-0.9, or 90%; 0.5 +0.45 .95, or 95%). For a one-tailed test, you will want to find the value in the body of the table that comes dosest to the remainder. If the particular remainder happens to fall between two values in the table, than estimate the Z score that "splits the difference." For example, for a 95% confidence interval, you would want to find 0.45 ir the table. The closest values are .4495, which corresponds to Z-1.64, and 0.4505 which corresponds to Z 1.63. Since it is easy to split the difference on these, you can use Z-1.645 in the equation. Now, if you wanted the area under two tails, finding the Z score requires you to divide the confidence interval In two. For a 95% confidence interval, you would divide 0.95 by two to find the look-up value (0.95/2- 0.475), which gives you a Z score of 1.96.) You are on the right track if the ROP at the 90% service level is approximately 1186 units EXCEL PROBLEM SET 2 APTER 13: olem 13.1: Production Quantity Model A: Atlas Carpet Mills the exclusive manufacturer of the Cascade carpet brand. The mill operates 7 days per week, 50 weeks per year. Annual demand is 20,000 yards of carpet, which is produced at a rate of 400 yards per day. Annual carrying costs are $2.75 per yard. The cost of setting up the manufacturing process for a production run is $720. Determine the following: 2. Optimal production run quantity Q b. Total annual inventory costs c. Optimal number of production runs per year d. Optimal cycle time (time between run starts) e. Run length in working days You are on the right track if the value of Tomin is 8239.28 3: Atlas would like to determine the safety stock level and reorder point that corresponds to numbers derived in Part A. Demand is variable and normally distributed, with an average of 57.14 yards per day. The standard deviation, which was determined using an Excel spreadsheet and historical data, is 7.5 units. The lead time required to produce an order and deliver it to the warehouse is 20 days. f. Determine the safety stock and reorder point if the mill wishes to maintain a 90% service level. g. Calculate the safety stock and the reorder point if a 95% service level is desired. (Hint: Use the Normal Curve Areas table in Appendix 1 to get the Z score values. Also remember that you Mulgui l Wing Uays You are on the right track if the value of Iain is 8239.28 B Atlas would like to determine the safety stock level and reorder point that corresponds to numbers derived in Part A. Demand is variable and normally distributed, with an average of 57.14 yards per day. The standard deviation, which was determined using an Excel spreadsheet and historical data, is 7.3 units. The lead time required to produce an order and deliver it to the warehouse is 20 days. f. Determine the safety stock and reorder point if the mill wishes to maintain a 90% service level. g. Calculate the safety stock and the reorder point if a 95% service level is desired. (Hint: Use the Normal Curve Areas table in Appendix 1 to get the Z score values. Also remember that you are looking for the area in one tail of the curve. To use the area table, convert the confidence interval to its decimal equivalent (90% equals 0.9; 95% equals 0.95). If one side of the curve equals 0,5, you would be looking for the Z score that corresponds to 0.5 plus the remainder needed to get the desired confidence interval (0.5 +0.4 = 0.9, or 90%; 0.5 + 0.45.95, or 95%). For a one-tailed test, you will want to find the value in the body of the table that comes closest to that remainder. If the particular remainder happens to fall between two values in the table, the estimate the Z score that "splits the difference." For example, for a 95% confidence interval, you would want to find 0.45 in the table. The closest values are .4495, which corresponds to Z-1.64, and 0.4505 whick corresponds to Z 1.65. Since it is easy to split the difference on these, you can use Z-1.645 in the equation. Now, if you wanted the area under two tails, finding the Z score requires you to divide the confidence interval in two. For a 95% confidence interval, you would divide 0.95 by two to find the look-up value (0.95/2 = 0.475), which gives you a Z score of 1.96.) You are on the right track if the ROP at the 90% service level is approximately 1186 units Page 2 of 3 EXCEL PROBLEM SET 2 CHAPTER 13: Problem 13.1: Production Quantity Model Part A: Atlas Carpet Mills the exclusive manufacturer of the Cascade carpet brand. The mill operates 7 days per week. 50 weeks per year. Annual demand is 20,000 yards of carpet, which is produced at a rate of 400 yards per day. Annual carrying costs are $2.75 per yard. The cost of setting up the manufacturing process for a production run is $720. Determine the following 2 Optimal production run quantity (0) b. Total annual inventory costs c. Optimal number of production runs per year d Optimal cycle time time between run starts) e Run length in working days Fou are on the right track if the value of Tomin is 8239.24 Part B: Atlas would like to determine the safety stock level and reorder point that corresponds to numbers derived in Part A Demand is variable and normally distributed, with an average of 57.14 yards per day. The standard deviation, which was determined using an Excel spreadsheet and historical data, is 7.5 units. The lead time required to produce an order and deliver it to the warehouse is 20 days. Determine the safety stock and reorder point if the mill wishes to maintain a 90% service level Calculate the safety stock and the reorder point if a 95% service level is desired (Hint: Uw the Normal Curve Areas table in Appendix 1 to get the score values. Also remember that you are looking for the area is one tail of the curre To use the area table, consert the confidence interval to its decimal equivalent (90% quals 0.9; 95% equals 0.95). If one side of the curve equals 0.5, you would be looking for the Z score that corresponds to 6.5 plus the remainder needed to get the desired confidence interval (0.5 +0.4-0.9, or 90%; 0.5 +0.45 ,95, or 95%). For a one-tailed test, you will want to find the value in the body of the table that comes closest to that remainder. If the particular remainder happens to fall between two values in the table than estimate the z score thar "splits the difference. For example, for a 95% confidence interval, you would want to find 0.45 the table. The closest values are.1495, which corresponds to Z-1.64 0.4503 which corresponds to Z - 1.65. Since it is easy to split the difference on these, you CAR MWZ-1.615 in the equation. Now, you wanted the area under the tails, finding the Zscere requires you to divide the condence interval in twe. For a 95% confidence interval, you would divide 0.95 byte to find the look up wlue (0.95/2- 0.475), which gives you a Z score of 1.96.) You are on the right track if the ROP at the 90% service Level is approximately 1186 ni