New Post at AITS: Decision-Making Under the Influence

My latest article for AITS was published today: Decision-Making Under the Influence: SME, HiPPO, and BOGSAT.

Good decisions require accurate, timely, actionable information and good decision-makers try to gather a variety of viewpoints. The influence of the subject matter expert is usually all three, while the highest-paid person’s opinion may be an undue influence, and peer opinions may be counter-productive. That’s not to say that you should dismiss them out of hand – far from it. You simply need to have a realistic expectation of what value they add. If you have comments on this topic, please leave a comment at AITS. If you have suggestions for future topics, please leave a comment here.

As always, thanks for taking the time to read my stuff.

New PM Articles for the Week of February 20 – 26

New project management articles published on the web during the week of February 20 – 26. And this week’s video: Jason Fried lays out his theory of why the office isn’t a good place to get work done, and some suggestions that address the cause of that conundrum.

Must read!

  • Bryan Menegus reports on the latest massive leak of passwords and personally identifiable information. Change all of your passwords and then read the details.
  • Mark Rice and Timothy Korson explain how to apply Malcolm Gladwell’s “thin slice” expert opinion approach and Planning Poker to estimate timeline and budget.
  • Michael Lopp tells how to win the “Successfully deliver hard news” merit badge.

Established Methods

  • Johanna Rothman suggests several scenarios that let you report defects based on the risks that arise from them.
  • Matthew Heminger illustrates the consulting power of Why with a story about a hole in the ground.
  • Rob England explains that #NoProjects doesn’t mean there won’t be any projects, just that it won’t be the primary mode of operation in IT.
  • Harry Hall links quality of requirements to quality of the outcome with a simple anecdote.
  • Dmitriy Nizhebetskiy provides a short textbook and video on creating a robust work breakdown structure. Even the full online course is free.
  • Mike Clayton tutors us on the project business case. Even if you’re not using Prince2, this is a great explanation of an important business practice.
  • Eamonn McGuinness just published the second edition of The Collaborative Project Management Handbook.
  • Karthik Subburaman lists eight excellent books on project management and related skills.
  • The nice folks at Smartsheet posted The Complete Glossary of Project Management Terminology.

Agile Methods

  • Stefan Wolpers curates his weekly links to Agile content, including the brilliant jerks who make Agility impossible, slicing user stories, prototyping, and more.
  • Ryan Ripley interviews Tom Cagley on the role and impact that certifications have had on the Agile movement.
  • Paul Culmsee reports from Creative Melbourne, where he was one of the inaugural speakers. Sounds like an interesting group of smart people being fascinating.
  • Max Ogle interviews Irene Au, design partner at Khosla Ventures and former head of design at Google and Yahoo, on the need to base designs on what people actually do.

Applied Leadership

  • Magi Graziano defines three tenets of leadership IQ: self-awareness, executive brain function, and response agility.
  • Seth Godin explains why we do what’s urgent, rather than what’s important.
  • The Clever PM contemplates the human factors that make people resist behavioral change.
  • Leigh Espy interviews Kane Hadley, who says that his foundational experience in project management came from playing Dungeons and Dragons.

Technology and Techniques

  • Kailash Awati tutors us on the basics of machine learning. Yes, there’s more to it than regression analysis.
  • Conner Forrest summarizes a recent report on the technical hurdles facing artificial intelligence based on machine learning.
  • Paramita Ghosh reports on one of Hitachi’s machine learning initiatives: the Robot Boss. Hitachi claims an 8% productivity increase in enterprise IT functions. No word on how much of that was attributable to fewer meetings.
  • Hussain Bandukwala begins a short series for the first-time PMO leader on setting up the PMO.
  • Elizabeth Harrin reviews a new on-line training course created by Philip R. Diab, a former Chair of PMI, called RapidStart PMO.

Enjoy!

Excel Functions You Probably Aren’t Using

I started using spreadsheets back in 1985, in the days of Lotus 123. At the time, I was developing reliability prediction models used in design tradeoff decisions for systems being developed for the US Army Corps of Engineers (that little reverse osmosis water purification do-dad under the counter at Starbucks is pretty straightforward technology, but when scaled to produce tens of thousands of gallons per day, using surface water and generator power, things can get complicated). I moved to Excel a few years later when I adopted Windows 3.1 but after more than three decades, I still manage to find new ways to capture and manipulate data. Let me share a few Excel functions that you probably aren’t using.

Calculating the Number of Working Days

It’s easy to calculate the difference in days, weeks, months, or years between two dates, but I often need to calculate working days.

NetWorkDays(Start_Date, End_Date,[Optional_Holidays])

