Worksheet for costing interventions: The Excel worksheet ...



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 leader. [2]

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.

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.

2. Collection of recurrent costs: Information about this set of cost data could be provided 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).

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.

-----------------------

[1] All five workbooks are available to view or download on

[2]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download