Having difficulty with this lab :/ any kind of help would be useful and very much appreciated!

Learning Objectives: Deeper understanding about the costs associated with an Ordering policy (i.e. an "order quantity" or "lot size") Reinforcement of concepts related to the Economic Order Quantity (EOQ) Practice Excel functionality including charts and Solver Part 1: Economic Order Quantity EOQ=H2D5 Q= Order Quantity; how much to order/produce each time D= Annual Demand H= Holding Cost; the annual cost to hold one item in inventory S= Ordering Cost (Setup Cost); the fixed cost to place an order (setup production), independent of order quantity A few questions that you should be able to solve based on the definitions: 1. Assume that D=1,000 units per year; H=$2 per unit per year; S=$50 per order; and Q =100 (order in batches of 100 units). Ignore demand variability and assume that an order arrives right as inventory levels reach zero. a. What is the average amount of inventory (hint: inventory will start at a max of Q units and gradually reduce to zero units before being replenished to Q )? b. What is the annual holding cost? c. How many times per year are orders placed? d. What is the annual ordering/setup cost? e. What is the TOTAL annual cost (ordering + holding) of a policy where Q=100 items are ordered each time? 2. Open the 'EOQ template' sheet. We will determine the optimal order quantity, Q, in a variety of ways: a. Complete the table provided and find (in the table) the Q that gives the lowest total annual cost. Use conditional formatting to find the EOQ. b. Make a chart ("scatter with smooth lines") that graphs Annual Order, Holding and Total costs on the y-axis with Q on the x-axis. Which Q gives lowest total cost, looking at the chart? c. Copy the first row of the chart into the other chart titled 'Solver'. Then use this row and use solver to find the Q that gives the lowest total cost. (Note: this is unnecessary in reality, but we use it as an opportunity to use solver again). d. Use the EOQ (Economic Order Quantity) equation to confirm the optimal Q. i. Q=sqrt(2DS/H) Part 2: Safety Stock for a Continuous Review Inventory Control System Learning Objectives: Better understanding of Safety Stock Relationship between Cycle Service Level and Safety Stock Consider a case where daily demand averages 10 units/day, with a standard deviation of 3 units, and an order to a supplier for replenishment takes 10 days to fill (lead time is 10 days). 1. What will be average demand during the lead time (i.e. average demand over 10 days)? 2. What will be the standard deviation of demand during the lead time (over 10 days)? 3. What is meant by the term "Reorder Point"? What is meant by the term "cycle service level"? 4. Recall that in a continuous review system, the reorder point (R) can be found as: R= average demand during lead time + safety stock R=dL+zdLT Where the z-score corresponds to the "cycle service level" and dLt is the standard deviation of demand during the lead time. a. Go to the first cell under the "Cycle Service Level" column (i.e., B14) and enter the value ' 0.50 '. Use the Fill/Series functionality to create a list in this column that goes from 0.50 to 0.9999 in increments of 0.00005 . b. In row 15 , create equations for 'Avg lead time demand', 'Safety Stock' and 'Reorder Point'. Hint: use the NORMSINV () function to find the z-score that corresponds to a given service level (i.e., reference the service level as the argument for the NORMSINV function). c. Plot the safety stock values in a chart ("scatter with smooth lines"). What does the shape of the graph look like? What does this tell you? Part 3: ABC Analysis The textbook describes ABC analysis as dividing items into three classes, according to their dollar usage (demand* cost). The classes are approximately as follows (other texts may use slightly different numbers): A items represent 20% of items (SKUs), but account for 80% of dollar usage. B items represent 30% of items (SKUs), but only 15% of dollar usage. C items represent 50% of items (SKUs), but only 5% of dollar usage. 1. Using the data provided in the spreadsheet, partition the inventory items into three classes (A, B, C), based on dollar usage. Use your judgment for where the divisions between classes should be. a. List all SKUs and determine annual dollar usage of each (annual demand * cost per item). b. Sort in descending order of annual usage. c. For each SKU, determine cumulative annual dollar usage and cumulative percentage of annual dollar usage. d. Classify each SKU as A, B, or C. We will automate this using an IF() statement based on the literal meaning of the classes. In reality, one might plot the data and look for natural "breaks", for example where the differences in cumulative \% become noticeably less (line in the graph becomes flatter) - this is likely a good spot to separate the classes. 2. [This has been completed already]. Compute statistics for each classification - % of items and % of dollar usage. 0= If annual demand is 1739 , holding costs are $9 per unit per year, and setup costs are $89 per order, what is the economic order quantity? Answer: What is the total annual cost for an EOQ calculated using the following: D=2126S=24H=3 Answer: The Economic Order Quantity (EOQ) should always be rounded to two decimal places. Select one: True False Using lab 8 part 2 , how much safety stock is needed to have a cycle service level of at least 97% ? (Round answer to the nearest whole number) Answer: Using lab 8 part 2, how much additional safety stock is required to go from a cycle service level of 98% to 99% ? Select one: a. 3.00 b. 2.48 c. 2.00 d. 1.64 e. 1.96 f. 2.59 In general, ABC states that in theory: A items represent of items, B items represent of items, and C items represent the remaining of items. However, when looking at dollar values, A items account for B items represent , and C items represent of the dollar usage of items. 5% Learning Objectives: Deeper understanding about the costs associated with an Ordering policy (i.e. an "order quantity" or "lot size") Reinforcement of concepts related to the Economic Order Quantity (EOQ) Practice Excel functionality including charts and Solver Part 1: Economic Order Quantity EOQ=H2D5 Q= Order Quantity; how much to order/produce each time D= Annual Demand H= Holding Cost; the annual cost to hold one item in inventory S= Ordering Cost (Setup Cost); the fixed cost to place an order (setup production), independent of order quantity A few questions that you should be able to solve based on the definitions: 1. Assume that D=1,000 units per year; H=$2 per unit per year; S=$50 per order; and Q =100 (order in batches of 100 units). Ignore demand variability and assume that an order arrives right as inventory levels reach zero. a. What is the average amount of inventory (hint: inventory will start at a max of Q units and gradually reduce to zero units before being replenished to Q )? b. What is the annual holding cost? c. How many times per year are orders placed? d. What is the annual ordering/setup cost? e. What is the TOTAL annual cost (ordering + holding) of a policy where Q=100 items are ordered each time? 2. Open the 'EOQ template' sheet. We will determine the optimal order quantity, Q, in a variety of ways: a. Complete the table provided and find (in the table) the Q that gives the lowest total annual cost. Use conditional formatting to find the EOQ. b. Make a chart ("scatter with smooth lines") that graphs Annual Order, Holding and Total costs on the y-axis with Q on the x-axis. Which Q gives lowest total cost, looking at the chart? c. Copy the first row of the chart into the other chart titled 'Solver'. Then use this row and use solver to find the Q that gives the lowest total cost. (Note: this is unnecessary in reality, but we use it as an opportunity to use solver again). d. Use the EOQ (Economic Order Quantity) equation to confirm the optimal Q. i. Q=sqrt(2DS/H) Part 2: Safety Stock for a Continuous Review Inventory Control System Learning Objectives: Better understanding of Safety Stock Relationship between Cycle Service Level and Safety Stock Consider a case where daily demand averages 10 units/day, with a standard deviation of 3 units, and an order to a supplier for replenishment takes 10 days to fill (lead time is 10 days). 1. What will be average demand during the lead time (i.e. average demand over 10 days)? 2. What will be the standard deviation of demand during the lead time (over 10 days)? 3. What is meant by the term "Reorder Point"? What is meant by the term "cycle service level"? 4. Recall that in a continuous review system, the reorder point (R) can be found as: R= average demand during lead time + safety stock R=dL+zdLT Where the z-score corresponds to the "cycle service level" and dLt is the standard deviation of demand during the lead time. a. Go to the first cell under the "Cycle Service Level" column (i.e., B14) and enter the value ' 0.50 '. Use the Fill/Series functionality to create a list in this column that goes from 0.50 to 0.9999 in increments of 0.00005 . b. In row 15 , create equations for 'Avg lead time demand', 'Safety Stock' and 'Reorder Point'. Hint: use the NORMSINV () function to find the z-score that corresponds to a given service level (i.e., reference the service level as the argument for the NORMSINV function). c. Plot the safety stock values in a chart ("scatter with smooth lines"). What does the shape of the graph look like? What does this tell you? Part 3: ABC Analysis The textbook describes ABC analysis as dividing items into three classes, according to their dollar usage (demand* cost). The classes are approximately as follows (other texts may use slightly different numbers): A items represent 20% of items (SKUs), but account for 80% of dollar usage. B items represent 30% of items (SKUs), but only 15% of dollar usage. C items represent 50% of items (SKUs), but only 5% of dollar usage. 1. Using the data provided in the spreadsheet, partition the inventory items into three classes (A, B, C), based on dollar usage. Use your judgment for where the divisions between classes should be. a. List all SKUs and determine annual dollar usage of each (annual demand * cost per item). b. Sort in descending order of annual usage. c. For each SKU, determine cumulative annual dollar usage and cumulative percentage of annual dollar usage. d. Classify each SKU as A, B, or C. We will automate this using an IF() statement based on the literal meaning of the classes. In reality, one might plot the data and look for natural "breaks", for example where the differences in cumulative \% become noticeably less (line in the graph becomes flatter) - this is likely a good spot to separate the classes. 2. [This has been completed already]. Compute statistics for each classification - % of items and % of dollar usage. 0= If annual demand is 1739 , holding costs are $9 per unit per year, and setup costs are $89 per order, what is the economic order quantity? Answer: What is the total annual cost for an EOQ calculated using the following: D=2126S=24H=3 Answer: The Economic Order Quantity (EOQ) should always be rounded to two decimal places. Select one: True False Using lab 8 part 2 , how much safety stock is needed to have a cycle service level of at least 97% ? (Round answer to the nearest whole number) Answer: Using lab 8 part 2, how much additional safety stock is required to go from a cycle service level of 98% to 99% ? Select one: a. 3.00 b. 2.48 c. 2.00 d. 1.64 e. 1.96 f. 2.59 In general, ABC states that in theory: A items represent of items, B items represent of items, and C items represent the remaining of items. However, when looking at dollar values, A items account for B items represent , and C items represent of the dollar usage of items. 5%