Cost measurement for programmes and activities

This article includes some sample Form templates which can be used to help you track the costs of your programmes and activities at high-level based on the expenditures made by each field office.

You can copy the sample Forms, use the Form Schemas at the end of the article or get some inspiration for your own Forms. You can also design similar Forms to collect more detailed data about the expenditures made.

In this example, we use two Reference Forms to create two lists. One list is for the country offices and the other list is for the Programmes and Activities which you want to monitor. Then, we add a Form with a Subform. 

The Parent Form links to the two Reference Forms so that users can select their country office and the Programme and Activity for which they are reporting. It also uses Calculated fields to sum up the amounts reported in the Subform. The Subform is used to collect monthly data (e.g. monthly expenditures).

You can customize these Forms to your needs. For example you can narrow down each category of expenditure and collect the amount spent for subcategories under each category.  You can also combine this information with other Forms collecting information about the objectives of the activities (e.g. number of beneficiaries or people reached) to see the impact of your activities and the cost effectiveness of your programmes.

The example uses dummy data.

Add a Reference Form for the field offices

If you are working with many country offices, you can create a list of these offices and link each office to the built-in Geodatabase of ActivityInfo to connect them to a specific country.

  • In the Database Design section, click on 'Add form' to add a Form.
  • Give a name to your Form and add a Reference field to reference the Geodatabase of ActivityInfo. Select 'Global' to add a list of all the available countries. Make the field a Key field.
  • Add Text field for the name of the field office and make it a Key field.
  • Save the Form.
  • In the Table View page, click on 'Add record' and add the first country office. Click on 'Save record' to save it.
  • Repeat the same process to add all the country offices. If you have a list ready you can import them as Records to this Form.
  • On the URL of the Table View page you can view the Form Id of this Form. If you want to import a fields list instead of designing a Form using the Form Schemas at the end of the article, you will need to use this Id.

You can change the visibility settings of the Form to Reference data to make sure users doing data entry don't get confused with it.

Add a Reference Form for your Programmes

Next, you can add another Reference Form to list your Programmes and all their Activities. You can also add the budget available for each Activity as well as their Starting Date and Ending Date. 

  • As before, in the Database Design section, click on 'Add form' to add a Form and give a name to your Form.
  • Add two Text fields for the Programme name and the Activity name which falls under the Programme. Make the Text fields Key fields.
  • You can add a Quantity field for the Budget available and two Date fields to capture the Start Date and End Date.
  • Save the Form.
  • In the Table View page, click on 'Add record' and add as a Record the first Programme and Activity. Click on 'Save record' to save it.
  • Repeat the same process to add all the Activities and Programmes. If you have a list ready you can import them as Records to this Form.
  • On the URL of the Table View page you can view the Form Id of this Form. If you want to import a fields list instead of designing a Form using the Form Schemas at the end of the article, you will need to use this Id.

You can change the visibility settings of the Form to Reference data to make sure users doing data entry don't get confused with it.

Add a Form

The Parent Form will link to the list of field offices and to the list of Programmes and Activities using Reference fields. Users will be able to select the country office for which they are reporting from the list as well as the Programme and Activities for which they are reporting. We will also add a Subform to the Parent Form to collect the monthly expenditures.

  • In the Database Design section, click on 'Add form' to add a Form and give a name to it.
  • Add a Reference field and link to the first Reference Form you created. This will bring in the list of field offices and their country.
  • Add another Reference field and link to the second Reference Form you created. This will bring in the Programmes and their Activities.
  • You can also add a User field so that users select their name from a list and so that you know who reported the data.
  • Add a Subform field and give it a descriptive name.
Add a Subform

 The Subform will be used to collect the monthly expenditures.

  • Click on the Subform to open it and add a Month field and make it a Key field to prevent duplicates and to be able to lock reporting for this Subform if needed.
  • Add a Multiple Selection field and add all the cost categories for your Programmes. Users will be able to select the categories for which they will be reporting expenditures for every month.
  • You can also add a User field so that users select their name from a list and so that you know who reported the data.
  • Then add Quantity fields with Codes for each category. We will use the Codes in Formulas to automatically calculate spending in the Parent Form.

You can add Relevance Rules to the Quantity fields to make them appear to users only when they select the respective category in the Multiple Selection field.

  • In the end, you can add a Calculated field and in the Formula add up all the Quantity fields using their Codes. This will give you the total amount spent by month.
  • Save the Subform to go back to the Parent Form.
Add Calculated fields to the Parent Form

In the Parent Form, we can make some calculations based on the numbers added in the Subform. We can add Calculated fields to automatically sum up the monthly expenditures reported based on each different category. We can use the 'sum()' Formula, along with the Codes we gave to each Quantity field in the Subform.

Finally, we can add up these sums to see the total expenditure of the specific field office for the specific Activity so far.

Analyze the data further with a Pivot Table

Finally, after some data has been collected, if you want to see more information about the data, add a Pivot Table. 

  • Click on "Reports" and click on "Add pivot table".
  • Use all the Forms you created as Resources. 
  • Now you can use Measures and Dimensions to design the Pivot Table in a way that will show the information you need.
  • For example, you can use as Measures the field showing the total expenditures per activity per field office and the field of the second Reference Form showing the budget available for each activity.
  • Then you can add more Dimensions to the Pivot Table to show more details. You can show for example the budget available per Activity and the amount spent per field office and see where you Programmes stand.
  • Click on "Save report" to save the Pivot Table.
Form Schemas

Instead of manually designing the Reference Forms, the Form and the Subform, you can directly import the fields list to your Reference Forms and Form and Subform. The following files contain the fields lists for the two Reference Forms, the Form and the Subform. As these are a sample Forms, you might need different fields so can add new fields, edit fields or delete the fields that you don't need to customize the Forms. 

The Parent Form links to the two Reference Forms using Reference fields. So after adding the two Reference Forms you will need to find their Ids and add them to the Parent Form template below.

Also, as the Form includes Calculated fields based on fields of the Subform, you will need to import the fields in three steps.

  • To import the first fields list to the first Reference Form, add a new Form and give it a name.
  • Download and open the file below which includes the fields for the first Reference Form.
  • Copy the cells along with their Headers.
  • In the Form Design page, open the fields palette of the Form and click on "Paste field list from spreadsheet". 
  • Paste the cells and click on "Add fields" to add them.
  •  In the Table view of this Form, you can find the Form Id in the URL of the Table View page of the Reference Form. You will need in the following steps.
  • To create the second Reference Form, add a new Form and give it a name.
  • Download and open the file below which includes the fields for the second Reference Form.
  • Copy the cells along with their Headers.
  • In the Form Design page, open the fields palette of the Form and click on "Paste field list from spreadsheet". 
  • Paste the cells and click on "Add fields" to add them.
  •  In the Table view of this Form, you can find the Form Id in the URL of the Table View page of the Reference Form. You will need in the following steps.

Now we will add the first part of the Parent Form.

  • Add a new Form and give it a name.
  • Download and open the file below which includes the first part of the Parent Form. You need to find the Reference Form Ids of the Reference Forms above as mentioned, and add each one in the cell in the Rows of 'reference', under the Column 'Referenced Form'.
  • Copy the cells along with their Headers.
  • In the Form Design page, open the fields palette of the Form and click on "Paste field list from spreadsheet". Paste the cells and click on "Add fields" to add them.
  • Continue with the Subform. To import the fields list to the Subform, click on the Subform to open it.
  • Download the file below and copy the cells along with their Headers.
  • As before, open the fields palette of the Subform and click on "Paste field list from spreadsheet".
  • Paste the cells and click on "Add fields" to add them.
  • Save the Subform to go back to the Parent Form.
  • Finally, in the Parent Form, we will add the Calculated fields.
  • Download the final file below which contains the second part of the Parent Form with the Calculated fields, open it and copy the cells along with their Headers.
  • As before, open the fields palette of the Parent Form and click on "Paste field list from spreadsheet".
  • Paste the cells and click on "Add fields" to add them.
  • Save the Form.