The Money Files

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.

Thanks, Nick!

About my interviewee:
Nick Nuss coaches and blogs about the super power of Emotional Intelligence over at VocationCultivation.com. He is also a medical claims data manager at a Fortune 500 company and an Excel expert. His mission is that everyone unlock their full career potential by mastering their emotions.

I know this is a topic I have returned to several times, but I feel really strongly that project budgeting shouldn’t be hard, and we should all learn from past experiences. In the infographic below, I share three really common budgeting mistakes that I see happen time and time again, especially with project manager who are managing project budgets for the first time.

I’m sure you can think of other errors that newbie budgeters might face. Why not leave a comment below and share some of your tips about what to look out for when creating a project budget for the first time?

For more on the tax issue, take a look at this article, which discusses common budgeting mistakes and especially the tax thing, in a bit more details.

Project risk management is a team effort. As the project manager it might feel like you are taking the lead role, but overall it shouldn’t be a one-person job. You need to work together to identify the risks on your project and do something about them.

You can’t work as a team if you don’t have a team. So, you should identify your risk management team as early as practical in the project. That’s what textbooks would recommend, but in my experience you don’t always know who is going to be the right person to be the risk owner for a particular risk until it makes it on to the risk log – then you need that person on your team.

However, there are some common roles you will definitely need involved in risk management. Identifying who is going to fill those roles will save you time later. When a risk is uncovered, you don’t want to be waiting around trying to work out who is going to look at it. You want to know, broadly, who is going to help you deal with it.

Let’s look then at who does what in risk management on a project. These are the people you need to inform about the risk management processes and get them lined up to act when something is brought to your attention.

Project manager

You might think this is obvious – many of you reading this will be project managers. But if you are an IT workstream lead or a Scrum Master, or Product Owner, then maybe you will be working alongside the project manager.

The role of the project manager is to create the risk management plan. The risk management strategy is likely to be set by the Project Management Office, but you might need one specifically for your project. It is more likely that you’ll take the risk management policies for the business and the PMO and make them actionable and meaningful for your project.

Another role for the project manager is to update the risk log. Unless you have a dedicated risk manager working alongside you, that job falls to the PM.

Finally, the project manager should take a role in the governance of risk. That involves ensuring risk management actually happens and that people take the process seriously. They should know what the process is and follow it. You can check that there is enough attention being paid to risk overall and provide oversight. For example, make sure you have risk management as a standing item on your project board agenda.

Project sponsor

Second, we have the role of the project sponsor. They may not take a hands on role in doing mitigation actions (although they might, depending on what is required). However, they are going to be a huge influence on how risk is managed.

The sponsor will set the risk appetite for the project. That means they are accountable for the risk profile of the project (making sure it isn’t riskier than they would like) and ensuring it fits within the risk appetite for the business overall. 

The sponsor also acts as the escalation point for the team. They are able to resolve risks that the project manager and team can’t. And if it needs to go even higher, the sponsor is the person to do that.

Suppliers

Next we have suppliers. This is shaping up to look like a list of people who are involved in your core project team and project board, and that is not a coincidence!

Suppliers and the work they do also carries risk. They have responsibilities around risk management, namely making sure that they flag anything important to the project manager. They may maintain their own project risk log, but they should also be passing up significant risks to the project manager.

If a supplier tells you that their work is creating no project risk and there’s nothing for you to be notified of, be very suspicious! That to me would sound like someone who doesn’t know what risk management is or what they should be doing.

Many risks relating to your supply chain are going to carry a financial risk. For example, if the supplier can’t source the correct parts for your machine, then you’ll have to get them elsewhere at a higher cost. Make sure you factor in risk management plans for supplier risks because they could leave you significantly out of pocket.

Project team

Your core project team are essential people to work with you on risk management. You’ll involve them in risk identification at the beginning of the project and throughout. You’ll rely on their expertise to put together risk management plans and own the actions. You’ll need them to help you spot new risks or to deal with risks that become issues.

The day to day risk management activities are going to be carried out by the team.

Project Management Office (PMO)

Before you get too far into a project at a new place, talk to the PMO. What they expect you to do for risk management is going to follow the normal pattern: identify risks, manage them, report the big ones, but there might be specific processes or templates they expect you to use.

You might also be subject to internal audit or project assurance. The PMO may get involved in this and it would be natural to expect them to see your risk logs as part of any review.

