project budgets

Shared by: akf17920

Tags

project budget, project management, project budgeting, project manager, project budgets, project costs, project managers, managing project, how to, microsoft project, budget management, project scope, budget plan, project management software, microsoft office project

Stats

views:: 41
posted:: 6/6/2010
language:: English
pages:: 51

Public Domain

Document Sample

PROJECT BUDGETS
This unit was compiled and written by Mary Cretney
who ran workshops for CID in 2001–02

How to use this resource

Each unit in this folder consists of three elements:

The cream pages are about definitions – what and why.

The apricot pages contain some of the tools NGOs need – how.

The grey pages are the toolkit itself –
pull out reference sheets for exercises
and activities.
COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

2 PB
CONTENTS

SECTION ONE: PROJECT BUDGETS: PREPARATION AND APPRAISAL ................... 5
Project Budgets ..................................................................................................................... 7
Some Characteristics of a Good Budget ........................................................................................... 7
VASS Project Budgets ..................................................................................................................... 8
VASS Budget Requirements ............................................................................................................ 9

Budget Methods ..................................................................................................................... 9
The Planning Hierarchy ................................................................................................................ 10
Output-Based Budgetting ............................................................................................................. 11
Method One: Budget by Objectives ............................................................................................... 12
Method Two: Budget by Activities ................................................................................................. 13
Method Three: Budget by Assistance ............................................................................................. 14
Comparing Methods ..................................................................................................................... 15
A Suggested Budget Method: ....................................................................................................... 16

Preparing Budgets ............................................................................................................... 18
Software Tools ............................................................................................................................. 18
Templates ................................................................................................................................... 19
Recommended Template: Budget by Activities and Recurrent Costs ................................................. 23
Budget Information Sources ......................................................................................................... 25
Budget Preparation Hints .............................................................................................................. 25

Budget Appraisal .................................................................................................................. 26
Common Budget Flaws ................................................................................................................. 26
Budget Check List ........................................................................................................................ 27

Simple Financial Reporting .................................................................................................. 28
Using the Summary Spreadsheet as a Financial Reporting Tool ........................................................ 28
A brief narrative report ................................................................................................................. 28

SECTION TWO: BUDGET TEMPLATES .............................................................. 31
Template One: Budget by Objectives. ............................................................................................ 33
Template Two: Budget by Activities ............................................................................................... 35
Template Three: Budget by Assistance Provided ............................................................................. 35

SECTION THREE: EXCEL PRACTICE ................................................................. 37
Using Templates .......................................................................................................................... 39
Formatting Techniques ................................................................................................................. 41
Working with Worksheets ............................................................................................................. 44
Formulas ..................................................................................................................................... 45
Quick Copy Techniques ................................................................................................................ 46
Using Comments ......................................................................................................................... 47
Linking Worksheets ...................................................................................................................... 48
Printing ....................................................................................................................................... 49

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 3
COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

4 PB
SECTION 1
PROJECT BUDGETS:
PREPARATION AND APPRAISAL

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 5
COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

6 PB
PROJECT BUDGETS

A project budget is a summary of how money is to be spent on the project during a
particular time period.

Budgets are used as:

• A planning tool in the project design stage

• A management tool during project implementation. A key to good project management
is to link workplans and budget. One drives the other. Regular review (monthly) of
spending against budget will ensure there is money to complete the workplan. If not, the
workplan should also be revised.

• As a tool for monitoring and evaluation both during the project and at its completion.

Some Characteristics of a Good Budget
Presentation

• Uses generic tools

• Simple and straightforward

• Clear, easy to follow and can be read at a glance

• Provides a summary

Content

• Applicable and relevant to the project

• Links to the design framework for planning

• Realistic and reasonable. The bottom line total is in line with funding expected

• Flexible and readily adaptable. It is easy to alter if circumstances change

• Transparent / honest / nothing is hidden

• Links to a reporting system for tracking overall financial position

• Encompassing, inclusive and complete

• Can be used for forward planning particularly for cashflow

• Shows the planned time frame

• Uses currency of country project is based

• With summary there is conversion to the currency of donor country

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 7
VASS projects require detailed budgets to be provided with the project application (VASS 2
form). A simple table is provided on the form for summary budget information. See below

While the above table is a useful summary for the VASS application, it is not in a format that
is detailed enough for partners to use for budget preparation.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

8 PB
VASS Budget Requirements
The following requirements are from the VASS Handbook, from the narrative explanations
provided with the VASS 2 application form.

• All income sources should be identified and quantified (eg, VASS, the organisation
applying for funding, other donors, local contributions – including in-kind).

• All expenditure must be itemised so that the expenditure for administration, staffing and
activities can be clearly identified.

• The budget provided should be for the fiscal year for which funding is sought.

• For year one of a multi-year project, a total budget should also be provided with
indicative annual figures.

