UN Millenium Project- Reproductive Health Needs Assessment Model- User Guide
Publication date: 2005
MDG Needs Assessment Tools Reproductive Health Needs Assessment Model USER GUIDE DRAFT v. 1.1 18 July 2005 This User Guide is designed to be used in conjunction with the Reproductive Health Needs Assessment model available at http://www.unmillenniumproject.org/policy/index.htm This User Guide was prepared by Eva Weissman Comments and suggestions are welcome and should be sent to the author at email@example.com TABLE OF CONTENTS I. NEEDS ASSESSMENT OVERVIEW .1 II. REPRODUCTIVE HEALTH MODEL BASICS .4 Objective .4 Scope.4 Limitations .4 Data Requirements.5 Methodology.5 Components of the Model.6 III. USING THE RH MODEL.7 Installing the Model .7 Getting Started .7 RH Model Part 1 – Cost per Case.10 RH Model Part 2 – Total Cost .13 RH Model Part 3 – Database .15 IV. ADAPTING THE RH MODEL .16 Dropping Interventions .16 Adding interventions.16 V. CHECKING THE RESULTS AND TROUBLESHOOTING.17 Checking the Results.17 Troubleshooting .18 Error Message on the Total Cost Summary Sheet .20 I. NEEDS ASSESSMENT OVERVIEW This user guide is a step-by-step introduction to the UN Millennium Project’s maternal and reproductive health needs assessment tool. It assumes that users have read the Handbook1 and have a basic familiarity with the fundamentals of an MDG Needs Assessment, but does not presume any prior technical knowledge of MDG needs assessment tools. The guide should be used concurrently with the maternal and reproductive health needs assessment tool, available at www.unmillenniumproject.org/policy. In conjunction with the Handbook, it aims to help users embark on an MDG-based maternal health needs assessment. Based on data input by the user, the maternal health needs assessment tool estimates the associated costs to support maternal health interventions as part of a strategy for meeting the MDGs at the national level. These estimates, along with estimates from other thematic areas (education, gender, rural and urban development, etc.) will help provide the basis for a national investment strategy for meeting the Millennium Development Goals. MDG Needs Assessments MDG needs assessments are the analytical building blocks for developing MDG-based poverty reduction strategies. They aim at helping governments to answer the question, “What investments will it take to meet the MDGs by 2015?” This approach marks a fundamental shift from current practice to strategy design, which asks the question: “How can governments best allocate existing resources?” Traditional sectoral work is thus based on forming annual budget allocations in a resource-constrained setting. An MDG Needs Assessment aims instead to help countries identify what resources are needed each year over a 10-year period to meet the MDGs by 2015. The resulting estimates can then be core inputs to an MDG investment strategy, including sequencing and capacity building, which, along with a policy and implementation outline, comprise a 10-year framework for meeting the MDGs. The Handbook specifies an approach to creating an MDG-based PRS, and describes in detail the steps required to conduct an MDG needs assessment. This introduction will briefly outline these steps, and the role that the maternal health model plays in the overall MDG needs assessment process. First and foremost, the MDGs need to be interpreted at the country level. This entails defining quantitative outcome targets that are meaningful at the national level, and defining the areas of intervention that are needed to meet each of the MDGs. For maternal health, the MDG target aims to reduce the maternal mortality ratio by ¾ by 2015, but countries may wish to alter the target – for example, by aiming to achieve it before 2015. Once outcome targets have been set, there are four steps in conducting a needs assessment, illustrated in Figure 1 and described below. 1 UN Millennium Project. Forthcoming 2005. Preparing MDG-Based Poverty Reduction Strategies: A Handbook of Best Practice. - 1 - Figure 1: Steps in an MDG Needs Assessment 1. Develop list of interventions 2. Specify targets for each set of interventions 3. Estimate resource needs 4. Check results 1. Develop list of interventions 2. Specify targets for each set of interventions 3. Estimate resource needs 4. Check results 1 – Develop list of interventions Users first need to define the critical interventions required to meet the MDGs. As outlined in the Handbook, interventions are defined broadly here as goods, services and infrastructure that need to be provided to generate outcomes. For maternal health, interventions include, for example, provision of antenatal and emergency obstetric care. The UN Millennium Project recommends that thematic working groups be organized as part of the MDG-based planning process. These groups will help to guide the selection of a comprehensive set of interventions that comprise each investment cluster [see Step 2 of the Handbook]. In many cases countries will have already elaborated such interventions in their national and sectoral planning documents. These documents should be a starting place for defining MDG interventions. The UN Millennium Project has drawn up sample lists of interventions to reach the MDGs that can also be an input into thematic working group discussions. This list will then have to be modified and adapted to national needs. The maternal health interventions from these lists are the basis of the interventions outlined in this model. 2 – Specify targets for each set of interventions Once national outcome targets have been set and interventions have been identified, countries need to determine who the interventions should reach, what proportion of this population will need to be covered by 2015, and how many units of each intervention are needed to reach them. This requires setting targets for each intervention and input quantity ratios that relate interventions to the people they reach. Where relevant, targets and their corresponding interventions can be disaggregated by age and gender as well as by urban and rural areas. For example, urban and rural areas often require distinct interventions and technologies or face very different unit costs.2 Disaggregation by gender and age will help countries better target services to populations in need and to adjust their service delivery to a changing demographic profile. You will find advice on using the models to reflect additional disaggregation in Part Four of this guide: Adapting the Model. 3 - Estimate resource needs The next step is to estimate the financial, human and other resources needed to achieve the identified targets. The UN Millennium Project’s maternal and reproductive health model is designed to assist countries in making these estimates. This Excel-based needs assessment tool 2 A clear distinction between urban and rural needs is particularly warranted for the following categories: water supply and sanitation, transport infrastructure and energy services. - 2 - integrates the information input by the user to generate these estimates. It uses outcome targets, coverage targets and ratios, and unit costs to develop aggregate as well as intervention-by- intervention estimates of resource requirements. Similarly, simple ratios between beneficiaries, HR parameters, and infrastructure yield the non-monetary results. A simple ten-year scale up path allows users to map out the yearly investments needed to meet 2015 targets. The model aims to be transparent and adaptable to national needs. This user guide focuses largely on explaining how to use and adapt this model. 4 - Check Results With any needs assessment, the results should be carefully reviewed to make sure that they are accurate and adequate to reach the MDGs. While every country will obtain different results based on local circumstances, the UN Millennium Project has carried out preliminary needs assessments in several countries that can serve as a basis for comparison. These results provide some guidance on the order of magnitude of the costs for reaching the MDGs in a subset of low income countries. See the Handbook for sample results across areas and countries. The maternal health needs assessment is part of a broad MDG strategy that covers all investment areas. Once needs assessments are completed for all investment clusters, they need to be aggregated and integrated as a first step in creating a ten-year MDG framework. As part of this consolidation process, countries should produce one summary budget outlining the projected expenditures for meeting the MDGs. In practice, this means that each model should contain a summary output page that can be easily summed and manipulated across clusters. This model has a [“summary outputs page”] that is formatted for incorporation in the UN Millennium Project’s “financing model”.3 This user guide is designed to explain the use of the needs assessment tool as clearly and simply as possible. As you work through it, please feel free to contact the UN Millennium Project with any comments, questions, or suggestions for improvement. We look forward to hearing from you and wish you good luck in the needs assessment process. 3 In addition to aggregation, this model allows countries to calculate the investments that can be financed by households and domestic government, and the remaining needs that will have to be financed by other sources such as ODA. - 3 - II. REPRODUCTIVE HEALTH MODEL BASICS Objective Universal access to reproductive health services is considered crucial not only for the achievement of the maternal health goal of the MDGs (MDG 5), but also for achieving MDG 4 (reducing child mortality) and MDG 6 (halting the spread of HIV/AIDS and malaria). The Reproductive Health (RH) costing model is designed to help countries estimate how much it would cost to scale-up a basic package of reproductive health services - ranging from family planning, antenatal and delivery care to emergency obstetric care and STI treatment - from current to universal coverage levels. Scope This model allows the user to estimate the direct costs associated with providing a set of 33 reproductive health interventions as outlined below. Family Planning ANC and Delivery Care Obstetric Complications 1 Oral Contraceptives (Pill) 2 Injectables 3 Condom - Male 4 Condom - Female 5 Intrauterine Device (IUD) 6 Implant 7 Sterilization - Female 8 Sterilization - Male 9 Other Methods 10 Emergency Contraceptives 11 Antenatal Care (ANC) 12 Malaria Prevention within ANC 13 Malaria Treatment within ANC 14 Delivery Care 15 Postpartum Care 16 Prolonged Labor (>18 hours) 17 Forceps or Vacuum-Assisted Delivery (AVD) 18 Cesarean Section (C-Section) 19 Postpartum Hemorrhage 20 Puerperal Sepsis 21 Hypertensive Disorders of Pregnancy 22 Postabortion Complications (PAC) Other Maternal Conditions Newborn Interventions Sexually Transmitted Infections 23 Obstetric Fistula (OF) 24 Urinary Tract Infection 25 Mastitis 26 Prevention of Ophtalmia Neonatorum 27 Treatment of Neonatal Complications (LBW, Sepsis, etc.) 28 Prevention of Mother-to-Child Transmission of HIV (PMTCT) 29 Chlamydia 30 Gonorrhea 31 Syphilis 32 Trichomonas 33 Pelvic Inflammatory Disease Limitations Currently, the model contains the interventions shown above. Not included, even though they theoretically fall under the area of reproductive health, are interventions related to HIV/AIDS prevention and treatment, as the Millennium Project offers separate models for them4. Also not included are, for instance, the detection and treatment of reproductive cancers, such as ovarian and breast cancer and the treatment of infertility. If a country wishes to include these interventions in its package of essential RH interventions, it needs to add them to the model (for instructions see page 15). Even though the model produces estimates for personnel costs, its main focus is on the costs of drugs and supplies. Because reproductive health services are usually provided with other health services in a rather integrated manner, it is generally recommended to calculate personnel costs in a separate model that deals with the human resource requirements of the health system as a whole. For the same reason, the RH model also does not include facility-related costs such as the cost of buildings and equipment or overhead costs. 4 The RH model does include, however, costing of prevention of mother-to-child-transmission of HIV/AIDS (PMTCT) - 4 - Data Requirements The inputs required to run the RH model fall into 5 categories: 1. Demographic data (number of women of reproductive age, number of births, etc.). 2. Epidemiological data (e.g., the incidence/prevalence of pregnancy complications & STIs), 3. Current coverage rates and coverage targets for the different types of interventions (% of women receiving antenatal care, etc.). 4. Information about the drugs and supplies required for each of the interventions 5. Cost data (cost of drugs and supplies and personnel) 5 The above information does not need to be collected from scratch. The model comes with a large database that – for over 200 countries – contains almost all of the above information. On opening the model, the user selects the name of the country from a drop-down list and the model automatically updates itself with the required information. The main task of the user thus consists of reviewing these inputs. Most of the data in the database comes from UN sources such as the UN Population Division, WHO's Global Burden of Disease and other databases, UNICEF's maternal health database, and Demographic and Health Surveys where available. The drugs and supplies required to provide the different interventions are based on WHO treatment guidelines. Drug prices are based on quotes from the UNICEF Supply Catalogue and the MSH International Drug Price Indicator6. Personnel cost/salaries are based on information provided by WHO’s CHOICE project. There may be more recent or better data available at the country level. The user should therefore spend some time reviewing the inputs provided by the database and replace them with better numbers where appropriate. Methodology The model first calculates the average cost of providing each of the interventions contained in the RH essential package by identifying all the drugs, supplies and personnel inputs required to provide the intervention. These inputs are then costed to arrive at an average cost per case. The model then identifies the target population for each of the interventions (all pregnant women for antenatal care, for instance, or all married women or women in union for family planning). Current coverage with these interventions is assessed and target coverage rates to be achieved by the year 2015 are set. Coverage is then scaled up over the years to achieve the target coverage in the final year of the projection (as universal coverage with a basic set of RH interventions is seen as a prerequisite for achieving the MDGs, the target coverage rate would in most cases be 100%). The scale-up from current coverage rates to universal coverage (100%) is assumed to be linear, i.e., growing at the same percentage increase a year, an assumption which, of course, can be modified by the user, if desired. The target populations for the different interventions are then multiplied by the coverage rates in the respective years to arrive at the number of persons actually covered by the interventions. These are then multiplied by the cost per case to arrive at the total resource requirements. 5 The UN Millennium Project recommends costing the personnel component separately. See the Health Systems user guide for more information. 6 UNICEF Supply Catalogue: http://www.supply.unicef.dk/catalogue, and MSH International Drug Price Indicator: http://erc.msh.org/dmpguide - 5 - Number of Women Requiring the Intervention Drug and Supplies Required Human Resources Required Coverage Rate Number of Women Receiving the Intervention Cost per Case Total Cost/Resource Requirements x + x = = = Number of Women Requiring the Intervention Drug and Supplies Required Human Resources Required Coverage Rate Number of Women Receiving the Intervention Cost per Case Total Cost/Resource Requirements x + x = = = Components of the Model The RH Costing Model consists of three Excel files: 1. RH Model Part 1 – Cost per Case 2. RH Model Part 2 – Total Cost 3. RH Model Part 3 – Database “Part 1 - Cost per Case” lists all the drugs, supplies and staff time requirements for each of the interventions and costs them to arrive at an average cost per case. “Part 2 – Total Cost” estimates the number of women/ newborns requiring the different interventions and -- using the information provided by the Cost per Case model -- calculates the total cost. Part 3 is the underlying database which supplies data to the other parts of the tool. In addition, there is a data collection form that can be used to review the required data for “Part 2 – Total Cost.” - 6 - III. USING THE RH MODEL Installing the Model As mentioned, the RH Costing Model consists of three Excel files. When transferring the files, care should be taken to save all files into the same folder. It is recommended to save an additional copy of the models in a safe location for back up. Getting Started Required Computer Setting The models contain several macros designed to facilitate certain processes and calculations. These macros will only work if the security setting on the computer being used is set to medium or low security. To adjust the setting, the user needs to open Excel, go to the Tools menu, and select Macro, Security. In the dialog box that will appear, the user should choose the medium security option. It is necessary to close Excel for the change to take effect. Opening the Models The user will mainly be working with “Part 1 – RH Cost per Case” and “Part 2 – RH Total Cost” of the tool. Part 3, which contains the database, does not need to be open to work; it automatically supplies data to the other parts of the tool. On opening the files, the user will see two dialog boxes, the first one asking whether to enable the macros in the model. The answer should be “Enable Macros.” The second dialog box will ask whether the user wants to update the links. The answer should be “Yes.” Viewing the spreadsheets As most of the spreadsheets are relatively large, it will not be possible to see the entire content of a particular sheet on the computer screen. In order to get a better idea of what the different worksheets look like, it is recommended to make an initial printout of all the worksheets of the models7. 7 The sheets have been configured for optimal printing. - 7 - Computer screens vary in their screen resolutions. It may be necessary to adjust the amount of information visible on the screen by adjusting the settings on the formatting toolbar. Color Coding The cells of all the models are color-coded (all parts of the model follow the same system). Cells with red and blue font are cells that contain user input, while cells with black font contain either descriptions or formulas which should not be changed. Red font denotes an input that needs to be set by the user (e.g. target coverage rates), while blue denotes inputs that should be reviewed and replaced with better, or more recent, national data where available. Main Input Data Country: Malawi 14.2% 100% 100% 100% 100% 100% Select desired country from drop-down list above POPULATION 2005 2015 Source/Comments Total Population 12,883,935 15,997,810 UN Population projections, 2004 Revision Women of Reproductive Age (15-49) 2,827,386 3,650,411 UN Population projections, 2004 Revision % Women Married 71% 67% UN Population Division, assumption that rate will go down by 5% by 2015 HIV Prevalence 14.2% Report on global AIDS epidemic (UNAIDS 2004) COVERAGE RATES 2005 2015 Target Source/Comments Antenatal Care 90% DHS 1992, Final report, p. 79 Skilled Attendance at Delivery 55% DHS 1992, Final report, p. 84 Postpartum Care 54% No data available, assumed to be same as facility-based delivery (Source: DHS) Emergency Obstetric Care 27% No data available, assumed to be 50% of delivery coverage STI Testing and Treatment 45% No data available, assumed to be 50% of ANC coverage Getting around The models contain some features to make it easier to navigate between the different sheets. “Part 1 – Cost per Case” and “Part 2 – Total Cost” contain one major input sheet, all other sheets of these models show a button in the top right corner, which, when clicked, will return the user to the “Main Input Data” sheet. Back to Main Input Sheet Press buttons below to get to intervention sheets Family Planning Short-Term Methods 1 Oral Contraceptives (Pill) 2 Injectables 3 Condom - Male 4 Condom - Female LIST of INTERVENTIONS “Part 1 – the Cost per Case” model, in addition also has buttons on the main input sheet that, when clicked, will send the user to the different intervention sheets (see graphic on the left). - 8 - Printing the model Before printing, the user should make sure the formatting is set to the right paper size (A4 or letter, respectively). To check, the user should go to File, Page Setup and check the information provided under Paper Size. Should the currently selected paper size be different form the local paper size, the user should click one of the blue buttons at the top of the “Main Input” sheet in “Part 2 – Total Cost” and the “Main Menu” sheet in “Part 1 – Cost per Case” to convert the entire model to the right paper size. Convert to A4 size paper Convert to letter size paper The following sections describe the different parts of the model in more detail. - 9 - RH Model Part 1 – Cost per Case The “Part 1 – Cost per Case” calculates the average cost per case for each of the RH interventions described on page 3. This information is then fed into the Part 2 - Total Cost model where it is used together with information on the number of women requiring the different interventions to calculate the total cost. Worksheets The model consists of 38 worksheets. Besides the cover page and a summary sheet, there is one sheet for each of the 33 interventions plus three price lists, the first one showing the cost of contraceptives, the second one the cost of drugs and supplies and the last one showing the cost of medical staff time. Each of the intervention sheets contains a detailed list of all the drug and personnel inputs required to provide that particular intervention. The following shows the sheet for antenatal care: ANTENATAL CARE DRUGS AND SUPPLIES REQUIRED PER CASE Description of treatment line % receiving this treatment Note Number Times per day Days per case/ episode Treatment units per case/ episode Unit cost Cost per Average Case Drugs and Supplements Ferrous Salt + Folic Acid, tablet, 200+0.25 mg (60mg iron) 100% 6 months of iron supplementation 1 2 180 360 $0.00 $0.60 Mebendazole, chewable tablet, 100 mg 30% Hookworm treatment 1 2 3 6 $0.01 $0.01 Tetanus toxoid, injection 100% Tetanus vaccination 1 1 2 2 $0.05 $0.10 Syringe, disposable, 2 ml, with needle 100% For tetanus vaccinations 1 1 2 2 $0.03 $0.06 Alcohol swab 100% For tetanus vaccinations 1 1 1 1 $0.02 $0.02 Tests Lancet, blood, disposable 100% For taking blood sample 1 1 1 1 $0.01 $0.01 Alcohol swab 100% For taking blood sample 1 1 1 1 $0.02 $0.02 Blood collection tube 100% For taking blood sample 1 1 1 1 $0.03 $0.03 Test, blood group, anti A + B, 10 ml 100% test for blood group and Rh factor 1 1 1 1 $0.02 $0.02 Test, Blood glucose 100% test for blood sugar 1 1 1 1 $0.24 $0.24 Test, hemoglobin 100% Anaemia screening 1 1 1 1 $0.98 $0.98 Pregnancy test 100% Pregnancy test 1 1 1 1 $0.15 $0.15 Test, Rapid plasma reagin (RPR) 100% Syphillis test 1 1 1 1 $0.08 $0.08 Test HIV 1 + 2, Doublecheck, rapid test 100% HIV test 1 1 1 1 $1.15 $1.15 Test strips, urine (blood/bilirubin/urobilinogen/ketones/ protein/glucose/PH) 100% Urine Test 1 1 4 4 $0.11 $0.46 Other Gloves, surgeon’s, latex, size 7-1/2, disposable, sterile, pair 100% Examination 1 1 1 1 $0.22 $0.22 Antenatal care record 100% ANC record 1 1 1 1 $0.12 $0.12 Condom, standard, male 53 mm 5% For HIV+ clients or clients with HIV+ patrner 1 1 30 30 $0.02 $0.04 TOTAL $4.31 PERSONNEL COSTS PER CASE Type of Staff % treated by this type of staff Note Minutes Times per Day No. of Days/ Visits Total No. of Minutes Cost per Minute Cost per Average Case Auxiliary/Attendant 0% $0.02 $0.00 Nurse/Midwife 100% 4 antenatal v isits @ 20 minutes each + 2 counseling and education sessions on SRH (FP, birth spacing, parenting,HIV, etc.) 20 1 6 120 $0.03 $3.60 General Physician 0% $0.09 $0.00 Obstetrician 50% Women with risk factors, 1 v isit with obstetrician 20 1 1 20 $0.15 $1.50 Paediatrician 0% $0.15 $0.00 Anaesthesist 0% $0.15 $0.00 Lab Technician 100% Lab tests 10 1 1 10 $0.03 $0.30 TOTAL $5.40 Back to Main Menu The “Main Menu” sheet shown below shows the average cost per case for each of the interventions (drug and supply costs and personnel costs are shown separately) and also serves as a switchboard from which to reach the other sheets in the “Part 1 - Cost per Case” model. - 10 - To hide local currency section, press - sign above COUNTRY Uganda Exchange Rate: 1,750 Local currency per 1 US $ Source: http://www.oanda.com/convert/classic UGX Press buttons below to get to intervention sheets Supplies Personnel Total Supplies Personnel Total Family Planning Short-Term Methods 1 Oral Contraceptives (Pill) $5.09 $1.01 $6.10 8,905 1,775 10,680 2 Injectables $3.83 $1.16 $4.99 6,698 2,028 8,726 3 Condom - Male $2.85 $1.01 $3.86 4,985 1,775 6,760 4 Condom - Female $118.10 $1.01 $119.12 206,682 1,775 208,457 Long-Term Methods 5 Intrauterine Device (IUD) $1.20 $1.45 $2.65 2,106 2,536 4,641 6 Implant $25.87 $1.45 $27.32 45,277 2,536 47,812 7 Sterilization - Female $4.19 $4.74 $8.93 7,326 8,295 15,621 8 Sterilization - Male $0.88 $2.09 $2.97 1,549 3,653 5,201 Other Methods 9 Other Method of Contraceptives $0.00 $0.00 $0.00 0 0 0 10 Emergency Contraceptives (EC) $0.29 $0.58 $0.87 503 1,014 1,517 ANC and Delivery Care 11 Antenatal Care (ANC) $4.31 $4.52 $8.83 7,536 7,918 15,454 12 Malaria Prevention within ANC $4.99 $0.29 $5.27 8,724 507 9,231 13 Malaria Treatment within ANC $10.54 $2.34 $12.88 18,445 4,092 22,537 14 Delivery Care $4.81 $6.58 $11.39 8,411 11,518 19,929 15 Postpartum Care $0.20 $0.87 $1.07 353 1,521 1,874 COST PER CASE PER INTERVENTION US $ PRICE LOCAL CURRENCY:LIST of INTERVENTIONS RH Costing Model - Cost per Case Convert to A4 size paper Convert to letter size paper The country should be selected in Part2, the Total Cost per Case model. Selecting a country from the drop-down list there will autom atically update the value in the above cell. To display costs per case in local currency, the user should enter the exchange rate (units of local currency per 1 US$) in cell L4.8 Adapting treatment guidelines and drug prices If the main purpose of the costing exercise is to produce cost estimates that are compatible among countries, it is recommended to use the inputs currently provided by the model (based on WHO standard treatment guidelines and international drug prices). In this case, no changes need to be made to the “Part 1 – Cost per Case” model. If the user wants to adjust the inputs and prices used in the model to more accurately reflect the treatment guidelines and drug prices in a country, the following steps should be followed. An experienced local physician or health expert should assist the user in this exercise. After obtaining copies of national treatment guidelines for all the different interventions covered by the model (standards for antenatal care and delivery care, guidelines for the provision of emergency obstetric care, treatment of STIs, etc.) a printout should be made of all 33 intervention sheets. The drugs and supplies listed for each intervention can then be checked against the drugs and supplies cited in the national treatment guidelines and the sheets can be marked up accordingly. Using the example of antenatal care, a country may not recommend routine syphilis testing or multivitamin supplementation. It may have a different iron supplementation regime (3 instead of 6 months). Or its routine antenatal care programming may include additional tests or drugs. All this should be marked on the printout so the information can later be easily transferred to the model. Making changes to the Cost per Case model 1. To delete a drug or supply from an intervention There are two ways of doing this: a) The user can either set the % in Column B (% of women receiving the intervention receiving this particular drug) to 0% or b) The user can delete the row altogether (highlight the row and select Edit, Delete from the menu). 8 Exchange rates can be found under: http://www.oanda.com/convert/classic. - 11 - It is strongly recommended to use approach A as this will facilitate further discussion of an issue, if necessary and will also save time should the user decide to revert to the original setup. 2. To change a drug Several of the intervention (mainly the STI treatment and the family planning method sheets) contain drop-down boxes from which it is possible to select alternative drugs (see below). Drug prices and dosages are set to adjust automatically. some cases it may be necessary to manually change a drug. order for the model to work, all drugs with their prices need to be included in the “Drug Price this is the case, the user should copy the drug name from the drug price list into the the drug does not exist on the drug price list yet, it needs to be added. To do this, one should . To add a drug to the procedure described under 2. e easiest way to do this is to B) ug already exists on the drug price list, if not, In In List” sheet at the end of the model. If one wants to change a drug, one should therefore first check whether the chosen drug is already included in the drug price list on that sheet. If intervention sheet. If insert a row in the appropriate place and fill in the drug name and price info following the given examples. Only then should the user copy the drug name into the intervention sheet. 3 This works similar A) On the intervention sheet, the user needs to insert a row (th copy a row from above and then change the cells coded in red or blue (drug name, % receiving the drug, description, and dosage). Again, the user needs to check whether the dr add it, and then copy the name into the intervention sheet. - 12 - RH Model Part 2 – Total Cost “Part 2 – Total Cost” contains the demographic and epidemiological data required to estimate the total number of women and newborns who will require the different interventions. As mentioned, the model is already pre-filled with country-specific data from the database (Part 3 of the model). Unlike the data in the Cost per Case model, the user needs to review these data in detail. The model consists of one main input page where all data inputs are specified and a number of results pages which show the results summarized in different ways with the main result page being the “Total Cost Summary” sheet. The following shows a partial view of the “Main Input Data” sheet. The first thing the user should do is to select the desired country from the drop-down list at the top of the sheet. This will lead the model to automatically fill up with data specific to that country. The user should then review the different inputs on this sheet. (All the other sheets contain only calculation, no further input.) Particular attention should be paid to the red numbers. Specifying the Target for Family Planning The FP target can be specified in several different ways: 1. As a specific contraceptive prevalence rate to be achieved, 2. As an unmet need level to be achieved or 3. As a proportion of family planning demand satisfied by the target year 2015. After entering current contraceptive prevalence and unmet need in cells B27 and B28 of the ”Main Input Data” sheet, the user should click the blue button that says, “Click here to select a Family Planning Target.” This will bring up a dialog box from which the user can choose one of the above options. This in turn will bring up another dialog box in which the user should enter the desired target value to be achieved in 2015. Clicking the OK button will lead the model to update the information in the worksheet. Click here to select a Family Planning Target - 13 - Personnel Cost In many cases, personnel cost will be covered in a separate model (for instance, by the UN Millennium Project’s health system model that deals with the physical and human infrastructure required to provide all the different health services and interventions; not just RH, but child health, malaria, TB and HIV/AIDS as well). In this case, it is recommended to set personnel cost in the model to $0. This is done by replacing the default salaries on the ”Main Input Data” sheet of the Total Cost model with zeros. Part 2 – Total Cost Model, Main Input sheet, cells B120- B126 ata Collection Form mes with a data collection form designed to facilitate the review of the HEALTH PERSONNEL AND SALARIES Country Gross Annual Salary Hours worked per week Weeks worked per Year Auxiliary/Attendant $0 30 48 $0 30 48 $0 30 48 $0 30 48 $0 30 48 $0 30 48 $0 30 48 Nurse/Midwife General Physician Obstetrician Paediatrician Anaesthesist Lab Technician Important note: If personnel costs are included elsewhere (e.g., in the health systems model), then the salaries below should be set to $0. D “Part 2 – Total Cost” co current data inputs. The following gives a partial view of what the data collection form looks like. Identically structured to the “Main Input Data” sheet of “Part 2 – Total Cost”, the form provides the necessary space to note down any changes or comments the user would like to make to the data initially provided by the model. Main Input Data Country: Yemen POPULATION 2005 2015 Source/Comments Total Population 20,974,655 28,480,069 UN Population projections, 2004 Revision Women of Reproductive Age (15-49) 4,716,119 6,700,046 UN Population projections, 2004 Revision % Women Married 70% 70% UN Population Division HIV Prevalence 0.1% 0.1% Source: Report on the Global HIV/AIDS Epidemic 2002, pp. 190-201 DATA COLLECTION FORM fter reviewing all the data, the changes noted down in the form should be entered into the “Main reaking the Links to the Database d into the Total Cost model, it will not be necessary to A Input Data” sheet of the “Part 2 – Total Cost” model. The form should be kept for the records. B Once all the information has been fille maintain the link to the “Part 3 – RH database” file. De-linking the file from the database file will not only speed up calculations within the model but will also make it easier to share the model with others as it will not be necessary anymore to also share the database file. - 14 - To de-link, the user only needs to click the button provided on the upper right side of the “Main Input Data” sheet of the “Part 2- RH Total Cost” file. This will automatically run a macro that will eliminate all links to the database file (retaining the data in the model, of course)9. De-link model from database RH Model Part 3 – Database As mentioned, this file contains the large database that feeds into “Part 1 – Cost per Case” and “Part 2 – Total Cost” of the costing tool. There is no need to open or modify this file. Once the user has selected a country and filled the other model parts with the country-specific data, there is no need for the database anymore and it should be de-linked from the other two files. (For instructions, see above.) 9 Should one wish to re-link for whatever reason, a button is provided right next to the De-link button that makes it possible to re-establish the links to the database. . This process might take up to a few minutes. - 15 - IV. ADAPTING THE RH MODEL Dropping Interventions In some cases, countries may wish not to use one or more of the interventions built into the model. For example, in a country obstetric fistula may not really be a problem. There are many ways to reflect this in the model, but the easiest is to simply zero out the cost per case and the coverage rate for that intervention. To set to 0 Go to Cost per Case Part 1 - Cost per Case model ”Main Menu” sheet Coverage Part 2 – Total Cost model, ”Main Data Input” sheet In the example below, malaria prevention and treatment within antenatal care have been eliminated. Part 1 Cost per Case model Supplies Personnel Total ANC and Delivery Care 11 Antenatal Care (ANC) $4.87 $4.05 $8.92 12 Malaria Prevention within ANC $0.00 $0.00 $0.00 13 Malaria Treatment within ANC $0.00 $0.00 $0.00 US $ PRICE LIST of INTERVENTIONS Part 2 – Total Cost Model Incidence/Prevalence of Pregnancy and Delivery Complications 2005 2015 Source/Comments Other Maternal Conditions - % of pregnant women requiring treatment Malaria Prevention 0% 0% Not applicable Malaria Treatment 0% 0% Not applicable Adding interventions It is possible to add interventions to the model. The first step will involve collecting information about the desired new intervention (treatment details such as drug, supply and personnel time requirements per average case) and then adding a new intervention sheet to the Cost per Case model. If drugs will be used that are not yet on the drug list, they need to be added there. The average cost per case estimate then needs to be included on the “Main Menu” sheet. After that it will be necessary to add the intervention also to the “Part 2 – Total Cost” model. Here the user needs to add information about the size of the target population as well as current and target coverage levels. This information then needs to be woven throughout the different sheets, resulting in the end in a total cost estimate for that intervention on the “Total Cost Summary” sheet. As the above process is relatively complex, and requires a very through knowledge of the models and the way the different spreadsheets are linked, it is recommended to request assistance from the Millennium Project when adding interventions. The user should make a copy of one of the intervention sheets in the Cost per Case model, rename it to reflect the new intervention, delete the current inputs and fill in the drugs and supplies required for the desired new intervention. The - 16 - entire model (Part 1 and 2 as filled out so far) should then be sent to Eva Weissman (Weissman@unfpa.org) and Janneke Saltner (Saltner@unfpa.org) with request for assistance. A response should be given within 2 days. V. CHECKING THE RESULTS AND TROUBLESHOOTING Checking the Results Once all the input data has been entered, the user should check the “Total Cost Summary” sheet to make sure there are no error messages. (As this sheet pulls together input data from both Part 1 and 2 of the model, most errors made there are likely to show up on the “Total Cost Summary” sheet. For instruction on how to deal with any error messages, see next section. Once any obvious errors have been fixed on the “Total Cost Summary” sheet, the user should make a printout of all the main pages10. Part 1 – Cost per Case model ¾ “Main Menu” sheet and ¾ All the intervention sheets Part 2 – Total Cost model ¾ “Main Input, ¾ “Total Cases and Cost” and ¾ “Total Cost Summary” sheets Checking the Cost per Case Data As a first step, the user should compare the “Total Cost Summary” sheet in Part 2 and the “Main Menu” in Part 1 of the model. Cost per case estimates in both models should match. Part 1 – Cost per Case Model, Main Menu Sheet Press buttons below to get to intervention sheets Supplies Personnel Total Family Planning Short-Term Methods 1 Oral Contraceptives (Pill) $5.09 $1.11 $6.20 2 Injectables $3.83 $1.27 $5.10 3 Condom - Male $2.85 $1.11 $3.96 4 Condom - Female $118.10 $1.11 $119.22 Long-Term Methods 5 Intrauterine Device (IUD) $1.20 $1.59 $2.79 6 Implant $25.87 $1.59 $27.46 7 Sterilization - Female $4.19 $5.20 $9.39 8 Sterilization - Male $0.88 $2.29 $3.18 Other Methods 9 Other Method of Contraceptives $0.00 $0.00 $0.00 10 Emergency Contraceptives (EC) $0.29 $0.64 $0.92 ANC and Delivery Care 11 Antenatal Care (ANC) $4.31 $4.97 $9.27 12 Malaria Prevention within ANC $4.99 $0.32 $5.30 13 Malaria Treatment within ANC $10.54 $2.57 $13.11 14 Delivery Care $4.81 $7.22 $12.03 15 Postpartum Care $0.20 $0.95 $1.16 US $ PRICE LIST of INTERVENTIONS Part 2 – Total Cost Model, Total Cost Summary Sheet Drugs & Supplies Personnel Cost Family Planning 1 Oral Contraceptives $5.09 $1.11 2 Injectables $3.83 $1.27 3 Condom - Male $2.85 $1.11 4 Condom - Female $118.10 $1.11 5 IUD $1.20 $1.59 6 Implant $25.87 $1.59 7 Sterilization - Female $4.19 $5.20 8 Sterilization - Male $0.88 $2.29 9 Other Method $0.00 $0.00 10 Emergency Contraceptives $0.29 $0.64 TOTAL ANC and Delivery Care 11 Antenatal Care $4.31 $4.97 12 Malaria Prevention within ANC $4.99 $0.32 13 Malaria Treatment within ANC $10.54 $2.57 14 Delivery Care $4.81 $7.22 15 Postpartum Care $0.20 $0.95 TOTAL 10 After checking whether the current setup matches local paper size and adjusting the setup, if necessary. For instructions, see page 9 - 17 - In case the numbers are not the same (this might happen if the user has, for instance, inserted a new intervention in “Part 1 – Cost per Case”), it is necessary to re-link the non-matching cells in “Part 2- RH Total Cost” to the correct cells of the “Part 1 – Cost per Case” file. In a second step, the user should check whether the cost per case estimates seem reasonable and within the expected range. If an estimate seems not right, the user should check whether the cells on the “Main Input” sheet in Part 1 link to the correct cells on the respective intervention sheet (as shown below) Part 1 – Cost per Case Model, Main Menu Sheet Part 1 – ANC Sheet ANTENATAL CARE DRUGS AND SUPPLIES REQUIRED PER CASE Description of treatment line % receiving this treatment Note Number Times per day Days per case/ episode Treatment units per case/ episode Unit cost Cost per Average Case Drugs and Supplements Ferrous Salt + Folic Acid, tablet, 200+0.25 mg (60mg iro 100% 6 months of iron supplementation 1 2 180 360 $0.00 $0.60 Mebendazole, chewable tablet, 100 mg 30% Hookworm treatment 1 2 3 6 $0.01 $0.01 Tetanus toxoid, injection 100% Tetanus vaccination 1 1 2 2 $0.05 $0.10 Syringe, disposable, 2 ml, with needle 100% For tetanus vaccinations 1 1 2 2 $0.03 $0.06 Alcohol swab 100% For tetanus vaccinations 1 1 1 1 $0.02 $0.02 Tests Lancet, blood, disposable 100% For taking blood sample 1 1 1 1 $0.01 $0.01 Alcohol swab 100% For taking blood sample 1 1 1 1 $0.02 $0.02 Blood collection tube 100% For taking blood sample 1 1 1 1 $0.03 $0.03 Test, blood group, anti A + B, 10 ml 100% test for blood group and Rh factor 1 1 1 1 $0.02 $0.02 Test, Blood glucose 100% test for blood sugar 1 1 1 1 $0.24 $0.24 Test, hemoglobin 100% Anaemia screening 1 1 1 1 $0.98 $0.98 Pregnancy test 100% Pregnancy test 1 1 1 1 $0.15 $0.15 Test, Rapid plasma reagin (RPR) 100% Syphillis test 1 1 1 1 $0.08 $0.08 Test HIV 1 + 2, Doublecheck, rapid test 100% HIV test 1 1 1 1 $1.15 $1.15 Test strips, urine (blood/bilirubin/urobilinogen/ketones/ 100% Urine Test 1 1 4 4 $0.11 $0.46 Other Gloves, surgeon’s, latex, size 7-1/2, disposable, sterile, 100% Examination 1 1 1 1 $0.22 $0.22 Antenatal care record 100% ANC record 1 1 1 1 $0.12 $0.12 Condom, standard, male 53 mm 5% For HIV+ clients or clients with HIV+ 1 1 30 30 $0.02 $0.04 TOTAL $4.31 PERSONNEL COSTS PER CASE Type of Staff % treated by this type of staff Note Minutes Times per Day No. of Days/ Visits Total No. of Minutes Cost per Minute Cost per Average Case Auxiliary/Attendant 0% $0.02 $0.00 Nurse/Midwife 100% 4 antenatal v isits @ 20 minutes each + 2 counseling and education sessions on SRH (FP, birth spacing, parenting,HIV, etc.) 20 1 6 120 $0.03 $3.82 General Physician 0% $0.08 $0.00 Obstetrician 50% Women with risk factors, 1 v isit with obstetrician 20 1 1 20 $0.15 $1.47 Paediatrician 0% $0.15 $0.00 Anaesthesist 0% $0.15 $0.00 Lab Technician 100% Lab tests 10 1 1 10 $0.03 $0.32 TOTAL $5.60 Back to Main Menu Press buttons below to get to intervention sheets Supplies Personnel Total Family Planning Short-Term Methods 1 Oral Contraceptives (Pill) $5.09 $1.11 $6.20 2 Injectables $3.83 $1.27 $5.10 3 Condom - Male $2.85 $1.11 $3.96 4 Condom - Female $118.10 $1.11 $119.22 Long-Term Methods 5 Intrauterine Device (IUD) $1.20 $1.59 $2.79 6 Implant $25.87 $1.59 $27.46 7 Sterilization - Female $4.19 $5.20 $9.39 8 Sterilization - Male $0.88 $2.29 $3.18 Other Methods 9 Other Method of Contraceptives $0.00 $0.00 $0.00 10 Emergency Contraceptives (EC) $0.29 $0.64 $0.92 ANC and Delivery Care 11 Antenatal Care (ANC) $4.31 $4.97 $9.27 12 Malaria Prevention within ANC $4.99 $0.32 $5.30 13 Malaria Treatment within ANC $10.54 $2.57 $13.11 14 Delivery Care $4.81 $7.22 $12.03 15 Postpartum Care $0.20 $0.95 $1.16 US $ PRICE LIST of INTERVENTIONS Press buttons below to get to intervention sheets Supplies Personnel Total Family Planning Short-Term Methods 1 Oral Contraceptives (Pill) $5.09 $1.11 $6.20 2 Injectables $3.83 $1.27 $5.10 3 Condom - Male $2.85 $1.11 $3.96 4 Condom - Female $118.10 $1.11 $119.22 Long-Term Methods 5 Intrauterine Device (IUD) $1.20 $1.59 $2.79 6 Implant $25.87 $1.59 $27.46 7 Sterilization - Female $4.19 $5.20 $9.39 8 Sterilization - Male $0.88 $2.29 $3.18 Other Methods 9 Other Method of Contraceptives $0.00 $0.00 $0.00 10 Emergency Contraceptives (EC) $0.29 $0.64 $0.92 ANC and Delivery Care 11 Antenatal Care (ANC) $4.31 $4.97 $9.27 12 Malaria Prevention within ANC $4.99 $0.32 $5.30 13 Malaria Treatment within ANC $10.54 $2.57 $13.11 14 Delivery Care $4.81 $7.22 $12.03 15 Postpartum Care $0.20 $0.95 $1.16 US $ PRICE LIST of INTERVENTIONS Troubleshooting Checking the Links between the Models The Total Cost model is originally linked to two files – the Cost per Case file and the Database file. To check the links, the user should go to the Edit menu, Links. A dialog box will appear that will show which files the Total Cost file is currently linked to. - 18 - Should the model turn out be connected to the wrong file (for instance, a “Part 1 - Cost per Case” file in another folder), then it can be re-linked to the right file by clicking on the wrong file, and clicking on ‘Change Source” and selecting the right file from there. Common Error Messages The following gives a quick overview over the most commonly encountered error messages in Excel as well as instruction on how to fix them. Error Message Problem How to Fix ####### The numeric value entered into a cell is too wide to display within the cell. The column should be resized by dragging the boundary between the column headings to accommodate the cell contents. #DIV/0! The formula is trying to divide by zero. Usually occurs when a cell reference points to an empty cell, which Excel interprets as the value 0. If the formula refers to an empty cell, one needs to check whether that number was accidentally erased. If the number in the referenced cell is actually 0, it is possible to avoid the #DIV/0! problem by using an IF formula like the one below (the formula in cell C5 being B5/A5 with A5 having a value of 0) =IF(A5=0,0,B5/A5))11 #N/A A value a function or formula is referring to is not available. One should check whether the cells the formula is referring to contain valid numbers #NAME? Excel doesn't recognize a name used in a formula One should check whether the name in the formula is defined and/or is linked to the right cell. This can be done by going to Insert, Name, Define and checking the list of names used in the spreadsheet. #REF! The formula refers to an invalid cell (e.g. a cell that has been deleted) The #REF! portion of the formula needs to be replaced with a reference to a valid cell. 11 For more information on the IF function, see the Excel on-line Help system. - 19 - The Auditing Tools Excel provides tools to help troubleshoot error messages or problems in a spreadsheet. To see them, the user should click on Tools, Auditing, Show Auditing Toolbar, which will bring up the Auditing toolbar. Tracing precedents The auditing toolbar makes it possible to select a cell and see which cells contribute to the value of that cell (blue arrows as shown below). Tracing dependents Tracing dependents will show all the cells that depend on the value of the highlighted cell. Tracing errors Clicking on a cell with an error value and the above button will display a red arrow pointing to the cell that causes the error. Blue and red arrows show cells in the same worksheet that provide data to the formula in the selected cell. If the selected cell contains a reference to another worksheet or workbook, a black arrow points from a worksheet icon to the selected cell.12 Error Message on the Total Cost Summary Sheet A shortcoming of Excel’s auditing tools is that it only traces precedents or dependents one step back/forward. As the user inputs in the RH Costing Model in most cases go through several sheets before they end on the main output sheet, the “Total Cost Summary” sheet of the “Part 2 – Total Cost” model, the following provides some guidance on where the user might want to look first when encountering error message on that final sheet. 12 For more information on the auditing tool, the user is referred to Excel’s on-line Help menu. - 20 - Where to look for errors Error Message in: Probable Cause (s) How to Fix 1 Column C (Drug and Supply Cost per Case) Problem with the price of a drug. The drug might not be listed on the drug price list or it might be misspelled. The user needs to go to the respective intervention sheet in Part 1 Cost per Case model. Most likely, there will be an error in the cot per unit column (column H). The user should check whether the associated drug is actually listed on the Drug Price List (sheet “Drug and Supplies”). If yes, it might be that there is a spelling problem. The user should copy the name as it is shown in the drug price list into the intervention sheet. This should fix the problem. If the drug is not listed yet, the user should insert a row in the drug price list and add all the required information (drug name, cost per pack, units per pack), and copy down the formulas required to calculate the cost per unit of the drug. 2 Column D (Personnel Cost per Case): Problem with the salary costs. The user should check the personnel cost section on the “Main Input” sheet (Cells B120:D126). Most likely there is a problem either with one of the salaries or with the working hours). 3 Column E (Total Number of Cases) Problem with either the incidence of a pregnancy or delivery complication or with the coverage rate. The user needs to check a) the incidence/prevalence of the condition (on the “Main Input” sheet) and b) the coverage rate. See page x what interventions fall under which coverage rates. For FP method, one needs to check both CPR and method mix percentages. 4 Column G (Total Drug and Supply Cost) Problem, either with a) cost of a drug b) number of cases (i.e. incidence or coverage rate) The user should check following instructions given above (1 and 3) 5 Column I (Total Drug, Supply and Personnel Cost) Problem, either with a) cost of a drug b) salary costs c) number of cases (i.e. incidence or coverage rate) If the error shows up in Columns E, G and I, it is likely caused by c) If the error shows up in Columns G and I, it is probably caused by either a) or c) If it only shows up in Column I, it is likely caused by b) The user should follow instructions given above (1,2,3) - 21 - I. NEEDS ASSESSMENT OVERVIEW Figure 1: Steps in an MDG Needs Assessment 1 – Develop list of interventions 2 – Specify targets for each set of interventions 3 - Estimate resource needs 4 - Check Results II. Reproductive Health Model Basics Objective Scope Limitations Data Requirements Methodology Components of the Model III. Using the RH Model Installing the Model Getting Started Viewing the spreadsheets Color Coding Getting around Printing the model RH Model Part 1 – Cost per Case RH Model Part 2 – Total Cost RH Model Part 3 – Database IV. Adapting the RH Model Dropping Interventions Adding interventions V. Checking the Results and Troubleshooting Checking the Results Troubleshooting Error Message on the Total Cost Summary Sheet
Looking for other reproductive health publications?
The Supplies Information Database (SID) is an online reference library with more than 2000 records on the status of reproductive health supplies. The library includes studies, assessments and other publications dating back to 1986, many of which are no longer available even in their country of origin. Explore the database here.