# Worksheet for costing interventions: The Excel worksheet is the by uVLerY

VIEWS: 28 PAGES: 4

• pg 1
```									Economic Cost of Malaria: Data Collection Tools

Donald S. Shepard*
Sareh Khoshi
Alec Mkwamba
Tapiwa Muperiki
Chidude Osakwe

Schneider Institutes for Health Policy, Heller School
Brandeis University, Waltham, MA 02454-9110 USA

Email: Shepard@brandeis.edu; Tel: +1-781-736-3975

June 30, 2011
Overview: Analyzing malaria cost requires capturing cost components on for both prevention
as well as illness. In order to do so, our team has created one set of Word questionnaires on
service use for illness services, and five Excel workbooks--two of which fall under prevention.
The prevention workbooks include costs of indoor residual spraying and insecticide treated wall
lining. The other three workbooks are to capture unit costs for illness management. These relate
to malaria consultations, malaria laboratory tests and specialized malaria medications.1
The Excel worksheets are used as a helpful tool for the purpose of collection and analysis of cost
data. Each sheet consists of two types of cells. First, there are blank cells in which data need to
be entered by the user. The second group is the cells which have zeros in them. This group of
cells will be computed by the Excel sheet based on the formulas that they contain.
If any of the data entry cells does not apply to a specific application, the user should enter a zero
in that cell. This process reduces the chance of inadvertent errors and confirms that the cell was
not inadvertently overlooked. After entering the zero, the user should enter an explanation in the
comment field of the related row.

Costing tools for preventive activities: As mentioned, there are two Excel workbooks for
capturing preventive activities cost for two given technologies of indoor residual spraying (IRS)
and insecticide treated wall lining (ITWL) or durable lining (DL). After entering all the required
data for each workbook, both economic and financial costs of each will be calculated. Due to
close nature of IRS and ITWL activities, the workbooks are almost identical with only minor
differences in some subcategories.
Output of these two workbooks is cost per person protected under each technology. This cost
information and data on their effectiveness (being gathered separately) are critical pieces of
information for policy makers.

Costing tools for treatment services: The Excel workbooks on this category are as
follows: Excel workbook for costing a malaria consultation, Excel workbook for costing a
malaria laboratory test, and Excel workbook for costing a specialized malaria medication.
Entering data in related cells for each sheet would result in finding economic and operating cost
for malaria consultation, laboratory test and specialized malaria medication.
With these worksheets and the Word questionnaires, the user will be able to answer to the
questions related to cost and offsets of each illness intervention. These options are categorized
under ambulatory care. The outputs of treatment services would be unit cost for consultation,
laboratory test and specialized malaria medication. In the case of hospitalized patients, the cost
could be captured by referring to the hospital cost manual published previously by the team
In comparison to worksheets on preventive activities, treatment services do not have capital
purchases worksheet since we are assuming that the health facilities and equipment already exist.
The data should be collected from one or more representative facilities in each facility in each
study area.

1
2
http://people.brandeis.edu/~shepard/w-manual.PDF

2
Collection and analysis of cost data: The collection of cost data entails four cost
categories: personnel cost, recurrent cost, capital purchases, and capital cost (as mentioned,
capital purchases are only for preventive intervention categories).

1. Collection of personnel data: In the case of preventive interventions, this set of data
should be collected from the administrative officers who are responsible of overseeing local
and international staff for the program. For treatment services, personnel information could
be collected from a clinic manager or front desk personnel.
One can categorize the personnel into local and international labor, and each group would
have its own specific monthly gross salary. The next column is the number of months that the
labor is paid (or expected to be paid), including paid vacation. In this column numbers from
1-12 could be entered. Also, if a person is paid for only 15 days the user can enter 0.5 which
is indicating half a month. The next column, allocated annual salary, will be automatically
calculated when the two previous columns are completed. Fringe benefits are calculated as
percentage of salary; therefore, the user should enter the rate which will be shown as
percentage. Then, any additional compensation should be entered by the user. As a result,
total annual payroll will be calculated by the computer which is the multiplication of
allocated annual salary by fringe benefits plus annual salary plus additional compensation.
Next, average share of time, for instance, ITWL should be entered by the user. Finally,
allocated ITWL cost will be calculated that is the result of multiplying the allocated ITWL
cost by total annual payroll. The result of total allocated ITWL cost will be automatically
shown on the summary sheet on the first page.

by the person in the administrative office who is responsible for preventive project
operations. And the same data could be collected from an administrative office in a clinic for
treatment services cost.
Consumable or recurrent cost consists of the items which would last for a year or less.
Basically, the quantity and the unit cost should be entered by the user. As a result, total cost
which is the multiplication of quantity and the unit cost will be shown on the next column.
For example, the user should enter the share for IRS as percentage. At the end IRS cost will
be calculated by the Excel sheet. The IRS cost is the result of multiplication of total cost and
share for IRS.

3. Collection of capital costs: Capital costs are items with a useful life greater than one
year. There are two alternative approaches to treating such costs, depending on the purpose.
If the purpose is a financial analysis to show the expenses of the initial year of a project, then
the capital purchase approach is relevant. It measures the expenses on capital purchases
during the study year. If the purpose is an economic analysis, then all known use of capital
items should be considered, and the capital use costs are relevant.
a. Collection of capital purchase items: This set of information can also be provided by
finance officers in the administrative office (this section is only for preventive interventions).

3
Completing capital cost is very similar to recurrent cost which is entering the quantity and
the unit cost for each item by the user. The total outlay will be calculated automatically
which is the multiplication of quantity and unit cost. Next the user should enter the share for
each intervention or treatment service as percentage. Finally, the outlay will be calculated by
the computer which is the result of multiplying of total outlay by share for each intervention.
In this category we have two categories for building which are newly opened and major
renovations completed since the financial value of these types of buildings would differ.
b. Collection of capital use costs: Again for this set of data one can go to the finance
officer or the assistant to obtain this information.
The items on capital costs have some similarities to the capital purchases but the calculation
is a bit different in the sense that we want to find out about the economic cost of the capital
items, so we need to calculate amortization. Also, the capital costs include previously
purchased capital items.
On this page, the user should enter the data for quantity and for unit replacement cost. As a
result, the total cost will be calculated by the Excel sheet by multiplying quantity by the unit
replacement. Next, share for each preventive intervention or treatment service should be
entered by the user. Later, the cost associated with each item will be found by the computer.
Next step is to enter the useful life for each capital item when they are new. There are some
hypothetical numbers already entered to prevent the error for the coming formulas. One can
change this numbers based on the professional opinions. Finally, amortization will be
calculated by the Excel sheet.
In contrast to capital purchases, in capital input category we only have one type of building
since we are going to enter the number of useful life when the building is considered to be
new. This step is required to reach IRS amortization, for instance.

Collection of output data: This set of data could be provided by administrative staff such as
finance assistant and communication officer by the end of project year.
The user should add the information on unit and quantity performed during year. This part is not
included in the overall cost of the project, but this data is crucial to be gathered by the end of the
program year for sustainability reasons.

4

```
To top