Training: Facilitator Guide: PFM Reporting Framework v2

Quick-navigation:
1. Overview of Workbook Setup
2. Setting up the Workbook for Distribution to Auditors
3. Consolidating the Data from Multiple Institutions after Data Entry is Complete
4. Copying and Pasting Dashboard Charts and Tables

1. Overview of Workbook Setup


Protected Worksheets

All of the worksheets (tabs) are protected and only cells in which data needs to be entered are unlocked. The data entry worksheets and dashboards are setup so that they do not need to be unprotected for data to be entered or analyzed. The only reason that any of the worksheets would need to be unprotected is if changes need to be made to Key Questions, formulas, etc. Therefore, the password should not be given to the auditors.

> Password: PFM123

Users can select (and copy) cells in the Instructions, Definitions, Framework, Dashboard, Data, and Ref-Mapping tabs. Data can also be entered in the unlocked cells in the Instructions tab. The protection for these tabs is configured as follows:

In the institutions tabs – MoF, RA, Par, MDA-1, MDA-2, MDA-3, MDA-4, MDA-5, MDA-6, MDA-7, users can also do the following:

  • Edit cells: Users can enter text into columns G, H, I, J, L, and R, and select from the drop-downs in columns K, N, and Q.
  • Hide/adjust width of columns: Users can hide columns if needed during data entry, and they can adjust the width of the columns.
  • Auto-fit row height: The row height will auto-fit the text that is entered into the cells in rows G, H, I, J, L, and R.
  • Insert hyperlinks: Users can enter hyperlinks, but these will not be copied when the data is consolidated (further explanation provided in section 3).

The protection for these tabs is configured as follows:

Macros

There are two macros that are used in the workbook.

Macro one: Clearing value in column N if value changes in column K: The purpose of this macro is to ensure that the Root Cause of Underperformance (column N) is cleared if the Performance Grade (column M) changes.

How it works: The macro listens for values in column M to change. If the value in column K is updated to a new value that changes the Performance Grade in column M, the contents of N are cleared.

Finding the macro: This macro is applied to all of the institution tabs (MoF, RA, Par, MDA-1, MDA-2, MDA-3, MDA-4, MDA-5, MDA-6, MDA-7). In the worksheet, click on the developer tab and then click “View Code”. If you want to make changes to this macro, the changes will need to be made to the code in all of the institution tabs.

Code:

Macro two: Auto-consolidation of data entry worksheets using “Import Data” button: The purpose of this macro is to enable the facilitator to easily import into one workbook the data entered into the institution tabs by the auditors (in multiple separate workbooks).

How it works: The macro reads the names of all the tabs and their respective file names in the “File Names” tab in columns A and B, respectively, to know which files to import into the master workbook. The columns from each tab that will be copied are listed in the array colArr and are (G, H, I, J, K, L, Q, R). The code is written to copy these columns specifically and this array should be edited with caution.

With the tabs, file names, and columns information stored, the macro begins to iterate through all the file names and checks to see if they exist at the same directory level as the master workbook that is doing the importing. For example, we want to import C:\ProjectFiles\MoF.xlsm into C:\ProjectFiles\Kenya_PFM Reporting Framework.xlsm. You will note that both files exist within the same folder and share the same directory level. The macro does not search outside of a directory level, and files not located within the same folder/directory cannot be found and will not be imported.

After the file to import is found, the macro opens the file, navigates to the correct tab, and unlocks it. After finding the correct tab, the macro then copies specific columns as designated in the colArr, copying rows 7 through 60 as these are the rows containing data and not column headers, etc. When all of the columns and rows have been copied, the worksheet is locked and the file is closed. This process is repeated for all the files listed. After all documents have been imported, a notification is given showing which documents were found and imported.

Care should be taken to ensure that the “File Names” tab in the master workbook is populated correctly before running the import. If a tab is recorded, a corresponding file name should always be recorded.

Finding the macro: The macro is applied to the “Import Data” button in the File Names tab. In the File Names tab, right click on the button and select “Assign Macro.” Select “Button1_Click” from the list and then select Edit.

Code:

Worksheet/Tabs Setup

Instructions, Definitions, Framework Tabs

The first three tabs of the workbook include key instructions, definitions, and the conceptual framework.

Institution Tabs

There is one data collection/entry tab for each institution. This is where the auditors should enter data. Guidance on data entry can be found in the Data Entry Guide. Some key details on the setup of the sheets are below.

  • Color coding: Each institution is assigned a color, and this is shown on the tab label, in the heading of the worksheet, and in the dashboards.
  • MDA tabs: As explained in more detail in section 2 of this guide, the names of the MDAs that will be audited should be entered by the facilitator before the workbooks are distributed for data entry. The names need to be entered both in the tab label at the bottom of the worksheet and in row 2 of the worksheet.
  • Explanation: How was the assessment done (column G), Sources Used (column H), Findings (column I), Analysis (5 Why model) (column J), If Not applicable selected, provide explanation (column L), Conclusion for PFM Process: Other Observations (column R): Text can be entered in the cells in these columns.
  • Performance Assessment (column K): This was configured using Data Validation. The answers in the drop-down for each question can be found in the Data-Performance Grading tab. An example of the configuration for one cell (K7 in the MoF tab) is shown below. The formula is slightly different in each cell in column K.

=’Data-Performance Grading’!$E$2:$E$7

  • Performance Grade (column M): The grade populates using VLOOKUP. The referenced data can be found in the Data-Performance Grading tab. An example of the formula can be found below (M7 in the MoF tab). The formula is slightly different in each cell in column M.

=VLOOKUP(K7,’Data-Performance Grading’!E2:F7,2,FALSE)

  • Root Cause of Underperformance (column M): This was configured using Data Validation. The options for the Root Cause of Underperformance can be found in Data-Root Causes. It is configured so that if the Performance Grade is less than or equal to 3, then the root causes will appear as options in the drop-down. If the Performance Grade is greater than 3 (i.e. 4) or is “No grade,” then the drop-down populates with the option of “N/A.” The formula, which is the same in all cells in column N, is found below.

=IF(CELL(“contents”,INDIRECT(“M”&ROW()))<=3,’Data-Root Causes’!$A$1:$A$6,’Data-Root Causes’!$A$8:$A$8)

  • Grade for PFM Output (column O): This is calculated by averaging the Performance Grades in column M for all questions under this output. In addition to calculating the average, the formula also tells the cell to populate with “No grade” if there is an error. The example below is for PFM Output 1.1 in the MoF tab.

=IFERROR(AVERAGEIF(M7:M14,”<5″),”No grade”)

  • Grade for PFM Process (column P): This is calculated by averaging the Performance Grades in column M for all questions under this process. In addition to calculating the average, the formula also tells the cell to populate with “No grade” if there is an error. The example below is for PFM Process 1 in the MoF tab.

=IFERROR(AVERAGEIF(M7:M18,”<5″),”No grade”)

  • Conclusion for PFM Process: Dominant Root Cause (column Q): This was configured using Data Validation. The options for the Root Cause of Underperformance can be found in Data-Root Causes. It is configured so that if the Grade for the PFM Process is less than or equal to 3, then the root causes will appear as options in the drop-down. If the Grade for the PFM Process is greater than 3 (i.e. 4) or is “No grade,” then the drop-down populates with the option of “N/A.” The formula, which is the same in all cells in column M, is found below. The formula is the same for all processes as the options are the same for each process.

=IF(CELL(“contents”,INDIRECT(“P”&ROW()))<=3,’Data-Root Causes’!$A$1:$A$6,’Data-Root Causes’!$A$8:$A$8)

Dashboard Tabs

These tabs are setup to automatically analyze the data entered in the institution tabs. All the dashboards are setup to enable easy printing or converting to PDFs. Individual charts can also be copied and pasted as images into Powerpoint, Word Docs, etc., even when the sheet is protected. Details on the setup of each Dashboard are below.

Dashboard-Overall

  • Government Performance by PFM Process: Graph references data in Data-PerfResults1.
  • Dominant Root Causes of Underperformance by Institution: The Dominant Root Causes of Underperformance will populate for the institution that is selected. The institution box is configured using data validation and references data in the Data-RCResults1 tab:

=’Data-RCResults1′!$B$1:$K$1

The Root Causes of Underperformance boxes are configured using VLOOKUP and reference data in the Data-RCResults2 tab:

=VLOOKUP(O8,’Data-RCResults2′!B2:C11,2,FALSE)

Conditional formatting is also used to make the text grey/italics if the cell contains “N/A” or “No Root Cause of Underperformance was Assigned.”

  • Performance by Institution: Graph references data in Data-PerfResults1.
  • Performance of Institutions in Integrating SDGs into PFM Processes: Graph references data in Data-SDGs tab.
  • Key Overall Risk Areas: Table references data in Data-PerfResults1 tab. Conditional formatting is used to highlight the cells in red with a pattern if the value is less than 2 and to clear any formatting if the cell is empty. Conditional formatting is also used for the institution headings to clear them of any formatting if the cell is empty (in case not all the MDA tabs are used, for example).

Dashboard-Processes

  • Contribution of Outputs to Performance of PFM Processes: Conditional formatting is used to highlight the cells based on the grade that was calculated for each output, sub-process, and process.The formulas in the conditional formatting reference the Data-PerfResults2 tab.

Dashboard-Institution

  • Performance of Institutions in PFM Processes
    • Graphs reference data in Data-PerfResults1.
    • Strongest Institution/Weakest Institution boxes reference data in Data-PerfResults3 and use VLOOKUP to determine which institution in the strongest/weakest. Examples of the formulas for Process 1 are provided below.

Strongest Institution:

=VLOOKUP(MAXIFS(‘Data-PerfResults3′!A2:A11,’Data-PerfResults3′!A2:A11,”<5″),’Data-PerfResults3’!A2:B11,2,FALSE)

Weakest Institution:

=VLOOKUP(MINIFS(‘Data-PerfResults3′!A2:A11,’Data-PerfResults3′!A2:A11,”<5″),’Data-PerfResults3’!A2:B11,2,FALSE)

  • Core PFM Institutions vs MDAs data is pulled from Data-PerfResults1, and conditional formatting is used to highlight the cells based on the values in the cells.

Dashboard-SDGs

  • Overall Performance: Data pulled from Data-SDGs
  • Performance by Process: Graph references data inData-SDGs.
  • Performance by Institution: Graph references data inData-SDGs.
  • Performance on Implementing Key SDG Activities: Conditional formatting is used to highlight the cells based on the grade that was calculated for each activity, institution, and process. The formulas in the conditional formatting reference the Data-SDGs tab.

Dashboard-Disaster

  • Overall Performance: Data pulled from Data-Disaster.
  • Performance by Process: Graph references data inData-Disaster.
  • Performance by Institution: Graph references data inData-Disaster.
  • Performance on Implementing Key SDG Activities: Conditional formatting is used to highlight the cells based on the grade that was calculated for each activity, institution, and process. The formulas in the conditional formatting reference the Data-Disaster tab.

Dashboard-Process1/Process2/Process3/Process4/Process5

  • Overall Process Performance: Data pulled from PerfResults1.
  • Key Risk Area?: Using conditional formatting, the cell populates with “Yes” and highlights with red in a pattern if the value is less than 2, and populates with “No” if the value is greater than or equal to 2.
  • Quality Assessment: For “Not applicable,” the formula counts the number of cells under Performance Assessment (column K) for that process that are populated with “Not applicable.”

=IFERROR(COUNTIF(MoF!K7:K18,”Not applicable”),0)+IFERROR(COUNTIF(RA!K7,”Not applicable”),0)

For “Total Questions,” the number of cells with any value are counted.

=IFERROR(COUNTIF(MoF!K7:K18,”*”),0)+IFERROR(COUNTIF(RA!K7,”*”),0)

  • Performance by Institution: Graph references data in Data-PerfResults1.
  • Root Causes of Underperformance: Graph references data in Data-RCResults1.
  • Dominant Root Cause of Underperformance by Institution: Table references data in Data-RCResults2.
  • Sub-Process Analysis: Conditional formatting is used for the cells with the grades for the Key Questions, the cells with the outputs, and the cell with the sub-process. The conditional formatting highlights the cell according to the grade assigned. The cells with the Key Question grades reference the institution tab where that grade was assigned. The cells with the outputs and sub-processes reference Data-PerfResults2.

Dashboard-Quality Assessment

  • Overall (table and graph): They both reference data in Data-PerfResults2.
  • By Institution: The graph references data in Data-PerfResults2.

Data Tabs

The data from the Institution tabs is consolidated and analyzed in these tabs. As detailed above, these tabs are the data source for the majority of the analysis in the Dashboard tabs.

  • Data-PerfResults1; DataPerfResults2; DataPerfResults2: The Performance Assessment data is analyzed in these tabs.
  • Data-RCResults1; Data-RCResults2: The Root Causes data is analyzed in these tabs.
  • Data-SDGs: The data for questions that specifically address the SDGs is analyzed in this tab.
  • Data-Disaster: The data for questions that specifically address disaster preparedness is analyzed in this tab.
  • Data-Performance Grading: This tab contains all of the Performance Assessment options and corresponding grades for each Key Question. The Performance Assessment (column K) and Performance Grade (column M) columns in the Institution tabs both reference the data in this tab.
  • Data-Root Causes: This tab contains the list of Root Causes of Underperformance. The Root Cause of Underperformance (column N)and the Conclusion for PFM Process: Dominant Root Cause (column Q) columns in the Institution tabs both reference the data in this tab.

Ref-Mapping Tab

The structure for the analysis is mapped in this tab. It maps the PFM Process → PFM Sub-Process(es) → PFM Output(s) → Key Questions. It also provides the codes that help to identify these various components throughout this workbook, including the PFM Process codes, PFM Output Codes, and Key Question Codes. This tab is purely for reference and is not linked to any of the other tabs in the workbook.

File Names Tab

This tab includes the setup for the “Import Data” button that is described in more detail under the description of the macros and in section 3.

2. Setting up the Workbook for Distribution to Auditors


1.     Enter information into Instructions tab

Enter the Country, Date, Period-end, and W/P reference.

2.     Label MDA tabs

After the MDAs to be audited have been identified, label the MDA tabs in 2 different places:

  1. Right click on the tab and select “Rename.” Enter the acronym for the institution (if any) or the full name of the institution. For example, enter MoH or Ministry of Health. Repeat for all MDAs to be audited.
  • Type the name of the institution in the top left corner (row 2). Be sure to type the name in the correct cell as this information will be pulled into the Data tabs and into the Dashboards. In the example below, “Ministry of Health” is the institution to be audited and this name has been entered in place of “[MDA-1].”

3.     Remove tabs and tab references for MDA tabs that are not needed

If there are less than 7 MDAs that will be audited, a few manual steps need to be taken to adjust the workbook before it is distributed to auditors. For example, if you are only auditing the Ministry of Health, Ministry of Agriculture, and Ministry of Education, Ministry of the Environment, and Ministry of Justice, then only 5 of the MDA tabs will be used (MDA-1, MDA-2, and MDA-3, MDA-4, and MDA-5). You will then need to take a series of steps to remove the tabs for MDA-6 and MDA-7 and to remove references to these tabs in the Data tabs. The instructions, using this example, are provided below.

  1. Right click on the tab(s), select “Delete,” and then confirm that you want to delete the tab in the pop-up box. Repeat for each tab that needs to be removed.
  • In the Data-PerfResults1 tab → unprotect the sheet, highlight columns J and K (where MDA-6 and MDA-7 were previously listed), right click on the columns and select “Delete,” and reprotect the sheet.
  • In the Data-PerfResults2 tab → unprotect the sheet, highlight columns J and K (where MDA-6 and MDA-7 were previously listed), right click on the columns and select “Delete,” and reprotect the sheet.
  • In the Data-PerfResults3 tab → unprotect the sheet, highlight rows 10 and 11 (where MDA-6 and MDA-7 were previously listed), right click on the rows and select “Delete,” and reprotect the sheet.
  • In the Data-RCResults1 tab → unprotect the sheet, highlight columns J and K (where MDA-6 and MDA-7 were previously listed), right click on the columns and select “Delete,” and reprotect the sheet.
  • In the Data-RCResults2 tab → unprotect the sheet, highlight rows 10 and 11 (where MDA-6 and MDA-7 were previously listed), right click on the rows and select “Delete,” and reprotect the sheet. Repeat to delete rows 20 and 21, 30 and 31, 40 and 41, 50 and 51.
  • In Data-SDGs → unprotect the sheet, highlight columns K and L (where MDA-6 and MDA-7 were previously listed), right click on the columns and select “Delete,” and reprotect the sheet.
  • In Data-Disaster → unprotect the sheet, highlight columns J and K (where MDA-6 and MDA-7 were previously listed), right click on the columns and select “Delete,” and reprotect the sheet.

4.     Save master workbook and duplicate workbook for distribution.

  1. After completing steps 1-3, save the workbook as the master workbook and give it a file name that includes the name of the country where the assessment is being completed. For example:

Kenya_PFM Reporting Framework

  • Create a new folder on your computer to store this workbook and the workbooks that will be populated by the auditors for each institution.
  • Duplicate the file → Select the file, hold Ctrl+C, then hold Ctrl+V. This will copy and then paste the file into the folder.
  • Rename the file with the institution name. For example: MoF
  • Repeat steps c and d until there is a file for each institution.

3. Consolidating the Data from Multiple Institutions after Data Entry is Complete


To import the data from the files completed by the institution auditors, an “Import Data” button (with a macro attached) in the File Names tab can be used. To ensure that the import works smoothly, it is critical that the following steps are followed. It is not necessary to unprotect any of the worksheets before completing the import.

1.     Save all files (with data entered) in the same folder with the master workbook.

2.     Update the File Names tab.

  1. Before updating, the tab will be setup as shown below.
  • Under Tabs (column A), update the tab names to match the Institution tabs in the workbook. Under File Name (column B), update the file names to match the actual file names. If the file names have been changed by the auditors, you can either rename the file or enter the file name as it is into the tab. Delete any tabs and file names that are not being used.

3.     Click the “Import Data” button.

When you click on the button, it will take the import about 5-10 seconds to run. After it is complete, the following message will appear that tells you which worksheets were imported.

Details on the Macro are found in section 1, but some key details on how the macro works are:

  • Will still work if not all files to import are in the folder: When it runs, it will search for all files listed in the Macro, but it will still work if not all the files are present. This will be helpful, for example, if you have received files for only some of the institutions but want to go ahead and import the data that you have.
  • Will override existing data: You can run the import multiple times if you need to override the data. For example, if you receive a partially finished institution audit and go ahead and import it, and then later receive the fully completed form, then you can run the import again to override the data.
  • Only values will be copied: To minimize risk of issues when importing (i.e. copying from Institution worksheets and pasting in master workbook) the data from the other workbooks, only the values in the cells will be imported. This means that if any hyperlinks have been entered into the cells (e.g. a link to a file on the auditor’s computer), these will not be copied.

4. Copying and Pasting Dashboard Charts and Tables


While protected, the pieces of analysis in the Dashboards can be copied and pasted into another application (e.g. Powerpoint, Word) for use in reports, presentations, etc.

  • Select the cells that you want to copy and then hold Ctrl+C or right click on the cells and select “Copy”.
  • Open the application where you want to paste the chart/table and then right click on the page (or slide) and select “Picture” under Paste Options. Examples from Powerpoint and Word are shown below.

Powerpoint:

Word:

Posted in