The PMO’s role isn’t all about governance and holding you to account. You may also be able to draw on them for support. Sometimes project coordinators sit within the PMO and can be ‘loaned out’ to project managers for project admin or support tasks. This could include coming to risk meetings to take notes, updating the risk log, chasing team members for updates and things like that.

In Summary…

Think about who you are going to need for risk management on your project, just like you think about what resources you need for every other area of your project. Identify the types of people who will need to know about the process. And then involve them early.

Let them know what you expect of them and what the process is going to be. The earlier you do this on the project, the easier you will find the later stages of risk management because everyone will know what the whole thing is about.

Pin for later reading:

There are different types of project cost – your budget isn’t made up of one big lump of cash. Although it can sometimes feel like that!

Understanding the different types of project cost is helpful because they help you review the different categories of spend. You can use mindmapping to check that you haven’t left out any budget items.

The infographic below shares the 5 different types of project costs that you should consider for your budget. However, what it doesn’t make clear is that the project cost categories overlap. You can have fixed direct costs and variable indirect costs, for example. Sunk costs remain sunk!

Within the different categories you can break down your budget even further. For example, cost of quality might be something you consider for your budget formulation, but it could be a fixed, variable, direct or indirect cost, depending on how you are going to build quality into your project.

In this instalment of What’s New In the PMBOK Guide®-- Sixth Edition, we’ve made it to the sixth and final process in Project Resource Management (see here for Plan Resource Management, Estimate Activity Resources, Acquire Resources, Develop Team and Manage Team).

This is a brand new process. The old section on Resource Management focused purely on managing human resources, so this new process is a response to the fact that the Knowledge Area is now far broader and includes other types of resources.

Control Resources Process

This is the sixth process in the Knowledge Area. We’re in the Monitoring and Controlling process group.

This process is all about ensuring that resources are assigned to the project effectively and that they are used appropriately. That includes looking at actual utilisation of resources against what was planned and taking action as necessary to course correct should that be required.

Inputs

This is a new process, so all the inputs are new! And yet not new. They are things we have seen time and time again across all the other processes. Here we go:

Project management plan: This will include the resource management plan, which is your baseline statement of what resources will be required.

Project documents: this could include the issue log, lessons learned register, schedule, resource assignments (however you record them, in your software, for example), resource breakdown structure and resource requirements and risk register. All of these help you understand the reality of what is going on so you can take appropriate action.

Work performance data: for checking what has gone on. This could include timesheets, for example.

Agreements: this vague term means things like agreements for resources made with line managers of the people involved, agreements around overtime worked or extra hours needed.

Organisational process assets: these turn up all over the place. In this process, the OPAs could be policies around resource assignments and task allocation, the process for escalating issues when work doesn’t go as planned and lessons learned.

Tools and Techniques

As this is a new process, there is nothing to compare to.

Data analysis is in there as a technique. This broad term includes different ways of reviewing what the resource information and working out what might be needed. For example, performance reviews and cost benefit analysis.

Problem solving is another tool. This isn’t rocket science. If resourcing on your project isn’t going well you need to solve the problem.

You might need to do some negotiating and influencing to secure resources or work with your colleagues to resolve resource issues. Interpersonal and team skills are core to being able to monitor and resolve problems.

Finally, your project management information system is a tool to help. If you use your project management tools for timesheets or resource allocation, then you can see how this would be useful. You might be able to get resource allocation reports out of your software. Reports like utilisation, over/under resourcing could be very useful.

Outputs

Again, nothing to compare to as this is a new process. But it all makes sense. I’m not actually sure why this process is new. It feels like it should have been around for a long time.

There are four outputs:

• Work performance information: information on how work is going so you can look back on what was planned.
• Change requests: like many processes, once you’ve done it you might end up with a change request. That could be for a task to be allocated to another resource, to change the timescale of a task due to resource constraints or something similar.
• Your project management plan: because you might need to make changes to the resource management plan, and baselines for cost and schedule.
• Project document updates: this catch-all covers basically everything else. Whatever you need to update as a result of your monitoring and controlling activity, you can. For some clues around where to look, common documents that might need updating include the resource breakdown structure, resource assignments, risk and issue log and your assumptions log so you can track any new assumptions that you have had to make as a result of tweaking the work.

And that is the end of the Project Resource Management Knowledge Area!

Pin for later reading: