Please help!! I can email the excel document. Please leave an email address and I will get it to you!

i can send the excel document via email of that helps.

Clarinda Community Hospital is a 230-bed, not-for-profit, acute care hospital located in Clarinda, Iowa. The city is a typical Midwestern county seat and farming community best known as the birthplace of Glenn Miller, the famous big band leader of the late 1930s and early 1940s. Clarinda carries more than 10,000 different items of inventory that vary widely in price, order lead times, and stockout costs. (Stockout costs are the total costs that result from running out of stock of a particular inventory item, including higher costs of service caused by scheduling delays or emergency replenishments as well as the costs associated with negative patient outcomes and potential lawsuits.) It uses the activity-based costing (ABC) method of inventory classification, also called "selective inventory control," along with a variety of inventory-control methods to manage its different inventory items. The ABC classification system works in this way: Clarinda maintains data on the average annual usage and unit cost of each inventory item, which typically is called a stock-keeping unit (SKU). Then, the dollar usage (Average annual usage Unit cost) is calculated for each SKU. Next, these amounts are converted into percentages of total dollar usage, and the SKUs are arrayed from highest to lowest percentage. The SKUs are then divided into three groups (or classes)-labeled A,B, and C - using the general guidelines provided in exhibit 28.1. To better use the limited resources available for inventory management, Clarinda's managers focus most of their attention on Class A items. The usage rates, stock positions, and delivery times for SKUs in this class are reviewed on a biweekly basis, with control and ordering system data adjusted Cases in Healthcare Finance as necessary. Class B items are reviewed every quarter, while Class C items are reviewed semiannually. Even though this process has served Clarinda well, Marco Muoz, its new chief financial officer, thinks the hospital is carrying excess inventories. He notes that Clarinda has never come close to having a stockout, even when it has been running near 100 percent occupancy. He believes that a thorough review should be undertaken of all Class A items and that inventory turnover might be increased by 25 percent, and hence might lower inventory carrying costs, by trimming current stocks. To convince Clarinda's CEO, Marco plans to perform a demonstration inventory analysis that focuses on the forms used by the surgical intensive care unit (SICU). Different forms are required for almost every aspect of SICU operations, including records of patient progress; requests for lab tests, blood products, and medications; nurse and physician notes; and transfer/ discharge instructions. Exhibit 28.2 shows inventory usage and cost data on the SICU's 25 forms. To begin his demonstration analysis, Marco plans to conduct a new ABC analysis on the SICU's inventory. (Clarinda is in the process of converting to web-based forms as part of the transition to electronic medical records, but the paper forms will still be used for several years.) As part of its commitment to supporting the local economy, Clarinda currently uses a single local source for all of the SICU's forms: Atwood Printing and Office Supplies (Supplier A). Supplier A requires a $25 set-up fee on each order, in addition to the cost per unit. Clarinda is considering using a national supplier, Bateman Medical Office Products (Supplier B), which charges no set-up fee but does charge $50 to cover postage and handling. Supplier B takes three days to deliver the forms, as opposed to only one day for Supplier A. Processing each order will cost Clarinda another $25, regardless of which supplier is used. Thus, the total order cost is $50 for Supplier A and $75 for Supplier B. Marco's ultimate goal is to use economic ordering quantity (EOQ) concepts to select the supplier for all forms used by the SICU. His primary areas of concern are (1) the number of orders placed each year, (2) reorder points (in units), and (3) total inventory costs. As part of the demonstration analysis, Marco will focus on the form used to order blood products from Clarinda's blood bank (SKU number 53104 in exhibit 28.2). The data associated with Suppliers A and B, as well as the inventory carrying costs and other data, are summarized in exhibit 28.3. In addition to an analysis without safety stocks, Marco is concerned about the impact of safety stocks on the decision. Clarinda currently carries a safety stock of two units of SKU 53104 to protect itself against stockouts as a result of delivery delays and/or an increase in the usage rate. When asked how that amount was calculated, the manager of the SICU stated that she didn't know but the hospital had always done it that way. However, if Clarinda decides to switch to Supplier B, the manager stated that it seems logical to increase the safety stock to six units to reflect Supplier B's three-times-as-long lead time. The following are of particular interest: the impact of safety stocks on inventory costs, the safety margins that such stocks would provide against higher-than-expected usage and shipping delays, and whether the current lead time for Supplier A and the estimate for Supplier B make any sense. Marco has heard a rumor that Supplier B is about to offer a 10 percent discount if the entire year's demand ( 92 units) is ordered at once. He wants to know what the impact of this discount would be on the decision about which supplier to use. How high a discount is needed to make Supplier B less costly than Supplier A is also useful to know. Furthermore, Marco is aware that the forms would not likely be ordered exactly as prescribed by the EOQ model, so he would like to know the impact of ordering variations on total inventory costs. Finally, he knows that Clarinda's CEO has expressed some doubt about the value of the EOQ model in making real-world inventory decisions. "If I'm right in my concerns," he asked, "what other inventory control methods are available to us?" Place yourself in Marco's shoes to see if you can conduct the demonstration analysis that he has in mind. Cases in Healthcare Finance Case 28: Clarinda Community Hospital 187 1. What is the economic ordering quantity for SKU Number 53104 if the forms are ordered from Supplier A? From Supplier B? Round your answers up to the next whole unit. 2. How many orders should be placed each year if the hospital buys from Supplier A? If it buys from Supplier B? 3. Calculate the total inventory cost (the cost of ordering plus the cost of carrying inventories) that the hospital would incur under each supplier. On the basis of the information developed, which supplier should the hospital use? INPUT DATA: KEY OUTPUT: MODEL-GENERATED OUTPUT: Without discount or safety stock: Note: Rounding the EOQ to the nearest unit creates a rounding difference in total carrying costs and total ordering costs, and hence they are not identical at the EOQ ordering quantity. With safety stock but without discount: With discount but without safety stock: Note: Only Supplier B is offering a discount, so this part of the model is not applicable to Supplier A. With both discount and safety stock: Clarinda Community Hospital is a 230-bed, not-for-profit, acute care hospital located in Clarinda, Iowa. The city is a typical Midwestern county seat and farming community best known as the birthplace of Glenn Miller, the famous big band leader of the late 1930s and early 1940s. Clarinda carries more than 10,000 different items of inventory that vary widely in price, order lead times, and stockout costs. (Stockout costs are the total costs that result from running out of stock of a particular inventory item, including higher costs of service caused by scheduling delays or emergency replenishments as well as the costs associated with negative patient outcomes and potential lawsuits.) It uses the activity-based costing (ABC) method of inventory classification, also called "selective inventory control," along with a variety of inventory-control methods to manage its different inventory items. The ABC classification system works in this way: Clarinda maintains data on the average annual usage and unit cost of each inventory item, which typically is called a stock-keeping unit (SKU). Then, the dollar usage (Average annual usage Unit cost) is calculated for each SKU. Next, these amounts are converted into percentages of total dollar usage, and the SKUs are arrayed from highest to lowest percentage. The SKUs are then divided into three groups (or classes)-labeled A,B, and C - using the general guidelines provided in exhibit 28.1. To better use the limited resources available for inventory management, Clarinda's managers focus most of their attention on Class A items. The usage rates, stock positions, and delivery times for SKUs in this class are reviewed on a biweekly basis, with control and ordering system data adjusted Cases in Healthcare Finance as necessary. Class B items are reviewed every quarter, while Class C items are reviewed semiannually. Even though this process has served Clarinda well, Marco Muoz, its new chief financial officer, thinks the hospital is carrying excess inventories. He notes that Clarinda has never come close to having a stockout, even when it has been running near 100 percent occupancy. He believes that a thorough review should be undertaken of all Class A items and that inventory turnover might be increased by 25 percent, and hence might lower inventory carrying costs, by trimming current stocks. To convince Clarinda's CEO, Marco plans to perform a demonstration inventory analysis that focuses on the forms used by the surgical intensive care unit (SICU). Different forms are required for almost every aspect of SICU operations, including records of patient progress; requests for lab tests, blood products, and medications; nurse and physician notes; and transfer/ discharge instructions. Exhibit 28.2 shows inventory usage and cost data on the SICU's 25 forms. To begin his demonstration analysis, Marco plans to conduct a new ABC analysis on the SICU's inventory. (Clarinda is in the process of converting to web-based forms as part of the transition to electronic medical records, but the paper forms will still be used for several years.) As part of its commitment to supporting the local economy, Clarinda currently uses a single local source for all of the SICU's forms: Atwood Printing and Office Supplies (Supplier A). Supplier A requires a $25 set-up fee on each order, in addition to the cost per unit. Clarinda is considering using a national supplier, Bateman Medical Office Products (Supplier B), which charges no set-up fee but does charge $50 to cover postage and handling. Supplier B takes three days to deliver the forms, as opposed to only one day for Supplier A. Processing each order will cost Clarinda another $25, regardless of which supplier is used. Thus, the total order cost is $50 for Supplier A and $75 for Supplier B. Marco's ultimate goal is to use economic ordering quantity (EOQ) concepts to select the supplier for all forms used by the SICU. His primary areas of concern are (1) the number of orders placed each year, (2) reorder points (in units), and (3) total inventory costs. As part of the demonstration analysis, Marco will focus on the form used to order blood products from Clarinda's blood bank (SKU number 53104 in exhibit 28.2). The data associated with Suppliers A and B, as well as the inventory carrying costs and other data, are summarized in exhibit 28.3. In addition to an analysis without safety stocks, Marco is concerned about the impact of safety stocks on the decision. Clarinda currently carries a safety stock of two units of SKU 53104 to protect itself against stockouts as a result of delivery delays and/or an increase in the usage rate. When asked how that amount was calculated, the manager of the SICU stated that she didn't know but the hospital had always done it that way. However, if Clarinda decides to switch to Supplier B, the manager stated that it seems logical to increase the safety stock to six units to reflect Supplier B's three-times-as-long lead time. The following are of particular interest: the impact of safety stocks on inventory costs, the safety margins that such stocks would provide against higher-than-expected usage and shipping delays, and whether the current lead time for Supplier A and the estimate for Supplier B make any sense. Marco has heard a rumor that Supplier B is about to offer a 10 percent discount if the entire year's demand ( 92 units) is ordered at once. He wants to know what the impact of this discount would be on the decision about which supplier to use. How high a discount is needed to make Supplier B less costly than Supplier A is also useful to know. Furthermore, Marco is aware that the forms would not likely be ordered exactly as prescribed by the EOQ model, so he would like to know the impact of ordering variations on total inventory costs. Finally, he knows that Clarinda's CEO has expressed some doubt about the value of the EOQ model in making real-world inventory decisions. "If I'm right in my concerns," he asked, "what other inventory control methods are available to us?" Place yourself in Marco's shoes to see if you can conduct the demonstration analysis that he has in mind. Cases in Healthcare Finance Case 28: Clarinda Community Hospital 187 1. What is the economic ordering quantity for SKU Number 53104 if the forms are ordered from Supplier A? From Supplier B? Round your answers up to the next whole unit. 2. How many orders should be placed each year if the hospital buys from Supplier A? If it buys from Supplier B? 3. Calculate the total inventory cost (the cost of ordering plus the cost of carrying inventories) that the hospital would incur under each supplier. On the basis of the information developed, which supplier should the hospital use? INPUT DATA: KEY OUTPUT: MODEL-GENERATED OUTPUT: Without discount or safety stock: Note: Rounding the EOQ to the nearest unit creates a rounding difference in total carrying costs and total ordering costs, and hence they are not identical at the EOQ ordering quantity. With safety stock but without discount: With discount but without safety stock: Note: Only Supplier B is offering a discount, so this part of the model is not applicable to Supplier A. With both discount and safety stock