• VASS requires budget totals to be converted to $NZ.

The 2001 VASS Institution review also recommends that:

• NGOs seeking support from VASS submit budgets that are clearly linked to objectives and
itemise expenditure by key inputs.

Currently, there is some debate about whether this should become a requirement.

A further requirement is that if a project’s activities (and therefore budget) need to change
after the project has been approved. See Handbook, Section B29. This section lists the
variance limits that require approval.

BUDGET METHODS:

The VASS handbook suggests three methods by which budgets can be prepared:

1. By objectives

2. By activities

3. By assistance provided.

The first two methods are similar and can be generally described as output-based
budgeting.

Output-based budgets are those that are based on the outputs of a project. Individual
activities that contribute to each individual project output are costed and totalled to give the
budget for that particular output.

Output-based budgets may take a little more time to prepare but they have two key
advantages

• They link to the design framework (also called the Planning Hierarchy) for the project

• Generally they are better tools for monitoring and evaluation purposes.

The third method is more traditional. It lists projected spending as individual line items that
relate specifically to the type of assistance provided. eg staffing, technical support, goods,
equipment, training, etc. These budgets tend to be simpler in format.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 9
The Planning Hierarchy

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

10 PB
Output-Based Bugetting

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 11
Method One: Budget by Objectives

Examples budgets of all methods can be obtained electronically from CID.
Email to cid@clear.net.nz or download from the CID website www.cid.org.nz.

This first method shows the full plan of the project – the objectives, outputs and activities.
It has been designed to give an overall picture of the project. It contains:

• a narrative summary of the goal, objectives and outputs that follow the agreed
framework (Hierarchy) numbering system

• a list of activities for each output (one line /row for each activity)

• a budget estimate for each activity.

This system requires a three level system for budget codes and therefore a more complex
accounting system. Part of the summary page of this budget method is reproduced below.
Note the three level coding system.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

12 PB
Method Two: Budget by Activities

This method is a simplified version of method one but contains only objectives and activities
The Outputs have been removed and all activities under one objective are listed under that
objective. This system requires a simplified numbering system (only two levels) and
therefore a simplified accounting system.

Part of the summary page of this budget method is reproduced below.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 13
Method Three: Budget by Assistance

This method lists projected spending as individual line items that relate specifically to the
type of assistance provided eg, staffing, technical support, goods, equipment, training, etc.

An example is reproduced below.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

14 PB
Comparing Methods

Method Advantages Disadvantages

Method One: • Link to the design • More difficult to prepare
By Objectives framework (also called • Can become very complex if recurrent
the Planning Hierarchy) costs have to be split across too many
for the project objectives /activities.
• Good tool for • Administration and equipment
monitoring and expenses can be difficult to assign to
evaluation purposes individual objectives
• Good tool for reporting

Method Two: • Link to the design • Don’t see the full design framework –
By Activities framework (also called only objectives and activities
the Planning Hierarchy) • Can become very complex if recurrent
for the project costs have to be split across too many
• Good tool for objectives /activities.
monitoring and • Administration and equipment
evaluation purposes expenses can be difficult to assign to
• Good tool for reporting individual objectives

Method Three: • Simple to prepare • No link to the design framework
By Assistance • Easy to report against • No transparent means of showing
inputs required to achieve objectives
• Cannot cost individual objectives for
comparative purposes
• Often provide too little information
• Often no narrative detail

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 15
A RECOMMENDED BUDGET METHOD

A recommended budget method is to combine features from methods 2 and 3 to mitigate the
main disadvantages of each:

• A budget by activities can become cumbersome if recurrent costs such a administration
and equipment have to be split across too many objectives or are difficult to assign to
objectives

• A budget by assistance has no link to the design framework so there is no transparent
means to show inputs required to achieve objectives.

This budget method has two distinct sections. The first section is an activity based budget –
objectives and activities with a two level coding system. The second section gives
recurrent costs that are not easily assigned to a particular objective.

Part of the summary page for this budget method is reproduced on the next page.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

16 PB
COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 17
PREPARING BUDGETS

Software Tools
Wherever possible, budgets should be prepared electronically as computer technology allows for:

• Good presentation – formats that are easy to read

• Ease of preparation – software that will carry out the calculations and allow links within
budgets so that changes made in a primary document can be automatically carried
through to summary documents

• Flexibility – budget changes are easy to make

• Linking to accounting and reporting systems.

Spreadsheeting software has been designed for tasks such as budgeting. MS Excel is the
most frequently used package worldwide. Excel is a very powerful programme – it combines
good mathematical tools with simple database features, so that it can also be used in
planning processes, in simple accounting and for financial reporting.

Because Excel has a wide range of uses, the generic computing skills contained in this
package are particularly useful for project administrators.

Templates
A template is a document that contains all the required features for a particular task. It acts
as a pattern. Some templates have only standard formatting eg, faxes, memos and reports.
Such templates gives documents prepared by different people a standard look. Other
templates contain more complex features such as automatic calculations.

The two key advantages of templates are:

• they provide uniform formats

• they save time (usually a lot of time)

• they enable complex features to be used by people who may not have the skills to set the
features up.

Developing templates is a very effective use of time. An easy way to create a template is by
opening a document that contains the items you want to reuse, and saving it as a template in
a new file with an appropriate name for the new work you want to do.

Templates for the different types of budgets discussed in this unit are printed on the following
pages. Electronic copies are available from CID. Email to cid@clear.net.nz or download from
the CID website www.cid.org.nz. These templates provide examples of how Excel can be used
for developing a budget. They can be altered in any way to suit the needs of a specific
project.

A template for the suggested budget method is outlined and printed on the following pages.
Templates for the other three methods are outlined in Section One.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

18 PB
Budget by Objectives Template (Worksheet One)

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 19
Budget by Activities Template (Worksheet One)

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

20 PB
Budget by Assistance Template (Worksheet One)

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 21
Budget by Activities and Recurrent Costs (recommended
method) Worksheet One

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

22 PB
RECOMMENDED TEMPLATE:
BUDGET BY ACTIVITIES & RECURRENT COSTS

The recommended template file is called Budget Combined Template.xls

It is a combination of budget methods two and three and attempts to mitigate the main
disadvantages of each:

1. Worksheet One: Summary
This template gives a summary of the project – the objectives and activities in the first
section, recurrent costs in the second.

Column Detail

A Budget code number which follows the design framework numbering

B Narrative detail In the top section of the budget, Description of the objective with the activities
relating to the objective list below it.
In the second section, this is the description of the recurrent cost

C Budget Number field, no decimal places.
In the top section of the budget, this is the budget figure calculated for each activity. It comes from
the other worksheets, Objectives 1–4 – also called the underlining worksheets. These worksheets
break each activity into its component costs. To see this, click on one of the active cells in Column C
and note the formula in the Formula bar. E.g. =’Objective 1'!$D$34. This formula says that the figure
comes from the cell found in worksheet Object 1, Column D, row 34. These formulae should not be
changed as the working to obtain these figures is done in underlying worksheets
In the bottom section of the budget, this is the figure calculated for that recurrent cost. It can be
calculated directly in the cell and a comment can be added to the cell to explain the working or
assumption. If more detail is required, calculated on worksheet 6, and the total brought through by
link pasting.

D Expenditure Number field, no decimal places
This column has been included so that the budget document (once approved and money allocated)
can then become a reporting document. When a financial report is required, the expenditure for each
budget code can be calculated and the total put into this column

E Difference Number field, no decimal places
This column automatically calculates the difference between budget and expenditure for each budget
line item

F Budget figure (col C) converted to $NZ if the exchange rate is inputted into cell F5

G and H If the project is multiyear, indicative figures for years 2 and 3 should be calculated in these columns

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 23
Objective Totals

There is a summary line for each Objective so that the budget total for each objective can be
summed (or added up to give the total amount). Rows 16, 29. 42 and 55.

This summary sheet can be changed to suit the needs of a project. For example:

• Rows can be added for additional activities or recurrent cost items.

• A column can be added to show inputs (money or otherwise) provided by the partner
organisation to the project

• A column can be added to calculate the percentage of the budget funds remaining

• A column can be added to show cashflow required (eg, by month or quarters).

It is best to try and keep the summary sheet to no more than two pages and put page breaks
in logical places.

This template has been organised to print portrait and only column A – F. If it is used for a
multi-year project, the page orientation should be changed to landscape and the print area
re-defined.

2. Worksheets 2–5: Objective 1, Objective 2, Objective 3,
Objective 4, Recurrent Costs
These worksheets are where the inputs that make up each individual activity or
recurrent cost are listed and calculated.

These sheets list the individual activities for each Objective with enough rows to list the
individual items/services that make up each activity and cost them. These are called inputs.

Again, the template can be changed to meet the requirements of the particular project being
costed.

Column Detail

A Activity Number

B Details of the item you are costing – text format, wrapping.
Give as much detail as possible about each item.
Additional rows can be added if there are more than 6 items to be costed for one activity.

C Subtotal – this is the amount or calculation of the cost of the item. Number field, no decimal
places as budgets should be in whole numbers.
Excel should be used to calculate the cost of the item.
Comments can be used to explain the workings or assumptions.

D Total. Number field, whole number
When all the items that make up the costs for one activity have been listed and calculated, the
Autosum tool gives a total.
This total is then taken through to the summary worksheet using the link paste feature.
This automatic links means changing or updating the budget is very easy.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

24 PB
Budget Information Sources
Once you have decided on a budget format and have developed a template, the budget can be
constructed. Firstly, save the template as a new document with a new name. Then construct
the budget using the appropriate worksheets and information from the following sources:

• Project Workplan for year

• Field workers and other staff

• Historical data – cost of previous activities such as workshops, office running costs

• Quotations from suppliers of goods and services

• Analysis of running costs of capital items.

While some funding agencies are reluctant to pay for major capital items, most are happy to
fund depreciation costs. Often the only way to replace capital items (such as office equipment
and vehicles) is to accurately cost the depreciation/replacement into the running costs.

For example:

A partner NGO owns a four wheel drive vehicle that does 15,000km per year. It is estimated
that the cost of running this vehicle is:

Item Cost $SAT

Insurance 850

Repairs and maintenance 2500

Fuel and oil 6200

Depreciation 25% of capital cost 4250

TOTAL 13800

Running cost is therefore $0.92 per km. This figure can then be costed into relevant activities
associated with the use of the vehicle.

Budget Preparation Hints
• Consult widely with staff and field workers. Having a group session on a budget (using a
large whiteboard) is very useful. Field workers have a very good knowledge of key costs
and often think of items that office staff forget. Staff are more likely also “own” budget
decisions if they have taken part in the process

• Spent time on the task. The more time put into detail of budget will assist with delivery of
workplan. Good financial management is often a key input to successful project
outcomes. A well prepared budget, that is regularly monitored, is one key indicator of
good financial management.

Add as much detail to the budget, by way of cell comments, as possible – all assumptions
especially. These comments are very useful when monitoring spending, revising the budget
and constructing the next year’s budget.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 25
BUDGET APPRAISAL

Common Budget Flaws

Budget Flaw Suggested Solution

Huge number of different models / systems Standardise on one of templates supplied. These can be
altered to suit individual project needs or partner

Partners don’t have appropriate tools or capacity • Supply simple templates and manual for partners to use if
to develop suitable project budgets they wish
• Partner NGO provide training / assistance if requested
• Use independent local person to provide assistance

Budgets that do not adequately reflect objectives Use a budget methodology that links the budget to the project
e.g. project proposal describes a high level of design framework (output based)
community participation but budget shows Templates can be supplied to partners
majority of spending is on NGO infrastructure

Budgets that are too detailed. Need a summary Use an Excel template that gives a summary worksheet. The
that matches financial reporting categories to the detailed workings for each objective are calculated in
project budget. NGO should be able to provide additional worksheets, the subtotals of which are linked
further detail if requested. through to the summary. Workings can be easily reviewed.

Budgets that provide too little information Same solution as above
No transparent means of showing inputs required
to achieve objectives

Budgets that do not provide enough narrative Use of comments in cells and a description column that is
detail formatted for text wrapping so summary detail can be added

Output-based budgets can become very complex • Make simple assumptions or
if recurrent costs have to be split across too • Add a final objectives that relates to the effective
many objectives /activities. administration of the project or
Administration and equipment expenses can be • Add a separate section after the objectives that lists
difficult to assign to individual objectives inputs that are not easily linked to a particular objective

Splitting of activities across more than one This is a design issue and should be able to be resolved in
objective eg, advocacy. design phase

Currency fluctuations. Budgets are prepared in • Additional money has to be found or the budget revised
partner country currency. Exchange rate downwards.
fluctuations can cause major drop in actual cash • VASS Block Grant agencies should be able to manage this
transferred. through the flexibility of Block Grant system
• Non-block grant agencies can apply for additional funds if
there is a large exchange rate change between
application approval and the sending of funds

What to do if project is part of a larger • VASS2 form is clear. Block grant form needs to be altered
programme • Agreement with other funders on reporting
• Pooling is done at the objective level

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

26 PB
Budget Check List

Project is clearly identified and local contact identified

Budget is clearly dated for period it relates to

There is an easily read summary

Goals, objectives and (inputs to and outputs from) activities are clearly defined.

Budget is linked back to these by an easy / sensible coding system

There is a reasonable break down of items – enough detail but not too much

There is a system or process to assess whether the cost is realistic

There is narrative and explanation

All key activities are included. All large amounts of money are clearly itemised

The budget is accurate. Calculations and costings can be reviewed if required

The budget us transparent. Detailed workings are available if requested

The budget is in local currency. If appropriate there is conversion to donor currency
using a clearly defined exchange rate. The budget can account for exchange
rate fluctuations.

The budget be either be used as a reporting tool or linked to a reporting system

The budget can be altered if circumstances change – flexible

The budget gives an indication of cashflow – eg when capital to be spent

The budget is realistic. Work planned is appropriate for resources available
and time frame?

Administration component can be assessed. It is either part of a capacity building objective
or listed as a separate section.

The budget was produced by project team not just one person. Does it reflect
participatory process?

There has been consultation with funding agency. Do both partners own the budget?

If part of a bigger project this has been made clear

