Hassan AFIF

ScrumProjectsPlan or SPP

I share with you my Excel workbook template which allowed me, as a Projects Manager, to follow the progress of my daily/weekly story tasks on several projects and contacts. I also adapted it to be useful for a management of projects using Agile Scrum methodology with several teams, features, stories and tasks. The workbook makes it easy to plan and target current and future dynamics Sprints (based on one or more weeks). Each workbook encapsulates a release. After a release was delivered, its unfinished stories can be migrated and added to the next release in a newly generated workbook. A snapshot of the previous release is kept in the workbook associated with this release. The workbook contains macros: ALL macros sources codes are opens and available within the workbook. We are still in alpha version because there are several tricks to implement. Your comments are very valuable to guide us on the right way.

Welcome to the ScrumProjectsPlan Tool Wiki!

Introduction:

The SrumProjectsPlan workbook was initially a simple tool for tracking tasks, especially when they have an enormous backlog with disparate tasks. It intensively uses a configurable conditional formatting to highlight tasks according to a well-defined typology. Subsequently, it evolved to allow a program manager or quality manager to monitor actions on several projects. Finally the tool opened to another dimension as agile as Scrum (I think :-). It allows managing the iterations or sprints where task / action becomes a Story and where the action type becomes a Module and where the project becomes a Feature. The Excel workbook encapsulate a Release. Future Releases can be generated by the tool so the unfinished stories will be migrated to the new Release. The tool also allows snapshots to be taken and sent using Microsoft Outlook. These are Excel workbooks with no macro useful for a sharing need. Emails of recipients can be configured as well as an email's subject and body. The email can be viewed before sent or sent directly to recipients. The workbook can be enriched with graphics other than the one presented by default.

Protections:

Automatic protection has been added to the workbook (which can be willfully disabled by the user) in order to guarantee:

• The protection of formulas against any alteration by mistake (cell lock)
• Protection from conditional formatting amalgamation. Because after copying cell, the destination cell inherits the conditional formatting of the initial cell. This is not often appreciated by the user (disabling the Copy / Paste function on the cells).

The user can at any time disable this protection if he wishes to adapt the workbook to his own convenience.

To do it :

1. Go to the 'Setting' sheet (click on the 'Setting' button in SPP Ribbon tab: see bellow )
2. In the 'Setting' sheet, press the 'DISABLE PROTECTION' and / or 'ALLOW COPY / PASTE' buttons.
3. Click the 'Data' ribbon button to go back to Data sheet
4. In the Excel 'Review' menu/tab, remove the protection from the sheet and, if necessary, the one from the workbook
5. Doing this, the protection will not be activated instantly by SPP (some time the protection may be restored after you press the other SPP buttons like the 'Migrate from' button).

To begin :

(1) Clean the workbook to remove the sample data provided by default:

• Click on SPP button 'Reset Data'

(2) Change the configuration data according to your project as follows:

• Click on the [Setting] button.
• Once in the 'Setting' sheet, make the following changes:

In 'Setting':

1. Features: Enter the list of projects or Features: Be careful the list must not exceed the bold frame. If you need it, insert new lines within this frame. 1th column: list of features used in data sheet Feature's combo-box. 2th column: A team name automatically associated to a story when you chose a Feature. 3rd and 4th columns: A list of a Snapshot's emails recipients separated by ';' The 5th column is just to recall some useful details for planning.

2. Module: Adapt the list of 'Type / Module / Sprint / Version'. Same as above, you must respect the limits of the bold frame. By occasion you can change the color of these cells so that it applies later to your Stories dashboard. Other colors are available to the right of this list. If you are interested only copy the formatting of it on the 'Type / Module / Sprint / Version' list.

3. Status: The status can also be changed according to your language. But only modify those within the black frame. Those outside the frame (left) must remain in English. The formatting of the font of these status can also be changed to be subsequently reflected later on the dashboard Stories.

