budgeting manual

MODULE 4 1

BARANGAY BUDGETING WORKBOOK: USER’S MANUAL

(17 FEBRUARY 2008 VERSION)

A. INTRODUCTIONA. INTRODUCTION

The barangay budgeting workbook provides a worksheet-based facility for preparing the barangay budget, particularly the Annual Investment Plan (AIP).

The worksheet has the following features:

• User-friendliness

• Internally linked

• Provides a warning for statutory requirements

• Generates indicators for gender-responsiveness

• Minimizes error – protecting formulas (to be done)

The worksheets used the forms found in the 2006 Budget Operations Manual for Barangays (BOMB).

There are several worksheets in the workbook, namely: (a) the Information Worksheet; (b) the Barangay Budget Preparation Form Worksheets; (c) the AIP Form Worksheets; (d) the Revenue forecasting worksheet; (e) the AIP Project Worksheets; and (f) the personnel time utilization worksheet. Each of these worksheets is discussed subsequently.

B. THE WORKSHEETSB. THE WORKSHEETS

Information Worksheet (InfoSheet)Information Worksheet (InfoSheet)

This worksheet provides summary information on the barangay, including name, budget year, and demographic information, to be used as input in individual worksheets. Figure 1 provides a picture of the information worksheet.

Budgeting workbook manual.indd 1Budgeting workbook manual.indd 1 1/26/2010 5:31:26 PM1/26/2010 5:31:26 PM

BARANGAY BUDGETING WORKBOOK2

Figure1. Information SheetFigure1. Information Sheet

Barangay Budget Preparation Form No. 2-4 (BBPF2-4)Barangay Budget Preparation Form No. 2-4 (BBPF2-4)

These worksheets provide the actual income and expenditure for the past (next preceding) budget year (BBPF2), the current (preceding) budget year (BBPF3), and the budget year (BBPF4). The worksheets have identical elements except that the budget preparation form for the current (preceding) year (BBPF3) divides the revenues and expenditures into actual fi gures for the fi rst six months of the year and estimated fi gures for the second six months of the year, to refl ect the availability of actual data.

Figure 2 is a picture of Income and Expenditure for the Budget Year (BBPF4).


MODULE 4 3

Figure 2. Income and Expenditure for the Budget Year (BBPF4).Figure 2. Income and Expenditure for the Budget Year (BBPF4).

Barangay Budget Preparation Form No. 4

Barangay: ABCCity/Municipality: DEF

Part A. Income TOTALBeginning Balance 10,690.37Share on Internal Revenue Collections 1,620,159.00 Share on Real Property Tax 221,988.07 Business Taxes (Stores & Retailers) 11,000.00 Shares on Sand & Gravel TaxShare on National WealthShare on EVATMiscellaneous Taxes on Goods & Services 2,200.00Other TaxesOther Specific IncomeSubsidy from Other LGUsTotal Available Resources 1,866,037.44

Part B. ExpendituresProgram/Project/Activity Personal Maintenance & Capital

Major Final Output Services Other Operating Outlay TOTALExpenses

Agricultural Services1.1 Establishment of Plant NurseryDaycare ServicesHealth and Nutrition Services 89,899.00 89,899.00Peace & Order Services 32,964.00 32,964.00Administrative & Legislative Services 814,419.74 88,083.43 75775.099 978,278.27Implementation of Development Projects (20% of IRA) 324,031.80 Implementation of SK Projects (10% SK Funds) 184,396.63 Implementation of Projects/Activities for Unforseen Events (5% Calamity Fund) 92,198.32 Other Services 92,198.32

Total Expenditures 937,282.74 88,083.43 75,775.10 1,793,966.34

Balance/Deficit 72,071.10

Prepared By: Certified By: Approved By:

______________________ _______________________ _______________________ Barangay Treasurer City/Municipal Accountant Punong Barangay

