The Money Files

Let’s say you have been through your programme and are ready to close it out. There is obviously quite a lot to do, and the finance elements will be part of that. Here’s what to consider when closing out the financial management of a programme, inspired by the Standard for Programme Management.

Benefits stewardship

The programme will have created benefits, some of which have probably been realised as the work progressed. Towards the end of the programme, you may need to estimate the ongoing costs for making sure those benefits continue to be realised. For example, maybe recruiting an additional person to manage some deliverables once the programme team is stood down. This should last for as long as the benefits are going to be tracked for, or as long as you think is appropriate.

Any ongoing costs that will be passed to the operational teams should be made clear and budgeted in their ongoing profit and loss accounts for the department.

Leftover funding

Will you have any money left at the end of your programme? Probably not – in my experience project and programme teams tend to spend everything allocated to them!

On the off-chance that you do have funds left – let’s say, in the case of closing the programme a little earlier than expected – you should be in a position to hand some funding back. Any contingency funds that have not been used can be returned to the corporate ‘pot’.

Reporting

You’ve been creating financial reports for the duration of the programme, and those will now stop as the programme is wound up. However, stakeholders may be relying on that information. If there is the expectation that some of the financial reporting is still required, perhaps in a slightly different or amended format, you should put in place options to make that happen.

For example, perhaps another department can pick up running the reports, or they can be automated.

Tip: Even if you are automating the reports, please make sure each report has an owner! When we migrated a load of reports from a legacy system into a new one we weren’t sure which reports were used and which were no longer required because there was no data ownership. We didn’t migrate a bunch of them, figuring that if they were missed someone would say! Nobody said anything, so it’s probably those were simply no longer required, even though the system produced them regularly.

Sustainment

Sustainment of a programme is the work required to make sure the outcomes are maintained going forward, once the programme structure itself is no longer there to support them. Beyond benefits, there might be some additional funding required to sustain the programme’s vision, achievements or outcomes. For example, perhaps you implemented new tools and now the business needs to have someone in post to maintain that software.

In my experience, people who enjoy the environment of delivery are not always the same people who enjoy the day job. You may find that programme resources are not interested in staying on in ‘day job’ roles to support the ongoing running of whatever needs to be sustained, so you could end up having to budget for hiring new roles.

Close out checklist

At the end of your programme, check to make sure you have the following aspects covered from a budget perspective:

• Financial inputs to the programme closure report and any final financial reports or closure statements
• Updates to the financial management plan if necessary
• Updates to the lessons learned database or organisational knowledge repository
• Any related documents that might be useful in the future
• All invoices and supplier contracts wrapped up and closed
• Programme budget and cost centre/cost codes shut down so no further work can be allocated to them.

What else would you consider when closing out a programme budget? Let me know in the comments!

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.

For more information and extra detail about who does what when it comes to project reporting at different levels of the organisation, you can check out this guide and infographic.

Or: Who Does What When It Comes To Project KPI Reporting

So many KPIs, so little time! There are reporting requirements at every step of the project and at all points in the hierarchy. Not sure who on the team should be involved in what kind of progress tracking? Here are some pointers for who should report what. Adapt these to fit your team, but it’s a good starting point.

Team Member

They should analyse the work they have done themselves, so they’re reporting on progress made and work still to do. And time sheets, if you use them.

There’s an overlap here with skills required, so you’ll need to get them involved with upcoming planning to ensure that they are capable of doing the tasks – otherwise it will take them muhc longer and with much worse outcomes in terms of quality.

Project Manager

Project-level analysis relating to project planning and budgeting. If this is you, you’ll complete the project dashboard and summarise the project performance metrics.

Programme Manager

If this is you, you’ll be doing programme level reporting, using inputs from the project managers. As your programme is probably broken down into stages, you can report actual progress (on all the projects) against the forecasted progress for the stage. Do  this for time and budget as a minimum.

There may also be other programme level key performance metrics that you choose to track such as resource allocation across the project with a view to managing skill levels.

Portfolio Manager

The kind of thing you’re reporting on is even more rolled up than that of Programme Manager. You’re looking at analysis by the type of work carried out, progress across phases and stages of programme against forecast and juggling the budgets across the portfolio so you’ll want to know top level budget performance to feed into portfolio metrics.

Look at metrics related to time to complete work so you’re also keeping an eye of capability and performance as well as time/cost/quality of the work done.

Director/Executive

