Field Manual #3
Contents
1. Pages Macros tblEst EH Countryvariety xls tblEstWweight Modiss tbIICPSPD tblIPC tblISIC tblitemweigh tblPrice tblProd tblProdWeigh tblReport tbISPD tbiuserDef Hep fT RW Import My Network version Places Files of type microsoft Excel xls v g Groups Favorites HEBGGGGOGGGGOGOGOOEOOEOOOTHCEEE 20 From the import dialog box in the files of type select Microsoft Excel xIs Select CountryVariety xIs that should have been created and saved Snapshot 14 then click import 13 21 Append the worksheet in the existing tbICountryVCode Snapshot 15 Microsoft Access Zanzibar CPI Database Access 2002 2003 file format pl Le Import Spreadsheet Wizard You can store your data in new table or in an existing table Where would you like to store your data ueries s In a New Table Forms tbiCountryCode Reports Pages Macros hailand Modules heat flour Maize Flour azam Unga 7 Sorghum Flour Unga wa Favorites aby food Excluding mi Sorghum grain Mtamail 22 When finished close Microsoft Access 1 6 Adding product and variety to each outlet 23 The next step is to define in each outlet the product s and varieties Note there is no easy way to import the varieties for each outlet since the system is building relationships and integrity That is a Serial Number SN is being assigned to each product and variety Click EN2. Ad Consumer Price Index Compilation Model Collect Compilation Information EN E File Edit Data Utilties Report Options Window Help EN EN fe 3 elga x ese alele eiss Compilation Method Imputation Formula Imputation Period C Jevons Laspeyres Preview Weight Ref Date 12 2005 f By Product Base Period tO 12 2008 Ge Jevons Geo Laspeyres Compile By Region By Outlet Download Current Period t Detect Outliers vw Two Stage Index Previous Period t 1 Include Imputed v Index and Price Cancel Rebuilding the array please wait Gute Lever Code Seege wo uaseries Prev Prco See 12 2005 06 2009 07 2009 gt 1 GAOSS DOMESTIC PRODUCT 2 11 INDIVIDUAL CONSUMPTION EXPENDITURE By HOUSEH 3 11 01 FOOD AND NON ALCOHOLIC BEVERAGES 4 11 01 1 FOOD 5 11 01 11 Bread and cereals G 11 01 11 1 Rice 5311010 H 11 01 11 1 01 Rice RICE 467603818 33 5311010 H 11 01 11 1 Dir Thailand 467603818 33 500 00 1000 00 S00 00 5321025 2 11 01 11 1 DI Rice RICE 467603818 33 5321025 H 11 01 11 1 Dir Thailand 467603818 33 700 00 900 00 900 00 5326021 H 11 01 11 1 01 Rice RICE 467603818 33 326021 H 11 01 11 1 Dir Thailand 467603818 33 480 00 S00 00 S00 00 541001 TI 11 01 11 1201 Rice RICE 467603818 2 5411001 H 11 01 11 1 Dir Thailand 467603818 33 500 00 1000 00 900 00 5511001 7 11 01 11 1 UI Rice RICE 467603818 33 5511001 H 11 01 11 1 Dir Thailand 467603818 3
3. Dutlet 1 LI a Ix Si File Utilities Report Options Window Help F X ag Ie X BB lede PE UKIE Create Outlet Outlet ID Outlet ID Coding Outlet Name Area Name Weights Urban _ Batch EdivUpload_ ide oe Area Description Add Edit Area Area Name Compilable parene te Outlet Information 5 Define the Area that will be covered in the CPI This information will determine how many regions or stratified indexes will be produced For example a country may cover and include urban rural lower income middle income upper income areas Snapshot 3 Note comparing the CPI for an area to another bnsumer Price Index Compilation Model Add Edit Area to the System le Edit Data Utilities Report Options Window Help area index gives an ENE EE Bic ale m 3 eo indication of difference ea Information has been saved to the database _ among the areas rates of comparisons indicate Zanzibar Urban CPI Add whether over time prices Zanzibar Rural CPI of similar items that Delete consumers in one area tend to buy have risen SE 3 more or less rapidly than Sens eit ihe em er the prices of items that consumers in another area Refresh tend to buy These comparisons DO NOT Cancel indicate whether the average level of prices in an area is higher or lower than the average level of prices in another area Click Save then Cancel to return to CPI Main Wind
4. AR File Edit Data Utilities Report Options Window Help alaale x ee 8 age BBS eld 93 OECD Product Coding System at 7 detail level Total 129 records Select Level M11 01 11 1 01 7 RICE I Display Mixed Level M11 01 11 2 01 7 FLOUR 11 01 11 2 06 7 OTHER CEREAL SOLD AS GRAIN M11 01 11 4 02 7 SCONES BISCUITS M11 01 11 4 16 7 BAKERY PRODUCT wi Urban 11 01 11 5 01 7 PASTA Rural 11 01 12 1 09 7 Beef non specific cuts M Total M11 01 12 3 05 7 LAMB MUTTON AND GOAT M11 01 12 4 01 7 WHOLE CHICKEN 11 01 12 5 01 7 ORGAN MEATS E M11 01 13 1 01 7 FISH M11 01 131 02 01 132 7 SEAFOOD M11 01 14 1 01 7 FRESH MILK M11 01 14 2 01 7 POWDER EVAPORATED CONDENSED MILK NIDO M11 01 14 4 01 7 EGGS IN SHELL M11 01 15 2 01 7 MARGARINE M11 01 15 3 02 7 COOKING AND OTHER EDIBLE OIL M11 01 16 1 01 7 CITRUS FRUITS M11 01 16 1 02 01 1 6 2 7 BANANAS 11 01 16 1_07 01 1 6 7 7 Tropical fruits M11 01 16 2 02 7 NUTS AND SEEDS M11 01 17 1 01 7 LEAF AND STEM VEGETABLES 11 01 17 1_02 01 1 7 2 7 Cabbages and cruciferous vegetables M11 01 17 1 03 01 1 7 3 7 FRUIT VEGETABLES M11 01 17 1 04 7 PULSES M11 01 171 05 01 1 7 4 7 ROOT CROPS NON STARCHY BULBS AND MUSHROOMS M11 01 17 2 01 7 POTATOES 11 01 18 1 01 7 White sugar M11 01 18 2 01 7 JELLY JAM PRESERVES MARMALADE FRUIT BUTTER 11 01 18 2_03 7 Honey 11 01 1931 09 7 SALT Generate Add Area ina nasa SSG M11 01 21 1 01 7 COFFEE _ Refresh _ Cancel M11 01 21102 01212 7 TEA 11 01 22
5. East Africa Regional Technical Assistance Center UPLOADING EXCEL WORKBOOK INTO THE PRICE INDEXES PROCESSOR Field Manual 43 NAT g Compiled by A Mr Iddi H Makame East AFRITAC INTERNATIONAL MONETARY FUND Zra RY REGIONAL TECHNICAL ASSISTANCE CENTER Selected Acronyms Ccode COICOP CNote Ctitle Digit ID IsActive HBS Ocode Otitle Onote PIPS PPS SN SPD Stitle tbICPSPD tbICountryVCode Country Code found in the SPD table Classification of Individual Consumption by Purpose Product note explaining the product found in the SPD table Title of product based on COICOP found in the SPD table Level of COICOP detail Product Identification found in the SPD table The Variety is Active found in the SPD table Household Budget Survey OECD Product Code found in the SPD table OECD Classification Title found in the SPD table OECD Product note explaining the product Price Index Processor Software Point of Purchase Survey Serial Number assigned to each product and variety Structured Product Description SPD title for products Microsoft Access table used to customize country s products A Dictionary used to create and define local products VParent Product defined in Microsoft Access table VCode Variety Code defined in Microsoft Access table VDesc Variety Description defined in Microsoft Access table iii FOREWORD This manual is the outcome of a j
6. 01 11 2 01b FALSE 11 01 11 2 O1c FALSE 11 01 11 2 01b FALSE 11 01 11 2 01b FALSE 11 01 11 2 01b FAISF 11 01 1172 th Dec 2005 Description Base January06 February06 March06 A Imported Rice Basmat 910 1000 1000 1000 Loacl Rice Pishori 1001 1100 1000 1000 Local Rice Kitumbo 910 1000 1000 1000 Imported Rice Basmat 910 1000 1000 1000 Loacl Rice Pishori gs50 1000 1000 1000 Local Rice Kitumbo 910 1000 1000 1000 Maize Flour 910 1000 1000 1000 Wheat Flour S10 1000 1000 1000 Sorgum Flour 910 1000 1000 1000 Maize Flour S10 1000 1000 1000 Wheat Flour 910 1000 1000 1000 Sorgum Flour 720 SA SA SR Sorghum grain Mtama 720 SA SA SR Maize Flour azam Unga 720 SA SA SR Maize Flour azam Unga SEN 700 00 SR Kdaize Flour azam naa FR ANN ANN AAN Next to enter the price information into the system go to Utilities in the PIPS and select Create Excel for Update 19 Snapshot 21 onsumer Price Index Compilation Model Main Manu File Edit Data SEER Report Options Window Help kl E NL Open Blank Worksheet 4 E Create Excel For Update Chr Item Weights Upload Template P ri Review Coding System Global Distribute Item Weights Global Impute Product Weights E Review Item WE Distribution Distribute Area Weight Database Archive Update Database Table Repair and Compact CPI Database 30 Click Select All to upload all the outlets then click Create Excel Sheet Click on the icon to open the Excel spreadsheet In th
7. 01 11 2_01a 11 01 11 2 01 Wheat flour unga wa ngano Tel 11 01 11 2 01b 1 01 11 2_01 Maize Flour azam Unga wa ser 111 01 11 2 ne 11 01 11 2 01 Sorghum Flour Unga wa Mtame I Mio 14 2 Ma 11 01 112 01 Baby food Excluding milk Cen Tel 1111 01 11 2 06a 11 01 11 2 05 Sorghum grain Mtama M 11 01 141 4 _02a 11 01 11 4_02 Biscut Glucose i EA im 11 4 16a 11 01 11 4 16 Bread mkate wa boflo gel 11 071 11 5 01a 11 01 11 Sr Di Spaghetti Tambi Je 11 01 12 1 09a 11 01 12 1 og Meat with Borns Nyama na mfu EI I 11 01 12 1 09b 11 01 12 1 09 Meat without Borns Nyama safil A 01 12 3 05a 11 01 12 3_ 05 Goat Meat bel Di 11 01 12 4 Ola 11 01 12 4 01 Chicken ikuku aliechinjwa bk 111 01 12 5 Dia 11 01 12 5 01 Organ Meat Utumbo 11 01 13 ps 11 01 13 1 01 King Fish Nguru H Im 191 016 11 01 13 1 0 Tuna Jodari kl Wii om 1 01c111 01 13 1 01 Emperors Changu 8 H 11 01 1341 md 11 01 13 1 01 Sword Fish Nduwaro 18 Remember IsActive means that price information for this item variety is available Save the spreadsheet as CountryVariety 11 Snapshot 12 EJ Microsoft Excel Example JE File Edit view Insert Format Tools Data Window Zanzibar CPI Calculation Help Adobe POF y Fy al k Arial v 10 B Els al C2 B ZS Imported Rice Basmat 11 1 O16 11 01 11 101 Loacl Rice Pishori TRUE 4 111 01 11 1 Dir 11 01 11 1 UI Local Rice Kitumbo TRUE Et 0 112 01a 1
8. 0g H Other nice products UO H Milling service 11 01 11 3 01 1 1 2 E Bread i z 11 01 11 4 0 1 1 4 6 Other bakery products i 17 25 Snapshot 19 shows how to select the varieties Remember that the varieties were appended to the products Snapshot 15 Therefore the varieties are associated with the products Snapshot 19 dl Consumer Price Index Compilation Model Products amp Varieties AN File Edit Data Utilities Report Options Window Help SIKI EIE x eEe lB alge 5905 EH Dutlet Weight Outlet ID 5311002 Outlet Name Mussa Khamis Weight Design D Choose or Create a Variety Outlet Assigned Parent Code Generate Variety Code Outlet Adjusted Parent Code 11 01 12 1 09 Add New Code o Se Description Beef and yeal Beef non specific cuts Delete Code Cancel Select a Variety Click grey button to select VarietyCode Voten Description Aetive 11 01 12 1 09a SS Meat with Borns Nyama na mfupa 11 01 12 1 09b Meat without Borns Nyama safi Product Yariet Add Product Add Variety Delete Refresh Cancel Price Input Ready SWINSTON 7417 2009 10 20 AM CAPS INS NUM A Consumer Price Inde DN Price Index Processor Za e 10 20AM 26 Once the products and varieties have been entered click Cancel to select another outlet Click Cancel to return to PIPS main menu 18 D 1 7 Entering Price Information 27 28 At this
9. 1 04 PULSES 27 11 01 17 1_05 ROOT CROPS NON STARCHY BULBS AND MUSHROOMS 28 11 01 17 2 01 POTATOES 29 11 01 18 1 01 White sugar Sheet Status 8 7 16 2009 5 08 AM caps num 13 From the Excel spreadsheet created step 10 copy and paste the information into the Excel PIPS spreadsheet step 12 Snapshot 9 Make sure all the information is entered in the correct columns 14 When finished under the file menu click Save to CPI database as shown in Snapshot 9 then click Exit To generate an Excel spreadsheet template for Weight upload click Cancel to return PIPS Main Menu PIPSVB Them Weight Data has been successfully saved to the database Do you want bo distribute the item weights 15 At this time products per outlets have not yet been assigned Therefore do not distribute the item weights when prompted in the PIPSVB Box select INO II JOINING WORKSHEETS 1 5 Appending worksheets 16 To calculate the price index prices are usually entered for each product at the variety level Therefore an Excel spreadsheet should have been created that has variety ies assigned to each product Varieties are usually found at level 8 of the COICOP hierarchal structure For example 11 01 11 1 01 is the level 7 for rice and 11 01 11 1 O1q level 8 is one type of variety for rice Snapshot 10 Snapshot 10 Ej Microsoft Excel Example SI File Edit wiew Insert Format Tools Data Window Zanzibar CPI Calculation He
10. Hafidh Rajab Chief the Government Statistician Office of the Chief Government Statistician Zanzibar INQUIRIES For further information about this publication and related statistics contact the Chief Government Statistician Office of the Chief Government Statistician P O Box 2321 Zanzibar United Republic of Tanzania Phone 255 24 22 31 869 Fax 255 24 22 31 742 Email zanstat ocgs go tz PREAMBLE BY THE EAST AFRITAC COORDINATOR After years of testing the IMF developed Price Index Processor Software PIPS the Zanzibar Office of the Chief Government Statistician OCGS decided to take the decisive step in 2010 to set up and use the PIPS for calculating the CPI To pass on the knowledge it has gained in this process the OCGS with guidance from East AFRITAC decided to document its experience by publishing this East AFRITAC field manual with a view to helping other CPI compilers in the East Africa region and elsewhere This field manual Uploading Excel Workbooks into the Price Index Processor aims to assist compilers who have already saved and formatted their data in Excel for their CPI calculation and who would like to import their data into the more robust PIPS The PIPS is an application developed software which simplifies sound CPI estimation analysis and report generation The PIPS also follows international best practices for calculating CPIs For example the PIPS classifies the index according to the Classif
11. and for each COICOP group For example the overall CPI in Snapshot 31 is 157 28 while the CPI for the health group is 151 37 Caution to Users of this Guide The procedures provided in this Guide works best with Excel 2003 Version The PIPS system is currently being tested using the Excel 2007 In the meantime Users are urged to use other creative means of using Excel 2007 and the PIPS system 28
12. on the Main PIPS window select the outlet of interest right click on the outlet and then click Edit Product 14 Snapshot 16 Ad Consumer Price Index Compilation Model Search for Outlet atl File Edit Data Utilities Report Options Window Help aldat Bs gt 2 age Sale SE Show Last Column By Search by Outlet ID f Area eight Search by Key Words Area Compilable Go C Search by Area DW District Data Collector C Search Alphabetically A B E E Bek Ee KT een Zb BEREAN e l UM WOA TE Ze List All Outlet Search by Last Column Outlet Information total 267 records right click for editting Deag TI Bitte Te oT oles 5311001 Hamadi Ali Ee Urban Urban 531100 i CF Urban Urban 23110 eit Price uma Machano 1 BS Urban Urban 5310 vuai 4 Urban Urban vam Die Abdalla Ussi 1 a Urban Urban 531100 Refresh sa Vuai 4 Urban Urban 531100 clone Outlet fikate RS Urban Urban 531101 Ir Ali Vua G Urban Urban Delete Outlet 53110 li M kwerekwe Hardware 3 Urban Urban 531101 24ve Grid atuma Mwanakwerek we Gq Urban Urban 5311013 Haji Ali ES Urban Urban 5311014 kMzee Hamadi madafu Ca Urban Urban 5311015 Ahmed Saidi 4 Urban Urban 15 Snapshot 17 J Consumer Price Index Compilation Model Products amp Varieties Ed x St Fie Edit Data Utilities Report Options Window Help Gi EIR ILIE x amp le B E eee E EE Dutlet Weight Outlet ID Outlet Name Jo
13. point all the outlets with their products and varieties should have been entered Recall also those weights for each product were also entered Snapshot 7 paragraph 10 More importantly the price information base price previous price and current price for the varieties should also be entered Before entering price information into the PIPS make sure the Excel spreadsheet with price information is organized according to at least Outlet ID Outlet Name Variety Code Description base price previous price and current price Snapshot 20 Columns B E and G are very important Snapshot 20 Microsoft Excel Example JE File Edit View Insert Format Tools Data Window Zanzibar CFI Calculation Help Adobe PDF Ree TEEN EEE SE IDE 4 4 fy 100 e Type question i Aa abl at FER 8 Sl 29 Ou Outlet ID 5311010 5411001 5612001 5326021 5511001 5521025 5311010 5411001 5612001 5611001 5321025 5311010 5411001 5612001 5326021 R511001 Outlet Name Armour Al Yual Bargisi Saleh Daria Hamduni Store Mecha Khamis Zakaria Store Amour Ali Vual Bargisi Saleh Diarial Mecha Khamis Zakaria Store Amour Ali Yual Bargisi Saleh Dariai Hamduni Store h rha Khamis IsProd Code FALSE 11 01 11 1_01a FALSE 11 01 11 1_01b FALSE 11 01 11 1 Dir FALSE 11 01 11 1 O1 FALSE 11 01 11 1 Ob FALSE Me IFALSE 11 01 11 2 Ola FALSE 11 01 11 2 01b FALSE 11 01 11 2 Olc FALSE 11 01 11 2 Dla FALSE 11
14. 00 33 33 VER 11 01111010 Rice Grade 3 mn 3000 00 33 Mo o D 39 The last outlet Shoppers 2003 was assigned a weight of 9 000 shillings since the outlet provided prices for all three varieties of rice 24 Snapshot 28 4 Consumer Price Index Compilation Model Update Outlet Weight Using Regional Census Data if File Edt Data Utilities Report Options Window Help Half AGES Xi BED age ar Outlet Weights Update Please Input a new area weight in Allocate Wt column It will then be Weight Date UI 12000 d re distribed Won Jeck der We Guo bit Se wei H Urban 36000 LU 3 Shoopers 2003 25 003 000 ben O02 3 Shoopers 2002 30 564 11000 koae i UU D Shoopers 2001 dd day 16000 40 Snapshot 28 shows the shares of each outlet after the total weight for the basket has been distributed In summary Table 3 Table 3 Meme MG SM VPN Meer TTT mn mn sd ee Merg ees SE eng 25 H 1 9 Checking for Data Consistency 41 It is important to check the data for errors each time a change is made therefore click Data Check on the PIPS main menu Snapshot 29 4 Consumer Price Index Compilation Model Data Diagnosis and Data Check MEIR AR File Edit Data Utilities Report Options Window Help ex Sall JI ael PE see sjan eaj asle Check Data Error Found Total 7 Errors Data Diagnosis and Error Check Report Refresh Outlet ID Dutlet Name Produ
15. 1 01 1102 01 Maize Flour TRUE E 11 01 11 2 01b 11 01 11 2 01 Wheat Flour TRIJE 11 07 11 2 01c 11 01 11 2 01 Sorgum Flour TRUE 18 111 01 11 2 06a 11 01 11 2 06 Sorghum grain Mtama TRUE ER TLOT 11 4 02a 11 01 11 4 D02 Biscut Glucose TRUE 11 01 11 4 Tha 11 01 11 4 16 Bread mkate wa bofloj TRUE 11 01 1154 01a 11 01 1154 01 Spaghetti Tambi TRIJE 11 01 12 1_05a 11 01 12 1_09 Meat with Borns Nyama na mfupa TRIJE 13111 01 12 1 09b 11 01 12 14 09 Meat without Borns Nyama sati TRUE 19 Next use Microsoft Access to import CountryVariety from the created Excel speadsheet Open the CPI Access Database which was the database created in Snapshot 1 Default Database File Location 11 1_014 fimported Rice Basmat I Rice Basmat and Default CPI Database i e CPI mdb On file menu click Get External Data then import Snapshot 13 EI Microsoft Access View Edit 1 ZanzibarCPI2009zanzibar CDI mdb Insert Tools window Help Ctrl M CirlH 0 Import 09 Link Tables 2 Program Filesi CDI Example mdb 3 Developer 2 0091CPI VB DatalTestIndex mdb 41 system cpi mdb wt We ai 12 Snapshot 14 H d Objects Zb tbl rea Look in B CPI software v a x CN FE Tools tbICOICOP El correct CPI calc xls tblCountry Si outlet for testing xls My Recent COICOP test xls tblCountry Cq Documents _ weights xls Forms Reports
16. 1 01 7 Mineral waters bd 12 Remember to select level 7 and then click the Generate to create an Excel spreadsheet Snapshot 9 will appear From this spreadsheet delete all the information except for the column headers Snapshot 9 Consumer Price Index Compilation Model Edit Reveiw Item Weights 9 sl Edit Insert Format Tools Help Hell FF a e win Aoa alela sag sm Save Ctrl S 24 4 Save s Isi B S U amp 7 Save to CPI Database P 11 01 12 1 09 11 01 12 3 05 10 11 01 12 4 01 11 11 01 12 5_01 Beef non specific cuts LAMB MUTTON AND GOAT WHOLE CHICKEN ORGAN MEATS Print Preview c D E F G H l J Print Ctrl P Description Urban Rural Total RICE Exit FLOUR 4 11 01 11 2 06 OTHER CEREAL SOLD AS GRAIN 5 11 01 11 4 02 SCONES BISCUITS 6 11 01 11 4 16 BAKERY PRODUCT 7 11 01 11 5 01 PASTA 8 9 12 11 01 13 1 01 FISH 13 11 01 13 1 02 SEAFOOD 14 11 01 14 1 01 FRESH MILK 15 11 01 14 2 01 16 11 01 14 4 01 17 11 01 15 2 01 18 11 01 15 3_02 19 11 01 16 1 01 20 11 01 16 1 02 21 11 01 16 1 07 22 11 01 16 2 02 23 11 01 17 1 01 24 11 01 17 1 02 POWDER EVAPORATED CONDENSED MILK NIDO EGGS IN SHELL MARGARINE COOKING AND OTHER EDIBLE OIL CITRUS FRUITS BANANAS Tropical fruits NUTS AND SEEDS LEAF AND STEM VEGETABLES Cabbages and cruciferous vegetables SSS SY zl 25 11 01 17 1 03 FRUIT VEGETABLES 26 11 01 17
17. 3 500 00 1000 00 900 00 27 44 Snapshot 31 shows the imputation results These results can be exported to Excel Snapshot 31 Ad Consumer Price Index Compilation Model CP Index Report IEN File Edit Data Utilties Report Options Window Help oF X ERJEN x eee elelel san STPRs Period 06 2009 0772003 User ID Developer LTPRs Period 12 2005 07 2009 Computer Name MEZIFA EADS Nee Show Report at Save to HTML Formula Method Jevons Geo Laspeyres By Product G to DE E ave to Report Aun at 117772009 9 09 25 PH Cancel Total 15 records 1 1 GROSS DOMESTIC PRODUCT 100 77 157 28 157 28 d 11 INDIVIDUAL CONSUMPTION EXPENDITURE Br HOUSEHOLDS 100 77 157 26 157 25 3 11 01 FOOD AND NON ALCOHOLIC BEVERAGES 100 89 36 98 167 54 11 02 ALCOHOL BEVERAGES TOBACCO AND NARCOTICS 106 50 1 84 161 67 3 11 03 CLOTHING AND FOOTWARE 100 57 WEI 126 24 3 11 04 HOUSING WATER ELECTRICITY GAS AND OTHER FUELS 100 38 24 52 155 43 11 05 FURNISHING HOUSEHOLD EQUIPMENT AND ROUTINE HOUSEHOLD M 100 22 7 56 142 60 11 06 HEALTH 100 00 ab 151 37 11 07 TRANSPORT 102 92 420 150 59 3 11 08 COMMUNICATION 100 00 2 25 93 88 3 11 09 RECREATION AND CULTURE 100 07 0 53 136 91 3 11 10 EDUCATION 100 00 1 45 130 73 3 11 11 RESTAURANTS AND HOTELS 100 00 5 7 184 54 3 VE MISCELLANEOUS GOODS SND SERVICES 100 91 323 134 09 45 In conclusion Snapshot 31 provides the CPI results The results are provided for the All ltem CPI
18. Excel 2007 go to the View menu click on the Macro drop down menu then run the macro to upload the data 33 At this point all the information necessary to compile the CPI should have been entered areas products and their weights outlets varieties and prices II ITEM WEIGHT DETERMINATION AND DISTRIBUTION i 1 8 Reviewing Item Weight Distribution 34 At this time products per outlets have been assigned and the system needs to distribute the item weights Hence Click Utilities in the PIPS menu then Global Distribute Item Weights 35 To understand how the PIPS distributes the item weight consider Snapshot 23 Products Ad Consumer Price Index Compilation Model Edit Reveiw Item Weights SW File Edit Insert Format Tools Help 2 Bl atx Ba lo a ois Dem SR H ge 431 a fo x Bl zl le A B C D E F 1 COICOP ICP Level Description Urban Total A 11 01 11 1 01 d RICE 18000 18000 3 11 01 12 1 01 H Beef and veal GROUND MMINCGEL BEEF DUU S000 4 11 01 18 1 01 d sugar hite sugar 10000 10000 5 a CPI basket that has three products rice beef and sugar with total expenditure of 36 000 shillings Snapshot 23 Note rice accounts for 50 percent beef 22 2 percent and sugar 27 8 percent For these products prices are collected from at least one of the three outlets Shoppers 2001 2002 2003 There are three different varieties of rice grades 1 2 and 3 and only one variety of beef and sugar respec
19. Weight 4 Consumer Price Index Compilation Model Products amp Varieties X Ap File Edit Data Utilities Report Options Window Help x agaat x ee l asim ois ea alse Outlet Weight Outlet ID Outlet Name Weight Design Date 01 2000 Outlet Assigned Weight 11000 Outlet Adjusted Weight 11000 Products Variety total 5 records Product Code Product Description Ja Weight A 11 01 11 1 01 Sl Rice RICE 6000 00 VER 11 0111101a Rice Grade 1 3000 00 50 vm e 11 01 11 1_01b El Rice Grade 2 3000 00 50 00 wo 00002 Shoppers 2002 29 11 01 18 10 5000 00 45 45 M 11 01 18 1_01a ER ER 5000 00 100 00 38 The second outlet Shoppers 2002 has a total weight of 11 000 shillings Each variety of rice grades 1 and 2 have been assigned 3 000 shillings each Sugar was assigned shillings 5 000 Snapshot 27 Shoppers 2003 Assigned Weight 4 Consumer Price Index Compilation Model Products amp Varieties AR File Edit Data Utilities Report Options Window Help A x S KE x lelea alea eaa elma Outlet Weight Outlet ID Weight Design Date Outlet Assigned Weight 9000 Outlet Adjusted Weight Products Variety total 4 records GN Product Code mms um we see 0 10 11 01 11 1_01 Rice RICE 9000 00 100 00 VER 11 0111101a Rice Grade 1 H 3000 00 33 33 Mo o KKK VER 11 01 11 1_01b Rice Grade 2 3000
20. ct Code Error Type Remedy 1101 Jones Bakery 11 01 11 4 O4a Warning product variety weight is not Check Item Weights Table Invoke Re calculation 1101 Jones Bakery 11 01 11 4 04b Warning product variety weight is not Check Item Weights Table Invoke Re calculation 1102 00 Mary Market 11 01 11 4_04a Warning product variety weight is not Check Item Weights Table Invoke Re calculation 1102 00 Mary Market 11 01 11 4 D b Warming product variety weight is not Check Item Weights Table Invoke Re calculation 1102 00 Mary Market 11 01 11 4_05a Warning product variety weight is not Check Item Weights Table Invoke Re calculation 1102 00 Mary Market 11 01 11 4_05b Warning product variety weight is not Check Item Weights Table Invoke Re calculation 1102 00 Mary Market 11 01 11 4_06b Warning product variety weight is not Check Item Weights Table Invoke Re calculation 42 If there are errors messages such as those displayed in Snapshot 29 will appear Hence the errors have to be solved before compiling the index If there are no errors then the box stating no error has been found will appear PIPSVB No error has been Found with the data You can proceed with compilation process 43 When finished checking the data checks click for the PIPS main menu to compile the index Select Current Period t Compilation Method and Imputation Formula and then Preview to see the preview before compiling the index 26 Snapshot 30
21. e Excel menu go to tools then protection then Unprotect Sheet To unprotect the sheet use password 123 For Microsoft Excel 2007 click on Review to unprotect the sheet Snapshot 22 EI Microsoft Excel X000DBC xls File Edit View Insert Format Data Window Help Adobe PDF al 6 i H Save ds EJA J sinn FOG ad NI Nee ou Unprotect Sheet D i PivotTable 7 ll Berg oi Allow Users to Edit Ranges Add Ins Protect Workbook 0 Customize Protect and Share Workbook Al e fi ag sd Save Back to CPI Database 2 Outlet SN Outlet ID Outlet Name IsProd Code Description of 1101 Jones Bakery TRUE 11 01 11 3 01 BreadVWVHITE BREAD af 1101 Jones Bakery FALSE 11 01 11 3 01a slice bread f 1101 Jones Bakery FALSE 11 01 11 3 01b whole EJ 1101 Jones Bakery TRUE 11 01 11 4 04 Other bakery products CAKES AND CUPCAKES EXCLU 20 31 Once the spreadsheet is unprotected go to Data from the Excel toolbar and filter the data Filter column D IsProd False All the varieties with their prices for each outlet should be listed Copy from the Excel spreadsheet already created Snapshot 22 into the PIPS Excel spreadsheet only the base previous and current period prices Make certain the correct information is copied for each outlet and variety 32 After entering the price information click Save Back to CPI Database then exit For Microsoft
22. ection II describes how to append the worksheets created in Section I how to enter products and varieties for each outlet and how to enter price information Section Ill concludes with reviewing item weight distribution and how to check for data consistency The Price Index Processor Software is developed by the International Monetary Fund and has authorized the United Nations Economic Commission for Europe UNECE Statistics Division with whom the IMF has no other affiliation to distribute the software The IMF retains ownership rights to the original software The IMF the UNECE Statistics Division and the East Africa Regional Technical Assistance Center assumes no responsibility to users for Support or maintenance and has disclaimed all liability for any errors that may exist in the software and for any other claims relating to the software The PIPS was developed using standard Microsoft tools available to all users and only requires that users have licensed versions of Microsoft Office Suite I UPLOADING EXCEL WORKBOOK INTO THE PRICE INDEX PROCESSOR 1 1 Create New CPI Database 1 Open the PIPS Compiler On the main window click System Configuration EF File Edit Data Utilities Report Options Window Help BLO Aat x ee Bl a e Hae Configure Default Database Provider G MS Access C MS SOL Server DE Status On line Test Connection Configure DataSource Connection String Provider Microsoft Jet OLEDB 4 0 Data Douce Program Hie
23. ication of Individual Consumption by Purpose COICOP It promotes regional items and outlets price index calculations More importantly the PIPS estimates the indexes according to the Jevons geometric mean formula for the lower and higher lever indices hope that the PIPS will contribute to promoting international and regional consistency and harmonization in CPI methods estimations and calculations Even for those statistical offices that do not use the PIPS this software will at least provide the templates and guidelines for producing and calculating accurate credible reliable and transparent CPIs I would like to thank the following for drafting this Manual Mr Iddi Makame from the Zanzibar Office of the Chief Government Statistician who was the main author Ms Shelley Winston East AFRITAC Statistics Advisor for overseeing and providing technical advice and for ensuring quality also would like to thank the region s statistics offices and the IMF Statistics Department for their valuable comments Mario de Zamaroczy East AFRITAC Coordinator Table of Contents PINNE ii PORE WOR E iv PREAMBLE BY THE EAST AFRITAC COORDINATOR EE V NNN 1 l UPLOADING EXCEL WORKBOOK INTO THE PRICE INDEX DROCESSOR Ae 2 Create New CP DCSE EE 2 2 NON e de ariere sernir rre iena Enr EEEE E Nr EEN E EEEE 3 1 3 MENN 4 EG 000002 EEE EEE asin AE gue eae E ents E A TEE 7 ll JOINING WORKSHEET REE 10 15 ANNE 10 1 6 Adding product and variet
24. lp Adobe POF i Nurse Al ul a P a AL 100 ei BD ee abl wat PO i Arial s 10 mj BJ U s Go so 2E ii o s ao so SS i I HI Ml A Level Variety level vCode vDesc IsActive H RICE FALSE da 11 01 11 1 01a Imported Rice Basmat TRUE 8 b 11 01 11 1 O1b Loacl Rice Pishori TRUE amp c 11 01 11 1_01c Local Rice Kitumbo TRIJE H FLOUR FALSE da 11 01 11 2 Ola Maize Flour TRUE 8 b 11 01 11 2 016 Wheat Flour TRUE Bc 11 01 11 2 Die Sorgum Flour TRUE H OTHER CEREAL SOLD AS GRAIN FALSE H SCONES BISCUITS FALSE H BAKERY PRODUCT FALSE H PASTA FALSE H Beef non specific cuts H LAMB MUTTON AND GOAT 7 WHOLE CHICKEN D ORGAN MEATS H FISH H SEAFOOD FALSE Ha 11 01 13 1 02a King Fish TRUE 8 b 11 01 13 1 026 Tilapia TRUE gr 11 01 13 1 O2c TRUE H FRESH Mill K 17 Create a spreadsheet in Excel Snapshot 12 similar to the information shown in the Access table in Snapshot 11 The columns should be named VCode the variety code VParent the product VDesc the variety description and IsActive the variety is active 10 Snapshot 11 FI Microsoft Access tblCountryVCode Table anm gt od 11 01 11 1 01 Thailand Cp EN 11
25. name of the area provided in column D For example Unguja is the name of the urban area shown in Snapshot 4 Snapshot 5 gi Consumer Price Index Compilation Model Edit Reveiw Item Weights 9 File Edit Insert Format Tools Help E x tee bl age Hae e Dengen Ise 93 fa din d B r u e EL D EE 1 F G J H 1 Please do not change the table format You can add rows at the end of the worksheet Click Save ta CPI Database once you fi gg H Outlet ID Outlet Name Area Description Area Compilable Outlet Status 8 To save the outlets information into the PIPS compiler select file menu and then save to CPI database as shown in Snapshot 6 Snapshot 6 4 Consumer Price Index Compilation Model Edit S AE Edit Insert Format Tools Help New CN A Di S PIPSVB Open Chrl 0 Ctrl 5 hos Save Save ds E Outlet information has been save to the database E Save to CPI Database Page Setup E Print Preview C OE E pn celp E table format You BW Outlet Name E Exit 45311002 Mussa Khamis 5 5311003 Mosi Ali 9 After saving to CPI Database on file menu click exit to return to the PIPS Main Menu A message will appear saying that the outlet information has been successfully saved To check that the outlet data have been uploaded correctly click al then the list of outlets will appear If correct click Cancel 1 4 Ente
26. nes Bakery Weight Design Date Outlet Assigned Weight Outlet Adjusted Weight Product Variety Add New Records SN ProductCode Pr duetDescription Unit Weight Share Active Le enee e pressnesseessnnsnsesnucsnusanscasuasnausnsasuasnnsenuaananenneen P Add Product i Add Variety Delete CA Revised3 Price Index Inbox Microsoft Out EJ microsoft Excel g AJ Consumer Price Inde zO 4 49AM 24 Click Add Product and then click the icon to select the product At this step the COICOP Product Code Table will open Next select the product for which there is at least a variety item for collecting a price 16 Snapshot 18 1 Consumer Price Index Compilation Model Select a Product Code SE File Edit Data Utilities Report Options Window Help algat x ea e B Bel ois sa Se Search for product from OECD Classification Key words Search List All Cancel COICOP ICP Product Classification generating 1258 Records COICOP ICP COICOP Level Description GROSS DOMESTIC PRODUCT E 11 d INDIYIDUAL CONSUMPTION EXPENDITURE Be HOUSEHOLDS E 11 01 3 FOOD AND NON ALCOHOLIC BEVERAGES HE 11 011 4 FOOD 11 01 11 01 1 1 z Bread and cereals 11 01 11 01111 6 Rice i 11 01 11 2 M GE gt B Other cereals flour and other products i 01 H FLOUR 02 H PREPARED FLOUR MIKES 03 H BREAKFAST CEREAL 04 H CORNMEAL 05 H WHEAT 06 H OTHER CEREAL SOLD AS GRAIN 07 H Cous Lous
27. oint effort by the Office of the Chief Government Statistician OCGS Zanzibar and East AFRITAC to produce a user s manual for compiling the Consumer Price Index CPI The fundamental features and contents presented in this manual aim at strengthening the compilation of the CPI by using a system known as the Price Index Processing Software PIPS This compilation software is essential for producing CPI estimates Experience shows that every country within the region has its own CPI compilation program and mainly uses Microsoft Excel Using the PIPS is an operational step for the CPI harmonization in the region The Office of the Chief Government Statistician has worked closely with East AFRITAC to develop a simple user s manual for uploading Excel workbooks into the PIPS The purpose of this manual is to help East African countries that intend to use the PIPS for compiling their CPI Thus I am pleased to introduce this Field Manual prepared with assistance from the East AFRITAC Statistics Advisor Dr Shelley Winston Indeed the success of this publication stems from a concerted effort and cooperation among staff members from the Price Statistics Unit under the Economic Statistics Section of the OCGS who piloted the PIPS for about three years I would like to extend special thanks to the East AFRITAC Statistics Advisor for her encouraging role and great contribution to this endeavour with technical support advice and encouragement Mohammed
28. ow i 1 3 Outlet Information 6 Organize the CPI outlets information in a Microsoft Excel worksheet as shown in Snapshot 4 This worksheet is formatted exactly as the PIPS processor format Snapshot 4 El Microsoft Excel Book JEg File Edit view Insert Format Tools Data Window Help Adobe PDF LUG id S M JA Kar EE EECH Aria 10 B Z 0 4 Outlet ID Outlet Name Area Descriptior Area Compilable Outlet Status 5311001 Hamadi Ali Urban Unguja z 5311002 Mussa Khamis Urban Unguja 5 5311003 Mosi Al Urban nguja 5 5311004 Salum Juma Machano Urban Unguja 5 5311005 Abdi A ual Urban nguja 5 5311006 Mirisho Abdalla Ussi 1 Urban nguja 5 5311007 ual Issa Vual Urban Unguja z 5311008 Juma Mikate Urban Unquja z 5311010 Amour Alt ual Urban Unguja x 5311011 Al Hilali M kwerekwe H Urban nguja 5 5311012 Asha Fatuma Mwanakw Urban Unquja 5 5 5311013 e All Urban Unguja a 7 Once finished on the PIPS main window click add New then Batch Edit Upload to upload the outlets information An Excel spreadsheet shown in Snapshot 5 will open The information from the Excel spreadsheet created Snapshot 4 can then be copied and pasted into the PIPS spreadsheet Snapshot 5 Note the columns include Outlet ID Outlet Name Area Description Area Compatible and Outlet Status Keep in mind that Area refers to urban rural etc depending on how the expenditure weights were determined Area Compatible refers to the
29. ption Unit Weight Share Active J 11 01 11 1_01 Sl Rice RICE 3000 00 18 75 11 01 11 1_01a Rice Grade 1 mz 3000 00 100 00 WM 2 B 11 01 12 1_01 SI Beef and veal GROUND MINCED BEEF Me 8000 00 50 00 8000 00 100 00 we KIKE 11 01 121 Dis Beef 1 Kg mm Qenan 5000 00 31 25 e 18 5 WM SIS W118 101s Suga 5000 00 100 0 22 36 To determine how the weights are assigned to Shoppers 2001 Snapshot 25 suppose the three outlets Shoppers 2001 2002 and 2003 provide prices for three products rice beef and sugar and all three outlets provide a price of 3 000 shillings for grade 1 variety rice Then based on the allocation mentioned in paragraph 35 the PIPS assumes Shoppers 2002 and Shoppers 2003 provide prices for grades 2 and 3 rice varieties and Shoppers 2003 provides prices for all three grades of rice Furthermore Shoppers 2001 is the only outlet that provides a price for beef hence it was allocated 8 000 shillings for beef Shoppers 2001 and 2002 both provides price for sugar therefore each outlet was attributed a weight of 5 000 shilling See Table 2 Table 2 Rice Grade 1 3 000 00 3 000 00 3 000 00 aa Rice Grade 2 mm 3 000 00 3 000 00 oOo Rice Grade 3 nm 3 000 00 18 000 00 Beef 8 000 00 8 000 00 Sugar 1 Kg 5 000 00 5 000 00 aa 10 000 00 37 As a result the total OUTLET ASSIGNED WEIGHT for Shoppers 2001 is 16 000 shillings 23 Snapshot 26 Shoppers 2002 Assigned
30. ring Weight 10 In an Excel spreadsheet organize the CPI weight information if possible at level 7 of the Classification of Individual Consumption by Purpose COICOP for which expenditure weights were determined An example is shown in Snapshot 7 Note Total is equal to Rural plus Urban Snapshot 7 EI Microsoft Excel Book Esl File Edit View Insert Format Tools Data Window Help Adobe PDF LSM oh S a FD a SIB e Aa abl C at i CR ER ids ep i Arial 220 BU sg GSE ir C12 SE fe Milling service 1 COICOP Code Level Description Rural Urban Total 2 111 01 11 1 01 7 RICE MEN 11 01 11 1 02 7 BOXED RICE DISHES 111 01 11 2 01 7 FLOUR 5 111 01 11 2 02 7 PREPARED FLOUR MIXES 6 111 01 11 2 03 7 BREAKFAST CEREAL 7 111 01 11 2 D 7 CORNMEAL 8 111 01 11 2 05 7 WHEAT 9 111 01 11 2 06 7 OTHER CEREAL SOLD AS GRAIN 10111 01 11 2 07 7 Cous Cous 111 11 01 11 2 08 7 Other rice products 12111 01 11 2 09 7 Milling service l 143 11 01 11 3_01 7 WHITE BREAD 11 01 11 5 02 7 BREAD OTHER THAN WHITE 15111 01 11 4 01 7 SPECIALTY BREADS 16 111 01 11 4 02 7 SCONES BISCUITS IN US 17 144 14 11 4 13 7 MUFFINS 11 Next on the PIPS main window click or go to the Utilities menu then select Item Weights Upload Template Before clicking Generate make sure level 7 is selected Snapshot 8 will open Snapshot 8 Consumer Price Index Compilation Model Generate Spreadsheet Template for Weights Uplaod
31. tively Therefore Shoppers 2001 provides prices for grade 1 rice beef and sugar Shoppers 2002 provides prices for grade 1 and 2 rice and sugar and Shoppers 2003 provides prices for grade 1 2 and 3 rice only 21 Table 1 Shoppers 2001 Shoppers 2002 Shoppers 2003 Snapshot 24 Outlets 4 Consumer Price Index Compilation Model Search for Outlet 5E File Edit Data Utilities Report Options Window Help 12 S nn x t ee aea BBS ERD Search for Dutlet Show Last Column By Search by Outlet ID Area Weight C Search by Key Words Area Compilable Go C Search by Area D l District Data Collector C Search Alphabetically A B C D ESR G H l J K L M N O P UH Sel U W Dee ist Ail duet F S arch by Last Cohn F Outlet Information total 3 records right click for editting Dutlet ID Outlet Name Notes Market Area Weights 00001 Shoppers 2001 CH Urban Urban 00002 Shoppers 2002 CH Urban Urban 00003 Shoppers 2003 Urban Urban Snapshot 25 Shoppers 2001 Assigned Weight dl Consumer Price Index Compilation Model Products amp Varieties AR File Edit Data Utilities Report Options Window Help 2 x AR xs ee Bl Bele saa ad gSe Outlet Weight Outlet ID Weight Design Date Outlet Assigned Weight Outlet Adjusted Weight Products Variety total 6 records Product Code Product Descri
32. y to each outlet nn 14 1 7 Entering Price Information 19 IL ITEM WEIGHT DETERMINATION AND DISITRIBUTION 21 1 8 Reviewing Item Weight Distribution 21 1 9 Checking for Data ConststencHy 26 vi Introduction This Field Manual Uploading Excel Workbooks into the Price Index Processor tries to address the needs and to provide assistance to countries that have tried or will try to use the Consumer Price Index Price CPI Processor Software PIPS Therefore this manual aims to provide easy to follow instructions for uploading already created Excel workbooks and databases that are formatted with expenditure shares CPI outlets products varieties and price information for uploading into the PIPS compilation system This manual hence is to be used purely as a guide to accompany the Consumer Price Index Compilation System User Guide Price Index Processor Version II Consumer Price Index Revised Version August 2009 which provides in depth details definitions and explanations Additionally this manual assumes that CPI compilers are already familiar with the PIPS system This Field Manual was drafted with screen shots or Snapshots taken directly from the PIPS to facilitate ease of understanding There are three sections with sub headings that proceed in a logical manner Section I starts by demonstrating how to create a new CPI database how to enter the areas covered how to add outlet information and how to enter weights S
33. z HI System Data HI Example mdbPersist Security Info False Configure CPI Working Environment Defalt CPI Compilation Country Tanzania Default Database File Location C Program Files CPI System D atat 3 Default CPI Database e g CPI MDB EG Vi Create New CPI Database e g County DP M Setit as Default DB Create Access DB newppi mdb Change the weight date 1 2002 jo 172002 Set Compilation Date DB Repair Compact Update DB Table Default Imputation Method Default Elementary Formula By Product Jevons Laspeyres By Region Jevons Geo Laspeyres C By Outlet a E il Apply Refresh Cancel 2 Write the name of the new CPI Database in Create New CPI Database field change the weight dates The default imputation method can be set now or at any time when needed The elementary formulae can also be set at this time or later When finished click to create the database Then click cancel to return to the main window PIPSVB Database file is already exist do you wank to overwrite it 3 If this message appears it means that that database being created already exists in that case click cancel Click the Set is as Default DB box in order for the system to always open the working database Click cancel to return to the main page 1 2 Area Description 4 From the main window click add New and then add Edit Area Add New Snapshot 2 4 Consumer Price Index Compilation Model
Download Pdf Manuals
Related Search