The Money Files

There are 5 steps in the procurement lifecycle:

1. Requirements
2. Vendor selection
3. Negotiation and contracting
4. Service delivery and performance monitoring
5. Renewal/contract closure.

This video outlines the steps and talks specifically about the requirements and getting the procurement off to a good start by understanding what you need from the exercise.

Read more articles about procurement.

What spreadsheet package do you use? Personally I use Microsoft Excel, although I do have Numbers on my iPad I case I need to look at a spreadsheet while I’m out and about. I sometimes use Google Sheets for personal projects as well, but I’m not massively comfortable with it.

Regardless of what tool you use, spreadsheets are a huge part of life as a project manager. Whether you are exporting data from an enterprise project management tool, creating a budget forecast or using filterable lists as a way to manage tasks, risks or issues, spreadsheets have so many uses in project management.

Here are some tips to make using spreadsheets easier – all tried and tested by me and colleagues over the years!

1. Add Notes and Comments

Add notes to your spreadsheet so you know how the formulae work and what estimates were based on.

You can do this by adding a separate tab to record notes, if there are a lot of them. I record the way I formulated an estimate for the budget, and what the figure includes, plus any assumptions around resource time and so on.

For short notes, I add them as a comment on the relevant cell. This is the easiest way to note when a cell was updated, or to record what was in the cell before you changed the estimate to be something else.

2. Add Version Control

Learn how to do version control on your documents (where version control is not added automatically by your software). Then add a tab to the spreadsheet showing a table with version control numbers.

When version controlling a spreadsheet, don’t try to add an incremental version number every time you update something. That would be time consuming and not add any value. Instead, do a major version update every time something substantial changes, such as changing major formulae or rebaselining. You’ll get a feel for when it’s important to create a new version.

3. Freeze the Top Panes

Project management spreadsheets can get quite large. In Excel you can freeze the top and side rows so that you can scroll through the spreadsheet and the headers stay in place. It makes reading and moving around a big spreadsheet much easier because you don’t lose the column and row descriptors.

4. Lock Cells

Use the functionality of your software package to lock cells that you don’t want other people to update. For example, in a status tracking spreadsheet, you might have cells that you need to have populated with ‘Open, Closed, On Hold’ etc.

You can fix the spreadsheet so that only those options are permitted. You can also lock cells so they can’t be changed, which is helpful if you have a form and you only want to allow people to enter information in certain places.

5. Add Conditional Formatting

Conditional formatting means the cell colour (or whatever section you choose) updates based on a rule. For example, if the contents of the cell is a number less than 50%, make the cell turn red. This is achieved using a red background fill. You can change the text colour, or use other formatting rules.

This is helpful when you have a large data set and you want to instantly draw people’s attention to figures that are outside the acceptable norms. You can colour code information as red, amber and green by setting up conditional logic once, and adding that formatting.

What tips do you have for making it easier to use spreadsheets? Share your best advice in the comments below!

Pin for later reading:

When you’re planning how to deliver quality results, and what needs to go into your project schedule, it’s worth looking at what regulations and standards you need to adhere to.

In this article we’ll look at the difference between regulations and standards and what that means to you managing a project.

What is a regulation?

A regulation is a requirement for your project. You have to follow regulations.

Regulations include applicable laws.

For example, there are a range of regulations that can influence the way you approach your project and what you can and cannot do. Here are some areas affected by regulation:

• Breaks for workers and time away from their job e.g. for lorry drivers
• Overtime hours
• Maternity, paternity, family, sickness and medical leave
• Health and safety
• Data protection
• Reporting e.g. for accidents or spillage of controlled substances.

Laws vary between countries, so check what is applicable to where you are based. Also note that in multinational projects, the laws and regulations might differ for people on your teams in different locations.

What is a standard?

A standard is a guideline. Your project should follow guidelines because they are there for a reason, but if you can justify why you need to approach something in a different way, then you don’t have to follow the standard.

For example, it might be the standard that no one in your office works on a bank (public) holiday. Let’s say it is normal for the office to close over the end of year period when many colleagues are celebrating Christmas. However, your project is to upgrade the telephony switches. Knowing that the call volumes will be low and no one will be around to answer calls anyway, you might decide that the Christmas period is the perfect time to do that project work. It’s not standard, but it’s the most appropriate solution for your project and least disruptive for the business.

Not all standards or regulations are going to affect every project. It’s important to have a view as to what is important for this project. It is something I would consider and resolve as part of project initiation, so that I can go into project planning with all the information needed.

How do standards and regulations affect projects?

Standards and regulations affect projects in a number of ways.

1. By affecting project scheduling

Any time legal compliance is required, you can bet you need to add extra time to the schedule to have the legal team check out what you are doing and ensure the project is ticking all the boxes. Build in enough time for regulation-related checks and work.

Equally, with resource-related regulations, you may have to constrain working time which will have an implication for the schedule. For example, you may not be able to use overtime hours, or you might have to factor in travel time to your schedule if your resources aren’t permitted to go over a certain amount of travel before taking a break.

Some of these constraints could be legislation affecting workers, others might be the way your company operates (or as PMI would define them, enterprise environmental factors). An example would be dictating that the standard working week is 40 hours. You would take that data and ensure your schedule reflects a 40-hour standard working week.

2. By affecting project quality

If you have to follow regulations or stick to standards, this could have an implication for project quality. You might have to do additional quality checks, or use particular materials. An example would be building control. In the UK, you need building control to sign off on construction work. You can’t simply carry on building or assume everything will be OK without having someone come round and inspect the site. That’s an external quality check you have to consider and plan for.

3. By affecting project budgets

If your project needs a building control check, you have to pay for that. The building controller will charge you for his or her time. That cost needs to go into your project budget forecast. Depending on what regulations and standards you have to abide by, your project costs will need to accommodate the related charges.

Once you understand the standards and regulations that affect your project, and how they are likely to affect the project, you are able to plan for them. Some might need mitigating factors and adding to the risk register. Others will be easy to manage, perhaps by adding a little extra time or an additional task to the schedule.

Do a bit of research at the start of your project and then incorporate what you need to so that your project, and your organisation, stay compliant with the relevant regulations and standards.

Pin for later reading:

I’m working my way through the Knowledge Areas and process of the PMBOK Guide®-- Sixth Edition, and at the moment we’re in Project Schedule Management. Last time I looked at the Estimate Activity Durations process. That means today is the turn of Develop Schedule.

Develop Schedule Process

This is the fifth process in the Knowledge Area. We’re still in the Planning process group – although this is the last Planning process for this Knowledge Area. Finally, after what seems like ages of gathering data, we are now in a position to actually create the schedule.

Unsurprisingly, the main output is your schedule. There are some other outputs too, and we’ll come on to those, but the schedule is the biggie.

Inputs

There are quite a few changes to the Inputs between the PMBOK Guide® -- Fifth Edition and Sixth Editions, and as we saw with the last process, it’s mainly items being removed.

This time we’ve lost 11 specific documents. These have been replaced with the generic Project Management Plan, project documents and agreements.

Agreements refers to documents from third parties that talk about how they will do their tasks. You could use contract schedules for this, or a statement of work. I think agreements should also refer to the arrangements you have with line managers to use their staff on projects. Getting this firmed up will help massively with being able to build a schedule because you’ll be confident that the people you need are available to you.

As we’ve now got ‘project documents’ as a handy catchall, it might be useful to clarify exactly what they could mean. Here’s a (non-exhaustive) list of the documents you could refer to during this process:

• The activity list and activity attributes as you need them to build the schedule
• The assumptions log
• The basis of estimates, an output of the previous process that will help you determine contingency for the schedule
• Task duration estimates (another output from the last process)
• Documents relating to team members that will help you schedule e.g. resource calendars and requirements
• The risk register
• The lessons learned register (from previous projects – you probably haven’t learned any useful scheduling lessons yet given you haven’t delivered any work yet).