If part of a bigger project there has been networking / consultation with other donors

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 27
SIMPLE FINANCIAL REPORTING

Using the Summary Spreadsheet as a
Financial Reporting Tool
Once a project is underway, the Summary Worksheet can be used as a simple financial
reporting tool. Often this is all a donor agency requires.

Every project should have some type of accounting system; manual, partly computerised (eg
Excel spreadsheet) or a full computer accounting system. Whatever system is used, it should
be set up to give money totals for each budget line item – a total for each activity or each
type of assistance.

At appropriate time periods these totals can be inputted into the Summary Worksheet in the
Expenditure column. The difference column can then be calculated.

If this system is being used to report on a project being funded by an external agency, this
sheet (or a selected part of it) can be attached to be acquittal form as the detail. This avoids
duplication of work.

Examples are included on the following pages. These can also be requested by e-mail
cid@clear.net.nz or found on the CID website www.cid.org.nz

FR Objectives.xls
This is a Financial Report compiled using a full output based budget method. All activities are
listed under their individual objectives. Administration has been included as a Capacity
Building objective – Objective Four. The report covers the four month period Feb–July 01
giving an expenditure total for each activity.

FR Assistance.xls
A simple Financial Report based on a budget that outlines the types of assistance provided. It
has been compiled to show monthly expenditure.

A brief narrative report
It is always good to provide with the summary spreadsheet above, a brief narrative report.
A template can be set up for this report.

The report should:
• give a brief summary of the financial position – money in, money out and current position

• explain any over/under expenditure

• highlight any cash flow issues for the forthcoming period
• discuss any matters where comment or decision is required from the Governing body or
Funding Agency

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

28 PB
The following headings could be useful for a Financial Report template:

Overall Financial Position:

• Give an overall summary of financial position.

• Total income received.

• Total money expended.

• Money remaining / overspent. Convert to percentage

• Explain the key reason(s) why there is money over / over expenditure. If money has
been transferred between objectives, was approval sought for this?

Activities Completed:

• Key activities completed.

• Were these to budget?

Activities not yet completed:

• Which key activities in the workplan are incomplete?

• How much money is unspent.

• Are these activities to be completed?

• If so, when?

• If not, why not.

• Does the workplan need to be changed?

Action that might be required:

• Does the workplan need to be changed?

• If so what are the changes needed

• What are the financial implications of these changes?

• Is more money needed and if so where would this come from?

• Is less money needed, if so does money to be transferred to the next financial year?

• If so, is it to be added to next year’s budget or is the full budget being sought in addition
to transferred funds.

• Has the projected cashflow changed significantly?

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 29
COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

30 PB
SECTION 2
BUDGET TEMPLATES

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 31
COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

32 PB
These templates are available on request by e-mail to cid@clear.net.nz or found on the CID
website www.cid.org.nz

TEMPLATE ONE: BUDGET BY OBJECTIVES
The file is called Budget by Obj Template.xls

This is the most complex template as it links exactly to the design framework of that has three
levels, Objectives, Outputs and Activities. It therefore has a three level numbering system.

This budget template would be suitable for partner organisations whose project follows the
full design framework and most costs can be easily attributed to individual objectives. Staff
would require good Excel skills.

1. Worksheet One: Summary

This template gives a summary of the project – the objectives, outputs and activities
(OOA). It has been designed to give an overall picture of the project.

Column Detail

A Budget code number which follows the design framework numbering

B Narrative summary
Description of the goal and objectives, with the outputs and activities relating to the objective list
below it. It follows exactly the design framework of the project.
This template contains three objectives, each with three outputs. Four activity lines have been
provided for each output
If there is a lot of narrative detail, it should be summarised

D Expenditure Number field, no decimal places
This column has been included so that the budget document (once approved and money allocated) can
then become a reporting document. When a financial report is required, the expenditure for each
budget code can be calculated and the total put into this column

E Difference Number field, no decimal places
This column automatically calculates the difference between budget and expenditure for each budget
line item.

F Budget figure (col C) converted to $NZ if the exchange rate is inputted into cell F5

G and H If the project is multiyear, indicative figures for years 2 and 3 should be calculated in these columns

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 33
Objective Totals

There is a summary line for each Objective so that the budget total for each objective can be
summed (or added up to give the total amount).

Budget Total:
There is a summary line for the whole project which subtotals the amount of budget allocated
for each Objective. (Row 65)

Once the narrative detail has been added to this template, the calculations are
automatic and should not be changed.

This summary sheet can be changed to suit the needs of an individual project. For example:

• Rows can be added for additional Objectives or Outputs (copy and paste to repeat the
same format)

• Rows can be added for additional activities

• A column can be added to show inputs (money or otherwise) provided by the partner
organisation to the project

• A column can be added to calculate the percentage of the budget funds remaining

• A column can be added to show cashflow required (e.g. by month or quarters)

