Recently I spoke to Nick Nuss, data manager, Excel expert and blogger. I like to think that I’m OK at using Excel, but one of the things I don’t understand is pivot tables. So I figured it was about time I learned how to use them.
Luckily, Nick was on hand to explain all.
If you’ve ever struggled with pivot tables, you’ll want to read this interview.
Nick, how can I use Excel to better report on data?
Excel is very versatile. You already know this. As a project manager, you probably have Gantt charts and templates saved out. Excel solves many of your data problems. As a data manager myself, I use Excel in a variety of ways but the functionality I use most is the pivot table.
OK, basics please. What does a pivot table do for me?
Pivot tables allow a user to report on a large data set in a table format. For instance, if you have to track anything in excel, you can report on it. Financials, time, counts of sick days, anything that you can lay out in a table, you can report on it.
So what do I need to know?
First, I want to tell you about table design. Then we’ll move on to creating your pivot table. Lastly, I’m going to dive into how to best use the pivot tables.
Great. Tell me about tables.
Good table design is essential for good reporting. Garbage in is garbage out in the data world, so if you create your table in a poor way, your reports will not be accurate.
Let’s talk lingo for a second. I will be referring to rows as “records” and columns as “fields” from now on. When we have a record, it will be a unique instance that we want to track. This can be per person, per date, per person per date, etc.
The lower down you go, the more information you can get later on. By tracking at a department level, you may not get the results you want later had you tracked things at an individual level.
What about fields?
Fields are what we will use in the pivot table to describe the records. These can be dates, IDs, financials and numbers to tell us information about what has or is to happen.
It’s important that fields have the correct formatting. For instance, financials should always be listed with the financial signs, and dates should always be listed as a date. Without the correct formatting, when you go to use the data, your dates may not calculate correctly (i.e. the difference between 11/30/2017 and 12/1/2017 is one day, but if you do not have it in date format, excel may calculate it as 1212017 - 11302017 = -10,090,000!).
Got it! Anything else I should know about fields?
Fields can be further broken down into two levels; descriptors and metrics. The descriptors are ways to split the data, such as department and job role. The metrics will be the things you want to control down the road, like your financials or hours.
You can certainly use metrics as descriptors, such as people who work more than 40 hours per week, against people who work fewer than 40 hours.
How do I do that?
It is usually easier to create the data table before creating the pivot table. You can create these metrics with functions like “=if()” which will take on one value when logic is true and another when logic is false. You can even have the function return “true” or “false” if you don’t enter what to do.
To do this, set up this equation =if(CELL >= 40, “what does it do when true”, “what does it do when false”). Be sure to replace “CELL” with the correct cell for your records! You can then click and drag this down your table to auto populate.
This image shows the beginnings of a data table with the IF formula for working out whether someone works more than 40 hours a week.
OK. Any other tips for the data table?
A good rule of thumb is to also have one field set as “count” where you enter a 1 for each record. This allows you to report on the total number of records later that meet a certain criteria.
Keep your records clean as they are created. Fill in each field for the record and keep them accurate. If you can track numbers as a decimal, do so. The more exact you are, the more exact future reports will be.
Each field needs a label name in record one for a pivot table to work. Be sure to name these with descriptive names that you can use later. These labels should be unique for later on. It’s also good practice to label these with the field type in case you forget. Keep “date” in the name for any dates and “amount” for any financial values.
Now I’ve got a good quality data table. Are we on to the pivot table?
Yes, creating the pivot table comes next. Go to a new tab within Excel to keep your table data separate from your pivot table. Select cell D6, which will give ample space for your pivot table to populate. This is will be the upper left of your new report. When we discuss how a pivot table works, selecting down and over will make more sense.
Now, in the ribbon at the top, navigate to the “Insert” tab and select “Pivot table” from the left.
This will open the data selection. Click where it asks you to enter the range for the data.
Now, click on the worksheet tab where you entered your data and select the upper left cell of your data (this will be field A and row 1). Next, press ctrl+shift+end on the keyboard (PC shortcut). This shortcut will select all records and fields in the table. Press enter and you have successfully created your pivot table!
This image shows selecting a portion of the data source for creating your pivot table. The full data table is shown below.
Pivot tables work best with lots of data. In this table you can see resource names, hours worked per week, whether or not the hours are above 40, a field for 'count' and the week number. That gives lots of metrics to analyse and report on.
Excellent! Is that it?
Not quite. The third step is reporting from your pivot table. By selecting the area of the pivot table that we had before, a wizard will appear at the right. The top section will show all of your fields from the data table and the bottom will contain four quadrants. The lower left quadrant will be where you place what descriptors you want for your data. In the lower right, you will place the metrics you want to display. You can still use your metrics in the lower left quadrant, but you cannot report on descriptors in the lower right.
In this pivot table, we've calculated sum of hours per person worked on the project overall during the first 3 weeks.
My favourite part about metrics is you can use the drop down to select properties, and display an average, sum, or percent of the total.
The top two quadrants are used for additional analysis. If you wanted to cross your descriptors by ANOTHER descriptor, you would want to place the second criteria in the upper right. The upper left quadrant is used as a filter, which tells your report “only by these records.” This limits the records to certain selected values, but they do not necessarily show in the report.
This pivot table shows average hours worked by employees who work over or under 40 hours. We can also add in more columns and see hours worked per week per employee as in the screenshot below.
That’s really helpful! I think I get it. Anything else I should know?
You can create any number of robust reports using a pivot table. The value of using pivot tables grows with the size of your data table. The larger the table, the better analysis you’ll get. Pivot tables are fast and easy to create. You will be amazed at how quickly you can report on things in Excel using pivot tables.
For more advanced pivot tables, you could also get data out of a database or Access file as long as you select the data as an import. You can do this from the data tab to allow you to select the information in a pivot table! Excel is amazing.
About my interviewee:
What a great question: is it worth moving clients to a retainer model for project services? A got in touch to ask me, and I thought it was probably a question worth sharing with everyone here.
This is what she said:
“We're a small graphic design studio with 3 employees. My boss wants to convince some of our clients to move to a retainer model. The thing is, most of our projects are on an ad hoc basis, without much consistency from month to month. My feeling is a retainer is not ideal in such a situation, but my boss likes the appeal of it... Money in the bank every month, how wouldn't you?! So my question is: when would you recommend a retainer, and when would you advise against it?”
What is a Retainer?
A retainer is a fixed fee that the customer pays you every month to secure a certain amount of work done. The work could be anything, as long as it’s covered by the scope of your agreement.
Sometimes hours not used are carried forward (often by a limited amount e.g. use within three months or forfeit the hours). Sometimes they are written off if the client doesn’t use them (which is the arrangement I have with a supplier at the moment).
Let’s look at the pros and cons of this payment model.
Advantages of Working on a Retainer
First, the most obvious advantage: it’s money in the bank every month! Whether you do the work or not! What manager wouldn’t want that? I totally get it.
This model works well for projects where there is an element of continuity. I know project have a start, a middle and an end, but if you have projects where there are incremental improvements planned over a year or so, you can see that having the commitment to move forward works well. Think design clients, web projects, app development, that kind of thing, although I’m sure there are other industries where this would also work.
It can improve the flow of work from the client. When they know they have committed to pay a certain amount for work done each month, you might find the work planning is easier. They should be letting you know what they need you for in advance of the next month. This can improve the consistency both of the incoming work (better for you) and the communication (better for both of you).
You should get to know them better and what they want, and that might help you advise them on how to use the retained hours each month. You are also more likely to prioritise their work above incoming fixed-fee or ad hoc projects, just because you have a relationship with them that’s different. That could be a selling point for clients.
Easier admin: both for you and the client. It’s one invoice, it’s a fixed fee, it can be largely automated as a recurring payment. It should be easier for you to maintain the relationship and manage the payment cycles (although for your own benefit and for “proof” you’ll still have to do timesheets). Fixed costs for the client could be a real plus point.
Disadvantages of Working on a Retainer
There are some disadvantages of course, for you and the client. First, you never know how much work the client might want you to do – if it’s a slow month you might be able to squeeze in extra ad hoc work from other people. It’s better to plan for all your hours to be used up so that you can definitely resource their work, but if they don’t send work your way you might have project staff waiting around.
Normally you’d charge your client less per hour on a retainer than you would for a project-driven rate – that’s the advantage to them of having a retainer.
The client might decide that if the work genuinely is ad hoc, that they don’t want this model and you’ll end up either going back to the way you worked before or potentially losing the client if you no longer offer that as an option.
Transparency becomes more of an issue. If the client doesn’t believe they are getting value for money they will vote with their feet and take their projects elsewhere. Think carefully about how you are going to do demonstrate what you have done and what value they have got from their investment each month.
So: When Does a Retainer Work Best?
I think the retainer model works well when the scope of the work is broad, ongoing or likely to evolve. In other words, where the requirement for a long term relationship seems apparent from the start. This might be through lots of micro projects such as graphic design projects, or through one larger piece of ongoing work.
It’s also an effective way of working where the breadth of the work required stretches over several teams or the capability of a whole agency/supplier. You aren’t costing hours per different type of specialist resource within your team, you’re quoting for work done on a flatter cost structure so it removes admin.
I have paid retainers before (and still do) but I am interested in hearing your thoughts on how this works in your business. Let us all know in the comments below, and thanks, A, for the thought provoking question!
A bridge is a way of displaying financial information in visual format. You might also know it is as a waterfall chart, or ‘the one with the flying bricks that looks like something from Mario’. It’s just a way of showing how an initial position has been affected by subsequent changes, so you can see why that would be useful for a company’s financial position. It can show changes that are positive and changes that are negative, and ends up with the new cumulative position as you can see in this diagram.
This picture shows a completely made up scenario, but I think it illustrates a point. In September, the starting position for this department was $75,000. This could represent value, profit or anything else. Then there were some things that changed. These are illustrated by the small floating boxes: the first change that happened was a positive improvement of $16,000. Then there were some other criteria, inputs and changes that also increased the situation positively.
Now we come to the black boxes. These on my chart represent money out, so let’s say this department spent $2,000 on some new software licences and $1,000 on a big party for everyone. This has had an impact on the net position so if my maths is right, the closing position on the graph, the situation in October, is now $100,000.
Great. But how is this relevant to projects?
Typically this type of bridge is used to represent financial information and you have financial information on your project, don’t you? I think it is a great way to present the impact of changes on your project budget to stakeholders. It’s useful because it’s a good visual representation of how you got from there to here and where the money went.
So you could use it to show the financial changes on your project, but there is nothing to stop you using the same layout to display other sorts of changes. Take this version, for example.
This shows you the situation in September in terms of project days. There are 150 days allocated to this project. Then there are a number of changes put forward. The green boxes show what would happen if you add those changes – the number of days spent on the project goes up (it’s not rocket science really). There are also some changes that save you time on the project. Let’s say that the big one, the 20 day time saving, is because the project sponsor has decided that the overseas office isn’t going to be included in this initiative after all, so there is no need to train those team members and you can save a whole lot of time. Another little change knocks 3 days off your project total.
If all these changes are approved, your project will now take 152 days.
When you are looking at individual changes at the change board, some stakeholders might find it hard to keep approving changes that add time. Two changes that add 10 days each? That’s huge. But when they see all the changes on the table that month laid out like this they can see that approving them all only adds 2 days to the project overall. That’s a very different story.
Of course, you might not want all those changes approved – there might be some stupid suggestions in there or functionality that would be better pushed off to a Phase 2. But using this bridge diagram gives you a new way to present the same data to stakeholders and help them decide on the impact overall.
I hope you find it useful!
At the Gartner PPM & IT Governance Summit last month Donna Fitzgerald gave a presentation about the factors that go to support a stellar delivery organisation. That’s a fancy way of saying getting your project management maturity levels up so you have a better chance of project success every time.
The Gartner PPM maturity level model includes 5 levels of maturity but Donna said very few clients make it Level 5. “Life at Level 2,” she said, “is no longer good enough. Many organisations are facing the crash and burn. They are operating in a perfectly acceptable paradigm for yesterday.”
The jump for Level 2 to Level 3 is what Donna called ‘crossing the chasm’. It’s the difference between process controls, governance and management and balancing capabilities and delivering measurable business value. That is quite a leap.
Don’t project manage everything
One of my top takeaways from Donna’s presentation was the fact that you don’t need a project manager for everything. Too often I see little projects managed by project managers when the development team or a business team could have managed it perfectly well by themselves (and 10 years ago did exactly that). If you want to do more with the skilled project resources that you have, stop asking them to work on business as usual projects that other teams can handle. “For optimal project success you want the work done in less than 6 months and full time staff of 5 for a half-time project manager,” she said. Project management deals with the uncertain, risky and complicated, she concluded, so if your project isn’t like that, then it doesn’t need a project manager.
If you do have a half-time project manager on the team she recommended time boxing their commitment. It’s now commonly believed that multi-tasking is bad and that humans aren’t very good at switching between tasks. Therefore if you are scheduled to work 50% of your time on one project and 50% on another project, make it so. Work Monday, Tuesday and Wednesday morning on one project and the rest of the week on the other. Don’t try to do both at the same time. A consulting firm wouldn’t expect someone to jump between assignments with an hour here, an hour there, she explained, so you can make fixed time allotments work.
I’m sure you can, but it’s going to be a mindset change for the workplaces I’ve experienced. Project queries and stakeholder phone calls don’t stop because it’s a Thursday.
“Done,” Donna said, “means the short list of things that define the business capability [being delivered by the project].” This is not the requirements document as that document is really a contract with an external supplier, she said. I would argue that it’s also often a document for internal supplier teams to work from and while you’re not likely to sue the team that sits on the other side of the office to you, there is a chance that you’ll use the requirements document as a basis to sue a vendor for failed delivery.
Define ‘done’ with your project team and sponsors so that you’ll know when you get there and you can work towards achieving that.
Don’t start without committed stakeholders
This was another major takeaway for me. Donna is ruthless! She said we shouldn’t start a project without committed stakeholders. She said that if they don’t turn up to the initial meetings (as sometimes happens) then don’t be afraid to ‘throw them under the bus’.
Say: “I’m sorry, I didn’t realise this was a bad time to start this project. We’ll reschedule.” And leave the meeting room or their office. Sometimes you’ll come across stakeholders who genuinely aren’t able to support the project right now and that’s OK. Reschedule project initiation for a time when they can commit. But sometimes people are just lazy or unclear on their responsibilities or happy to delegate everything to you and take no role in their project at all, and that’s where the bus comes in.
Make time to reflect
Part of delivering a stellar project organisation is knowing that things need tweaking. And with the stresses of managing projects it is unlikely that you, as a PMO Manager or even as a project manager thinking about your own project culture, have time to do that.
Make time was Donna’s recommendation. “Nothing ever gets fixed if you don’t realise it’s not working,” she said.
Book two afternoons per month to get away from your desk and think. Start with saying, where am I, where are we, what’s the health of my projects, what’s bothering me. Sometimes you can’t see patterns in behaviour because you are too close to them day-to-day so this gives you the opportunity to reflect and objectively assess what is going on.
She recommended we spend 20% of our time talking to sponsors and extended stakeholders as this is good for careers, and it’s what successful PMO Managers do. I don’t think I do that, but it’s certainly something I would like to focus on.
These were the top tips I took away from Donna’s very practical presentation. I hope they help you!
You can follow Donna Fitzgerald on Twitter @nimblepm.
I attended the Gartner Summit as a guest of Genius Project.