Home

ASAP HIVAIDS Costing Model User Manual

1. fgpersonmonh A Staffing Laboratory Technician 00 k1 1 FE r f A Sia ea 1 uda A Staf ng LocalFacilitatos 0048240 f personmihl S A Staffing FE E A Staffng ther Technical and Craft Skilled Staf LLJ FE A Staffing OtherTechnician Fl A Staffing Phyiceaa FE A Sumog alte H siih Cen S e A Staffing Registered Nurse 000000 I FE END v M 4 n NE YEN Basic Data x cae of Accounts Strategic Plan Me lt CR Ready NUM start SE Ei F Welcome IB DN User Manual Ell ASAP HIV AL EN Y Kit Figure 8 Chart of Accounts and Mapping To add an account it is necessary to position the cursor somewhere within the appropriate account group and click on the button Add Acct The new account name and units should then be replaced vvith nevv unique information for the nevv account 13 The units define how the quantities of a particular account item will be measured For example condoms would be measure in units of each while staff might be measured in staff months or staff years also known as Full time Equivalents FTEs and consultants in person months This is an important link between the unit costs and the number of units For example if the un
2. SoAnuoout 1ejououi ur uonedioned simN 95 SoAnuoout 1ejououi ur uonedroned uerorsAquq 93 1Inosos p q e1rdsoq Jad sueroruqooj pue sosinu suerorsAud uone ndod 0001 194 sp q yerdsoH 07 07 07 07 07 07 suonne d es roAmu ul puq oui oseg p9J9A05 ST YTOA YHL JO uu UOISIOUINIAL IEN 2 x OS SUOTUDSAIOUI UOTOWOId uropuoo ZUA SACI 94 uonmnsqn SHIG ZUA SOG 94 ISUBYOXY ISULIAS pu SUTAT L SNGI uoneonp 4 Jooq pu yorayng Ajrunuruio FUI SOI 9o Surjso pue Surj sunoO SurAI929J SGI 94 WAVY SUIA SAC SACI 001 Jed poured q o sroy suno SICH Jo J9quinw si osn ship n luy E N ul puq ur se i SUIOPUOD SUISN pue TVS N Jod suropuo SUIOPUOD SUISN A1 OYM poy WSW 44 0 TE A yoko poyoval S NS N SINSIN JO 1TequinN UJUI QJIA X S JABU OYA WW N ul puq oui oseg poyoval JoyIOM xos Jad 1eo Jod p mqinsip suropuoo y Jo A IoyIOM xos Jad 1eo T d p mqinsip suropuoo jew Jo SUIOPUOD o eul9 osn 180 POYDVII SIOYIOM KIS JO SUIOpUoOO IJEU SR jet SI9XJOA KIS JO Teak YORI SIOYIOM X S 0 SIDYIOM X S JO JOQUINN X S E N oui oseq suone ndog xsu 3e SOWN S 3 us A 15 oui oseq oq o suoneqndogq eroodg Jo 94 suone ndog Ter
3. YIM po9J9A0 S Q Jo uone ndod ATH 1 np V peusunougeui sy npy Jo sjuaurg dddng L E N ul puq ur se i UJIPIIYO oneurojduiAS ATA N 10 98919402 A MV 9 5 sy py oneurojdulAS ATA N 10 98919402 9 5 uonuoASJd VHA Id 103 p33 UAP PAJUTI Jo 9 uone ndog orerpoeq 99919402 AV 41 10 Doan o uone ndod ATH ynpe JO Suez de1oq L our puooog uo osoq Jo 04 Adev q g 9ur7 puosas uo uone ndod ATH 1inpv Jo 94 Suez de1oq our 1512 uo sou Jo Adev u our siri uo uone ndod ATH 1 np v Jo AV Suunnb i suosiad p y l Jo uoguoASJd VHAA Id 401 sy npe p yul Jo 04 uonus49Jd VHAVId pu V E N ur puq un se i VHAM Id L 399uSs 980 1940 pue SIISE L sjoous ndul veg cg x uuy ST AIH uneripoegq 310ddns eorSo ouo sd A o8e19A00 04 uoneyndod ATH ornerpoeq III ejuop YUM 998919400 97 uone ndod ATH merp eq III SWIOY YM 989J9A00 0 uone ndod ATH 1 npy 1310ddns ye rsoyoq Asd YIM 998919A00 9 uone ndod ATH np V 3129 e3uop UJIM 99919A00 04 uoneyndod ATH 1 npV r IWO YUM 2 0 2 AHETVEQ SUIA SYPIA ATH 9Hnerpoegq 10 04 9187 oAnI Jeqd SUIA S p q ATH MPY 04 E N E N E N ur puq un se i uo1p rqo npuro dul4S A MON IO YIM sy npe onewozdwAs AMN TO UNA uone ndod ATH oLjerpoeq 1 YIM uonernd
4. 2 6 2 idi ga e 10 z B ZI Ul m a 9 S S SE E L Oe A B27 fe A NEIN C 4 1 E FE amp T H d T xa LE 28 29 30 31 E 33 A ARY 2006 2007 2008 2009 2010 34 Adult HIV population 1 518 000 1 613 273 1 714 526 1 822 134 35 5096 36 37 38 39 40 96 Failing Treatment ooo l 2596 2396 596 42 ber on Fi 253 43 44 45 46 950 3 412 47 Pat die pulation 20 214919 227 922 241 713 256 337 ECH 48 Percent ARV Coverage P Toe 2196 0 50 Newly symptom 320619 337670 355627 274540 394458 ei Percent ARY Coverage Tt ml cl ox 52 Popul vere 54 Percent ARV Coverage UZ 006 0 0095 55 Population red 56 57 v 4 gt n EER Basic Data X Targets and Coverage Chart of Accounts Strategic Plan Map o Je sil Ready NUM e w E ES ge a 27 W 2 Microsoft El ASAP Costing T E Microsoft Powe Figure 6 ARV Target Coverage Section The information entered in this section will automatically be carried forward to the following sections so that it doesn t need to be entered again To ensure consistency all changes in coverage levels should be reflected in this section rather than for example in the section where the interventions are costed Once a particular section has been completed the user can return to the menu for this
5. s pick list External accommodation and per diem per Z E 95 ee a 1961 i 5 i 98 ft Is uu 99 jp kr LEE ees KSE 102 Costs not related to number ofpeople trained J o 18 Number 103 Local Consultants person month Jf 5 00 7 711 a 5 5 i v M h M Chart of Accounts Strategic Plan Activity Costing Y Training Financing Gap An mil Ready NUM E w E E v KN Welco v i User M E3 Micros an Cor Figure 18 New Training Template The number of people to be trained is entered in the yellow line while the number of units per trainee 1s entered below the relevant number of trainees Space is provided to allow the user to add further account types from the pick list included in the blank cells below the pre filled ones The second part of the template includes cost types that are not directly related to the number of trainees and also allows additional cost types to be specified Rows can be added to both sections as appropriate using the same approach as for the activity costing sheet Since the Total Table at the top of this worksheet Lines 48 81 is the vehicle for accumulating the costs and volumes into the activity costing sheet it 1s critical that any cost types that are added to a particular training t
6. the currency to be used as a reference currency the number of units of national currency per unit of reference currency the scaling factor for reference currency units thousands or millions the number of days in a year that the average person 1s expected to work net of weekends holidays vacation sick time etc the number of days in a year that the average person 1s expected to work net of weekends holidays vacation sick time etc HINT Because there are a large number of individual activities and cost elements a large scaling factor may be needed for reporting but this may result in fewer significant digits than desired at the individual activity level that If this happens it may be useful to set the scaling factor low for the costing but increase it for the reporting This can be simply done by changing the scaling factor on this sheet The remaining parts of this section includes basic demographic data total population and various age gender groupings details on the demographics of the HIV epidemic and some general capacity indicators regarding the health system facilities and personnel The parts entitled Demographic Indicators and Epidemiological Indicators can be copied directly from an RNM if available while the remaining data 1s additional to that model and 1s needed because of the expanded scope of the interventions included in this costing model In order to collect this data it may be usef
7. AIDS Strategy and Action Plan ASAP A Service of UNAIDS USER MANUAL ASAP HIV AIDS Costing Model V1 2 October 2008 www worldbank org asap What s new in v1 2 Improved consistency and functionality reflecting use of the model to date Inflation capability added at user discretion Financing gap analysis added including both recording of financing sources and reporting on the funding gap Report added on unit costs by beneficiary group variable costs only to facilitate cost comparisons Template added for monitoring and evaluation costing Templates added for training activities New function introduced to make it easier to add accounts Development of data input sheets for Basic Data and Targets and Coverage ASAP HIV ATDS Costing Model User Manual v1 2 Table of Contents A niroducton s 4 B Basic Eunctonality UD 4 C Startno TE EE 7 D Enterino Basic Data ee 8 E Entering Targets and Coverage Levels 10 F Complete Chart of Accounts and Mapping 13 G Enter Unit COSIS unudam d baa daaa vey 14 H Map Strategic Plan to HIV AIDS Functions 17 I Costing for Standard Interventions 20 J Costing for Special Interventions 26 K Special Functionality for Training optional 28 L Financing Gap
8. Je Ready 2 start v EES a 27 Dominic 5 Ha Di 2 Microsoft Oe G Microsoft Exc Type a question for help 5 8 x Standard Unit Cost Enter unit costs in national currency Check USD amount 1 USD 71 KSH Check units to make sure they make sense Document assumptions regarding unit costs Col J When finished click the Done button before returning full time equivalent Unit Cost z E E Units Assumptio ri rl ri 71 71 ri iHiHIHIHIH tri tri prd ri tri 63 ri ri d pri tri FTE FTE FTE FTE FTE FTE person month person month person month stipend months stipend months each each e 5 mg EN E Figure 9 Enter Unit Costs As in the development of the chart of accounts it is strongly recommended that financial staff of the HIV AIDS agency and or Ministry of Health be involved in this part of the process which could take place in parallel with the data collection activities of the Basic Data and Targets and Coverage Levels sections Unit costs are entered in local currency units using the exchange rate highlighted at the top of the page This is pulled forward from the Basic Data sheet The costs are converted to USD as they are entered As noted above it is important that the unit costs represent the same units as specified in Column G As much as possible the costs that are ente
9. ds ur uone ndod mo Jo suonv ndog yer ds JW CS 07 07 07 E N ouip oseq E N ouip oseq E N E N E N E N N E N ur puq un se i AISDI X UOU PUB y0q sdrusr uyred reyns r Ul GP S so e A IAY ATTeNXIS 9ATjo enxos Ya 99e dx 10A4 MSO NSIN 9pngour zou op UOIsIAOAd uropuo Joquioui 9910JJIoA d suropuo SUIOpUOO SUIAI929I 929JODJIOA 9 juoumeoJ LS SUIAI929J 0 SI9XJOA 19512 001 Jod poured 99 o s1ojeonpo q d yo 1oquinN uoneonpo Jood SUIAI9291 93JOD IOA 06 guu peAo duro uorje ndod 94 oeu uonpindod 94 SUIBISOId IIg dy AA pouoeoi YINOA oouos Jo 1no SCIV ut poured s19 99 23 YIM sjuopnjs repuooos SGIV ut pouen s19goe9 YIM sjuopnjs SUILISOJA Sururen 91 u99A4 q SILVIA AUPU MOH one 10 repuooog 9 JILI uoryedrorured ooqos Arepuo t o euro 1 94 JILI uonedronaed oouos Arepuo g ALW one Ioqo 2j rdnd Awg 9 JILI uonedroned yoov s o euiro 1 9 JILI uonedroned oouos ALN uno x uonu A id EJouos 9 J uS c SJo Jeo yo 1oquunu SI SOd Jo 1oquinu SUID Jenou jeuonowod Jo s uour uum oo 1odedsAou Jo 1oquinu sojnurui orpel
10. 9 ost Su gt Ready NUM EC MEE d re cmd TJ EH ES Figure 19 Initial Screen for Financing Gap Analysis For financiers other than the Government and the Global Fund a blank template for that financier can be added by clicking on the Add Financier button The result is shown in Figure 20 30 E E Microsoft Excel ASAP HIV AIDS Costing Model v1 2 user manual xls E m ud Eile Edit View Insert Format Tools Data Window Help Type a question for help 5 m x 49 100 e Si 5 3 Pe Reply with Changes End Review 2 1 Amount in Amount in EUR 2009 2010 2011 2012 2008 2009 2010 EN DOLA Se BH Q 12 49PM Figure 20 Blank Template from Adding a Financier Because the user can change the number of rovvs vvhile add ng the financ ng information the blank template for a new financier is always added directly after the list of financiers and before the existing financier templates The other templates are pushed further down the worksheet The Add Financier button takes the user directly to cell 87 where the drop down list can be used to select the financier for this template The currency exchange rate and scaling factor are pulled down from the table above The user then enters the total financing available from that financier over the five year or less period covered by the strategy If desired the amount for each year can be further allocated by fu
11. El Microsoft Excel Type a question For help 1 Select desired expenditure types from d 3 Fill in qreen sections and check costs for reasonableness 2 Fill in qreen sections with number of un 14 4 To change expenditure types select from drop down pick list 3 Do not add rows to standard activity use ES 5 To add rows position cursor in Column A select lt Insert gt lt Row gt BS Then copy previous row and change expenditure type as needed 4 To add nevv set of activities add rovvs to carried forward to strategic p Then copy previous table and increase 803 Other Costs Related to Number of Persons Treated Number of Units per Person Treated Pregnant women attending ANC tested for e 43 918 43931 4394 43958 43971 nponent of antenatal visit 1 4p 2k Z Physician visit examination and medicines PMTCT test kits consumables other T 21 959 219651 21972 2197 21985 810 E 811 Other Technical and Craft Skilled Staff 0 E EES 812 Semi ckiled Operatives and unskilled Staff ET mq 813 814 4 RE WE C I 815 BE Ee uu Ee See DS E ES EE j 23 1 HIV Positive pregnant Ohmufa l Formula milk annual 2 s He 819 Nutritional supplements annual cos ee uu uu uu besser 820 1 sj j 821 Cos
12. Institutional Care OVC Other OVC Program Management Financial Management Monitoring and Evaluation Operations Research Surveillance sero sentinel behavior surveillance HIV drug resistance surveillance Drug supply systems Information Technology Supervision of programs and program support for patient tracking 4 10 4 11 4 12 4 99 5 01 5 02 5 03 5 04 5 05 5 99 6 01 6 02 6 03 6 04 6 99 7 01 7 02 7 03 7 04 7 99 8 01 8 02 8 03 8 04 8 05 8 06 8 07 8 99 Upgrading laboratory infrastructure Construction of new health centers Other renovations upgrading AIDS Program Other Monetary incentives for doctors Monetary incentives for nurses Monetary incentives for other staff Formative Education HIV Workforce Training HR Other Monetary Benefits In kind benefits Social services Income generation Social Other Advocacy and communications Human Rights Institutional Development Programs focused on Women Community Other Biomedical research Clinical research Epidemiological research Social science research Behavioural research Research in Economics Vaccine related research Research Other 41 CV V LUST OAO Jo 19qumN 675 DAO JO IoquinN FU DAO JO JoquinN syp ATH 9Hjerpeeq syed ATH RP V oneuro3duiAs A MON 5 HE F 5 p E j j sirpronuous Simon uu x Arn ompong qoneyndo
13. User Ma El Microso C Docu EN LG SIE Figure 11 Mapping of Strategic Plan to Functions Section As shown in Figure 11 a drop down menu is used to simplify the process and ensure a valid mapping More than one activity can be mapped to a particular classification using up to five additional sections per function Once the mapping is done the 17 costing can then commence by following the instructions in the sheet The costs are automatically brought forward to the appropriate activity The user instructions are shown in the green box at the top of the page with the numbers in the yellow arrows corresponding to the numbered instructions This part of the user manual provides more detail to supplement the instructions provided in the model worksheet The first thing to be done is to insert the details of the strategic plan into the model Three levels of aggregation are allowed in the model with Priority representing the highest level Priority Strategy the intermediate level and Activity the lowest level t 1s the activities which will be costed and then rolled up to the other levels If the particular HIV AIDS strategy has only two levels of detail then only the Priority and Activity columns should be used The user should determine the total number of activities in the strategic plan and then create sufficient space for all of these activities to be copied This can be done by j
14. 99 3 01 3 02 3 03 Mass media Community mobilization Voluntary counseling and testing Special populations Youth in school Youth out of school Prevention programs for people living with HIV Programs focused on sex workers and their clients Programs focused on MSM Harm reduction programs for IDUs Workplace Activities Condom social marketing Public and commercial sector condom provision male Female condom Microbicides Improving management of STIs Prevention of mother to child transmission Blood safety Post exposure prophylaxis safe medical injections Male circumcision Universal precautions Prevention Other Provider initiated testing Prophylaxis for Opportunistic Infections Antiretroviral therapy Nutritional support associated to ARV therapy Laboratory monitoring Dental Programs for PLWHA Psychological Treatment and Support Palliative care Home based care Alternative and informal providers Out patient care NEC Treatment of Opportunistic Infections In patient Care NEC Patient Transport and Emergency Rescue Treatment Other Education OVC Basic Health Care OVC Family home support OVC 40 4 AIDS program development 6 Social mitigation 7 Community development and enhanced environment S HIV and AIDS related research 3 04 3 05 3 06 3 99 4 01 4 02 4 03 4 04 4 05 4 06 4 07 4 08 4 09 Community support OVC Organization costs OVC
15. Analysis optional 30 M Review Total Costs 33 N Kepo rtine nam z asya ss 34 O Global F nd Module secde na 37 Annex A HIV AIDS Function List 40 Annex B 1 Data Input Sheet Basic Data 42 Annex B 2 Data Input Sheet Targets and Coverage 44 User Manual ASAP HIV ATDS Costing Model A Introduction Based on experience in Swaziland and Guyana the ASAP HIV AIDS Costing Model incorporates key features of existing costing models including the concept of target groups and coverage levels from RMN and the functional classification from NASA into an approach specifically designed for costing HIV AIDS strategies It follows a logical menu driven sequence of steps to walk the user through various set up mapping and costing processes with the end result being a fully costed strategic plan The level of detail to be included in the costing is entirely up to the user For example the costing can be limited to the major drugs laboratory supplies and other key cost items or it can be comprehensive encompassing all known types of costs The model also allows the user easily to examine the impact of different coverage levels unit cost reductions and various combinations of strategic plan activities to determine how best to live within overall funding constraints If there 1s sufficient d
16. Edit View Insert Format 1 Check and or print as needed 2 Return to Report Menu when finished x 1 000 000 Cost National Currency KSH ASAP Costing Model Total Costs by Expenditure Type A Staffing 17 11 16 11 56 9 B Medical supplies and drugs 303 307 312 316 1 238 10 C Non medical Supplies s 11 D Operating Costs 1 572 1 197 1 621 1 234 5 624 12 E Equipment and vehicles 13 F Buildings E G Grants Financing 15 H Health care services 270 274 278 282 1 104 16 Total Costs hy Type 2 162 1 790 2 227 1 844 8 022 18 A Staffing 17 11 16 11 56 Administrative Staff Clerical and office support Staff Contracted Employees Other Technical and Craft Skilled Staff Senu skilled Operatives and unskilled Staff Senior Technical Staff International Consultants 7 4 7 4 24 Local Consultants higher level 4 2 3 2 11 Local Consultants lower level 6 4 6 4 21 Stipends Community Volunteers M 4 gt MD Cost Summary by Exp Type Cost by Govt E Classification SS I Je Ready CN Document W E E E3 Microsoft Figure 25 Report of Costs by Exp Cost USD 30450 25 204 31 363 25966 112 983 30450 25 204 31 363 25 966 112 983 30 450 25 204 31 363 25966 112 983 22 378 17017 23 059 17 544 79998 8 072 8 187 2 304 2 42 32985 gt NUM e Welcome t x ed Type a ques
17. FUND REPORTS Budget Summary by Cost Category Human Resources Technical and Management Assistance Training 3 323 3 374 3 426 3 480 3 534 17 137 Health Products and Health Equipment 242 158 231 231 158 1 019 Phatmaceutical Products Medicines R Procurement and Supply Management Costs PSM Infrastructure and Other Equipment z z Communication Materials 2 2 Monitoring and Evaluation M amp E i E Living Support to Clients T arget Population R Planning and Administration R e Overheads 6 702 6 306 6 911 7 018 7 127 34 564 Other 661 632 703 725 748 3 519 Total 10 927 11019 11 272 11454 11566 56 238 DERI x vi i labal Fund SStUB h Global Fund Reports Function List CofA Sorted lt m Ready NUM e w E3 E 9 2 Dominic 5 Ha W 3 Microsoft v EI 2 Microsoft v Figure 30 Reports Screen for Global Fund Module 39 Annex A HIV AIDS Function List based on 2008 version of NASA Classification 1 Prevention 2 Treatment and care 3 Orphans and vulnerable children 1 01 1 02 1 03 1 04 1 05 1 06 1 07 1 08 1 09 1 10 1 11 1 12 1 13 1 14 1 15 1 16 1 17 1 18 1 19 1 20 1 21 1 22 1 99 2 01 2 02 2 03 2 04 2 05 2 06 2 07 2 08 2 09 2 10 2 19 ZOLL 2 29 2 30 2
18. final costs Also it is very important that the user not alter the list at the top of column AA of the Strategic Plan Map sheet since this would affect the overall calculation of the model Once in the costing part of the model the review and finalization of the costing can take place The user should be sure to address the following areas with reference to the corresponding labels in Figure 16 a through 16 c l Number of people to receive the intervention The first step is to make sure that the number of people to receive the intervention 1s appropriate If there appear to be problems in this area it is important to go back to the Targets and Coverage section to revise the coverage levels and obtain the appropriate numbers rather than changing them in this part of the model 2 3 Setting practice pattern variables as needed A number of the standard interventions are pre programmed with flexible patterns of practice variables as shown in Figure 13 a For PMTCT for example a number of potential tests are available and the user 1s able to select the type of test 2 and the percent of the target population who will receive this intervention 3 Once these variables are set the number of units of each type of input 1s determined by the model automatically 4 Check unit costs to determine if these are appropriate for this intervention while the standard unit costs may be fine for most interventions there may be some
19. in base line and end line data base year figure is carried forward but can be changed if needed if end line is provided the intervening years will be interpolated Check results in mauve cells Yellow cells are imported from Basic Data sheet Click on Go to top to return to list of headings To Print Select lt File gt lt Print gt then select printer and select lt OK gt Print area is set and paper is selected for A4 n EY Basic Data X Targets and Coverage Chart of Accounts Strategic Plan Je gt Ready start Mo ON 4 m Dominic S H 112 Microsoft EI microsoft Ex Figure 5 Targets and Coverage Menu 10 Using ARV as an example Figure 6 one can see that the relevant populations adult HIV pediatric HIV newly symptomatic adults newly symptomatic children are pulled forward from the Basic Data section so the user only needs to input the relevant coverage levels in this case for ARV treatment To aid in the data entry the percent coverage entered in the first year 15 automatically carried forward to subsequent years Of course the user can always over ride this by entering specific coverage levels for each year Microsoft Excel ASAP Costing Template Demo v9 xls Read Only Bales 184 File Edib View Insert Format Tools Data Window Help Type a question For help F x 5 05 ied Bl 3 2 1 7 4 53 3 74 0212
20. inflation 1s expected or the user can specify individual inflation rates for each year This rate would be used in each account where the General option is selected 16 Specific a particular inflation rate would be specified for each account where it is selected in Columns L to O of the appropriate line The user has can specify the initial value and having it carried forward to other years if constant inflation is expected or individual inflation rates for each year Depending on the option chosen from the drop down box the appropriate inflation rate and index is reflected and calculated The final rates and inflation index can be viewed in columns P S and T X respectively adjustments can be made if they do not look appropriate As in the development of the chart of accounts once all of the unit costs and inflation factors have been entered it 1s important to click on the mauve button labeled Done in position F1 This initiates a macro that sorts the revised data to make it useable for other parts of the model H Map Strategic Plan to HIV AIDS Functions The mapping section of the model is where the strategies and related activities are introduced into the system Once the strategies and activities are copied each activity is mapped to a standard functional classification To go to this section the user should position the cursor over the Map Strategic Plan to HIV AIDS Functions box on the m
21. lead to confusion and errors E Entering Targets and Coverage Levels The Targets and Coverage section allows the user to set specific targets in 21 separate areas including both prevention and treatment activities To go to this section the user should position the cursor over the Enter Targets and Coverage Levels box on the main menu and left click the mouse A hyperlink will take the user directly to the appropriate part of the model When the user is finished entering data it is possible to move back to the main menu by positioning the cursor over the Menu box and left clicking the mouse This section of the model is also menu driven see Figure 5 The user can either use the menu by clicking on the appropriate hyperlink or it 1s also possible to just scroll down through the various areas As in the previous part of the model all of the instructions as well as the data that needs to be entered by the user are shaded green for easy reference Data calculated by the system is shaded in purple and data that is pulled forward from other sheets is shaded in pale yellow E Microsoft Excel ASAP HIV AIDS Strategy Costing Model v2GF xls AAZ DN File Edit View Insert Format Tools Data Window Help Type a question for help e 2 8 x D ES id ib dv X sadi Z z t 122 214 0 st 2 2 Hz Targets and Coverage Click on heading to qo to relevant section 2 Fill in Green Cells only can put
22. level of funding is allocated for that group For example funding should be allocated to 1 Prevention if there 1s a desire to allocate VCT function 1 03 financing as a percent of the group If additional lines are needed this section only they can be generated by highlighting the number of rows to be added and then clicking on Insert lt Rows gt on the Excel toolbar menu Then one of the existing rows 1s highlighted and the user copies this down through the resulting white space by clicking on Edit lt Copy gt and then highlighting the white space and clicking on Edit Paste on the Excel toolbar menu This is the same process as for Activity Costing Once all of the financing data 1s entered the financing gap can then be analyzed This can be done by clicking on the box entitle Financing Gap Analysis Report located at row 60 of the sheet This will take the user to the report The report can also be accessed from the report menu Once the box 1s clicked the report shown in Figure 2 will appear E Microsoft Excel ASAP HIV AIDS Costing Model v1 2 xls 180 Fie Edit View Insert Format Tools Data Window Help Type a question for help iv 2 8 X LAR gt fe o x 211 Reply with Changes End Review u 9 gt 00 2 E 1 A 2 1 Check and or print as needed Fe 2 Can print either by function group or by function 4 Click on outline buttons at left to expand
23. or contract 5 3 Return to Report Menu when finished LB 5 8 ASAP Costing Model x 1 000 9 Financing Gap Analysis 10 2008 2009 2010 au 2012 Toil 11 Financing Gap 12 Total Gap e 13 l Prevention OF 2 Treatment and care 53 3 Orphans and vulnerable children 1 4 AIDS program development 75 5 Human resources 821 6 Social mitigation b igation 88 7 Community development and enhanced environment 941 8 HIV and AIDS related research 104 168 b Requirements 107 1 Prevention 1311 2 Treatment and care H 147 3 Orphans and vulnerable children 1155 4 AIDS program development 163 5 Human resources Cost Summary by Exp Type Cost by Govt da gt 13 start SG EE Ep a di E N z za ulu Ela dHla EN zeien By EH Q 2 05 PM Figure 21 Financing Gap Analysis Report The report shows the financing gap first then the requirements based on the costing that has been done the total financing and then the details by financier The default level of detail is the function group although this can be expanded by clicking on the em box next to the relevant group this would need to be done for each group where further detail is desired or by clicking on the little number 3 at the top left hand of the worksheet see red circle above To compress again click on number 2 32 The desired parts of the report can then be hi
24. supplies and drugs Non medical Supplies Operating Costs Equipment and vehicles Buildings Grants Financing and Health care services Within these groupings it is possible to add and delete accounts according to the requirements of the particular country E Microsoft Excel ASAP HIV AIDS Costing Model v1 2 xls 18 Ele Edit View Insert Format Tools Data Window Help Type a question for help 2 8 x Standard Uni Fill in Green Cells only with teg government sum daa Enter unit costs in national cu 1 2 Make sure that all necessary types of costs are included Check EUR amount 1 EUI 3 Add accounts under relevant accounting group include units and account name Check units to make sure the Place cursor in Col A of line where account needed and press button below Document assumptions regar When finished click the Done button and then the Menu button If desired define inflation pai When finished click the Don m ua Unit Cost o wa Account Head Units Currenc Asma Staff FTE E ee A Staf ng ClinicalOfier j FE A Stafng Community Outreach Workers LL stipendmithsli A Stsf ng ContractedEmployees FE A Staffing Diver FE 1 A Stsf ng EnroledNus 1 ER A Staffing lntemational Consultants
25. where a different unit cost may be needed The revised cost can be entered in the relevant row in Column H Alt Unit Cost However as noted in the section entitled Enter Unit Costs above if there are many places where alternative unit costs are needed it may be preferable to establish a new account with an appropriate unit cost This would ensure consistency and also ensure that the physical quantities reports provide meaningful data 23 ASAP Costing Template Demo v9 xls El Microsoft Excel E E Type a question for help O x 1 For standard activity check units imported from targets section I 2 Revise targets if units are not correct press Menu Enter Targets 3 Fill in green sections and check costs for reasonableness 4 To change expenditure types select from drop down pick list 5 To add rows position cursor in Column A select lt Insert gt lt Row gt Then copy previous row and change expenditure type as needed i Select desired expenditure types from d Fill in green sections with number of un Do not add rows to standard activity use To add new set of activities add rows to Then copy previous table and increase i can change individual annual percentages if needed v KSH Cost USD 2006 2007 2008 2009 2010 w ic Plan Map Activity Costing Tc i I IEEE FREE
26. 09 wF Training costs material Training costs overnight accommodation Training costs refreshments and meals Local Transportation External accommodation and per diem Cours e conference fees Select cost types from pick list Ready NUM id start E DEEDES A CN Welco T User M Micros 1 EN amp munu WA O 12 25 PM Figure 18 Initial Screen for Training Module 28 To add a training activity the user should click on the button entitled Add Training Activity with the result shown in Figure 19 A new blank template is added directly after the Total Table all previous training templates are pushed down The user then copies the activity name into the appropriate cell in Column C and then the other required data is entered E3 Microsoft Excel ASAP HIV AIDS Costing Model v1 2 xls a Edt Mew Insert Format Tools Window Help Type a question for help x ilg il dh 2 AR 3 5 4 52 2 c A S x BAY 100 m ziulm Z H ii tT e Ae 858 WE E G H J K L Units 0 Cost EUR 2007 2008 2009 86 Number of People to be Trained 0170701 Select Training costs overnight accommodation er training da 89 costtypes Training costs refreshments and meals pe 2 i 90 from Local Transportation kilo 5 il EEN
27. 2 Blood safety and universal precaution M 4 gt l Global Fund Setup Function List CofA Sorted lt Ready NUM Global Fund pcc Mass media BCC Mass media BCC community outreach and schools Condom Testing and Counseling PMTCT Y Y h2 FO SO D i i Y 0211 1 I CO NO O O e WIN lt gt E E fm F Dominic 5 Ha WI 3 Microsoft v 2 2 Microsoft EN k S 0 e 11 18 AM Figure 29 Set up Screen for Global Fund Module The next step is the mapping of expenditure areas to the Global Fund Cost Categories To get to this section of the sheet the Expenditure Mapping box is clicked Again initial suggestions have been made but these need to be checked carefully especially if the user has added expenditure types as part of their costing process To get back to the top the red Top box should be left clicked 38 Microsoft Excel ASAP HIV AIDS Strategy Costing Model v1GF xls dit View Insert Format Tools Data Window Help d 7 X Type a question for help 5 amp x Cost USD 2007 2008 2009 2010 2011 Total GLOBAL FUND REPORTS Budget Summary by Service Delivery Area BCC Mass media 10 266 10 338 10 566 10 729 10 818 52 719 BCC community outreach and schools 2 Condom S Testing and Couns
28. 4 31 363 25 966 112 983 20 2 Treatment and care 21 3 Orphans and vulnerable children 22 4 AIDS program development 23 5 Human resources 24 6 Social mitigation 25 7 Community development 26 8 HIV and AIDS related research 27 Total Costs hs Functia 2162 1 79n 2227 1 244 2022 an Aan 95 NA 21 263 25 OFA 117 022 y M 4 Mj Strategic Plan Map 2 Activity Costing Function List Total Cost Summary Reporting lt gt Ready NUM z start E DEBET e gt ISR exa Microsoft C Document Welcome t Figure 22 Summary Report 33 N Reporting There is also a reporting menu which provides more detailed costing by priority activity function expenditure type from the chart of accounts and government expenditure classification There 1s also a report that details the physical quantities of each type of input used in the model and reports highlighting the main assumptions included in the model including the basic data epidemiology the targets and coverage levels and the unit costs used in the model Graphs are also provided showing priority function expenditure type information To go to this section the user should position the cursor over the Reporting box on the main menu and left click the mouse A hyperlink will take the user directly to the appropriate part of the model From this sheet the user can left click on the appropriate box depending on which type of report they want Wh
29. HIV AIOS 2 AIDS Strategy and Action Plan ASAP A Semice of UNAIDS Ready l I NUM KRIPS 831N31831821 1 x Figure 3 Title Page D Entering Basic Data As noted above the first step 1s entering the basic data This can largely be drawn from RNM or similar sources Three years of data are entered and this is used to project through the following 4 years it 1s planned to extend this to 5 years in the next version of the model The user can also directly enter annual figures where the calculated projections do not appear to be reasonable To go to this section the user should position the cursor over the Enter Basic Data box on the main menu and left click the mouse A hyperlink will take the user directly to the appropriate part of the model When the user is finished entering data it is possible to move back to the main menu by positioning the cursor over the Menu box and left clicking the mouse All of the instructions as well as the data that needs to be entered by the user are shaded green for easy reference Data calculated by the system is shaded in purple and data that is pulled forward from other sheets is shaded in pale yellow The system makes extensive use of drop down menus such as the one for scale below which reduces errors and speeds up the use of the model El Microsoft Excel ASAP HIV AIDS Costing Model ei 2 sl Baik 2 File Edit Vi
30. I ofadults on ART treatment to scale up the therapy 2 Treatment 2 03 Antiretroviral Standard to 3 000 by 2012 response and care therapy Activity 16 1 Increase number 11 1 Expand access to ARV 1 1 4 Children receive ART 2 adults on ART treatment to scale up the 2 Treatment 2 03 Antiretroviral Standard to 3 000 by 2012 response therapy L 2 Increase general 12 1 Expand access to 2 1 1 Distribute male and female condom condoms condoms through social Sech 112 Condom social Standard distribution to 1 program marketing Activity 18 milionbv 2012 a L M X Costing Profile io GoTo Cost 2008 Cost 2009 3 1 Expand PMTCT program 1 Prevention 75 Activity 18 3 Increase 3 1 Expand PMTCT 3 1 2 Provide treatment to H V N 3 M Mj Targets and Coverage Chart of Accounts Strategic Plan Map Activity Costing Te lt TN Ready NLIM Jj start SE E Ens A gt R 3 Ew Figure 12 Exsupl of Completed Activity Matrix The next step in this section 1s to map the activities in the strategy to a standard set of HIV AIDS related functions using the NASA classification A full set of these functions is included in Annex A For each activity in the strategic plan a corresponding function must be specified in Column H To assist in this process all of the possible functions have been included in a drop down list in each cell of this column Wh
31. Jo JoquuNu Sojnulul A JO Joquunu poonpoud oq o spreoqil q Jo Jaquinu uaredureo Jad uoneyndod Got q d 98919407 D Reese ie Dg Eas suyuouu u amp redureo jo t i u RF eee eof s d suredureo Jo z qumu 07 07 07 07 07 07 BIPOU SSE JA oui oseq UOISIAOJd eroJourur09 ot qnd uonod 6303 JO 94 weisold euoneu yer ds qonodg SUVOPUO uonnqinsip uropuo UOISIAOIA erogourur09 or qnd usnouu 630 JO weisoid euorgeu eroods usnoiu Yo SUIOpuoo ojew uornqrsrp uropuo suropuoo aJ jeu suiopuoo 930 JO uonnq ns p pue SULINP 999 Se A WOPUO XOS Jeeu ur suropuoo SUISN sJouj31ed enseo UJIM osou JO SSS en ge tJ sulopuoo TIM po1oA00 Sjot TENSEI JO 94 sdrusr uyred Jenga ur Jou So eui s1ouj1ed enseo LA 5128 xos Jo Joquinu enuue ogeJoAV oAIsn oxo uou JOU 2AISn oXo ISU sued Ien s v UJIM JOU 5198 X S 910 JO pue oAISn oxo u oq sr uyred ens YM so eur 10 sjo xos eNUUL 9SeJoA V sIouj1ed enseo savy OSTE OYM S TETN OSA JO 9
32. Universal precautions annual cost per EES Physical Quantities Function List Z CofA Sorted 4 TN Ready NUM start Ji E F LEN Figure 26 Example of Physical Quantities Report Figure 27 shows a new report which calculates unit costs by various types of beneficiaries which can allow comparisons with other countries which have used a similar costing methodology EI Microsoft Excel ASAP HIV AIDS Costing Model v1 2 xls EES 21 Be Edit View Insert Format Tools Data Window Help Type a question for help 5 8 X E z id cd WPW T LA ee 1 Check and or print as needed 2 Return to Report Menu when finished m Cen costz Cove 201i costa Average Corto Gor costone c simi coran Adult HIV population Total ARV First Line Therapy Second Line Therapy Treated for TB Paediatric HIV population Newly symptomatic adults Newly symptomatic children Laboratory Monitoring Adults Laboratory Monitoring Children ASAP Costing Model Unit Costs by Beneficiary Sex workers Men who have sex with men Injecting drug users Harm reduction Counseling and Testing Community Outreach and Peer Education Needle and Syringe Exchange Drug Substitution Condom promotion interventions Unit Costs by Beneficiary Dominic Figure 27 Unit Costs by Beneficiary 36 Figure 28 provides and example of the graphs that are produced with the figure showing th
33. ain menu and left click the mouse A hyperlink will take the user directly to the appropriate part of the model as shown in Figure 11 Microsoft Excel ASAP HIV 4105 Costing Model v1 2 xls du m View Insert Format Tools Data Window Help Type a question for help 5 8 X 7 21 AL 0 00 58 30 E a BL 2 Make sure you add rows above the red line only 3 If there are only 2 levels use only the Priority activity Columns B and D 4 Select appropriate function from drop down menu illumn H 5 Select Section from drop down menu in Column I andard Activity indicates pre programmed set of costs 6 If there is more than one activity within a function tyelect Additional using sequencing from 1 to 5 7 Goto appropriate costing profile by selecting F5 en costing profile name from the drop down list 8 For subsequent simulation purposes select wheth include this ns in the overall strategy Column E aua S Priority Priority Strategy Function F Costing Profile Group vartan io GoTo Cost 2008 Cost 2009 13 lr leet EN T d n m D NNNM UL ct nba I 1 6 C jJ C 1 03 Voluntary counseling 7 Tf EE upaya 1 05 Youth in school lu ESN prog Mo gt vi EN B Basic Data Targets and Coverage Chart of Accounts X Strategic Plan Map gt Ready NUM 4 Start iv ix o uo M Welcom s V
34. d ATH py x I pI mim wa ma eremm 5 0 ed ooot suum ares iq ap rF9 uone ndod o euroq rF9 uone ndod o e A uone ndod o euro q uone ndod o e A uoneyndod 930 S10 BIIPUI orqde souroqq xxx 14 Hat ULI suumn oo p lold poarsop SI ejep VILIOJSIY JO SILA ou WHOA suonoofoad 5 JI SAOJPVIIPUT AUB 104 9JON UI SANOY An npolq Ivo YAOM Ul SAEP oAponpoaq A u Lin IEUODEN LIND Joy Jod TEUODEN uonoof o d 10 JILA 3 1875 EI asa uo LUIN 0 ejeg 2196 918 2158 SIS Indu ejeq pg xouuy tr 1oquinu souoje1oqe 19quinu sr yu q qumnu s e3idsoH 1oquinu s1ox1oA YLA 10YO 1oquinu SOSINN q qurnu suerors qq S10 VIIPUT s224nosos uy e H amp repuooos p UPO ALN amp reurd 1 9 UPI ALN INNY UI JoN 19030 Yo oouo eAoJd ATH UdWIOM 3ueusoJq 94 n rgA nd ATH HNPY S10 BIIPUI VIISO OIWOpIdy sjuouro ddng YIM p T AOD S Jo 04 oreurojduiAs ATA N peusunougeul UdIPTIYO Jo 9 4 sjuouio ddng YIM p T AO2 S Q Jo 04 sy npe oreuirojduiAs AMN peusunougeui synpy Jo sjuouio ddng Teuoninnv YIM p9J9A0 2594 Jo uone ndod ATH ornerpoegq pousunougeuir uoJp rqgo Jo sjuouio ddng
35. e costs by year and function Microsoft Excel ASAP HIV AIDS Strategy Costing Model v1GF xls Beles a File View Insert Format Tools Data Window Help Type a question for help e 5 amp 8 X LES 2l AU 9 95 e EI Graph Function PR Ha r kre rrr r mmN Expenditures by Function 12 000 11 500 11 000 Program Dev O OVC il Care Treat G Prevention 10 500 10 000 9 500 4 74 c ON Ooo n cni 07 07 On On cn On OD ee Ei Ei Ei ERU ER Pi Es to CO ul M CA CO ho LO 00 4 07 CO Bo 2 O CO 1 n CO Bo CH O CO 2007 2006 2009 2010 2011 v SE SNE SR y OCP VEEL MA DIANNE da PTET VN V SISOS XOCA DAR T AT ECPI MT R SER I ECR D ROS SIT j Cost Summary by Priority Cost Summary by Activity Cost Summary by Fur Ready NUM 2 start aa ii CH Dominic S Ha 113 Microsoft ME sc Micro Figure 28 Example of Expenditure by Func on Graph Once the relevant reports or graphs have been reviewed and or printed the user can left click on the box entitled Report Menu to return to access another report O Global Fund Module The last function of the model to be reviewed is the module for converting the results to a format suitable for Global Fund proposals using the Round 8 templates This part of the model begins by left clicking on the box entitled Produce Global Fund Formats which 1s located at the fa
36. eeded Also in order to allow the presentation of the model results in a format familiar to government decision makers this section also allows the individual accounts to be mapped to the government chart of accounts Enter Unit Costs this is where the standard unit costs are added for each expenditure account which will be used throughout the model This ensures consistency in the costing and allows physical quantities to be calculated later in the process to assist in procurement planning Map Strategic Plan to HIV AIDS Functions in this part of the model the various strategies and activities are mapped to standard HIV AIDS functions using the classification system from the National AIDS Spending Assessment NASA tool This mapping allows the relatively free form strategy to be explicitly linked to a number of related interventions Complete Costing for Standard Additional Interventions to the extent that targets and coverage levels as well as standard unit costs have been entered already the costs of the standard pre programmed interventions should already be calculated This part of the model would then be reviewed to ensure that all of the appropriate costs are included and that the figures make sense Special interventions are more free form with the user being able to select the relevant target populations or quantities as well as the appropriate cost categories Complete Financing Gap Analysis optiona
37. eling i 3 PMTCT E E 2 i Post exposure prophylaxis PEP E STI diagnosis and treatment R gt 5 Blood safety and universal precaution 7 z R R Antiretroviral treatment ARV and monitoring Prophylaxis and treatment for opportunistic infections 2 5 2 3 Care and support for the chronically ill 661 682 703 725 748 3 519 a Support for orphans and vulnerable children 3 TB HIV B 3 Policy development including workplace policy R Strengihening of civil society and institutional capacity building x Stigma reduction in all settings R HSS Service delivery R R HSS Health Workforce 2 H38 Medical Products vaccines and technology d 4 A HSS Financing 6 E HS5 Leadership and Governance R e i R R HSS Information system e e 8 Programme management and Administration cost Total uu 10927 11019 11272 11454 11566 56238 9 4 gt h Global Fund Reports 4 Function List CofA Sorted lt sil Ready em e E P t E S Dominic 5 Ha EH S Biz Microsoft EN Ajr LR KSE El Microsoft Excel ASAP HIV AIDS Strategy Costing Model v1GF xls DER s S E Yew d Tuna Joos Data Window ep Type a question for help m X ii k zi y AN k d EE J r ir tu GLOBAL
38. emplate also be added to the Total Table It is also important not to repeat costs types in both the top and bottom part of the template since they will be double counted 29 L Financing Gap Analysis optional Another enhancement that was brought up during the preparation of the model and further inspired by the recent update of the UNDP Framevvork model is the inclusion of the capability to do financing gap analysis In this model this process follows two separate steps 1 entering known information on the financing plans of the Government the Global Fund and other HIV AIDS financiers and 11 comparing the financing information to the requirements generated by the model to determine the financing gap The first part of the process is accessed by clicking on the Complete Financing Gap Analysis optional box on the main menu This will take the user to the screen shown in Figure 19 The module is prefilled with the Global Fund and Government as financiers For each financier the name of the financier if not pre filled and the currency code of the funds to be provided by that financier should be entered in the green list of financiers starting in cell C61 Then the exchange rate relative to the Reference Currency specified in the Basic Data section and the scaling factor to be used in reporting the currency from that financier should be entered The scale for the reference currency will be taken from the Basic Da
39. en the cursor is placed on the cell a small white box with a downward pointing arrow shows up next to the bottom right hand corner If the user left clicks on this white box the drop down list will appear The user can then scroll down to the appropriate function and presses Enter to select that function It may be that an individual activities described in the strategic plan encompasses more than one function In this case an extra row should be added and the activity line then copied as many times as needed The various functions included in this activity can then be selected as described above Finally there is a possibility that multiple activities may be mapped to a single HIV AIDS function In this case there is the possibility to reflect this in the model specification One of the activities should be designated as the Standard Activity which would be what someone would think of if someone mentioned ARV treatment or OVC support If this is still unclear the user may want to sneak a peek at the Activity Costing worksheet to see what is included in the standard activity for a particular function Thus one of the related activities should be designated as standard in Column T again using a drop down menu as described above while the others would be designated as Additional Up to five additional activities have been programmed into the manual The main difference is that the 19 standard acti
40. en the user has printed or reviewed the data it is possible to move back to the main menu by positioning the cursor over the Menu box and left clicking the mouse EJ Microsoft Excel ASAP HIV AIDS Costing Model v1 2 xls Bd File Edit View Insert Format Tools Data Window Help Type a question for help 2 X e 00 x Se 9 Go o 25 1 Select type of report required 2 Return to Menu when finished Assumptions tal Cost Summary Reporting Graphs Cast Summary by Priority Cost Summary t gt v fix I Dr Dominic 16 18 G User Ma ASAPH Figure 23 Reporting Main Menu Examples of the function Figure 24 and expenditure type Figure 25 reports are presented below The reports by activity and government classification would be generated once the corresponding information has been entered into the system Figure 26 presents an example of the physical quantities report which presents the number of units of each type of input Because the various accounts are used throughout the model this report 1s calculated by dividing the total cost by account type by the corresponding unit cost Accordingly the numbers will make sense only to the extent that alternative unit costs have not been extensively used 34 To vvarn the user to check carefully about the use of alternative unit costs the report indicates whether they are used at all in the model for that particular accoun
41. etail in the human resources section of the chart of accounts the cost and human resource implications of various levels of task shifting can also be analyzed Because of the mapping of expenditure types to the government accounting framework the model can then be used as a budgeting and financial monitoring tool which would allow users to examine price quantity or pattern of practice variances This monitoring and analysis can serve as an input for costing future strategies or adjusting the cost projections in the middle years of a strategy In this way the complete cycle of planning budgeting operations and evaluation is supported The model is an Excel spreadsheet which is largely menu driven The user simply has to click on the box to go to the appropriate part of the spreadsheet All data are dynamically linked so information only needs to be entered once and will be used throughout the spreadsheet B Basic Functionality Figure 1 shows the flow of the model through the various functions contained in it In summary the following functions are included Entering Basic Data this includes country and currency data scaling options for reports and the initial year for the projection It also includes space to enter demographic information and data on the various at risk populations with respect to HIV AIDS as well as data on health resources both human and physical As noted above this data will be fed as needed to other
42. etail in the text below some of these steps can be done in parallel The following table highlights the expected types of staff who should be involved in each step of the model 6 Costing Standard Interventions Financial Program Management Costing Special Interventions Funding Gap Analysis Management Financial 6 Reporting Financial Program The next sections of the user s manual describe how to enter the system and then go through each of the steps in turn highlighting the information needs as well as the key features of the model C Starting the System The system is configured as a normal Excel file although because of its initial size will likely be distributed in a zm file By double clicking on this file the contents of the zip file will be shown To start the system the user should then double click on the Excel file in the list A dialogue box similar to the one shown to the right should pop up The user should click on Enable Macros circled in red fer EET Gre Ae The title page should now be displayed see Figure 3 and once the button Press here to go to main menu the main menu will appear see Figure 1 and the system is now ready for use E Microsoft Excel ASAP HIV AIDS Costing Model v1 2 xls Baik nse Format Tot ata Type a question For help s _ 8 x D 125 er Am qm m J ye A uu s l EE JOINT UNITED NATIONS PROGRAMME ON
43. ew Insert Format Tools Data Window Help Type a question for help 8 x AR fe 2 2 21 1 Yo io 20 E REES 1 Basic Data Instructions Enter data in green areas only 1 Enter data in areas with green highlight can cut paste from RNM 2 Examine growth rate for each line to make sure it makes sense 3 If projections do not make sense enter appropriate growth rate To Print Select lt File gt lt Print gt then select printer and select lt OK gt Print area is set and paper is selected for A4 Basic Data EE HINT Because there are a large number of individual activities Start year forprojec on and cost elements a large scaling factor may be needed for National currency S s 5 e reporting but this may result in fewer significant digits than Scale for currency e NR KR desired at the individual activity level that If this happens it Reference Currency USD EUR gt may be useful to set the scaling factor low for the costing but National currency per EUR 5 e increase for the reporting This can be simply done by Scale Se changing the scaling factor on this sheet Productive days in work year ging 9 Productive hours in vorkday AHistrial 418 Projected Demographic indicators 2005 2006 2007 2008 2009 2010 2011 20121 Growth rate Total population H population 15 49 Female population 15 49 Male pop
44. expenditure areas within the budget The head and sub head data 1s essentially free form so it can accommodate numbers letter names or combinations thereof When filling out this information it is possible to copy head and sub head information as required to reduce the data entry workload Although there are expected benefits from using this feature such as presenting the strategic plan costs in a format familiar to the Ministry of Finance the model will function without having this data filled 1n Once all of the accounts and the mapping to the government chart of accounts have been completed it 1s important to click on the mauve button labeled Done in position F1 This initiates a macro that sorts the revised data to make it useable for other parts of the model This macro takes the user to another worksheet in the model so the user must get back to the main menu by positioning the cursor over the Menu box on that sheet and then left clicking the mouse to invoke the hyperlink G Enter Unit Costs The standard unit costs and the chart of accounts use the same sheet but focus on different parts of the sheet Instructions are included in the green boxes at the top To go to this section position the cursor over the Enter Unit Costs box on the main menu and left click the mouse A hyperlink will take the user directly to the appropriate part of the model 14 E Microsoft Excel ASAP HIV AIDS Strate
45. fr Fill in green sections with number Do not add rows to standard activit To add new set of activities add ro Then copy previous table and incr To use training template click on h 1 2 3 4 Then copy previous row and change expenditure type as needed Costs are automatically carried forward to strategic plan summa Costs with fewer than two significant digits are shaded as on specific activity to qo to that area k 11 Select Activity from List below expand group to get details and clic Prevention Ireatment and care Orphans and vulnerahle children AIDS program development Human resources Social mitigation Community development and enhanced environment HIV and AIDS related research LS qp Kei 104 Prevention Activities 105 Mass media q b s 7 d Mass media Unit Cost Alt Unit Unit Cost Number of Units 107 National Campaigns EUR E 202 See 108 The of Billboard production 1109 units are Billboard rental calculated 1 Media Caste Televisinn Targets and Coverage Chart of Accounts 4 Strategic Plan 275 Costing Tral ni LAE 4 b M Ready E w E3 9 P Welco Th User M Micros fi C 6Doc n mumu w Q 11 43 Figure 13 Beginning Standard ntervention Costing from Main Menu Here the user will see the various function groups from NASA which can be e
46. ghlighted and printed for further review M Review Total Costs The cost summary sheet provides an overview of costs by the priority areas of the national strategy this will be populated after the strategy has been entered by function and by expenditure type All costs are presented in local currency units and United States Dollars using the scaling factors specified in the Basic Data sheet The system is currently set up to provide a four year projection starting from the year after the base year specified in the Basic Data sheet To go to this section the user should position the cursor over the Review Total Costs box on the main menu and left click the mouse A hyperlink will take the user directly to the appropriate part of the model When the user has printed or reviewed the data it is possible to move back to the main menu by positioning the cursor over the Menu box and left clicking the mouse EJ Microsoft Excel ASAP Costing Template Demo v3 xls tea File Edit View Insert Format Tools Data Window Help Type a question for help e 2 8 x 0 00 00 0 Cost Summary X fe Cost Summary E UT t Y t m TT s K L M N 0 14 1 Check and or print as needed Cost Summary 2 Return to Menu when finished p x R 23 x 1 000 000 x 1 000 Cost Bunmary Total Costs by Priority Total Costs by Activity Total Costs by Function 19 1 Prevention 2 162 1 790 2 227 1 844 022 30 450 25 20
47. gy Costing Model v2GF xls 21 Ele Edit View Insert Format Tools Data Window Help 53 2 d la 2d ERM AR SR SR ail gt FOT L _Chort of Accounts 1 Fillin Green Cells only with relevant government account information 2 Make sure that all necessary types of costs are included 3 Add accounts under relevant accounting group include units and product codes 4 When finished click the Done button and then the Menu button Product sis b e bb e o P ta sa o za ra ta ra to ra ra ta ra t ita ra ra ra ra ira co Halt jo 1 ADI Administrative Staff A02 Clerical and office support Staff Contracted Employees ATEN to I Other Technical and Craft Skilled Staff Semi skilled Operatives and unskilled Staff enior Technical Staff hysician Un sek AD6 Med A06 Med A06 Med AD6 Med A06 Med AD6 Med A06 Med A06 Med A7 egistered Nurse Enrolled Nurse linical Officer ublic Health Officer aboratory Technician ther Technician ther Medical Staff ternational Consultants ECHT B 25 16 Local Consultants higher ry 26 A09 27 18 Ald Community Outreach Workers 281 19 Temporary Employees 0000 0 Medical supplies and drugs B l 30 Medical supplies and drugs Other SMI syringes additional cost x h WEW Basic Data Targets and Coverage X Chart of Accounts Strategic Plan
48. he Mapping Section 22 The other option is to access the costing from the strategy mapping section accessing the related costing module for each activity in sequence To do this the user should go to the particular activity that they wish to cost and move the cursor to Column J of the appropriate line Then the user should press lt F5 gt which will cause a pop up menu to appear The user would then scroll down the list until the name is the same as the one in Column J and then click on lt OK gt Figure 15 provides an example of this The cursor is positioned at cell J14 which reads Costing AIDS Program When lt F5 gt is pressed the pop up box appears as shown in white in the figure Once the user scrolls down to the appropriate spot and clicks OK the hyperlink takes the user directly to the Program Management costing part of the model When the user 1s finished the box Back to Plan is clicked which returns the user to the mapping section of the model As noted above if there are several activities which map into a single Standard Activity e g ARV therapy for adults and for children the user should map all of them to that activity 2 03 Antiretroviral Therapy in this example and then set the Include flag column E to N for all but the first of those activities See rows 14 17 in Figure 10 This is critical to ensure that the same costs are not included multiple times in the
49. ise targets if units are not correct press Menu Enter Targets drop down list ue y i Fill in green sections and check costs for reasonableness units by year To change expenditure types select from drop down pick list used another additional activity instead To add rows position cursor in Column A select lt Insert gt lt Row gt Then copy previous row and change expenditure type as needed 5 to create sufficient room Costs are automatically carried forward to strategic plan summa se the number in Column B by one Cost d v 4 E x J Cn Cn CO it CO t tu CO 4 oon mr me ee Administrative Staff Clerical and office support Staff caf Contracted Employees Other Technical and Craft Skilled Staff Semi skilled Operatives and unskilled Staff Senior Technical Staff International Consultants Wa 420 40 Select cost types I CO 52 para I I I ioc Em eee h3 O o0 C0 4 0 wen ma ee GE 7n davis 1 acting IUIS M gt Mj Targets and Coverage Chart of Accounts Strategic Plan Map X Activity Costing Tc lt Ready NUM 2 start E Pi E m WIEN x uran aa Dominic Xa User Ma Figure 17 a Costing Special Interventions 26 Some of the special interventions sections are pre programmed target popula
50. it cost for staff 1s per staff year it 1s important that the quantities entered 1n subsequent parts of the model are also expressed in staff years The units variable provides a reminder of this necessary linkage during the costing process and it is shown next to the account name on each line on the activity costing part of the model It is also important when adding accounts that all of the data be kept above the red line that reads Insert Rows only above this line To accommodate different numbers of accounts this part of the model 1s quite dynamic but all of the data must be entered between rows 11 and this line to be captured in other parts of the model To delete an account the appropriate row of the spreadsheet 1s highlighted and the commands edit delete are invoked from the main Excel menu line However there is no particular problem if superfluous accounts are left in this part of the model so It 1s not critical to delete all non used accounts The second activity within this section 1s the mapping of the final chart of accounts into the government chart of accounts framework For each account the expenditure head and sub head are entered into the appropriate space beside the account While the government accounting framework may extend one or more levels below this it 1s expected that the two levels should provide a sufficient level of detail to allow an informed analysis of the impact of the costed strategy on different
51. ivities is largely driven through the targets and coverage data as well as protocols that are programmed into the system Within the protocol there is considerable room for incorporating patterns of practice variations These activities also draw on the unit cost information that has been entered earlier For many of the activities the combination of the targets and coverage data quantities and the unit costs are sufficient to actually calculate a cost in others there is a need for additional data such as the specific tests to be used or the quantities of a particular input for each person who 1s to receive the intervention This step of the process will be to fill in the missing information and check the costing to ensure it is reasonable and consistent There are two ways of going to this section of the model If the user just wants to start at the beginning of the costing modules and work through sequentially the user should position the cursor over the Review Costing for Standard Interventions box on the main menu and left click the mouse A hyperlink will take the user directly to the beginning of this section of the model see Figure 13 20 El Microsoft Excel ASAP HIV AIDS Costing Model v1 2 xls gt x ted Ele Edit View Insert Format Tools Data Window Help Type a question for hep iz m x with Changes End Review K L M N _ Special Interventions Select desired expenditure types
52. k a m A 2 Mer gt Ell asapc H Microsof V 11 42 AM Figure 7 Variation on Entry of Coverage Information Figure 7 shows a slightly different type of data entry requirement Three of the 21 sections are shown in this screen shot while discrete on the menu they follow sequentially on the actual spreadsheet Here there is a need to enter both the actual numbers sex workers MSMs IDUS since these are not already captured in the Basic Data section as well as the coverage levels for various interventions Further the estimated number of condoms per sex worker should also be entered since this could vary from country to country Similar types of requirements are found in different parts of this section Again there may be some value in printing out the blank section prior to collecting the data and then once the data has been entered and the target values have been calculated sharing the completed section with stakeholders to ensure that the numbers targets and coverage levels are consistent with both the best available information and the actual strategic plan By doing this ground work up front the amount of work to be done during the actual costing process can be reduced Further such a process should ensure greater buy in from various stakeholders and allow the costing team to concentrate on issues of unit costs and numbers of various types of inputs required for each target beneficiary As in the previous section t
53. l this part of the model allows the user to enter expected sources of funds by financter broken dovvn by functional group function to the extent known This can then be compared to the funding requirements implied by the costing to determine the funding gap To the extent that funds are allocated by financiers to specific groups or functions the funding gap can be reviewed at a lower level of detail as well Review Total Costs this section provides summaries of total costs across several dimensions a by priority activity b by function according to the NASA classification and c by broad cost category This allows the user to determine if the overall costs as well as the distribution of costs within the total look reasonable Reporting the final section of the model provides detailed breakdowns of costs by activity grouping individual function and cost element There are also reports which detail the costs by government expenditure classification and show the physical quantities of various types of inputs implied by the costing This can be useful both for procurement planning and in physical or human resource capacity analysis There is also a separate module for reorganizing costs and activities in the Global Fund framework which will be discussed below To work through these steps various types of staff from the MOH HIV AIDS and or other stakeholders would need to be involved in the process As noted in more d
54. lude flag set to Y while the flag for all other training activities covered through the training module should be set to N to avoid double multiple counting of the costs The module can be initiated from either the Strategic Plan Map see Figure 11 or the Activity Costing see Figure 13 sheets by clicking on the button titled Go to Training The former of these is probably easiest since the first step in the process with be to enter the activity description which can easily be copied from the Strategic Plan Map and pasted into the relevant training template The result of clicking on the button is shown in Figure 18 E Microsoft Excel ASAP HIV AIDS Costing Model v1 2 xls du File Edit View Insert Format Tools Data Window Help Type a question for help 2 amp 8 X AD 100 with Changes z d zi Ei De Ae 1 Y Reply End Review En the name of the training activity from activity matrix Can just cut and paste from Strategic Plan Map Page Go down to empty template and fill in number of people to be trained Enter number of units for each trainee as appropriate Add costs types and number of units not directly related to trainees Can select other cost types as appropriate Should also add cost type to total table to ensure totals are brought forward Cost J Number of Unit 2007 2008 20
55. munity Outreach Workers 2 L Year 2008 Year 2009 Year 2010 Year 2011 dc 2008 to to to to km A KH reca C D Asa feo A Staffing lntemational Consultants Nome A Staffing Laboratory Technician Nome A Stafing LocaConsultants Nee A Staffing Local Facilitators Nome 1 A Staffing j _ A Staffing Other Technical and Craft Skilled Staf Nwe A Staing Physician Nome A Staffing egisteredNussee LEE TO Semi skilled Operatives and unskilled Staff lt NN 4 h M A Basic Data 4 Targets and Coverage X Chart of Accounts Strategic Plan Je Tm Ready NUM start SE x e CH Fe Welcom CA User Ma Microso C Docu EN B Mes m 9 41 Figure 10 Specifying Inflation Factors There are three inflation options available for each account None the default value and results in no inflation being calculated in other parts of the model General this would use the general inflation rate which the user would specify in Line 8 Columns L to O of this sheet Or the user has the option of specifying the initial value and having it carried forward to other years if constant
56. nction group or by NASA function to the extent that the distribution is known This information is useful in helping to identify the key gap areas in the current plan The line under the total line is indicates the total amount of financing that 1s not allocated to functions or function groups For the function groups the user can specify one of two types of allocations by using the drop down list in column C a allocation as a percent of the total cost for that year or b allocation as an absolute value in the currency indicated As in other parts of the model the value entered in Year 1 is carried forward to subsequent years as a default although the user can enter their own values for any year The amount specified 1s calculated in the reference currency and the amount unallocated to specific functions is also shown in the line below each function group The next section allows the user to provide further detail by specifying the amounts to be allocated to particular functions Here the appropriate NASA function 1s selected from the drop down list referenced in column B and an allocation method 1s 31 specified in column C In addition to a allocation as a percent of the total cost for that year or b allocation as an absolute value in the currency indicated this section also allows c an allocation as a percent of the total cost for the relevant function group In order to use this last method it 1s important to ensure that some
57. npe Jo 9UIj 9 PUEL sui osunoo 1ejun oA E N un se i pue Huljasunog 3 us av suonoofur ao Jo 1equinu ur 04 S SUHAS TV ur pooe do suoroofur yesun yesun oie yey suon lul Jo 04 T d uosiod r d suonoofur 3 npe Jo joquinN 959J9A00 UOTjeZIUnUTUI uon lur eorpour yes E N ur puq ouip oseq o SI qad 96 uone ndod uoi rur r d p rnb syy 444 sixe udo d oansodxo 3soq I N ouip oseq pois uoisngsutJ 10 poo q Jo spun Jo 4 000 1 s d sun poo q 5 poo g 0c 0c 0c 0c 0c N ul puq oui oseg juouneoJ 80111909 11 YM So euio 04 yu unp n 80111999 LS YIM SIVU oreurojduij s 1801 SLS JO s jew oreuirojdui s o1 jeu SILS JO 95 oouoprout ypur r S S o qt1e9 JO soseo Aou Jo 1oquinN 9ouoprour JIBU SLLS 9190218917 yo S SLI MOU Jo JOqUINN juouioseueul LS E N E N E N E N N E N ul puq oui oseg SUOIJUSAI9 U Suu s S YESH p 3eeus Di UOISIOUIDJIO UT QO o 62 SIIBUW JO 95 pojeAouoJ pojonujsuoo q o SOL YLA 19010 dq o SILIOJLIOGE7 pojonujsuoo q o souojeJoqve aq 0 S197U2 p n nsuo q o s193u2 q e H SoAnuoout Ae uow ul uonedion ed Ye
58. o assist in this process Annex B 2 includes data input sheets which can be printed and circulated to the various program heads to obtain the appropriate information to be included in this part of the model When the user is finished entering data it is possible to move back to the main menu by clicking on the box entitled Go to top and then by positioning the cursor over the Menu box and left clicking the mouse 12 F Complete Chart of Accounts and Mapping The chart of accounts and the standard unit costs use the same sheet but focus on different parts of the sheet Instructions are included in the green boxes at the top To go to this section the user should position the cursor over the Complete Chart of Accounts and Mapping box on the main menu and left click the mouse A hyperlink will take the user directly to the appropriate part of the model It is strongly recommended that financial staff of the HIV AIDS agency and or Ministry of Health become involved in this part of the process which could take place in parallel with the data collection activities of the previous two sections There are two key activities that take place in this section Figure 8 First the chart of accounts is entered according to the needs of the particular country The sheet is pre filled with more than 200 different account categories accounts grouped into eight major classifications groups as follows Staffing Medical
59. od ATH HNPY IO YUM E N E N E N oui oseq E N E N u rpyq npuro dur4 A MON 9 988J9AoO stxejAydorId TO S npe onewozdwAs SET5AO sTxejAydord IO uone ndod ATH 9rnerpoegq 98e19A0 stxefAydorg TO uone ndod ATH 1npy 9991940 srxejAydolg TO uonooju onsiunjagoddQ oy sixe Audoaq E N E N E N N ur se i uone ndod IIB 49430 PUB 2184 juourneoa uon ju psrunm roddo AA 7 9v n T d 80011 111501 Aq pouoeor 0 T d suonuoAJ19jut AUTO q pouoeo 04 Jad suonuoA19jut 1 40 5 ouroq Aprum Aq poyova 0 T d suonuoAJ9jUl OIEI UE 21580 Aq PIYOLA 04 T d suonuoA19jut UOTyeONps Aq payoeal 04 q9 rou n A pue suvydio UdIPJIUD ejqeueu nA pue SUBUCIO Z 3eeus LV i j Sunsa parentur t9ptA01d ur poajoauy uone ndod jo 07 07 07 07 07 07 SUSI pojenrug un se i BINULIOF JULJUT 9AI9291 je uouroA jueusoJd a sod ATH Ya AV WIM pawon uouioA jueusoJd oAnrsod ATH ATISOd ATH 91 oym USWIOM D S JO ATH pose 24 o ONY Surpuogj uouioA jueusoJd Jo 04 ej Uu9ju ouios peu OYM UdUIOM JO UOISSIUISUEI D IQ2 03 191 jOUI JO UOINUIAIAG E N ur se i 98219409 aq o SI YOIYM poou LOA JO K enuue LOA Surpoou uone ndod 3
60. on of the model it should be possible to move more quickly through the various steps making adjustments as necessary to reflect local conditions Once one activity is finished the user can then either scroll down to the next section or click on Back to Plan to return to the strategic plan mapping section of the model The next activity of interest can then be selected as indicated in the description of Figure 15 J Review Costing for Special Interventions The special interventions are more free form and the user can select the types of costs using drop down lists and the number of units to be included There is ample space for documenting the costing process in each intervention The special interventions can be accessed the same way as the standard interventions either by scrolling down or by using the hyperlink to go directly to that section of the model The hyperlink will direct the user to the beginning of that section which will always be the standard activity so the user will have to scroll down to the appropriate group of special interventions Microsoft Excel ASAP Costing Template User Manual xls Beles SEI Edit View Insert Format Tools Data Window Help Type a question for help 5 amp 8 x 3 Ad 42 100 sj e fe Reply with Changes End Review m0 Z U E E S 5 9 9 For standard activity check units imported from targets section Rev
61. ones will be included for costing It may be that subsequent to the development of the strategy there has been a separate prioritization exercise to determine the areas of greatest importance These can be flagged by setting the indicator in Column E of this section to either Y yes include in the costing or N no don t include This option also allows the user to adjust the activities to be included as the costing 1s done if for example there are indications that the funding available will not be sufficient to cover all of the desired activities Simply by changing the flag from Y to N the effect of removing various activities from the overall plan can be evaluated 18 E Microsoft Excel ASAP HIV AIDS Strategy Costing Model v5GF Case Study xls PEE File Edit View Insert Format Tools Data Window Help Type a question For help 5 B x lt 0 ET E ge Lii a 112 X fe Section A B Priority Strategy uate 121 1 1 Increase number 11 Eu Expand access to ARV 1 1 Patients recerve first line of adults on ART treatment to scale up the therapy to 3 000 by 2012 response 14 1 Increase number 11 1 Expand access to ARV 1 1 2 Patients recerve first line R of adults on ART treatment to scale up the therapy and TB therapy 2 Tr satme to 3 000 by 2012 response and care 15 1 Increase number 11 1 Expand access to ARV 1 1 3 Patients recefve second line I
62. ows position cursor in Column A select Insert Row Then copy previous row and change expenditure type as needed 5 to create sufficient room Costs are automatically carried forward to strategic plan summa se the number in Column B by one drop down list units by year ised another additional activity instead a Cost National Currency KSH x 1 000 000 3 2007 2008 2009 2010 Total 2 60 60 60 l 2391 enani Physician visit examination and medicines B PMTCT test kts consumables other HIV Positive women treated with ARV Drugs Drugs NVP Intrapatum dose dos 813 PMTCT component of antenatal visit pervist 814 Physician visit examination and medicines nervisit H M 4 gt M Targets and Coverage Chart of Accounts Z Strategc Plan Map Activity Costing Tc Figure 16 c Checking Resulting Cost Calculations 25 8 Checking Resulting Cost Calculations the final step in the process 1s to check the results to see if they are reasonable All costs are expressed both in local currency and USD using the exchange rates and scaling factors indicated in the Basic Data section If the scaling factor does not produce enough significant digits it can be changed by simply going back to the Basic Data section of the model At first this process may take some time but as the user team gets more familiar with the operati
63. parts of the spreadsheet Entering Targets and Coverage Levels this is where the objectives from the strategic plan are quantified into specific targets and coverage levels for various groups and subgroups and various interventions This section takes the necessary data from the Basic Data sheet and also feeds the targets and coverage levels into the subsequent calculations E3 Microsoft Excel ASAP HIV AIDS Costing Model v1 2 xls iB Eile Edit view Insert Format Tools Data Window Help Type a question for help amp 5 8 X 52 Z el l ig elt El 2 A 0 00 00 Q Enter Tar gets and Coverage Levels Complete Char t of Complete Financing Accounts ond Analysis Map pin optiona nal SE r DEE a en Enter Unit Costs Review Total Costs Map Str ate jic Plan de HIV AIDS Reporting Functions 17 x gt n WEB Menu Basic Data Z Targets and Coverage Chart of Accounts 4 Strategic Plan Me gt Ready NUM start 1 o DIr Welcome IB Ch User Manual f Elli ASAP HIV AID EN 42 VY Ki Figure 1 Main Menu Complete Chart of Accounts and Mapping in order to maintain consistency through the calculations in the model a single chart of accounts and standard unit costs are used The model assists the process by including a fairly comprehensive set of accounts which can be added to or modified as n
64. questions are raised about the unit costs used and also update the appropriate costs when for example additional procurement processes are completed which result in cost changes It also allows the user to specify for example the wage level classification step used in cost calculations whether employee benefits are included and how much etc While this will take a bit more time than simply adding the unit costs the time will be well spent once the inevitable questions arise about the source and determination of the unit costs There is also a report in the reporting section to print out these assumptions if needed An additional element included in this section is the entry of inflation factors which is also optional This part of the model is shown in Figure 10 below E Microsoft Excel ASAP HIV AIDS Costing Model v1 2 xls AER File Edit view Insert Format Tools Data Window Help Type a question for help 5 X Fill in Green Cells only with am government um Make sure that all necessary types of costs are included Add accounts under relevant accounting group include units and account name Place cursor in Col A of line where account needed and press button s When finished click the Done button and then the Menu button MEE Rate Specify nflation to daaa 0 Use Administrative Staff Staff None Clerical and office support Staff c linical Officer p om
65. r right of the main menu see Figure 1 This takes the user to the first of two sheets that comprise the Global Fund Module as shown in Figure 21 The first section of this sheet directs the user to perform or confirm the mapping of function in the model per the NASA classification to the Service Delivery Areas SDA s used by the Global Fund This starts on line 6 of this sheet The mapping has been pre filled to make this process easier although it 1s possible for the user to modify this as well Drop down menus have been included with all of the valid SDA s based on the Round 8 forms 27 dr Microsoft Excel ASAP HIV AIDS Strategy Costing Model v1GF xls EL i Ele gdt ie Inset Format Tools Data Window Hep Type a question for EZ lal ld 13 H X Gs SA Z 0 0 5 5823 3E SELLE 1 Times New Roman v 10 Cs 1 Do check Mapping of Functions to SDA s 2 Do check mapping of expenditure items to GF cost categories especially if accounts have been added button at right 3 Generate reports button at right Mapping of Functions to Service Delivery Areas 1 Prevention 1 01 Mass media 1 02 Community mobilization 1 05 Youth in school Blood safety and universal precaution Mi Condom PMTCT Blood safety and universal precaution Post exposure prophylaxis PEF 1 O 1 20 Safe medical injections 22
66. red should represent actual unit costs for those types of activities in the country for which the costing is being done By disaggregating the individual types of costs within a particular activity it is expected that there will be a greater availability of the more granular unit costs and less need to rely on aggregates or benchmark costs from other countries There is also space for documenting the assumptions made in developing the unit costs so that they can be printed and kept for future reference It should be noted that while standard unit costs are specified in this section it 1s possible to assign alternative unit costs for a particular account during the actual costing at the activity level when the stated unit cost is not considered appropriate However this should be done as infrequently as possible since it affects the user s ability to utilize the physical quantities reports Where there is more than an occasional use of alternative unit costs for a particular account it is recommended that a separate account with a specific unit cost be created 15 In addition to entering the unit costs themselves it is also very important to document the assumptions and data sources used to determine these unit costs and enter this information in column J of this sheet For example the data may come from the MOH procurement system or recent Global Fund proposal or other sources This allows the user to go back later 1f
67. section by clicking on the box entitled Go to top A different menu choice can then be selected and the process can be repeated 11 Microsoft Excel ASAP Costing Template Demo v9 xis Read Only AAA Bd File Edit View Insert Format Tools Data Window Help Type a question For help 2 amp 8 x OG l D 3 a Hl 2 Oo Z LA o x li 10 B 7 U 5 5 S us azi H As D67 NT D64 D65 D62 D63 A B C 5 E F G H J 58 B Sexvorkers 2006 2007 2008 2009 2010 59 Population size 33 000 000 33 511 658 34 031 250 60 ee ee eee 51 96 sex workers reached each year sss 09 09 09 096 62 lofw usingcondoms 09 o 0 095 63 female condoms O o 64 uu K e ie Men who have sex with men Population size INumberofMSMs 0 1 k 73 MSMs reached eachyew D o o x 74 ow usmgcondons O m Om 75 Condoms per MSM reached and using condoms D HE hed vile co ce E 1 a 2 kl 4 HA e e CN HA e e bei HA e e e ing drug 2009 2010 81 Population size 351165 Number ofIDUs 1 o 86 96 IDUs Community Outreach and Peer Education 87 96 IDUs Needle and Syringe Exchange IDUs receiving Drug Substituition 4 ji ced 096 096 096 v n NER Basic Data X Targets and Coverage Chart of Accounts 4 Strategic Plan Map lt Ready NUM start fix i
68. t see red oval in Figure 26 El Microsoft Excel ASAP Costing Template Demo v3 xls EL Window Help Ber Edt view Insert Format Tools Data Type a question For help B y with Changes End Review 1 Check and or print as needed 2 Return to Report Menu when finished x 1 000 000 ASAP Costing Model Total Costs by Function 1 Prevention 2 162 1 790 2 227 1 844 8 022 2 Treatment and care 5 i 2 3 Orphans and vulnerable children e e e 4 AIDS program development gt 5 Human resources 5 amp 6 Social mitigation t i T 7 Community development and enhanced environm 6 HIV and AIDS related research e e Grand Total 2 162 1 790 2 227 1 844 8 022 1 Prevention 2 162 1 790 2 227 1 844 8 022 1 01 Mass media 1 539 1 208 1 637 1 246 5 680 1 02 Community mobilization 1 03 Voluntary counseling and testing 573 381 590 598 2 342 1 04 Youth in school 1 05 Youth out of school 1 06 Programs focused on sex workers and their clients 1 07 Programs focused on MSM 1 08 Harm reduction programs for IDUs 1 09 Workplace Activities 29 1 10 Prevention pro M M Reporting Ready start for people lying with HIV Cost Summary by Activity Y Cost Summary by Function r EDEEnS e CA Document E3 Microsoft Excel ASAP Costing Template Demo v3 xls Tools Data Window Help EL
69. t to the bottom right hand corner a drop down list will appear The user can then scroll down to the appropriate account and press Enter to select that account This can be done to add as many additional accounts as necessary The user may need to add rows to the model to create sufficient space for all of expenditure types This can be done by just adding rows to the worksheet highlight the number of rows to be added and then click on Insert lt Rows gt on the Excel toolbar menu Then one of the existing rows is highlighted and the user copies this down through the resulting white space by clicking on Edit lt Copy gt and then highlighting the white space and clicking on Edit Paste on the Excel toolbar menu The result should be that all of the rows including those just added should look the same with the same shading etc The user can then proceed to select the appropriate accounts for each of these rows E Microsoft Excel ASAP Costing Template User Manual vis Ele Edt View Insert Format Tools Data Window Help Type question For help 202 id AZ XA Z 2 C Q XE Oh S For standard activity check units imported from targets section Revise targets if units are not correct press Menu Enter Targets Fill in green sections and check costs for reasonableness To change expenditure types select from drop down pick list To add r
70. ta section of the model since the reporting phase of the expenditure gap analysis will compare the available funding to the financing requirements as determined by the costing model which will be expressed using this scaling factor El Microsoft Excel ASAP HIV AIDS Costing Model v1 2 xls Ek iki B Ele Edit View Insert Format Tools Data Window Help Type a question for help 2 8 X 521 02 d l d 1 Fe x AL Ba BASEE Review 5 Arial 110 z B I UE x 9 3 3E E El 2 A Enter data in green areas only Enter financier information including currency code scale amp exchange rates Go to next available funding block and enter totals by year in indicated currency then allocation by Function group if desired and detailed allocation by Function 3 For amounts by function group can enter amounts or of the total pick option in Col C 4 For amounts by function can select actual of total or of group total option in Col C 5 Line under total function group shows unallocated balance it is OK to not allocate 100 6 T Instructions To add a funding block for a new financier click on the Add Financier button below Funding block will be added just under list of financiers Currency to Scale for Exchange use forthis this Rate units Scale List Financiers i per EUR Factor Global Fund M Mj Strategic Plan Map Activity Costing
71. tion For help hd 242 158 231 158 788 4 266 4 327 4 388 4 451 17 432 22 136 16 860 22 828 17 387 79 211 3 806 3 260 3915 3 971 15 553 30 450 25204 31 363 25966 112 983 242 158 231 158 788 105 63 105 63 336 53 32 4 32 158 34 63 34 63 294 NUM E Welcome t enditure Type k M xl EJ Microsoft Excel ASAP Costing Template User Manual xis IGI Tal He Edit View Insert Format Tools Data Window Help Type a question For help 0X 1735 1 Make sure summary by expenditure type is generated 2 Re size print area and print as needed 3 Return to Report Menu vvhen finished ASAP Costing Model Alt Unit Number of Units by Year Physical Quantities Units CestUsed 207 208 2009 2019 Total A Staffing Administrative Staff FTE N Clerical and office support Staff FTE N Contracted Employees FTE N Other Technical and Craft Skilled Staff FTE N Semi skilled Operatives and unskilled Staff FTE N Senior Technical Staff FTE N International Consultants person month N 5 3 5 3 16 Local Consultants higher level person mo 25 15 20 15 75 Local Consultants lower level person 2 80 60 80 60 280 Community Outreach Workers stipend montt Temporary Employees stipend month N B Medical AD syringes additional cost supplies and drugs each N Other SMI syringes additional cost each N Formula milk annual cost per person N Nutritional supplements annual cost per person N
72. tions to accommodate additional programs beyond the standard program that are directed at particular target groups Other blocks of additional activity allow the user to select from a number of potential target populations see Figure 17 b In either of these cases the procedure as described in 5 of the previous section should be used If no target populations are indicated the procedure described in 6 should be used In either case the checking of unit costs 4 above should be done and there may also be a need to select additional expenditure types 7 above The checking of the results 8 should obviously be done regardless Microsoft Excel ASAP Costing Template User Manual ls PIE SCH File Edit Viev Insert Format Tools Data VVindov Help Type a question for help 2 x Reply with Change Ae 5 J N 57 For standard activity check units imported from targets section Special Interventions E Revise targets if units are not correct press Menu Enter Targets 1 Select desired expenditure types from d Fill in green sections and check costs for reasonableness 2 Fill in green sections with number of un To change expenditure types select from drop down pick list 3 Do not add rows to standard activity use To add rows position cursor in Column A select Insert Row 6 Then copy previous row and change expenditure type as needed 4 To add new set of acti
73. ts not related to number of persons treated Number of Units 822 PMTCT medical equipmen 823 Vehicles Lk EA 24 PMTCT supplies other es 825 MM cost types uu v M gt Mj Targets and Coverage Chart of Accounts egic Plan Map gt 3 fix m D3M xur F ES Mic 4 Ado mi Figure 16 b Selection of Accounts and Number of Units 24 5 Enter the number of units per person treated as shown in Figure 16 b some of the pre programmed standard activities require the user to enter the number of units needed for each person to be treated This allows the impact of subsequent variations in the number of beneficiaries to be done without further changes to the costing part of the model the change can simply be made to the Targets and Coverage section 6 Enter the number of units for costs unrelated to the number of persons treated in addition there may be some costs program activity overhead etc which will not vary with the number of people treated The units required for these types of costs should also be entered into the model 7 Specify additional expenditure types and add units if there are types of expenditure required for the activity beyond what 1s programmed it 1s possible for the user to specify additional expenditure types By placing the cursor on the blank space Column and then left clicking on the small white box with a downward pointing arrow shows up nex
74. ul to simply print the page as a reference when looking at potential data sources Alternatively Annex B 1 includes a data input sheet which can be printed and used to collect the Basic Data Once collected the sheet can be used to enter the data into the model When the user is finished entering data it is possible to move back to the main menu by positioning the cursor over the Menu box and left clicking the mouse The areas that are shaded purple are calculated by the model based on a simple linear extrapolation of the three years of data entered in the green section using the average growth rate over those three years However if the user has more precise estimates of future trends these can be manually entered for the years that they are available If manual entry is used it may be useful to document this so that there 1s some record that the calculated values have been over written Once all of the data is entered to the satisfaction of the user there may be some value in printing and discussing the data with a broader audience to ensure that there is consensus on the basic information To ensure consistency throughout the model all changes in the basic data should be made in this section rather than for example in the targets and coverage section or the section where the specific interventions are costed Otherwise a situation may arise where the same variable has different values in different parts of the model which could
75. ulation 15 64 Female population 15 64 Crude birth rate births 1000 pop Children 6 13 primary Children 14 17 secondary Adult HIV population e Uu wi bul x lt gt n NEES Basic Data Z Targets and Coverage Chart of Accounts Strategic Wireless Network Connection isnot connected Wireless network unavailable 2 en Y I rrp z Sv rss 5 1 4 4 04 x 4 qT C 06202 gi C s s 5 Y J 500 imi a eg J Em EES EES ae ae ae ae Oe Eee i Ready start SE Ei Mo Welcome IB DN User Manual Figure 4 Basic Data Section ASAP HIV AL As shown in Figure 3 the top part of this section includes basic identification data as follows Country start year National currency Scale for currency Reference currency USD EUR National currency per reference currency Scale for reference currency Productive days in work year Productive hours in a work day the country for which this costing 1s being done this name will be used in all reports the last year of actual data after which estimates will be calculated the abbreviation of the national currency which will be used in subsequent sections as well as reports the scaling factor for national currency units thousands or millions
76. ure 14 a a total of 20 rows Then the user should highlight the entire block of the previous additional activity and copy it to the blank space starting 2 rows below the last row of the previous block 1 row 156 in Figure 14 a The final step 15 to increase the value of the number below the Return to Plan box e g cell B137 in Figure 14 a by one in the same place in the block of cells that has just been copied in order to differentiate this block from the previous additional activity As indicated 21 previously this process can be repeated as necessary so that a maximum of 5 additional activities are included in the model for that function Once one activity is finished the user can then either scroll down to the next section or click on Back to Plan to return to the strategic plan mapping section of the model The next activity of interest can then be selected as indicated in the description of Figure 15 K Special Functionality for Training optional Based on previous use of the model it was decided to add special optional functionality to deal with training This module uses a common template to collect all of the training related activities which can then be mapped to a particular umbrella activity in the strategy The user can decide to use this functionality or not but if it 1s used the HIV AIDS Training function 5 05 must be mapped to one particular activity in the strategy with the Inc
77. ust adding rows to the worksheet highlight the number of rows to be added and then click on lt Insert gt lt Rows gt on the Excel toolbar menu Then one of the existing lines is highlighted and the user copies this down through the resulting white space by clicking on lt Edit gt lt Copy gt and then highlighting the white space and clicking on lt Edit gt lt Paste gt on the Excel toolbar menu The result should be that all of the rows including those just added should look the same with the same shading etc It is important when adding space for activities that all of the data be kept above the red line that reads Insert Rows only above this line To accommodate varying numbers of activities the Swaziland strategic plan had 910 while the Guyana strategy had 192 and Kosovo had 178 this part of the model 1s quite dynamic but all of the data must be entered between rows 13 and this line to be captured in other parts of the model Once sufficient space Is created the list of activities can then be copied To ensure an appropriate roll up the corresponding Priority and Priority Strategy labels should be copied as well for each activity The result is as shown in Figure 12 This information can be copied directly from a Word document or assembled in another Excel file and copied into the model Once the activities in the strategy have been entered into the model the next step 1s to set the flags to determine which
78. ut patient care NEC 48 2 21 Treatment of Opportunistic Infections 49 2 29 In patient Care NEC 2 30 Patient Transport and Emergency Rescue 51 2 99 Treatment Other fel 1521 3 Orphans and vulnerable children zx M 4 M Chart of Accounts S Map XActivi i Training Fir J Gap Anz gt Ready NUM E w F o 9 D e Welco ml User M E Micros n CiiDoc at Type a question for help 8 x gt eda Activity indicates pre programmed set of costs Additional using sequencing from 1 to 5 osting profile name from the drop down list Costing AIDS Logistic 2 RUE Costing AIDS Monitoring de this activity in the overall strategy Column E Costing AIDS Operations Cost Nati Costing AIDS Prog Other Costing AIDS Proqram Costing AIDS Supervision Costing AIDS Surveillance Costing AIDS Training osting Alt Informal Providers I Costing ART 9 Reference GG 2 Dt Co IDS_ Costing AIDS Program a Program E Cost 2007 Cost 2008 UU and Coordinate the National relationships between HSDU i HIV AIDS Multi Sectoral NAPS and PCHA as well as Health Theme Group Partnershup Forum 1 1 Empower the PCHA 1 1 3 Review update TORs and Y i v Targets and Coverage 4 Chart of Accounts X Strategic Plan Map Je il gt Ready NUM E EE Ep a m Ty User Mer she Figure 15 Accessing Costing Bon t
79. vities add rows to Costs are automatically carried forward to ic plan summa Then copy previous table and increase 214 n Unit Cost Alt Unit Unit Cost Number of Units Per SECO A T USD 2007 2008 SL Sg E 8 F Turan ner a REESE K tss e 70 784 1 P NIN i 169 NINI KITI 3 2 S E b x xmniIn Select EEUU UN ee cost types eau from pick list NIM MMM NINI Co O KA YY Khoj S W O 4011 b jc LO 7 fs T R U SEQ ak les Cosa A 223205 4 M gt M Targets and Coverage Chart of Accounts ic Ready NUM WA start E w E amp aA Iz 3 Micr Microsof Dominic User Ma Figure 17 b Additional Activity with Selection of Target Population As indicated in the section entitled Map Strategic Plan to HIV AIDS Functions up to five blocks of additional activity can be accommodated Most functions in the costing section have only one or two blocks of additional activity included but the user can add to this using the procedure to be described next First the user should add a number of rows below the latest block of additional activity equal to the number of rows in that block plus two the user needs to be sure to include the header row shown as row 136 in Figure 14 a in the calculation of the number of rows in Fig
80. vities have been pre programmed in terms of costs to be included while the additional activities are more free form In practice however it is possible to select specific accounts and quantities which would allow additional cost quantity profiles to closely match the standard profile if this was ever desired It may also be possible that several activities should logically be mapped to a given Standard Activity An example is ARV treatment for adults and for children which might be shown in the strategy as separate activities but are included in the same NASA function In this case one of the activities should be marked as Y in the Include column E while subsequent related activities should be marked as N to avoid double multiple counting All of the actual costs for these functions will be reflected in the total Once all of the activities in the strategic plan have been assigned a function and a section it 1s then possible to go to the next step of the process In terms of division of workload it 1s probably preferable that this step be done by the program people in the MOH and or HIV AIDS secretariat with some assistance from the finance people as needed When the user is finished entering all of the data it is possible to move back to the main menu by positioning the cursor over the Menu box and left clicking the mouse I Review Costing for Standard Interventions The costing of standard act
81. xpanded by clicking on the eh box next to the relevant group This vvill reveal the functions underneath each group see Figure 14 This figure shows the expanded Treatment and Care Group where the desired functions can be clicked to go to that part of the model Nutritional Support in this example 24 Microsoft Excel ASAP AIDS pasting Model v1 2 xls Type a question for help 5 Es x ull eee N iS Enter data in green areas only Special Interventions Select desired expenditure types fr Fill in green sections with number Do not add rows to standard activit To add new set of activities add ro Then copy previous table and incr 6 Costs are automatically carried forward to strategic To use training template click on h T Costs with fewer than two significant digi 4 12 1 Prevention 36 2 Treatment and care 37 2 01 Provider initiated testing 38 2 02 Prophylaxis for Opportunistic Infections 39 2 03 Antiretroviral therapy 40 2 04 Nutritional support associated to ARV therapy 41 2 05 Laboratory monitc r file C Documents and 42 2 06 Dental Programs Settings wb161710 My Documents DS 1 HaazenlHIV AIDSYASAP HIV AIDS Costing 43 2 07 Psychological Tre Model v1 2 xls Costing ARV Nutrition 44 2 08 Palliative care Click once to follow Click and hold to 5 select this cell 45 2 09 Home based care 46 2 10 Alternative and informal providers 47 2 19 O

ASAP HIVAIDS Costing Model User Manual

Contents

Download Pdf Manuals

Related Search

Related Contents