It is best however, to try and keep the summary sheet to one page wide (use landscape page
setup) and put page breaks in logical places eg, one page per objective.

2. Worksheets 2–4: Objective 1, Objective 2, Objective 3
These worksheets are where the inputs that make up each individual activity are
listed and calculated.

These sheets list the individual activities for each Objective with enough rows to list the individual
items/services that make up each activity and cost them. These are called inputs. Again, the
template can be changed to meet the requirements of the particular project being costed.

Column Detail

A Activity Number

Total Number field, whole number
D
When all the items that make up the costs for one activity have been listed and calculated, the
Autosum tool gives a total.
This total is then taken through to the summary worksheet using the link paste feature. This
automatic links means changing or updating the budget is very easy.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

34 PB
TEMPLATE TWO: BUDGET BY ACTIVITIES
This template is called Budget by Activity Template.xls

This template is a simplified version of Template One but contains only objectives and
activities (O&A). The Outputs have been removed and all activities under one objective are
listed under that objective. This system provides a simplified numbering system and therefore
a simplified accounting system. The worksheets are constructed and work in exactly the same
way as in Template One.

This budget template would be suitable for partner organisations whose project follows a
design framework and where most cost can be easily attributed to individual objectives.
Staff would again require good Excel skills.

TEMPLATE THREE: BUDGET BY ASSISTANCE PROVIDED
This budget template is called Budget by Asstnce Template.xls

This template lists projected spending as individual line items that relate specifically to the
type of assistance provided. eg staffing, technical support, goods, equipment, training, etc.
This budget template requires only one spreadsheet, which has a simple structure. No
summary worksheet is required.

This type of budget has been the tradition budget used by many organisations. Its major
drawback is that it does not relate projected spending to the design frame work of the project.

Column Detail

A Budget Code Each item of assistance (called a line item) is given a number
Similar types of assistance are grouped together under one base number.

B Details of the type of assistance being costed – text format, wrapping.
Give as much detail as possible about the item.
Additional rows can be added in each grouping

C Budget – this is the amount or calculation of the cost of the item. Number field, formatted as
zero decimal places as budgets should be in whole numbers. Excel should be used to calculate the
cost of the item.
Comments can be used to explain the workings or assumptions.

D Total Number field, whole number
The Autosum tool gives a total for each grouping

E YTD Number field, whole number
Year to date expenditure – this figure comes from an Income and Expenditure report (Worksheet 2)

F Difference Number field, whole number
Difference between budget and actual expenditure. Automatically calculated

G Budget $NZ Number field, whole number
The budget figure in local currency is automatically converted to $NZ using the exchange rate
found in cell G3.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 35
COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

36 PB
SECTION 3
KEY EXCEL FEATURES
USED IN BUDGETING

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 37
COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

38 PB
USING TEMPLATES
If you have a document that you use a lot e.g. logframe, budget, report, fax, letterhead, you
can make one document that contains all the required features and save it as a template.
This template acts as a pattern. Each time you wish to use it, the template is opened and
saved with a new name as with any other document

To Make A Template (In Word, Excel or Publisher)

• Open a new blank document (Click New button on button bar)

• Prepare this document with all the features you want e.g. for a Memo you might prepare
the document to look like this:

Memo To:

From:

Date:

Topic:

• When you have the document as you want it, you need to save it as a template
document. From the File menu, choose Save As

• When the Save As dialogue box appears, use the drop down box, Save As Type, to
choose the option Template (Excel) or Document Template (Word).

• Give the template a short name eg, Budget O&A and click the Save button.
The document is now saved as a Template document in a special folder.
This is hidden within the Microsoft Office programmes so they cannot be easily deleted.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 39
To Use A Template

• When you want to use a template, from the menu bar, choose File, New

In Excel, the following box appears which will lists any new templates:

• Double click on the template that you want and a new document will be opened with all
the formatting of that template.

• You can start working on your new document – don’t forget to name and save your new
document as soon as you start work on it.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

40 PB
FORMATTING TECHNIQUES

In preparing an Excel template, formatting techniques are important as the spreadsheet
needs to be easy to read. Some useful features for formatting spreadsheets are:

Selecting

To format a worksheet you must be able to select single cells, multiple cells, individual
columns/ rows, multiple columns and rows.

Select a column Click in the header of the column (right on top of the letter)
Select a row Click in the header of the row (right on top of the number)

Multiple Selections

• If the rows or columns are adjacent, you can drag through the headers to select the ones
you want

• If the rows or columns are not adjacent, you can use the Ctrl key to select individual
rows / columns. Hold Ctrl key down as you make your selections.

Adding Columns and Rows

• Add a column Select the column which will be on the right hand side of your new
column. Then from the menu bar choose Insert, Column

• Add a row Select the row which is below where you want the new row. Then from the
menu bar choose Insert, Row

