Medical Supplies for Banjul Model 1The SituationYou are the team leader for a unit of a non-profit organization in Banjul, Gambia. The non-profits mission is to ensure that rural populations world-wide have access to health and sanitation-related supplies. Due to the sudden departure of another team leader, you are taking over responsibility for ordering certain medical supplies for three villages. Each village consists of four demographic groups:Children under the age of 13Teens between the ages of 13 and 19Adults between the ages of 20 and 65Senior citizens over the age of 65The medical supplies for each village consist of:Bandages, types A, B and CMedical tapeHearing aidsChildren need type A bandages, teens need type B bandages, everyone else needs type C bandages. All members of the population use the same medical tape. Only seniors require hearing aids. A rule of thumb used in the past suggests that for each village, inventories should consist of two bandages per person and hearing aids for 5% of the population. One roll of medical tape is typically used per package of bandages. Your units budget does not allow you to purchase more supplies than are needed in a given quarter. At the end of each quarter, your team members provide you with the population count per village and the inventories of medical supplies. The former team leader completed this cumbersome task manually each quarter. However, due to your other responsibilities, you will not be able to devote more than a few minutes to this task. In addition to that, you are on a flight to another country in three hours and must prepare a working model before you leave. You do not have access to data and will have to communicate by email later with an administrative assistant about entering the actual data.Thinking About a Model1.The last two sentences are intended to make the point that you do not need data to create a model. You can use plausible numbers as placeholders, verify that the model is working as you want it to, and that it has the ability to provide useful insights.2.A good place to start with your spreadsheet design is to define the output. In this case, it should seem reasonable that you need to create a purchase order. Furthermore, this purchase order must be reusable, i.e., a copy that can be updated every month with current data and can automatically calculate purchases would seem appropriate. We need to decide whether supplies can be shipped between villages during the quarter. The problem is silent on that, it could be either case. For our purposes, we will create a model where shipment between villages is not possible.3.The next step is to understand the timeline and the inventory model. Suppose we are doing our analysis on day X of the current quarter. We will make one purchase of supplies that will bring the inventory level to a target where it will be sufficient for estimated demand for the remainder of the quarter. Note that this is a simplification of the problem. Normally, we would have information about demand, including variability, and have to make a decision about adequate inventory levels. That inventory rule of thumb was given as two bandages per person per quarter and 5% of the senior population for hearing aids. Rolls of tape are a function of the number of packages of bandages.

Page

of 2

ZOOM

Medical Supplies for Banjul Model 1The SituationYou are the team leader for a unit of a non-profit organization in Banjul, Gambia. The non-profits mission is to ensure that rural populations world-wide have access to health and sanitation-related supplies. Due to the sudden departure of another team leader, you are taking over responsibility for ordering certain medical supplies for three villages. Each village consists of four demographic groups:Children under the age of 13Teens between the ages of 13 and 19Adults between the ages of 20 and 65Senior citizens over the age of 65The medical supplies for each village consist of:Bandages, types A, B and CMedical tapeHearing aidsChildren need type A bandages, teens need type B bandages, everyone else needs type C bandages. All members of the population use the same medical tape. Only seniors require hearing aids. A rule of thumb used in the past suggests that for each village, inventories should consist of two bandages per person and hearing aids for 5% of the population. One roll of medical tape is typically used per package of bandages. Your units budget does not allow you to purchase more supplies than are needed in a given quarter. At the end of each quarter, your team members provide you with the population count per village and the inventories of medical supplies. The former team leader completed this cumbersome task manually each quarter. However, due to your other responsibilities, you will not be able to devote more than a few minutes to this task. In addition to that, you are on a flight to another country in three hours and must prepare a working model before you leave. You do not have access to data and will have to communicate by email later with an administrative assistant about entering the actual data.Thinking About a Model1.The last two sentences are intended to make the point that you do not need data to create a model. You can use plausible numbers as placeholders, verify that the model is working as you want it to, and that it has the ability to provide useful insights.2.A good place to start with your spreadsheet design is to define the output. In this case, it should seem reasonable that you need to create a purchase order. Furthermore, this purchase order must be reusable, i.e., a copy that can be updated every month with current data and can automatically calculate purchases would seem appropriate. We need to decide whether supplies can be shipped between villages during the quarter. The problem is silent on that, it could be either case. For our purposes, we will create a model where shipment between villages is not possible.3.The next step is to understand the timeline and the inventory model. Suppose we are doing our analysis on day X of the current quarter. We will make one purchase of supplies that will bring the inventory level to a target where it will be sufficient for estimated demand for the remainder of the quarter. Note that this is a simplification of the problem. Normally, we would have information about demand, including variability, and have to make a decision about adequate inventory levels. That inventory rule of thumb was given as two bandages per person per quarter and 5% of the senior population for hearing aids. Rolls of tape are a function of the number of packages of bandages.

Build model 1You are now ready to build your model. At this point, the assumptions about data are irrelevant. They are only placeholders that allow you to create a model that works properly. Having said that, for our purposes, it is useful for everyone to be using the same assumptions. This permits you to verify that your model is working correctly, i.e., the same as everyone elses model. Once it is, you (or someone else) can fill in the actual data for the three villages and the medical supplies.

Common assumptions: population Village1 Village2 Village3 Totalchildren 200, 300,100,600, teens 300,400,200,900,adults,500,700,400,1,600,seniors, 400,500,300,1,200,total 1,400,1,900,1,000,3,100 Current inventory (#packages)Village1 Village2 Village3 type A 1.0,2.0,1.0,4.0, type B,1.0,3.0,1.0,5.0 type C ,2.0,4.0,2.0,8.0,medical tape, 10.0,15.0,6.0,31.0,hearing aids, 3.0,5.0,2.0,10.0

Bandages #bandagesperpackage costperpackage targetinventory* TypeA 40 $4.00 0.05 TypeB 40 $5.00 0.05 TypeC 40 $7.00 0.05 #rollsperpackage costperroll

Medicaltape 1.0 $2.50 costperunit targetinventory

**Hearingaids $25.00 5.0%**%ofseniorpopulation