Instructions: A. Indicate in Part A the estimated income (less beginning balance for the Budget Year. B. Indicate in Part B the estimated expenditure allotment by class and by Program/Project/Activity or major Final Output.

INCOME AND EXPENDITURE ESTIMATES FOR BUDGET YEAR

AIP Form 4 (AIP F4)AIP Form 4 (AIP F4)

This worksheet provides a summary of the barangay’s priority development projects, funded out of the Development Fund (at least 20% of internal revenue allotment or IRA)



Figure 3. Projects Funded from Development Fund (AIP F4)Figure 3. Projects Funded from Development Fund (AIP F4)

AIP Form No. 4

Barangay: ABCCity/Municipality: DEF

TOTAL IRA for Barangay 1,620,159x 20% 324,032

Priority Developmet Projects funded by the 20% of IRA

Project Description (1)

Nutrition Program 15,500 15,500Sanitary toilet 75,576 91,076Education Program (Conditional Cash transfer) 103,474 194,550Livelihood (animal dispersal) 126,454 321,004Unallocated 3,027 324,032

Instructions:

(3) Indicate the total project cost that will complete the project.(4) Add all project costs from Rank 1 to the last rank equivalent to the 20% of the IRA or higher.

(2) Indicate in this column the ranking of development projects in their proper order, Rank 1 is the first priority, Rank 2 is the second, etc.

RANK (2)PROJECT COST

(3)CUMULATIVE

TOTAL (4)

PRIORITIES FOR DEVELOPMENT PROJECTS(20% OF IRA), BY ____________________

Note: Formulated and endorsed in a BDC planning workshop conducted last ______________________. For approval by the SB.

(1) Describe the project to be implemented like construction of a Day Care Center, acquisition of a computer etc. in their order of priority.

AIP Form 5 (AIP F5)AIP Form 5 (AIP F5)

This worksheet summarizes a barangay’s priority development projects to be funded from external sources.

AIP Project Budget (AIProj?)AIP Project Budget (AIProj?)

This worksheet provides the costing details of each AIP project. This is the heart of the budgeting workbook.

Worksheet Sections. The worksheet is divided into fi ve sections (A to E). Section A identifi es the fi nancing options. Each fi nancing option is contained in a column. Section B provides the costs. Costs are disaggregated into personnel (B1), material (B2), and capital expense (B3). B4 simply sums B1, B2 and B3. Section C provides the distribution of the budget across local funding (Barangay Development Fund), Municipal/City Development fund, and other external funding. Section D provides the details of the computation of per unit cost. Together with the number of prospective benefi ciaries, this computation provides the basis for budget allotments thereby improving the transparency of fund allocations. Finally, Section E provides the sex disaggregation of the prospective benefi ciaries, to provide some prospective incidence analysis. If the service to be provided is at the individual level, e.g. nutrition intervention


MODULE 4 5

or education intervention, then CBMS data on these outcomes provides the disaggregation by sex of the intended benefi ciaries. When the services to be provided are for households, e.g. toilet facilities or livelihood programs, then it will be assumed initially that the household members will equally benefi t from the program. Thus, the sex disaggregation of the household members benefi ted is used. This will provide a direct measurement of the benefi ciaries of the programs/projects being proposed. This assumption of equal benefi t can be improved if data becomes available that will allow refi nement of this assumption.

Providing figures on the number and the sex-disaggregation of the benefi ciaries demonstrates the strength of the CBMS data. It connects plans and budgets to identifi ed and quantifi ed community needs. It also provides a sex-disaggregate accounting of prospective benefi ciaries of programs, projects and activities (PPAs).

Computing Unit Cost. Computing unit cost requires the defi nition of the unit of costing. This can either be per person or per household. For instance, in the nutrition project (AIPProj1) the unit of cost is on per child basis. The number of benefi ciaries (malnourished children) is taken from the CBMS tabulation. Computing for per unit cost is usually based on administrative data on programs. In the nutrition project, the cost of the food for the feeding program is P20 and P5 is the cost of gas for cooking per day. Feeding is done 5 school days per week and will run for 12 weeks, yielding a unit cost of 1,500 per child. The personnel cost will be based on the wage of the personnel directly involved the project and the proportion of time he/she will spend on the project. Again using the nutrition program as an example, the BHW is assumed to spend one-fourth of his/her time on the project, so one-fourth of his salary will be charged to the project. Similarly, a proportional allocation will be done for capital equipment if these are used only part of the time for the project. If it is fully utilized for the project alone then this should be fully charged to the project. If the equipment is rented, the rental value should be used. If the equipment is purchased, the capital consumption allowance or depreciation would be budgeted. For instance, in the nutrition program, for cooking facilities costing 10,000 pesos with a useful life of 5 years the presumed depreciation is 2,000 per year.

Sex-disaggregation of beneficiaries. To generate sex-disaggregation for the beneficiaries one needs to determine the recipient unit of the service – a person or a household. This is also the unit of costing. For instance, in the case of the nutrition project (AIPProj1) the costing unit is per person. The sex disaggregation of would be beneficiaries is given by tabulating the CBMS data on malnourished children as it is presumed that they will be the recipients of the intervention. In the case where the recipient is a household, such as the Sanitary Toilet project (AIPProj2), one



needs to assume a distribution of the benefits. The simplest assumption is proportional to the sex-disaggregation of the members of the beneficiary household. If the specific beneficiary household cannot as yet be identified, such as in the case where projects do not intend to cover the whole subject population, the community sex disaggregation (provided in the InfoSheet) can be used as proxy. The presumption is that selection of beneficiaries is at random so that the sex disaggregation of the community is presumed to be maintained. If necessary, the attribution of benefits can be fine-tuned some more based on the programmed implementation of the project. In addition, project implementation programming can be done with desired sex-differentiated impacts as an important consideration.

Allowing for several fi nancing options. When a project has several fi nancing options, one can use different columns to defi ne these choices. For instance, AIPProj2 has two fi nancing options while AIPProj3 has three fi nancing options. The fi nancing options for AIPProj2 are 100% LGU (i.e., DF) funded or 50% LGU funded. For AIPProj3, three fi nancing options were presented, namely: (a) 100% LGU funded at 5% coverage, (b) 50% LGU funded at 10% coverage, and (c) 20% LGU funded at 30% coverage.

To enable the use financing option selections one needs to define and attach a name to the financing option data range. The financing option data range is from the A. Financing Option row to the last row in Section C and from the Financing label column to the last financing options column. In the case of AIPProj2 the financing option data range is from B3:D25. To attach a name to the financing option data range, do the following: (a) select Financing Options Data Range; (b) click Insert/Name/Define; (c) in the Names in workbook, input the name for the range (this is Financing_Option_P2 of AIPProj2); note that in the Refers to: window, the appropriate range has already been provided because the range selection had been done earlier; (d) select OK. The range has been named. The name is useful for selecting the financing options automatically.

Figure 4 provides a sample project worksheet where the sections described above are shown.


MODULE 4 7

Figure 4. Sample Project Budget WorksheetFigure 4. Sample Project Budget Worksheet

Title of Project Sanitary toilet1 A. Financing Option Option 1 Option 22 Description 100% LGU 50% LGU34 B. Costs5 B1. Personnel 12000 120006 BHW (P4000/month) (1/4 time) 12000 12000789 B2. Material 139,153 139,153

1011 No of households without12 sanitary toilet (CBMS 2006) 835 83513 Unit Cost 500.00 500.00 14 Coverage for the budget year (over 3 years) 33.33 33.331516 B3. Capital Equipment171819 B4. Total 151,153 151,1532021 C. Financing22 Bgy Development Fund 151,153 75,57623 Mun/City Development Fund 0 75,57623 External 0 0

D. Unit cost (per household) computationMaterial 500Cost of toilet bowl plus installation 400Information/Educ. campaign 100

Personel (BHW) 12000Time in the project 0.25Salary per month 4000Months 12

Capital Equipment

E. Sex-disaggregation of beneficiaries Total 1,392 Male 716 Female 675 Male/Female Ratio (Male per 100 Female) 106

AIP Project Summary (AIPProjSum)AIP Project Summary (AIPProjSum)

This worksheet provides the summary of the AIP projects both funded from the Development Fund and External sources.

Specifi c cells in this worksheet are linked to specifi c cells in the other AIPProj worksheets:

(a) Amount of development fund (B4) is linked to the development fund in the current budget year (BBPF4!E32).

(b) The sources of fi nancing column are linked to the corresponding fi nancing cells in each of the project worksheets.

The Budget Total column is merely the sum of the Development Fund and External Fund columns.



Linking Financing Cells. To get the summary of the AIP project budgets, one needs to link specifi c cells in the summary worksheet to specifi c cells in the project worksheet. The cells in the Development Fund and External Fund columns need to be linked to corresponding cells in the project worksheets. When there is only one fi nancing option, one needs to link specifi c cells in the summary worksheet to the corresponding cell in the project worksheet. For instance, the Development Fund cell for the nutrition program is linked to cell C22 of the AIPProj1. To link, just type = in the destination cell then move to the specifi c cell in the project worksheet e.g. C22 in the AIPProj1, then press Return/Enter. The data in that specifi c cell is shown in the destination cell. The link is now established. All changes in the source cell will be refl ected in the destination cells as well; this is what linked cells mean. This can be done for the External fund column as well.

In case the project has several fi nancing options, one can automate the selection of the fi nancing options by defi ning the options in the fi nancing options column.

Defi ning fi nancing options needs a listing of the options in the corresponding cell. To defi ne options do the following: (a) go to the cell where options are to be defi ned (e.g. E13 in the budget workbook), (b) Click Data/Validation, (c) in the Allow: window select List, (d) in the Source: window, type in <Option 1, Option 2>, (e) then select OK. A drop down button would be added to the cell and clicking at the button will show the option list. If there are more than two fi nancing options, input the required number. To activate the fi nancing option selection the HLOOKUP( ) function is used. The syntax of HLOOKUP function is given in Box 1.

Box 1: Syntax of the HLOOKUP function Description: Searches for a value in the top row of a table or any array of values, and then returns a value in the same column from a row you specify in the table or array. HLOOKUP(lookup_value, table_array, row_index_num, range_lookup) Lookup_value is the value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text string. Table_array is a table of information in which data is looked up. Use a reference to a range or a range name. The values in the first row of table_array can be text, numbers, or logical values. If range_lookup is TRUE, the values in the first row of table_array must be placed in ascending order; otherwise, HLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted. Row_index_num is the row number in table_array from which the matching value will be returned. A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on. Range_lookup is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. Source: MS Excel Help File.


MODULE 4 9

In the Sanitary toilet example (AIPProj2) the HLOOKUP function has the following parameters: the Lookup_valueLookup_value is given by the corresponding cell in the Financing Options column (E13 in the Sanitary Toilet project); the Table_arrayTable_array is named Financing_Option_P2 of the Sanitary Toilet project; the Row_index_numRow_index_num is 22 (see the sequence numbers in column A of AIPProj2, the corresponding number of External is 23); the Range_lookupRange_lookup is FALSE because we need not sort the options. The HLOOKUP function parameters of the External Fund cell are identical except that the Row_index_num Row_index_num is 23, rather than 22.

The Remarks column also uses the HLOOKUP function. In the example, this would refer to the fi nancing label row, i.e. the Row_index_numRow_index_num is 2.

Figure 5 provides the Sample AIP program structure. Note the HLOOKUP function in the selected cell (C13).

Figure 5. Sample AIP Program Structure (AIPProjSum) Figure 5. Sample AIP Program Structure (AIPProjSum)

HLOOKUP Formula

Revenue Forecasting Worksheets (RevFor)Revenue Forecasting Worksheets (RevFor)

This worksheet provides the forecast of revenues from the different sources. It also provides estimates of the basis for statutory allocation such as regular income or general fund.

The forecast of revenues for the budget year uses the MS Excel function TREND to automate the forecasting of revenue sources using the linear trend method. The syntax of the TREND function is given in Box 2.



Box 2. Syntax of the TREND function

TREND computes the values along a linear trend (y=mx+b) fitted through a method of least squares.

TREND(known_revenues,known_years,forecast_year,const)

Known_revenues is the set of revenue values that are available

Known_years is an optional set of year values where revenues are available

• If known_year is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_revenues.

Const is a logical value specifying whether to force the constant b to equal 0.

• If const is TRUE (non-zero value) or omitted, b is calculated normally. • If const is FALSE, b is set equal to 0 (zero), and the m-values are adjusted so that y = mx.

Source: MS Excel Help File

Figure 6. Sample Revenue Forecasting Worksheet.Figure 6. Sample Revenue Forecasting Worksheet.

Revenue ForecastNext

Preceding Preceding BudgetYear Year Year2006 2007 2008

1 Beginning balance 0.00 0.00 10,690.372 Internal Sources 213,807.34 224,497.71 235,188.073 Tax Revenues 213,807.34 224,497.71 235,188.074 Share on Real Property Tax 201,807.34 211,897.71 221,988.075 Business Taxes (Stores & Retailers) 10,000.00 10,500.00 11,000.006 Shares on Sand & Gravel Tax7 Miscellaneous Taxes on Goods & Services 2,000.00 2,100.00 2,200.008 Other Taxes9 Non-tax revenue 0 0 0

10 Permit and licenses11 Clearance and certification fees12 Business income13 Sale of assets14 Other Specific Income1516 External Sources17 Tax Revenues 1,492,912.00 1,493,168.00 1,620,159.0018 Share on Internal Revenue Collections (IRA) 1,492,912.00 1,493,168.00 1,620,159.00 19 Share on National Wealth20 Share on EVAT2122 Non-tax revenues 0 0 023 Subsidy from Other LGUs24 Loan proceeds25 Others2627 Regular Income (1+3+10+11+12+14+17+23+25) 1,706,719.34 1,717,665.71 1,866,037.4428 General Fund (26+12+23) 1,706,719.34 1,717,665.71 1,866,037.44

Statutory RequirementsSK (10% of 28) 170,671.93 171,766.57 186,603.74 DF (20% of 18) 298,582.40 298,633.60 324,031.80 Calamity (5% of 27) 85,335.97 85,883.29 93,301.87 GAD (5% of 28) 85,335.97 85,883.29 93,301.87

PS (55% of 27 of next preceeding year) 938,695.64

Unallocated 230,102.52


MODULE 4 11

Personnel Time Utilization (Personnel)Personnel Time Utilization (Personnel)

This worksheet provides an accounting of the time utilization of personnel directly involved in the AIP projects.

AIP Project Budget Form (AIPProjForm)AIP Project Budget Form (AIPProjForm)

This worksheet provides a blank form for new projects. Use this worksheet to add a new worksheet for a new project.

Adding a new project worksheet. To do this, right-click on the Worksheet tab; select Move or Copy; in Before sheet list select the worksheet where you want to pla ce the new project worksheet; check the box labeled Create a copy; select OK. A new worksheet is then added. Double click on the tab of the new worksheet to rename it. One can now input data on the new project. Once the budgeting for the new project is done, add the project to the summary worksheet (AIPProjSum) by establishing the needed links to the new project worksheet.

Figure 7. AIP Project Worksheet FormFigure 7. AIP Project Worksheet Form

Worksheet tab


budgeting manual

Documents