This function calculates the difference between the two dates but ignores Saturdays, Sundays, and whatever holidays you pass it in a list. You can pass this example list either directly as B2:B9 or by defining a name for that range (highlight the range, right click, Define Name) and passing the name. We’ll use this list as an example:

Holiday

Celebrated

Martin Luther King’s Birthday (US)

1/16/2017

President’s Day (US)

2/20/2017

Memorial Day (US)

5/29/2017

Independence Day (US)

7/4/2017

Labor Day (US and Canada)

9/4/2017

Thanksgiving Day (US)

11/23/2017

Day after Thanksgiving (US)

11/24/2017

Christmas Day

12/25/2017

Let’s say I want to include the number of working days until some event in a status report. The effective date of the status report is in a named cell (done the same way you named the range of holidays) and so is the event date. Like so:

=NETWORKDAYS(Status_Date,Go_Live,Holidays)

This will return the integer number of working days, which you can then use directly or in another calculation.  I typically include the holidays for the project in a separate tab. But let’s say you crashed the schedule and decided you needed the team to work a few weekends, especially at go live. So create a list of Working Weekend Days, name the range, and add it to the calculation.

Working Weekend Days

Worked

Conversion Sunday

1/8/2017

Cutover Saturday

6/10/2017

Cutover Sunday

6/11/2017

Now we can incorporate those weekend working days into the formula:

=NETWORKDAYS(Status_Date,Go_Live,Holidays)
+COUNTIF(Weekend_Work,">"&Status_Date)

In this example, the CountIf function picks up the two days in the list greater than the status date of January 9, which is then added to the 108 days from the NetWorkDays function result:

Status Date

1/9/2017

Go Live

6/12/2017

Working days to Go Live

110

The Working days remaining equals the number of weekdays between the two dates, minus the three holidays that fall in the range, plus the two weekend dates greater than the status date. Note that if your weekend days are something other than Saturday and Sunday, the NetWorkDays.Intl function allows you to specify alternatives.

Summarizing Data in a Table

While we’re talking about status reports: it helps to summarize the information in your risk register, even at a high level. Take a look at this sample, which includes the results of a qualitative risk analysis.

Risk ID

Risk description

Last likely date of occuring

Probability

Impact

Calculated risk

1

This risk

1/1/2017

Medium

Medium

3.0

2

That risk

1/23/2017

Low

Medium

2.4

3

The other

4/1/2017

High

Medium

3.5

4

One more

2/1/2017

Low

Large

2.8

5

And another

7/1/2017

Very low

Medium

1.7

The Calculated risk field is based on a formula:

=SQRT(VLOOKUP(D2,RiskProbValue,2,FALSE)
*VLOOKUP(E2,RiskImpValue,2,FALSE))

Note that two lookup tables were defined to associate numeric values with the Probability and Impact scores; the risk is calculated as the square root of the product of the two numeric values. So let’s say you want to tally up the number of risks with High or Medium scores that are still likely to occur. On the status report, it looks like this:

High Risks

1

Medium Risks

2

We can use the CountIfs function to tally the risks for each criterion. For High risks, e.g. those with a Calculated risk value of 3.0 or more, and a Last likely date after the status date:

=COUNTIFS(Calculated_risk, ">="&3, Last_Date,">"&Status_Date)

For the Medium count, we’ll use a range of values:

=COUNTIFS(Calculated_risk, ">="&2, Calculated_risk, 
"<"&3, Last_Date,">"&Status_Date)

Getting a Completion Date

Sometimes I need to pencil out a very high-level timeline to determine if a goal is even achievable by some date. So I’ll create a list of tasks, each with a proposed duration, and start date. The assumption is that each task begins the day after the predecessor completes. I can then use the WorkDay function:

WorkDay(Start_Date, Duration,[Optional_Holidays])

This returns the serial number of the date Duration days after Start_Date. I can convert it for display using the Text function, like so:

=TEXT(WORKDAY(B7,C7,Holidays),"m/d/yyy")

I can also convert the date to the day of the week, again using the Text function:

=TEXT(WORKDAY(B11,C11,Holidays),"dddd")

This lets me specify a State Date for the first task and durations for each task in the list, with the subsequent start dates and completion date calculated automatically.

Task

Start Date

Duration

Plan

1/5/2017

5

Analyze

1/12/2017

25

Build

2/17/2017

15

Test

3/13/2017

26

Cutover

4/18/2017

3

Complete on

4/21/2017

Friday

Of course, the danger in this approach is that by tweaking the start date or individual durations, you can convince yourself that something is achievable simply by giving yourself less time to do it. So, don’t do that.

The ability to create reusable spreadsheets that use Excel functions to provide actionable information from raw data is still one of those skills that will pay big dividends over the course of a career in project management. All you really need is a little imagination and an understanding of what the data actually represents.