4. Start / End: The range of rows for which above conditional formatting is to be applied, is to be defined in the 2 fields ‘Stories / Tasks begin line’ and ‘Stories / Tasks end line’. The ‘StoryMapping’ sheet provides an area of 999 stories, but for performance reasons only apply conditional formatting on the lines planned for the project. So start with an acceptable value for the 'Stories / Tasks end line' field. Thereafter you can readjust it if the number of stories exceeds this value.

5. Priority: Likewise in the 'Priority' area, respect the limits of the frame and modify the color that suits you. The effect of this color will only be on the 'Priority' cell of the 'StoryMapping' dashboard, not on the wool line.

(3) Now that you have customized the configuration, you must apply these changes to the 'StoryMapping' dashboard. To do this :

1. Return to the StroyMapping dashboard by clicking on the SPP button 'Data' .
2. Once in the 'StroyMapping' sheet, click on the SPP button 'Apply Setting....' and wait for a while (the value of 'Stories / Tasks end line' considerably affects this waiting time).

Other functionality:

In SPP ribbon tab:

1. [Show Active] button: Add a quick filter to show only activated stories with "In progress", "Not started" and Blank Status.
2. [Show All] button: Allows a single click to disable all applied filters. Useful especially before migrating data to a new version of the tool.
3. [New Story] button: Allows you to position yourself after the last story to allow the entry of a new one.
4. [Copy as new] button: Adds a new Story as a copy of the selected Story to the end of the stories list. Just have the cursor on the Story line to copy (not outside!)
5. [Snapshot] button: For people who do not like macro workbooks. This button generates a copy of the workbook without the macros (without the VBA code). The Snapshot is generated in the current directory with a suffix indicating the date and time of generation.
6. [Mail Snapshot] button: Generate a Snapshot and send it attached to Outlook email. The email may be viewed before by user in Outlook to be sent manually or sent directly without prompting. This behaviour is controlled in Setting sheet by: The email recipients (To and Cc), Subject and Body are also defined in Setting sheet:
7. [Goto next release>] button: Press this button once the current release is issued and you want to pass the next release. The button generates a new copy of the workbook but removes the finished and canceled Stories. In this case values in the 'Ref.' column will be generated accordingly so that a Story will have a unique Ref regardless of releases.
8. [Share me] button: Switch between: 2. 'allowing multiple users to work in SPP workbook at the same time'. 3. and 'Exclusive access'. While in "Sharing" mode (after click on [Share me]), some features are disabled (Excel limitation):
9. [Connected Users] button: Activated only when in “Sharing” Mode and bring out an Excel builtin box to let you know connected peoples to your SPP workbook. So you can request users to temporarly disconnect from SPP, to safely allow you switch to Exclusive mode and then benefits from full SPP features.
10. [New Tasksbook] button: In the data sheet (StoryMapping) a row is related to a story, and this story may have a list of action to be done. You have to manners to do this: (1) if it is not important for you to follow and share actions with others, so the column "Task/Comment" is enough for you with a prefix-naming convention (as explained far away in this document); (2) But if these actions require a little bit of management effort, in this case you should create for the story a related Tasksbook, this is the aim of the following sections. Tasksbook is a separate Workbook based on a template and it will be filled by SPP from story actions in "Task/Comment" cell. To od this, just select cell in a story row and click on [New Tasksbook] button. SPP will extract all lines in "Task/Comment" cell and produce for each line a one task. The status of each task in Tasksbook will be deduced from the prefix a extracted line (the prefix convention is explained later). In addition: (1) An hyperlinks to Tasksbook is anchored to Ref cell of the story: ; (2) and an Excel "Wraping Text" for "Task/Comment" cell is disabled; (3) an other hyperlink is anchored to "Task/Comment" to allow user easly take a look the tasks in this cell without opening a related Tasksbook.
11. [Edit Tasks] button: This button open a Tasksbook related to selected story for editing.
12. [Synchronize Tasks] button: Retrieve all tasks in Tasksbook back to the story in SPP "Task/Column" with a prefix for status and task's due date.
13. [Mail Tasksbook] button: The existing Tasksbook for selected story may be sent attached to Outlook email. The email fields are picked from Setting sheet (Recipients To & Cc, Subject and Body). As for Snapshot Mailing (see above), the Tasksbook mailing behavior is controlled by the same parameter : directly sending email or viewing it before sending.
14. [Show/Hide Header]: Toggle between showing and hiding a dashboard to let freeing more space in data sheet "StoryMapping".
15. Dashboard status (Data sheet header): First click on the status apply filter to show only stories with this status. A second click on the same status disable this filter. In the above example, a click on Closed status display only the closed story in the current release and the dashboard become like this:
16. [Migrate from...] button: Useful if a new SPP version is available in Github and you want to take advantage of its new features without retyping all your previous SPP Stories, so:

