Monday, May 25, 2020

15 Excel Like Features in Microsoft Project – Part 1

There are a number of features that MS Excel users can use in MS Project. If you have mastery over MS Excel features frequently used, you can use many such features in MS Project.

Important Caveat: At the outset, I want to make this distinction clear: MS Excel is NOT a replacement for MS Project. The MS Project software stands on its own as a project management scheduling, cost, and resource management tool. MS Excel software, on the other hand, is a data storage, validation, analysis, representation tool with a number of built-in formulas. This article only informs the excel like features in MS Project.

I’ve used MS Project 2019 while using the features. I believe almost all of these features will be available in the earlier editions of MS Project, i.e., MS Project 2016 and MS Project 2013. I am not sure of very old editions such as MS Project 2010 and MS Project 2007.

Feature – 1: Auto-filling of items

Brief: Instead of filling up repetitive task names or resource types etc., you want to auto-fill these items.

How to do: To have auto-fill, you have to simply select the item and as the arrow converts to a tiny black plus symbol without arrows, you can drag the items to fill.

Example: I’ve a task A1 and I need to have similar 5 tasks. Simply put your cursor over the right corner of task’s cell and ensure it converts to a small black plus symbol without arrows. Next, drag the arrow down.

After the arrow converts to a small “+” sign, select and drag it down.

After you have dragged down, you need to release the mouse pointer. 

Similarly, if you have similar durations, you can drag for the duration by putting the cursor over there. I selected the 1st cell for duration and did an autofill. It comes as shown below.

Feature – 2: Insertion of multiple rows (Not columns)

Brief: You want to add multiple rows in one go. 

How to do: Select the number of rows before which you want to do insertion. Next, right click and use command “Insert Resource” or “Insert Tasks”. Based on the number of rows selected, an equal number of rows of resources or tasks will be added. 

Example: In the below figure, I’ve a set of human, material and cost resources. Do note that I’ve used the autofill feature to populate the resource names as well as the resource types.

Let’s say, we now have three more human resources for the project – “Human Resource 6”, “Human Resource 7”, and “Human Resource 8”.  You can add it at the bottom of the sheet or you can add just below “Human Resource 5”. However, to add just below “Human Resource 5”, you need to insert multiple new rows. To do so, select “Material Resource 1” and the subsequent two rows – “Material Resource 2” and “Material Resource 3”. And then right click. 

Next, select “Insert Resource” command as shown above. As you have selected 3 rows of resources, 3 more rows will be inserted. Because you selected three rows and then used the “Insert Resource” command shown above, three new rows are added.

As shown above, you now have 3 more rows added. Next, you can enter the resource names. If you have selected four rows and then clicked insert resource command, then four rows will be added. Depending on your need, you can add as many new rows at any place in the sheet.

Note: This feature is not applicable for columns in MS Project.

Feature – 3: Selection of Entire Row or Entire Column

Brief: You want to select the entire row or entire column in a table of any view.

How to do: Like Excel, you can select an entire row or entire column with two keyboard shortcuts. 
  • “Shift + Spacebar” command – Selects the entire row.
  • “Control + Spacebar” command – Selects the entire column.
Example: Put your cursor anywhere in the view of the MS Project. Currently, my cursor is placed on task “A3”.

Next, press “Shift + Spacebar” to select the entire row of A3. This is shown below. The selected row has been highlighted.

On the other hand, if you, press “Control + Spacebar”, then the entire column of A3, which is shown below. The selected column containing A3 task has been highlighted.

Feature – 4: Selection of a contiguous set of rows or a contiguous set of columns

Brief: You want to select a contiguous set of rows (or columns) in a table in any view of MS Project.

How to do: Like Excel, you can select a contiguous set of rows or a contiguous set of columns with two keyboard commands.
  • Select a column + “Shift” key+ Select the other column – Selects a set of columns, from the first selected column.
  • Select a row + “Shift” key + Select the other row – Selects a set of rows, from the first selected row.
Example: Let’s say you have selected the “Task Mode” column in the below Gantt chart view. Next, you want to select all the columns upto “Finish”. For that, press the “Shift” key and simultaneously select the “Finish” column. With this, all the columns from “Task Mode” to “Finish” will be selected.

