Family Planning Forecasting Tool
Publication date: 2007
Family Planning Forecasting Tool User’s Guide September 2007 WORKING DRAFT Family Planning Forecasting Tool User’s Guide September 2007 WORKING DRAFT © 2007 EngenderHealth/The ACQUIRE Project. All rights reserved. The ACQUIRE Project c/o EngenderHealth 440 Ninth Avenue New York, NY 10001 U.S.A. Telephone: 212-561-8000 Fax: 212-561-8067 e-mail: info@acquireproject.org www.acquireproject.org This publication is made possible by the generous support of the American people through the Office of Population and Reproductive Health, U.S. Agency for International Development (USAID), under the terms of cooperative agreement GPO-A-00-03-00006-00. The contents are the responsibility of the ACQUIRE Project and do not necessarily reflect the views of USAID or the United States Government. Printed in the United States of America. Printed on recycled paper. This publication is a draft and is not intended for distribution. Population data used in Reality √ were provided with the permission of the United Nations Population Division. Source: United Nations, Department of Economic and Social Affairs, Population Division. 2007. World Population Prospects: The 2006 Revision, CD-ROM Edition—Comprehensive Dataset in Excel and ASCII formats (United Nations publication, Sales No. E.07.XIII.8). Contents Acronyms and Abbreviations . v Acknowledgments . vii Introduction . 1 I. Overview: What is Reality √ and What Can It Do for You? . 3 Statement of Purpose . 3 Uses of Reality √ . 5 Reality √ : Getting Acquainted with the Tool . 5 1. Continuation of Past Trends Calculation File . 6 2: Future Goals Calculation File . 7 II. Getting Down to Business: Using Reality √ to Generate Projections . 9 What Do I Need to Begin? . 9 Running the Projections . 9 1. Calculating Past Trends . 9 Detailing Source Data and Assumptions . 10 Source Data and Assumptions You Have to Input . 10 CPR Survey Data . 11 CPR Data That You Have to Input . 12 CPR Summary Information . 13 Calculating the Past CPR Trend . 14 Interpreting the Data: CPR Trends . 15 Continuation of the Past Trend: Projecting into the Future . 16 Interpreting the Data: Projected Past Trend Continuation . 18 Population Data . 19 Population Data That You Have to Input . 20 Number of Users . 21 Interpreting the Data: Number of Users . 22 Number of Adopters (New Users) . 23 Discontinuation . 24 Discontinuation Data That You Have to Input . 25 Interpreting the Data: Number of Adopters . 27 Commodities . 28 Commodity Costs . 30 Commodity Cost Data That You Have to Input . 30 Interpreting the Data: Commodities and Commodity Costs . 31 The ACQUIRE Project Reality √ User’s Guide iii Service Provision: Estimated Number of Clients Per Month . 32 Service Provision Data That You Have to Input . 32 Interpreting the Data: Service-Delivery Capacity . 34 Couple-Years of Protection . 35 2. Future Goals: Projecting by Testing Scenarios . 37 Overview . 37 Source Data and Assumptions Data That You Have to Input . 37 Future Goal CPR Data That You Have to Input . 38 Additional Data That You Have to Input in the Projected Scenario Worksheet . 40 Interpreting Your Data . 41 Conclusion and Additional Resources . 47 Appendix A: Population Data . 49 Appendix B: Commodity Costs . 55 Appendix C: Excel Functions: Tips on Using Excel . 57 Appendix D: CYP Factors . 73 Appendix E: Contraceptive Discontinuation Rates . 75 iv Reality √ User’s Guide The ACQUIRE Project Acronyms and Abbreviations ACQUIRE Access, Quality, and Use in Reproductive Health CPR contraceptive prevalence rate DHS Demographic Health Survey ICPD International Conference on Population and Development IUD Intrauterine device JSI John Snow International LAPM long-acting and permanent method MWRA married women of reproductive age NGO nongovernmental organization PATH Program for Appropriate Technology in Health PIO public international organization RHS Reproductive Health Surveys SDM Standard Days Method UN United Nations UNFPA United Nations Population Fund USAID U.S. Agency for International Development WRA women of reproductive age The ACQUIRE Project Reality √ User’s Guide v Acknowledgments The project management team consisted of the following individuals: John M. Pile, Senior Technical Advisor, Family Planning; Alyson Smith, Program Associate, Family Planning; and Jan Kumar, Senior Program Manager; all with the ACQUIRE Project, New York. The project management team wishes to acknowledge, with gratitude, the assistance and contribution of several individuals. We are grateful to the U.S. Agency for International Development (USAID) for the opportunity to develop this tool and for their support throughout. We also thank Patricia MacDonald and Carolyn Curtis of USAID for their input and review of the tool and their dedication to putting data into the hands of decision makers. A number of individuals played a critical role in creating a user-friendly Reality √ family planning projection tool. We would like to thank the ACQUIRE Tanzania Country Office for serving as an early pilot test for Reality √ projections and for demonstrating the need for a tool that could be used in limited-resource settings. We also thank John Ross for his invaluable guidance and contributions to the development of this tool; John’s insights on design and methodology provided a strong foundation for the final tool. And we thank Robert Patton for providing instrumental technical input on the Excel formulas used to make Reality √ a user-friendly product. For their role in pilot-testing use of the tool at a micro level, we thank Julie Wiltshire and Henry Kakande of the ACQUIRE Uganda Country Office, along with the national-, provincial-, and district- level officials of the Ugandan Ministry of Health who participated in the workshop; they provided invaluable insight into the actual use of the tool in limited-resource settings. Sareen Thaddeous, from USAID Uganda, served as a champion for the Uganda pilot test, and we thank her for this opportunity. The work done on Reality √ projections in Ethiopia, Nepal, and Bangladesh also contributed to the design of this product. We would also like to thank Nalin Johri for monitoring and evaluation guidance and Marie Rose Charles for providing critical administrative support for the rollout of this product. The authors also gratefully acknowledge the United Nations Population Fund for granting us permission to utilize key Excel population files from their publication World Population Prospects: The 2006 Revision and include these on the CD-ROM accompanying this volume. Several individuals were instrumental in reviewing the tool, including EngenderHealth and ACQUIRE Project staff Lissette Bernal-Cruz, Maureen Clyde, Kamlesh Giri, Nalin Jhori, Anna Kaniauskene, Hannah Searing, and Jyoti Vajpayee. Michael Klitsch and Elkin Konuk provided editorial and design oversight on this guide. Finally, the management team extends its heartfelt thanks and appreciation to all of the planners and administrators who have expressed a need for local, specific data. This tool was created because of those voices; we thank those individuals who work every day to improve their family planning programs, and we hope that this tool provides valuable projections for future decision making. The ACQUIRE Project Reality √ User’s Guide vii Introduction Reality √ is a family planning projection tool consisting of a straightforward Excel Workbook that allows one to assess past trends in the contraceptive prevalence rate (CPR) and test future scenarios for the geographic area in which one’s program is operating. The tool also allows users to test and assess whether established goals are reasonable based on the local, specific context. Reality √ was designed for multiple audiences to run projections based on the geographic focus of their choosing. Beneficiaries of the tool would include Ministry of Health planners and administrators at the national, provincial, or district levels, as well as family planning programmers at donor agencies or cooperating agencies. Anyone with basic Excel skills will be able to make their way through using the tool. The tool was also designed to be a stand-alone product that could be used in low-resource settings, where high- capacity Internet connections or high-level programming skills may not be available. The purpose of this User’s Guide is to instruct a user on how to apply the tool; the guide sets forth instructions and methodology for projecting family planning trends. It is written for self-teaching and can also be used as a tool for group trainings. The guide presents an overview of the tool and instructions on how to examine past trends and project them into the future. The complete Reality √ package consists of the guide and an accompanying CD-ROM. The CD-ROM contains an electronic version of the guide, the Reality √ Excel example used in this guide, and a blank Reality √ template that you can use for your own projections. You can copy the blank Reality √ template as often as needed; just be certain you keep the original blank template for reference. The CD-ROM also contains additional resources, primarily electronic copies of the resources used for this guide, and United Nations population files that can help aid in projecting future populations. (Projected population figures are needed in Reality √.) In reading the guide, it is important to also have the Excel example on one’s computer screen; this will help the reader comprehend the instructions in the guide and allow him or her an opportunity to see the file in its entirety. The ACQUIRE Project Reality √ User’s Guide 1 I. Overview: What Is Reality √ and What Can It Do for You? Statement of Purpose Reality √ is a family planning forecasting tool1 that can be utilized to generate data for evidence- based advocacy and planning. Reality √ consists of an Excel Workbook that allows you to assess past CPR trends and test future scenarios for the geographic area in which your program is operating, and to test whether established goals are reasonable for the local context. Projecting Family Planning CPR Goals and Realistic Planning Projecting overall CPR and method-specific rates is essential to evaluating current family planning program efforts, setting goals for the future, and planning realistically for the resources the program will require. But program managers often are not equipped with the figures or tools needed to make realistic programming decisions. Sound programming requires data, so that the goals and activities selected are appropriate and evidence-based. Projecting CPR is an art, not a science, but utilizing projections as part of the decision-making process helps to establish realistic programming goals and contributes to the planning process for how to meet these goals. Need for Local, Context-Specific Projections Projection software is sometimes used to project CPR at the national level, but the same type of projections cannot necessarily be made at the regional or district level. There is a real need for projecting family planning goals and needs at lower levels to improve programming processes and family planning program results. Often, significant regional differences in family planning usage exist within a given country and should be reflected in both program design and resource allocation. Reality √ builds on existing national-level family planning projection tools such as Target-Cost and Spectrum/FamPlan to project CPR and user, commodity, and service needs at the regional, district, and site levels. What Is Reality √ and What Can It Do for You? Reality √ is a tool consisting of a simple Excel Workbook that provides data for decision making; it allows one to assess past CPR trends and test future scenarios for the geographic area in which a program is operating. A user can also test and assess whether established goals are reasonable for a local context, given the human or financial resources available. The tool was designed with the end user in mind, making it easy to generate data for decision making. Reality √ allows a user to test multiple CPR goals and to provide data needed for planning the service- delivery expansion that may be required to meet these goals. The figures also provide evidence to use for advocacy purposes: One can use the projections as evidence for advocating for an increase in family planning resources, or a user can provide multiple scenarios to policy makers or donors to 1 The Reality √ tool draws from methodology used in FamPlan, a national-level family planning projection tool created by The Futures Group (Stover et al., 2005). FamPlan is currently a component of a planning tool called Spectrum. The ACQUIRE Project Reality √ User’s Guide 3 Reality √: It’s Not About Targets, It’s About Planning Informed and voluntary decision making is a pillar of quality family planning services and a basic tenet of the fundamentals of care. Clients’ right to freely choose the family planning method they want to use to achieve the desired number and spacing of their children is widely recognized. The right to choose implies that the client is not being pressured or coerced to accept family planning or to use a particular method. It also implies that a range of methods is available from which the client can choose without constraint. For clients to exercise this right, they need accurate information that they can understand, access to trained health care professionals with good communication and counseling skills, and a social context that supports them in making decisions about their fertility and reproductive health that meet their needs. In developing goals or targets for contraceptive prevalence in a population, one must be very careful not to compromise individual clients’ rights to voluntary and informed choice. Though numerical goals are important for realistic planning and budgeting, they should not be passed down to individual health care workers as targets or quotas that they are required to meet as part of their performance expectations. Doing so compromises the quality of services and jeopardizes the client’s voluntary choice by creating a provider bias for or against specific methods. Reality √ is a planning tool to help policy makers and managers understand what is realistic within the limits of existing resources and to project what additional resources will be needed to serve the estimated number of family planning clients at future points, based on what is known about the population and the unmet need for services. The results should be used to project resource requirements and to advocate for and deploy them, but not to set individual targets for family planning workers. Not only would this compromise the quality of services, it would also violate the terms and conditions of USAID funding, as stipulated in the Tiahrt Amendment. This U.S. law prohibits the use of any numerical targets or quotas of total numbers of births, of family planning acceptors, or of acceptors of a particular family planning method (though it permits quantitative estimates or indicators used for budgeting or planning purposes). The Tiahrt Amendment applies to any U.S. nongovernmental organization (NGO), foreign NGO, public international organization (PIO), or foreign government receiving funds, technical assistance, commodities, or training from any U.S. government account for family planning activities. demonstrate the need for more family planning resources. The tool can also be used to generate projections to assess how unmet need can be addressed into the future. Sample Questions That Reality √ Can Help Answer: If past contraceptive prevalence trends continue, where will we be in 2015? The Ministry of Health has set a goal of 30% modern method prevalence by 2010. Is this achievable? What human and material resources will be required to achieve this goal? How many IUDs will have to be inserted at a site to obtain a 1% IUD prevalence? How many vasectomies will have to be done at a site to obtain 0.5% vasectomy prevalence? What would be the impact of reducing discontinuation on the numbers of users and commodities needed? If you have a high unmet need for family planning, what is needed to meet 50% of that unmet need? 4 Reality √ User’s Guide The ACQUIRE Project Uses of Reality √ The Reality √ tool helps you plan based on informed estimates of need, by examining the relationship of contraceptive prevalence to contraceptive users, adopters, commodities, commodity costs, and service-delivery capacity based on the number of women of reproductive age (WRA) or married women of reproductive age (MWRA) in a given geographic area. This type of local, specific information is crucial in evaluating whether CPR goals are achievable and in advocating for the resources needed to actually achieve these goals. The tool is set to run projections based on simple assumptions for estimating family planning usage and needs. It is important to note that these data are intended to illustrate trends for future family planning programs; they are only intended to be used to set goals for the purposes of planning, not to set performance targets. The projections are only estimates based on an established methodology and can provided a basis for predicting future family planning needs. Reality √: Getting Acquainted with the Tool The Reality √ consists of an Excel Workbook containing two basic worksheets 1. Past trend continuation 2. Future goals Each worksheet contains set formulas to minimize the amount of data you will have to input to run either a past trend or a projected scenario. The only software you will need is Microsoft Office, specifically Microsoft Excel. While this guide will help you to navigate the process of inputting data and analyzing the projections, basic Excel skills are needed. Skills such as knowing how to move through a worksheet, how to save files, how to copy a workbook, how to print, how to hide columns and rows, or how to copy data to a Microsoft Word file will come in handy when using the Reality √ Tool. Appendix C: Excel Functions: Tips on Using Excel provides instructions on the multiple Excel functions used in the Reality √ Tool. The worksheets have been color coded to help guide you through the process. The only cells in which you need to input data are the green cells. The rest of this guide will help you to use the actual Excel file. The visual illustrations contained in the guide have been enlarged or reduced for this guide; you will have complete control over how you view the Reality √ Excel file when you are using it on your computer. In some images, you will see a blue line denoting cells linked to a formula; this helps direct your attention to the appropriate cells in the visual, but these lines will not appear on your screen. While this guide will direct you on how to use the tool to input data and generate projections, each section also contains additional information and illustrative questions that can help you interpret the projections and think about them in a larger programmatic context. The ACQUIRE Project Reality √ User’s Guide 5 To follow along with the examples in the guide, you need to look at the Excel file on your computer as you read. Looking at the Excel worksheets in their entirety also helps provide context on the data that are captured within the worksheet. The Reality √ Excel file contains two different Excel worksheets; the first calculates your past trend and projects that trend into the future, while the second allows you to test future family planning scenarios. While you do not necessarily have to run your past trend calculations first, this will help provide insight into how your program has been doing and will also illustrate the effects of a simpler extrapolation into future years. After that, you will be able to test future scenarios. 1. Continuation of Past Trends Calculation File To project contraceptive prevalence into the future based on your past trend or to know whether future CPR goals are realistic, it helps to first assess where you have been: How has prevalence by method changed over time? What will the future total prevalence be if the trend continues? The above questions can be answered by calculating your past trend; this is done in Worksheet No. 1 Past Trend Continuation. This worksheet will calculate past CPR trends based on two data points and project those trends up to 25 years into the future. By inputting two data points, you can use the worksheet to capture a basic trend during a specific time period. Due to the fact that some goal-setting exercises focus on long-term goals, Reality √ was designed to generate projections for up to 25 years. Please keep in mind, however, that while the tool is capable of projecting up to 25 years, the further out you project, the less reliable the numbers become; uncertainty in the projections increases as the period of the projections increases. The image on page 7 is an extremely reduced image of the Past Trend Continuation worksheet and is included here to provide a quick, big-picture visual to help explain the file’s overall set up. This does not represent the size of the fields you will see in Excel. This image is here to simply give an idea of how the worksheet is generally organized. Notice that while this file may at first seem complex, all of the cells are linked and contain formulas that automatically update once you input your data into the green cells. The green cells contain figures such as CPR, population figures, and commodity costs. The color coding can help make sense of what you are seeing. As noted, the green cells are the only cells in which you have to input data. The area of the spreadsheet demarcated by the grey border to the left contains the main information used for calculating the projections. The purple area demarcates the area where future projections are calculated. The spreadsheet is formulated to instantaneously run calculations and projections as you input the necessary data. All cells, with the exception of the green ones, contain intricate formulas used for the projections. The formulas in the worksheet are locked to minimize mistakes. The graphs that you see to the right of the image show the projections as changes are generated within the spreadsheet; they show the trends and help expose data input or assumptions that are incorrect. For example, if you see sharp increases or decreases in the lines (versus a steady trend), 6 Reality √ User’s Guide The ACQUIRE Project you may need to question how realistic or achievable those increases or decreases will be in the future. The indicators that Reality √ can project are: CPR Population data for number of women or number of married women Number of users Number of adopters Commodities Commodity costs Service provision (estimated number of clients per month) Couple-years of protection (CYP) Before proceeding, navigate the Past Trend Continuation Excel worksheet to get a better sense of how it is set up. 2. Future Goals Calculation File You may want to test family planning projection scenarios for a multitude of purposes, such as: Planning for future needs to determine what CPR goals really mean in terms of numbers of users, of commodities, or of sites that will be required to serve clients Assessing whether previously established goals are realistic or what kind of resources it will take to meet these goals Advocating for more resources at the national or donor level The ACQUIRE Project Reality √ User’s Guide 7 Analyzing how to optimize your family planning resources for maximum impact and how to obtain estimates for future resource allocation. For purposes such as this, you can use the Future Goals worksheet. Below is a reduced image of the Projected Scenario worksheet in its entirety; again, this is included just to provide an overview of how that worksheet is set up. You can see that it is very similar to the Past Trend worksheet. As in that worksheet, the only cells in which data have to be input are the green cells. This worksheet contains the same level of analysis as your Past Trend Excel worksheet. Be sure to take an opportunity to examine this file to get a better sense of how it is set up. Now that you have an overview of the tools, you can start to run projections. 8 Reality √ User’s Guide The ACQUIRE Project II. Getting Down to Business: Using Reality √ to Generate Projections What Do I Need to Begin? For any level of projection that you would like to run (national, regional, district, site), you will need: Two years of CPR data points (with data segregated by method) Population data CPR Data CPR data from sources such as the Demographic Health Survey (DHS) or Reproductive Health Survey (RHS) can be utilized. You can run your projections on either women of reproductive age (WRA) or married women of reproductive age (MWRA); therefore you will need to ensure that you use CPR data corresponding to the level of projection you would like to run (i.e., CPR figures for WRA or CPR figures for MWRA). Estimated data can also be used: If you have one data point for the past and you have a reasonable estimate for the current CPR, you can utilize estimated data for the second data point. You can also utilize data from another nation or region that has a similar profile to the geographic area on which you would like to focus. Please keep in mind that regional estimates or some other data can be used as your starting point, and that you can adjust the CPRs you input based upon how you think your program has done. The tool was designed to give you, the user, as much flexibility as possible, based on the data or estimates that are available. All CPR figures in the spreadsheet have been rounded to the second decimal place. This allows for easy display and computation and keeps enough precision for the projections. Population Data Depending on the level of analysis you choose, population data on numbers of WRA or married women of MWRA are needed for Reality √ projections. If you have them, local-level population projections (such as projected census data) can be used here. If not, a commonly used source of population projections is the United Nations World Population Prospects Database. For specific information on how to obtain or calculate population data estimates needed for Reality √, see Appendix A: Population Data. You can also use information from the U.S. Bureau of the Census for population projection estimates. Running the Projections 1. Calculating Past Trends Calculating past trends before jumping into testing future scenarios will help give you a sense of how the family planning program has been doing. While you can certainly test future scenarios (i.e., run projections on the Future Goals worksheet) without calculating past trends, the past trend figures will help you assess what may be realistic for the future of your program, based on the historical trends; it will also help provide historical context before you begin to run future scenarios. The ACQUIRE Project Reality √ User’s Guide 9 DETAILING SOURCE DATA AND ASSUMPTIONS On your computer, open Worksheet No. 1, Past Trend Continuation, and in the left-hand corner you will see seven fields of information that will be critical to input to ensure that you are able to remember all of your source data and assumptions. This will help you follow the work you are doing in the spreadsheet and will also help others understand exactly what the projection is based upon. Source Data and Assumptions You Have to Input 1. Date In this field, you should note the date on which you are working on the file. 2. Title of Scenario Label and name the scenario to make it easy to refer back to it. The title may contain information such as the country or district name and the nature of the projection (i.e., Region X in Country X: Projected Past Trend Continuation). 3. Geographic Level/Name of Analysis (i.e. national, region, district) This field will allow you to specify the geographic area of your projections. For example, if you are running national-level trends, you can input National; however, if you are running the data on a specific region, district, or site, it is important to note the proper name within this field. 4. Assumptions Detailing the assumptions is crucial to knowing what the data are based upon. The example notes that the 1998–2003 trends are being calculated and are a basis for projecting into the future. It is very important to note that year-to-year increases can be projected in various ways; this tool projects based upon an average annual increase. 5. Based upon WRA or MWRA In this field, it is important to note whether you are running your projections on populations of WRA or MWRA. This will ensure that anyone looking at the file understands which CPR trends are being analyzed and also which population numbers are being utilized for the projections. 10 Reality √ User’s Guide The ACQUIRE Project 6. Population Data Source This field will capture information on the source of the WRA or MWRA data used. The example shows that the numbers utilized in this file came from the United Nations Population Division World Population Prospects. If you obtain your data from an Internet page , you should reference the web site and the date on which you accessed the information. 7. CPR Data Source Here, you will want to note the source of the CPR data. In this example, we can see that 1998 and 2003 DHS data are being used. Be as specific as possible regarding your sources. If, for instance, you use service statistics for a specific district to estimate your CPR, be sure to note the time period for which the service statistics apply. If you are using some other source, such as the RHS, be sure to note that information in this field. Entering this information in every worksheet you use will help you and others refer back to the original sources. This information is also critical in explaining what the projections illustrate. CPR SURVEY DATA Now that you have completely filled in your background data and assumption fields, it is time to begin to input the numbers. This section will guide you as to where to input data and will illustrate the formulas and projections that result from your data inputs. Again, for clarity, be sure to continuously look at the Excel file on your computer screen to get a better sense of the worksheet as you read this guide. The figure below shows cells that require your data input for calculating the past trend. As previously noted, the green cells are the only cells throughout the Reality √ tool into which you will input data. It is important to keep this in mind, as most of the other cells within the worksheets contain formulas that link back to the green cells. We can see that this section of the worksheet contains information on Survey data (or estimated data) for Calculating CPR Past Trend. Column A contains the labels for individual methods. Columns B and C are the sections of the worksheet where the two CPR data points for each method will be entered. We know from the background data previously detailed that the trend being calculated is based upon MWRA and that the data came from the DHS; therefore, the numbers in column B are based upon the CPR for MWRA from the 1998 DHS and the numbers in column C are based upon CPR for MWRA from the 2003 DHS data. (Note that if you chose to base the projection upon WRA, you must be sure to enter the CPR figure for all women, not just for married women.) The ACQUIRE Project Reality √ User’s Guide 11 Reality √ has the capacity to project figures for a number of methods: The pill The two-month injectable The three-month injectable Condoms The Standard Days Method (also known as SDM or CycleBeads) Any traditional or folk methods Hormonal implants (Norplant®, Jadelle, or Implanon) The intrauterine device (IUD) Female sterilization Male sterilization It is possible that you will not have data for every method listed; this could be due to the fact that the method is not available in your region or that the method is too new for data on that method to be available. It is important to keep in mind that while these options are available in Reality √, you only have to input figures for the methods that are applicable to your program. The tool has been designed to allow for this type of flexibility, so it can meet the needs of multiple users. CPR Data That You Have to Input 1. Column B contains the First Data Point. Input the year of your first data point (Cell B13). In this example, 1998 was chosen for the first year. 2. Using the CPR data from your source, input method-specific CPRs for the first year (Cells B14- B24). 3. Input the year of your last data point (Cell C13). Column C contains the Last Data Point. This should be for the most recent data you have available. Here, 2003 appears to be the latest year in which CPR data was accessible. 4. Using the CPR data from your source, input method specific CPRs for your last year (Cells C14– C24). Each figure shows the percentage of MWRA using that method. As mentioned earlier, you may not have data for every method listed in Reality √; if this is the case, you can input the number 0 into the appropriate cells. You will also notice that there are multiple options for the injectable (Row 15 is the two-month injectable, and Row 16 is the three-month injectable); given that many programs have both options available, the tool allows you to project based on both types of injectables. The same is true for implants: Row 20 is for Norplant or Jadelle (given that Norplant will not be on the market in the future), while Row 21 is for Implanon. Again, providing multiple options for both the injectable and implants offers flexibility for the individual user. Given that there are two options in Reality √ for the injectable and implant, you can choose which fields you want to use and how to input those data. For example, if you are using a DHS survey that gives you one CPR figure for injectables (e.g., 10%) and you know that about half of that use is due to the two-month injectable and about half is due to the three-month injectable, then you can input 5 into cells B15 and B16. Alternatively, if the injectable CPR is 10% and you know that this is due solely to the three-month injectable, you would then input 10 into Cell B16. Please keep in mind that it is really up to the individual user to decide which fields to use. 12 Reality √ User’s Guide The ACQUIRE Project For the purposes of this guide, we will use the three-month injectable as our injectable example, and we will use the Norplant/Jadelle option for implants, to keep the examples as straightforward as possible. In using survey data (such as the DHS or RHS) always check to ensure that you are using the correct survey numbers. As indicated above, if your projection is to be based on MWRA, ensure that you are inputting the CPR for MWRA and not for WRA. It is also critical to always double-check all of your numbers for accuracy. Given that the spreadsheet formulas are linked to the figures you input and will automatically calculate projections, inaccuracies will result in faulty forecasts. CPR Summary Information Under the list of methods, there are three summary categories; based on the data you previously entered (cells B14-21 and C14-21), this information will be automatically calculated: Any method: sum of all the methods listed above Formula =SUM(B14:B24) Any modern method: sum of pill, injectable, condom, standard days method, implant, IUD, female sterilization, and male sterilization Formula =SUM(B14:B18,B20:B24) All long-acting and permanent methods: sum of implant, IUD, female sterilization, and male sterilization Formula =SUM(B20:B24) This level of analysis provides a useful picture of the overall CPR status and provides a comparison among categories of methods. For example, these projections can help you decide if you want to put more effort into advocating for more resources. If long-acting and permanent methods (LAPMs) are particularly underutilized, you may want to think about strengthening LAPM services in your program. LAPMs are often underutilized, for a number of reasons, including: myths and misconceptions about the methods; a lack of supplies; or a lack of skilled providers who can offer them. LAPMs also require a larger up-front investment than do resupply methods such as the pill or the injectable, yet in the longer term, LAPMs are among the most cost-effective methods and are appropriate for both those who want to space births and those who want to limit future pregnancies. We can see from the image to the right that Cell C28 (All LAPM Methods in the year 2003) is calculated by adding implant, IUD, female sterilization, and male sterilization in the year 2003 (cells C20 through C24). This is an example of the formulas contained throughout the worksheet. The ACQUIRE Project Reality √ User’s Guide 13 Calculating the Past CPR Trend To project based on the past trend, the average annual increase must be calculated. This is simply the difference between the first CPR and the second CPR divided by the number of years between them. This approach is more conservative than using a constant percentage increase from year to year. The formulas in Column D will automatically calculate the average annual increase during the past period by subtracting the first data point from the last one and dividing it by the number of years between surveys. This information is very important for assessing the progress of your family planning program against strong family planning programs. Strong family planning programs have typically been able to increase CPR by approximately 1.5 percentage points annually (see the table on page 15 for more information on family planning trends). The averages in Column D (specifically, cells D26 and D27) can help you think about how your program compares against a strong program achieving annual increases of approximately 1.5 percentage points. When testing future goal scenarios (as will be discussed in detail later in the guide), the average annual increase or decrease can also help you think about whether your future program goals are realistic. Since an annual increase of 1.5 indicates a strong program, an increase projected to go much higher than 1.5 points annually may not be reasonable to expect. Goals may turn out not to be achievable if they are initially set too high. Assessing what an achievable annual gain for your context may be requires thinking about factors such as past performance, family planning resource allocation, and trends of family planning programs in settings similar to yours. The image below shows Column D: Average Annual Increase/Decrease for Past Trend The formula for this calculation is: (Last Data Point–First Data Point) Divided by Number of Years between Surveys Cell D13 calculates the number of years between data points by subtracting the last data point from the first (C13–B13). 14 Reality √ User’s Guide The ACQUIRE Project Interpreting the Data: CPR Trends We can see from the example that over the five-year period between data points, the use of some methods has grown, while the use of others has declined. It is critical at every step in this process to think about what the data are illustrating. When examining the data, it is necessary to drill down into the various root causes that may have influenced CPR and family planning uptake in the years between surveys. Issues to Consider When examining the past trend’s annual increase or decrease, you may want to think about issues such as: Is the annual increase or decrease equal to, more than, or less than you may have thought? In terms of international experience, relatively strong family planning programs can experience a 1.5% annual increase in CPR; how does your program compare to this number? (See the table below for international estimates on annual increases/decreases of family planning programs.) What factors contributed to these changes within the health care system and in an external environment? Have there been shifts in commitment at various policy levels either toward or away from family planning in general or for specific methods? Where have resources been allocated? Have they been allocated to public health? Are they being allocated to other health needs? What were the family planning priorities during the years between surveys? Have champion policy makers, providers, community leaders, or other family planning advocates been lost to other professional positions or geographic areas? Are myths and rumors about a particular method contributing to possible decreases in utilization? Were there any communications campaigns or community activities that may have contributed to an increase in method utilization? Have there been contraceptive stock-outs that reduced usage? Have new family planning methods been introduced in your program? Have there been any changes in the policies that govern the level of cadre allowed to provide a specific method during the years between surveys? Are there any other factors that may have played a role? Table: Average annual gain in modern method use among all women, 1990s–2000s, selected countries Annual gain, any modern method use Region/country 1990s 2000s Africa East/Southern Africa Kenya 1.0 -0.2 Malawi 1.9 0.2 Tanzania 1.4 0.4 Uganda 1.1 -0.2 West Africa Burkina Fasso 0.3 1.0 Cameroon 0.5 0.9 Ghana 0.7 0.9 Nigeria 0.6 0.0 Senegal 0.6 0.1 Asia South Asia Bangladesh 1.2 0.9 Nepal 1.2 -0.3 Southeast Asia Indonesia 1.3 0.3 Philippines 0.4 0.9 Latin America/Caribbean Bolivia 0.9 1.4 Colombia 1.1 1.1 Dominican Republic 1.5 0.9 Source: STATCompiler and select DHS surveys The ACQUIRE Project Reality √ User’s Guide 15 It is important to ask these types of questions to make sense of the projections. In the example provided, one may analyze the data based on factors such as: The three-month injectable has experienced the highest growth rate, at 0.78 percentage points annually. What might the reasons be for this increase? Could it be due to word-of-mouth report (i.e., women hearing about it from their friends)? Could it be because women feel they can get the method without their partner’s knowledge? Could it be due to provider bias, because the method can be given relatively quickly? Could it be due to an increase in the number of providers trained to offer the service? Was there a policy change that allowed a lower level of cadre to offer injectables? Did the method satisfy an unmet need in the community? IUD use declined by 0.04 percentage points per year. What might the reasons be for this decrease? What level of provider can offer IUD services? Was there a decrease in the number of providers who could offer it? Could the decline be due to myths or rumors about the IUD? Did communications campaigns about the method previously exist and then stop at some point between the survey years? Were there adequate commodities to meet demand? What impact did donor support or termination of support have on IUD use? Implant prevalence (for this example, we are using Norplant/Jadelle) rose from 0.0 to 0.30 between survey years. What might account for this growth? Were implants introduced in the area for the first time? Did satisfied users tell their friends about the method? Was there a champion in the district who advocated for implants? What level of cadre could provide the service? Were there community outreach activities in place to inform community members about the method? This level of analysis is important in thinking about how best to plan for future family planning programs and to advocate for the types of interventions that are needed to strengthen and sustain programs. Continuation of the Past Trend: Projecting into the Future The spreadsheet will automatically calculate (on an annual basis) the future trend 25 years after your last data point, based on the assumption that the past trend will continue. It is important to keep in mind, however, that the further out you project, the less reliable your numbers become. Projecting out 25 years provides simply some indication as to where your program could possibly be years from now if a basic past trend continues. Note: If you wanted to examine a past trend up to 2015, for example, you can look at the column containing those figures; assessing figures for up to 25 years is not required for use of the tool. We can see how the formulas are constructed in order to project CPR into the future. For example, female sterilization in the year 2004 is calculated by using the 2003 data point in Column C and adding the average annual increase. This calculation is performed for all of the methods for the subsequent 25 years. One caution is that if the 2003 CPR for a method is already high, for example, and the annual increase was also high, a 25-year projection can produce an impossibly high level of use (even to more than 100% of women). This will also raise the projection for total CPR too high, and it may do so for the subtotals for modern methods and for LAPMs. Therefore, you must inspect each method’s projection to decide how likely it is and analyze accordingly. 16 Reality √ User’s Guide The ACQUIRE Project It is important to note that the formulas have been constructed to ensure that the CPR of any given method will not go below zero, even if a negative trend is occurring. Also, as noted above, no maximum levels are set within the cells, so particularly high annual average increases could lead to a projected CPR exceeding 100%. Clearly, that would indicate that early trends cannot continue at the same annual rate. An important caution is to examine the trend for the total CPR, since the individual method assumptions may drive it too high, way above the average level for strong family planning programs (1.5% per year). We can see in the figures below how, based on our defined assumptions, the past CPR trend behaves for every method, based on the average annual increase previously calculated. The ACQUIRE Project Reality √ User’s Guide 17 Here we see the projections through 2029, which is the 25-year projection in this example. Interpreting the Data: Projected Past Trend Continuation It is critical to assess projections and ask yourself questions about what they suggest. One may look at the results and see, for example, that due to the annual decrease in prevalence of any traditional method, use is projected to be zero in 2013. It is important to think about how likely this is, or whether there may in fact still be some traditional method use by then. IUD use is also projected to be zero in 2008; again, it is important to think about how reasonable these numbers are and what they mean. If you are only concerned with knowing the projected modern method CPR in 2015, all that you need to do is find the column for that year and analyze the results accordingly. Though the file is set to project to 25 years, it is not necessary to use it beyond the final year of interest. The image on page 19, for example, shows prevalence up to the year 2015. We can see that the prevalence of any method is 29.40%; modern method prevalence is also 29.40%, due to the annual decrease in traditional methods. Again, it is important to analyze the data and think about what the numbers suggest. Assessing the past trend will help you think about the types of scenarios you would like to test when projecting and will help put into perspective the goals that have been set. 18 Reality √ User’s Guide The ACQUIRE Project Issues to Consider As you move forward, be sure to keep in mind your current family planning program context and capacity. Running projection models can help illustrate future needs, but it is up to the programmer to think about how the results relate to the current local context in terms of issues such as: Current resource allocation: Will current resources keep up with projected increases in services? Stock-outs: Are stock-outs or lack of supplies a barrier to service provision? Provider capacity: What level of cadre is able to provide services? Community awareness of methods: Do communities know the benefits of all methods? Client access to methods: Can clients obtain a variety of methods without having to travel long distances? Other issues POPULATION DATA Now that you have projected CPR based on your past trend, it is time to explore the relationship between population data and CPR. Thinking about programmatic elements such as the ones previously noted will help you to put the data into a larger context and help you think about how and where you may need to advocate for more funding and human resources, or where you may need to target interventions to strengthen the family planning program. The projected population of WRA or MWRA is a critical component in calculating numbers of users, adopters, commodities, and service-delivery capacity. Most countries are experiencing an increase in population, and this will impact those numbers. Data on the numbers of users, adopters, and The ACQUIRE Project Reality √ User’s Guide 19 commodities are necessary for illustrating what CPR means in terms of numbers, not just percentages. It also helps to illustrate the impact of a growing population on CPR. Let’s look at an example to illustrate how population growth can have an impact on users: Country X had a 31.5% any-method CPR among MWRA in 1998 and a 31.5% any-method CPR among MWRA in 2003. The CPR data seem to indicate that Country’s X CPR stagnated over the five- year period between surveys. However, Country X experienced population growth over that same time period. In 1998, the population of MWRA was approximately 4.3 million, while in 2003, the number of MWRA had risen to approximately 5.1 million. The chart below shows that while the any-method CPR stayed the same between surveys, the population increased, and therefore the number of family planning users actually increased. Year Any-method CPR among MWRA Estimated MWRA population Estimated no. of users Difference in users between 1998 and 2003 1998 31.5% 4.3 million 1.4 million 2003 31.5% 5.1 million 1.6 million 200,000 more users This example points out that there were gains in the number of family planning users in Country X; however, if one solely focused on CPR, it would have appeared that no gains had been made and that the family planning program had stagnated. Population Data That You Have to Input Now that you have completed inputting past CPR trend data and projecting future CPR, it is time to input the population data needed to calculate the numbers of users and adopters for any given year and method. You must be sure to input the number of WRA or MWRA, depending on which you decided to use as your basis for planning. As noted above, most projections use MWRA, but if you use WRA, you must also use CPR based on WRA. For specific information on how to obtain or calculate population data estimates needed for Reality √, see Appendix A: Population Data. We can see from the image on page 21 that Row 32 contains the Number of MWRA in this example. You will then need to input the projected population figures into these green cells going out 25 years. It is very important that you always double-check your population figures to make sure they were input correctly. Again, as this input is connected to formulas throughout the worksheet, any inaccurate data entry here will create faulty projections. 20 Reality √ User’s Guide The ACQUIRE Project Population data are linked to the other levels of analysis, so we will now move on to the Number of Users to explore how population growth impacts family planning programming. NUMBER OF USERS After inputting the number of WRA or MWRA, it is time to quantify CPR in terms of users. Again, while CPR itself is important, calculating the number of users by method helps to quantify and illustrate what CPR means for the demands upon a program. While we saw a simplistic example above of the relationship between CPR and population, the actual formula for calculating the number of users is: MWRA*CPR/100, or WRA*CPR/100, where the CPRs pertain to the percentage of all women using a method The example below shows the projected number of female sterilization users in 2015. We can see the calculation in the formula bar, and the blue line denotes the two fields linked to the calculation for the CPR and for MWRA: Number of MWRA*female sterilization CPR/100 The ACQUIRE Project Reality √ User’s Guide 21 Interpreting the Data: Number of Users We know from the earlier trend analysis that female sterilization utilization was increasing by 0.12 percentage points annually. You may have thought “0.12 points is not much at all” or “increasing female sterilization usage by 0.12 points annually should not be that difficult”; this is where looking at the numbers of users and adopters becomes extremely useful. In this example, female sterilization prevalence in 2014 is 3.32% which equals an estimated 50,352 female sterilization users. In 2015, the prevalence is projected to be 3.44%. However, because the number of MWRA rose, there would be an estimated 53,758 users in 2015. Thus, because of the increase in MWRA, an increase in prevalence of a mere 0.12 percentage points means an increase of 3,406 users in one year. We can look at an example of resupply methods to solidify the importance of examining users. We see from the image below that pill users are calculated in the same way. All of the cells pertaining to the projected number of users are formulated in the same way throughout the worksheet; they all link to the number of MWRA*CPR/100. 22 Reality √ User’s Guide The ACQUIRE Project This shows that pill prevalence of 4.64% in 2015 means that there will be 72,511 users in 2015. We saw from the trend analysis that pill prevalence was calculated to rise by 0.12 percentage points annually, so the 0.12 increase in prevalence translates into an increase of 3,960 pill users between 2014 and 2015. Again, explore the projections and think about what the numbers indicate. It is important to think about the projection in the context of family planning programming and the likely implications on access, resources, services, etc. Before continuing with further discussion and analysis on what user numbers can illustrate, it is important to look at numbers of adopters. Calculating the number of adopters (new users) will shed more light on how to understand the number of users. The strong relationship between the various levels of analysis becomes increasingly apparent as we move through these steps. NUMBER OF ADOPTERS (NEW USERS) We have seen how the number of projected users is calculated, but to actually attain a given number of users in any one year, we have to account for various factors. New adopters are needed each year for three reasons: to replace those who discontinued since the previous year (as well as sterilization users who pass age 49); to allow for the rise in the CPR; and to allow for population increase in WRA or MWRA. These three sources of adopters (new users) produce the projected increase in users for the next year. The Excel worksheet shows the growth in users needed from one year to the next due to the CPR rise and population growth, and the number of adopters needed is this increase plus an allowance for dropouts from the previous year. The formulas in each cell for adopters correspond to that. Thus the formula for calculating the number of adopters in a given year is: (the number of users in the current year – the number of users in the previous year) + (the number of users in the previous year * the discontinuation rate) The examples below will help show the application of these formulas and the relationship between users and adopters. First, we will explore discontinuation. The ACQUIRE Project Reality √ User’s Guide 23 Discontinuation Discontinuation rates impact the programmatic decisions you make. Discontinuation will always be a factor with contraceptive methods, for a range of reasons: People may not be satisfied with their method; the partner may not like the method; people may lack access to a method; a couple may want to have another child; they may switch to another method; or they may no longer need contraceptive protection. However, discontinuation can be reduced by implementing measures such as providing thorough counseling, so that clients know what to expect from their method. It is important for programmers to assess why clients discontinue their method, so the reasons can be addressed accordingly. Discontinuation rates are calculated here by estimating the percentage of all users who cease use in a 12-month period. If you have local data on method-specific discontinuation rates, you can use those. Discontinuation data may also be available through the DHS. If you do not have local data on discontinuation rates, you can use average international rates: Pill 0.50 Injectable2 0.50 Standard Days Method 0.40 Condom 0.50 IUD 0.28 Implant (Norplant or Jadelle) 0.28 Implant (Implanon) 0.48 Sterilization 0.10 These rates are based on the following factors: Discontinuation rates for resupply methods (pill, injectable, and condoms) consist of the percentage who stop using a method over a one-year period. The discontinuation rate for the Standard Days Method (also known as CycleBeads) also refers to the percentage who stop using the method in a one-year period. It is important to point out that this is a new method currently being introduced in many settings. The discontinuation rate given here is based on initial work done with the method and may be a conservative estimate. A multicountry study on this method is currently in the planning stages.3 For more information on this method, please see the Institute for Reproductive Health, Georgetown University (www.irh.org/RTP-SDM.htm). For the IUD and implant, the mean percentage discontinuing is estimated by calculating the reciprocal of the mean duration of use (the duration of use is also linked to the CYP factor). Thus, current estimates indicate that IUD users employ the method for approximately 3.5 years; dividing 1 by 3.5 produces the percentage of users who drop out per year. This approach suggests that approximately 28% of IUD users drop out from using the method in any given year (Ross et al., 2005). 2 Previous international estimates indicated that the discontinuation rate for injectables was approximately 0.40. However, recent analysis of DHS data (35 DHS surveys in 19 countries) suggests that the average discontinuation rate is higher, at approximately 0.50. 3 Information on discontinuation rates was discussed in e-mail correspondence with Victoria Jennings of Georgetown University in August 2007. 24 Reality √ User’s Guide The ACQUIRE Project Implant discontinuation is calculated in the same way as for the IUD. Estimates for five-year implants indicate that women use them for approximately 3.5 years (again, the duration of use is also linked to the CYP factor). Dividing 1 by 3.5 produces the percentage of users who drop out. Again, approximately 28% of implant users drop out in any given year (Ross et al., 2005). If your program is switching to an implant such as Implanon, which can be used for up to three years, then you could estimate that in actual practice, duration of use may be approximately 2.1 years. You would therefore divide 1 by 2.1 to obtain the percentage of users who drop out annually (48%). (Because information on Implanon use is limited, the Implanon estimates used here are calculated based on average Norplant use.) While one does not discontinue using sterilization, its discontinuation rates actually refer to the percentage who age out of the reproductive cycle after age 49. International estimates indicate that a user relies on sterilization for approximately 10 years before aging out. We can see from the numbers above that resupply methods have higher discontinuation (or drop out) rates than do LAPMs. Discontinuation rates can significantly impact the number of adopters needed each year to match a projected number of users. These estimates are also based on CYP factors (see Ross et. al., 2005, B.1). See Appendix D for more information on CYP. For more information on discontinuation, see Stover et al., 2000, and Ross et al., 2005. Discontinuation Data That You Have to Input You will need to input the discontinuation rate into the worksheet. Column D below shows the cells where you need to include the discontinuation rate for the corresponding method. The ACQUIRE Project Reality √ User’s Guide 25 To illustrate the link between adopters and users, we can look at an example for a resupply method. We will continue with the example of the pill in the year 2015. We see that in Cell Q37, there are 72,511 pill users in 2015. The 72,511 pill users in 2015 represents a total of: 1. The number of women who continued using the method between 2014 and 2015 2. The number of women that contributed to the increase in pill prevalence by 0.12 percentage points between 2014 and 2015 3. The increase in the number of MWRA in the population base However, even more adopters are needed to replace users from the previous year who discontinued use. Therefore, we can see (in Cell Q57) that 38,326 MWRA must adopt the method in 2015 for there to be 72,511 pill users in 2015. This is calculated using the following formula: (Pill Users in 2015 – Pill users in 2014) + (Pill Users in 2014 * Pill Discontinuation Rate) In terms of cell locations, the formula is: (Q38-P38)+($D57*P38)) We will continue with the example of female sterilization in the year 2015 to examine the impact of aging out on the number of adopters. 26 Reality √ User’s Guide The ACQUIRE Project The image below shows the number of adopters needed in 2015 in order to attain 53,758 users in 2015. We can see that the formula is linked to the number of users in the previous year. Interpreting the Data: Number of Adopters In this example, to make up for the numbers of sterilization users who age out and to allow for the increase in MWRA in the population base, a total of 8,442 MWRA will need to adopt the method in 2015 in order to raise the overall number of users from 50,352 to 53,758. Given that the sample projections here pertain to the district level, 8,442 women accessing female sterilization services in one year could have a dramatic impact on the service-delivery system. If, for example, only two sites in the district can provide female sterilization services, the 0.12% increase in female sterilization prevalence may not be achievable. Later in this guide, we will revisit this example to examine further how this increase impacts the service-delivery system’s capacity over time. In going through the various levels of analysis, it becomes clear that projections are useful in estimating future family planning needs. Looking at an increase in the number of users and adopters raises issues you need to consider, such as: What do these numbers mean in terms of commodities and resource requirements? Can the current service-delivery system handle this type of projected client load? The ACQUIRE Project Reality √ User’s Guide 27 Is there a way to move services to lower levels in the service-delivery system so that clients can access services more easily? Would providing some methods in the community free up higher level cadres to offer more LAPM services, such as IUDs or sterilization? Before delving deeper into these questions, it will be helpful to look at projections of commodities and service-delivery capacity. COMMODITIES Now that the numbers of users and adopters have been calculated, it is important to determine the corresponding numbers of commodities that are needed. Commodities for LAPMs are simply equal to the number of adopters in any given year. Commodity needs for the IUD, the implant, female sterilization, and male sterilization are all calculated in this way. (See image below for examples). The Standard Days Method is also calculated in this way. Commodity needs for resupply methods are calculated differently than are those for LAPMs; needs for these methods are calculated based upon the number of users. Thus, it is important for your program to additionally account for wastage, due especially to the fact that pills and condoms can be received but not used. (This is not done for the injectable, since it acts for three full months once 28 Reality √ User’s Guide The ACQUIRE Project received.) Pill users are typically handed three cycles at a time, and with high discontinuation rates, many of these are never used. The same is true of condom supplies given to adopters and users. The image below shows a sample of the calculations for pills and injectables. In this section of the worksheet, for resupply methods, you will have to input the estimated number of commodities a user would need over a one-year period. You can base these figures upon local CYP factors, or you can use international estimates included here (Ross et al., 2005). These figures are input into cells D76–79. Pill commodity needs are estimated by calculating that a user will require approximately 15 cycles per year. Therefore, the number of users in a given year is multiplied by 15. Formula = N38*$D76 Condom commodity needs are calculated by multiplying the number of users in a given year by 120 (the international estimate for the number of condoms a condom user will use annually). Formula =P41*$D79 Injectable (three-month) commodity needs are calculated by multiplying the number of users in a given year by four (the number of injections a user will need annually). Formula =O40*$D78 The ACQUIRE Project Reality √ User’s Guide 29 COMMODITY COSTS Utilizing commodity data, you can project how much those commodities will cost. This is crucial to adequately plan and advocate for the resources needed for family planning. Commodity Cost Data That You Have to Input You can use local cost estimates for each method and input those costs into cells D94–104. Here, international costs are shown in US$ (UNFPA, 2005; USAID, 2007). (See Appendix B: Commodity Costs for more information.) It is important to note that the commodity costs indicated here are costs for commodities only; they do not include additional service delivery and distribution expenses, such as personnel salaries or transport costs to deliver the commodities. These are also only estimates based on averages. To capture additional costs, such as shipping or wastage, you could mark up these costs by a certain percentage, based to your local program estimates. When inputting local costs, be sure to specify the currency type in D91. Sterilization and implant costs appear to be the highest among all methods, but it important to keep in mind that these are a one-time cost. When the one-time LAPM commodity costs are compared over a future-year basis against the costs of resupply methods, LAPMs are among the most cost-effective methods, and they reduce the need for resupply. The costs included here are based on international estimated costs for a commodity or procedure. The commodity costs in Column D are fixed costs. Therefore, the projected costs do not account for future inflation. When projecting commodity costs, please keep in mind that as with all figures projected, Reality √ was designed to only project estimates to help inform future programmatic decision making; the tool can provide insight on possible future scenarios based on the estimated figures and the best data available at the time the projections were run. 30 Reality √ User’s Guide The ACQUIRE Project The commodity costs for all methods are linked directly to the projected commodity requirements for that year. The image below shows the link between cost and the number of commodities. Interpreting the Data: Commodities and Commodity Costs In assessing commodity costs, it is important to think about your existing commodity and logistics system. For example: Has your program experienced stock-outs recently? How do commodities actually reach service-delivery points? Who is responsible for contraceptive commodity procurement? We can also examine the summary data in terms of the cumulative costs of “any modern method” and “all LAPMs.” This will help you to budget and advocate for the necessary resources to program for family planning services in the future. It can also help you to assess which methods are cost-effective for your program. It is important to keep in mind that costs alone should not drive the program; cost must be balanced with protecting a client’s right to a choice of methods. The final level of analysis in Reality √ will concern the service-delivery capacity of your program. The ACQUIRE Project Reality √ User’s Guide 31 SERVICE PROVISION: ESTIMATED NUMBER OF CLIENTS PER MONTH As you have gone through the process of calculating your past trend and projecting that trend, you have probably thought about the current service-delivery capacity of your program. Now it is time to look at estimates for the impact of the projected increases in numbers of users and of adopters on the service-delivery sites. It is very important to keep in mind that it is difficult to fully capture client load for resupply methods, due to factors such as discontinuation and community access to pills and condoms. The estimates for LAPMs are much more reliable, due to the fact that these are provider- dependent methods; therefore, clients will have to visit service-delivery sites to access the methods. Service Provision Data That You Have to Input In this section of the worksheet, you can input current figures for the estimated number of sites able to provide service (Column D) and the estimated client visits per year (Column C). The figure below shows those cells. The estimated number of visits per year for all methods can be adjusted based upon your current policies. For example, if pill resupply clients can receive six packages of pills per visit, then you would input two visits per year for that method in Cell C112. Or, if your current policy required IUD clients to come in for a follow-up visit, you could input a 2 in Cell C120. The formula for calculating the number of clients per month per site for resupply methods is: ((number of users * the number of visits per year)/number of sites able to provide service)/12 See the following figure for an example. 32 Reality √ User’s Guide The ACQUIRE Project Given that pills and condoms may be available through community distribution points such as pharmacies, it is up to you as to how you want to use this field. Given that LAPMs are provider dependent and that clients have to access those methods at the site level, to be consistent, you may want to solely focus on site-level distribution here (versus trying to also account for community-level distribution). Very Important Qualification: It is extremely important again to point out that estimates for resupply methods are based on the number of users, and therefore the service-delivery estimates will actually be higher for these methods, due to clients who begin using a method but then discontinue. These figures only take into account the number of users (i.e., women who continue using a method for a 12-month period) and not those who start using a method but then discontinued. When examining these figures and analyzing the estimates, consideration must be given to the number of people who will visit sites and who subsequently will discontinue use of their method (thus having an impact on client load). Because LAPMs and the standard days method (also known as CycleBeads) do not require resupply visits, the estimated client load for these methods is calculated by using the number of adopters in a given year instead of the number of users. The formula for these methods is: ((number of adopters * the number of visits per year)/number of sites able to provide service)/12) The ACQUIRE Project Reality √ User’s Guide 33 Because the estimated client load figures for the LAPMs are linked to adopters and because adopters are visiting a site for one insertion or procedure (which is provider-dependent), these estimates will be more accurate than the estimates for the shorter acting methods. Interpreting the Data: Service-Delivery Capacity When looking at the data for service provision, it is again important to think about issues that have been mentioned previously, such as the number of service sites or trained providers, etc. The existing and expected service capacity may not be sufficient to provide for the projected client load; therefore, more resources and interventions may be required to increase the capacity to provide those services. If we look at the estimated number of implant clients (in this example, Norplant/Jadelle) that will be served per month at each site that can provide implants in the year 2015 (Cell Q118), the estimated service-delivery capacity for each site able to provide implants is approximately 30 clients per month. The daily client load could then be calculated; this would mean approximately one implant client per day per site. This may prompt a need to think about issues such as: Is it feasible to offer implant services to more clients? What is the best schedule for implant services—daily, or perhaps only once a week? 34 Reality √ User’s Guide The ACQUIRE Project COUPLE-YEARS OF PROTECTION Reality √ is designed to estimate CYP. In Column D, rows 126–136, you can input the CYP factor used in your program. (See Appendix D for more information on CYP.) You can see from the image below that the CYP factors for the pill, injectables, and condoms are the same as the commodity estimates used earlier. The CYP factor currently used by USAID for SDM is 2 (Institute for Reproductive Health, 2006), while the CYP factor for LAPMs is based upon average length of usage (as described in the section on discontinuation). The CYP figures calculated for each LAPM adopter are captured all at once within the year of adopting the method. This section of the worksheet is linked to commodities. The example below shows that CYP for the pill in 2012 is calculated by dividing pill commodities in 2012 (Cell N126) by the CYP factor (in this case, 15—Cell D126). While CYP for the resupply methods is calculated by dividing the commodities by the CYP factor, CYP for LAPMs and SDM is calculated by multiplying commodities by the CYP factor; this is because one commodity (such as one IUD insertion) provides longer protection for a client than the resupply methods. For example, we can look at the implant in 2013 (Cell O132) and see that it is linked to implant commodities in 2013 (Cell O82). Thus, the formula for calculating the CYP for implants in 2013 equals (O82*$D132). The ACQUIRE Project Reality √ User’s Guide 35 As throughout the use of the tool, keep in mind that all figures are rounded for ease of use in working with the data. Therefore, there may be minor discrepancies in the projections due to rounding. As you have now completed the calculations and projections on your past trend, you can move next to testing scenarios. But before jumping into the next section, be sure to review the data sheet and think about what the results suggest. Examining these assumptions will help you think about scenarios to test in the next steps of the process. Running CPR Quickly If you wanted to run a quick calculation to view a projection for any method CPR or for any modern method CPR, you could use the “pill” option as a proxy; you would do this if you did not want to run details on methods individually and instead just wanted a quick overview of any method or any modern method CPR and users. To do this, you would have to detail in your assumptions that you are using the “pill” option as a proxy for any method or any modern method CPR and you would input the CPR into “pill” cells B14 and BC14. You would input the number 0 into all other methods. You would also input your projected population data so that you could project estimates on the number of users. Please keep in mind that you would then only be able to look at projections for CPR and users. Data on adopters, commodities, commodity costs, service delivery, and CYP would not be applicable for this type of proxy calculation. 36 Reality √ User’s Guide The ACQUIRE Project 2: Future Goals: Projecting by Testing Scenarios OVERVIEW Now that you have calculated past trends and what things would look like if the past trend continued, it is time to test alternative CPR and method-mix scenarios. This will allow you to input data based on previously established CPR goals or based on scenarios that you would like to test. You can also use past trend data to inform the goals you will test. Testing scenarios is really about the art of programming for family planning. Being able to input and test various method prevalence rates will help you to create projections that can inform programmatic decisions. For example, the data will help guide your decision-making process on factors such as: Resource utilization Interventions needed to reduce discontinuation Future commodity needs requiring attention Provider capacity needed to meet projected service estimates Demand-side interventions needed to address barriers to contraceptive uptake The worksheet for testing future projection scenarios is very similar to the one used to calculate the past trend. The same concepts that apply to that worksheet apply to this one. The biggest difference is that here, you are testing various CPR scenarios by inputting your scenarios or goals on the far right side. The concepts are identical to those for calculating past trends, so you can refer back to the previous sections of the guide for clarification. Note that this time, however, the annual changes come from the difference between the goal (far right side of the worksheet) and the starting value. Previously, the changes were projected from the past trend. Source Data and Assumptions Data That You Have to Input The same instructions previously noted have to be followed for inputting the source data and assumptions (see image below for illustrative example). The assumptions outlined in this section of the worksheet are: 1998–2003 Trend Calculation used for resupply methods. Scenario is set to attain a 26% modern method prevalence in 2015. This is based on an increase in LAPM use while setting resupply methods similar to past trends (see worksheet 1 for this trend). SDM is now included in the method mix, and it is set to rise to 0.30. Male sterilization is set to rise to 1% from 0%, female sterilization is set to increase to 1.2%, and implant (in this case, Jadelle will be used) is set to 2.3%. Overall, these goals mean a projected 1-percentage-point increase in modern method CPR per year from 2004–2015. The ACQUIRE Project Reality √ User’s Guide 37 Future Goal CPR Data That You Have to Input Your CPR data points must be entered into this worksheet in the same way as in the previous worksheet. The biggest exception is that here, you will only input your most recent survey or estimated data points (Column D) (see below). You also will need to input Final Year to be Projected (Cell C14). This scenario is being projected through 2015. If you wanted to project to 2020, for example, you would input 2020 in that cell. Once you have input the year and the CPR data points, you must input your projected goals. This is done by inputting your Final Year Projected Prevalence into the green cells in column AE. This worksheet can also project up to 25 years after the year of your initial starting point, but you can choose the final year you would like to project. For example, if you would like project to 2020, you would input 2020 in Cell C14. Column AE automatically recognizes the final year and adjusts the columns in the middle of the worksheet to stop at 2019. For this example, 2015 will be the final year projected. The image below shows that column P contains figures for 2014 and that column AE shows figures for 2015. All of the columns in between are hidden for visual purposes. Please keep in mind that you can test any scenario you would like in a relatively fast manner. For example, you can input projected prevalence based on previously established national FP goals. Or you could test other scenarios based on variants such as: Increasing male sterilization Increasing injectable use Decreasing traditional method use Increasing implant use Introducing SDM 38 Reality √ User’s Guide The ACQUIRE Project We can see below that the various projected method prevalence rates are set to add to a 26% modern method prevalence and a 30.6% any-method prevalence. In this scenario, LAPM use is set to rise to 7.7%, while resupply methods are set to increase at a pace similar to the past trend. One minor difference in this worksheet is that the average annual increase is to the right side of the spreadsheet. The right side does show the differences in measurement between the final year projected and the first year projected (see below). This information can give you a sense of the magnitude of change from your first year projected and your last (in this case, 2004 to 2015). The ACQUIRE Project Reality √ User’s Guide 39 In this worksheet, calculations for the section on adopters and for all sections below it begin with the first year projected—in this example, 2004. That is because they are based upon the difference between users from one year to the next. Here, 2003 adopters cannot be calculated, since there is no 2002 column to yield changes from 2002 to 2003 (see the empty 2003 cells in the image below). Additional Data That You Have to Input in the Projected Scenario Worksheet The information noted below should be entered in the same way as it was in the Past Trends worksheet. Please see those sections of the guide for more information. Population Data The number of MWRA (or WRA) can be copied and pasted from the previous spreadsheet. Number of Adopters You will need to input local method-specific discontinuation rates, or you can utilize the international averages previously provided. Commodity Costs You will need to input the cost estimates, or you can utilize the international rates previously provided. Service Provision: Estimated Number of Clients per Month You will need to input the estimated number of sites able to provide services and the estimated number of visits per client per year. Given that in this worksheet you are projecting based on future goals, you can test different service-delivery options, such as increasing the number of sites that can provide a service or reducing the number of visits a resupply client would need. 40 Reality √ User’s Guide The ACQUIRE Project Interpreting Your Data In analyzing your projected scenario and goals, it will be important to think about all of the concepts previously discussed. The following considerations, which have been discussed throughout the guide, can help you interpret your results: In terms of an international standard, relatively strong family planning programs can experience a 1.5% annual increase in CPR. How does your projected scenario compare with this number? What factors have to be in place in order to reach the goals? Will a renewed commitment at various policy levels towards family planning be required in order to reach the goals? What resources will be needed to meet the goals, and where will they be needed? What communications campaigns or community activities will be required to increase method utilization? What should the family planning priorities be? Should training more providers in LAPM be a priority? How can the contraceptive logistic systems be strengthened to improve supply lines and access to services? Do champion policy makers, providers, community leaders, or other family planning advocates exist? How can the program identify and nurture them to strengthen a focus on family planning? Should new family planning methods be introduced in your program? Is a policy change needed in terms of the level of cadre allowed to provide a specific method, so that services are easier to access at the community level? Does your program offer a balanced method mix? Is there a balanced distribution of use by method? If not, what factors may be at play, and what action is needed? Let’s look at vasectomy prevalence in the projected scenario as an example. The ACQUIRE Project Reality √ User’s Guide 41 For the context of this example, let’s say a family planning programmer decided that male sterilization is an underutilized method and determined that there is a need to offer this service. Male sterilization prevalence was 0.00% in 2003, and the family planning programmer decided that the projected future goal in 2015 should be 1.00%. We can see that this will require an average annual increase of 0.08 percentage points (Cell AG25). What does this mean in terms of users and adopters? Let’s look at the image below to see what a level of prevalence in 2015 of 1.00% translates into. The image shows the first three years projected for 2004–2005 and the final year projected for 2015. The number of users in 2004 is 888 (Cell F48) and in 2015 it is 15,627—a difference of approximately 14,740 users (Cell AG48). The number of adopters rises from 888 in 2004 (Cell F66) to 3,115 in 2015. To determine what this means in terms of services, we will have to look at trained providers, commodities, and estimated monthly client load. As can be seen in the image on page 43, in 2004, the cost of providing male sterilization services was projected to be $4,395 for 888 procedures. In 2015, due to an increase in adopters, that cost would be $15,421 for 3,115 procedures. The estimated number of sites where male sterilization services would be available is set at just one here, but this assumption can be based on either current service capacity or a goal of offering male sterilization services at just one site in the district. In this example, the single site would have its monthly client load increase from 74 in 2004 to 260 in 2015. The example also projects the estimated CYP associated with each year. 42 Reality √ User’s Guide The ACQUIRE Project In analyzing the projections, one may realize that it is not realistic to expect one site to provide 260 male sterilization services in one month; this suggests that service capacity needs to be expanded to meet the projected case load. Decision makers would have to plan and provide for: Trained providers Equipment for the procedure Small initial client load so providers can practice and build their skills Possible negative community perceptions of male sterilization Resources needed to launch a male sterilization program Where services will be located (will there be mobile services?) Drawing upon the concepts explained in previous sections of the guide will help you analyze your data and the scenarios you would like to test. You can copy the projected scenario worksheet to run as many projections and scenarios as you would like. To build on the previous example, you may want to see how the results change if male sterilization services were offered at three sites in the district. Running various goals and scenarios will allow you to make programmatic decisions based on sound data and assumptions. Before concluding, we can look at a resupply method as an example. Let us say that a family planning programmer decides that injectable prevalence(in this example, the three-month injectable) would rise to 11% by 2015, from 6.4% in 2003. This would be an approximate annual increase of 0.38 percentage points (Cell AG17). The ACQUIRE Project Reality √ User’s Guide 43 What does this increase mean in terms of users and adopters? The image below shows us that this average increase of 0.38 percentage points per year will result in close to 100,000 more users (Cell AG40, formula = AE40 – F40) per year by 2015 over initial 2004 projections. It also means that there will need to be almost 52,000 more new adopters in 2015 over the 2004 projection (Cell AG58, formula = AE58 – F58). Examining commodities and commodity costs can shed further light on how to plan for injectable supplies while looking at projected client load can help assess service-delivery capacity planning needs (see figure below). 44 Reality √ User’s Guide The ACQUIRE Project While looking at the projections, however, it is important to remember that these figures are based on users, so therefore one must take into account that discontinuers (who do not count as users) are not accounted for within these figures. Therefore, you must consider how the impact of discontinuation will lead to higher estimates than the figures estimated here. The sample programmatic considerations raised throughout the guide can help you think further about these projections and about their implications for your family planning program. The ACQUIRE Project Reality √ User’s Guide 45 Conclusion and Additional Resources Now that you have been through the steps for assessing your past trends and for testing future goals, you can generate and use projections to make programmatic decisions more wisely or advocate for more resources to help meet the family planning goals of your program. Exploring alternative projections will help you plan realistically to ensure that service capacity keeps pace with the desire to serve more clients both efficiently and effectively. If you would like to learn more about any of the concepts introduced in this guide, please see the following additional resources: Institute for Reproductive Health (IRH), Georgetown University. 2006. Standard days method: Implementation guidelines for program personnel. Washington, DC. PATH and United Nations Population Fund (UNFPA). 2006. Meeting the need: Strengthening family planning programs. Seattle. Ross, J., Stover, J., and Adelaja, D. 2005. Profiles for family planning and reproductive health programs: 116 countries, 2nd Edition. Glastonbury, CT: The Futures Group. Ross, J., and Bulatao, R. 2001. Contraceptive projections and the donor gap: Meeting the challenge. Washington, DC: Population Action International. Stover, J., Heaton, L., and Ross, J. 2005. FamPlan: Version 4: A computer program for projecting family planning requirements. Washington, DC: The Policy Project/The Futures Group. Stover, J., Bertrand, J. T., and Shelton, J. D. 2000. Empirically based conversion factors for calculating couple-years of protection. Evaluation Review 24(1):3–46. United Nations Department of Economic and Social Affairs, Population Division. 2005. World population prospects: The 2004 revision. New York. UNFPA. 2005. Achieving the ICPD goals: Reproductive health commodity requirements 2000–2015. New York. U.S. Agency for International Development (USAID). 2007. CycleBeads Emailable USAID Funds Order Form 2007. The ACQUIRE Project Reality √ User’s Guide 47 Appendix A: Population Data Note: We are grateful to the United Nations Population Division for the permission to use data from World Population Prospects: 2006 Revision in Reality √. Full data are available from: United Nations, Department of Economic and Social Affairs, Population Division. 2007. World Population Prospects: The 2006 Revision, CD-ROM Edition - Comprehensive Dataset in Excel and ASCII formats (United Nations publication, Sales No. E.07.XIII.8). To estimate the projected MWRA or WRA population for a given geographic area, you can use the United Nations Population Division Department of Economic and Social Affairs data. World Population Prospects: 2006 Revision has population projections through the year 2050. Please keep in mind that this file has been set to show numbers in thousands. The World Population Prospects files can be used to create your own Excel file containing the data for the country in which your program operates. You need to copy and paste the information into a new Excel file. There are a number of ways to do this; the steps detailed here provide one way. Bangladesh is used as an example below. Bangladesh data has been copied into a new Excel file. Within the new file, estimates on WRA or MWRA can be calculated. The ACQUIRE Project Reality √ User’s Guide 49 The original population file is formatted so that the cells show a number of decimal places, but for ease of use in working with the data, you can format your new file to show no decimal places (the image below shows example without decimal places). In your new file, you will want to first segregate the age range that you want to examine. Given that Reality √ population data focus on WRA or MWRA, you will need to isolate the columns for ages 15–49. You can do this by hiding the columns containing estimates from 0–14 and 50–100. This has been done in the image below. 50 Reality √ User’s Guide The ACQUIRE Project Now that you have isolated the data for the female population aged 15–49, you want to total those cells. Insert a column to the right of the data for the totals. We can see in the image below that this has been done in Column Y and that a formula summing cells G–M has been inserted; this formula calculates the total female population aged 15–49. The ACQUIRE Project Reality √ User’s Guide 51 Now that you have calculated the total number of WRA at the national level, you can input these numbers into Reality √ if you are running national-level WRA projections. If, however, you are running projections based on national-level numbers of MWRA, you must calculate the estimated number of MWRA. To do this, you must add two additional columns and formulas. As we can see from the image below, Column Z contains Estimated percentage of WRA who are married (MWRA). The estimate used here of 76.20% was taken from Ross, 2005, Appendix A: Table A.4. This table contains the latest survey or estimates on the percentage of women currently married for nations globally, and it can be used if local data are not available from another source. Column AA contains a formula for obtaining the number of MWRA: Total WRA*Estimated % of WRA who are married (columns Y*Z) If you are running national-level projections based on MWRA, these estimates can be entered into Reality √. However, if you are running regional- or district-level data on MWRA, then another calculation will be needed to determine the estimated number of MWRA at a regional or district level. You will again add two additional columns. The first is Regional or District Estimate of National Population and the second column is Regional or District Estimate of MWRA (see figure below). In column AB, you will input the percentage of the national population that your region or district represents. The formula to calculate the estimated MWRA for your geographic are is AA*AB. For example, if you were looking at data for six regions in a country, the population breakdown of the country could look like the following: Region A: 18% Region B: 30% Region C: 14% Region D: 15% Region E: 12% Region F: 11% 52 Reality √ User’s Guide The ACQUIRE Project We can see from the image below that data for Region A are being run in this example; column AB shows the 18%. Now that you have your estimated MWRA for the geographic area in which your program is operating, you can copy it to a separate worksheet. In it, you will transpose the figures from a column to a row, so as to fit it into the Reality √ worksheet. To do this, block (copy) the column, put the cursor on an empty cell in a new sheet, click on “past special,” click on “values,” then click on “transpose.” This arranges the figures in a row, from which you can then copy/paste into the Reality √ worksheet. Given that the UN Excel data is formatted as thousands, after you transpose your MWRA data into the new worksheet, you must multiply the figures by 1,000 to obtain population figures correctly formatted for use in Reality √ (see below for example). It is important to also keep in mind that the cells are rounded, so the rounded figures are used in all calculations. You can now copy your data into your Reality √ projection files. The ACQUIRE Project Reality √ User’s Guide 53 Appendix B: Commodity Costs The table below was adapted from UNFPA’s Achieving the ICPD Goals (UNFPA, 2005) to reflect estimated averages for Norplant, Jadelle, Implanon, the two-month injectable, and the Standard Days Method (also known as CycleBeads). Method Units required for one couple-year of protection Price per unit (U.S dollars) Cost per couple- year of protection (c) Female sterilization 1/9 (a) $9.09 (b) $1.01 Male sterilization 1/9 (a) $4.95 (b) $0.55 IUD 1/3.5 $0.576 (b) $0.16 Norplant 1/3.5 $23.80 $6.80 Jadelle 1/3.5 $21.00 $6.00 Implanon (d) 1/2.1 $20.74 $9.87 Pill 15 $0.24 (b) $3.60 Injectable—three-month 4 $0.965 (b) $3.86 Injectables—two-month 6 $0.86 $5.16 Condom 120 $0.035 (b) $4.20 Standard days method/CycleBeads (e) 2 $0.99 $0.50 a Though sterilization’s effect is permanent, it is assumed to provide only nine years of protection, because many are not sterilized until late in their reproductive careers. b Prices are weighted averages of those normally paid by UNFPA and USAID, with weights reflecting respective shares of commodities they supply. c Calculated from preceding columns. d Implanon is not purchased by USAID. The cost cited here is based on a tiered pricing system where the cost of purchasing 30,000–50,000 units is $20.74. This tier is cited based on an example of what the cost would be if a country with a population size of Uganda had a 1% CPR for implants; then, approximately 43,000 units would be needed. The other tiers range in price from approximately $18.18–$23.37 per unit. e Price is inclusive of CycleBeads with instructions and calendar (USAID, 2007). The ACQUIRE Project Reality √ User’s Guide 55 Appendix C: Excel Functions: Tips on Using Excel The following tips on using Excel are taken, with permission, from the help files of Microsoft Office Online. Save a File On the File menu, click Save. Note: If you’re saving the file for the first time, you’ll be asked to give it a name. Save a copy of a file 1. On the File menu, click Save As. 2. In the File name box, enter a new name for the file. 3. Click Save. Tip: To save the copy in a different folder, click a different drive in the Save As drop-down list or a different folder in the folder list, or both. To save the copy in a new folder, click Create New Folder. Save a file to another format 1. On the File menu, click Save As. 2. In the File name box, enter a new name for the file. 3. Click the Save as type drop-down list, and then click the file format that you want the file to be saved in. 4. Click Save. Save files automatically while working 1. On the Tools menu, click Options, and then click the Save tab. 2. Select the Save AutoRecover info every check box. 3. In the minutes box, enter the interval for how often you want to save files. The more frequently your files are saved, the more information is recovered if there is a power failure or similar problem while a file is open. Note: AutoRecover is not a replacement for regularly saving your files. If you choose not to save the recovery file after opening it, the file is deleted and your unsaved changes are lost. If you save the recovery file, it replaces the original file (unless you specify a new file name). Speed up saving a file 1. On the Tools menu, click Options, and then click the Save tab. 2. Do one of the following: To save only the changes to a file, select the Allow fast saves check box, and then continue to save as you work on the file. The ACQUIRE Project Reality √ User’s Guide 57 To save a complete file, clear the Allow fast saves check box when you finish working on the file, and then save it one last time A full save occurs when this check box is clear. Note: The Allow fast saves option is only available for Microsoft Word and Microsoft PowerPoint. Copy a File 1. On the standard toolbar (toolbar: A bar with buttons and options that you can use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), click Open. 2. Click a shortcut on the Places Bar (Places bar: The bar on the left side of certain dialog boxes [such as Open, Save As, or Insert Picture] that contains shortcuts to the My Recent Documents, Desktop, My Documents, My Computer, and My Network Places folders.), or in the Look in box, click the drive or folder that contains the file you want to copy. 3. In the folder list, locate and open the folder that contains the file you want to copy. 4. Right-click the file you want to copy, and then click Copy on the shortcut menu (shortcut menu: A menu that shows a list of commands relevant to a particular item. To display a shortcut menu, right-click an item or press SHIFT+F10.). 5. In the Look in box, click the drive or folder you want to copy the file to. 6. In the folder list, locate and open the folder you want to copy the file to. 7. Right-click anywhere in the folder list (make sure your pointer is not resting over a file in the list), and then click Paste on the shortcut menu. Note: You can also select more than one file to copy at a time. How? 1. On the standard toolbar (toolbar: A bar with buttons and options that you can use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), click Open. 2. Do one of the following: To select nonadjacent files in the Open dialog box, click one file, and then hold down CTRL and click each additional file. To select adjacent files in the Open dialog box, click the first file in the sequence, and then hold down SHIFT and click the last file. Tip: If you select a file you don’t want, hold down CTRL and click the file again. Move or Copy Sheets Caution Be careful when you move or copy sheets. Calculations or charts based on worksheet data might become inaccurate if you move the worksheet. Similarly, if you insert a worksheet between sheets that are referred to by a 3-D formula reference (3-D reference: A reference to a range that spans two or more worksheets in a workbook.), data on that worksheet might be included in the calculation. 1. To move or copy sheets to another workbook, open the workbook that will receive the sheets. 58 Reality √ User’s Guide The ACQUIRE Project 2. Switch to the workbook that contains the sheets you want to move or copy, and then select the sheets. How? When you enter or change data, the changes affect all selected sheets. These changes may replace data on the active sheet and other selected sheets. To select Do this A single sheet Click the sheet tab. If you don't see the tab you want, click the tab scrolling buttons to display the tab, and then click the tab. Two or more adjacent sheets Click the tab for the first sheet, and then hold down SHIFT and click the tab for the last sheet. Two or more nonadjacent sheets Click the tab for the first sheet, and then hold down CTRL and click the tabs for the other sheets. All sheets in a workbook Right-click a sheet tab, and then click Select All Sheets on the shortcut menu (shortcut menu: A menu that shows a list of commands relevant to a particular item. To display a shortcut menu, right-click an item or press SHIFT+F10.). Note: If sheet tabs have been color-coded, the sheet tab name will be underlined in a user-specified color when selected. If the sheet tab is displayed with a background color, the sheet has not been selected. Cancel a selection of multiple sheets: To cancel a selection of multiple sheets in a workbook, click any unselected sheet. If no unselected sheet is visible, right-click the tab of a selected sheet. Then click Ungroup Sheets on the shortcut menu. 3. On the Edit menu, click Move or Copy Sheet. 4. In the To book box, click the workbook to receive the sheets. To move or copy the selected sheets to a new workbook, click new book. 5. In the Before sheet box, click the sheet before which you want to insert the moved or copied sheets. 6. To copy the sheets instead of moving them, select the Create a copy check box. The ACQUIRE Project Reality √ User’s Guide 59 Hide or display rows and columns Hide a row or column 1. Select the rows or columns you want to hide. To select Do this Text in a cell If editing in a cell is turned on, select the cell, double-click in it, and then select the text in the cell. If editing in a cell is turned off, select the cell, and then select the text in the formula bar. A single cell Click the cell, or press the arrow keys to move to the cell. A range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells Click the first cell of the range, and then drag to the last cell. A large range of cells Click the first cell in the range, and then hold down SHIFT and click the last cell in the range. You can scroll to make the last cell visible. All cells on a worksheet Click the Select All button. Nonadjacent cells or cell ranges Select the first cell or range of cells, and then hold down CTRL and select the other cells or ranges. An entire row or column Click the row or column heading. Adjacent rows or columns Drag across the row or column headings. Or select the first row or column; then hold down SHIFT and select the last row or column. Nonadjacent rows or columns Select the first row or column, and then hold down CTRL and select the other rows or columns. More or fewer cells than the active selection Hold down SHIFT and click the last cell you want to include in the new selection. The rectangular range between the active cell (active cell: Tip: To move sheets within the current workbook, you can drag the selected sheets along the row of sheet tabs. To copy the sheets, hold down CTRL, and then drag the sheets; release the mouse button before you release the CTRL key. 60 Reality √ User’s Guide The ACQUIRE Project The selected cell in which data is entered when you begin typing. Only one cell is active at a time. The active cell is bounded by a heavy border.) and the cell you click becomes the new selection. Cancel a selection of cells Click any cell on the worksheet. 2. On the Format menu, point to Row or Column, and then click Hide. Notes If the first row or column of a worksheet is hidden, click Go To on the Edit menu. In the Reference box, type A1, and then click OK. On the Format menu, point to Row or Column. and then click Unhide. Instead of being hidden, the height or width of the first row or column of a worksheet may have been set to zero. In this case, point to the border of the Select All button until the cursor changes to or , and then drag to widen the row or column. Move or Copy Cells and Cell Contents Using the Cut, Copy, and Paste commands in Microsoft Excel, you can move or copy entire cells or their contents. You can also copy specific contents or attributes from the cells. For example, you can copy the resulting value of a formula without copying the formula itself, or you can copy only the formula. Note: Excel displays an animated moving border around cells that have been cut or copied. To cancel a moving border, press ESC. Move or copy entire cells When you move or copy a cell, Excel moves or copies the entire cell, including formulas and their resulting values, comments, and cell formats. 1. Select the cells that you want to move or copy. How to select cells To select Do this Display a hidden row or column: 1. Do one of the following: To display hidden rows, select the row above and below the rows you want to display. To display hidden columns, select the column adjacent to either side of the columns you want to display. 2. On the Format menu, point to Row or Column, and then click Unhide. The ACQUIRE Project Reality √ User’s Guide 61 A single cell Click the cell, or press the arrow keys to move to the cell. A range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells Click the first cell of the range, and then drag to the last cell. A large range of cells Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible. All cells on a worksheet Click the Select All button. Nonadjacent cells or cell ranges Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges. More or fewer cells than the active selection Hold down SHIFT while you click the last cell that you want to include in the new selection. The rectangular range between the active cell (active cell: The selected cell in which data is entered when you begin typing. Only one cell is active at a time. The active cell is bounded by a heavy border.) and the cell that you click becomes the new selection. Note: To cancel a selection of cells, click any cell on the worksheet. 2. Do one of the following: To move cells, click Cut on the Standard toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), or press CTRL+X. To copy cells, click Copy on the Standard toolbar, or press CTRL+C. 3. Select the upper-left cell of the paste area (paste area: The target destination for data that's been cut or copied by using the Office Clipboard.). Tip: To move or copy a selection to a different worksheet or workbook, click another worksheet tab or switch to another workbook, and then select the upper-left cell of the paste area. 4. Click Paste on the Standard toolbar, or press CTRL+V. Notes: To choose specific options when you paste cells, you can click the arrow next to Paste on the Standard toolbar, and then click the option that you want. By default, Excel displays the Paste Options button on the worksheet to provide you with special options when you paste cells, such as Keep Source Formatting and Match Destination Formatting. If you don't want to display this button every time you paste cells, 62 Reality √ User’s Guide The ACQUIRE Project you can turn this option off. On the Tools menu, click Options. On the Edit tab, clear the Show Paste Options buttons check box. Excel replaces existing data in the paste area when you move cells. When you copy cells, cell references are automatically adjusted. When you move cells, however, cell references are not adjusted, and the contents of those cells and of any cells that point to them may be displayed as reference errors. In this case, you'll need to adjust the references manually. If the selected copy area (copy area: The cells that you copy when you want to paste data into another location. After you copy cells, a moving border appears around them to indicate that they've been copied.) includes hidden cells, Excel also copies the hidden cells. You may need to temporarily unhide cells that you don't want to include when you copy information. If the paste area contains hidden rows or columns, you might need to unhide the paste area to see all of the copied cells. Move or copy entire cells by using the mouse By default, drag-and-drop editing is turned on so that you can use the mouse to move and copy cells. 1. Select the cells or range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells that you want to move or copy. How to select cells To select Do this A single cell Click the cell, or press the arrow keys to move to the cell. A range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells Click the first cell of the range, and then drag to the last cell. A large range of cells Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible. All cells on a worksheet Click the Select All button. Nonadjacent cells or cell ranges Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges. More or fewer cells than the active selection Hold down SHIFT while you click the last cell that you want to include in the new selection. The rectangular range between the active cell (active cell: The selected cell in which data is entered when you begin typing. Only one cell is active at a time. The active cell is bounded by a heavy border.) and the cell that you click becomes the new selection. 2. Do one of the following: The ACQUIRE Project Reality √ User’s Guide 63 To move a cell or range of cells, point to the border of the selection. When the pointer becomes a move pointer , drag the cell or range of cells to another location. To copy a cell or range of cells, hold down CTRL while you point to the border of the selection. When the pointer becomes a copy pointer , drag the cell or range of cells to another location. Notes Excel replaces existing data in the paste area (paste area: The target destination for data that's been cut or copied by using the Office Clipboard.) when you move cells. When you copy cells, cell references are automatically adjusted. When you move cells, however, cell references are not adjusted, and the contents of those cells and of any cells that point to them may be displayed as reference errors. In this case, you'll need to adjust the references manually. If the selected copy area (copy area: The cells that you copy when you want to paste data into another location. After you copy cells, a moving border appears around them to indicate that they've been copied.) includes hidden cells, Excel also copies the hidden cells. You may need to temporarily unhide cells that you don't want to include when you copy information. If the paste area contains hidden rows or columns, you might need to unhide the paste area to see all of the copied cells. Insert moved or copied cells between existing cells 1. Select the cell or range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells that contains the data that you want to move or copy. 2. Do one of the following: To move the selection, click Cut on the Standard toolbar, or press CTRL+X. To copy the selection, click Copy on the Standard toolbar, or press CTRL+C. 3. Select the upper-left cell of the paste area (paste area: The target destination for data that's been cut or copied by using the Office Clipboard.). Tip: To move or copy a selection to a different worksheet or workbook, click another worksheet tab or switch to another workbook, and then select the upper-left cell of the paste area. 4. On the Insert menu, click Cut Cells or Copied Cells. 5. In the Insert Paste dialog box, click the direction in which you want to shift the surrounding cells. Note: If you insert entire rows or columns, the surrounding rows and columns are shifted down and to the left. Copy visible cells only If some cells, rows, or columns on your worksheet are not displayed, you have the option of copying all cells or only the visible cells. For example, you can choose to copy only the displayed summary data on an outlined (outline: Worksheet data in which rows or columns of detail data are grouped so that you can create summary reports. The outline can summarize either an entire worksheet or a selected portion of it.) worksheet. 64 Reality √ User’s Guide The ACQUIRE Project 1. Select the cells that you want to copy. 2. On the Edit menu, click Go To. 3. In the Go To dialog box, click Special. 4. In the Go To Special dialog box, click Visible cells only, and then click OK. 5. Click Copy on the Standard toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), or press CTRL+C. 6. Select the upper-left cell of the paste area (paste area: The target destination for data that's been cut or copied by using the Office Clipboard.). Tip: To move or copy a selection to a different worksheet or workbook, click another worksheet tab or switch to another workbook, and then select the upper-left cell of the paste area. 7. Click Paste on the Standard toolbar, or press CTRL+V. Notes Excel pastes the copied data into consecutive rows or columns. If the paste area contains hidden rows or columns, you might need to unhide the paste area to see all of the copied cells. If you click the arrow next to Paste on the Standard toolbar, you can choose from several paste options to apply to your selection. When you copy or paste hidden or filtered (filter: To display only the rows in a list that satisfy the conditions you specify. You use the AutoFilter command to display rows that match one or more specific values, calculated values, or conditions.) data to another application or another instance of Excel, only visible cells are copied. Prevent copied blank cells from replacing data 1. Select the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells that contains blank cells. 2. Click Copy on the Standard toolbar, or press CTRL+C. 3. Select the upper-left cell of the paste area (paste area: The target destination for data that's been cut or copied by using the Office Clipboard.). 4. Click the arrow to the right of Paste on the Standard toolbar, and then click Paste Special. 5. Select the Skip blanks check box. Move or copy the contents of a cell 1. Double-click the cell that contains the data you want to move or copy. Note: By default, you can edit and select cell data directly in the cell by double-clicking it, but you can also edit and select cell data in the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.). 2. In the cell, select the characters that you want to move or copy. 3. Do one of the following: To move the selection, click Cut on the Standard toolbar, or press CTRL+X. To copy the selection, click Copy on the Standard toolbar, or press CTRL+C. The ACQUIRE Project Reality √ User’s Guide 65 4. In the cell, click where you want to paste the characters, or double-click another cell to move or copy the data. 5. Click Paste on the Standard toolbar, or press CTRL+V. 6. Press ENTER. Note: When you double-click a cell or press F2 to edit the active cell, the arrow keys work only within that cell. To use the arrow keys to move to another cell, first press ENTER to complete your editing changes to the active cell. Copy cell values, cell formats, or formulas only When you paste copied data, you can do any of the following: Convert any formulas in the cell to the calculated values without overwriting the existing formatting. Paste only the cell formatting, such as font color or fill color (and not the contents of the cells). Paste only the formulas (and not the calculated values). 1. Select the cell or range of cells that contains the values, cell formats, or formulas that you want to copy. 2. Click Copy on the Standard toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.). 3. Select the upper-left cell of the paste area (paste area: The target destination for data that's been cut or copied by using the Office Clipboard.) or the cell where you want to paste the value, cell format, or formula. 4. Click the arrow to the right of Paste on the Standard toolbar, and then do one of the following: To paste values only, click Values. To paste cell formats only, click Paste Special, and then, in the Paste Special dialog box, click Formats. To paste formulas only, click Formulas. Note: If the copied formulas contain relative cell references, Excel adjusts the references (and the relative parts of mixed cell references) in the duplicate formulas. For example, suppose that cell B8 contains the formula =SUM(B1:B7). If you copy the formula to cell C8, the duplicate formula refers to the corresponding cells in that column: =SUM(C1:C7). If the copied formulas contain absolute cell references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.), the references in the duplicate formulas are not changed. If you don't get the results that you want, you can also change the references in the original formulas to either relative or absolute cell references and then recopy the cells. Edit data on multiple worksheets When you select several worksheets (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) and then change the data on one of them, the changes are applied to the same cells on all the selected worksheets. 1. Click the tab of the first worksheet that contains the data that you want to edit. Then hold down CTRL while you click the tabs of other worksheets that contain the same data. 66 Reality √ User’s Guide The ACQUIRE Project Note If you don't see the tab that you want, click the tab scrolling buttons to display the tab, and then click the tab. 2. On the active worksheet (active sheet: The sheet that you're working on in a workbook. The name on the tab of the active sheet is bold.), select the cell or range of cells that contains the data that you want to edit. How to select cells To select Do this A single cell Click the cell, or press the arrow keys to move to the cell. A range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells Click the first cell of the range, and then drag to the last cell. A large range of cells Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible. All cells on a worksheet Click the Select All button. Nonadjacent cells or cell ranges Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges. An entire row or column Click the row or column heading. Row heading Column heading Adjacent rows or columns Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column. Nonadjacent rows or columns Select the first row or column, and then hold down CTRL while you select the other rows or columns. More or fewer cells than the active selection Hold down SHIFT while you click the last cell that you want to include in the new selection. The rectangular range between the active cell (active cell: The selected cell in which data is entered when you begin typing. Only one cell is active at a time. The active cell is bounded by a heavy border.) and the cell that you click becomes the new selection. The ACQUIRE Project Reality √ User’s Guide 67 Note: To cancel a selection of cells, click any cell on the worksheet. 3. Edit the data in the selected cell, and then press ENTER or TAB to move the selection to the next cell. 4. Repeat the previous step until all the changes have been made. Notes When you edit data, the changes affect all selected worksheets and may inadvertently replace data that you didn't mean to change. If needed, you can view worksheets at the same time: 1. On the Window menu, click New Window. 2. Switch to the new window, and then click a worksheet that you want to view. Repeat for each worksheet that you want to view. 3. On the Window menu, click Arrange, and then click the option that you want. 4. To view worksheets in only the active workbook, select the Windows of active workbook check box. To cancel a selection of multiple worksheets, click any unselected worksheet. If an unselected worksheet is not visible, right-click the tab of a selected worksheet, and then click Ungroup Sheets. Rename a worksheet The name (or title) of a worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) appears on its sheet tab on the Sheet tab bar at the bottom of the screen. By default, the name is Sheet1, Sheet2, and so on, but you can give your worksheet a more appropriate name. 1. To rename the active sheet, do one of the following: On the Format menu, point to Sheet and then click Rename. On the Sheet tab bar, right-click the tab you want to rename, and then click Rename. 2. Type the new name over the current name. Tip: You can include the name of the sheet when you print the worksheet. On the View menu, click Header and Footer, and then, in the Page Setup dialog box on the Header/Footer tab, click Custom Header or Custom Footer. In the Left section, Center section, or Right section box, enter the sheet tab by clicking Tab in the row of buttons in the Header or Footer dialog box. Copy Excel data and charts to Word or PowerPoint 1. Select the data or chart you want to copy. 68 Reality √ User’s Guide The ACQUIRE Project 2. Click Copy. 3. Switch to Microsoft Word or Microsoft PowerPoint. 4. Click in the document or presentation where you want to put the Microsoft Excel data or chart, and do one of the following: Paste the data into Word 1. Click Paste on the Formatting toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.). 2. Click Paste Options next to the data, and then do one of the following: To paste the data as a Word table, click Match Destination Table Style or Keep Source Formatting. To paste a link to the Excel data, so that the data in the Word document is updated whenever you change the data in the original Excel workbook, click Match Destination Table Style and Link to Excel or Keep Source Formatting and Link to Excel. To paste the data as text with each row in a separate paragraph and tabs separating the cell values, click Keep Text Only. Paste a chart into Word 1. Click Paste on the Formatting toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.). 2. Click Paste Options next to the chart, and then do one of the following: To paste the chart as a chart, so that when you double-click the chart in the Word document you can use Excel to edit it, click Excel Chart. To paste a link to the Excel chart, so that the chart in the Word document is updated whenever you change the chart in the original Excel workbook, click Link to Excel Chart. To paste a bitmap picture of the chart, click Picture of Chart. Paste the data into PowerPoint 1. On the Edit menu, click Paste Special, and then do one of the following: To paste the data as an Excel range, so that when you double-click the range in the presentation you can edit it with Excel, click Microsoft Excel Worksheet Object. If data is cut off, double-click the object and resize the columns to show all the data. To paste a link to the Excel data, so that the data in the presentation is updated whenever you change the data in the original Excel workbook, click Paste link, and then click Microsoft Excel Worksheet Object. To paste the data as text that you can edit in PowerPoint, click Formatted Text (RTF) or Unformatted Text. To paste a bitmap picture of the data, click Picture or Bitmap. Paste a chart into PowerPoint 1. On the Edit menu, click Paste Special, and then do one of the following: The ACQUIRE Project Reality √ User’s Guide 69 To paste the chart as a chart, so that when you double-click the chart in the presentation you can use Excel to edit it, click Microsoft Excel Chart Object. To paste a link to the Excel chart, so that the chart in the presentation is updated whenever you change the chart in the original Excel workbook, click Paste link, and then click Microsoft Excel Chart Object. To paste a bitmap picture of the chart, click Picture. Tip: If you want a range of Excel data that you've pasted into Word or PowerPoint to expand when the data expands in Excel, name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) the range in Excel before you copy it, and then paste a link to the named range. Remove the split from a window or unfreeze a pane 1. To restore a window that has been split into two scrollable areas, double-click any part of the split bar that divides the panes (pane: A portion of the document window bounded by and separated from other portions by vertical or horizontal bars.). 2. To remove nonscrolling “frozen” panes, click Window, and then click Unfreeze Panes. Change the scale of a value axis By default, Microsoft Excel determines the minimum and maximum scale values of the value (y) axis (axis: A line bordering the chart plot area used as a frame of reference for measurement. The y axis is usually the vertical axis and contains data. The x-axis is usually the horizontal axis and contains categories.) in a chart. You can, however, customize the scale to better meet your needs. 1. In a chart sheet (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.) or an embedded chart (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.), click the value (y) axis (axis: A line bordering the chart plot area used as a frame of reference for measurement. The y axis is usually the vertical axis and contains data. The x-axis is usually the horizontal axis and contains categories.) that you want to change. How to select an axis 1. Click the chart sheet or the embedded chart. 2. On the Chart toolbar, click the arrow next to the Chart Objects box, and then click Category Axis or Value Axis. Tip: If you don't see the Chart toolbar, point to Toolbars on the View menu, and then select Chart. The Chart Objects box is available unless the toolbar has been anchored at the left or right side of the screen. 2. On the Format menu, click Selected Axis. Note: The Selected Axis command is only available when a chart axis is selected. 70 Reality √ User’s Guide The ACQUIRE Project 3. On the Scale tab, do one or more of the following: Important: The following scaling options are only available when a value (y) axis is selected. The Scale tab provides different options for a category (x) axis. To change the number at which the value axis starts or ends, type a different number in the Minimum box or the Maximum box. To change the interval of tick marks (tick marks and tick-mark labels: Tick marks are small lines of measurement, similar to divisions on a ruler, that intersect an axis. Tick-mark labels identify the categories, values, or series in the chart.) and chart gridlines (gridlines in charts: Lines you can add to a chart that make it easier to view and evaluate data. Gridlines extend from the tick marks on an axis across the plot area.), type a different number in the Major unit box or Minor unit box. To change the units displayed on the value axis, select the units that you want in the Display units list. If you want to show a label that describes the units, select the Show display units label on chart check box. Tip: If the chart values are large numbers, you can make the axis text shorter and more readable by changing the display unit of the axis. For example, you can display chart values ranging from 1,000,000 to 50,000,000 as 1 to 50 on the axis and show a label that indicates that the units are expressed in millions. To change the value axis to logarithmic, select the Logarithmic scale check box. Tip: This type of scale is useful when the values that are plotted in the chart cover a very large range. To reverse the order of the values on the selected axis, select the Values in reverse order check box. Note: When you change the order of the values on the value (y) axis from bottom to top, the category labels on the category (x) axis will flip from the bottom to the top of the chart. Likewise, when you change the order of the categories from left to right, the value labels will flip from the left side to the right side of the chart. To specify the value at which the category (x) axis crosses the value (y) axis, type that value in the Category (X) axis crosses at box. To force the x-axis, cross the value axis at the highest value, and select the Category (X) axis crosses at maximum value check box. This effectively moves the category labels to the opposite side of the chart. Note: This option overrides the Category X crosses at value. The ACQUIRE Project Reality √ User’s Guide 71 Tip: XY (scatter) charts show values on both the category (x) axis and the value (y) axis, while line charts show values on only the value (y) axis. This difference is an important factor in deciding which chart type to use. Because the scale of the line chart's category axis can't be changed as much as the scale of the value axis that is used in the xy (scatter) chart, you might consider using an xy (scatter) chart instead of a line chart if you need to change the scaling of that axis or display it as a logarithmic scale. 72 Reality √ User’s Guide The ACQUIRE Project Appendix D: CYP Factors The following are recommended couple-year of protection (CYP) factors for sterilization, by country. Country CYP per procedure Country CYP per procedure Global 9 Africa 8 Latin America 9 Ethiopia 8 Bolivia 10 Gambia 8 Brazil 10 Ghana 8 Colombia 11 Guinea 8 Dominican Republic 12 Kenya 9 Ecuador 10 Liberia 9 El Salvador 12 Madagascar 8 Guatemala 11 Malawi 8 Mexico 8 Mali 7 Nicaragua 11 Mauritius 9 Paraguay 11 Namibia 7 Peru 9 Nigeria 8 Trinidad and Tobago 9 Rwanda 8 Venezuela 9 Sierra Leone 8 Tanzania 8 Uganda 8 Zaire 8 Zimbabwe 8 Asia 10 North Africa and the Near East 8 Bangladesh 11 Egypt 7 India 13 Jordan 8 Indonesia 9 Morocco 9 Nepal 12 Tunisia 9 Pakistan 9 Turkey 9 Philippines 11 Sri Lanka 11 Thailand 10 Adapted from: Stover, J., Bertrand, J. T., and Shelton, J. D., 2000. Empirically based conversion factors for calculating couple years of protection. Evaluation Review 24(1): 3-46. The ACQUIRE Project Reality √ User’s Guide 73 Appendix E: Contraceptive Discontinuation Rates Contraceptive discontinuation rates Pill IUD Condom Injectables Female Sterilization Implants (Norplant) Total Sub-Saharan Africa Ethiopia 2005 60.9 - 41.8 32 - - 40.9 Malawi 2004 52.3 - 61.9 32.5 - - 35.8 Kenya 2003 46.2 - 59.4 31.8 37.6 Tanzania 2004 40.5 - 45 36.5 - - 37.8 Zimbabwe 1994 15.1 - 44.2 15.6 - - 19.5 Zimbabwe 1999 16.3 - 43.3 26.2 - - 20.2 North Africa/West Asia/Europe Armenia 2000 - 6.5 38.9 - - - 39.7 Armenia 2005 6.1 31 32.1 Egypt 1992 41.8 12.5 47.8 - - - 29 Egypt 1995 46.2 14.2 55.7 52.1 - - 29.8 Egypt 2000 48.4 14.2 52.5 48.4 - - 28.5 Egypt 2005 50.2 15.2 37.9 45.5 - 19.3 30.6 Jordan 1990 63.5 20.9 63.8 - - - 44.6 Jordan 1997 67.9 17.7 67.6 - - - 48.5 Jordan 2002 55.4 12.8 56 55.2 - - 42 Moldova Republic of 2005 49.8 6.6 38.8 - - - 37.8 Morocco 1992 37.4 18.9 - - - - 38.6 Morocco 2003-2004 (1) 40.9 15.3 50.4 52.4 - - 43.2 Turkey 1993 55.1 10.1 49 - - - 36.8 Turkey 1998 56.4 9.4 43.3 - - - 34.9 Central Asia Turkmenistan 2000 72.8 11.7 58.5 - - - 58 South & Southeast Asia Bangladesh 1993/94 (2) 44.9 37.1 71.9 57.6 - - 47.7 Bangladesh 1999/2000 (3) 46.3 34.3 66.7 50.1 - - 48.3 Bangladesh 2004 (4) 46.3 - 71.2 48.7 - - 49.2 Indonesia 1991 29.9 15.7 51.2 32.8 - 3.5 27.5 Indonesia 1994 33.8 15.2 50.9 29.1 - 4.3 26.8 Indonesia 1997 33.9 12.3 37.7 23.5 0.1 2.9 24.1 Indonesia 2002/2003 31.9 8.9 38.8 18.4 - 2.7 20.7 Philippines 1993 40.6 22.6 59.8 - - - 35.6 Philippines 1998 43.8 14.3 60.1 51.8 - - 41.1 Philippines 2003 39.2 14 58 52.7 - - 39 Vietnam 1997 33.2 10.3 32 - - - 17.6 Vietnam 2002 36.1 12.5 37.8 - - - 24.8 The ACQUIRE Project Reality √ User’s Guide 75 Contraceptive discontinuation rates Female Sterilization Implants (Norplant) Pill IUD Condom Injectables Total Latin America & Caribbean Bolivia 1994 58.9 11.9 69.8 78.2 - - 41.1 Brazil 1991 (5) 51.1 - 82.9 72.7 - - 46.8 Brazil 1996 44.8 - 60 63.7 - - 43.4 Colombia 1990 44.9 20 57.4 63.7 - - 39.3 Colombia 1995 52.4 19.1 64.8 67.4 0.1 - 50.3 Colombia 2000 49.7 16.8 61.5 62.1 - - 52.1 Colombia 2005 47.2 16.6 52.9 50.7 - - 42 Dominican Republic 1991 63.4 37.6 79.1 - - - 52.5 Dominican Republic 1996 59.2 32.7 82.8 - - - 52.7 Dominican Republic 1999 60.4 - - - - - 54.8 Dominican Republic 2002 51.7 27.2 67.4 67 - - 47.5 Guatemala 1995 46.6 18.5 50.6 56 0.8 - 35.8 Guatemala 1998/99 51.4 - 68.9 56 - - 42.3 Nicaragua 1997/98 49.2 24.3 63.3 59.7 - - 39.7 Paraguay 1990 60 15.5 72.2 71.2 - - 57.1 Peru 1992 56.4 11.9 66.7 66.6 - - 47.2 Peru 1996 53.8 17.4 59.2 53.6 - - 45.2 Peru 2000 54.4 15.1 53.3 40.6 - - 40 Average 47.6 16.4 55.8 49.2 0.3 5.5 39.2 Source: ORC Macro, 2007. MEASURE DHS STATcompiler. http://www.measuredhs.com 76 Reality √ User’s Guide The ACQUIRE Project
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.