Project Management

The Money Files

by
A blog that looks at all aspects of project and program finances from budgets, estimating and accounting to getting a pay rise and managing contracts. Written by Elizabeth Harrin from GirlsGuideToPM.com.

About this Blog

RSS

Recent Posts

Trends and Emerging Practices in Project Risk Management (Part B)

5 Key Financial Dates For Projects [Video]

Trends and Emerging Practices in Project Risk Management (Part A)

Why are your governance processes failing? [Infographic]

What tool do I use for tracking a project budget? [Video]

What is Payback Period?

payback period

Payback period is a term that you’ll seen on business cases and in other project selection documentation. It’s a criteria that helps organisations decide if they want to go ahead with a project or not.

But what is it? And how can we use it to our advantage?

Payback period refers to the amount of time required for your investment to pay back. In other words, it’s the time taken to ‘earn’ the amount of the original investment.

Let’s say you own a hotel. You build a penthouse room on the top. It costs £100,000 to build (you get the materials very cheaply! Go with me, this amount makes the maths easier).

The price for one night’s accommodation in the penthouse is £100.

Therefore, you need to sell 1,000 nights of accommodation to break even.

 100,000 / 100 = 1,000.

The payback period for this room is 1,000 nights.

That example is SO simplistic but hopefully you get the idea. Payback period measures how long something takes to pay for itself.

What kind of payback period is best?

The best kind of payback period is the shortest!

The faster something can pay back, and start bringing in profit, the better in business terms. Once you have broken even, payback period has been completed and any money earned after that is extra – the benefits you were hoping to see.

I love using payback period because it’s easy to understand and easy to work out. If you are comparing projects, when everything else is equal, the project with the shortest payback period should be the one you do first.

Of course, in real life projects aren’t equal in all other respects. You will be juggling a range of other selection criteria, of which, payback period is only one. However, it is a useful measure to look at first, to give you an idea of the effort involved in earning anything from this project. Something that has a payback period of over 10 years might not be worth doing at all and can be eliminated from project selection.

Depending on your industry, payback periods could be short or long. Any type of large-scale construction project will necessarily have a longer payback period than launching a fast-to-market consumer app. Some projects may not pay back for years, and still be worth doing. You know your industry and projects better than I ever will, so apply your professional judgement and consider what the ‘appropriate’ kind of payback period is for your organisation.

Pin for later reading:

payback period

Posted on: March 11, 2020 09:00 AM | Permalink | Comments (1)

Agile Finances on Projects: Cost and Procurement Management

You know I’m interested in the financial aspects of project management – I’m even thinking of teaching a workshop on project budgeting and accounting later this year. I feel like I’ve got quite a good understanding of the topic, but I’m constantly learning.

Having only done a very small amount of work in a team I would say is using agile practices, I am by no means an expert in the practical ways of making agile approaches work on project teams. However, there is a lot of guidance out there for people like me who want to learn more.

The Agile Practice Guide, in particular, has some interesting information about how to apply information from the PMBOK® Guide Knowledge Areas to agile work processes. From a financial perspective, here’s my interpretation of how this would work.

Project Cost Management in Agile Environments

Typically, on predictive projects you would do a lot of costing and estimating in advance. For projects with a high degree of uncertainty, or those where you don’t have a fully-fleshed out scope (hello, agile projects), then obviously you don’t have the data to do this level of cost analysis.

Instead, the recommendation is to use “lightweight estimation methods” (although no detail is given as to what these might be) to come up with fast, high level cost forecasts for resource costs. I can see this working when you are forecasting the general length of a particular engagement – even if you simply plan out the resource costs for the next financial period as a basic benchmark.

Detailed costs for things that aren’t people costs do still need to be done. You can’t run a successful business if you aren’t aware of what project work is costing you. It is OK, however, to do those detailed analyses on a more just-in-time and rolling basis.

To be honest, on some of the long programmes I’ve been involved with we’ve taken the same approach. The business case costs might have been fixed from the start, but frankly they were only ever our best estimate at the time. I then worked out detailed forecasts for the actual year we were in, so the Finance team could manage cash flow and we could accurately account for the capital outlay in the current financial year.

Where your budget is fixed, but you still need to be agile in other respects, then your choice is simply to flex scope and schedule to stay within the cost constraints.

Project Procurement Management in Agile Environments

Procurement management is another area where we incur costs, and as project managers, we need to be aware of how to manage that – in all environments.

Typically, procurements I have been involved with have had a protracted contract negotiation at the beginning to come up with a Master Services Agreement, and then you define the current engagement with a Statement of Work. As we needed suppliers to do extra things, we created new SoWs detailing that engagement. This is also how change control worked: the change control process generated either a credit note (when something was being changed and the end result was the development cost less) or a purchase order and an addendum to the current SoW.

It turns out that, according to the Agile Practice Guide, this is a pretty agile way of working.

There probably are projects where it is prudent and necessary to sign a massive contract upfront (ERP deployments spring to mind, from experience!) but generally, staying small with the engagement and working in a flexible way will suit both parties on the majority of projects.

Either way, something that is the same regardless of the project management approach you are taking is the need to document contractual arrangements and file them somewhere you can easily find them again. I have had a few moments in my career where I have sheepishly rung a vendor and asked for a copy of the executed contracts because – whisper it – it wasn’t possible to find our version of the same document.

Don’t make that mistake! Be agile. Be tidy!

Pin for later reading:

Posted on: February 19, 2020 08:00 AM | Permalink | Comments (2)

Pivot Tables for Beginners [Interview with Nick Nuss]

pivot tables headerNick NussRecently 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.

IF formula in Excel

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.

select pivot table

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!

select the data source

This image shows selecting a portion of the data source for creating your pivot table. The full data table is shown below.

full data table

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.

sum of hours

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.

average hours

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.

hours per week

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.

Posted on: November 06, 2018 10:48 AM | Permalink | Comments (9)

5 Levels of Project Financial Management Maturity [Infographic]

Categories: financial management

I put this infographic together to show the various different levels of project financial management maturity, as outlined by the P3O guidance from AXELOS. My view is that most companies should be looking to aim for Level 4. Level 5, with the implications that you are using Monte Carlo simulations and other types of advanced estimating tools, is probably overkill for most smaller projects or businesses without the exposure to risk that this helps mitigate. What are your thoughts? Is Level 5 where we should all be heading?

If you’d like to read more about the different levels, you can in this article.

Posted on: November 16, 2017 08:59 AM | Permalink | Comments (7)

Project Filing: Sorting Out Your Financial Papers [Video]

Keep the records on paper? Electronically? Or shred them? This video will tell you what project financial paperwork you need to keep (and how) and what you can destroy.

Posted on: April 04, 2016 11:59 PM | Permalink | Comments (1)
ADVERTISEMENTS

"In the beginning, the universe was created. This has made a lot of people very angry, and is generally considered to have been a bad move."

- Douglas Adams

ADVERTISEMENT

Sponsors