The below view shows that the “Task Mode” column is selected.

In the below view (after using the “Shift” key and simultaneously select the “Finish” column), you have all the columns selected.

Feature – 5: Selection of a discontiguous set of rows or a discontiguous set of columns

Brief: You want to select a discontiguous set of rows (or columns) in a table in any view of MS Project.

How to do: You can use two keyboard commands for this purpose. The commands are similar.
  • Select the row, hold down the “Control” key and select other rows: This selects a discontiguous set of rows.
  • Select the row, hold down the “Control” key and select other columns: This Selects a discontiguous set of rows columns.
Example: As shown below, I’ve selected a row, i.e., the first row, in "Resource Sheet" view.

Next, you need to hold down the “Control” key and select as many rows (discontiguous ones). All of them will be selected as shown below.

Saturday, May 16, 2020

Scenario Analysis for Management Practitioners with Microsoft Excel (2)

In the earlier part of this post, I had outlined how scenario analysis helps managers to present their cases for the project,under various situations or scenarios. 

During project execution, too, this analysis will be helpful to communicate with the stakeholders based on various situations or scenarios. 

In the earlier part, we saw how to create the scenarios with MS Excel with a quite powerful pack of "What-if analysis". In this part, we will know how to run the scenarios and check the summary of all scenarios in one sheet.

Run the Scenarios
Next, we will run the scenarios by using the “Show” command in the Scenario Manager screen. This command is highlighted in the previous image. 

Select the first scenario of “High Labor Costs” and click on the “Show” command. This, in turn, will update the values of “C5” and “C6” and also the values in “Total” columns. This is shown below and updated ones are highlighted in blue.

As shown above, the labor costs for Phase 1 and Phase 2 have gone up by $200,000 and the total cost for these phases also have gone up. 

Similarly, when you the second scenario of “Low Expenses”, it will come as shown below. 

As you can see, in this case the expenses and hence total costs for all the phases have gone down by $100,000. The updated cells are again highlighted in blue.

Reverting Back to the Original Scenario
Many times, it’s possible you would like to revert back to the original scenario. To do that simply click on the “Undo” command on the Quick Access Toolbar (QAT) of MS Excel tool.

Summary of Scenarios
Another powerful functionality given with MS Excel is the summary of the scenarios you have created. This way you can check for the impact of all the scenarios along with the original values.

The summary report can be generated by running the “Summary…” command in the Scenario Manager screen.  

The summary report informs on the current or original cases, the “High Labor Costs” scenario and its impact, the “Low Expenses” scenario and its impact. For example:
  • For Phase 1 and Phase 2, the labor costs were $500,000 and $515,000, respectively. If you apply the scenarios, it will become $700,000 and $715,000, respectively.
  • For Phase 1, Phase 2 and Phase 3, the expenses were $275,000, $200,000 and $290,000, respectively. If you apply the scenarios, it will become $175,000, $100,000 and $190,000, respectively.

Final Words
While working as a project manager, it’s likely that you will be asked to check upon various scenarios for your projects. In this case we saw the increase or decrease of cost related items for the project. This can also be for your project schedule, too. MS Excel provides this in-built tool which can help work with various scenarios. 

Scenario analysis is not only needed by project managers, it may also be required if you are working as a portfolio manager or program manager or finance manager. Also, as mentioned earlier, in operational work with inventory, utilities, site related costs, you can be asked to have various scenario analysis.

This series Part - 1: 
Scenario Analysis for Management Practitioners with Microsoft Excel (1)

Wednesday, May 13, 2020

Illustration of Planning Poker Game Usage in Agile

By Suresh Juturu, PMP, ACP, CSM

Planning Poker is a consensus-based technique for estimating, mostly used to estimate effort or relative size of user stories in Scrum.

Planning Poker combines three estimation techniques − Wideband Delphi Technique, Analogous Estimation, and Disaggregation. Next, we will understand what these techniques are.

Three Techniques Used 
  • Expert Opinion − In expert opinion-based estimation approach, an expert is asked how long something will take or how big it will be. The expert provides an estimate relying on his or her experience or intuition or gut feel. 
  • Analogy − Analogy estimation uses comparison of user stories. The user story under estimation is compared with similar user stories implemented in the past sprints by the same team.
  • Disaggregation − Disaggregation estimation is done by splitting a user story into smaller, easier-to-estimate user stories. 