Warning :

Use this button on the new SPP version (the target Workbook)

Before operating it, make sure that:

• No filter is activated on the old workbook (otherwise only the filtered data will be migrated to the new version)
• It is necessary to close the old workbook before migration. Then once you click the button prompts you to choose the old workbook and then the trick is played. Before migration starts, you may be prompted by a dialog informing you that there is a big difference between the two versions but you can always chose to continue migration. In this case you must check the resulting SPP workbook if there is a missing data or it's in no expected place.

Intercepted events:

The tool performs some operations to keep track of changes on the 'Status' and 'Reviewed Date' key cells:

1. 'Closure or Applied Date' column: If this date is not filled, it will be entered self-filled if the status of story become closed or canceled. You can change this self-filled date if you desire.
2. LOG column: This read-only column shows you all changes happen in the columns 'Status' and 'Date'. If you want to see the entire LOG history, select a cell and switch the Excel button 'Wrap text' and switch it back. Logging can be disabled in Setting sheet.

Working with Sprints:

The ‘Current Week’ cell indicates the current week based on the OS date. Followed by a combo-cell to let you apply filter by Sprints so only stories planned for the selected Sprint will displayed. This filter is applied on the column 'Target Sprint / Week': It is important to know that in SPP, the Sprint is automatically deduced depending on a special Boundary story. You don't need to specify these stories are to belong to this Sprint (In my opinion is wasting of time). The principle is to define for each set of Stories a special Story that defines the final week (Target Date cell or reviewed date cell). This special Story can also mean the delivery Story as in this example "Deployment Story": To let SPP know the type of Story is of this kind, you should configure it in Setting sheet as follow: On the combo-cell only last week of sprint is visible and choosing this week "Sprint YYYYww" will show stories of all week in the sprint early to this last week (including the last one) and since previous Sprint boundary. If you start planning a new sprint and editing new Sprint stories, don't forget to synchronize the combo-cell with these change by clicking on the SPP button 'Compute Sprints' ! To let a related "Sprint YYYYww" appears in the combo-cell. Again: To let this computation be done correctly and let it find the sprint boundaries, please have practice to mark end of sprint by inserting the special story 'Deployment Story' as configured in 'Setting'. In Setting: In Data sheet:

Working with Stories / Tasks:

Each line represents a Story and change in its closure date affects the 'ReleaseProgress' sheet. On the other hand, to carry out the Story, and unlike stories a related tasks are entered in the column 'Task / Comment' without any monitoring of their progress, unless you start working on Tasksbook feature..

For my case (and as used by Tasksbook functions), I use the following convention for tasks:

• prefix '-': task defined but not started yet
• prefix '+': task performed
• prefix 'X': task canceled
• prefix '=': task In progress
• if a task have a sub-tasks, these one follow the main task with following prefixes:

• '_': sub-task defined but not yet done.
• '_/': sub-task done.
• '_x': sub-task canceled.
• '__': sub-task defined but not yet done.
• '..': sub-task in progress.

Modifiable data:

If the protection mode is activated :

• All locked cells are to be left read only because there not need to change it (the most are using formula of hided data)
• and Excel Does not allow you to modify it.

To know that your are in this mode, check 'Setting' buttons 'DISABLE PROTECTION' and / or 'ALLOW COPY / PASTE' buttons if are in red colored. This color means that clicking on it is not safe for SPP stability.

So no worries about this rules :-)