Question

Please read the case (Medical Supplies for Banjul) description carefully, sketch an appropriate spreadsheet on paper, and then build and test a prototype model on excel. The purpose of the model is specified in the question. MEDICAL SUPPLIES FOR BANJUL You are the team leader of a unit of a U.S. nonprofit organization based in Banjul, Gambia (capital city). The nonprofit’s mission is to ensure that rural populations worldwide have access to health and sanitation-related supplies. Due to the sudden departure of one of your team leaders, you are taking over responsibility for ordering certain medical sup- plies for three villages. Each village consists of four groups: • Senior citizens (those over 65) • Children (population 12 and under) • Teens (those aged 13–19) • The population aged 20–65 The medical supplies required by each village include bandages (types A, B, and C), medical tape, and hearing aids. Children need type A bandages; teens need type B bandages; and adults (i.e., everyone else), need type C bandages. All members of the population use the same kind of medical tape. Only senior citizens require hearing aids. The former team member explained to you that a good rule of thumb is to ensure that at all times a village should keep in stock two bandages per person and hearing aids for 5 percent of the senior citizen population. Cost and packaging information for the products is as follows: • Type A bandages come in packages of 30. Each package costs $3.00. • Type B bandages come in packages of 30. Each package costs $5.00. • Type C bandages come in packages of 30. Each package costs $6.00. • Medical tape comes in rolls of 2 feet each. You usually use one roll per package of bandages. One roll costs $2.50. • Hearing aids are sold in single units(1perpackage) and are $5.00 each. POPULATION FOR EACH GROUP: Senior citizen: 500 Children: 3000 Teens: 2500 The population aged 20–65 : 9000 Your unit’s budget does not enable you to purchase more supplies than you need in a given quarter. At the end of every quarter, one of your team members provides you with the population count by age group for the village and the stocks remaining in each village. The former team member completed this cumbersome task by hand every quarter. However, owing to your other responsibilities, you will have no more than a few minutes to spend on this task on a quarterly basis—doing it by hand is out of question. In addition, you may be transferred to another post in three to six months, so you may have to pass on the responsibility to a successor before too long. It is 6:00 a.m. Monday morning. You have three hours left at the city headquarters until you leave to begin two weeks of fieldwork in the villages without computer access. The initial order must be placed by next Friday, so you will have to take care of procuring a check from the finance officer and placing the order before you leave. Other team members are beginning to arrive, but the office is still quiet. Everything else on your plate can wait until you return. Yet, something else could pop up at any moment, so you have to work quickly. You don’t have the latest population or stock figures yet, but the team member who has them will arrive at 8:30 a.m.

Answer 1

We can calculate the supplies based on the estimatd population mentioned in the case study above:

For bandages, as per the thum rule we have to keep 2 bandages for each person, hence

Type A bandages (For children ) = 3000 x 2 = 6000 i.e. 6000 /30 = 200 packages

Type B bandages ( For teens) = 2500 x 2 = 5000 i.e. 5000/30 = 167 packages (round off)

Type C bandages (All adults incl senior citizens) = (500 + 9000) x 2 = 19000 i.e. 19000 / 30 = 633 Packages

Medical tape (One roll for each package of bandage) = 200+167 +633 = 1000 medical tapes

Hearing aid = 500 x 5% = 25 sets

Now for this total budget required will be = (200x3) + (167x5) + (633x6) + (1000x2.5) + (25x5) = $7858

Changes can be done in the above formula after receing the stock and population details form the other team member.