Refer to the Medical Supplies for Banjul case. Design a spreadsheet that will enable you to request the required funds from your team’s finance officer, order supplies from the home office, and ensure dissemination of appropriate quantities to each village. Your final order to the home office should specify the number of packages required of each item:
You are the team leader of a unit of a US 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 supplies for three villages. Each village consists of four groups:
Senior citizens (Those over 65)
Children (population under 12)
Teens (those aged 13-19)
The population aged 20-65
The medical supplies required by each village include bandages (type a, b and c) medical tape and hearing aids. Children need type A bandages; teens need type B bandages; and adult (i.e. everyone else) need type C bandages. All members of the population use the same kind of medial tape. Only senior citizens require hearing aids. The former team member explained to you that a good rule of thrum 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 is $3.00
Type B Bandages come in packages of 30. Each package is $5.00
Type C Bandages come in packages of 30. Each package is $6.00
Medical tape comes in rolls of 2 feet each. You usually use one roll per package of bandages. One roll is $2.50
Hearing aids are sold in single units (1 per package) and are $5.00 each.
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 for the village and the stocks remaining in each village.
The former team member completed this cumbersome task by hand every quarter. However owning 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 the 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 6am 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 financing 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 pate 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.