Home

User`s manual - World Health Organization

1. reporting currency value will be transferred to the input cost sheet Step 4 next to the note reference In order to add rows to the input cost calculation note place the cursor on the note below the headings and right click the mouse Select the Insert rows command and in the dialogue box enter the number of rows to be inserted In this way the note can be expanded to the required size 36 To delete a row select the row and right click the mouse Select Delete row from the command box To delete the entire note it is necessary to go back to Step 4 and delete the note from the input cost sheet see above In the input cost calculation box describe each input and its measurement unit In the Quantity column enter the number of units of the input required and in the Frequency column enter the number of occurrences In the example below 40 hours quantity are required to do the research but the research is carried out only once frequency Once the cost ingredients have been defined enter the item values taking careful note of how the unit of measure has been defined The same logic and functionality described in Step 4 with respect to entering item values applies Enter a local currency item value except where a USD or Euro value is more appropriate As indicated above the total of the input cost calculation box will automatically be transferred to the input cost sheet Step 4 and there is no need to manually create a link Below
2. To check go to Start gt Control Panel gt System and security Click on System and check to see that your computer has at least the capacity outlined below 48 Capacity Requirements Excel 2003 Excel 2007 or 2010 Operating system Windows XP Vista or Windows 7 Pentium dual core or Processor Pentium 4 or better m 4 better 1 69 GHz or better 1 69 GHz or better At least 1 GB of RAM 3 GB of RAM Old system with 1 GIG of RAM running Windows 7 operating system To check computer specifications in Windows XP go to My computer gt Control Panel and click on System and then on the General tab If you are using Windows Vista or Windows 7 you should have a more recent processor at least a core duo and preferably an i series processor and at least 3 GIG of RAM If these specifications have been met try shutting down other spreadsheets and applications running while you are using the tool Consider also the programmes that are launched during start up and in consultation with your systems administrator establish whether some programmes can be disabled If these measures do not solve the problem contact technical support 4 2 3 The totals in the summary budget reports do not match each other The summary budget reports use look up commands to search for and match entries for Cost Category and SDA and then return values for these in the budget reports Values will not be returned if nothing is selected in these categories and
3. a cell which should contain a command menu this indicates a problem Frequently this is linked to a failure of the autosave function to operate when the model is closed It may also be linked to a general failure of the formulae and functions in the model to Operate If this happens to you take the following steps 1 Save the file 2 Close the file 3 Close Excel 4 Wait 30 seconds 5 Re launch Excel 6 Open the Excel file containing the saved version of the tool In most cases this procedure will get the tool running again If not try taking steps 1 3 and then restart the computer before taking steps 5 and 6 If this does not work after 2 3 attempts please contact technical support 4 2 7 see a where there should be a number The tool is set to run at 80 zoom If the zoom is decreased this can cause large numbers to overflow the cell Excel then returns a HHHHHHHH If you encounter this problem first make sure the zoom is not set too low If it is 85 or above and you still encounter this problem consider changing the scale for local and or proposal currency in step 2 51
4. configuration data across different workbooks and this may lead to errors in the tool It is also not recommended to operate the tool together with other complicated software This may slow the operation of the tool and could lead to a crash of the programme 2 2 2 Installation procedure This tool can be copied from portable media CD or USB drive or downloaded from http www who int hiv pub toolkits GF Resourcekit en index2 html To open the tool simply copy the file into an appropriate location specified by you open the file in Excel and follow the instructions regarding the security settings in Section 2 2 3 below Careful attention must be paid to the security settings as these will have an impact on the functioning of the macros see following section Once opened the tool will automatically save when it is closed Therefore it is critical to save a clean unopened version on your computer and to save any opened version with a different file name To create a shortcut to the file on the desktop right click on the desktop and follow the shortcut wizard instructions 2 2 3 Enabling macros This tool contains macros It will not function correctly unless the macros are enabled To ensure macros are enabled you must adjust the security settings to the appropriate level before opening the workbook In Excel 2003 before opening the workbook go to the Tools gt Macro gt Security menus and set the security setting to Low
5. current list of planning elements i e if a new activity is added it will be placed as the last activity within that SDA For Activities and Sub activities users must enter the number of rows to add once the Add or Insert command has been selected The following command box will appear 2 x4 Enter the number of rows you wish to insert coe If the number 5 is entered 5 additional activities or sub activities will be either added or inserted This speeds up the process of building the logical framework Insert A new planning element will be inserted immediately below the item selected Move up The selected planning element will be moved up one level in the structure i e if an objective is moved one level up it will be placed above the previous objective An activity will be placed above the previous activity and so on Move down The selected planning element will be moved down one level in the structure i e if an objective is moved one level down it will be placed below the following objective An activity will be placed below the following activity Delete The entire planning element will be deleted including its subsidiary components If an activity is selected and deleted it will be deleted together with all its sub activities Deleting an SDA will result in the deletion of all the activities under that SDA Minimize This command allows the subsidiary items in the item to be col
6. directly into the model Copying and pasting can result in errors Users are advised to enter data manually and to avoid copying and pasting To avoid such problems only the content of a cell should be copied without formats or other embedded commands There are a number of ways to do this 1 Paste Special function To avoid pasting formats and other commands from the source cell you may use the Paste Special command To do this copy the cell from the source sheet using the normal Copy command Then place the cursor in the destination cell In Excel 2007 select the Paste drop down list on the Clipboard group on the Home tab Then select the Paste Special command and click the Text or Values button Do not select the All or Format option buttons Under the Operations heading select None Click on OK and the value of the cell will be pasted without the formats In Excel 2010 follow the above instruction and select the Values button In Excel 2003 the Paste Special command can be selected from the right click command box or from the Edit function Note that the Paste Special command is frequently not available from the right click command box in the tool as the command box is often used to locate and execute many of the tool s macros The Paste Special function also works to copy and paste multiple cells at a time 2 Excel cell edit text function recommended The edit text function allows you to enter a cell and copy or past
7. in more than one annex indicate this in the appropriate cell 3 3 Step 2 General assumptions The general assumptions worksheet documents the key financial assumptions that underpin the calculations in the tool see Figure 3 3 below The worksheet title and step descriptions are automatically reflected and linked to the cover sheet 3 3 1 Basic data Enter the required information for all items in the green cells This includes 1 The start year for the budget The tool will automatically calculate the subsequent years for the budget The start year is defined as the first year of the proposal programme period The base year referred to in subsequent sections is the year prior to the start year of the programme and refers to the proposal preparation period 2 Scales for national and proposal currencies The tool supports the selection of a specific scale units thousands millions to simplify the reporting of large numbers When units are selected the cost figures are expressed in units of the local or proposal currency When thousands are selected the numbers are expressed in thousands of currency units and so on This option can be selected independently for local and proposal currencies Click on the green fields next to Scale for local currency or Scale for proposal currency and select the scale from the drop down list The selection of scale will be used in the tool and reports 19 3 Exchange rates The tool
8. providing a possible solution for the problem or acknowledging receipt of the email and indicating the way forward for resolving the issue Where a problem with use of the tool has occurred and an error message has been displayed it is important to include a screenshot or an accurate description of the error message in the email This will assist the support team to resolve the query 47 To create a screenshot of the error message hold the Control key down and simultaneously press the Print Screen button on your keyboard Open a blank word document and paste the resulting snapshot of the screen Screen shots can be large and should be cropped and reduced to only reflect the error message before emailing To crop the screen shot right click on the image and select Show picture toolbar Click on the Crop icon and use it to crop the picture down to include only the part you want to send Click on the picture and resize using the sizing buttons on the sides and corners of the picture Once the image has been re sized attach it to the email or copy it into the body of the email 4 2 Troubleshooting The tool may respond differently depending on which operating system is used e g Windows XP Vista or Windows 7 and the version of Excel you are using 2003 2007 or 2010 In some cases there may incompatibilities in the software versions that may cause problems The following common errors may occur Possible solutions for these errors are sug
9. structure of the logical framework right click commands Total al Goal 1 Enter goal name Objective 1 1 Enter objective name Activity 1 1 1 1 Enter activity name Activity description Enter description Sub Activity 1 1 1 1 1 Acti ity 1 1 1 2 Enter activity name Activity description Enter description Sub Activity 1 1 1 2 1 Insert new Activity Move Activity up Delete Activity Maximize Activity Minimize Activity In much the same way the right click menu can be used to either expand or collapse the item selected For example if the cursor is placed on the objective row and the Minimize command is selected the SDAs the activities and sub activities will be collapsed and will no longer be visible This functionality exists at all levels of the logical framework It is therefore possible to start reviewing the master worksheet with only certain planning elements visible In order to protect the integrity of the logical framework structure users are prevented from deleting the first planning element in any set of planning elements For example if there is only one goal in your structure you will not be able to delete it and the delete command will not appear The same applies to the first objective SDA activity and sub activity 25 Table 3 4 Functionality of various commands Add A new planning element or group of planning elements will be placed at the bottom of the
10. the menu select the language in which you would like to complete the tool Once a language has been selected the headings and instructions in the tool will be translated into your language When the tool is opened subsequently you will be taken directly to the workflow menu Please note that the tool currently does not support changing the language once the initial selection has been made 10 2 3 Conventions used in the tool 2 3 1 Protection The tool contains numerous macros and formulas and it will return incorrect results if these are changed or deleted The tool is protected to prevent inadvertent deletion or corruption of macros and formulas during data entry This protection may restrict the flexibility of the tool in certain instances and imposes some limitations on users The stability and accuracy of the tool is however significantly improved as a result 2 3 2 Colour coding In an effort to assist users colours have been used to indicate where data must be entered where data have been calculated and where a result has been generated The following colour codes apply to the tool Data must be entered or selected from a drop down list Data have been entered or selected on at least one occasion Data have been calculated by the tool or are derived from elsewhere in the tool No data entry needs to take place in these cells which have been protected from further changes In the reports results from the to
11. the proposal currency inflation rate Every effort should be made to obtain an independent estimate of inflation and exchange rates for the proposal period from a reliable source such as forecasts from the banking sector or published economic research 5 Productive work time The data categories Productive days in work year and Productive hours in work year require information on the standard number of working days per annum and hours per day These fields are optional but may be useful where input costs require the calculation of a human resource component These cells have no dependents and are not required inputs for subsequent calculations 3 3 2 Principle and sub recipient matrix In Step 3 the user is required to allocate a principle recipient PR and an SR to each sub activity included in the logical framework It is therefore necessary to build a PR SR matrix which will be reflected in a drop down list in Step 3 Navigate to the PR and SR matrix by clicking on the Recipient setup button which is located below the Basic data table on the General assumptions sheet Step2 On the Recipient Setup worksheet enter the name of each principle recipient in the cells provided next to the Principle Recipient heading Provision has been made for 5 different principle recipients for each disease component If you have more than 5 PRs for a disease component please contact technical support Under each defined PR enter the names of th
12. where a Windows Vista or Windows 7 platform was used together with only 1 GB of RAM Windows Vista and Windows 7 use a large amount of memory to operate If a computer has only 1 GB of RAM nearly all the memory will be used by the operating system and little will be available to run the tool For this reason it is strongly discouraged to run the model on a computer with Vista or Windows 7 and only 1 GB of RAM For best results it is recommended that the tool be used only on a computer with a new generation dual core or i series processor and at least 3 GB of RAM Table 2 1 System requirements Capacity Requirements Minimum Recommended Operating system Windows XP Vista or Windows 7 Pentium 4 or better Pentium dual core or better 1 69 GHz or better 1 69 GHz or better At least 1 GB of RAM At least 3 GB of RAM Old system with 1 GIG of RAM running Vista or Windows 7 operating system To check the capacity of your computer go to Start gt Control Panel gt System or alternatively go to My Computer right click and choose Properties Click on the General tab and check to see that your computer has at least the capacity outlined in figure 2 1 Software requirements Any user with Excel 2003 or a later version of Excel should not need additional software Due to the large number of macros used in the tool the use of an older version of Excel will result in errors and the tool will not function properly This updated versi
13. 1 Cover sheet details Description and function The cover sheet provides a cover to the electronic and printed version of the tool output On the cover sheet the user is required to enter information relating to the project title country name disease component currency and other necessary information to facilitate the administrative control of the tool Step 2 General assumptions On this sheet the general assumptions applicable to the costing must be entered and include items such as the exchange rate between the local and reporting currency the unit of reporting the inflation rate and project start year The principle and sub recipients matrix is accessed from this worksheet Input cost and reference notes Step 3 This step requires the user to define the logical structure for the Define the logical programme or proposal Following Global Fund convention the framework structure includes goals objectives service delivery areas activities and sub activities Defining the logical structure must be largely complete before the costing can be started This sheet is known as the master worksheet For each sub activity a PR and SR must be selected from the drop down list Step 4 Once the logical structure has been defined the user is encouraged to list and identify not quantify all input cost items that will be required to cost the sub activities in the structure The input cost items must also be linked to a Global
14. 1 3 9 2 3 9 3 Additional guidance on defining input costs Input cost values Exchange rates Apply inflation Notes Source of data Step 5 Calculate aggregate input costs Using the cost calculation notes Per unit costs Step 6 Define input cost items and quantities for sub activities Enabling and disabling calculations Selecting inputs Estimating quantities Step 7 Completion of funding matrix Step 8 Financial reports Standard proposal reports Overview of other reports Print model reports for submission 4 TROUBLESHOOTING AND SUPPORT 4 1 4 2 4 2 1 4 2 2 4 2 3 4 2 4 4 2 5 4 2 6 4 2 7 Technical support Troubleshooting The programme or certain functions do not respond when I try to use them The tool is slow to respond and functions such as report printing take a long time The totals in the summary budget reports do not match each other I have pasted data into a cell and now I cannot change it When I perform a certain function a run time error message appears 36 36 38 38 39 40 41 42 43 44 45 46 47 47 48 48 48 49 50 50 I am using Excel 2003 and the tool has stopped working or the right click commands have become disabled Isee a H where there should be a number 50 51 Table of Figures Figure 2 1 Enabling macros in Excel 2003 Figure 2 2 Enabling macros in Excel 2007 Figure 2 3 Error message for a protected CCI eeesssssscsssessssssssssessssssss
15. 29 However where several input costs can be combined to arrive at an aggregate input cost then use of aggregated input costs is encouraged see Step 5 Note that this function can be used only if all input costs can be included in a single cost category see below 3 5 2 Cost categories The Global Fund requires that costs be classified according to a pre defined set of cost categories such as human resources communication materials infrastructure and other equipment and so on The budget summaries must be presented by cost category To facilitate proper designation of cost categories the Global Fund s set of cost categories is included in the tool Each input cost must be assigned a single cost category Unless all the inputs in a sub activity fall under the same cost category it is not possible to assign a cost category to a sub activity or activity 3 5 3 Data input Broadly speaking the establishment of input costs is divided into two stages Firstly it is necessary to identify and define the physical inputs required to implement a given set of sub activities e g human resources technical assistance or a vehicle Secondly it is necessary to quantify the value of the identified input cost items Splitting the process in this way is beneficial in certain settings For example it may be possible to hand the list of defined input costs without values over to a procurement specialist with a request to research and enter the requi
16. 37 500 1875000 2250000 6223214 Non Government PR FBO 2 39 178 571 0 0 0 0 178 571 BUDGET TOTALS 100 00 1446 429 900 000 956250 1893750 2268750 7465179 Report summary budget by PR and SR per activity This report comprises the total proposal budget at main activity level but shows the main activities and costs for each PR and SR If more than one PR or SR contribute to the same activity the main activity will appear twice on the report Report of activities and budget amounts by indicator If users have completed the indicator field in the logical framework for relevant activities this report will list all the activities by indicator together with the allocated budget for those activities The intention of the report is to provide an indication of the investment being made to achieve the different indicator targets Users should note that this report total may not reconcile with the total budget for each year Budgets are included and aggregated only for those activities which have an indicator assigned to them There may often be activities which cannot be tied to an indicator and will therefore not be included in the budget 3 9 3 Print model reports for submission To print the report in the default format use conventional Excel print commands e g File gt Print Preview gt Print Alternatively you may copy the entire report onto a worksheet in a new workbook where the formats can be changed a
17. 4 Figure 3 13 Example of an aggregate unit cost calculation Figure 3 14 Input cost item commands in the master work plan and costing Sheet ssss s ssssssssssssrsssssrsrsssssrsnsss 40 Figure 3 15 Format of the Global Fund funding matrix sssss ssssssssssssssrssssnsnennsnnnnannsnnnnanunnnansnnnnnnnannnonnnnnnnnnnnnnnnnannnnnnnanana Figure 3 16 Summary reporting MENU srrcsssecssesesresecsnecssnsessssesseeesnessnessnsessseesessnniesssnessses Figure 3 17 Budget by cost category and budget by objective and SDA reports Figure 3 18 Summary budget by PR and SR ssssssssssssrsssssnsrssnsnrsrssnsnrsnusnnnrnannnnnennnnnnnannnnnanannnnnannnnnnnannnnnnnannnnnnnannnnnnannnnnnnnnnna List of Abbreviations and Acronyms ART Antiretroviral therapy ARV Antiretroviral drugs ASAP AIDS Strategy and Action Plan World Bank DSA Daily subsistence allowance GB Gigabyte GF The Global Fund abbreviation used in parts of the tool HSS Health systems strengthening MB Megabyte NASA National AIDS Spending Assessment RAM Random Access Memory SDA Service delivery area WHO World Health Organization 1 Introduction The Work Planning and Budgeting Tool referred to as the tool in this document has been developed by WHO to assist countries in developing funding applications to the Global Fund to Fight AIDS Tuberculosis and Malaria referred to as The Global Fund in this document It has been updated and revised in preparation for Round 10 propos
18. 7 3 Estimating quantities For each input cost item selected target quantities must be inserted for each quarter in years one to three and annually for years four and five To enter target quantities scroll to the right of the input cost item and enter the correct quantities in the Quantity columns The Quantity columns have been highlighted in green for ease of use Consider carefully the nature of the input cost item and the measurement unit when entering target quantities For example assume a consultant or trainer has been engaged to conduct ten training courses each of five days duration If the unit of measurement for consultant fees is per day and the input cost value is USD 500 the quantity entered should be 50 i e 5 days each for 10 training courses It should NOT be for example 10 one per training course or 5 5 days for each training course Enter target quantities only in those quantity columns corresponding to the timing of planned activities To continue with the above example if one training course is scheduled for November of the first year a quantity of 5 should be entered for the consultant trainer in the quarter four column QZ If the activity will continue in the first half of the second year quantities should be entered only in the two columns that correspond to the first two quarters of year two If the quarters for years one and two are not visible then place the cursor on the column heading
19. Budget by Cost Category This section is using the GF cost categories sf CS CC SE TC sf CC SC of CC ae 100 00 1194 444 27 388 348 42768 985 4 Budget by Objective and Service Delivery Area To expand access to 111 appropriate care support and Antiretroviral treatment 73 71 1510 516 17 826 096 34 196 293 treatment ARY and monitoring 44 Detailed Budget for Years 1 5 The detailed budget shows the master worksheet at sub activity level and all the input cost items for each year with the first three years by quarter This report shows all the values but quantities are shown only on an annual basis Proposal workplan The proposal work plan shows the entire master work plan from goal level to sub activity input cost item level For each input cost item the quantities are reflected for all the years and by quarter in years one to three The total cost for the five year period is also shown to facilitate reconciliation between the work plan and the detailed budget 3 9 2 Overview of other reports A number of other reports have been included in Step 8 A brief description of each report follows below To print these reports follow the instructions as outlined below Budget summary by main activity The budget summary by main activity is not required for Global Fund proposals but it can be useful for analysing the results by main activity and for quality control Applyi
20. Fund cost category and the unit of measure described The user is required to enter a value for each input cost defined Where an input cost has been aggregated and a calculation is required a note can be inserted and referenced at this point in the process The note is completed in Step 5 It is also on this worksheet that the user will decide how the item will be affected by inflation Data sources must be carefully documented for each item Step 5 Calculate the input costs Where possible the user is encouraged to calculate aggregate input costs that are then transferred to the actual input cost sheets These input costs calculations are presented on a separate sheet and only the total is transferred to the input costs sheet A blank note is created and automatically referenced during Step 5 16 Step 6 On the master worksheet the user is required to select input costs Define input cost from a drop down list for each sub activity until the sub activity is items and quantities fully costed A brief description of the activity will guide the for sub activities selection of input costs The cost category unit of measure and value will be automatically transferred from the input cost sheet A separate column is however provided for additional notes and assumptions relating to the costing of the sub activity Importantly the user must also enter quantities for each of the input cost items selected Step 7 On th
21. Ministry of Health One Stop Sho Two Stop Shop NGO Umbrella NGO Umbrella 1 NGO 2 One Stop Shop TIP If an activity is implemented by more than one SR then either create a sub recipient which includes the names of both SRs or for improved accuracy create similar activities in the logical framework and then select the correct SR name 3 4 Step 3 Define the logical framework The tool is based on the planning structure most often recommended by WHO for structuring 21 Global Fund proposals for HIV AIDS This structure is based on a hierarchy of planning elements that cascade down from goals to objectives SDAs activities and sub activities Input costs and quantities are entered at the level of sub activities This structure is equally applicable to any logical planning framework The logic is illustrated in the schematic in Figure 3 5 below Figure 3 5 WHO hierarchy of planning elements the logical framework Goals 1 5 per proposal Sub activities 1 5 per activity Input costs Can be several per sub The tool is flexible enough to accommodate activities and sub activities at any level of detail However it is important that proposals be internally consistent in defining these planning elements All activities and detailed sub activities should be roughly similar in content and level of detail For the best results construct the proposal logical framework from activities and sub act
22. RNY World Health Y Organization Work Planning and Budgeting Tool User s Manual For Use in Preparing Funding Applications to The Global Fund to Fight AIDS Tuberculosis and Malaria Round 11 July 2011 Table of Contents 1 INTRODUCTION 2 THE USER S MANUAL IN BRIEF 2 1 The user s manual 2 2 Installing the tool 2 2 1 System and software requirements 2 2 2 Installation procedure 2 2 3 Enabling macros 2 2 4 Selecting your language 2 3 Conventions used in the tool 2 3 1 Protection 2 3 2 Colour coding 2 3 3 Copying and pasting 2 3 4 Back up procedure 3 DETAILED INSTRUCTIONS FOR COMPLETING THE TOOL 3 1 Overview and workflow 3 2 Step 1 Cover sheet details 3 2 1 Project title and country 3 2 2 Component 3 2 3 Enter currencies 3 2 4 Enter version number 3 2 5 Annex number and name 3 3 Step 2 General assumptions 3 3 1 Basic data 3 3 2 Principle and sub recipient matrix 3 4 Step 3 Define the logical framework 3 4 1 Numbering convention 3 4 2 Constructing the logical framework 3 4 3 Enabling and disabling calculations 3 4 4 Changing the framework structure 3 4 5 Input of data 3 4 6 HSS Component Source of funding 3 4 7 Selecting service delivery areas 3 5 Step 4 Input costs and reference notes 3 5 1 General approach to input costs 3 5 2 Cost categories 3 5 3 Data input 28 28 30 30 3 5 4 3 5 5 3 5 6 3 5 7 3 5 8 3 5 9 3 6 3 6 1 3 6 2 3 7 3 7 1 3 7 2 3 7 3 3 8 3 9 3 9
23. Total quantity Year 1 or Total quantity Year 2 These cells have also been shaded in green To view the columns for each quarter right click the mouse and select the Show quarters command This will expand the columns and reveal the quarters In the same way the quarters can be hidden while the totals for year one and two remain visible Collapsing the quarters allows the user to more easily view all five years at the same time on the same screen Once target quantities have been entered the tool automatically calculates the total cost of individual sub activity input costs by quarter annually and for the five year period Value totals are also provided by activity SDA objective and goal There may be slight divergences in the cost figures due to rounding These can be safely ignored as the totals will reflect the correct figures To view a summary of the budget by cost category and SDA enter the reporting menu Step 8 and view the Budget summary by main activity Budget by SDA and Cost category reports On these reports the proportion of each item is shown as a percentage of the total budget For example the cost of the M amp E cost category is shown as a percentage of the total budget in the cost category report NOTE As the tool is being populated it may be necessary to make changes to the logical framework either in terms of its structure or descriptions The logical framework structure cannot be changed from Step 6 The user m
24. acent cell and paste it into the cell containing the wrong format You can then re enter the data in that cell and the format will be correct If the above steps do not work you can request technical support by e mailing the tool to the technical support address Specify which cells in which worksheets i e which steps need to be corrected The technical support team will make the necessary corrections and send the corrected tool back to you 4 2 5 When perform a certain function a run time error message appears Run time errors usually occur when a macro is running and an error occurs during the execution of the macro Many different types of run time errors exist and range from minor errors that do not affect the functioning of the tool to more serious errors that can result in the programme freezing and possible loss of data Run time errors may relate to errors in the coding of macros but in other cases they may relate to the incompatibility between software i e conflict between the operating system and the version of Excel being used When a run time error occurs users are requested to take a screen shot of the error and email the error message to the support team as described above in 4 1 If this is not possible the programme has frozen please document the error message and number for inclusion in an email Users should note carefully what they were trying to do in the tool when the run time error occurred As a possible solutio
25. als The main purpose of this tool is to facilitate the development of well structured work plans and accurately costed budgets for Global Fund proposals and to express these in a format acceptable to the Global Fund The tool is designed to make the planning and costing exercise more automated accurate and user friendly Many of the functions associated with the development of the work plan and budget are automated and cost calculations are protected to avoid errors and omissions Above all the tool is designed to promote consistency between the proposal work plan and the detailed budget and aims to improve the overall quality of Global Fund applications The model design has been based on a review of best practice design concepts developed from a review of existing models including the costing template developed by the Global Fund for Round 8 10 applications and after consulting with a number of costing consultants government officials and other professionals involved in costing funding proposals An effort has also been made to create some visual consistency with the recently developed World Bank AIDS Strategy and Action Plan ASAP Activity based Costing tool which is aimed primarily at costing strategies and operational plans In keeping with the requirements of the Global Fund and most operational plans the tool is designed to budget an activity based structure It breaks quantity and costing data down by quarter for the first three ye
26. ars of the proposal and by year for years four and five All the data are included on one planning worksheet with totals by year and for the total duration of the project The programme generates summary sheets that include work plans and budgets as well as budget summaries by service delivery areas and cost categories The tool has been developed for use in costing Global Fund proposals but it is generic and can be used to develop and cost other plans and proposals as well This user s manual contains detailed instructions on how to use the tool It also contains a number of warnings about possible errors that may occur if the tool is not used correctly Failure to follow the instructions contained in the user s manual may lead to errors in using the tool and may result in an incomplete or incorrect work plan and budget Users are strongly advised to carefully read the entire manual before attempting to install and use the tool The tool and the accompanying user s manual are available in English Spanish and French The tool contains an option to select the language of the tool when it is installed Subsequent changes in the language are not possible 2 The user s manual in brief 2 1 The user s manual The purpose of this user s manual is to provide the user with instructions to install and correctly use the tool The user s manual can also assist users to resolve problems that may occur Although the tool has been designed to lead
27. ated budget Similarly under the heading Upper ceilings in approved component proposals C enter the unspent funds associated with previous Global Fund proposals which are approved but are not subject to a signed grant agreement but for which activities and costs have been included in the consolidated proposal budget In the row at the bottom New incremental funding request A B C the matrix will reflect the net amount being requested from the Global Fund for each quarter for years 1 to 3 and for years 4 and 5 of the proposal period Figure 3 15 Format of the Global Fund funding matrix Step 7 Summary of Global Fund funding request Year 2012 Year 2013 Total Quarter 1 Quarter 2 Quarter 3 Quarter 4 Quarter 1 Quarter 2 Quarter 3 Quarte Currency Total consolidated proposal budget A o o o o o o o Existing funding signed grant agreements B Total a 2 ic a gt 7 id Upper ceilings in approved component proposals C Total New incremental funding request A B C 42 3 9 Step 8 Financial reports Steps 8 comprises a simple report writer Step 8 allows the user to print summary financial reports and a number of additional reports including the detailed budget and the proposal work plan Figure 3 16 illustrates the reporting menu To access the reports click on Step 8 in the main workflow m
28. ce This can only be done by changing the exchange rate in Step 2 Users must therefore take care to ensure that the name of the currency entered corresponds to the currency in which costs are entered 18 The currencies entered on the cover sheet will feed into additional calculations in Step 2 and beyond 3 2 4 Enter version number In developing proposals it often happens that several versions of the budget may exist It is useful to assign version numbers and dates to these to avoid confusion The tool provides a place to record and label different version numbers and dates on the cover page Simply enter a new version number of your choice and date every time a new version of the tool is created Similarly a field is available to record the date of submission to the Global Fund 3 2 5 Annex number and name A printed copy of the detailed Global Fund proposal budget must be annexed to the main body of the proposal Given the long list of attachments that accompany the main proposal the printed budget must be clearly identified in the table of contents of the main proposal document The tool includes a feature to record the correct annex number and annex name When printing the tool from the cover page the cover page is included in the printout together with the annex number and name see Section 3 2 6 below Enter the annex number and name from the proposal table of contents into the green cells provided If the tool reports will be used
29. dinated significant efficiencies can be achieved 32 3 5 4 Additional guidance on defining input costs The benefit of a single input cost approach is that it provides a smaller number of more uniform input cost items upon which to build the cost estimate It also requires a careful examination of input cost data and helps to ensure a certain amount of consistency in costs across different implementing agencies or geographic locations A common method for constructing a proposal is to collect portions of the proposal from various sub recipients implementing agencies or geographic areas This may result in different input cost estimates for the same cost items being received from different implementing agencies or geographic areas In some cases these discrepancies may be justified For example the cost of renting a venue for a workshop may vary based on its size quality or location and the choice of venue may depend on the number of participants Similarly the cost of hiring an international consultant may differ depending on his or her level of expertise place of residence travel costs and so on However if the unit costs estimated by different sub recipients or implementing agencies differ substantially for no apparent reason this may indicate that errors have been made or that some cost estimates are unrealistic In such cases users should carefully examine the cost data to identify and correct the source of the discrepancies Accura
30. down menu The selection of the disease component will generate the appropriate drop down list for disease specific service delivery areas SDAs in the master worksheet This drop down list is used for selecting SDAs when developing the logical framework 17 Figure 3 2 Cover sheet example Workplan and budgeting tool Project title WHO Budgeting tool proposar WS become BR er 3 2 3 Enter currencies The tool requires three currencies to be entered 1 Proposal currency this is the currency in which the proposal is to be denominated The Global Fund requires that proposals be denominated in either USD or Euros Select the proposal currency from the drop down list 2 Local currency Enter the name of the national currency 3 Report currency Enter the currency in which you would like to have reports generated For Global Fund proposals this would normally be the same as the proposal currency However countries may find it useful to have reports generated in their local currency as well The tool supports entering cost data in a mix of the local and proposal currency In Step 2 the user is guided to include an exchange rate that will calculate the conversion of local to proposal currency and vice versa The currency fields can be changed at any time during the creation of the budget and the new currency names will be reflected in the tool However no automatic conversion from one currency to another takes pla
31. e 3 6 Overview of master worksheet construction of the logical framework Step 3 Master Workplan and Budget Disable LOG ICAL FRAM EWOR K Return to workfiow calculations Directly Heading Title Related Indicator Principle ne Sub recipient recipient Total proposal budget Goal 1 Enter goal name Objective 1 1 Enter objective name Activity 1 1 1 1 Enter activity name Activity description Enter description Sub Activity 1 1 1 1 1 When a planning element is added all its subordinate planning elements are also added For example each time a goal is added a goal objective SDA activity and sub activity are added Only subordinate planning elements are added i e those below the added planning element in the planning hierarchy described earlier Superior planning elements are not added For example when an activity is added the subordinate sub activity will also be added but not the SDA objective or goal Adding an SDA results in the addition of an SDA activity and sub activity but not an objective or goal The same conventions apply when deleting moving or inserting planning elements 3 4 3 Enabling and disabling calculations On the Master Workplan and Budget sheet the user has the option to either disable or enable the automatic calculations on the worksheet As the amount of data in the model increases the calculation of all formulas each time data is entered can slow down the process of build
32. e similar training takes place it is much easier to calculate this just once and to use the cost for training per person per day In the tool the detailed calculation is referred to as the cost calculation note and is completed as part of Step 5 This step is useful to simplify the budgeting process and to avoid the need to enter an identical set of inputs multiple times It also provides the user and reviewer of the budget with the detailed calculation of the aggregate input costs This level of detail is also necessary once implementation is initiated and a more detailed budget must be prepared If a cost calculation note is used it is very important that the measurement unit in Step 4 accurately reflects the measurement units of the cost calculated in the note 3 6 1 Using the cost calculation notes Each time a cost calculation note reference is created in the input cost sheet during Step 4 a blank input cost calculation box is created in Step 5 with the same reference note designation In Step 5 the user is required to develop the calculation of each aggregate input costs The format of the cost calculation note is standardized but the content of the note is flexible The user is free to enter any input along with the measurement unit frequency and price An example of the standard format of such a calculation is provided in Figure 3 13 below In this example the user has calculated the cost of developing communication material The total
33. e sub recipients To add additional SR rows place the cursor on the Sub recipient row and right click the mouse In the command box select Add sub recipient Then enter the number SRs to be added to that PR Click OK The required number of cells will be added below the selected PR Enter a name for each SR in the 20 green cells provided A SR name cannot be entered twice under one PR but can be entered under different PRs Figure 3 3 General assumptions table Step 2 General assumptions Return to workflow Basic Data Country Start year for projection Local currency Scale for local currency Local currency to USD rate 2011 Local currency to USD rate 2012 Local currency to USD rate 2013 Local currency to USD rate 2014 Local currency to USD rate 2015 Local currency to USD rate 2016 Proposal currency Scale for proposal currency 2012 po 90 Proposal inflation rate Inflation rate 2012 Inflation rate 2013 Inflation rate 2014 Inflation rate 2015 Inflation rate 2016 Productive days in work year Productive hours in workday Recipients setup Figure 3 4 Principle and Sub recipient matrix Global Fund Explore ProjectName Elysium Return to workflow HIVIAIDS April 2011 Case Study Step 2 General assumptions Recipients setup ee 70 600 60 Principle recipient Ministry of Health Sub recipient
34. e text within the cell without copying and pasting formats or other commands To copy text or values not formulas from an Excel worksheet to the tool e g an activity description select the cell in the source sheet and double click Select the content to be copied from within the cell and select the Copy command Next place the cursor on the destination cell in the tool double click on the destination cell 12 select any existing text and select the Paste command This process will copy the text or value only and not the format associated with the source cell 3 F2 Function recommended The F2 button on your keyboard functions like the edit text function To copy text or values from an Excel worksheet to the tool select the cell in the source sheet and press the F2 button on your keyboard This enters the cell Then select the text or numbers to be copied from within the cell and use the normal Copy command Next place the cursor on the destination cell in the tool press the F2 button select any existing text and select the Paste command This process will copy the text or value only and not the format associated with the source cell Text or numbers can be copied from Word documents using the same procedures outlined above As with Excel the Paste Special or another method should be used to avoid copying and pasting formats from the source document into the tool If the normal Paste command is used in place of one of the above
35. eate a similar or duplicate sub activity in the logframe and allocate the second sub recipient to this sub activity 2 The alternative is to create a sub recipient in Step 2 which contains the names of both sub recipients In the latter option the budget will also be combined and cannot be shown separately Where the situation arises frequently users should also re examine the level at which the sub activities are being defined The directly related indicator can also be entered at a later stage once the monitoring and evaluation framework has been completed This facilitates a link between the performance framework and the work plan and budget Click in the appropriate cell in the Directly Related Indicator column and type in the required information Failure to enter a reference to an indicator at activity level will imply that the activity will not be included in the report showing the summary of activities and budget by indicator If an SDA has been added select the correct SDA from the drop down list This list will reflect the SDAs applicable to the disease component selected on the cover page above in Step 1 See also Editing the SDA List below Figure 3 8 Data entry error message F Stop X x Select SDA from list Retry l Cancel l Help 3 4 6 HSS Component Source of funding Where HSS has been selected as the disease component in Step 1 an additional field must be completed
36. enu Then click on the button corresponding to the report you want to generate The tool will automatically generate the report and display it on your screen Figure 3 16 Summary reporting menu Global Fund Explore Project Name Elysium HIV AIDS August 2011 43 3 9 1 Standard proposal reports The following standard reports are specifically requested as part of the proposal and are provided for in Step 8 e Summary of Global Fund funding request e Budget summary by SDA and cost category e Detailed budget for years 1 5 e Proposal workplan Summary of Global Fund funding request This report is a copy of the funding matrix described in Step 7 above and shows the total request of the consolidated proposal budget per annum the available funding from previous Global Fund grants and the incremental funding request associated with the proposal Budget by SDA and Cost Category The summaries by SDA and cost category must be submitted as part of the main body of the proposal document in Section 5 The summary budget reports by SDA and cost category are in the format typically required by the Global Fund Unless formats change it should be possible to simply copy and paste the output into the tables provided in the main proposal document Both reports highlight the proportion of each cost category or SDA as a percentage of the total budget Figure 3 17 Budget by cost category and budget by objective and SDA reports
37. ernal storage device If working on a network this back up may take place automatically In many situations this is not the case and the user is required to manually back up onto an external hard drive or similar device When backing up it is critical that a unique identification and date filed is included in the backup file name to ensure that the user is able to keep track of the latest version of the file The cover sheet of the tool itself also provides the user with an opportunity to enter a new version number each time the tool is used 3 Detailed instructions for completing the tool 3 1 Overview and workflow The development of an accurately costed budget for a Global Fund proposal requires teamwork To work through the budget development steps successfully various types of staff from the various sectors HIV AIDS councils and programmes and or other stakeholders should be involved All core team members should participate in the initial planning discussions and be involved at key stages of the proposal development process Team members should have a good understanding of the workings of the tool and the dependencies between the various components of the tool The table in Table 3 1 below highlights the types of staff who should be involved in completing each step of the tool It is not appropriate to expect the costing or finance professional to complete the costing tool alone 14 Table 3 1 Type of staff required to complete the to
38. ge from red to green and 39 display the message Calculations enabled Users may find that calculations do not immediately update when the enable calculations button is clicked It may be necessary to prompt the macro to run by entering new data or re entering the old data in a cell before the macro will engage and the calculations will be run Sometimes it may be necessary to exit step 6 enter another step and then re enter step 6 Normally this will prompt the macro to run and calculations will be updated 3 7 2 Selecting inputs It is most constructive to complete the costing of the logical framework one sub activity at a time For each sub activity in the logical framework consider what inputs will be required to implement the sub activity To select an input place the cursor on the green cell next to the sub activity description in the column Input cost component The default description of the green cell referred to is Select input cost from list From the drop down list select the correct input cost item The tool will automatically transfer the measurement unit the cost category and the input cost item value into the master worksheet TIP Only the active input cost items see Step 4 will appear in the drop down list The drop down list appears in alphabetical order under each heading A heading cannot be selected These items can only be changed by going back to the menu and accessing the input cost shee
39. gested below 4 2 1 The programme or certain functions do not respond when try to use them This may happen because the macros are not enabled To ensure macros are enabled you must adjust the security settings to the appropriate level before opening the workbook To do this you must close the workbook and then enable the macros in the workbook as described in Section 2 2 3 Enabling Macros in this manual 4 2 2 The tool is slow to respond and functions such as report printing take a long time This tool contains a large amount of data and requires considerable system memory and processing capacity It may take considerable time to run some functions even when the tool is functioning perfectly The tool becomes slower when populated with a large amount of programme and cost data Some complex functions e g updating the master worksheet generation of reports adding planning elements may take 20 30 seconds to run in a fully populated tool If the tool is consistently taking longer than expected to process routine tasks such as adding planning elements or generating reports it may indicate that there is a problem This may result from e using a computer that does not meet the recommended specifications e running the tool in parallel with other complicated spreadsheets or e using a corrupted spreadsheet To address a slow response first ensure that the computer being used meets the specifications described in Section 2 2 1 above
40. he cover page To select an SDA click on Select SDA from list A drop down arrow will appear to the right of the cell Simply click on the arrow and select the appropriate SDA from the list WHO recommends that countries try to limit themselves to the use of the pre defined SDAs whenever possible These have been approved by the Global Fund and will be easily recognizable to the Technical Review Panel which reviews the proposals However it may occasionally be necessary to add an additional SDA to the list of Global Fund approved SDAs To add a new SDA to the drop down list right click on the mouse and select Add SDA not listed from the command list You will then be required to enter the name of the new SDA Place your cursor on the SDA row and select the new item from the drop down list 3 5 Step 4 Input costs and reference notes 3 5 1 General approach to input costs This tool uses a single list of inputs and input costs to calculate the costs of sub activities Sub activities are constructed from the bottom up using detailed inputs as the basic building blocks Each detailed input and its corresponding cost can be used many times in any number of 28 activities For example the input costs might include an entry for one international consultant and that cost may be equal to 500 per day This component may figure in several activities but the activity component and its unit cost data need be entered only once in the input cost li
41. headings that you do not need for the moment This will hide the subordinate cost items and make it easier to navigate through the input cost list To make the list of input items easier to read and administer it is possible to collapse or expand each heading and its associated input cost items To expand or collapse the list of input cost items under each heading right click on the heading and select either the Maximize category or Minimize category command from the command box In the same way select the Show all or Minimize all categories commands to show all input cost items under all categories or hide all input cost items respectively In Figure 3 10 above the Communication Materials heading has been expanded and Human Resources has been collapsed In much the same way it is possible to show only all inactive input costs or show only active input cost items by selecting the respective commands Once the worksheet has been completed it can be printed or copied electronically and used to source input cost values for defined items This may require giving certain sections of the input cost sheet to various relevant government departments a procurement agency or other reliable source of costing data that can assist by entering the value for each item identified For example the Human Resource section of the worksheet can be given to the salaries department in the Ministry of Health for completion Where this process is well managed and coor
42. here the user wishes to copy text into a numeric cell the above methods will not work and an error message will be displayed Users may also experience difficulty when trying to copy and paste merged cells Excel cannot copy and paste data from one place to another if the size of the source and destination cells are not identical If the source data are located in a single cell they can usually be copied and 13 pasted into a destination that contains two or more merged cells However if data are copied from a block that contains two or more merged cells they cannot be copied into a block that contains a different number of merged cells If this is attempted the following error message will appear Microsoft Excel xj A Cannot change part of a merged cell In some cases entering value data as opposed to narrative data also initiates a macro Where more than one cell is copied from a source to several destination cells the macro will only run on the first destination cell For the macro to run accurately the data must then be entered into the other destination cells as well To avoid these issues it is strongly recommended that users enter values manually cell by cell If electing to copy and paste it is recommended to copy and paste data in only one cell at a time 2 3 4 Back up procedure During a costing exercise it is critical that the tool is saved frequently during use and backed up at least once a day on an ext
43. in Step 3 in the column Source of Funding Given that HSS activities may be funded by either GAVI or the Global Fund users must indicate for each sub activity whether the requested funding will be sourced from the Global Fund or from GAVI 27 Place the cursor on the appropriate cell next to the sub activity in the Source of funding column and select either Global Fund or GAVI from the drop down list This data is required for accurate reporting in Step 8 TIP Build the early versions of the logical framework outside of the tool while significant changes are still being made Once some consensus has been achieved with respect to the framework capture it in the tool This limits the amount of editing of the structure within the tool Complete the structure as far as possible in the tool before commencing with the other steps required to populate the tool A logical framework that keeps changing will lead to inefficiencies 3 4 7 Selecting service delivery areas The Global Fund requires that proposals be developed in terms of SDAs The SDAs define the broad areas in which activities occur and contribute to achieving objectives Budget summaries are required to be presented by SDA The tool includes pre defined sets of SDAs that are consistent with the SDAs recommended by the Global Fund Each disease component and HSS has its own set of SDAs that are generated automatically depending on the disease component selected in t
44. ing the logical framework and entering data To speed up the process of entering data click on the Disable Disable calculations button at the top of the worksheet calculations The adjoining cell will change from green to red and display the text Calculations disabled To enable the calculations click on the same button which now displays the message Enable calculations Enable e a calculations The adjoining cell will change from red to green and display the message Calculations enabled 3 4 4 Changing the framework structure Planning elements may be easily manipulated through the right click function on the mouse To add insert move or delete a planning element place the cursor on the row of the planning element you wish to change and right click A menu will appear that contains a number of options Simply highlight the appropriate action and left click The tool will automatically perform the required function and renumber all the planning elements automatically There is 24 no need to manually adjust the numbering in the structure The list of commands in the menu evolves automatically as planning elements are added or deleted When only one planning element exists for example the Move commands are disabled because they are not relevant The list of commands in the command box is illustrated in Figure 3 7 below Each command is described in more detail in the Table 3 4 below Figure 3 7 Edit the
45. is worksheet the user is required to complete a grant matrix Previous funding that indicates the total amount of grant funding being requested as received per the Round 11 proposal as well as the annual amounts carried forward from previous Global Fund grants The total net grant request is then calculated on the matrix Step 8 From this menu item it is possible to print the financial reports that Financial reports are required by the Global Fund In addition a number of other detailed reports are provided including values by activity which is useful for presentation and analysis work plan report the detailed budget report and the list of input cost items 3 2 Step 1 Cover sheet details In this step complete the cover sheet with general proposal related information see Figure 3 2 below for an example Completion of the cover sheet is important as the project title and details are automatically reflected on all the subsequent worksheets 3 2 1 Project title and country On the cover sheet enter the title of the project or the proposal and the name of the country submitting the proposal Place the cursor on the green fields provided for this purpose and type in the required information 3 2 2 Component To select the disease component click on the green field to the right of the Component heading Click on the drop down arrow and select the disease component MARPS or health systems strengthening HSS from the drop
46. ity Sub activity Planning Element Number 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 ELLI 1 1 1 2 1 1 1 1 2 1 1 1 1 2 2 1 1 1 2 2 1 2 1 1 1 1 2 1 1 1 2 1 3 5 3 2 1 3 5 3 3 4 2 Constructing the logical framework The master work plan and budgeting sheet must be completed in two parts The first is accessed via Step 3 and is the construction of the logical framework The second part is accessed via Step 6 and deals with the detailed work plan and budget functions The logical framework component allows the core proposal team to define the planning elements in the proposal s logical framework This framework underpins the detailed work plan and budget which are developed at a later stage To complete the structure of the logical framework click on Step 3 on the menu The user will be transferred to the master work planning and budgeting sheet The structure of this worksheet reflects the hierarchy of planning elements described above goals objectives SDAs activities and sub activities When a blank version of the tool is opened it looks like figure 3 6 below Note that the initial version of the tool contains only one planning element for each level of planning framework i e one goal one objective one SDA and so on To develop a complete proposal framework users will need to add and occasionally delete move or insert the various elements of the structure The tool provides functions to do this 23 Figur
47. ivities that contain a relatively high level of detail For the purposes of this tool activities and sub activities can be most usefully defined as a set of actions that consume resources and result ina desired primary output A primary output is the first discrete level of output that can be combined with other outputs in a service delivery area to help achieve objectives and goals Where a results based framework has been developed it is important to define and group activities in such a way that these can easily be related to the detailed output indicators 22 For example a sub activity might comprise a series of training courses for community outreach workers The primary output might be a trained cadre of X community outreach workers The input costs might include salary and subsistence allowance for the trainers and support staff adaptation and reproduction of training materials purchase of audio visual equipment travel and subsistence for the participants rental of venue and so on 3 4 1 Numbering convention All numbering in the tool is created automatically according to the convention described below As the logical framework structure is edited and amended it updates the numbering Users should not attempt to manually number the planning elements In the table below the last column reflects the numbering that will be visible in the tool Table 3 3 Numbering convention Goal Objective SDA Activ
48. lapsed and therefore hidden from view For example it is possible to collapse all objectives and view the entire structure at objective level only Maximize The expand command will reveal all the subsidiary elements of the planning element selected In the above example if an objective is expanded it will reveal all the SDAs main activities and sub activities attached to that objective Note Throughout this table reference to a planning element also includes reference to a group of planning elements e g adding an SDA will add an SDA activity and a sub activity 3 4 5 Input of data Once a planning element has been added the appropriate information can be inserted For each planning element enter the name of the planning element and for activities an additional field has been provided below the activity title to enter further details about that activity Avoid using very long activity and sub activity names 26 For each sub activity enter the name of the principle recipient and the sub recipient responsible for implementation in the respective columns Select the principle and sub recipient from the drop down list in the Principle recipient and Sub recipient columns These are required fields and diligent completion of these fields provides for meaningful analysis of the budget by PR and SR TIP Where a sub activity is carried out by more than one sub recipient then two options are possible 1 Cr
49. ll apply the default inflation rate to the input cost value over the period of the proposal Selecting No will result in an input cost value that is static from one year to the next De activate the default inflation rate for input cost items that are not expected to increase in price during the proposal period 3 5 8 Notes In certain cases it is possible to define a single recurrent cost item that aggregates a number of input costs This will be explained further in Step 5 below This step requires the user to create an input cost calculation note reference To create a cost calculation note reference place the cursor in the row of the input cost item in the column with the heading Note Right click the mouse and select the Add note command from the command box This will add a note reference and automatically create a blank cost calculation note in the aggregate input cost calculation sheet Step 5 The completion of the cost calculation note is described in Step 5 below A cost calculation note reference can be added at any time but the process re sets the input costs value to nil and links the value cell to the note To delete the cost calculation note reference and the note itself place the cursor on the note reference and right click the mouse Select the Delete note command from the command box The re numbering of the notes will take place automatically To view the note from the input cost sheet place the cursor on the note refe
50. methods you will copy formats and other embedded commands into the tool Often this will include a command to lock the cell When this happens you will no longer be able to change the content of that cell The edit text and F2 methods will paste text into the destination cell in addition to any text that already exists there To avoid this ensure that the existing text or values are selected before the Paste special command is selected The text or value being copied will then replace the existing text When formats have accidentally been copied into the tool s destination worksheet it may be possible to use the format painter to copy the format from an adjoining cell To use the format painter select a cell with the correct format on the same worksheet In Excel 2007 click on Format Painter located in the Clipboard group on the Home tab Drag the painter cursor across the cell that has the incorrect format In Excel 2003 the Format Painter can be found on the standard toolbar Select the format to copy click on the format painter then click on the cell you wish to format The format will be copied automatically Another method is to copy and paste input from an adjacent cell either blank or populated with data This will copy over the existing formats so the correct data can be re entered into the cell In worksheets where copying and pasting of more than one cell at a time is not permitted where destination cells are protected or w
51. mple if the note calculates the total cost of a workshop for 50 people but the input cost required is a cost per participant then enter 50 as the number of units The cost per participant will then be transferred to the input cost sheet Steps 4 e Secondly amend the measurement units in the aggregate cost note to reflect the per unit cost In the example of the training workshop the measurement unit would be per participant Ensure that the quantity entered for each input is calculated as a per participant quantity For example if a facilitator is employed for five days and there are 20 participants the quantity to be entered next to that input cost is 0 05 This quantity is derived by dividing the single facilitator by 20 participants The frequency would remain 5 days for a 5 day workshop If the second method is used the Number of units cell must reflect a value of 1 which is the default value and must remain 3 7 Step 6 Define input cost items and quantities for sub activities This is the most complicated step in the process of developing the proposal budget Completing this part of the master work plan and costing sheet requires a team effort combing the programmatic knowledge of programme and M amp E staff together with the costing expertise of costing and budgeting staff Users should be prepared to spend considerable time on this step The basic logic underlying the master work plan and costing sheet is that the implementa
52. n close the file and re open the file to re initiate the macros or close and re open both the file and Excel This often eliminates the problem You may also try to run the tool on another computer to establish whether the same error occurs on that computer If the error is caused by incompatibility of software on a particular computer then this may solve the problem and facilitate further work with the tool Users are reminded to keep regular backups of their proposal budgets to minimize the possible loss of data that might result from a run time error 4 2 6 am using Excel 2003 and the tool has stopped working or the right click commands have become disabled Testing has revealed that the model sometimes does not work well on Excel 2003 The tool was developed in Excel 2007 and must be converted into an Excel 2003 file for use in Excel 2003 Due 50 to the substantial differences between Excel 2003 and 2007 some functions may not be adequately transferred during the conversion process This incompatibility may manifest itself in a number of ways Often the right click commands relating to functions of the model become disabled Instead of the command menu that normally appears you will see an empty small grey square slightly below and to the right of the cursor This box appears when the right click command menu is empty It will always appear when a cell is right clicked which does not contain a command menu However when it appears in
53. nd adjustments made to suit the user before printing The reports contain a number of large worksheets summarizing the work plan and budget for the proposal Due to the large amount of information contained in these reports legibility is achieved only if the reports are printed on A3 size paper If reports are being copied into a separate workbook it is important to only copy the values across and then the formats in that order _If the simple Paste command is used as is the worksheet formulas will also be copied which will generate HREF errors in the destination workbook To copy reports across to a new workbook select the entire report to be copied and click on the 46 Copy icon in the toolbar or go to Edit gt Copy Next place the cursor on the top left hand cell of the destination worksheet and in Excel 2003 select the following commands Edit gt Paste Special gt Values Ensure that the cursor remains on the top left hand cell of the worksheet and then select Edit gt Paste Special gt Formats In Excel 2007 and 2010 after copying the report click on the Paste drop down list from the Clipboard group on the Home tab and select Paste Special Select the Values option and click OK Repeat the process but select the Format option and click OK In Excel 2010 the commands are similar except there is no need to click on an OK command button Before printing the worksheet check all the columns to ensure that these are wide e
54. ng the principles of trend analysis to the activity report frequently generates useful discussion with the stakeholders and provides a useful overview of where the money is being allocated Quarterly budget by year 1 3 The Quarterly budget report shows the quarterly and annual values for the first three years by cost category No quantities are provided in this report Unit cost detailed assumptions This report generates the input cost sheet It is useful to generate this report and request sign off of the input costs and all the related assumptions from the client Unit cost calculations This is a report of all the unit cost calculation notes completed during Step 5 Print this report together with the unit cost report as it will frequently refer to the unit cost calculation notes PR SR Summary The PR SR Summary report comprises the total annual budget amounts by PR and SR An example of this report is shown in Figure 3 18 below It is therefore relatively easy to see how much of the total proposal budget has been allocated to the different PRs and SRs 45 Figure 3 18 Summary budget by PR and SR 5 YEAR BUDGET SUMMARY pet wor Return to report list PR AND SR SUMMARY kflo BUDGET SUMMARY Principle recipient Sub recipient Percentage of total budget Year 1 Year 2 Year 3 Year 4 Year 5 Ministry of Health NGO1 14 24 _1 000 000 7 143 18 750 18 750 18 750 1 063 393 Ministry of Health CBO 83 36 267 857 892 857 9
55. note command from the command box Where the user is referred to menu options in Excel the menu items and commands have been highlighted in blue for example File gt Print Preview gt Print Throughout the document tips have been shown in blue boxes as shown below to assist in the execution of specific instructions TIP Build the early versions of the logical framework outside the tool while significant changes are still being made Once some consensus has been achieved with respect to the framework capture it in the tool This limits the amount of editing required within the tool Where the attention of the user must be drawn to critical issues or warnings these have been highlighted in each section in an orange box as shown below Ignoring these warnings will lead to the incorrect completion of the tool 2 2 Installing the tool 2 2 1 System and software requirements System requirements The tool was tested on four relatively new laptop computers less than a year old using either Vista or Windows 7 operating systems All laptops had dual core processors generating processor speeds of between 1 6 and 2 1 MHz Random Access Memory RAM ranged between 1 and 3 gigabytes GB Testing comprised the re capture of three Round 10 Global Fund proposal budgets The tool worked correctly on these tests although some functions were slow as the amount of data entered increased Processing speed became very slow on computers
56. nough to display all the text and values Adjust other formatting as required set the print preferences and print the report 4 Troubleshooting and support 4 1 Technical support From time to time users may encounter problems in using the work planning and budget tool These problems may be caused by different issues and may in some cases not be related to the tool itself Some users questions may relate to how best to use the tool and others might relate to the functionality of the tool As a first step users should read the relevant section of the user s manual carefully to make sure that their use of the tool has been correct and that instructions have been carefully followed Also consider contacting other users of the tool who may have some experience to share Contacting other users is particularly useful to discuss issues relating to the best use of the tool For example discussing questions with colleagues about how best to structure the work plan and at what level to aggregate input costs will often generate the best solution As a second option consider the items described in Section 4 2 Troubleshooting below as a means of resolving the problem If neither of these approaches leads to a solution users should contact the support team for assistance For assistance please contact the help desk at help sdc co za with a detailed description of the problem and contact details A reply email will be sent within 24 hours of receipt
57. o the cost categories approved by the Global Fund Nevertheless it is possible to create a new cost category To create a new cost category place the cursor under the Description column right click the mouse and select the Cost category list add command from the menu In the dialogue box type in the cost category you would like to add The new cost category will be added to the list in the correct alphabetical order 31 Where input costs items can be applied to several cost categories it may be necessary to enter the item more than once The only difference between the items would be the cost category selected For example transport costs may be allocated to the training cost category or to overheads In such a situation create a transport cost item under two separate headings and select different cost categories Note that cost categories cannot be subsequently changed in Step 6 For each item define and type in the unit of measure in the Unit of measure column It is critical to ensure that the correct unit of measure is described as this will determine the value to be entered in Step 4 For example the unit of measure for staff salaries can be either earnings per month per quarter or per year Users should consider the need to enter appropriate target quantities during Step 6 when defining the units of measure as well as any directly related indicators TIP You can make the input cost list easier to read by minimizing
58. ol Step id Type Staff In designing the tool every effort has been made to encourage the user to complete a defined series of steps that will lead to the completion of a logical programme or proposal structure that is accurately costed In order to achieve this the user must follow a menu driven sequence of steps that require the completion of various set up costing and linking tasks The end result of these steps is a fully costed proposal budget The level of detail to be included in the costing is determined largely by Global Fund requirements or the overriding purpose of the planning and costing exercise Although experienced users may be frustrated by the need to return to the menu to move between steps the use of the menu triggers auto save and other macro driven functions that are aimed at enhancing the overall stability and robustness of the tool Figure 3 1 below shows the tool s main menu with each step clearly named Clicking on any step will result in the user being transferred to the appropriate worksheet A return button is provided on each worksheet to transfer the user back to the menu Figure 3 1 Tool menu outlining key steps aaa Costing 15 There are a total of eight steps to be completed A brief overview of these steps is included in the table below Each step is described in detail in subsequent sections Table 3 2 Overview of steps required to complete the tool Step in menu Step
59. ol are reflected in a white field and are protected 2 3 3 Copying and pasting The tool has been designed to facilitate direct data entry where input data is required It has not been designed to facilitate the pasting of lists of data or multiple cells from a source spreadsheet Copying and pasting data may create errors or other problems In some sheets the insertion of data into a cell activates a macro and attempting to copy and paste more than one cell at a time will generate an error message see Figure 2 3 below Elsewhere in the tool destination cells may be protected Pasting into these protected cells will generate an error message as this action will attempt to override protected formats 11 Figure 2 3 Error message for a protected cell Microsoft Office Excel The cell or chart that you are trying to change is protected and therefore read only To modify a protected cell or chart first remove protection using the Unprotect Sheet command Review tab Changes group You may be prompted for a password It is strongly recommended that the user enter the data directly into the tool cell by cell However there may be cases where users may wish to cut or copy text from Word documents or other worksheets and paste it into the tool Most steps in the tool can accommodate this but some difficulties may be encountered when using this technique TIP The model works best when data are entered
60. on of the tool was developed in Excel 2007 and use of Excel 2007 or better is recommended While it will run on Excel 2003 some features may not work correctly and the tool may experience occasional errors or instability It is recommended to run the tool only on excel 2007 or better The tool cannot normally be run on netbooks or with Excel Starter This is a stripped down version of excel that will not run macros Without macros the tool cannot operate Other systems issues Many other settings and Windows set up options influence the capacity and speed of any computer Please consult an expert to ensure that these have been set to maximize the processing speed and performance of your computer Requirements may also change depending on the operating system you are using Windows Vista or Windows 7 require more than the minimum capacity specified above With these operating systems the tool may not operate with less than 3 GB of RAM The tool requires a relatively large amount of memory and processing power to operate The system requirements increase significantly as the tool is populated with data Some functions such as updating the master work plan or generating reports may take 10 15 seconds in a fully populated version of the tool Users should be patient and wait for these processes to finish before attempting further entries It is discouraged to run more than one version of the tool at the same time on the same computer Excel shares
61. or Medium see Figure 2 1 below If you choose Low macros will be enabled automatically If you choose Medium you will be prompted to enable or disable macros when you open the workbook Ensure that you enable the macros when prompted to do so Figure 2 1 Enabling macros in Excel 2003 High Only signed macros From trusted sources will be allowed to run Unsigned macros are automatically disabled Medium You can choose whether or not to run potentially unsafe macros Low not recommended You are not protected from potentially unsafe macros Use this setting only if you have virus scanning software installed or you have checked the safety of all documents you open No virus scanner installed caei In Excel 2007 a security warning will appear above the worksheet To the right of the warning is an Options button Click on the Options button and then click on Enable this content Then click on OK In Excel 2010 simply click on the Enable button The procedure needs to be repeated each time a workbook is opened as macro settings sometimes change when the workbook is closed In Excel 2007 it is possible to enable all macros permanently in the trust centre see Figure 2 2 below This is not recommended as it may result in the execution of unauthorized code Should you however wish to do so then click on the Office button the round button at the uppermost left corner of the worksheet and go to Excel options g
62. re automatically calculated from this worksheet and reports can be printed in Step 8 TIP When accessing the master worksheet from the workflow menu the headings will be frozen in a particular way If users which to change the frozen section of the sheet this can be done by using the normal Excel commands Place the cursor in the top left corner of that portion of the sheet which must remain un frozen Click on the View tab on the main Excel menu click the Freeze Panes drop down list in the Windows group and select the correct command The original freeze settings will be over written In the same menu group the Split screen function can also be used 3 7 1 Enabling and disabling calculations As mentioned above in para 3 4 3 on the Master Workplan and Budget sheet the user has the option to either disable or enable the automatic calculations on the worksheet As the amount of data in the model increases the calculation of all formulas each time data is entered can slow down the process of building the logical framework and entering data To speed up the process of entering data click on the Disable Disable calculations button at the top of the worksheet calculations The adjoining cell will change from green to red and display the text Calculations disabled To enable the calculations click on the same button which now displays the message Enable calculations Enable ae calculations The adjoining cell will chan
63. red values To access the input cost sheet click on Step 4 of the menu The input cost sheet is illustrated in Figure 3 10 below Figure 3 10 Defining the input costs on the input cost sheet WHO Budgeting tool Project Name Test Country HIV Aids Version Version 1 1 7 June 2010 rate ZAR to USD T 750 Apply Description GF Cost Category PA pest lag peli ily nanan entered Billboards Communication Materials 2 000 00 15 000 00 Local Pamphlets and leaflets Communication Materials pershest pershest 0 07 Local Product development Communication Materials 100 00 750 00 ZE Communication Materials er booklet D i ie Local General Costs Local No 30 Review the sub activities that have been defined in Step 3 and consider the type of resources that will be needed during the implementation of the sub activities Based on this analysis identify and define the required input costs Enter the names of the identified input costs in the first column of the worksheet under Description in the green cells provided under the most appropriate heading in yellow The headings are only used as separators in the input cost drop down list described under Step 6 The headings cannot be edited TIP To protect the integrity of the tool input costs items cannot be deleted completely from the sheet As noted above descriptions can however be amended Users should therefore not add input cost items
64. rence and right click the mouse Select View note from the command box The user will be transferred to that specific cost calculation note on the aggregate input cost calculation sheet Step 5 35 3 5 9 Source of data Often users may forget where a particular cost value was sourced or how it was calculated Other times another user may need to use the tool and will need to have access to this information In such cases it is useful to have a record for later reference The Source of data assumption column exists to allow the user to record any observations on the source of the input cost data or assumptions made in calculating the input cost value To use this feature enter the source of the value data in the Source of data assumption column on the right of the worksheet This field can also be used to enter notes or assumptions that refer to the input cost item value 3 6 Step 5 Calculate aggregate input costs As mentioned during Steps 4 the user may define input cost items that comprise an aggregate of more than one cost ingredient A typical example might be the cost of training a trainee for one day This input cost comprises the sum of the cost of the facilitator venue hire meals travel allowances and training material To arrive at the cost of training a trainee for a day the total aggregate cost must be divided by the number of trainees in the training workshop Instead of listing each input for training every tim
65. requires the user to enter an exchange rate for converting local currency amounts for the base year as well as each year of the proposal period The base year represents the period during which the proposal is being prepared and is shown as the year before the proposal programme start year Input cost items Step 4 will be converted at the base year exchange rate Enter the exchange rates as the number of local currency units per unit of the proposal currency NOT as number of proposal currency units per local currency unit For example if there are 20 local currency units per dollar enter this as 20 not as 0 05 If this number is entered incorrectly the budget will be incorrect 4 Inflation rates Enter the estimated local currency and proposal currency inflation rates for each year in the proposal budget in the respective columns provided Enter the inflation rates in percentage points e g for 5 8 enter 5 8 not 0 058 If no inflation is provided for then a zero must be entered here In Step 5 the user can choose whether or not to apply the default inflation rate for specific input cost items Not applying the inflation rate means that the item will not be inflated Input cost items are inflated from the base year to the first year of the proposal programme period and for each year thereafter Input cost items entered in the local currency will be inflated by the local currency inflation rate and proposal currency items will be inflated by
66. s for inactive inputs these will not be used in calculating costs and budgets and will not appear in the drop down lists used in Step 6 34 3 5 6 Exchange rates The exchange rate entered in Step 2 can be modified at any time Any modification to the exchange rate will trigger an automatic recalculation of the budget The budget is calculated by multiplying the proposal currency input cost value by the target quantities entered in Step 6 When input cost values are entered in the proposal currency a change in the exchange rate will have no impact on the input cost value used for budget calculations However when the input cost value has been entered in the local currency a change in the exchange rate will result in a change to the proposal input cost value Any change in the exchange rate will cascade throughout the tool and result in changes to the budget figures 3 5 7 Apply inflation Users will occasionally find it useful to exclude some costs from the effects of inflation For example the prices of antiretroviral medicines can be expected to fall over time so it would be unrealistic to inflate those prices The same may be said of computer equipment communications etc This tool includes a feature to apply or not apply the default inflation rate to individual input costs Once the input cost item value has been entered go to the column labelled Apply Inflation and select Yes or No from the drop down list Selecting Yes wi
67. serting values and source data for defined input costs poe 200 Prca eo Eee i ase iid Source of data assumption Measurement in USD in ZAR Lihat P entered T Ferial per month 200000 000 00 Loca Yes J a E T Oooo O O M coea No O T S yS Local No a TIP Enter cost values in either the proposal currency or the local currency but not both The alternative currency is calculated automatically using the exchange rate entered in Step 2 Input cost values should be entered in the currency that is most often used to denominate and or procure that input If input cost items are usually procured with local currency then enter these values in the local currency column If the input is usually denominated or purchased with the proposal currency enter the value in the proposal currency For example items such as local salaries venue rental local travel costs office supplies utilities and local services are most often denominated and paid in local currency These items should be entered in the local currency However items such as international consultants imported supplies and equipment imported pharmaceuticals and so on may be denominated and purchased using USD or Euros The cost values of such items should be entered in the proposal currency It is only necessary to enter cost values for those cost items that are active No values need be entered for inactive inputs While the system will allow you to enter cost value
68. snseessnusssssssssssasessniusssssssssnnasesssiissnnsesnassssinsen 12 Figure 3 1 Tool menu Outlining Key Steps uesseecssseccssssecssssssssssnseessssssssssssssssassssssenssssnseesssusssssesesssaneesssissssnsiesssnessssninesnnateses 15 Figure 3 2 Cover sheet example ricci nerian a e A a aan eae ata cana 18 Figure 3 3 General assumptions table wnersseccsssesccssnsecsssecssssessssnseessasessssssesssatesessenssssnseessnaeessssesssssnseessinssssinisssantessniessniessens 21 Figure 3 4 Principle and Sub recipient matrix u eecssseeccsssessssssessssecssssessssnseesssnessssnssessssscesssinssssnseessnsessssnsissnnasessnssssnieen 21 Figure 3 5 WHO hierarchy of planning elements the logical frameworR ssssssssssssssssssssssnsrssssnnsrenssnnensnnnnrnnnna 22 Figure 3 6 Overview of master worksheet construction of the logical fra M WOPK esses 24 Figure 3 7 Edit the structure of the logical framework right click commandS ss sssss1sssssissssssrsssssnsrssssrsnsss 25 Figure 3 8 Data Cntry Crror MESSAGE evvessssscssssscsssserssssssessssssssssensessssscssseesssnsnsssansusssioesssessssssssssssiosssssnsssssssssassusssinssnnssinesiins Fig r 3 9 Input COSES a sscsssssessecrssuistssccsaasesshsnisinesssuanetinstansssanisaissyaticascesiliiatis seer anibesasiabaaiannsiiiedder ana anaia Figure 3 10 Defining the input costs on the input cost sheet Figure 3 11 Inserting input cost rows Figure 3 12 Inserting values and source data for defined input costs veers 3
69. st Figure 3 9 below illustrates this principle Input 1 and its corresponding cost are entered in Activity A and again in Activity B Input 3 is used in Activities B and C while Input 2 figures in all three activities Figure 3 9 Input Costs For optimal use the tool requires input cost data to be standardized across different activities The same input cost must be used for all sub activities that make use of that input cost For example if an international expert will be recruited for 5 days for sub activity A another for 10 days for sub activity B and yet another one for 15 days for sub activity C the same input cost figure should be used In other words assuming that the international experts referred to above have similar skill levels then the same daily rate must be used and multiplied by the number of days for each sub activity In order to achieve the above consistency the tool accommodates all input costs on a single input cost sheet The master worksheet refers back to the input cost sheet to retrieve input cost data The tool can accommodate input cost data at any level of detail but it is recommended to provide for input costs at the lowest level of each input cost unit Examples of input costs include one salary day for consultants one salary day or month for regular staff one day for venue rental one day of per diem and allowances for workshop participants one airfare one vehicle one dose of medicines and so on
70. t Trust centre gt Trust centre settings gt Macro settings Check Enable all macros and finish by clicking OK In Excel 2010 the Trust centre is reached by clicking on the File menu tab and the selecting the Options button Figure 2 2 Enabling macros in Excel 2007 gt SS SIPFET Sad are a Clipboard x Alignment 7 Number Editing aa Mo Security Warning Macros have been disabled Options B1 O f S World Health Organization bur convention applies Projecrname P O Security Alert Macro jata Version and date Be Macro ed references data er Date of submission En Macros have been disabled Macros might contain viruses or other security hazards Do ed contains the result not enable this content unless you trust the source of this File Warning It is not possible to determine that this content came from a trustworthy source You should leave this content disabled unless the content provides critical functionality and you trust its source More information File Path C el Beta version 25 Nov 09 Copy of Costing Tool Version 1 0 BETA 2 xls Help protect me from unknown content recommended Enable this content 2 2 4 Selecting your language The tool is available in English French or Spanish The first time the tool is opened the user will be prompted to select one of these languages from a list containing the language options and corresponding flags From
71. t cost values must be entered at their current values i e the base year values Input costs will be inflated from the base year to the start year of the programme on the basis of inflation rates entered in Step 2 In the same way the current exchange rate is entered for the base year and used for converting local currency values to the proposal value Figure 3 13 below illustrates the relevant columns for entering input cost values The columns 2010 Price in USD Euro and 2010 Price in ZAR are the relevant columns The year and the name of the proposal and local currencies will change depending on what you have entered in 33 Step 2 To enter a cost value go to the headings for Year Price in USD Euro or Year Price in local currency Enter a cost value in either the proposal currency or the local currency for each input cost item When a value is entered in the proposal currency the local currency value is automatically calculated shaded in green in Figure 3 12 below The same applies when a value is entered in the local currency it is automatically converted to the proposal currency It is not possible to enter values for both proposal and national currency The programme will use only the last value entered and translate that value into the alternative currency In the column Last currency entered the tool will automatically indicate in which currency the input cost value was entered Figure 3 12 In
72. t in Steps 4 For each sub activity only one input cost item row will initially be visible To add an additional input row place the cursor on an input cost item cell in the column that reads Input cost component next to the sub activity that is being costed Right click and select the Add new sub activity input cost command from the list of commands in the command box see figure 3 14 below Enter the number of input cost items to insert in the dialogue box and click OK Repeat the process of selecting an input cost item from the drop down list as described above Figure 3 14 Input cost item commands in the master work plan and costing sheet O Add new sub activity input cost Insert new sub activity input cost p Move sub activity input cost up Valid lt Move sub activity input cost down Delete sub activity input cost valid input cost v alid input cost To insert an input cost item place the cursor on the input cost item cell above the location where the input cost item must be inserted Right click the mouse and select Insert new sub activity input cost from the list of commands in the command box Enter the number of items to 40 be inserted in the dialogue box and click OK A new input cost item will be inserted below the cursor position To delete or move up or move down an input cost item repeat the process above and select the correct command from the command box 3
73. tely reflecting differences in input costs in the input cost sheet presents a challenge for users The right balance must be struck between a legitimate differentiation of input costs and keeping the set of input costs to a reasonable size In most cases the preferred method is to arrive at an average cost that reflects the average cost of the item based on the best available information However when there are obvious and significant differences in costs based on quality location capacity or other aspects of the component these can be reflected by differentiating the activity input costs For example there may be one entry for a large venue rental and another for a small venue or potentially it may be necessary to differentiate input costs by location due to substantial cost differences between one location and another In many cases input cost data will be available through government accounts based on previous expenditures and actual cost data In such cases a reasonably comprehensive and accurate list of input costs can be constructed from existing data potentially eliminating the need to rely on estimates from sub recipients or implementing agencies 3 5 5 Input cost values This tool allows input costs to be entered in either the local currency or in the proposal currency The tool uses the exchange rate entered in Step 2 to automatically calculate the alternative value and returns an input cost item value in the other currency The inpu
74. the summary budget reports will therefore not match the total value of the proposal budget To ensure the budgets are correct take the following steps 1 To ensure that the summary budget by cost category is correct make certain that you have selected a cost category for each detailed cost component in the input cost sheet Step 4 2 To ensure that the summary budget by SDA is correct make certain that you have selected an SDA name for each set of activities under an SDA in the master work plan and costing Step 3 3 Check the logical framework and ensure that a PR and SR have been allocated to each activity and sub activity 49 4 2 4 have pasted data into a cell and now I cannot change it This may occur if the procedures outlined in Section 2 3 3 are not followed when copying and pasting data If the simple copy and paste functions are used you may inadvertently copy formats as well as a cell lock into the tool Since much of the workbook is protected you will not be able to change the data in the cell subsequently If you have inadvertently changed the format of a cell but it remains accessible i e the cell is not locked you can reinstate the correct format by using the Format Painter command Select a cell in the worksheet which has the correct format On the Home tab select the Format Painter from the Clipboard group Then click on the cell which you want to repair An alternative method is to copy the data in an adj
75. the unit cost note is a narrative Description field In this filed enter any assumptions or comments about the input cost note the calculation and how the amounts and were arrived at Figure 3 13 Example of an aggregate unit cost calculation WHO Budgeting tool Project Name Test Country HIV Aids Version Version 1 1 7 June 2010 Step 6 Calculate input costs the composite event or process All input costs should subscribe to the same cost category NOTE 1 Product development Measurement Quantity Frequency 2010 Price 2010 Price Last currency 2010 Total 2010 Total Unit in USD in ZAR entered in USD in ZAR Research material per hour 40 00 1 00 66 67 500 00 Local 2 666 67 20 000 00 PaaS 1 00 10 00 333 88 2500 00 Local 3 333 33 25 000 00 Test on focus groups group meeting Develop material per hour 100 00 1 00 75 00 562 50 Proposal 7 500 00 56 250 00 Local Note Total 13 500 00 101 250 00 Number of 1 00 1 00 units Cost per unit 13 500 00 101 250 00 TIP Take extra care to ensure that input item values accurately reflect the measurement unit indicated under the Measurement unit column When calculating aggregate costs it is extremely important to ensure that input item values reflect the measurement units indicated under the Measurement unit column A common error is to provide for the quantity in the input costs
76. the user through the various steps to complete the work plan and budget detailed instructions are only contained in the user s manual The user s manual must be read completely before the tool is installed and used Failure to do so may lead to incorrect completion of the tool and may result in a work plan and budget that contains errors The user s manual comprises four sections 1 Section 1 An introduction which provides a brief background to the development of the tool and its primary purpose 2 Section 2 An overview of the user s manual the conventions used in the manual and the tool and instructions for installing and running the tool 3 Section 3 The bulk of the user s manual which provides detailed instructions for using the tool In order to improve the readability of Section 3 the following structure has been followed for each step e Introductory paragraphs that describe the logic and purpose of that step e Specific instructions for completing the step 4 Section 4 A troubleshooting section with a list of possible problems and solutions that may be experienced and contact details for technical support Where possible screenshots of the tool s workbook have been included to illustrate each step and assist in the learning and problem solving process The instructions frequently refer to headings or commands in the tool These are identified in bold as in Right click the mouse and select the Add
77. tion of sub activities will consume resources The consumption of these resources generates costs In most cases several types of resources will be consumed and therefore generate different input costs for each sub activity In order to calculate the budget the tool multiplies each input cost 38 item value by a target quantity for each period of the budget quarter This mathematical process can be represented by the statement v q m c where v Input cost item value q Target quantity m Expected inflation c Total budget value for that input cost item for any given period The variables v and r will have already been entered in previous steps In Step 7 the user must select the inputs and estimate the variable q the number of units of each input that will be required for each sub activity for each budget period The aggregate of the budget values for all the input cost items under each sub activity represents the total budget requirement to implement the sub activity This budget requirement is presented by budget period and in the aggregate over the five year time horizon of the proposal Step 6 is the final step in developing the budget proposal This step brings together the information provided in previous steps to finalize the master work plan and costing sheet Once this step is completed the master work plan and budgeting sheet will contain all the information required to cost the proposal in one work sheet Budget values a
78. unless they are reasonably certain that the items will be used To add a new input cost item you may either overwrite an existing item or add a new item To add a new item place your cursor below the row where you would like to add the new item right click the mouse and select the Add new input cost command from the command box A dialogue box will appear that asks the user to indicate how many rows need to be added see Figure 3 11 below In the space provided type in the number of rows to be added Where an existing item is edited or overwritten it is important to ensure that the correct cost category is selected and that the unit of measurement is amended Figure 3 11 Inserting input cost rows r x to How many rows to create i Enter the number of rows you wish to insert Cer For each item identified select either Yes or No in the Active column from the drop down list Selecting Yes will identify the input cost item as an active input cost item and will display it in the drop down list used in Step 6 Selecting No will de activate the input cost item This is useful where users may not want to overwrite an input cost item in case they might want to use the item at a later stage For each item select the associated cost category from the Global Fund cost categories included in the drop down list in the column entitled GF Cost Category WHO recommends that countries try to limit themselves t
79. ust return to the menu and access the logical framework through Step 3 3 8 Step 7 Completion of funding matrix Countries applying for grant funding during Round 11 will be required to consolidate previous grants and submit a proposal which includes all the consolidated activities together with new activities The proposal will therefore represent the total grant portfolio by PR in the country for a specific disease Given that some of the activities will already be funded from previous grants it is necessary to isolate the total amount of the consolidated grant proposal which requires additional funding In order to achieve this it is necessary to complete the Global Fund grant funding matrix Navigate to the matrix by clicking on Step 7 in the work flow menu The total value of the current proposal will be reflected in the top row Total consolidated proposal budget A Remember that this amount reflects the consolidated total of all proposal activities and not only the value of new activities Under the heading Existing funding signed grant agreements B enter the unspent funds associated with previous Global Fund grants that have been included in the consolidated proposal budget Place the cursor on the appropriate row and enter the grant description For each quarter in years 1 to 3 and years 4 and 5 enter the unspent portion of the grant allocated to that period on the basis of the timing of activities included in the consolid
80. value For example suppose a five day training course is being planned for 50 participants and one of the cost ingredients is a daily subsistence allowance DSA of US 30 for participants The measurement unit for DSA for participants is indicated to be per day In this case enter only the US 30 DSA figure in the 37 EUR USD price column Do not multiply by five to get the total cost per participant Enter the number of participants 50 in the Quantity column and the number of days 5 in the Frequency column 3 6 2 Per unit costs Users may sometimes wish to express the aggregate unit cost calculation as a cost per unit of output Taking the example above for the development of communications materials the user may wish to express the cost of development as a cost per booklet rather than the total development cost Or the cost of a training workshop may need to be expressed as the cost per person trained rather than the total cost of the workshop If it is necessary to calculate a cost per unit of output this can be achieved in two ways e Firstly if the note has been completed to calculate the total cost of the aggregate input then the total must be divided by the number of units to calculate the per unit cost In the cell Number of units below the note enter the number of units The last two cells bottom right hand corner of the note will then indicate the per unit cost in both the proposal and the local currency For exa

User`s manual - World Health Organization

Contents

Download Pdf Manuals

Related Search

Related Contents