Guidelines to Complete the 2004 2008 Forecast Spreadsheet
GUIDELINES TO COMPLETE THE 2012–2016IMMUNIZATIONFORECAST SPREADSHEET
Introduction
The forecast spreadsheet requires a large amount of information to be entered. The guidelines below explains what is required within each field of data. Use it as a reference document when completing the form. If you require further clarification do not hesitate to contact the Planning Unit within the Vaccine CenteratSupply Division.The focal point for the forecast at Supply Division are Myungsoo ChoTel : +45 35273011 & Email : , Gwenn Martiny +45 3527 3155 & Email and Anthony Bellon Tel: +45 3527 3061 & Email
General Comments:
- Please use the current format of the Forecast Spreadsheet for 2012 and not a prior version.
- Please calculate all vaccines in doses, deworming drugs in tablets, devices in pieces and cold chain equipment in units.
- Please note that you cannot make any comments by each vaccine/device description. There is space provided for comments in the last section.
- The worksheets and workbook are protected to keep validated and consistent information through the forecast tool. Only relevant cells are open for edition. Information messages will appear when clicking on a cell. Error messages will appear whenever the recorded information does not correspond to what is required.
Examples:
The Forecast Spreadsheet Layout for Vaccines and Injection Devices.
The forecast spreadsheet is split into 7 sections.
- Section 1 focuses on the programmatic requirements including the target population, wastage and coverage rates, stock levels, etc. It consists of 5 Tables that need to be filled in and additional information regarding how the forecast was generated.
- Section 2 captures data regarding stock-outs that occurred during 2011.
- Section 3 focuses on what procurement mechanism will be used and what source will be used to fund the products. It consists of 2 Tables to be completed.
- Section 4 focuses on the shipment information. This requires information regarding the quantity and timing of the shipment for the coming year as well as information on the forecasted quantities for the years 2013-2016.The forecast requirements for 2013 are splitted into quarterly requirements.
- Section 5 contains additional notes regarding the forecast.
- Section 6 contains any supply-related question(s) for better supply arrangements.
- Section 7 provides space for the country office to make comments regarding the forecast. Please indicate the table you are referring to.
Section 1 – Total Programmatic Requirement
General Description:
This section has groups of data to be completed:
- Total Country Population
- 3 Questions on the process of developing the Forecast 2012 – 2016
- 1 Question regarding vaccine registration requirement in the country
- 5 programmatic tables
The Total Country Population cell has been formatted soonly whole numbers can be entered.
The 3 Questions on the right of the spreadsheet refer to the process of developing the Forecast 2012 – 2016. It is important that the Forecast is done in consultation with the ICC, since it gives a higher degree of validity to the data.
The relevant answer can be chosen from a drop-down menu when clicking on the cell in blue, and then on the arrow that appears. See example below:
If the answer is “NO”, please explain the reasons why on the cells in blue as indicated in the spreadsheet.
There are 5 Programmatic Data Tables to be completed.
Table 1 covers requirements and stock situation of routine products.
Table 2 is used to indicate the required devices for the Routine Activities.
Table 3 covers stock levels of devices (routine and supplementary).
Table 4 covers products requirements for supplementary activities (campaigns).
Table 5 is used to calculate the required number of devices for supplementary activities.
General Comments on Tables:
- Complete all fields for each product within the Country programme.
- To forecast a product that is not in the drop-down menu, you canuse the last rows“Other” towrite whatever the product you plan to procure. These cells are not protected and can therefore be used for miscellaneous/non-standard products.
- Please note that each cell in the Routine and Supplementary tables for devices (Tables 2 and 5) refers to a vaccine in the table to the left (Tables 1 and 4). The quantities are calculated by a formula to help you reviewing the quantities of injection devices needed.
- For deworming products it might be difficult to decide if a distribution is considered Routine activity (Table 1) or a Supplementary / Campaign activity (Table4). Products needed for distribution through child health events, measles SIAs and Polio NIDs are to be entered in Table 4.
Table 1 –ProductsRequirements: Routine Products
Target Age Group
This field is to record the target age group.
For TT, please specify whether the target is all women of child bearing age or just pregnant women.
Target Population
It is the quantity of children and/or women within the Target Age Group.
Estimated Coverage 2012 (%)
This is the percentage of the Target Population that you expect to be able to reach.
Use numbers from 0 to 100 to indicate the percentage, as in the example below.
Number of Doses/Tablets per Person
Input the number of Doses/Tabletsrequired per child and/or woman.
For deworming drugs one dose can be more than one tablet. Please enter the average number of tablets per person based on the historical data of your programme.
Estimated Wastage 2012 (%)
Enter the percentage of the quantity of product that is expected to be wasted. The estimated wastage needs to be calculated based on the quantity of product procured. If unsure, use historical wastage rates as a guideline.
Use numbers from 0 to 100 to indicate the percentage, as for the Estimated Coverage.
Total Number of Doses/Tablets
The formula below has already been inserted in Table 1 to help you calculate the number of Doses/Tablets you would ideally need with the given conditions.
Please note, the “Total Number of Doses/Tablets” figure does not take into account the current stock, the goods in transit, the expiry date and the buffer stock. It will therefore vary from the quantity to procure, which is recorded in Section 3.
Normal Buffer Stock Quantity
Please indicate your required buffer stock quantity(for vaccinesusually 3 months stocks of annual requirement).
Current Stock
Please indicate your current stock quantity,regardless of whether it is for routine or supplementary activities, and the date when the stock was counted.
Note:
Take into account the remaining stock at the end of 2011 to calculate your fundings needs for 2012.
Table 2 - Routine Vaccine Device Requirements
General comments:
A formula has been inserted in the cell which automatically calculates the number of AD-syringes, reconstitution syringes and safety boxes, you would theoritically need with the given programmatic conditions and selected vaccine product in column A. For the syringes, it includes a 10% wastage rate andprovides one reconstitution syringe per vial of vaccine.
Please note that the “Total Number” figure does not take into account the current stock, the goods in transit, the expiry date and the buffer stock. It will therefore vary from the quantity to procure, which is recorded in Section 3.
Total Number of AD-syringes
The formula used for the AD-Syringes is:
Please note that for the vaccines: Cholera, Human Papillomavirus, Japanese Encephalitis, Rabies and Rubella, we have considered the AD syringes in table 2 when applicable but not any reconstitution syringe. For Typhoid vaccine which is not pre-qualified yet, we do not know the presentation and thus devices are not taken into consideration.
Total Number of Reconstitution Syringes
The formula used for the Reconstitution Syringes is:
In line with the Programme Division/Supply Division Program Instruction on the introduction of 2, 3, 5 and 10 ml re-use prevention featured syringes for the reconstitution of vaccines to increase Injection Safety, UNICEF continues phasing out the distribution of standard disposable syringes for vaccine reconstitution and replacing them with re-use prevention (RUP) syringes.
RUP Featured Syringe: Re-Use Prevention Featured Syringe for Reconstitution / RUP Featured Syringe with SIP: Re-Use Prevention Featured Syringe with Sharps Injury Prevention for Reconstitution i.e. a syringe with a mechanism to prevent needle stick injuries.
Total Number of Safety Boxes
The formula used for the Safety Boxes is:
For estimation purposes, each safety box has capacity for 100 syringes.
Table 3 - Device Stock Data
Table 3 requires current and buffer stock data to be entered for devices,whatever they are for routine or campaign activities.
Please follow the same guidelines as mentioned under “Current Stock” and “Buffer Stock” in the “Table 1” section above.
Table 4 – Supplementary Product Requirements
Enter the programmatic requirements for supplementary activities (campaigns) in Table 4. This follows the same procedure as Table 1 above.
Table 5 - Supplementary Vaccine Device Requirements
Enter the devices quantities required for the supplementary activities following the same procedures as outlined under Table 2 above.
Section 2 – Report on Product Stock-Outs
This section is intended to collect information on stock-outsthat occurred during 2011.A stock-out situation is defined as having no useable products at the central store level.
Select‘YES’ or ‘NO’ from the drop-down menu by clicking on the greencell. If you have experienced a stock-out, indicate the products concerned, the period, the durationand the cause.
See below example:
Section 3 – Procurement and Funding of Programmatic Needs
General Description:
This section records data on:
- the actual quantities to be procured,
- whether these quantities will be procured through UNICEF Supply Division or outside UNICEF
- and the expected source of funding.
There are 2 tables:
- Table 6–Procurement Quantities and Mechanism, in which the total quantity to be procured is automatically calculated as the sum of quantities to be procured through UNICEF Supply Division and outside of UNICEF Supply Division.
- Table 7 – Funding Source information for Procurement through UNICEF SD only, in which the funding source is to be provided for the quantity of products to be procured through UNICEF Supply Division. It provides valuable funding related data.Please record the data as accurate as possible.
General Comments:
- The vaccinesselected in Tables 1& 4 will be shown automatically in Table 6.
- The devices must be selected in the drop-down menu in the Table 6.
- When calculating the quantities to be procured, please take into account the following factors:
- the number of Doses/ Tablets resulting from the calculation of the formula in Section 1
- the current stock quantity and its expiry date
- the incoming shipments (Goods In Transit)
- the products needs for the remainder of 2011
- the buffer stock size
- the presentation/vial size preferred for each product
Table 6 – Procurement Quantities and Mechanism
Total Number of Doses/Tablets to procure
This is the total quantity of each product that you intend to procure for the year. It is calculated automatically by the sum of the following 2 columns (EF and GH).
Number of Doses/Tablets to be procured through UNICEF SD
This is the total quantity that you intend to procure through UNICEF SD. It can be through (i) Programme or through (ii) Procurement Services or (iii) funded by GAVI.
Number of Doses/Tablets to be procured by the Government or other Agencies or Organisations
This is the total quantity that you intend to procure independently, without going through UNICEF.
Table 7 – Funding for Procurement through UNICEF SD
Number of Doses/Tablets
This quantity is equal to the Number of Doses/Tablets to be procured through UNICEFSD indicated in the previous table. This figure is automatically copied from the column “EF” in Table 6.
Programme Funding – Quantity funded by RR
It is the quantity of products intended to be procured using UNICEF “Regular Resources” funds. Only enter the quantities here if you have sufficient funding to procure the product using RR.
Programme Funding – Quantity funded by OR
It is the quantity of products intended to be procured using UNICEF Programme “Other Resources” funds. Only enter the quantities here if you have sufficient funding to procure the product using OR.
Programme Funding – OR Funding Source
It is the name of the source providing the OR funds e.g. ECHO, CDC, CIDA. Where the source is not determined yet, please write “unknown”.
Procurement Services – Quantity funded through PS
It is the quantity of products that is intended to be procured through Procurement Services. Only enter the quantities here if you know the Government has sufficient funding to procure the product using PS.
Procurement Services – PS Funding Mechanism
It is the name of the source providing the funding. When the Government utilizes specially targeted funds (e.g. World Bank, ADB) please indicate the actual source of the funds (e.g. MoH/World Bank, MoH/ADB). In case it is not determined yet, please write “unknown”.
Quantity funded by GAVI
It is the quantity of vaccine or devices intended to be procured in 2012that is funded by GAVIas submitted and approved.In certain instances the funding for a particular vaccine or device is split between GAVI funded and other funding.
Quantity Unfunded
The quantity shown in the last column represents the amount of the required products which are unfunded. This is calculated by a formula.
Section 4 – Shipment Plan for 2012 and Forecast Quantities for 2013 (by Quarter) and 2014–2016 (annual forecast demand)
General Description:
This sectioncontainstwo tables: the Table 8for the Vaccines& Deworming drugsand the Table 9forthe Devices. The shipment plan is recorded by month for the first coming year, by quarter for year+2 and by annual demand for the last 3 years.
General Comments:
- The product you select from the drop-down menu in Table 1& 4 for the vaccines and deworming tablets, and 6 for the devices will be shown automatically in Table 8 & 9.
Tables 8 and 9 – Shipment plan for Products procured through UNICEF
Table 8: 2012: Shipment Plan for number of Doses/Tablets to receive and requested timing of receipt
Table 9: 2012: Shipment plan for number of Pieces to receive and requested timing of receipt
The quantity filled in under the “Total Number of Doses/Tablets to be procured through UNICEF SD”in the Section 3, should as well be recorded in the shipment plan underits expected time of arrival into the country. The quantities can be split into severalshipments.
Please note that the “Total Shipment 2012” figure (column “P”) in Section 4should equal the “Total Number of Doses/Tablets to be procured through UNICEF SD”in Section 3 (column “EF” in table 6). If not, a warning message will appear.
These tables provide us with important shipment information regarding:
- the expected time of arrival into the country and
- the size (quantity) of each shipment
It is important that this data is as realistic as possible. Inaccurate data may lead to delays in the delivery of the products. When preparing the Shipment Plan, please take into account the following factors:
- fundings availability
- in-country Cold Chain storage capacity
- supplier lead-time
- transport time (sea freight takes 3 months from receipt of funds)
Q1 2013 – Q2 2013 – Q3 2013 – Q4 2013
These columns should contain the total quarterly quantities that you expect to procure through UNICEFSD during these quarters. This quarterly split of the annual requirement is necessary to ensure the continuity of supply for the following year.
When preparing the future forecast, please take into account any changes in your country’s immunization schedule, such as phasing in or out of products.
2014 – 2015 – 2016
These columns should contain the total annual quantities that you expect to procure through UNICEFSDduring these years.
Section 5 - Notes
This section consists of 3 general comments.
Section 6 – Comments to the Country Office regarding new syringe options
This section is to give new information to you for better supply arrangements.
Section 7 – Comments from the Country Office
Please use this section to write any additional comment, request, and information.
Please do not leave any blank lines between comments andwrite in the column A.
The Forecast Spreadsheet Layout for Cold Chain Equipment.
The forecast spreadsheet is split into 4 sections.
- Section 1 focuses on what procurement mechanism and what funding source will be used. It consists of 2 Tables to be completed.
- Section 2 focuses on the shipment information. This records information on the shipment size and arrival date. This section requires as well information on the forecasted quantities for the years to come.
- Section 3 contains additional notes regarding the forecast.
- Section 4 provides space for the country office to make comments regarding the forecast. Please fill in any comments you would like to make regarding the forecast under this section. When making notes please note the table you are referring to.
Section 1 – Procurement and Funding of Programmatic Needs
General Description
This section records data on:
- the actual quantities to be procured,
- whether these quantities will be procured through UNICEF Supply Division or outside UNICEF
- and the expected source of funding.
There are 2 tables:
- Table 1 – “Procurement Quantities and Mechanism”, in which the total quantity to be procured is automatically calculated as the sum of quantities to be procured through UNICEF Supply Division and outside of UNICEF Supply Division.
- Table 2 – “Funding Source information for Procurement through UNICEF SD”, in which the funding source is to be recorded for the products to be procured through UNICEF Supply Division.
Table 1 – Procurement Quantities and Mechanism
Total Number of Units to procure
This is the total quantity of each product that you intend to procure for the year.
When calculating this quantity, please take into account the following factors:
- the model needed
- the actual stock quantity
- the incoming shipments
- the product needsfor the remainder of 2011
Number of Units to be procured through UNICEF SD