Tools & Techniques

There are only a few tweaks to Tools and Techniques.

Critical chain method is out and data analysis is in. Data analysis covers things like ‘what if’ scenario planning and other simulation techniques. These are great if you have software that can do them, but the majority of non-multi-million dollar and major infrastructure projects will probably have to manage without.

Resource optimisation techniques has been renamed resource optimisation.

Scheduling tool has been replaced with project management information system.

And we’ve got a nod to Agile with Agile release planning.

Outputs

The only change to Outputs is the addition of change requests.

Change requests pop up in other processes, so this could be a standardisation improvement. You can see why change requests are relevant here. If you are making changes to the schedule that might affect the scope baseline or other elements of the project management plan. It’s all part of being integrated with project management and using the processes as appropriate throughout the project. If you need to make changes, go through the change control process and document what is decided.

Next time I’ll be looking at Control Schedule.

Pin for later reading:

So you’ve been given a project with a budget? Or you’ve been asked to create a project budget for the first time? Here’s what you should know.

The expected total

Even if you’ve been told to create a budget from scratch, the person who asked (your project sponsor) already has an idea in mind for what the “right” number is. Save yourself some time and ask them to tell you what their expectations are for the final figure.

Now you have to work out how close you can get to that number.

Breaking down the cost elements

You need to work out how much each individual element of the project is going to cost. This is called bottom up estimating.

Take your list of items and estimate a cost for each one. Involve any subject matter experts in this and make sure that you take into account any constraints and assumptions. The aim is to get a detailed and accurate (albeit estimated) take on how much each element costs.

Add the individual estimates together, and that’s your total expected spend. Hopefully, this matches the initial expectations of your manager or project sponsor.

Review against expectations

Once you’ve done some work on the budget, you’ll have an idea of whether you can hit the target they have in mind. If it looks like you’ll need more than that, you can start to position the scope of the project in a way that helps you get closer to the number. Here are some suggestions:

• Take scope items out
• Propose a Phase 2 so that extra funding can be secured later for additional features
• Reduce the resource requirements so you use cheaper resources, and accept that this might mean the project takes longer.

Or, of course, you could build a robust defence for why your project is going to cost more than your sponsor originally expected! Use data and facts to create the justification.

Next, you should talk to your sponsor about the other budget areas that should be considered before the budget is considered final.

Add management reserves and contingency

You can find out more about management reserves and contingency in this article, including how they fit into your overall budget.

Talk to your project sponsor about making sure there is adequate contingency for the project, and management reserves as required.

Add a risk management fund

If your project is relatively large and likely to be affected by significant risk, then it’s worth building in a risk management fund (more details on that here).

Basically, a risk management fund helps you pay for the activities required to mitigate, transfer or otherwise deal with risks. Instead of just tracking the risks, you can actually do something useful with your pot of money to offset the impact they might have on the project.

Set tolerances

Finally, talk to your sponsor about budget tolerances. That basically means the boundaries within which you can act without going to them for further approval. On a budget of £1m, you don’t want to be asking them for sign off because you need to spend an extra £10. Set some limits with which you are both comfortable and that give you freedom of action but ensure some rigour.

Note: tolerances in this instance specifically relate to financial boundaries, but the concept of tolerance is useful for other areas of project management too, like setting boundaries for schedule and risk. Tolerances provide you with guidance around how you can act without escalating.

Finally…

With all the calculations and discussions out of the way, you should be at a point now to agree the final budget. This figure is what you will work to and track against during the life cycle of the project. It’s the figure approved for your project, and you’re responsible for using the money wisely.

Report spending to the project board or steering committee and make sure you keep your numbers up to date. If you need help with tracking spend in a way that is considered appropriate by your company, start with the PMO. They probably have software or spreadsheets you can use to store all the financial figures. If you’re lucky, it might automatically create the information required for your financial reporting too!

Want more tips on creating a budget? Read this next: 5 Ways to Create a Budget

Pin for later reading: