Project Management for Wildlife Conservation
PMWC TRACKERS AND REPORTS USER GUIDE
V1
PMWC Trackers and Reports User Guide
Acknowledgements The Project Management for Wildlife Conservation (PMWC) Trackers and Reports User Guide, outlined in this manual, was developed with reference to the PMWC approach and PMWC manual v2.0 [1] The development of the PMWC Trackers and Reports User Guide would not have been possible without the generous financial support of an anonymous Donor.
Authors Beth Robinson (WildTeam) Stuart McBride (WildTeam) Adam Barlow (WildTeam) Jane Hooper (WildTeam)
Photo credits The photos used in this manual are modified versions of the following photos: Front cover: Crimson Chat by Matthew Creasey Setting up Google Drive: Zebra by Matthew Creasey Key Functions of Google Sheets: Butterfly by Matthew Creasey Producing Reports: Bamboo by Beth Robinson
WildTeam
2
PMWC Trackers and Reports User Guide
Creative commons license This PMWC Trackers and Reports User Guide is issued under the Attribution-ShareAlike Creative Commons licence version 4.0, which allows others to: • Share: Copy and redistribute the material in any medium or format • Adapt: Remix, transform, and build upon the material for both non-profit and commercial purposes. Under the following terms: • Attribution: Give appropriate credit, provide a link to the licence, and indicate if any changes were made. This may be done in any reasonable manner, but not in any way that suggests the licensor (WildTeam) endorses you or your use • ShareAlike: Make available any additions or changes to the material under the same licence as the original • No additional restrictions: legal terms or technological measures may not be applied that legally restrict others from doing anything the license permits. The above copyright does not cover the use of the following items (which are covered by separate copyrights): • The WildTeam logo • All photos used in the PMWC Trackers and Reports User Guide • Any PMWC Trackers and Reports User Guide content referenced to another source.
Suggested citation WildTeam. 2017. PMWC Trackers and Reports User Guide v1.0. WildTeam, Cornwall, UK.
WildTeam
3
PMWC Trackers and Reports User Guide
Contents 1
Introduction ...................................................................................................... 5
SETTING UP GOOGLE DRIVE ......................................................................................... 6 1
Overview ............................................................................................................7
1.1
Setting up ..........................................................................................................7
1.2
Navigating ........................................................................................................ 8
1.3
Sharing folders and documents ........................................................................ 8
KEY FUNCTIONS OF GOOGLE SHEETS ........................................................................... 11 2
Overview .......................................................................................................... 12
2.1
Document details ............................................................................................. 12
2.2
Tabs ................................................................................................................. 12
2.3
Filtering ............................................................................................................ 13
2.4
Entering dates .................................................................................................. 15
2.5
Formatting options ........................................................................................... 16
2.6
Data Validation ................................................................................................ 17
2.7
Conditional Formatting..................................................................................... 18
PRODUCING REPORTS ............................................................................................... 20 3
Overview .......................................................................................................... 21
Literature references .................................................................................................. 25
WildTeam
4
PMWC Trackers and Reports User Guide
1
Introduction
The purpose of this document is to provide guidance for use of the Google Sheets-based Trackers and Reports (Box 1), in support of implementing the PMWC approach (Box 2). Further details regarding the PMWC approach can be found in the PMWC manual v2.0 [1]. The Trackers are used to monitor progress against the plan, and the reports provide snapshots of project progress at regular intervals and at the end of a phase. See the PMWC manual for more details about the different types of report, when they are required, and who is responsible for producing them [1]. This document introduces the reader to how to set up Google Drive, it covers useful functions within Google Sheets, and provides a step-bystep guide for producing reports.
Box 1. Types of Trackers and Reports. Trackers: • Pre-project tracker • Project tracker • Programme tracker • Document review tracker • Document tracker Reports: • Meeting report • Monthly status report • Quarterly status report • Annual status report • Phase-end status report • Exception report • Project assurance audit report • Operations assurance audit Box 2. Project management for wildlife conservation (PMWC): A methodology covering the knowledge, skills, and approaches required to successfully manage wildlife conservation projects. The methodology has been developed by WildTeam, but based on a range of established approaches, including PRINCE2 [2] and The Open Standards for the Practice of Conservation [3].
WildTeam
5
PMWC Trackers and Reports User Guide
SETTING UP GOOGLE DRIVE WildTeam
6
PMWC Trackers and Reports User Guide
1
Overview
There are lots of different types of Google documents, including Google Sheets, Google Docs and Google Slides. Google documents are free to use and can be edited by multiple people at the same time, which makes them very useful for collaborative work. All work in Google documents is saved automatically as long as you are connected to the internet. All Google documents are stored within a Google Drive. This section provides guidance on how to set up and use Google Drive.
1.1
Setting up
Tips: 1. Where possible use Google Chrome when using the PMWC Trackers and Reports as it is most compatible with Google documents. 2. Because of the popularity of Google documents there are many internet help forums. Therefore, searching online for a solution to any problem you may encounter often produces a suitable answer. 3. For keyboard shortcuts in Google documents follow this link.
To access Google Drive you will first need a Google account, this can either be a Gmail address or a Google account linked to a different email. To set up a Google account with a different email follow this link. You can download Google Drive software to sync all the files in the Google Drive to your computer. This is useful if you need to access the files offline. • • •
To download Google Drive follow this link In the window that appears there will be a cog button in the top right hand corner, click this and select the option ‘Download Drive’ (Figure 1) To sync the Google Drive to your computer choose the ‘Settings’ option that is found in the same location and from the window that will appear select ‘Sync Google Docs…’ (Figure 2).
Figure 1. How to download Google Drive.
WildTeam
7
PMWC Trackers and Reports User Guide
Figure 2. Location of syncing Google Drive option.
1.2
Navigating
Once you are in the Google Drive window you can search it like any other file explorer by double clicking on a folder to open it. Near the top of the window will be the file path of the folder you are currently in (Figure 3). If you are unsure of the location of a document or folder you can use the search bar at the top of the window.
Tip: You can star important documents to make them easier to find. To do this right click the document or folder and click ‘Add star’. All the starred documents will be accessible from the menu option on the left-hand side of Google Drive labelled ‘Starred’.
Figure 3. Location of folder within Google Drive.
1.3
Sharing folders and documents
Individual Google documents and whole folders can be shared with your colleagues and collaborators. This is useful if you want to share only specific documents or folders rather than the whole drive. Restricting who has access to a document can be useful for security reasons. To view who has access to a document or folder click the ‘i’ button located in the top right-hand corner of the Google Drive window. Under the heading ‘details’ you will be able to see who has access and if they can edit the document, only view, or only comment.
WildTeam
8
PMWC Trackers and Reports User Guide
1.3.1 • • •
•
How to share a document Click the ‘Share’ button located in the top-right of a document (Figure 4) Type the recipient’s email address into the box (Figure 5) Then select the pen icon on the right of the box and select whether you want the person you are sharing the document with to be able to edit the document, to only comment on it, or to only view it When you are finished click ‘Done’.
Tips: 1. You can also get a shareable link to share documents by clicking the option in the top right corner of the sharing documents dialogue box. 2. If someone shares a file or folder with you it will be located in the ‘Shared with me’ section of your Google Drive which you can access from the left-hand side of your Google Drive home page. 3. If you share a folder with someone, they will also have access to all sub-folders within that folder you shared.
Figure 4. Location of the Share button.
Figure 5. The dialogue box that appears when sharing a document.
WildTeam
9
PMWC Trackers and Reports User Guide
1.3.2 How to share a folder • • •
Locate the required folder within Google Drive Right click on the folder you want to share and select ‘Share…’ (Figure 6) The steps from this point on are the same as for sharing single documents.
Figure 6.The location of the option to share a folder.
1.3.3 Sharing documents and folders within an email If you are using Gmail then documents and folders can be shared within an email. This function is useful for directing people to documents already within their Google Drive, for example, recent reports. To do this: • Start a new message or reply to an existing message • Click the ‘Insert files using Drive’ option located at the bottom of the window (Figure 7) • Navigate to the required document or folder • If the recipient of the email does not have access to the document or folder you will be asked if you want to give access. If this happens choose the type of access you want the recipient to have (e.g. view only or edit), then click ‘Share & send’.
Figure 7. Location of the ‘Insert files using Drive’ option in Gmail.
WildTeam
10
PMWC Trackers and Reports User Guide
KEY FUNCTIONS OF GOOGLE SHEETS WildTeam
11
PMWC Trackers and Reports User Guide
2
Overview
Google Sheets is Google’s version of a spreadsheet. As with other spreadsheets, Google Sheets is set out in rows and columns, and can have multiple tabs.
Tip: If you hover the cursor over any column within the trackers there will be notes giving details about the content of that column.
This section covers the features of Google Sheets required for using the PMWC Trackers and Reports. Not all these features need to be used on a regular basis; however, knowledge of them is useful should any of the formulas break or if you wish to tailor the Sheets to suit your needs.
2.1
Document details
At the top of a Google Sheet is information (Figure 8) such as: • The name of document which can be edited by clicking in the box • The location of the file and the option to move the document • The date the document was last edited.
Figure 8. Details of a Google Sheet: a) document name, b) location of the file and c) the date the document was last edited.
2.2
Tabs
Within a Google Sheet there can be multiple tabs.
2.2.1
Rearranging tabs
The different tabs of a Google Sheet can be rearranged if required, or they may accidently fall out of order. To rearrange tabs: • Click on the tab you wish to move • When the four-pointed arrow (Figure 9) appears drag the tab to its new location.
WildTeam
12
PMWC Trackers and Reports User Guide
Figure 9. Rearranging tabs.
2.2.2 Renaming tabs To rename tabs: • Right click the tab and select ‘Rename’ (Figure 10) • Type in a unique name (Note: a workbook cannot have two tabs with the same name).
Figure 10. Location of the renaming tabs option.
2.3
Filtering
Filtering is used to only view a subset of the information in a sheet according to a specific set of criteria. There are two types of filtering: Full filter and Filter view.
2.3.1 Full filter When this type of filter is used, it will change the view for anyone viewing that document. To use the full filter: • Click in the top row (this is to make sure this is the location you filter from) • Click ‘Data’>‘Filter’ (Figure 11) • Use the white drop down arrows in the top right of each column heading to select the data you want to view. WildTeam
13
PMWC Trackers and Reports User Guide
Figure 11. The location of the filter option.
2.3.2 Filter view When this type of filter is used it only changes your view of the document, anyone else using the document will not see a change. This is useful for viewing only your own tasks and milestones in the work plan section of the Tracker. To use the filter view: • Click in the top row of the document • Click ‘Data’>‘Filter views…’>‘Create new filter view’ (Figure 12) • A grey box will appear around the edge of the document • Use the white drop down arrows in the top right of each column heading to select the data you want to view • To change the name of a filter view, click where the name is displayed (top left of the grey area) and type in a new name, e.g. Tom’s actions (Figure 13).
WildTeam
14
PMWC Trackers and Reports User Guide
Figure 12. The location of the filter view option Enter the name of the filter view here
Click here for more options including deleting the filter view
Exit the filter view here
Figure 13. How to use the filter view.
2.4
Entering dates
There are some locations within the Trackers where dates need to be added - for example, the baseline and expected due dates within the work plan tab. To apply a date format to a column: • Select the column(s) that contain dates • Go to ‘Format’ > ‘Number’ > ‘More Formats’> ‘More date and time formats…’ • From the drop down boxes select the format you want. Dates within the PMWC trackers are set up as shown in Figure 14.
Figure 14. The format of dates within the Trackers. WildTeam
15
PMWC Trackers and Reports User Guide To enter a date in a cell:
• •
•
Enter it in the format day/month/year e.g. 01/03/2017 When you click out of the cell it will automatically change to the set format e.g. 1 Mar 17 To edit an existing date double click on the cell and a calendar will appear from which you can choose a date (Figure 15).
Figure 15. The date editing window.
2.5 Formatting options There are various options within Google Sheets to format the cells to your preference.
2.5.1
Wrapping text
Text wrapping formats the text within a cell so that it all fits within one cell. If the length of the text within a cell is greater than the width of the cell, the height will automatically change so all the text fits within it. To wrap text: • Click in the cell(s) you want to apply text wrapping to • Go to ‘Format’ > ‘Text wrapping’
2.5.2 Merging cells Cells can be merged together to make the layout of a Google Sheet neater. To merge cells: • Highlight the cells you want to merge • Go to ‘Format’ > ‘Merge cells’ > ‘Merge all’ • There is also the option merge the highlighted cells only horizontally or vertically • To unmerge cells click on the cells and go to ‘Format’ > ‘Merge cells’ > ‘Unmerge’.
2.5.3 Paint format The ‘Paint format’ tool is very useful for setting up or fixing problems with Google Sheets. It allows you to copy and paste the formatting of one cell to another, including conditional formatting.
WildTeam
16
PMWC Trackers and Reports User Guide
Figure 16. The symbol used to select the paint format tool. To use paint format: • Click in the cell that contains the formatting you want to transfer to another cell • Select the paint format tool (Figure 16) • Then click in the cell you want to transfer the formatting to • This will paste the formatting of the origin cell into the new cell.
Tip: To undo an action press ‘CTRL’ + ‘Z’ on windows or ‘⌘’ + ‘Z’ on a Mac. To redo an action press ‘CTRL’ + ‘Y’ on windows or ‘⌘’ + ‘Y’ on a Mac.
2.6 Data Validation Data validation is used when you want to be able to choose from a list of inputs in a cell. Examples within the PMWC Trackers include: • If you want to report on an item in your work plan within the Project tracker you can choose from the options ‘Yes’ or ‘No’ • If you want to assign a task to an owner from a pre-set list of people working on the project. To use cell validation: • Highlight the cells you want the rule to apply to • Right click and select ‘Data validation…’ (Figure 17) • Fill in the data validation dialogue box (Figure 18) • When you are finished click ‘Save’.
WildTeam
17
PMWC Trackers and Reports User Guide
Figure 17. Location of the Data validation option. The cell that the data validation rule applies to.
Figure 18. How to use data validation tool.
Enter the list of options for the data validation by a) choosing ‘List from a range’ and highlighting the range of cells you want to choose from (this can be in a different tab). Or b) choosing ‘List of items’ and type the options separated by a comma.
2.7 Conditional Formatting Conditional formatting is used when you want to automatically format a cell dependant on the contents. Examples of conditional formatting used in the PMWC Trackers include: • Making a cell a certain colour if it contains a particular name • Making a whole row of cells grey if a task is complete • Making a cell red if the task or milestone is in exception. To use conditional formatting: • Right click on the cell(s) you want to apply this function to and select ‘Conditional formatting…’ (Figure 19) • This will bring up the box shown in Figure 20a that lists the rules that exist already for that cell • To create a new rule, click ‘Add new rule’. This will bring up the box in Figure 20b
WildTeam
18
PMWC Trackers and Reports User Guide •
• •
You can choose from several pre-set rules, for example, ‘Less than’ which means the rule will apply if the number in the cell is less than a number you specify in the box labelled ‘value or formula’ Then select the formatting style of the cells to which that rule applies. You can change the text format, e.g. make it bold or italic, or make the whole cell(s) a different colour. When you are finished click ‘Done’ or ‘Add another rule’.
Figure 19. Location of the conditional formatting option.
Select the range of cells to apply the rule to Select set rule or custom formula Select formatting style of cells that rule applies to
Figure 20. Conditional formatting options. a) The display of existing Conditional formatting rules and b) adding a Conditional formatting rule.
WildTeam
19
PMWC Trackers and Reports User Guide
PRODUCING REPORTS WildTeam 20
PMWC Trackers and Reports User Guide
3
Overview
The PMWC Reports also use Google Sheets. The Reports extract information from the Project tracker, thereby capturing a snap shot of the project. This section covers the steps involved in creating a report.
Step 1: Update the Project tracker First you need to ensure that the Project tracker is up to date. This includes making sure all dates are correct on the work plan and that all milestones and tasks that are to be reported on are marked ‘yes’ in the ‘report against?’ column.
Tip: Entering a weekly reminder into each team member’s calendar can be useful for keeping the Projecttracker up to date.Sending an email to team members a few days before making the report asking them to ensure the Tracker is up to date can also help with this.
Step 2: Prepare the Report conversion sheet To create a report there is a middle step that uses the Google Sheet called the ‘Report conversion sheet’. The purpose of this middle step is to import the information from the Project tracker and sort it into the correct order so that it can be copied and pasted into the report template. Tips: The Report conversion sheet contains, at 1. It is good practice to glance at the minimum, two tabs, ‘Risks & Issues’ and data to see if everything looks to be ‘Milestones & Activity’. Tabs can be added if where it should be and fix any errors additional data is required for the report, e.g. as required. Lessons learnt. Within each tab of the Report conversion sheet there is a formula called the 2. The Report conversion sheet IMPORTRANGE formula which extracts automatically refreshes but it is good information from the Project tracker. The practice to hit the refresh button IMPORTRANGE formula is only in row 4, before you start to ensure it is up to however, it automatically fills the cells in the date. rows below.The IMPORTRANGE formula will only need editing the first time the Report conversion sheet is used.
Figure 21. Details of the ‘IMPORTRANGE’ formula.
WildTeam
21
PMWC Trackers and Reports User Guide There are three parts to the IMPORTRANGE formula which are explained in Figure 21. To change the Project tracker the Report conversion sheet is inputted from you need to change only the unique ID of the IMPORTRANGE formula. To do this, follow these steps: • Highlight the whole of row 4 by clicking on the ‘4’ on the far left-hand side column of the document. This turns the whole row blue • Click ‘Edit’>‘Find and Replace…’, or press ‘CTRL’ + ‘H’ on windows, or ‘⌘’ + ‘Shift’ + ‘H’ on a Mac to bring up the find and replace box (Figure 22) • In the ‘Find’ box enter the unique ID already in the Sheet that you want to replace (from within the formula; Figure 21) • In the ‘Replace with’ box enter the unique ID of the new Google Sheet you are linking to. This can be found in the address bar of the new Sheet (explained in Figure 23) • To make sure the function also searches with formula you must select the box that says ‘Also search within formulae’ (Figure 22) • If the text ‘#REF!’ appears in cell you need to hover the pointer over it and click ‘Allow access’ (Figure 24) • These steps must be repeated for each tab. The unique ID of the OLD Google Sheet The unique ID of the CURRENT Google Sheet
Figure 22. Find and replace box.
The numbers and letters inside the forward slashes is the unique ID of the Google Sheet Figure 23. Details of where to find the unique ID of a Google Sheet.
Figure 24. Error message that might appear when changing the IMPORTRANGE formula. WildTeam 22
PMWC Trackers and Reports User Guide
Step 3: Filter the data In order to copy only the data required for the report you will need to use the filter option in Google Sheets (see section 2.3 of Key Functions of Google Sheets). Depending on which report you are creating you should filter on a number of criteria. The main thing to remember is to always filter the ‘report?’ column to ‘Yes’ and to always keep this filter on whilst you are filtering the other columns. Other areas to consider filtering are: • Date columns – think about the period you are reporting against (monthly, quarterly etc.) and use the filter parameters as required • Remember that milestones due this period relates to the baseline due date and milestones due next period are filtered by expected due date.
Step 4: Prepare the status report template This step requires you to prepare the appropriate report template by ensuring that all areas of the report are blank and ready to be pasted into. If you are using a previous report as a template be sure to check that all merged cells are unmerged and that there are enough rows to paste in the new data.
Step 5: Paste into the status report Now that the data has been filtered and the template has been prepared, you can populate the report. First highlight all relevant columns from the Report conversion sheet and copy them (‘CTRL’ + ‘C’ on windows and ‘⌘’ + ‘C’ on a Mac). You should copy only columns with grey headings and not those with purple headings. When you paste the data you must paste the values by right clicking and click ‘Paste special’> ‘Paste values only’ (Figure 25). If you do not do this then the formulas will be pasted across rather than the data.
Figure 25. Where to find the ‘Paste values only’ option.
WildTeam 23
PMWC Trackers and Reports User Guide
Step 6: Format the status report Now that all the data has been copied across you may find that some of the formatting has shifted the text or moved, so it is a good idea to review the Sheet and make improvements where needed. Some things to consider are: • Wrapping the text so it remains on one page (see section 2.5.1 Key Functions of Google Sheets) • Merging some cells which contain lots of text (see section 2.5.2 Key Functions of Google Sheets) • Adjusting the font • Adjusting the font size (size 10 is recommended) • Adjusting the font colour • Spell checking (‘Tools’ > ’Spelling…’) • Checking conditional formatting on coloured cells (see section 2.7 Key Functions of Google Sheets) • Checking all headers are correct.
Step 6: Add extra detail to the report Extra detail can be added at the top of the report, this includes: • A report heading (e.g. WildImpact Monthly Report May 2017 v1.0) • A date • A summary of the report, which can be used to highlight anything of note to the period the report covers. The information entered into the summary on the report tab will automatically appear in the summary box on the tab which contains the dashboards • A rating for the Budget, Schedule and Results which can be Red, Amber or Green. The contents of these cells will automatically feed into the ‘Overall’ box to the right.
Step 7: Paste in the dashboards Next take the relevant graphs from your Project tracker Progress dashboard. To do this either directly copy and paste the graphs into the relevant space on the report dashboard or take a screen grab using an image capture tool (e.g. snipping tool in Windows). Make sure to align all graphs so the layout is clear and tidy.
Tip: It is good practice to note in the report the date and time the graphs were captured.
Step 8: Share and file the reports Once the report is complete it will need to be shared with the relevant people. The simplest way to do this is to send a link within an email. See section 1.3 in Setting up Google Drive for options for sharing documents. The report will need to be filed in the appropriate folder according to the guidelines in the PMWC manual [1].
WildTeam 24
PMWC Trackers and Reports User Guide
Literature references 1. WildTeam. 2017. Project Management for Wildlife Conservation v2.0. WildTeam, Cornwall, UK. 2. OGC (Office of Government Commerce). 2009. Managing Successful Projects with PRINCE2. 2009. TSO (The Stationery Office). ISBN 978-0- 11-331059- 3. 3. CMP (Conservation Measures Partnership). 2013. The Open Standards for the Practice of Conservation v3.0. Conservation Measures Partnership, Washington, D.C.
WildTeam 25
PMWC Trackers and Reports User Guide
WildTeam
www.wildteam.org.uk 26