Sizing Columns and Rows

The line between each header box can be used to drag any row/column out to the desired
size. The mouse shape becomes a double black arrow which is it’s “dragging” mode.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 41
Formatting the Page
From the menu bar, choose File, Page Setup.

• Make your page landscape under the Page tab

• Click Sheet tab and ensure your gridlines are On.

• If you want your header row (usually row one) on the worksheet to be printed at the
top of every page, choose File, Page Setup, Sheet. Click in the box called Rows to
Repeat at the Top: then drag your mouse through the row or rows you want printed at
the top of each page. Then choose OK.

• To insert a Header, choose File, Page Setup, Header and Foote. Make a header by
choosing the Custom Header option. Use the middle section and type your header eg, the
name of your project. You can format the text (Arial Size 10 bold is good) by clicking the
A button.

• To insert a footer, choose File, Page Setup, Header and Footer. A good footer is one
that is already listed using the drop down arrow – Page 1 of ?.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

42 PB
Formatting Cells
To format cells; one cell, a group of cells, a column of cells or a row of cells, you must first
select the cells. Then, from the menu bar choose Format, Cells.

Number Determines how numbers, currency, dates, and times are displayed in selected
cells.

Alignment Aligns the contents of selected cells and allows you to wrap text.

Font Changes the font for text in the selected cells or text boxes.

Border Adds or removes solid border lines, shading, or both in selected cells.

Patterns Changes the appearance of the selected cells or objects.

Protection Cell protection when worksheet protection is enabled.

• To format cells to whole numbers. (Standard for budgets) Select the column
concerned, choose Format, Cells, Number and adjust the decimal places to 0.

• To format cells as text that “wraps” – that ism, text that flows in the cell and does
not overlap into the next cell. Select the column by clicking in the header row, then from
the menu bar choose, Format, Cells. Click the Text option in the Category list, then click
the Alignment tab and choose Wrap text.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 43
WORKING WITH WORKSHEETS

An Excel spreadsheet can have made up of several worksheets within that spreadsheet.
Those worksheets that are related and be constructed and stored within one documents. It is
also easier to link worksheets than individual spreadsheets.

Worksheet Features
• Each worksheet is noted by a TAB at the bottom on the main spreadsheet itself. To see a
particular worksheet, click on the TAB name.

• To add another worksheet, click on the worksheet to the right of where you want the
new one. From the menu bar, choose Insert, Worksheet.

• To delete a worksheet, click in the worksheet you wish to delete. From the menu bar,
choose Edit, Delete Sheet. You will be asked to confirm the deletion, as worksheets
cannot be retrieved.

• To name or rename a worksheet, double click on the TAB of that worksheet – you can
then type over the name.

• To move a worksheet so it is in a different order, click on the tab of the worksheet,
use the right hand mouse button to select Move or Copy. Highlight where you wish the
sheet to be moved – in the Before Sheet list. You can move a worksheet into another
spreadsheet file but it is normal to use the copy function.

• To copy a worksheet, click on the tab of the worksheet, using the right hand mouse
button to select Move or Copy. Click Create a copy box. You can create a copy in the
existing spreadsheet, a new blank spreadsheet or another spreadsheet. If you want it
moved to another spreadsheet then you must have that spreadsheet open and select it
from the To Book list.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

44 PB
FORMULAS

Mathematical formulas are a fundamental aspect of Excel. By setting up formulas that make
use of cell references, you can be assured of accurate answers even if the source data is
changed.

To enter a formula
1 Select the cell in which you want to enter the formula.

2 Type an equal sign (=) to activate the formula bar.

3 Do any of the following:
Type the actual calculation eg, 3*56
Type the complete formula (but this is very slow)
Type the function and select cells to insert the cell references
Use the AutoSum tool
Use Formula menu, Paste Function to enter a function.

4 After you have completed the formula, press ENTER.

Examples of Formulas

=B4+B6
=J23-K23
=M5*M6
=R32/R57*2.5

Since 70% of all spreadsheet formulas are the SUM formula, Excel gives you instant access
to this formula. The AutoSum button on the toolbar can be used to enter a SUM function
based on the data above or to the left of the active cell.

1. Place the cursor in the cell to the right or below the range of number that you want to
total.

2. Click once on the AutoSum Button.

3. Excel will automatically enter a Sum formula, and will make a best guess on the range of
cells to be totalled. If this choice is incorrect, you can drag your mouse across the cells.

4. Click a second time on the AutoSum Button.

Design note

A good principle to follow when setting up your worksheet formulae is to design the layout so
that a formula only uses values from cells which lie to the left and/or above the cell
containing the formula. To do this requires that you plan in advance (preferably on paper) the
layout of your worksheet and put some thought into the flow of data through it. The benefit
is that errors are much easier to find when they occur and the worksheet will be much easier
for another person (or yourself at a later date) to use and modify.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 45
QUICK COPY TECHNIQUES

