The electronic database allows routine programme monitoring data to be compiled and viewed by distribution cycle either for the programme as a whole or for individual distribution sites or months. The following description of the database refers to the database template found here. Parts of the database are locked. The password is be careful.  

Using the CMAM Monitoring Databases

The sample databases are in the format that is recommended for reporting in CMAM programmes. It is simple to:

• Add distribution site names in the second column for distribution cycle 1 (for subsequent distribution cycles, names will appear automatically).
• Enter data from weekly compilation sheets into appropriate fields on the input sheet.
• Refresh the pivot table on the report sheet (right click on it and select ‘refresh’).

Data in the pivot table report can be viewed by distribution site or by month. This is done by clicking on the tabs at the top of the sheet. To view graphs, first ‘hide’ columns for future distribution cycles. To view graphs for specific distribution sites, select a distribution site on the pivot table report, then click on the graphs sheet to view graph.  

Constructing a Monitoring Database in Microsoft Excel

The following instructions can be used to construct a database for the OTP or SC together. Similar stages can be followed to set up the other databases. In each case, weekly compilation sheets and the categories they contain should be used as the basis for the database categories.  

Creating the Data Input Sheet

Figure 1: An input sheet

Create Columns

• Create a spreadsheet in Excel with three columns titled: ‘Distribution Week’, ‘Distribution Site’ and ‘Month’.
• Create columns corresponding to the admission and exit criteria on your weekly compilation sheet.
• Ensure each column title is written in its own cell.
• Create additional columns titled: 'Total end last distribution Total Admissions', 'Total Exits' and 'Total in OTP'
• Add columns for additional information and gender distribution according to the weekly compilation sheet.

Create Rows

• Write site names in rows moving down the spreadsheet for cycle 1. (Leave some space to add extra sites as the programme progresses).
• Write 1 in each cycle number box.
• For the first row corresponding to cycle 2, write in a formula of the first box +1.
• Do not write site names for cycle 2. Write a formula of the appropriate cell in cycle 1.
• Make a bold line under the first cycle

Enter Formulas

Add in formulas for:

• Total end of last distribution. (For distribution cycle 1, there is no formula. Put 0 in the cells. For subsequent cycles, use total in OTP from the previous cycle;
• Total Admissions (this is New Admissions, not including movements) = Oedema + MUAC
• Total Exits (this does not include movements) = Discharged/Cured + Deaths + Defaulters + Non Cured; and
• Total in OTP = Total End Last Distribution + (Total Admissions + Moved In) – (Total Exits + Moved Out).

For all formulas, click and copy formulas down the sheet to fill as many distribution cycles as required. (We recommend no more than one year’ s data in one database). Data can now be entered by distribution cycle from the weekly compilation sheets. Input to check formulas.  

Creating the Data Report

• From the input sheet under ‘Data’ on the top menu, select ‘Pivot Table Report’.
• Click ‘Microsoft Excel List or database’.
• Highlight all of the spreadsheet but exclude top line with Admissions, Exits, Other in, Other Out, Totals and Additional Information.
• Click ‘Next’ to get to the Pivot Table construction.
• Into ‘Page’, drag Site and Month.
• Into ‘Column’, drag Distribution Week.
• Into ‘Data’, drag the following variables in the following order: Oedema, < MUAC 110mm, <70%, Other, Total Admissions, Discharged Cured, Deaths, Defaulters, Non Cured, Total Exits, from SC, Returned defaulters, From other site, To SC, To other site, Total in OTP, Relapse, Male, Female.
• Double click on all variables in data sheet in the Pivot Table construction. Perform the following on each one:

1. Change title by removing Count of. Do not shift the title too much to the left or Pivot Table alarm will be set off;
2. Change from Count to Sum;
3. Click Number – click Number again – tick Use 1000 separator. Change decimal place to 0; and
4. Click Next – click new work sheet – rename it ‘Report’.

• Blank out number in grand total column for ‘Total in OTP’ (the number is meaningless for CTC data).
• Once data is added to the input sheet, the report can be updated. Place the cursor inside the report, right click and select ‘refresh data’.
• Data can be viewed by distribution cycle for the whole programme or, if required, for individual sites or months.

For additional guidance on the construction and use of pivot tables see: http://office.microsoft.com/en-us/assistance/HA010346321033.aspx  

Creating the Graphs

Create Chart of Admission and Exit Trends

• To make the bar chart, first click on the chart icon.
• In Custom Type, select ‘Line – Column’.
• In Series, click ‘Add’.
• Click ‘Name’ bar and write in ‘Total Admissions’.
• Click ‘Values’ and highlight ‘Total Admissions’ row in report (leave out grand total).
• Click ‘category x labels’ and highlight distribution cycles row.
• Click ‘Add’ again.
• Click ‘Name’ bar and write in ‘Total Exits’.
• Click ‘Values’ and highlight ‘Total Exits’ row in report (leave out grand total).
• Click ‘Add’ again.
• Click ‘Name’ bar and highlight ‘Total OTP’ row in report.
• Click ‘Next’, click ‘Chart Title’ and add in overall title and titles for axes.
• Put in new sheet – rename ‘Graphs’.

Create Pie Chart Showing Breakdown of Exit Categories

• Click on the chart icon.
• Click on pie chart.
• In ‘Series’, click ‘Add’.
• Click ‘Values’ and highlight the data in ‘Grand Total’ for exits variables (i.e. discharged cured, default, death, non-cured). Do not include movements or data for Total Exits.
• Click ‘Category labels’ and highlight the exit titles (i.e. discharged cured, default, death, non-cured). Exclude titles for movements or for Total Exits.
• Click ‘Next’, click ‘Chart Title’ and write in title.
• Click ‘Data’ label and tick percent.

Note: When you start to input data into the database, columns corresponding to cycles where no data has yet been entered can be hidden. This makes it easier to view the report and graphs. This tutorial was taken from Valid International, 2006. Community-based Therapeutic Care (CTC): A Field Manual, Oxford: Valid International pages 207-217.