USERS GUIDE PHC MANAGEMENT ADVANCEMENT PROGRAMME
Item
- Title
- USERS GUIDE PHC MANAGEMENT ADVANCEMENT PROGRAMME
- extracted text
-
:-------------------- ;---------------- ■
---------------------------------------------- ;———=
~
'
«a
»
•
.
r
*•
•
••
.
»
•’
. . •
'
•'
'
w .*
•
**
USER’S GUIDE
PHC MANAGEMENT ADVANCEMENT PROGRAMME
MODULE?:
COST ANALYSIS
Prepared by
Jack Reynolds
D_R_A_F_T
July, 1990
Revised
December 26,1990
ACKNOWLEDGEMENTS
A number of people have contributed to the development, review and testing of this
module on cost analysis. Among those are the project staff at Kisumu PHC in Kenya, AKU
in Karachi, AKCHP in Dhaka, URMUL Trust in Bajju, India, and NPPHC in Gilgit,
Pakistan, and AKHS, Pakistan. These and other AKHN staff also participated in a review
of a draft of the module in Dhaka (May, 1990). Special thanks are due to Murray Brown
for his groundwork with these project staff in attempting to determine which costs could
and could not be generated. The principal computer program is based on an article in
Lotus magazine by Stephen L. Nelson.
USER’S GUIDE
PHC MANAGEMENT ADVANCEMENT PROGRAMME
MODULE 7:
COST ANALYSIS
Page
INTRODUCTION......................................................................................................................... 4
1.
What is Cost Analysis: An Explanation and Illustration................................... 4
2.
How You Can Use Cost Analysis.................................................... .................. 12
3.
Steps in a Cost Analysis....................................................................................... 12
4.
Some Limitations of Cost Analysis..................................................................... 13
5.
Strengths of Cost Analysis.................................................................................... 14
HOW TO USE THE COST ANALYSIS MODULE........................................................... 14
COST ANALYSIS PROCEDURES........................................................................................ 14
Step 1:
Specify the Objectives of the Cost Analysis (Manager)..................... 14
Step 2:
Decide What to Cost (Manager)............................................................ 15
Step 3:
Select the Type(s) of Tables and Graphs to be Produced (Manager) 18
Step 4:
Set up Cost Coding System (Analyst).................................................... 18
Step 5:
Code Income and Expense Data (Analyst)...........................................20
Step 6:
Enter Data and Compute Costs (Analyst).............................................22
Step 7:
Analyze Cost Data (Analyst).................................................................. 22
Step 8:
Present/Report the Cost Analysis Findings (Analyst)......................... 22
APPENDICES................................................................................................................................23
A:
Basic Cost Analysis Concepts.............................................................................. 25
B:
Cost Allocation..................................................................................................... 34
C:
Templates, Tools, Checklists and Computer Programs................................... 42
D:
References and Bibliography.............................................................................. 80
E:
Glossary................................................................................................................... 82
USER’S GUIDE
PHC MANAGEMENT ADVANCEMENT PROGRAMME
MODULE 7: COST ANALYSIS
INTRODUCTION
1.
What is Cost Analysis: An Explanation and Illustration
Cost analysis is the examination of expenditures to determine how resources have been
spent. In Primary Health Care (PHC) we are concerned with the examination of PHC
expenditures and usually want to know:
1)
how much has been spent;
2) how does that compare with the budget;
3) what is the distribution of costs by "line items" (personnel, travel, supplies,
etc.);
4) what is the distribution of costs by site or facility (how much has been spent
in the Northern Health Center, the Central Health Center, etc.).
Sometimes managers also want to know other things as well:
5) what have been the trends in costs over time;
6) what will costs be in the future (projections);
7) what are the average costs of providing a service (e.g., the cost of immunizing
a child);
In addition to costs, many managers are interested in revenues, and the same types of
analyses described above can be applied to revenues. That is: 1) how much revenue has
been generated; 2) how does actual revenue compare with budgeted projections; 3) what is
the distribution of revenue by source; and so forth. At a minimum, managers are likely to
want to know:
8) how much revenue has been generated from various sources and what are
the projections for the future;
9)
what is the difference between revenues and expenditures and when will the
project break even (that is, when will revenues equal or exceed
expenditures)?
FILE: MOD7TXT.DOC
4
December 26, 1990
Examples
Table 1 illustrates the first two of these analyses, how much has been spent and how that
compares with the budget. Table 2 illustrates the third, the distribution of total costs by
"line item".
Table 1
Illustration: PHC Program Costs and Budget
Description
Expenditures
Budget
Amount
$4,699.60
$4,395.00
+ $304.60 overspent
Difference
106.9 %
Percent Spent
Table 1 shows how much the program has spent, $4,669.60. It also shows that the program
has overspent its budget by $304.60, or put another way, by 6.9 %.
Table 2
Illustration: PHC Program Costs by Line Item
Description
Amount
Personnel
Travel
Equipment
Supplies
Other Direct Costs
Indirect Costs
2,345.00
345.00
456.00
332.00
876.00
345.00
Total Costs
$4,699.60
Percent
49.9%
7.3%
9.7%
7.1%
18.6%
7.3%
_
100.0%
Table 2 shows that almost half of the expenditures were for personnel costs. The next
highest item was Other Direct Costs, a miscellaneous category that might include such
items as utilities, maintenance, postage and rent.
Table 3 illustrates the fourth type of analysis listed, a comparison of the line item costs of
two hypothetical PHC subprojects, one in the North and another in the Central region
The analysis shows that personnel costs were higher by 2,990 in the Central region, or 31 8
%. But the overall difference in costs was only 3.9 %.
FILE: MOD7TXT.DOC
5
December 26,1990
The same analyses illustrated in Figures 1 and 2 could also be applied to Figure 3. That is,
the percentage distribution of the expenses of each subproject could be calculated, and
each subproject’s budget could be compared with its expenditures.
Table 3
Illustration: Costs for Two PHC Subprojects by Line Item
Description
North
Central
Variance*
Personnel
Travel
Equipment
Supplies
Other Direct
Indirect Costs
9,410
1,294
350
5,680
3,300
4,214
12,400
2,232
-31.8%
-72.9%
4,324
2,382
3,862
-2,990
-938
350
1,356
918
352
Total
24,248
25,200
-952
-3.9
Percent*
23.9%
27.8%
8.4%
* Variance = North - Central
Sometimes it is easier to understand these figures if they are translated into graphs.
Figures 1 and 2 illustrate some of the data from Tables 2 and 3.
Figure 1: PHC Program Expenditures
This figure shows the distribution of line item expenses displayed in Table 2.
FILE: MOD7TXT.DOC
6
December 26, 1990
Figure 2
North and Central PHC Expenditures
Central
This figure shows the distribution of line item expenses for the North and Central projects,
from Table 3.
Sometimes managers and policymakers want to look at costs over time, that is, they want to
look at trends. Table 4 and Figure 3 illustrate a trend analysis.
Table 4
Illustration: Trend Analysis of PHC Costs
1986-1989
Description
1986
1987
1988
1989
Personnel
Travel
Equipment
Supplies
Other Direct
Indirect Costs
9,410
1,294
350
5,680
3,300
4,214
12,400
2,232
4,324
2,382
3,862
14,600
3,456
1,290
4,653
2,234
3,467
16,896
2,890
587
4,125
3,124
3,210
Total
24,248
25,200
29,700
30,832
+ 952
+ 3.9%
+ 4,500
+ 17.9%
+ 1,132
+ 3.8%
Change from
previous year
These figures shows that costs have increased each year, but especially in 1988 when they
went up almost 18 percent over the previous year. Each line item can be analyzed this way
FILE: MOD7TXT.DOC
7
December 26, 1990
also, to spot the cause of the increases. Figure 3 graphically shows the trend in total and
personnel costs using the data from Table 4.
Figure 3
PHC Expenditures 1986—1989
Sometimes managers want to make projections of future costs. The analysis would look the
same as in the last table and figure, only the dates would be in the future.
All of these are ways that cost analyses can be done. The analyst can examine the costs of
one unit or several. For example, the total costs of the entire PHC programme can be
analyzed, or that can be broken down and the costs of each subproject examined.
Financial people often calculate "average costs" or "unit costs", which are estimates of the
costs of producing one unit of a product or service. Managers are often interested in this
type of cost analysis, also. They may want to know how much it costs to immunize one
child, how much it costs to install a well, or how much it costs to train a Community Health
Worker (CHW). This type of cost analysis is different from the other types described
previously in that it involves a measure of output as well as of cost. An example:
Cost of immunization component
Number of children immunized
$16,852
8,954
= $1.88 per child
immunized
Total cost of PHC programme
Total Target Population
$150,896
45,000
= $3.35 per
capita
These types of ratios are also used in Cost-Effectiveness Analysis, where alternative
approaches to achieving the same objective are compared to see which one can achieve the
most with the same level of resources.1 This manual will not deal with Cost-Effectiveness1
1. It can also be looked at the other way, that is, which approach can achieve the same level of output for the least expenditure of
resources.
FILE: MOD7TXT.DOC
8
December 26, 1990
or Cost-Benefit analyses, which are more complicated, and which are described in another
publication.2
Table 5
Source
Illustration: Trend Analysis of PHC Revenues
1989-1992
1991
1990
1989
1992
Federal
State
USAID
AKF
CIDA
Service fees
8,310
4,294
3,500
5,680
3,500
1,214
8,500
4,500
3,500
6,500
3,500
1,458
9,000
5,000
3,500
5,000
2,500
1,500
9,500
5,500
0
4,500
1,500
2,000
Total
26,498
27,958
26,500
23,000
+1,460
+ 5.5%
-1,458
-5.2%
-3,500
-13.2%
Change from
previous year
The last two types of analysis mentioned at the beginning of this section are the analysis of
revenues and break-even analysis. These are particularly useful for looking at project
sustainability and replicability.*3
Table 5 illustrates a hypothetical "trend" analysis of revenue spanning two years of actual
revenue (1989-1990) and two of projected revenues (1991-1992). Figure 4 is a graph
showing the distribution of revenues by source for two years, 1989 and 1992.
The tables show that overall revenue is expected to decline steadily, and the graph shows
the differences in contributions expected from the various sources between the initial and
last year. Federal and state revenues and service fees are expected to increase slightly, but
donor revenues will decline substantially. The largest decline is projected for 1992, when
revenues will decrease 13 percent.
Be^ cTntXHSLRnTX:nid9^CC'eS,e
CoS"E^^*
™COR Monograph Seria; Methodi Paper 2.
3. Sustainability refers to the ability of the project to continue and implies that this would be done lareelv without external donor
assistance. Re^cabihjy refers to the feasibility ot expanding or duplicating the project in other areas, usualK?6y mher o^Sdonl It
also implies that costs would be covered by that organization’s revenues.
y uy oincr organizations, it
FILE: MOD7TXT.DOC
9
December 26, 1990
or Cost-Benefit analyses, which are more complicated, and which are described in another
publication.2
Table 5
Source
Illustration: Trend Analysis of PHC Revenues
1989-1992
1991
1990
1989
1992
Federal
State
USAID
AKF
CIDA
Service fees
8,310
4,294
3,500
5,680
3,500
1,214
8,500
4,500
3,500
6,500
3,500
1,458
9,000
5,000
3,500
5,000
2,500
1,500
9,500
5,500
0
4,500
1,500
2,000
Total
26,498
27,958
26,500
23,000
+1,460
+ 5.5%
-1,458
-5.2%
-3,500
-13.2%
Change from
previous year
The last two types of analysis mentioned at the beginning of this section are the analysis of
revenues and break-even analysis. These are particularly useful for looking at project
sustainability and replicability.*3
Table 5 illustrates a hypothetical "trend" analysis of revenue spanning two years of actual
revenue (1989-1990) and two of projected revenues (1991-1992). Figure 4 is a graph
showing the distribution of revenues by source for two years, 1989 and 1992.
The tables show that overall revenue is expected to decline steadily, and the graph shows
the differences in contributions expected from the various sources between the initial and
last year. Federal and state revenues and service fees are expected to increase slightly, but
donor revenues will decline substantially. The largest decline is projected for 1992, when
revenues will decrease 13 percent.
^nafysis.
Ga5Pan'
PRICOR Monograph Sma: Melhods Paper 2.
3. Sustainability refers to the ability of the project to continue, and implies that this would br
, .
assistance Rephcability refers to the feasibility of expanding or duplicating the project mother a^
also implies that costs would be covered by that organization’s revenues.
P
r arcas’ usually by other organizations. It
usilX^
FILE: MOD7TXT.DOC
9
don<}r
December 26, 1990
Figure 4
PHC Revenues by Source: 1989, 1992
Source of Revenue
The break-even analysis compares the revenues and expenditures for the four years and
shows a surplus of 2,250 and 2,758 in the first two years, but deficits in the last two years of
3,200 and 7,832. Figure 5 shows that the project ceases to break even between 1990 and
1991, where the two lines cross. Again, the gap is projected to be substantial in 1992 when
expenditures will exceed revenues by over 25 percent.
Table 6
Illustration: PHC Program Break-Even Analysis
1989-1992
Source______
1989
1990
1991
1992
Revenues
Expenditures
26,498
24,248
27,958
25,200
26,500
29,700
23,000
30,832
Difference
+ 2,250
+ 2,758
-3,200
-7,832
Percent Difference (Expenditures/Revenues)
+ 10.9
+ 9.3%
FILE: MOD7TXT.DOC
10
-10.8
-25.4%
December 26,1990
Figure 5
Break-Even Analysis 1989—1992
----- M----Revenues
------- 5-------
Costs
This scenario is rarely acceptable, and either revenues would have to increase or
expenditures would have to be reduced. The analysis shows how much would be needed
(7,832) to break even in 1992.
Summary
Cost analysis is the examination of expenditures to determine how resources have been
spent. Nine types of analyses have been described:
1.
2.
3.
4.
5.
6.
7.
8.
9.
The total amount of resources spent
Expenditures compared with budgets
The distribution of costs by line item
The distribution of costs by site or facility
Trends in costs over time
Projections of future costs
Average costs
Analysis of revenues (past and projected)
Break even analysis (revenues compared to expenditures)
The managers and policy-makers need to decide which of these analyses to apply That
decision depends largely on the purpose of the cost analysis, which is discussed in the
following section.
FILE: MOD7TXT.DOC
11
December 26, 1990
2.
How You Can Use Cost Analysis
There are three basic ways cost analysis can be helpful to managers.
MONITORING: This is the most common use - to monitor expenditures (and
revenues) in order to make adjustments, if necessary. This type of analysis
usually involves comparing actual expenditures with a budget, and is also
most often done periodically to identify problems before they become
serious. Managers may want a more detailed analysis than board members
and donors, but the general purpose is the same - to ensure that expenditures
are under control.
EFFICIENCY: A cost analysis can help a manger identify areas where savings
can be made. If services can be provided at reduced costs while maintaining
the same quality, then the project will be more efficient. This type of analysis
requires comparisons between different strategies, and is often a reason for
comparing the costs of different PHC subprojects or programs to see if there
are lessons that can be learned from one project and applied to another to
reduce costs.
PLANNING: Managers can also use cost data to make projections of future costs
and to estimate what it would cost to replicate a program or service in
another area. Cost analysis can be used to estimate what it would cost to
continue a program or service at the same, an expanded, or a reduced level that is, what it would cost to sustain it.
There is no single type of cost analysis that should be done and no standard purpose that
fits all programs. Like most other management activities, it all depends on the what the
user(s) want from the analysis. Since most PHC projects have a number of potential users
(managers, boards, donors, communities, for example), it is entirely possible that each user
would have a different objective in mind. In such a case it will be important to clarify what
those objectives are, because each one might require a slightly different analytical
approach. That is, multiple users could mean that multiple analyses will be required.
3.
Steps in a Cost Analysis
This User's Guide shows managers and finance staff how to carry out a cost analysis in eight
steps. The first three steps describe what the manager (user) should do to tell the finance
staff what type of analysis to conduct. The remaining steps describe how the finance staff
would set up the procedures and carry out the analysis.
Step 1:
Specify the Objectives of the Cost Analysis
Step 2:
Decide What to Cost
Step 3:
Select the Types of Tables and Graphs to be Produced
Step 4:
Set up Cost Coding System
Step 5:
Code Income and Expense Data
FILE: MOD7TXT.DOC
12
December 26, 1990
4.
Step 6:
Enter Data and Compute Costs
Step 7:
Analyze Cost Data
Step 8:
Present/Report the Cost Analysis Findings
Some Limitations of Cost Analysis
This module deals with a specific type of cost, what economists would call "financial" or
"accounting" costs rather than "economic" costs. The latter includes in-kind contributions,
donated labor, etc. To economists, "the true cost of an activity is the value of the
alternative endeavor that might have been undertaken with the same resources."4
Measuring the true cost of PHC is very difficult. It would require, among other things,
estimating the cost to the client of attending a clinic session, the cost of donated labor and
materials, and the true cost of subsidized supplies. This module uses a practical
approximation of true costs, which is financial costs - actual monetary expenditures, which
is the type of cost information that appears in financial reports. However, suggetions are
included in the module for estimating the cost of some important and typical items that do
not have a monetary cost attached. For example, CHWs are often unpaid volunteers. It
would be important in most cost analyses to include an estimate of the market value of
their labor.
The terms cost and expenditure are used interchangeably in this module, even though they
have different meanings to economists.
Most cost analyses will probably be done retrospectively, that is, the analyst will have to
reconstruct costs that have already occurred in the past year or so. Often there are gaps in
the records and estimates of costs will have to be made. That can be quite difficult to do.
For example, many health workers carry out a variety of activities each day and do not keep
track of how much time they spend on any one activity. That makes it difficult to compute
the average cost of specific services. Several approaches for making estimates and
allocating costs are described in this Guide, but the manager must accept that these are
estimates and therefore, may not be completely accurate.
Some costs are difficult to predict, costs of drugs and vehicles, for example. That will make
projections difficult to make. Imported goods are sometimes subject to higher or lower
fluctuations than local goods. Exchange rates can vary over time, thereby artificially raising
or lowering costs of the same good during a relatively short time period.
These and other common problems are discussed in the appendices and suggestions are
made for dealing with them. In many cases, financial staff are familiar with these issues
and can take them into account when conducting the cost analysis. The manager and
policy-maker should, however, also be alert to the effects these limitations can have on the
results.
4. Kenneth E. Warner and Bryan R. Luce, Cost-Benefu and Cost-Effectiveness Analysis in Health Care, Ann Arbor, MI: Health
Administration Press, 1982, p. 44.
FILE: MOD7TXT.DOC
13
December 26, 1990
5. Strengths of Cost Analysis *
Even with these limitations, cost analysis can be a very useful tool for the manager and
policy-maker. In the absence of certainty, even approximations can help improve decision
making. And given the strains on most PHC budgets, as well as the pressure from boards
and donors to become self-sustaining, cost analysis will become an essential part of every
manager’s tool kit.
HOW TO USE THE COST ANALYSIS MODULE
The following section describes the procedures for designing and carrying out a cost
analysis. More detailed explanations of some steps are found in the Appendices, along
with some ’’tools” to help the manager and finance staff carry out the analysis. These tools
include checklists, computer programs for entering and analyzing the cost data, and
suggested procedures for allocating costs to various categories.
In many cases, especially among more sophisticated private PHC programs, the finance
staff will be able to cany out the cost analysis as soon as the managers have clarified what
they want analyzed and how they want the data presented. The finance staff would
probably be able to set up a coding system, classify the cost data and prepare the required
tables without referring to the steps in this Guide. In some cases, especially among public
sector programs that do not normally track expenditures, the managers may have to call on
one of the planning staff to set up a cost analysis from scratch. Those programs may find
the steps and the "tools" particularly helpful and time-saving.
In any case, the user of this module should consider it a guide that can be adapted and
modified to fit each local situation.
COST ANALYSIS PROCEDURES
Step 1: Specify the Objectives of the Cost Analysis (Manager)
There are three things the manager needs to specify: 1) the USER; 2) the PURPOSE; and
3) the SCOPE of the analysis. The following checklist can guide the manager in doing that.
First, who will be the user of the cost analysis? The manager is the most likely user, but the
others listed could also be interested in the analysis. If so, it will be important that each
one clarify exactly what the desired purpose and scope is.
Second, what is the purpose of the analysis? The three purposes listed were described in
the previous section. It would help to write out the specific purpose, or purposes, as any
one user could have several purposes in mind.
Finally, how broad should the scope of this analysis be? What geographic area will it cover
(the entire country, a region, a city, several rural sites)? Some projects operate in multiple
sites. Will they all be included in the analysis?
FILE: MOD7TXT.DOC
14
December 26,1990
WORKSHEET for SPECIFYING OB.TE
&
User/Audience:
Manager
__ __________
Board of Directors
_____________
Central Directorate
_____________
___Donors
___________________________ _______________
Other:
__ ______________
Purpose:
Monitoring
Efficiency
Planning
Other:
Scope:
Geographic area
Program/project/activity
Time/Duration
Expenditures &/or Revenues
Other:
■
How much programmatic detail is desired? Is it sufficient to look at the
PHC program overall, or does the user want to break the costs down further:
by project; by subproject; by outreach and clinical services; by specific PHC
component (immunization, ORT, growth monitoring, training, supervision,
etc.)? Is there a special component that the user wants to examine?
■
What time frame should the analysis cover: the past year, the past five years,
the next six months, the next three years?
■
Should the analysis include revenues as well as costs? Or only revenues?
Only costs?
It would be helpful to write out the objective(s) in narrative form and to prepare a separate
statement for each user, since each user is likely to have different objectives that will
require different cost data.
Step 2: Decide What to Cost (Manager)
Here the manager needs to get specific regarding the level of detail desired. Obviously, the
more detail requested, the more time, effort and expense will be involved. The simplest
analysis would be of the PHC program overall, that is, the total cost of the PHC program
without any breakdowns. Let us call that Level 1.
For some small projects that will be sufficient. But many PHC programs are made up of
two or more ’’projects", "subprojects", "locations" or "sites", and the user may want to have a
separate analysis of each of these. Let us call that Level 2. The Aga Khan University
Urban PHC Project in Karachi consists of seven subprojects, or sites. An analysis of each
of those, plus the overall project, would require eight separate cost analyses.
FILE: MOD7TXT.DOC
15
December 26, 1990
AKU Urban Project
Subprojects
____________________ 1
Orangi
1
Grax
Essa
Nagri
.
1
Chanesar
Goth
1
Azam
Basti
Baba Karimabad
Island
Each PHC project (or subproject) usually offers an array of services, which we will call
"components". The user may want to cost some or all of these PHC service components.
Let us call that Level 3.
Kisumu PHC Project
Locations
Central
North
Water Supply
Sanitation
Immunization
Growth Monitoring
Maternal Care
Family Planning
Curative Care
Nutrition
Health Education
Drug Supply
School Health
Income Generation
Kajulu
Water Supply
Sanitation
Immunization
Growth Monitoring
Maternal Care
Family Planning
Curative Care
Nutrition
Health Education
Drug Supply
School Health
Income Generation
Water Supply
Sanitation
Immunization
Growth Monitoring
Maternal Care
Family Planning
Curative Care
Nutrition
Health Education
Drug Supply
School Health
Income Generation
The Kisumu PHC project in Kenya consists of three "locations", each of which offers
several services. The user may not wish to cost all of these, of course, as the number of cost
analyses increases rapidly with each level. If all three Kisumu locations and all 12 PHC
services were to be costed, that would be 36 cost analyses. An alternative would be to
select one or two services that are really important or to group the services, e.g., Health
Center Services, Satellite Clinic Services and Community-based Services.
In addition to these direct services, the user may want to know the costs of certain support
services, such as the following:
Training
Supervision
Planning
Logistics
Financial Management
Monitoring/Evaluation
Research
Community Organization
The final level of detail, call it Level 4, is the General Ledger Accounts. These are
sometimes called "line items" (personnel, travel, equipment, etc.). Most PHC projects
already have a "Chart of Accounts", and would use those categories. An example of a
FILE: MOD7TXT.DOC
16
December 26,1990
detailed list is found in the Appendix and on the enclosed computer disk as
ACCOUNT. WK1.
The user needs to determine how much detail is needed. The general ledger accounts
could be collapsed into a small number, as
Income
Personnel Wages and Salaries
Commodities
Travel
Capital Expenditures
Other Direct Costs
Indirect Costs
Subcontracts
Or some or all categories could be expanded, as
Commodities
Drugs, medicines
Office supplies
Health education supplies
Other commodities
WORKSHEET for deciding what to cost
Level 1: PHC Program (Total Costs)
------------------------------
Level 2: Projects, Subprojects, Locations, Sites, etc.
Level 3: PHC Service Components or Managment Components
Level 4: General Ledger Accounts
FILE: MOD7TXT.DOC
17
December 26, 1990
The preceding worksheet can be used to specify the level of detail wanted for the cost
analysis. Remember that each user may need to fill out a separate sheet. And since the list
can get very detailed very quickly, it is worth asking whether each additional breakdown is
necessary to meet the objectives of the cost analysis.
Step 3: Select the Type(s) of Tables and Graphs to be Produced (Manager)
The Introduction to this Module described nine types of cost analyses that could be
performed. The user needs to select those that are needed for this analysis. It can be very
helpful to set up the "dummy tables" and graphs at this point, since that will make clear to
the analyst what is expected, and ensure that the information that the user desires is
produced. Appendix C 4 includes instructions for constructing dummy tables and graphs,
together with a computer file of illustrative tables and graphs (MOD7ANAL. WK1).
WORKSHEET FOR SPECIFYING TABLES AND GRAPHS NEEDED
1. The total amount of resources spent
__2. Expenditures compared with budgets
3. The distribution of costs by line item
4. The distribution of costs by site or facility
5. Trends in costs over time
__ 6. Projections of future costs
___7. Average costs
8. Analysis of revenues (past and projected)
_ 9. Break even analysis (revenues compared to expenditures)
10. Other:
Again, each of these analyses requires time and effort to produce, especially if the cost
analysis will involve several levels of detail. The users should limit their requests to those
analyses that are needed to meet the cost analysis objectives.
Step 4: Set up Cost Coding System (Analyst)
At this point the finance staff can take over. The next step would be to develop or adapt a
coding system for the items to be costed. For example, assume that the Kisumu PHC
project manager decided to limit the analysis to the levels shown in the box on teh opposite
page. The finance staff (or analyst could then assign a code to each category to make it
easier to classify the expenditures into groups for summing and further analysis. If the
Finance staff already have a coding system, that should be used or adapted, if at all
possible. If new codes have to be developed, they should be simple and easy to remember.
The above example only requires 39 separate codes (3 locations * 2 service components * 5
expense ledger accounts + 3 locations * 3 revenue sources), so consecutive numbers of 139 could be used as codes. This may not be easy to remember, however, and letter codes
might be preferred. For example, the locations could be coded as follows:
N =
C =
K =
N. Nyakach
C. Nyakach
Kajulu
FILE: MOD7TXT.DOC
18
December 26, 1990
The Service Components could be:
C =
O =
Clinical
Outreach
And the General Ledger Account codes could be:
Revenues
F =
D =
N =
Expenditures
P = Personnel
T = Travel
S =
Supplies
O = Other Direct Costs
I =
Indirect Costs
Service Fees
Donor Support
Other Income
Each transaction would then be given a three-letter code, where the first letter stands for
the location, the second for the PHC component and the third for the General Ledger
Account. Examples:
KCP Clinic personnel costs at Kajulu
NOT Outreach travel costs at N. Nyakach
CCF Service fees collected at the C. Nyakach clinic
Level 1: PHC Program Total Costs
KISUMU PHC
PHC Program Total Revenues
Level 2: Projects, Subprojects, Locations, Sites, etc.
N. Nyakach
C. Nyakach
Kajulu
Level 3: PHC Service Components or Managment Components
Clinical Services
Outreach Services
Level 4: General Ledger Accounts
Expenditures:
Personnel
Travel
Supplies
Other Direct Costs
Indirect Costs
Revenues:
Service Fees
Donor Support
Other Income
Appendix C 2 includes a printout of a computer file (ACCOUNTS.WK1) - also on the
enclosed disk) that describes how to develop numeric and letter codes. Appendix C 3’
FILE: MOD7TXT.DOC
19
December 26,1990
contains version 2 of the data entry worksheet (MOD7V2.WK1 - also on disk) includes an
illustrative set of cost codes and a sample printout of an income and expense report
organized according to those codes.
Step 5: Code Income and Expense Data (Analyst)
Before coding begins the manager and finance staff will need to meet to determine which
costs will and will not be included in the analysis. Some costs (international travel to
attend a PHC conference, subscriptions to computer magazines, receptions for visiting
guests) may distort the cost analysis if included. However, if those costs are a reasonable
input to the project (e.g., computer magazines to keep the MIS staff informed about the
latest hardware and software), then they should be included.
The guiding principle should be:
are these costs relevant to the cost analysis objectives?
If they are they should be included.
Now the income and expense data can be coded. If this is a prospective analysis (to be
done in the future) then the financial staff will need to set up procedures to make sure that
all expenses and revenues are coded, following these codes, as they occur. This will
probably require an orientation session for all PHC staff to make sure that they understand
the new system, and periodic monitoring to ensure that they remember to code
expenditures properly.
Unfortunately, in most cases the cost analysis will be done retrospectively and the finance
staff will have to reconstruct many of the costs. Appendix B provides suggestions for
allocating past and future costs.
When costs can be clearly attributed to a category, then the coding is straightforward.
Examples are fuel costs for a mobile outreach van, vaccine costs for the immunization
component, and computer repair costs for the MIS. Sometimes the attribution is not at all
clear and then estimates will have to be made. Examples are the operating costs of an allpurpose project vehicle, PHC brochures describing services available, and project
management costs.
Allocation estimates can be done by the manager and staff making educated guesses
(which may be more accurate than one would expect - and definitely inexpensive and
rapid) or through some sort of formal estimation survey. One of the most common
allocation problems is staff time. Most PHC staff do not keep track of how they spend
their time, which makes it difficult for the finance staff to allocate costs if the analysis is to
include breakdowns by PHC service or management component.
One approach to time allocation is shown on the next page. First, the cost categories
specified in Step 2 are listed. If there are only two categories (outreach and clinical, for
example), then time should be allocated to one or the other of those two categories. In the
following example five component categories have been identified: immunization, growth
monitoring, administration, supervision and other.
FILE: MOD7TXT.DOC
20
December 26, 1990
Allocations can be done for each staff member, for classes of staff (all CHWs, all nurses),
or just for those that are relevant to the cost analysis (e.g., all outreach staff).
STAFF TIME ALLOCATION WORKSHEET
1.
List categories to be costed: IMM, GM, Admin, Sup, Other
2.
Estimate percent of staff time spent on each activity*
3.
List compensation of staff
4.
Multiply 2*3
Staff: J. Riley
4,600/year
Compensation:
Hrs/Week
Percent
Cost
IMM
20
25.0
1,150.00
GM
10
12.5
575.00
Admin
15
18.75
862.50
Sup
10
12.5
575.00
Other
25
31.25
1,437.50
Total
80
100.0
4,600.00
* See following worksheet for a suggested procedure for collecting
needed data.
The sampling period can be relatively short if the staff member follows a similar routine
every day or week. Obviously, the sampling period should be as typical of the cost analysis
period as possible.
Data can be collected by trained observers or reported by the staff themselves. The former
is more expensive, the latter is likely to be less objective. However, given the costs involved
and the relative importance of being highly accurate, self-reporting may be a practical
approach.
Time allocation data can be collected on a simple form, such as the one shown below. This
form is for a two-week period, but could be shorter or longer. It can be used prospectively
as well as retrospectively, and it can be used by an observer or the staff themselves. The
number of hours each staff member spends on each component would be filled in each day
The percent distribution could be computed by the analyst.
FILE: MOD7TXT.DOC
21
December 26, 1990
Additional suggestions for conducting time analyses are found in Appendix B: Cost
Allocation.
TIME ANALYSIS WORKSHEET
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Total Percent
“Day:
CODE:
____ IMM_________________________________________________________
GM_______________________________________________________________
Admin____________________________________________________________
Sup__________________________________________________________
_____ Other_____________________________________________________________
Total______________________________________________________________
Step 6: Enter Data and Compute Costs (Analyst)
The data can now be entered into a journal or computer for processing. Included in this
Guide are three versions of a data entry and tabulation program designed for PHC projects
that do not have a computerized financial system that can produce the desired cost data.
These programs are found on the data disk (MOD7V1.WK1, MOD7V2.WK1 and
MOD7V3.WK1) complete with instructions. They are also illustrated in Appendix C.
Whether these programs or Finance staff programs are used, the data (transactions) all
need to be entered (recorded) for the specified cost analysis period, tabulated according to
each cost code selected, and summed.
Step 7: Analyze Cost Data (Analyst)
Assuming that the dummy tables and graphs were selected in Step 3, the analysis should be
straightforward. The Introduction illustrated nine different cost analyses procedures, and
includes tables and graphs. Appendix C includes a computer file (MOD7ANAL.WK1) that
contains five illustrative templates that provide simple instructions for analyzing cost data.
They also include pre-designed graphs and instructions for displaying them. These
templates can be copied or modified so that analysts can substitute other cost data for
those illustrated.
Step 8: Present/Report the Cost Analysis Findings (Analyst)
The final, obvious, step is to report the findings to the user(s). The tables and graphs
included in this Guide, and the templates included in the computer disk, can be copied and
modified to display the findings in a simple, clear manner.
FILE: MOD7TXT.DOC
22
December 26, 1990
APPENDICES
These appendices contain additional guidelines, tools and detailed suggestions for
conducting a cost analysis. The computer files that are illustrated are included on the
accompanying disk. They are all written to work on Lotus 1-2-3.
Page
.....25
A: Basic Cost Analysis Concepts
■ Direct, Indirect, Infrastructure (Capital) Costs................................................................ 26
■ Capital and Recurrent Costs................................ ............................................................... 26
■ Fixed and Variable Costs.................................................................................................... 27
■ Average Costs, Marginal Costs and Economies of Scale................................................. 27
■ Cost-Effectiveness and Cost-Benefit Analyses..................................................................28
■ Monetary and Non-monetary Cost Estimates and Shadow Pricing............................... 28
■ Foreign Exchange................................................................................................................. 29
■ Adjusting for Inflation.......................................................................................................... 30
■ Computing Present Value................................................................................................... 30
B: Cost Allocation .................................................................................................................................... 34
Step 1: List the Codes to be Used.......................................................................................... 35
Step 2: Separate the Costs into Direct and Indirect............................................................. 36
Step 3: Compute the Indirect Cost Rate............................................................................... 36
Step 4: Allocate Direct Costs.................................................................................................. 37
Step 5: Allocate Indirect Costs............................................................................................... 40
Summary.................................................................................................................................... 41
C. Templates, Tools, Checklists and Computer Programs......................................................... 42
■ Cl Blank Forms................................................................................................................... 43
■ C 2 Income and Expense Account Worksheet (ACCOUNT.WK1).............................. 46
■ C 3 Cost Analyses Worksheets
FILE: MOD7TXT.DOC
23
December 26, 1990
Page
■ C 3.1 Version 1 (MOD7V1.WK1)........................................................................... 51
■ C 3.2 Version 2 (MOD7V2.WK1)........................................................................... 59
> C 3.3 Version 3 (MOD7V3.WK1)........................................................................... 66
■ C 4 Cost Analysis Procedures (MOD7ANAL.WK1)......................................................... 71
D. References and Bibliography.. ................................................. ...................................................... 80
E. Glossary ............................................ .................................... ....... ..................................................... 82
FILE: MOD7TXT.DOC
24
December 26, 1990
APPENDIX A:
BASIC COST ANALYSIS CONCEPTS
FILE: MOD7TXT.DOC
25
December 26,1990
APPENDIX A: BASIC COST ANALYSIS CONCEPTS5
This appendix defines and illustrates the basic cost concepts that are needed to understand
what constitutes a cost analysis. Specifically, the sections that follow describe:
■
Type of costs (direct and indirect, capital and recurrent, fixed and variable,
average and marginal)
■
Cost-Effectiveness and Cost-benefit
■
Economies of Scale, Monetary and Nonmonetary Cost Estimates and
Shadow Pricing
■
Inflation, Foreign Exchange and computing Present Value
TYPE OF COSTS
The true cost of a primary health care program is measured as the value that could be
gained by using the resources in some other way, such as the building of a surgical unit, the
implementing of a nutritional program, or the refurbishing of a hospital.
The first step in cost calculation is to identify all the resources that have been or will be
expended to produce the given effect. Whether or not these resources have a monetary or
nonmonetary value associated with them is immaterial in this identification phase. (In.a
later section, guidelines for valuing nonmonetary resources will be given.)
DIRECT, INDIRECT, AND INFRASTRUCTURE COSTS
The first way to classify costs is as direct, indirect, or infrastructure. Direct costs are
directly distributable to the service. For example, the direct costs of expanding a primary
health care program to include an ORT component may consist of personnel salaries,
volunteer time, ORT salt packets, and transportation. Indirect costs are the costs of
supporting the direct services. The expansion of the primary health care program may
require the Ministry of Health to hire an additional planner to deal with the administration
of the ORT activity, or the Ministry of Transportation may need to hire additional
mechanics to service the program’s new vehicles. Infrastructure costs are even less directly
related to the program. They include the costs of roads, ports, and telephone systems that
the program requires.
CAPITAL AND RECURRENT COSTS
Once costs have been categorized as direct, indirect or infrastructure, they can be classified
as capital (or developmental) and recurrent (or operating). The distinction between the
two types is based on life expectancy. Those resources that have a life expectancy of 1 year
or more usually are called capital costs. They may include buildings, cars, trucks, beds, and
medical equipment. Those resources that are purchased and used (or replaced) within 1
year’s time are recurrent costs. They include such items as personnel salaries, medicine
and supplies, gasoline, electricity, drugs, and food.
5. Adapted from "Appendix A: A Guide to Calculating Costs", in Jack Reynolds and K. Celeste Gaspari, Cost-Effectiveness Analysis, op tit.
FILE: MOD7TXT.DOC
26
December 26, 1990
The distinction between capital and recurrent costs is important in PHC cost analyses
because:
1.
These costs are calculated in different ways (this will be described in a later section.)
2.
Some donors limit their contributions to capital costs and expect the host country to
be responsible for the recurrent costs.
3.
In many countries there is one budgeting and accounting process for recurrent costs
and another for capital costs.
FIXED AND VARIABLE COSTS
Fixed costs do not vary with program size; variable costs do. Examples of fixed costs are
those of a building, salaries of permanent staff, and medical equipment. These costs are
set (fixed), regardless of whether the program serves 1 person or 1,000. Variable costs
include drugs, gasoline, and vehicle maintenance. These items vary with the amount of
service provided. The cost of providing ORT packets is an example. The more people
served, the greater the number of ORT packets distributed and the greater the resultant
cost.
The distinction is particularly important when the analysis is used to estimate the costs and
effects of expanding a program. Sometimes a program could be expanded inexpensively
because the fixed costs would remain the same and only the variable costs would increase.
For example, a primary health care program with an ORT component might be expanded
from 1,000 to 1,200 persons simply by providing additional oral rehydration packets and
some gasoline for the supervisors to deliver the ORT packets to the CHWs. The cost of
expansion would be relatively low.
Under another alternative, expansion might require increased fixed costs. For example,
new staff and a jeep might be required to serve the additional 200 people, which could
increase the cost of expansion significantly. Thus, it is important to identify and measure
those costs that will vary, including the costs of supervision, information systems, storage,
and vehicle maintenance.
AVERAGE COSTS, MARGINAL COSTS AND ECONOMIES OF SCALE
One other distinction in calculating costs is worth mentioning at this point. The average
(or per unit) s&si is the total cost of a program divided by the total number of units of
outcome. An example would be the total cost of an immunization component of a primary
health care program (e.g. $10,000) divided by the number of children immunized (e.g.
5,000), which would produce an average cost per child immunized ($2).
A marginal cost is the increased cost of providing an additional unit of outcome. POr
example, if a program could immunize 5,000 children at a total cost of $10,000 any
additional cost of immunizing the 5,001st child (e.g. $.40) would be the marginal ’ cost
Hence, marginal costs are the additional resources required for immunizing an additional
child. In this case, the expansion - even to one more person - would include the extra
vaccine, additional medical supplies, and the amount of personnel time spent in
administering the vaccine. The marginal cost calculation is particularly useful when
estimating the cost of expanding a program to different levels.
FILE: MOD7TXT.DOC
27
December 26, 1990
Economies of Scale exist when the greater the number of children immunized the less it
costs to immunize each additional child. In other words, the marginal cost per child
immunized would be less than the average cost. Economies of scale usually occur when
fixed costs do not increase as services are expanded. That is, the program has ’’excess
capacity" (unused clinic beds, staff with available time, an underutilized Land Rover) that
can be used at no extra cost. The extra costs of serving more people usually are limited to
the variable costs (medical supplies, gasoline, etc.).
When there is little or no excess capacity (no unused beds, no available staff time, etc.)
then additional fixed costs may have to be incurred to permit expansion. In this case there
usually is an increase in average costs, marginal costs and total costs. This is called
diseconomies of scale, since the larger the program gets, the more it costs to serve each
additional child.
COST-EFFECTIVENESS AND COST-BENEFIT ANALYSES
Cost effectiveness analysis (CEA) is an analytical tool to help decisionmakers assess and
compare the costs and effectiveness of alternative ways of achieving an objective. The cost
effectiveness (CE) ratio is calculated by dividing the cost of an alternative, which is usually
expressed in monetary terms, by the effectiveness of that alternative, which usually is
expressed in nonmonetary terms.
Cost-benefit analysis (CBA) is often confused with CEA; however, in this method, both the
cost (the numerator) and the effect (the denominator) are expressed in monetary terms.
For example, in computing the cost-benefit of a seat-belt program designed to save lives,
the economic value of a life saved would have to be determined and then multiplied by the
number of lives saved. The results would show how the benefit (in monetary terms)
compares with the cost (also in monetary terms). Some analysts invert the numerator and
denominator to compute a "benefit/cost ratio.” Health benefits usually are difficult to
express in monetary terms, which makes this type of analysis difficult to undertake.
MONETARY AND NONMONETARY COST ESTIMATES AND SHADOW PRICING
Once the cost categories have been listed for each program, activity, service, or task to be
costed, the quantity of each of these inputs must be identified; i.e. the number of hours or
days of personnel time, the physical quantities of supplies, the hours of use of facilities.
Conversion of these inputs into some monetary unit (e.g. pesos, rupees, dollars) is a critical
step in cost analysis. Many items are easily convertible to a monetary value by using the
current market price for the item. For example, personnel costs of health education
activities can be computed using current wages and salary rates. However, sometimes this
market price does not reflect the true cost of the input, and "uncritical use of market prices
can lead to large gaps between cost estimates and the true cost.’’6
Sometimes the prices of goods and services are artificially high, low, or nonexistent.
Common examples of this phenomenon are the following:
6. Warner and Luce, op. at., p. 80.
FILE: MOD7TXT.DOC
28
December 26, 1990
■
"Free” drugs and medical care
■
Donations for drugs or medical care
■
Donation of land, labor, or furniture
■
Volunteers, especially community health workers.
For items such as the above, shadow prices should be calculated. Shadow prices are
estimates of the true costs of goods or services. For instance, how should the value of a
volunteer’s time or of furniture donated to a health project be computed? The easiest
method would be to assume that the costs are zero since the project paid nothing for them.
In fact, this is incorrect. The social cost of these items is not zero, because if the health
project had not used them, they could have been used elsewhere. The true cost of these
items, their shadow prices, can be calculated in one of two ways.
1.
If the project includes similar material or services that have been purchased
in the market, apply these prices to the donated materials or services.
2.
If no value can be assigned in this way, calculate the monetary value that
would have been paid for the material or service in an alternative project.
For example, if paid employees perform tasks similar to those carried out by volunteers,
then a wage rate of the paid employee can be assigned to the volunteer. Otherwise, ±e
analyst should calculate what it would cost to substitute wage, labor for the volunteer. The
value of donated furniture should be calculated similarly. If no monetary figure is
available, then use the market price for similar furniture.
Great care must be taken at this stage of the analysis, lest artificial inflation or deflation of
the program’s total cost result from uninformed estimates.
A final caveat must be added:
...certain costs cannot be valued in meaningful fashion - for example, pain
and suffering - but the analysts’ inability to take them to and through the
evaluation stage is no excuse for ignoring them in the final cost-benefit
calculus. Even when it is not possible to indirectly or implicitly value such
costs, it is incumbent on analysts to bring the immeasurable to the fore when
measured costs and benefits are being prepared.7
FOREIGN EXCHANGE
All costs should be calculated in the currency of the host country. However, if the program
relies on imported items that must be paid for with foreign exchange, then this presents two
problems. First, the official exchange rate may be artificial, and shadow prices will have to
be used to compute the actual costs of imported goods. For example, one Asian country
recently had an '’official” exchange rate of 6:1 and a "black market" exchange rate of 76:1.
Calculating the cost of imported items under the official exchange rate would result in a
cost 11 times less than the real costs. In effect, the government’s official exchange rate
7.
Ibid., p.82.
FILE: MOD7TXT.DOC
29
December 26, 1990
subsidizes the health program and should be added to the project cost. This is a case where
the official exchange rate does not reflect the true price of foreign exchange and a shadow
price for foreign exchange should be calculated. The cost of items should then be
calculated according to the shadow price of foreign exchange.
The second problem is that foreign exchange may be so limited that replication or
sustainability may not be feasible. For example, government restrictions might be such that
the project could not obtain enough foreign exchange to import needed drugs.
Alternatively, the foreign exchange might be available, but import restrictions might limit
the amount or type of items that can be imported. A cost analysis must account for these
nonmonetary constraints.
ADJUSTING FOR INFLATION
Inflation is an especially important consideration when the cost analysis is projecting future
costs. If the inflation rate is expected to be the same each year (say 5 %) and to be equally
applicable to all items (personnel, fuel, etc.), then it is a simple matter to multiply the
current costs by a standard inflation factor (e.g., 105%).
However, inflation does not usually behave that way. It may be very high for certain items
(rent in an urban area, gasoline) and low for others (supplies, maintenance). It is also
likely to vary over time, perhaps 3 % one year and 15 % the next. Predicting inflation is
practically impossible, so the best the analyst can do is adopt the figures used by major
financial institutions, such as banks, manufacturers, etc. The analyst should also use
different inflation factors for different cost categories if those categories are significant in
the analysis. For example, personnel costs often account for the largest expenditures. The
analyst should take special care to apply a reasonable inflation rate to those costs. If
commodity costs are not expected to rise, then a different rate should be applied, especially
if this is a large cost item.
COMPUTING PRESENT VALUE
If a projection is to exceed 1 year, then economists usually discount costs to obtain an
estimate of the present value of the projection. The rationale for computing present value
is that the funds for years 2, 3, and beyond could be invested and earn interest, which would
offset some of the cost Discounting is usually applied to the total annual cost, but it can
also be applied to individual items, such as fuel, medicines, etc.
The formula for computing present value is PV = c/(l + r)n. Assuming an interest rate of
15 percent, the present value of fuel cost for the next three years would be computed as
follows:
FILE: MOD7TXT.DOC
30
December 26,1990
Year______________ _____ Cost______________ r
1
1,250
2
1,563
.8696
3
1,953
.7561
TOTAL COST
4,766
Present Value
4,086
Present value fuel
=
1,250 + 1,563/(1+ r) + 1,953/(1+ r)2
1,250 + 1,563/(1 + r) + 1,953/(1.15)2
1,250 + 1,359 + 1,477 = 4,086
Using a present value table (see table A-l), the factors for r are found by reading down the
15% column for 1 and 2 and the calculation becomes:
Present value fuel
FILE: MOD7TXT.DOC
1,250 + (1,563 x .8696) + (1,953 x .7561)
1,250 + 1,369 + 1,477 = 4,086
31
December 26, 1990
Table D-3.—Present Value Table
u
o
n
0
Period1 1%
PV =
c
(1+r)n
2%
3%
4%
5%
6%
7%
8%
9%
10%
12%
14%
15%
16%
18%
20%
24%
28%
32%
36%
1
2
3
4
5
9901
.9803
9706
.9610
.9515
9804
.9612
.9423
9238
9057
9709
9426
9151
.8885
8626
.9615
.9246
8890
.8548
.8219
9524
.9070
.8638
.8227
.7835
9434
.8900
.8396
.7921
.7473
.9346
.8734
.8163
.7629
.7130
.9259
.8573
.7938
.7350
.6806
9174
8417
.7722
.7084
.6499
.9091
8264
.7513
6830
.6209
8929
.7972
.7118
6355
.5674
8772
.7695
.6750
.5921
.5194
8696
.7561
.6575
.5718
4972
.8621
.7432
6407
.5523
4761
8475
.7182
.6086
.5158
.4371
8333
.6944
5787
.4823
4019
8065
.6504
.5245
4z30
.3411
.7813
.6104
.4768
.3725
.2910
.7576
.5739
4348
.3294
.2495
.7353
.5407
.3975
.2923
2149
6
7
8
9
10
.9420
.9327
9235
.9143
.9053
8880
8706
8535
.8368
8203
8375
.8131
.7894
7664
7441
.7903
.7599
7307
.7026
.6756
.7462
.7107
6768
.6446
.6139
7050
.6651
6274
.5919
5584
6663
.6227
5820
5439
5083
.6302
.5835
.5403
.5002
.4632
.5963
.54 70
.5019
.4604
4224
5645
5132
4665
4241
.3855
.5066
4523
4039
.3606
.3220
.4556
.3996
.3506
.3075
.2697
.4323
.3759
3269
.2843
2472
.4104
.3538
3050
.2630
2267
.3704
.3139
2660
.2255
.1911
.3349
.2791
.2326.1938
1615
2751
2218
1789
.1443
1164
2274
.1776
1388
.1084
0847
.1890
.1432
.1085
.0822
.0623
.1580
.1162
.0854
.0628
.0462
11
12
13
14
15
.8963
8874
8787
8700
.8613
8043
7885
7730
.7579
.7430
.7224
7014
6810
661 1
6419
6496
6246
6006
5775
5553
5847
5568
5303
.5051
4810
.5268
.4970
.4688
4423
4173
4751
4440
4150
3878
3624
.4289
.3971
3677
3405
.3152
.3875
.3555
3262
.2992
.2745
3505
3186
2897
2633
.2394
2875
2567
2292
.2046
.1827
.2366
2076
1821
.1597
.1401
2149
.1869
1625
.1413
.1229
.1954
1685
.1452
.1252
.1079
.1619
.1372
.1163
.0985
.0835
.1346
1122
0935
0779
0649
0938
0757
0610
0492
.0397
0662
0517
0404
0316
.0247
0472
0357
0271
0205
0155
0340
0250
0184
0135
0099
16
17
18
19
20
8528
8444
8360
8277
8195
.7284
7142
7002
6864
6730
6232
.6050
5874
5703
5537
.5339
5134
4936
4746
4564
4581
.4363
4155
.3957
.3769
3936
3714
3503
3305
3118
3387
3166
2959
2765
2584
2919
2703
2502
2317
.2145
.2519
.2311
2120
1945
1784
2176
.1978
1799
1635
.1406
1631
.1456
1300
.1161
.1037
1229
.1078
0946
0829
0728
1069
.0929
0006
0703
0611
0930
0802
0691
0596
.0514
.0708
0600
.0508
.0431
.0365
0541
0451
0376
0313
0261
0320
.0258
0208
0168
0135
0193
0150
0118
.0092
0072
one
.0089
0068
0051
0039
0073
0054
0039
0029
0021
25
30
40
50
60
.7798
7419
6717
.6080
.5504
6095
5521
4529
.3715
.3048
4776
4120
.3066
2281
.1697
3751
3083
2083
1407
0951
2953
.2314
.1420
0872
0535
2330
1741
0972
0543
0303
1842
1314
.0668
0339
0173
1460
.0994
.0460
0213
0099
1160
.0754
.0318
0134
.0057
.0923
0573
.0221
.0085
.0033
0588
0334
0107
0035
0011
0378
.0196
0053
.0014
0004
0304
0151
.0037
0009
.0002
.0245
0116
0026
.0006
0001
.0160
.0070
0013
.0003
•
.0105
0042
0007
0001
.0046
.0016
0002
•
0021
0006
.0001
•
•
0010
0002
•
•
•
0005
0001
•
•
•
’Th# l*dcx t3 tto (0 tour docmd pUc«l
o
Annualization Factors for Determining Annual Cost of Facilities
and Equipment for Different Periods of Depreciation and Interest Rates*
Lifetime of
Assets
(n)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
’Annualization Formula:
Interest Rates (r)
5%
10%
15%
0.5378
0.3672
0.2820
0.2310
0.1970
0.1728
0.1547
0.1407
0.5762
0.4021
0.3155
0.2638
0.2296
0.2054
0.1874
0.6151
0.4380
0.3503
0.2983
0.2642
0.2403
0.2229
0.2096
0.1736
0.1627
0.1295
0.1204
0.1128
0.1065
0.1010
0.0963
0.0923
0.0887
0.1540
0.1993
0.1911
0.1468
0.1408
0.1357
0.1849
0.1791
0.1747
0.1315
0.1710
0.1679
0.1278
0.1247
0.0855
0.0827
0.0802
0.0780
0.0760
0.0741
0.1632
0.1195
0.1175
0.1613
0.1598
0.1584
0.1156
0.1140
0.1126
0.0725
0.0710
0.1113
0.1102
0.0696
0.0683
0.0671
0.1092
0.1083
0.0660
0.0651
a(r.n) =
0.1654
0.1219
0.1573
0.1563
0.1554
0.1547
0.1541
0.1535
0.1075
0.1067
0.1531
0.1527
0.1061
0.1523
k(1 ♦ r)n]
Id ♦ r)n - 1]
where r = interest rate andI n- lifetime of asset for depreciation.
Source: Henry M. Levin, Cost-Effectiveness: A Primer (Beverly Hills- Ssop
Publications, 1983), p. 7(L
"----------- --y
S' ba9e
FILE: MOD7TXT.DOC
33
December 26, 1990
APPENDIX B:
COST ALLOCATION
FILE: MOD7TXT.DOC
34
December 26, 1990
APPENDIX B: COST ALLOCATION
Unless one has a sophisticated system for classifying costs as they occur, cost allocation has
to be done after the fact. Some costs are clearly attributable to a specific cost category.
Examples would be Salter scales for growth monitoring, Vitamin A for blindness
prevention, polio vaccines for immunization services, and the time of a family planning
field worker. The more costs there are that can be assigned to a particular category, the
more likely the analysis will be relatively accurate.
Unfortunately, many cost items are not so easily attributable. A common example is a
multi-purpose van that is used to transport supplies to various health centers, take CHWs
to in-service training sessions; provide logistic support for various PHC campaigns; and
pick up visiting dignitaries at the airport. Some drivers keep track of the mileage for each
of these activities. When that is done, the total costs of operating the vehicle can then be
divided among those activities. That is, the costs can be allocated among the cost
categories of interest.
There are several ways to allocate costs. Some are simple and can be done quickly. Others
are more complicated and can be expensive and time-consuming. What is important to
keep in mind is that most of these methods are estimates, and complicated procedures are
not necessarily more accurate than simpler ones. If accuracy is important, the project
should set up a prospective cost allocation system so that costs can be classified as they
occur.
Failing that, the following steps will provide most projects with a reasonably accurate
estimation of actual costs.
Step 1: List the Cost Codes to be used.
See Step 4 in the text and Appendix C 2: Income and Expense Account Worksheet for
more detail on how to do this. For the purposes of this example, we will use the cost
categories and codes described in Step 4 of the text. Those are:
Locations
N = N. Nyakach
C = C. Nyakach
K = Kajulu
Services
C = Clinical
O = Outreach
General Ledger Accounts
P = Personnel
T = Travel
S = Supplies
O = Other Direct Costs
I = Indirect Costs
FILE: MOD7TXT.DOC
Examples:
NCP = N. Nyakach, clinical, personnel costs
COT = C. Nyakach, outreach, travel costs
KOS = Kajulu, outreach, supply costs
35
December 26, 1990
Step 2: Separate the Costs into Direct and Indirect Categories
Direct costs are those that can be attributed (and therefore, allocated) to one or more of
the principal cost categories. Indirect costs cannot be. These costs are sometimes called
"overhead" costs. Figure B-l illustrates one way to separate direct and indirect costs.8 this
step.
Direct Costs
Amount
Figure B-l
Indirect CostsAmount
Community Nurses
Community H.Wkrs
Travel, outreach
Vaccines
Weighing scales
2,000
3,500
1,245
250
144
Accountant
MIS specialist
Property taxes
Insurance
Utilities
500
800
345
125
367
Total
7,139
Total Indirect
2,137
Total Costs
9,276
2,137/7,139 =
.29934
Step 3: Compute the Indirect Cost Rate
The direct costs are listed on the left in Figure B-l and summed, the indirect costs are on
the right, also summed. Total costs are the sum of the direct and indirect costs. It is worth
noting that the total costs are not affected by the classification. The total will remain the
same whether there are more or fewer costs classified as indirect. It is possible, for
example, that utilities, the time of the MIS specialist, and other indirect costs could be
allocated directly to the various health centers. In theory, a PHC project that did nothing
but PHC, and whose staff worked only on PHC would have no indirect costs. Everything
would be directly attributable to the PHC project.
The computation in the lower right-hand corner of Figure B-l shows that indirect costs are
about 30% (.29934) of direct costs. That proportion can be used in two ways: 1) to allocate
indirect costs by adding a proportion of indirect costs to each direct cost item; and 2) to
estimate future indirect costs when preparing budgets, assuming that there will be no
significant change in the categories or amounts. This figure of 30% is sometimes called the
"indirect cost rate".
Thus, one way to allocate indirect costs is to multiply the direct costs of each health center
by 30 percent, and add that amount to each health center’s direct costs, as shown below. If
you decide to use this method, you can allocate the indirect costs now, otherwise, see Step
5.
8 It is important to keep in mind that each project will have to determine which costs it classifies as direct and indirect There are no
rigid rules as to which costs fall into each category. For more guidance on this issue see Appendix C2: Income and Expense Account
Worksheet.
FILE: MOD7TXT.DOC
36
December 26, 1990
Category
Amount Percent
Figure B-2
N. Nyakach
C. Nyakach
Kajulu
Direct
Indirect
7,139
2,137
3,200
958
2,131
638
1,808
541
Total
9,276
4,158
2,769
2,349
.29934
Step 4: Allocate Direct Costs
The direct costs will usually be of two types: 1) those that can be attributed solely to one
direct cost category (in this example, one of the three health centers and one of the two
PHC services); and 2) those that can be attributed to more than one direct cost category.
Note the emphasis on direct cost. The allocation of indirect costs will be addressed in Step
5.
Step 4,1; Code all Direct Costs that Can be Attributed to a Single Cost Category,
This is the easiest allocation step, as described at the beginning of this appendix. Figure B3 illustrates how the direct costs of staff, travel and supplies would be coded for costs
attributable to outreach services at each health center.
Category
Personnel
Community Nurse
Husein
Rahman
Manji
CHW
Bana
Jillani
Farida
Shirazi
Khan
Mohad
Travel
Husein
Rahman
Manji
Supplies
Vaccines
GM cards
Scales
Total
FILE: MOD7TXT.DOC
Code
Figure B-3
Nyakach
NOP
COP
KOP
NOP
NOP
COP
KOP
KOP
KOP
854
NOT
COT
KOT
85
COS
NOS
KOS
223
C. Nyakach
Kajulu
765
812
123
134
112
122
114
133
44
34
65
222
986
1,419
37
1,437
December 26, 1990
Step 4,2; Allocate all Remaining Direct Costs
There are two principal ways to allocate these costs: 1) equally among the cost categories;
or 2) proportionately. It is easiest to do the calculations in percentages.
■
Equal allocation. The total costs are divided equally among the cost
categories. In this example, the costs would be allocated equally to each of
the three health centers, and within each center they would be allocated
equally between outreach and clinic services. Thus, if personnel costs were
3,500, they would be divided equally, among the three health centers =
1166.67 each. Within each health center they would be divided equally
among clinical and outreach services = 583.33.
■
Proportional allocation. The costs are divided according to some formula
that allocates a greater share of the costs to one center over another.
Obviously, this approach can be applied to the total costs or to each
individual cost item.
The following are some of the principal ways that costs can be allocated proportionately:
■
Size. The largest proportion of the costs is allocated to the largest center, on
the assumption that the larger the center, the greater the costs to operate it.
Example: Kajulu is twice the size of the other two centers, therefore 50% of
the costs are allocated to it and 25% to each of the other centers.
■
Volume. The largest proportion of the costs is allocated to the center with
the highest volume (# visits, for example), on the assumption that the greater
the volume of service, the greater the operating costs. Example: N. Nyakach
has half the number of patient visits as the other two centers, therefore, 20%
of the costs are allocate to it and 40% to each of the other two centers.
Category
Figure B-4
N. Nyakach C. Nyakach
Personnel
Nurse Supervisor
Project Director
Health Educator
Code
25%
40%
30%
NOP
25%
20%
35%
COP
50%
40%
35%
KOP
10%
50%
50%
KOT
50%
20%
KOS
Travel
Van, operating exp.
Shipment: refrigerators
Code
NOT
40%
50%
COT
Supplies
Vaccines
Hlth Ed. Supplies
Code
30%
40%
NOS
20%
40%
COS
FILE: MOD7TXT.DOC
38
Kajulu
December 26, 1990
■
Number of Staff. The largest proportion of the costs is allocated to the
center with the largest number of staff, on the assumption that the center
with the largest staff has the higher volume and/or greater activity.
Example: in Central Nyakach there are twice as many outreach as clinic staff,
therefore, the 67 percent of the costs are allocated to outreach and 33
percent to clinic services.
■
Staff timg. This is a refinement of the former approach. The largest
proportion of the costs is allocated to the center with the largest percentage
of staff time (as measured in hours, days, personyears, etc.). Example: the
Project Director spends 40 percent of her time at N. Nyakach, 20 percent at
C. Nyakach and 40 percent at Kajulu. All of her costs are allocated
accordingly.
■
Budget. The largest proportion of the expenses is allocated to the center
with the largest budget, on the assumption that actual expenses will be close
to budgeted expenses. Example: Outreach at Kajulu is budgeted at 35
percent of total costs, clinic services at 65 percent. Expenses are allocated
the same way.
■
Direct Costs. Similar to the above. Example: after allocating all direct costs
that can be attributed to a single cost category (see Step 4.1 above), the
results show that the totals are as follows:
Category
Figure B-5
N. Nyakach C. Nyakach
Kajulu Total
Direct Costs 1,419
986
1,437 3,842
Percentage 36.9
25.7
37.4
100.0
The remaining direct costs would then be allocated according to these
proportions - 36.9 percent to N. Nyakach, etc.
Although this discussion shows how to allocate the sum of the direct costs, the same
procedure can be used for each transaction. That is, the cost of each nurse and CHW
could be allocated to as many cost categories as necessary. That would require that each
charge for personnel would be allocated first to a health center and then to outreach and
clinic services.
Individual cost categories can be allocated according to the unit of measure that is typically
associated with it9.
■
Personnel: Staff time. Example: proportion of staff time spent at each health
center or in each PHC activity.
9. See Estimating Costs for Cost-Effectiveness Analysis: Guidelines for Managers of Diarrhoeal Diseases Control Programmes.
CDD/SER/883 World Health Organization: Geneva. 1988, pp. 23-26.
FILE: MOD7TXT.DOC
39
December 26,1990
■
Supplies: Weight, volume, or number of units. Example: proportion of total
ORS packets shipped to each health center.
■
Facilities: Space or time. Example: proportion of health center space used
for PHC; proportion of time facility is used for Immunization services.
■
Equipment: Time. Example: proportion of time audio-visual equipment is
used for PHC.
■
Vehicles: Distance or time. Example: proportion of total mileage used for
outreach; proportion of time vehicle is used by the PHC project.
The following illustration shows how all of the direct costs can be allocated according to
one criterion, in this case, the proportion of staff time.
Total
Figure B-6
N. Nyakach
C. Nyakach
Staff time
Percent
45 PY*
100.0%
10 PY
22.2%
15 PY
33.3%
20 PY
44.4%
Direct Costs
Allocation
7,139
.222* 7,139
1,586
.333 * 7,139
2,380
.444 * 7,139
3,173
Kajulu
Step 5: Allocate Indirect Costs
Indirect costs can also be allocated equally or proportionately, using the same criteria as
described above.
Equal allocation is fairly straightforward. All of the indirect costs summed in Step 2 would
be divided equally among the three health centers. Then within each health center they
would be allocated equally to clinical and outreach services.
Proportional allocation would involve allocating the indirect costs according to size,
volume, time, budget, or one of the other criteria described above. The example shown in
Step 3 (Figure B-2) is an allocation according to direct costs. The indirect costs are divided
into the same proportions as the direct costs. Figure B-7 shows a second way to make this
calculation. The results are the same as shown in Figure B-2.
Total
Figure B-7
N. Nyakach
C. Nyakach
Kajulu
Direct Costs
Percent
7,139
100.0%
3,200
44.8%
2,131
29.8
1,808
25.3%
Indirect Costs
Allocation
2,137
.448 * 2,137
958
.298 * 2,137
638
.253 * 2,137
541
If the indirect costs were allocated according to staff time, as the direct costs were the
results would be as shown in Figure B-8.
FILE: MOD7TXT.DOC
40
December 26, 1990
Summary
Cost allocation can be done prospectively (as costs occur they are allocated to one or more
cost categories) or retrospectively. Prospective allocation is likely to be more accurate, but
it is also seen as time-consuming by many managers.
Total
Figure B-8
N. Nyakach
C. Nyakach
Kajulu
Staff time
Percent
45 PY*
100.0%
10 PY
22.2%
15 PY
33.3%
20 PY
44.4%
Direct Costs
Allocation
2,137
.222 * 2,137
475
.333 * 2,137
712
.444 * 2,137
950
Retrospective allocation can be done simply by making rough estimates of what the money
was spent on, but it can also be a sophisticated exercise that attempts to allocate the
appropriate amount of each item to the appropriate cost category.
Step 1. In doing a retrospective allocation the first step is to decide which cost categories
and codes are going to be used. All costs will have to be allocated to one or more of these
categories.
Step 2. Next, the costs should be separated into direct and indirect costs.
Step 3t At this point an ’’indirect cost rate" can be computed and used to allocate the
indirect costs to each cost category. Or another method can be used (as described in Step
5).
Step 4. The direct costs can now be allocated, either by dividing them equally among the
major cost categories, or using a criterion to divide them proportionately. The allocation
can be done item by item or for all of the direct costs combined.
Step 5, Finally, the indirect costs can be allocated, either equally or proportionately, as
described in Step 4.
FILE: MOD7TXT.DOC
41
December 26, 1990
APPENDIX C
TEMPLATES, TOOLS, CHECKLISTS
AND COMPUTER PROGRAMS
FILE: MOD7TXT.DOC
42
December 26, 1990
APPENDIX C 1
BLANK FORMS
FILE: MOD7TXT.DOC
43
December 26, 1990
WORKSHEET for SPECIFYING OBJE Wi: S.
User/Audience:
Manager
Other:
Board of Directors
Central Directorate
___Donors
_______________ __________________________
Purpose:
Monitoring
Efficiency
_ Planning
Other:
Scope:
_ Geographic area
_ Program/project/activity
Time/Duration
Expenditures &/or Revenues
Other:
___
WORKSHEET FOR DECIDING WHAT TO COST
Level 1: PHC Program (Total Costs)
------------------------------
Level 2: Projects, Subprojects, Locations, Sites, etc.
Level 3: PHC Service Components or Managment Components
Level 4: General Ledger Accounts
FILE: MOD7TXT.DOC
44
December 26, 1990
WORKSHEET FOR SPECIFYING TABLES AND GRAPHS NEEDED
1. The total amount of resources spent
2. Expenditures compared with budgets
3. The distribution of costs by line item
4. The distribution of costs by site or facility
5. Trends in costs over time
6. Projections of future costs
7. Average costs
8. Analysis of revenues (past and projected)
9. Break even analysis (revenues compared to expenditures)
10. Other:
TIME ANALYSIS WORKSHEET
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Total Percent
Day:
CODE:
IMM
GM
Admin
.Sup___
Other
Total.
FILE: MOD7TXT.DOC
45
December 26, 1990
APPENDIX C 2
INCOME AND EXPENSE ACCOUNT CODING WORKSHEET
Also See Computer File:
ACCOUNTS.WK1
FILE: MOD7TXT.DOC
46
December 26, 1990
19-Jul-90
PHC MAP INCOME AND EXPENSE ACCOUNT WORKSHEET
FILE: ACCOUNT. WK1
PROJECT:
For the Period of:
INSTRUCTIONS: Determine the level of detail you want for your cost
analysis.
You may need to have codes for up to four levels so that you
can analyze costs (and income) for: a) the overall PHC Program; b) each
project, subproject, location or site; c) PHC service component or
or activity; and d) general ledger accounts.
In general, the more detail you want, the greater the amount of work
required to code each expenditure.
The following example shows a
relatively simple coding system that requires only three letters or
digits for each expenditure.
ACCOUNT CODES
Program:
Codes
NPPHC*
Subprojects: Chitral
Gilgit
C or 1
G
2
Activities:
0
C
M
1
2
3
P
T
C
0
I
1
2
3
4
5
Outreach
Clinical
Management
Ledger Accounts
Personnel
Travel & per diem
Commodities
Other Direct Costs
Indirect Costs
Examples:
COT (or 112) = Chitral , Outreach, Travel
GMI (or 235) = Gilgit, Management, Indirect Cost
* Code for NPPHC not necessary as their is only one PHC Program.
The system illustrated above will enable you to compute total project
costs, costs of each location (Chitral and Gilgit), costs of each major
activity for the overall project and by location; and cost of each major
ledger item (personnel, etc.), again for the overall project, for each
location, and for each activity.
Use this format and the summary of cost categories below to construct
your own coding system.
Then print it out and use it as a reference
for entering cost data into the computer program.
Examples of codes using both letters and digits are shown below.
codes must always follow the same sequence.
For example:
The
Program
Subproject Component
Ledger Acct.
XXXX-XXX
XXX
AXU-Urban Baba Is.
Malaria
Wages
The code would be written
U
B
MAL
P
UBMALP
or
!
7
110
401
17110401
*************************************************************************
FILE: MOD7TXT.DOC
47
December 26, 1990
DETAILED ACTIVITY AND LEDGER ACCOUNT CODING OPTIONS
The following is a checklist of common PHC activities and General
Ledger accounts with sugested codes.
You can also use digits or make
up your own coding scheme.
ACTIVITIES
PHC Services
Oral Rehydration Therapy
Growth Monitoring/Nutrit.
Immunization
Maternal Care (ANC+Deliv)
Family Planning
Health Education
Water & Sanitation
Curative Services
Tuberculosis
Malaria
Acute Respiratory Infect.
Letter Digit
100
P
101
PORT
102
PGMN
103
PIMM
104
PMC
105
PFP
106
PHE
PWS
107
PCS
108
109
PTB
PMAL
110
PARI
111
Management Support Services
Planning
Training
Supervision
Financial
Information Mgmt.
Community Org.
Personnel Mgmt
Research
M
MPL
MTR
MSUP
MFI
MIN
MORG
MPER
MRES
200
201
202
203
204
205
206
207
208
I
IF
IG
ID
IC
300
301
302
303
304
E
EP
EPW
EPF
EPC
EPT
400
EC
ECD
ECS
ECH
ECO
410
411
412
413
414
ET
420
421
422
423
LEDGER ACCOUNTS
Income
Service fees
Sales of goods
Donor contributions
Contributions
Expenses (DIRECT)
Personnel
Wages & Salaries
Fringe benefits
Consultants
Temporary labor
Commodities
Drugs, medicines
Office supplies
Health Ed supplies
Other commodities
Travel
Local Travel
International
Per diem
FILE: MOD7TXT.DOC
ETL
ETI
ETP
48
401
402
403
404
December 26, 1990
Capital Expenditures
Buildings
Vehicles
Medical equipment
Audio-visual
Office equipment
ECAP
ECAPB
ECAPV
ECAPM
ECAPAV
ECAPOF
430
431
432
433
434
435
Other Direct Costs
Advertising
Bank charges
Books, subscriptions,dues
Conferences
Data processing
Depreciation
Equipment rental
Freight
Insurance
Interest - mortgage
Interest - other
Laundry & cleaning
Legal & Accounting
Moving & storage
Parking
Participant train.
Photocopying
Postage, courier
Printing
Recruiting & relocation
Rent
Repairs & Maint.
Security
Stipends
Taxes
Telephone, fax
Utilities
Vehicle fuel, maint.
Videotaping
EO
EOA
EOBANK
EOBOOK
EOCON
EODATA
EODEP
EOEQ
EOFR
EOINS
EOINTM
EOINTO
EOLAUN
EOLEG
EOMOV
EOPARK
EOTR
EOPH
EOPOST
EOPRN
EOREC
EORENT
EOREP
EOSEC
EOSTIP
EOTAX
EOTEL
EOUTIL
EOV
EOVID
440 450 460
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
ESUB
500
El
600
(Name:
Subcontracts
)
Indirect Costs
Indirect costs will need to be determined for each project.
Some PHC
projects may not have indirect costs, others could have a duplicate
of the direct cost codes, i.e., personnel, commodities, travel, etc.
An example of "typical11 indirect costs is shown below.
Wages & Salaries
Fringe benefits
Consultants
Temporary labor
EIP
EIPW
EIPF
EIPC
EIPT
610
611
612
613
614
Office supplies
Other commodities
EIC
EICO
EICC
620
621
622
Personnel
Commodities
FILE: MOD7TXT.DOC
49
December 26, 1990
Travel
EIT
EITL
EITI
EITP
630
631
632
633
Capital Expenditures
Buildings
Vehicles
Office equipment
EICAP
EICAPB
EICAPV
ElCAPO
640
641
642
643
Other Indirect Costs
Advertising
Bank charges
Books , subscriptions,dues
Conferences
Data processing
Depreciation
Equipment rental
Freight
Insurance
Interest - mortgage
Interest - other
Laundry & cleaning
Legal & Accounting
Moving & storage
Parking
Participant train.
Photocopying
Postage, courier
Printing
Recruiting & relocation
Rent
Repairs & Maint.
Security
Stipends
Taxes
Telephone, fax
Utilities
Vehicle fuel, maint.
Videotaping
650 660 670
EIO
EIOAD
651
EIOBANK 652
EIOBOOK 653
EIOCON 654
EIODATA 655
EIODEP
656
657
EIOEQ
658
EIOFR
EIOINS
659
EIOINTM 660
EIOINTO 661
EIOLAUN 662
EIOLEG 663
EIOMOV 664
EIOPARK 665
666
EIOTR
667
EIOPH
EIOPOST 668
EIOPRN 669
EIOREC 670
EIORENT 671
EIOREP 672
EIOSEC 673
EIOSTIP 674
EIOTAX 675
EIOTEL 676
EIOUTIL 677
EIOV
678
EIOVID
679
Local Travel
International
Per diem
FILE: MOD7TXT.DOC
50
December 26,1990
APPENDIX C 3.1
COST ANALYSIS WORKSHEET
VERSION 1
General Ledger Accounts Only
Also see Computer File MOD7V1.WK1
—*
FILE: MOD7TXT.DOC
51
APPENDIX C 3.1: COST ANALYSIS WORKSHEET, VERSION 1:
General Ledger Accounts only
This first worksheet is designed to be used to enter transactions (income and expenditures)
for a single program, project, subproject, health center, or component service. That is, it
will provide a standard accounting breakdown of expenditures by general ledger accounts.
This worksheet would be most useful for PHC programs that merely want an analysis of
total costs broken down by such lines as personnel wages, drugs, equipment, laundry, rent,
etc.
The worksheet is basically a program for entering and tabulating data. It is adapted from a
program published in Lotus magazine10 designed for small businesses without full-time
bookkeepers or accountants. It includes several macros that make it easy to enter income
and expense transactions following a set of account codes that the user can modify as
desired. Other macros check for mistakes, sort income and expenditures into a summary
table, and also keep a record of all transactions entered. The worksheet is not meant to
replace normal accounting and bookkeeping procedures. Rather, it is designed specifically
for cost analysis. In many cases, the information from routine financial reports can be
entered directly into this file to start the cost analysis.
The model uses Lotus 1-2-3, version 2.01 and higher. The principal macro in the model
provides a series of prompts that guide the user through the data entry process. The data
(from receipts, journals, invoices, checks, etc.) can be entered in any order. When data
entry is completed the macro sorts the entries into the categories shown in Figure 1 and
places the total for each category in the Amount column. That is, the program adds all of
the entries that have the same code and places the totals in the proper cell. For example,
suppose there were 12 entries for Service Fees received. The code for this entry is IS
(Income, Service). The program will add all of those entries and put the total in the
"Service fees" row in the "Amount" column (cell CIO).
The program also computes the total income (row 14), the total expenses (row 49), and the
difference (row 51, "Net Profit"). If budget figures are inserted in column D, the program
will also compute the differences between the actual and budgeted amounts and put the
result in the Variance Column (E).
To use this worksheet, follow these steps.
Step l:Modify the Account Categories and Codes
Figure 2 shows the income and expense categories and corresponding codes in the current
worksheet. These are located in a "Help" screen in columns K and L. To change them, just
type in new labels in each column. For example, to replace "Service fees" with "Community
Contributions" just type Community Contributions in K2. Make up a code (say IC) and
type that in column 12. When finished, enter the new labels from column K in Column A
on the spreadsheet. The labels must match for the program to work correctly.
10. Stephen L. Nelson, "Keeping the Books: An Income and Expense Recorder". Lotus, May, 1990, pp. 52-55.
FILE: MOD7TXT.DOC
52
December 26, 1990
Although rows can be inserted or deleted, this requires making other changes in the
program that require a working knowledge of macros. Before trying that, examine
Versions 2 and 3 to see if they would fit your needs better.
FILE: MOD7TXT.DOC
53
December 26, 1990
Figure 1
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
1A
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
37
39
40
41
42
43
44
45
46
47
48
49
50
51
52
B
C
D
PHC MAP COST ANALYSIS WORKSHEET
E
VERSION 1
PROJECT NAME:
Income and Expense Summary for (MM/YY):
Description
Code
INCOME CATEGORIES:
I
Service fees
Donor support
Other income
IS
ID
10
19-JU1-90
FILE: MOD7V1 .WK1
Amount
Budget
Variance
$235.88
$8,545.67
$259.00
$300.00
$8,500.00
$100.00
($64.12)
$45.67
$159.00
$9,040.55
$8,900.00
$140.55
$0.00
$10.00
$445.00
$0.00
$800.00
$25.00
$1,156.89
$1,245.00
$124.67
$234.00
$0.00
$0.00
$35.67
$0.00
$175.55
$355.86
$0.00
$67.12
$0.00
$266.00
$0.00
$258.00
$45.76
$56.87
$1,050.00
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$10.00
$500.00
$0.00
$600.00
$25.00
$1,200.00
$1,500.00
$150.00
$250.00
$0.00
$0.00
$35.00
$0.00
$200.00
$400.00
$0.00
$50.00
$0.00
$300.00
$0.00
$250.00
$25.00
$50.00
$1,050.00
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
($55.00)
$0.00
$200.00
$0.00
($43.11)
($255.00)
($25.33)
($16.00)
$0.00
$0.00
$0.67
$0.00
($24.45)
($44.14)
$0.00
$17.12
$0.00
($34.00)
$0.00
$8.00 ■
$20.76
$6.87
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
Total Expenses
$6,351.39
$6,595.00
($243.61)
Net Profit
$2,689.16
$2,305.00
$384.16
Total Income
EXPENSE CATEGORIES:
E
Advertising
Bank charges
Consultant fees
Depreciation
Drugs, medicines
Dues & publications
Employee benefits
Equipment
Freight, postage, courier
Insurance
Interest - mortgage
Interest - other
Laundary and cleaning
Legal and professional
Office expense
Rent - facilities
Rent - other
Repairs
Subcontracts
Supplies
Taxes
Travel and per diem
Telephone, fax
Utilities
Wages, salaries
Other 1
Other 2
Other 3
Other 4
Other 5
EA
EB
EC
EDE
EDR
EDU
EEM
EEQ
EF
EINS
EINM
EINO
ELA
ELE
EO
ERF
ERO
EREP
ESUB
ESUP
ETAX
ETR
ETEL
EU
EW
EO1
E02
EO3
E04
E05
________________ Figure 2_________________
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
K
L
CATEGORY
Service fees
Donor support
Other income
Advertising
Bank charges
Consultant fees
Depreciation
Drugs, medicines
Dues & publications
Employee benefits
Equipment
Freight, postage, courier
Insurance
Interest - mortgage
Interest - other
Laundary and cleaning
Legal and professional
Office expense
Rent - facilities
Rent - other
Repairs
Subcontracts
Supplies
Taxes
Travel and per diem
Telephone, fax
Utilities
Wages, salaries
Other 1
Other 2
Other 3
Other 4
Other 5
CODE
IS
ID
IO
EA
EB
EC
EDE
EDR
EDU
EEM
EEQ
EF
EINS
EINM
EINO
ELA
ELE
EO
ERF
ERO
EREP
ESUB
ESUP
ETAX
ETR
ETEL
EU
EW
EO1
EO2
EO3
EO4
EO5
Step 2.Enter A Budget.
Projected income as well as expenses can be entered in Column D. You do not need to
enter a budget for the program to work. If the budget isn’t broken down in this much
detail, you can always aggregate the subtotals later to compare with the broader budget
lines.
FILE: MOD7TXT.DOC
55
December 26, 1990
Step 3. Enter the Transactions.
3.1
Hold down the macro key (Alt on most computers) and press r (for record) to start
the macro. The macro will display the "Income & Expense Transaction Database" (see
Figure 3) and will prompt you to enter the "Date & Description" of the first transaction.
You can enter the date anyway you wish (July 27, 1990; 7/29/90; 29-7-1990) or ignore the
date and just enter a description of the transaction (e.g., City electric, Staff salaries, MIS
supplies). Figure 4 shows a number of examples.
Figure 3
G
H
I
551 Income & Expense Transactions Database
561-------------------------------------------------------------------571 Date & Description
Code Amount
581-------------------------------------------------------------------59|
601----------------- END OF TRANSACTION LIST-------
You may also enter other descriptive data that may be important. Some examples: August
telephone, Check # 234; Invoice 77, 29-7-90).
Figure 4
H
G
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
I
Income & Expense Transactions Database
Date & Description
Code
Amount
7/12/90 Benston Plumbing
7/12/90 AKF reimbursement
7/12/90 Barbra Braile
7/15/90 Air conditioners
7/23/90 AKF payment
7/25/90 Service fees
7/30/90 July salaries
8/1/90 Barbra Braile
8/2/90 Service fees
8/3/90 Raffle proceeds
8/5/90 Center rent Aug
8/12/90 Ralston Electric
8/15/90 AKF Qtly payment
8/25/90 Drugs
8/30/90 August salaries
EREP
ID
EC
EEQ
ID
IS
EW
EC
IS
IO
ERF
EREP
ID
EDR
EW
$34.56
$45.67
$245.00
$1,245.00
$3,500.00
$85.88
$550.00
$200.00
$150.00
$259.00
$355.86
$32.56
$5,000.00
$800.00
$500.00
---------------------------- END OF TRANSACTION LIST
FILE: MOD7TXT.DOC
56
December 26, 1990
3.2
After typing the description, press Enter. The macro prompts for a transaction
code. Enter the appropriate code from the list in column L (example: drugs = EDR,
Wages = EW). It is best to have a copy of the list handy for reference. However, the
program includes a ’’help'’ macro if you need to view the codes. As the prompt says, ’’press
Enter" instead of entering a code to view the help screen. The screen will split and show
Figure 2 on the right side. Move the cursor with the up or down arrow to the appropriate
code and press Enter. The macro will record this code in a temporary range and copy it to
your "Income & Expense Transaction Database" when you finish entering this transaction.
The program includes an error-checking routine so that if you enter an incorrect code by
mistake, it will tell you and prompt you to enter a correct code. The codes are text
sensitive. You must enter capital letters if the codes are capitalized.
3.2
After pressing Enter the prompt asks for the amount of the transaction. Enter a
number, with decimal points if appropriate. Both income and expense transactions should
be positive amounts. A negative entry would be appropriate for transactions that reduce
income or expenses (e.g., a bank credit for an error would be entered as a negative
"expense"; return of a community contribution would be entered as negative "income").
Step 4:Tabulate the Data
When all entries have been completed, press Enter. The program will compute the totals
for the income categories and for the expense categories. The macro will return to cell Al
at the top of the worksheet.
Step 5:Review for Mistakes and Correct Them
If you make a mistake in an entry, go to the database (cell G55 - press Alt + D to move
there quickly). Make the appropriate corrections to the amount or code. You can also
delete rows that are incorrect, as long as you do not delete the blank row at the bottom of
the database.
Press Alt + R and Enter when finished to recalculate the totals. You can also enter new
transactions by pressing Alt + R.
Step 6:Save the File
Although you can keep adding data to this file, you may want to save it at the end of each
accounting period. Always save the file under a different name so that you will always have
a blank worksheet to work with when you need it.
You can customize this spreadsheet. See Appendix C 3.2 for instructions.
FILE: MOD7TXT.DOC
57
December 26, 1990
MACROS for MOD7V1.WK1
\r
cde
amt
{snug}
{GETLABEL "Enter date & description (Up to 35 chars): ",tmpdesc
{IF tmpdesc=""}{tabl}{QUIT}
{GETLABEL "Enter code <Press Return for helplist > :",tmp code}
{IF tmp code=""}{helper}
{IF @ISERR(@VLOOKUP(tmp_code,codes,0))}{errmsg}{BRANCH cde}
{GETNUMBER "Enter income or expense amount: ",tmp amt}
{IF @ISERR(tmp amt)} {errmsg} {BRANCH amt}
/WIR —
/Ctmp area—
{CALC}/WTC{BRANCH \r}
tabl
/WTC{INDICATE WAIT} {PANELOFF}
/DTltable 1 - crit 1 /DT1TABLE2 - CRIT1 {CALC}
{HOME} {PANELON} {INDICATE}
helper
{RIGHT 2}
/WWV/WWU{ WINDOW} {GOTO}helplist~ {RIGHT}
/WTB{?}
{LET tmp code,@CELLPOINTER("contents")}
{WINDOW} {LEFT 1}
/WTC/WWC
snug
{GOTO}trans~{END}{DOWN 2}{UP @ROWS(database)-3}
/WTH{DOWN @ROWS(database)-3}
errmsg
Invalid entry. Press any key to continue.{GET key}{ESC}
\d
{GOTO} database-{UP 2}
key
tmpdesc tmp_code tmp_amt Code
FILE: MOD7TXT.DOC
58
December 26, 1990
APPENDIX C 3.2
COST ANALYSIS WORKSHEET
VERSION 2
User-Generated Accounts
Also see Computer File MOD7V2.WK1
FILE: MOD7TXT.DOC
59
December 26, 1990
APPENDIX C 32: COST ANALYSIS WORKSHEET, VERSION 2:
User-Generated Accounts
This worksheet is a modification of Version 1. The major difference is that the income and
expenses categories and codes have been revised to classify transactions by three levels,
instead of one, as in Version 1. This will enable the analyst to examine the data by: 1)
location; 2) service component; and 3) general ledger accounts.
In this example, Level 1 codes were developed for the three locations of the Kisumu PHC
Project:
Kajulu
North Nyakach
Central Nyakach
K
N
C
Two Level 2 codes were developed:
Outreach
Clinical Services
O
C
And five general ledger codes were selected:
Personnel
Travel
Supplies
Other Direct Costs
Indirect Costs
P
T
S
O
I
The expense categories and codes were changed on the worksheet as shown in Figure 1.
The income categories and codes were not changed since we were only interested in the
aggregate amounts, not the breakdowns by location. (See NOTES 1 and 2)11
The codes are also changed automatically in the help screen (see Figure 2). The macros
work exactly as in Version 1. So, to use the worksheet, just enter budget figures in Column
D (if appropriate), then hold down Alt + r to enter the transactions (see Figure 3).
Tabulate the data when done by pressing Enter, review for mistakes, correct them, and
save the file - under a different name. Figure 1 is a sample printout of the completed file,
showing subtotals for each General Ledger Account and for each Location and site. (See
NOTE 3).
11. See Notes at the end of this section for information about how to customize the spreadsheet.
FILE: MOD7TXT.DOC
60
December 26, 1990
Figure 1
A
1
2
3
4
5
6
7
8
9
10
11
12
-1.1 ?
14
1F
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
□coO
A
~
B
PHC MAP COST ANALYSIS WORKSHEET
c
D
VERSION 2
E
24-JU1-90
F
PROJECT NAME:
FILE: MOD7V2. WK1
Income and Expense Summary for (MM/YY):
Description
Amount
Budget
$12,567.00
$55,000.00
$5,489.00
$10,000.00
$50,000.00
$5,000.00
$2,567.00 125.7%
$5,000.00 110.0%
$489.00 109.8%
$73,056.00
$65,000.00
$8,056.00 112.4%
$8,665.00
$3,500.00
$678.00
$1,573.00
$1,658.00
$1,256.00
$10,181.00
$3,895.00
$497.00
$1,500.00
$2,500.00
$1,789.00
$13,122.00
$6,543.00
$436.00
$1,433.00
$3,455.00
$1,255.00
$12,813.00
$6,734.00
$456.00
$357.00
$3,589.00
$1,677.00
$17,825.00
$6,899.00
$563.00
$2,365.00
$4,533.00
$3,465.00
$23,031.00
$9,654.00
$876.00
$2,546.00
$4,533.00
$5,422.00
$9,900.00
$4,500.00
$450.00
$1,250.00
$2,200.00
$1,500.00
$8,300.00
$3,500.00
$350.00
$900.00
$2,300.00
$1,250.00
$13,840.00
$6,800.00
$540.00
$1,500.00
$3,500.00
$1,500.00
$13,100.00
$5,475.00
$325.00
$1,200.00
$4,500.00
$1,600.00
$18,725.00
$8,600.00
$450.00
$1,675.00
$5,500.00
$2,500.00
$18,020.00
$8,800.00
$350.00
$1,870.00
$3,500.00
$3,500.00
765.00
87.5%
($1,000.00) 77.8%
$228.00 150.7%
$323.00 125.8%
($542.00) 75.4%
($244.00) 83.7%
$1,881.00 122.7%
$395.00 111.3%
$147.00 142.0%
$600.00 166.7%
$200.00 108.7%
$539.00 143.1%
($718.00) 94.8%
($257.00] 96.2%
($104.00] 80.7%
($67.00] 95.5%
($45.00' 98.7%
($245.oo; 83.7%
($287.00
97.8%
$1,259.00 123.0%
$131.00 140.3%
($843.00
29.8%
($911.00
79.8%
$77.00 104.8%
($900.00) 95.2%
($1,701.00
80.2%
$113.00 125.1%
$690.00 141.2%
($967.00) 82.4%
$965.00 138.6%
$5,011.00 127.8%
$854.00 109.7%
$526.00 250.3%
$676.00 136.1%
$1,033.00 129.5%
$1,922.00 154.9%
Total Expenses
$62,606.00
$63,865.00
($1,259.00)
Net Surplus (Deficit)
$10,450.00
$1,135.00
$9,315.00 920.7%
Code
Variance
%
INCOME CATEGORIES:
Service fees
Donor support
Other income
IS
ID
IO
Total Income
EXPENSE CATEGORIES:
Kajulu Outreach
Personnel
Travel
Supplies
Other Direct Costs
Indirect Costs
Kajulu Clinical
Personnel
Travel
Supplies
Other Direct Costs
Indirect Costs
N.Nyakach Outreach
Personnel
Travel
Supplies
Other Direct Costs
Indirect Costs
N.Nyakach Clinical
Personnel
Travel
Supplies
Other Direct Costs
Indirect Costs
C.Nyakach Outreach
Personnel
Travel
Supplies
Other Direct Costs
Indirect Costs
C.Nyakach Clinical
Personnel
Travel
Supplies
Other Direct Costs
Indirect Costs
FILE: MOD7TXT.DOC
KO
KOP
KOT
KOS
KOD
KOI
KC
KCP
KCT
KCS
KCD
KCI
NO
NOP
NOT
NOS
NOD
NOI
NC
NCP
NCT
NCS
NCD
NCI
CO
COP
COT
COS
COD
COI
CC
CCP
CCT
CCS
CCD
CCI
61
December 26, 1990
98.0%
________________ Figure 2________________
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
FILE: MOD7TXT.DOC
K
L
CATEGORY
Service fees
Donor support
Other income
Kajulu Outreach
Personnel
Travel
Supplies
Other Direct
Indirect Costs
Kajulu Clinical
Personnel
Travel
Supplies
Other Direct
Indirect Costs
North Nyakach Outreach
Personnel
Travel
Supplies
Other Direct
Indirect Costs
North Nyakach Clinical
Personnel
Travel
Supplies
Other Direct
Indirect Costs
Central Nyakach Outreach
Personnel
Travel
Supplies
Other Direct
Indirect Costs
Central Nyakach Clinic.
Personnel
Travel
Supplies
Other Direct
Indirect Costs
CODE
IS
ID
IO
KO
KOP
KOT
KOS
KOD
KOI
KC
KCP
KCT
KCS
KCD
KCI
NO
NOP
NOT
NOS
NOD
NOI
NC
NCP
NCT
NCS
NCD
NCI
CO
COP
COT
COS
COD
COI
CC
CCP
CCT
CCS
CCD
CCI
62
December 26,1990
Figure 3
H
G
55
Income
Database
&
Expense
57
Date & Description Code
59
60
61
62
63
64
65
66
67
68
69
70
71
72
Jan salaries
Jan salaries
Jan salaries
Jan salaries
Jan salaries
Jan salaries
Feb salaries
Feb salaries
Feb salaries
Feb salaries
Feb salaries
Feb salaries
KOP
KCP
NOP
NCP
COP
CCP
KOP
KCP
NOP
NCP
COP
CCP
I
Transactions
Amount
356.00
245.00
315.96
225.78
215.78
365.00
415.00
246.00
265.96
229.78
265.78
285.00
----- END OF TRANSACTION LIST-—
NOTES:
1.
To change these codes and expense categories to something else, replace the labels
on the worksheet (columns A and B) with the new expense category names and codes.
That will automatically change the labels in Columns K and L, also.
2.
You can insert or delete rows of expense categories and codes. It is probably not
worth the effort to delete lines. Just leave some unused. To add more lines of expenses
(and codes) you will need to expand those lists. Although this can be done by inserting
rows, this can also affect the macro’s operation. The easiest way to add rows is to use the
Move command, as follows:
2.1. Determine the number of rows that need to be added.
2.2. Place the cursor on any row above the last expense category (for example,
A40).
2.3. Move everything below that row down the same number of rows that are to
be added. For example, if seven rows are to be added, move the block
A40...F60 down seven rows to A47, and press enter (/, Worksheet, Move,
{highligh area to move}, Enter, {move cursor to A47}, Enter.
Do the same thing with the help list. That is, move seven rows down from
K35...L35 to K42.
FILE: MOD7TXT.DOC
63
December 26,1990
This procedure enables you to avoid inserting blank lines in the macros,
which would make them inoperable.
2.4. Next, copy one of the preformated lines into the blank spaces. For example,
copy A39...F39 to A40...A47. Do the same in the help list. Copy K34..L34 to
K35...K42.
2.5. Finally, you can type in the names of the new expense categories and the new
codes. Remember to have the same names and codes in the help list and the
prime worksheet.
2.6. Save the new worksheet in a separate file.
3.
If you modify the expense categories by adding or deleting rows, you will need to
modify the macro that computes the Location subtotals in the Amount column. After the
transactions have been summed and inserted in the worksheet, the /t macro copies the
@SUM formulas from column D to column C. This produces the subtotals for each
category (e.g., C18, C24, etc.) after all of the transactions have been summed.
Currently, the macro goes to cells D18, D24, D30, D36, D42 and D48, where it copies the
formulas to the corresponding cells in column C (i.e., C18, C24, etc.). If you insert or
delete a row in the spreadsheet, you will need to change those addresses. For example, if
you insert two rows above row 11, all of the expense categories will be lowered two rows.
In that case, go to the /t macro (cell N43) and change the addresses as follows:
From
\t {GOTO}D18~/C~{LEFT}~
{GOTO}D24 - /C-{LEFT} {GOTO}D30~/C~ {LEFT}{GOTO}D36~/C~ {LEFT} {GOTO}D42~/C~{LEFT} {GOTO}D48~/C~ {LEFT} {HOME} {PANELON} {INDICATE}
To
{GOTO}D20~/C~ {LEFT} { GOTO} D26 - /C - {LEFT} {GOTO} D32-/C-{LEFT}{GOTO}D38~ /C~ {LEFT} { GOTO} D44 - /C - { LEFT} {GOTO}D50~/C~{LEFT}~
{HOME} {PANELON} {INDICATE}
If you add or delete subtotal categories, remember to add or delete corresponding lines in
±e macro.
FILE: MOD7TXT.DOC
64
December 26, 1990
MACROS for MOD7V2.WK1
\r
cde
amt
{snug}
{GETLABEL "Enter date & description (Up to 35 chars): ",tmpdesc
{IF tmpdesc=""} {tabl} {QUIT}
{GETLABEL "Enter code <Press Return for help list > :",tmp_code}
{IF tmp code=""}{helper}
{IF @ISERR(@VLOOKUP(tmp_code,codes, 0))}{errmsg}{BRANCH cde}
{GETNUMBER "Enter income or expense amount: ",tmp_amt}
{IF @ISERR(tmp amt)}{errmsg}{BRANCH amt}
/WIR~
/Ctmp area—
{CALC}/WTC{BRANCH \r}
tabl
/WTC{INDICATE WAIT} {PANELOFF}
/DTltable 1 ~ crit 1 ~
/DT1TABLE2 - CRIT1 {CALC}{\s}
helper
{RIGHT 2}
/WWV/WWU{ WINDOW} {GOTO} helplist ~ {RIGHT}
/WTB{?}
{LET tmp code,@CELLPOINTER("contents")}
{WINDOW} {LEFT 1}
/WTC/WWC
snug
{GOTO}trans~{END}{DOWN 2}{UP @ROWS(database)-3}
/WTH{DOWN @ROWS(database)-3}
errmsg
Invalid entry. Press any key to continue.{GET key}{ESC}
\d
{GOTO}database~{UP 2}
key
tmpdesc tmp_code tmp_amt Code
\c
{GOTO}helplist~{UP 1}
\w
{HOME}
\t
{GOTO}D18~/C~{LEFT}~
{GOTO} D24 ~ /C ~ { LEFT} ~
{ GOTO } D30 ~ /C ~ {LEFT} ~
{GOTO}D36~ /C~ {LEFT} {GOTO}D42~/C~{LEFT}~
{GOTO}D48~/C~{LEFT}~
{HOME} {PANELON} {INDICATE}
FILE: MOD7TXT.DOC
\s
65
{GOTO}TRANS~{DOWN 4}
/DSRD.{end} {down} {right 2}~
P{right 1}~A~S~A~G
December 26,1990
APPENDIX C 33
COST ANALYSIS WORKSHEET
VERSION 3
Open-ended, User-Generated Accounts
Also see Computer File MOD7V3.WK1
FILE: MOD7TXT.DOC
66
December 26,1990
APPENDIX C: COST ANALYSIS WORKSHEET, VERSION 3
Open-ended, User-Generated Accounts
This is the most flexible worksheet. It does not require any pre-definition of income or
expense categories and codes in the worksheet, llie categories and codes need to be
defined, but that can be done on a piece of paper. They do not have to be inserted in the
computer program file, as with Versions 1 and 2. The biggest advantage of this version is
that the codes can be as simple or complex as the user wants. They can also be modified as
one goes along. For example, if a PHC project adds a service (Nutrition) or a service
location (South-Central Kajulu), codes can be added without changing the program. Just
make up a code for Nutrition or South-Central Kajulu and type it in when prompted.
The major disadvantage is there is no help screen or error checking when you enter codes.
You can enter a non-existant code and the program will accept it. See Figure 1 for an
example of transactions.
Figure 1
________________________________
42
PHC PROJECT INCOME AND EXPENSE WORKSHEET
24-JU1-90
43
PROJECT:
For Period:
44
Date
Description
Code
Amount Subtotal
45-------------------------------------------------------------------------------------------------------------------------46
47
23/7/90
64.56
Plywood for shelves
FR5
48
24/7/90
342.00
Salaries: Chris Cross P2
49
23/7/90
89.65
Lumber
FR5
50
23/9/90
345.67
Furniture mart
F7
FR5
45.89
51
12/7/90
Lumber bits
A3
33.55
52
12/7/90
Repair truck tires
456.00
53
30/7/90
Salaries: Martha Page P2
345.00
12/7/90
P2
54
Salaries: Jon Brown
10.98
23/7/90
Garden supplies
TR5
55
212.00
A3
56
26/7/90
Auto repairs
10.98
TR5
23/7/90
Garden supplies
57
Also, this program does not produce a single summary list of expense categories as in
Versions 1 and 2. Rather, it produces a listing of all expenses for each category and its
subtotal. See Figure 2 for an example. This is advantageous for analysis of the detailed
income and expenditure transactions, however.
The worksheet is also very simple to use. There are four macros that enable you to: 1)
enter transactions (Alt + R); 2) sort the entries into similar categories (Alt + S); 3)
compute subtotals and a grand total (Alt + L); and 4) print the results in compressed type
(Alt + E).
To use this Worksheet the steps are similar to those of Versions 1 and 2:
FITE: MOD7TXT.DOC
67
December 26,1990
1.
Develop the Income and Expense categories and codes. Remember, the
program will accept any codes (including those illustrated in Versions 1 and
2) and no labels have to be entered into the program in pre-determined
expense categories or help lists.
2.
Press Alt + R to begin data entry, just as in Versions 1 and 2. A similar
transaction screen will appear, except you must enter a date separately. If
you do not wish to enter a date, just enter any character (x, ") and enter.
Enter the remaining data: description, code, amount.
3.
When you finish entering the transactions, press Enter twice at the "Date"
prompt. This will stop the macro. You can stop here, save the file (under a
new name) and return to it later to enter more transactions.
FILE: MOD7TXT.DOC
68
December 26, 1990
4.
When all data entry is completed for the accounting period (month, quarter,
year), save the file and then press Alt + S to sort the data and compute
subtotals. The transactions will be sorted by code and by date within each
code. It is important to save the file first, because once the data has been
sorted and tabulated, it is more difficult (but not impossible) to make
corrections.
5.
After the first sorting and tabulating is completed, check for errors, reload
the file, make the corrections, save the file again, then run Alt + S again to
resort and retabulate subtotals.
6.
If you have budget figures, enter them for each category in Column F. If you
would like to compute the variance between the budget and expenditures
and the percent of budget expended, copy the formulas in cells G46...H46
down to the GRAND TOTAL row.
7.
Print the file. Although you may want to save it in its sorted and tabulated
form, it is generally best to save the file before it is sorted. This will enable
you to make corrections or changes in codes later without having to
rearrange the worksheet. It is relatively simple and fast to run the sort and
tabulate macros again.
FILE: MOD7TXT.DOC
69
December 26, 1990
MACROS for MOD7V3.WK1
\R
cde
amt
♦♦♦♦DATA ENTRY MACRO***
{snug}
{GETLABEL "Enter date (DD/MM/YY): ",tmp_date}
{IF tmp_date="”}/WTC{QUIT}
{GETLABEL "Enter description (Up to 25 chars): ",tmpdesc}
{GETLABEL ’’Enter code:”,tmp_code}
{GETNUMBER "Enter income or expense amount: ”,tmp_amt}
{IF @ISERR(tmp_amt)} {errmsg} {BRANCH amt}
/WIR~ /Ctmp_area~ - {CALC}/WTC{BRANCH \R}
snug
{GOTO}trans~ {END} {DOWN 3} {UP @ROWS(database)-3}
/WTH{DOWN @ROWS(database)-3}
errmsg
Invalid entry. Press any key to continue.{GET key}{ESC}
key
tmp_date
tmpdesc tmp_code
tmp_amt
♦** GRAND TOTAL MACRO *♦*
\L
GLOOP1
GLOOP2
GSUM
GSINGLE
GTEST
{GOTO}TRANS~ {right 2}{down 5}
{end} {down} {down 4}GRAND TOTAL: - {right 2}
@sum({Ieft l}{up 4}{end}{up}{right 1}.
{left 1} {end} {down} {down 2})—
{left 2} {up 4} {end} {up}
{let GTEST,@cellpointer("contents")}
{down} {if @cellpointer("type")="b"}
{down} {GSUM} {GOTO}TRANS~ {quit}
{if @exact(GTEST,@cellpointer("contents")) = l} {branch GLOOP2}
/WIR{down 2} ~ {down}Subtotal: - {GSUM}
{down 2} {branch GLOOP1}
{right}{up 3}{if @cellpointer("type")="b"}{branch GSINGLE}
{down 3}{right}@sum({left}{up 2}{end}{up}.{end} {down}{down})~
{left 2} {return}
{down3}{right}@sum({left}{up 2}.{down})-{down 2}
{left 2} {branch GLOOP1}
FR5
♦♦♦PRINT ELITE COMPRESSED MACRO***
\E
{calc} {GOTO}database /PPR{?}~OS{esc}S
\027@\015~
P66-MR240-MB4QAGPOS{esc}\027@~
MB2-QQ
\s
{GOTO}TRANS~{down 5}
/DSRD.{end}{down}{right 3} ~P{right 2} - A-S-A-G
{CALC}{\L}
♦♦♦SORT DATABASE MACRO***
FILE: MOD7TXT.DOC
70
December 26,1990
APPENDIX C 4
COST ANALYSIS PROCEDURES
Data Analysis Templates
Also see Computer File MOD7ANAL.WK1
FILE: MODTXT.DOC
71
December 26,1990
APPENDIX C 4: COST ANALYSIS PROCEDURES
The worksheet (MOD7ANAL.WK1) includes five templates with illustrative tables and
graphs covering the following:
1.
Percentage distribution of income and expenditure lines;
2.
Magnitude differences between lowest and other expenditures;
3.
Differences between budgeted and actual expenditures and income (variance
and percent differences);
4.
Trend analysis of expenditures; and
5.
Break-even analysis of expenditures vs. revenues.
The quickest way to examine these procedures and select the one that are desired is to load
the computer program, experiment with each template by inserting new labels, expense and
budget figures, and follow the directions for displaying the graphs.
Obviously, these templates can be modified. The "Line Item" labels can be changed to fit
the income and expense categories actually used. They can be expanded by inserting
additional rows and columns. They can also be reduced by deleting rows and columns.
The graphs can be changed or modified, as well. New graphs can also be generated. This
template does not include any macros, just formulas.
Table 1 shows the first template and its accompanying graph. Table 2 illustrates how the
template can be expanded to show more detail and compare two subproject sites.
Table 1
COST ANALYSIS PROCEDURES FILE: MOD7ANAL.WK1 26-Jul-90
1. Examine Percentage Distribution of Line Items
Expenses
Percent
Line Item
B
C
A
49.9%
Personnel
$2,345.60
7.3%
$345.00
Travel
9.7%
Equipment
$456.00
$332.00
7.1%
Supplies
$876.00
18.6%
Other Costs
$345.00
7.3%
Indirect Costs
Total
$4,699.60
100.0%
1.1 List line items and total in Column B.
1.2 In column C divide each line item by total.
1.3 To display graph press F10.
Or press /Graph, Name, Use, PERCENT
{PRESS PAGE DOWN FOR MORE)
FILE: MODTXT.DOC
72
December 26, 1990
Figure 1
Distribution of Costs
Personnel (49.9%)
Table 2
1. Examine Percentage Distribution
Chitral
Line Item
Expenses
Percent
B
C
A
Bank Charges
10.00
.2%
445.00
Consultant fees
7.3%
Drugs, medicines 800.00
13.1%
Dues/publicat.
25.00
.4%
Employee Ben.
1,156.89
18.9%
Equipment
1,245.00
20.4%
124.67
Freight, post.
2.0%
35.67
Laundry, clean.
0.6%
Office exp.
175.55
2.9%
355.86
5.8%
Rent
67.12
1.1%
Repairs
266.00
4.3%
Supplies
258.00
Travel/per diem
4.2%
45.67
0.7%
Telephone/fax
56.87
0.9%
Utilities
Wages/salaries 1,050.00
17.2%
Total
FILE: MODTXT.DOC
6,116.30
100.0%
73
of Line Items
Gilgit
Expenses
Percent
D
E
25.00
.3%
367.00
4.6%
657.88
8.3%
0.00
0.0%
2,345.90
29.5%
345.88
4.3%
45.67
0.6%
35.00
0.4%
256.00
3.2%
355.86
4.5%
352.00
4.4%
333.00
4.2%
56.33
0.7%
123.88
1.6%
87.88
1.1%
2,567.00
32.3%
7,954.48
100.0%
December 26, 1990
The remaining figures show the other four templates in the computer file together with
their graphs.
Table 3
2. Analyze Magnitude of Differences in Lines
Line Item
A
Pers
Trav
Eqip
Supp
ODC
I DC
Expenses Difference
B
C
$2,345.60 $2,013.60
$345.00
$13.00
$456.00
$124.00
$0.00
$332.00
$876.00
$544.00
$345.00
$13.00
Total
$4,699.60 $4,367.60
2.1. Select smallest line item amount.
2.2. In Column C subtract that amount from each line.
2.3 For Graph press / Graph, Name, Use, Diff
{PRESS PAGE DOWN FOR MORE}
Figure 2
FILE: MODTXT.DOC
74
December 26, 1990
Figure 3
Table 4
3. Compare Expenses with Budget
Line Item
A
Pers
Trav
Eqip
Supp
ODC
IDC
Expenses
Budget Differencee Percent
B
C
D
E
$2,345.60 $2,200.00
$145.60
106.6%
$345.00
$245.00
$100.00
140.8%
$456.00
$500.00
($44.00)
91.2%
$332.00
$400.00
($68.00)
83.0%
$876.00
$700.00
$176.00
125.1%
$345.00
$350.00
($5.00)
98.6%
Tot
$4,699.60 $4,395.00
$304.60
106.9%
3.1 List budgeted lines in Column C.
3.2 Subtract: C-B in column D.
3.3 Compute percent under (over) budget: C/B.
3.4 Graph for differences betwee B and C: /GNU BUDGET
3.5 Graph Actual Differences (D): /GNU BUD_DIFF
3.6 Graph Percent Differences (E): / G N U PER_DIFF
(PRESS PAGE DOWN FOR MORE)
FILE: MODTXT.DOC
75
December 26, 1990
Figure 4
Figure 5
$
Percent Difference: Budget — Expenses
1.4
1.3
1.2
1.1
1
0.9
0.8
0.7
0.6
0.5
0.4
0.3
0.2
0.1
0
Pers
FILE: MODTXT.DOC
Trov
Eqip
Supp
ODC
76
IDC
Tot
December 26, 1990
_
_____ Table 5__________
_
4. Trend Analysis of Expenditures
Line Item
A
Pers
Trav
Eqip
Supp
ODC
I DC
Year 3
Year 1
Year 2
D
B
C
$2,345.60 $2,500.00 $2 ,657.00
$456.00
$345.00
$367.00
$164.00
$456.00
$289.00
$342.00
$332.00
$567.00
$657.00
$876.00
$700.00
$456.00
$345.00
$350.00
Year 4
E
$3,477.00
$578.00
$223.00
$877.00
$567.00
$554.00
Tot
$4,699.60 $4,773.00 $4 ,732.00
$6,276.00
4.1. Enter expenses for each period (year, month, quarter)
4.2 Plot actual expenses for each line froni period to period.
4.3 Compute percent differences from period to period for each
line, e.
6.6% increase
(2500-2345.60)/2345.60=
Pers (C-B)/B
Pers (D-C)/C
6.3% increase
(2657-2500)/2500 =
30.9% increase
Pers (E-D)/D
(3477-2657)2657=
{PRESS PAGE DOWN TO VIEW PERCENT CHANGES OVER TIME}
Line Item
A
Pers
Trav
Eqip
Supp
ODC
IDC
Year 1
B
Year 2
C
6.6%
6.4%
-36.6%
70.8%
-20.1%
1.4%
Year 3
D
6.3%
24.3%
-43.3%
-39.7%
-6.1%
30.3%
Year 4
E
30.9%
26.8%
36.0%
156.4%
-13.7%
21.5%
1.6%
-0.9%
32.6%
Tot
4.4 Graph of actual differences over time: Pers and Total:
N U TREND1
Graph of Equipment and Supply Expenses: /GNU TREND2
/ G
{PRESS PAGE DOWN FOR MORE}
FILE: MODTXT.DOC
77
December 26, 1990
(Thousands)
Figure 6
Personnel
+
Total
(Thousands)
□
□
FILE: MODTXT.DOC
Supply
+
Equipment
78
December 26,1990
Table 6
5. Break-Even Analysis: Expenses vs. Revenues
Line Item
A
Expenses
Year 3
Year 2
Year 1
D
B
C
,732.00
$4,699 .60 $4,773.00 $4
Year 4
E
$6,276.00
Revenues
$3,789 .00 $4,356.00 $4 ,690.00
$7,653.00
$1,377.00
Difference
Percent Difference
5.1
5.2
5.3
5.4
($910 .60)
($417.00)
($42.00)
-24.0%
-9.6%
-0.9%
18.0%
List total expenses and revenues by period (year, quarter, etc.)
Subtract expenses from revenues.
Compute % difference: Difference/Revenue (e.g.,-910.60/3789 = -24%)
Graph break-even chart (revenues vs. expenses): /GNU BRAKEVEN
{END.
PRESS HOME TO RETURN TO TOP OF WORKSHEET}
Figure 8
□
FILE: MOD7TXT.DOC
Expenses
79
+
Revenues
December 26, 1990
APPENDIX D
REFERENCES AND BIBLIOGRAPHY
FILE: MOD7TXT.DOC
80
December 26,1990
APPENDIX D: REFERENCES AND BIBLIOGRAPHY
Berman, Peter A, "Cost Analysis as a Management Tool for Improving the Efficiency of
Primary Care: Some Examples from Java”. International Journal of Health Planning and
Management. 1986.
Creese, Andrew and David Parker (eds.), Cost Analysis in Primary Health Care: A Training
Manual for Programme Managers. WHO/SHS/NHP/90. World Health Organization:
Geneva. 1990.
Levin, Henry M., Cost-Effectiveness: A Primer. Beverly Hills: Sage Publications, 1983.
Nelson, Stephen L., "Keeping the Books: An Income and Expense Recorder". Lotus. May,
1990, p. 52-55.
Reynolds, Jack and KLCeleste Gaspari, Cost-Effectiveness Analysis. PRICOR Monograph
Series: Methods Paper 2. Bethesda: Center for Human Services, 1985.
Shepard, Donald S. and Mark S. Thompson, "First Principles of Cost-Effectiveness Analysis
in Health", Public Health Reports, November-December, 1979.
Thompson, Mark S. and Eric E. Fortess, "Cost-Effectiveness Analysis in Program
Evaluation". Evolution Review, Vol. 4, No. 4, August, 1980.
Warner, Kenneth E. and Bryan R. Luce, Cost-Benefit and Cost-Effectiveness Analysis in
Health Care, Ann Arbor, MI: Health Administration Press, 1982.
WHO, Estimating Costs for Cost-Effectiveness Analysis: Guidelines for Managers of
Diarrhoeal Diseases Control Programmes. CDD/SER/88.3 World Health Organization:
Geneva. 1988.
FILE: MOD7TXT.DOC
81
December 26,1990
APPENDIX E: GLOSSARY
FILE: MOD7TXT.DOC
82
December 26, 1990
APPENDIX E: GLOSSARY
COST(S): The value of a good or service, which is conceptually defined as the value that
could be gained by using the resources in a different way. For example, the cost of drugs
could be seen as the value of using the resources to purchase some other commodity or
service.
AVERAGE COST(S): The mean cost per unit of outcome, computed by dividing
the total cost by the number of units of outcome, also called unit cost.
CAPITAL COST(S): Costs of items which have a life expectancy of 1 year or more,
usually land, buildings, vehicles, and equipment.
DIRECT COST(S): Costs that are directly attributable to a program, project,
product or activity, such as the cost of gasoline used by project vehicles for project
work.
ECONOMIC COST(S): The "true" costs of a product or service, which is the value
of an alternative endeavor that might have been undertaken with the same
resources.
ECONOMIES OF SCALE: When marginal costs are less than average costs. Often
occurs as a program expands and no additional fixed costs are incurred, thus making
the cost of serving one more person or produlcing one more unit less than the
average cost.
FINANCIAL COST(S): See Monetary Costs.
FIXED COST(S): Costs that do not vary with program size, such as those of a
building, permanent staff, medical equipment.
INDIRECT COST(S): Costs that are not directly attributable to a program, project,
product or activity, but which are incurred in support of those direct activities.
Overhead, fringe benefits, general and administrative expenses are typical indirect
cost categories.
MARGINAL COST(S): The increased (or decreased) cost of providing one
additional (or one less) unit of outcome.
MONETARY COST(S): Financial expenditures incurred in purchasing a product or
service.
RECURRENT COST(S): Costs of items that are purchased and used (or replaced)
within a period of 1 year or less, such as personnel salaries, medicine and supplies,
gasoline, and utilities.
VARIABLE COST(S): Costs that vary with program size, such as drugs, gasoline,
and vehicle maintenance.
COST ANALYSIS: The examination of expenditures to determine how resources have
been spent.
FILE:MOD7TXT.DOC
83
December 26,1990
COST-BENEFIT ANALYSIS: A technique for comparing the monetary costs and
monetary outcomes of alternatives. In CBA, the effect (the denominator) is expressed in
monetary terms; in CEA it is not
COST-EFFECTTVENESS ANALYSIS: A technique for comparing the costs and the
effectiveness of alternative ways of achieving the same objective.
COST-EFFECTIVENESS RATIO:
effectiveness.
The ratio that is derived by dividing costs by
COST-UTILITY ANALYSIS: A procedure that compares the costs of alternatives with
the subjectively derived ratings of those alternatives; used when effectiveness cannot be
measured.
EFFECTIVENESS: The degree to which program or system objectives are achieved.
Usually, outcomes are compared to some standard, such as the objectives that were set
originally. For example, the program reached 90 percent of its target
EFFICIENCY: The achievement of objectives without wasting resources; the relationship
of output to input. For example, in two programs that use the same amount of resources,
program A, which screens 10 mothers/day, is more efficient than program B, which screens
5 mothers/day.
EXPENDITURES: The amount of money, time, or effort spent
INCOME: Funds received from contributions, donations, allotments and/or sales of
products and services.
INFLATION: An increase in the price of goods and services.
INPUT: The types or quantities of resources (labor, money, material, etc.) used in a
program, project, or activity; sometimes called "effort.”
PRESENT VALUE: The current value of goods or services, usually applied to costs or
outcomes expected in the future. The future value is discounted at a given rate to
determine the present value.
PRICE: The amount for which a product or service is sold.
REPLICATE (REPLICABILITY): The feasibility of expanding or duplicating an activity
in other areas.
REVENUE: Money received. See Income.
SHADOW PRICING: Estimates of the true costs of goods and services that are applied,
for example, to subsidized services, donated time and equipment, and other goods and
services whose true value is not the same as the listed value.
SUSTAIN (SUSTAINABILITY): The ability of a project/program to continue, implying
that this will be done largely without external donor assistance.
FILE:MOD7TXT.DOC
84
December 26, 1990
- Media
4617.pdf
Position: 6150 (1 views)