Using the Cell handle

The cell handle is the black cross that appears when you put the mouse over the black dot on
the bottom right hand corner of the cell.

To copy a cell down a row

• Put the mouse over the cell handle

• Drag down as many cells as you wish to copy

To copy a cell across a row

• Put the mouse over the cell handle

• Drag across as many cells as you wish to copy

Fill Commands

Fill right and fill down

• Select the cell or cells you want to copy and the adjacent cells you want to fill.

• To copy the selection’s first column into the adjacent cells to the right, choose Fill Right
from the Edit menu. To copy the selection’s first row into the adjacent cells below, choose
Fill Down from the Edit menu.

Fill left and fill up

• Select the cell or cells you want to copy and the adjacent cells you want to fill.

• To copy the selection’s last column into the adjacent cells to the left choose Fill Left from
the Edit menu. To copy the selection’s last row into the adjacent cells above choose Fill
Up from the Edit menu.

Autofill
Excel can create series using the Auto Fill feature. Examples of Series are: Jan, Feb, Mar, Apr,
or Qtr1 Qtr2, Qtr3 or 2,4,6,8. The AutoFill feature makes use of the Fill Handle which appears
in the lower right hand corner of the selected range.

To create a series by dragging

• Select a range of two cells and enter the first two values for the series. Leave the range
selected.

• To extend the selection and fill in the remainder of the series, position the mouse over
the fill handle. The mouse pointer will change to a cross hair.

• Drag the mouse pointer in the desired direction (generally right or down) until the desired
number of cells are highlighted.

• Release the mouse button and notice that the series has been filled in.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

46 PB
USING COMMENTS

It is sometimes very useful to make comments in cells to explain how you have done a
calculation. For example, you might like to note you have used last year’s figure plus 5% for
known price increase, or list the individual items that make up the sum.

To add a comment to a cell

• Click on the cell

• Click the right hand mouse button

• Choose Add Comment

• The comment box pops up. Type the comment into it. Press Enter when you have
finished.

• A red dot will then be placed in the top right corner of the cell showing that the cell
contains a comment.

To read a comment in a cell

• Place the mouse over the cell and the comment will appear

To Edit a Comment in a Cell

• Click in the cell

• Click the right hand mouse button

• Choose Edit Comment

• The comment box appears, click into it and edit the comment

To Delete/Edit a Comment in a Cell

• Click in the cell

• Click the right hand mouse button

• Choose Delete Comment

Note:
To use the comment function you must be clicked into the cell you wish to add,
edit, or delete comment.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 47
LINKING WORKSHEETS

When you are working with a spreadsheet in Excel that may contain several workbooks, you
often need to copy the contents of a cell from one worksheet to another. Then, should the
contents of that cell be altered in the original worksheet, it will automatically be altered in
the worksheet it was copied into.

The cell you wish to copy may contain just a number or it may contain a formula. Whatever it
contains, the technique is quite simple.

Copy the cell in the usual way:

• Select the cell you want to copy

• Click the Copy button (or use Edit, Copy from the menu bar).

The linking techniques uses Special Paste function:

• Go to the cell where you want to do the linking and select it (by clicking in it) – it may be
in a different worksheet or even a different spreadsheet

• Choose Edit, Paste Special and the following dialogue box appear

• Click the Paste Link option, bottom right.

The special paste will be carried out and if the original cell is changed, so will the cell it is
copied to.

This technique can be used for groups of cells, whole rows and whole columns.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

48 PB
PRINTING

Print Area
Always set the Print Area before you print
• Highlight the area that you want to print

• Go to File .. Print Area … Set

Print Preview
Always check your spreadsheet before you print it by looking at with Print
Preview.

Print Preview displays each page as it will look when printed. The status bar at the bottom of
the screen shows the current page number and the total number of pages in the selected
sheet. The Print Preview button is on the standard toolbar and it is usually the fifth
button from the left, one to the left of the Print button.

Next: Displays the next page. You can also use the scroll bars or the arrow keys to move
through the previewed pages while in print preview.

Previous: Displays the previous page.

Zoom: Switches between a magnified view and a full-page view. Zoom does not affect
printing size. Clicking the mouse button anywhere on the sheet is equivalent to choosing the
Zoom button.

Print: Displays the Print dialogue box. If you chose Print Preview button from the Print
dialogue box, choosing this button prints the previewed pages.

Set-up: Displays the tab that was last selected in the Page Set-up dialogue box.

Margins: Turns on or off the handles for the page margins, header and footer margins, and
column widths. Dragging the handles is the best way to adjust margins or column widths.

Use your Page Up and Page Down keys to see all the pages of the spreadsheet.

COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

PB 49
COUNCIL FOR INTERNATIONAL DEVELOPMENT RESOURCE KIT

50 PB

Related docs