As a project sponsor, you’ll want to digest the project-level reporting from the project manager. But as a senior exec responsible for diverse portfolios, your data needs are more aligned to the governance and strategic functions you perform.

Look for metrics that analyse time for the different types of work that you have in the business. Splitting activities by runners, repeaters and strangers is a good starting point, and then you can apply the governance metrics to those.

‘Runners, repeaters and strangers’ is a Lean technique to help you know where to concentrate your efforts.

• Runners are things your team is working on all the time: normal processes and tasks you require to be completed every day. For example, the fundamental customer service processes for selling your products.
• Repeaters are the activities that you see regularly but they aren’t daily occurrences. For example, handling complaints or the change management process.
• Strangers are activities that rarely happen, like a one-off project to close down a facility and lay off 100 staff.

As an exec, look at the time/cost analysis relating to these tasks to see if the business is spending its resources wisely.

So your project is being audited? Lucky you! There’s quite a lot to think about if this is happening to your project, whether the audit is a formal thing or whether you’ve been selected as the project that’s being scrutinised by the PMO this month as part of an informal ongoing review process.

But auditing isn’t that big of a deal if you know what to expect. Every audit process is going to be slightly different but here are some common things that you can prepare for.

Provide The Information

The first step is that you’ll have to provide a whole pile of information to the people doing the audit. Whether that’s your best mate from the PMO or a scary-looking external auditor, they’ll give you a list of the kind of things they want to see. This will include:

• Your plans and project initiation document
• Latest project reports and budget spreadsheet
• Risk, issue and change logs
• Process documents
• Test scenarios
• Access control information or policies
• Strategies that relate to your project such as your approach for data consolidation, cleansing or validation or coding quality approaches.

The auditor (or audit team) will then take some time to go through all of this. It’s likely to be around two weeks as there’s a lot to digest, and they’ll want to understand it so they might come back to you with follow up questions.

They’re preparing to dig deep into the material and establishing what else they might need to know.

First Meeting

Once the audit team has reviewed your paperwork, they’ll start digging more deeply into the project. At your first meeting (for which there should be an agenda so you can prepare adequately in advance), expect to be taken through a list of points. Consider this the orientation meeting.

It can help if you provide an overview of the project and the objectives as part of the scene setting so be prepared to do that on the spot if asked.

Use the time to really understand what is expected of you and what the process is going forward. For example, what are the reporting expectations during the audit period? If they are asking you for updates, how long will you have to put those together and send them over? In what format?

And if you are expected to make changes to the way you are doing things (which might not be at the request of the auditors but by your manager or the PMO perhaps) then how long will you have for doing that?

Try to establish what the outcome of the audit process will be – they should be able to tell you, and should be able to explain what input you will have to the format and content of the final report. There should be a process by which you can comment on a draft and put your points across before anything is published to senior management.

Find out what your management team or project sponsor intends to use the audit output for, so you can better prepare for any action that is coming your way at the end of the audit.

Follow Up

Following the first meeting you should expect lots of emails! The auditors may want to speak to people in your team or subject matter experts, so make the right people available. If you are submitting follow up or additional documentation you may need to upload this to a secure online repository (if they are external) or provide it over email. There are lots of documents whizzing around in a formal audit situation so try to keep track of what you’ve sent to whom and when.

You might find that after a flurry of activity it all goes quiet for a while. This is the time when the report is being written up. Try to find out when the draft will be coming to you for review so you can block out some time to look at it and add your comments. Be honest in your commentary back but be prepared to challenge anything that you don’t think is accurately representative of your project or the processes being used to manage it.

All that sounds quite daunting but taking part in a project audit shouldn’t be scary – it’s just time consuming and a drain on your team’s resources while you are also trying to get work done. Make sure that your stakeholders and project sponsor know what is going on so that they can give you a bit of grace when it comes to short deadlines and the like during the audit period.

Audits like this won’t take more than about 8 weeks start to end as a maximum, from finalising commercial terms and the engagement letter that kicks off the audit process with an external group to the submission of the final report. However, that time can drag when you are trying to hit project milestones and keep all the plates spinning as well!

Remember that audits are supposed to be highly useful, informative exercises, whether that’s a formal external review or an informal assessment by your PMO peers. The outputs are going to help you manage the project more effectively and get better results for the business, so don’t take the recommendations personally and work together with your team to put the advice into action. Not everyone is lucky enough to go through an audit and what you learn can be invaluable to helping you manage future projects more effectively.