Step By Step – Planning Poker Game
  • In Planning Poker Estimation Technique, estimates for the user stories are derived by playing planning poker. The entire Scrum team is involved.
  • Planning Poker is played with a deck of cards. As modified Fibonacci sequence is used, the cards have numbers - 1, 2, 3, 5, 8, 13, 20, 40, 100 etc. These numbers represent the “Story Points”. Each estimator has a deck of cards.
  • One of the team members is selected as the Moderator. The moderator reads the description of the user story for which estimation is being made. If the estimators have any questions, product owner answers them.
  • Each estimator privately selects a card representing his or her estimate. Cards are not shown until all the estimators have made a selection. At that time, all cards are simultaneously turned over and held up so that all team members can see each estimate.
  • In the first round, it is very likely that the estimations vary. The high and low estimators explain the reason for their estimates. The team can discuss the story and their estimates for a few more minutes.
  • After the discussion, each estimator re-estimates by again selecting a card. Repeat the process till the estimates converge to a single estimate that can be used for the story. 
  • Despite relative estimating being easier than absolute estimating, there will still be times when someone will not be able to estimate a particular product backlog item. This might be because the person’s skills aren’t needed on that item. But that person may still be able to contribute to the discussion.
A representation figure of cards is depicted below. 

Image Source: Book: I Want To Be An ACP, 2nd Edition

In the above figure, you would have noticed cards with marks such as “0”, or “½” or a question mark. You would be wondering what are these cards? In planning poker, these are special cards, e.g., the card marked with "0" can be for “this story is already done,” or “this story is almost nothing, just a few minutes of work".

Various Questions on Planning Poker Estimation
Question – 1: The purpose of the planning poker?
Answer: The purpose is to get each team member's input on how difficult the story is from their point of view and estimate.

Question – 2: Agile teams uses scrum but may be having the difficulty in using the right estimation technique and try with planning poker once/twice and stop using it. Why?
Answer: Some of the teams start with planning poker estimation, without sufficient background work on understanding the concept and thinks this estimation technique takes too long for an agile team to adapt as they tend to see lot of debates for a given user story estimation which may vary from 5mins to 60mins! in the beginning, due to which many will stop using this technique.

Question – 3: What is the key to succeed with planning poker? 
Answer: There are two key points to note here.
  • Apply T-Shirt sizing technique first and categorize the user stories based on its size. Your team needs to get this done before sprint planning meeting.
  • Once the relative size is known(done with T-shirt sizing in the first key point), team can easily go for estimating the story points with their analogy and comparing from the past Sprint’s sample user stories.
Example: If the user story “JIRA-1234” (considering you are using JIRA tool for your Agile development work) took 3 story points, your team can now compare that with the current story point value (arrived viaT-Shirt size technique) and say that the current user story takes either more than 3 story points or less or may be equal and gets into the estimation. 

Question – 4: What if there are technical and/or product uncertainties and product owner needs more time to provide clarity? What if the product owner needs to get end users input to provide clarity to answer’s team’s questions? Or what if the team is unable to fold the cards to the same number and has large differences?
Answer: Best thing is to put back into the product backlog so that product owner gets more clarity for next sprint and move to next highest priority or go with a range of estimates.

Question – 5: Teams may have a situation where there will be dedicated testers will be there, but during planning meeting do they need to participate and estimate the user stories for other developers using planning poker?
Answer: I would say the answer is Yes. Because all the scrum team members need to participate in the planning meeting, of course a given person may not be a subject matter expert for the given story, but he/she can still pay attention and understand the discussion about the user story which will benefit the team in long run by having more product knowledge and also providing their estimates with their understanding. 

Your team can decide whether to consider their number or not for a given sprint. But over a period, everyone in the team will need to be capable of sharing every task. Hence it is important to participate and estimate for all members.

Useful Videos to Watch - Learn with fun
Right Way -
Wrong Way -
Why to use Fibonacci -

Brief Profile:
Suresh Juturu, PMP, ACP, CSM®-acp®-csm®