Stop using Excel for project financials

Deliver on budget and stay profitable with Price&Cost

 
18
Aug

5 Tips on Project budget management you can use today

Andrei Bernovski
by
Andrei Bernovski

18th August 2017

Project management has many faces. Some see a project manager as a mere facilitator, whereas others say they need to be a subject-matter expert. Amongst other success factors (timely and quality delivery, client satisfaction, etc) there’s always money involved and armed with these practical project budget management tips – you can be sure you’ll gain control over project financials in no time!

Tip 1: From Gantt charts to Effort estimate

When people think of project management – they immediately visualise a project plan (yes, Gantt charts are predominantly an attribute of waterfall, but somehow became a widely-spread visualisation of Project management as a whole). Gantt chart are no more than tasks plotted onto a timeline with inter-dependencies mapped. Gantt charts are a great visual way to communicate multiple threads of data on one graph:

  • Scope – by showing the work breakdown structure and the task names
  • Duration and sequencing – by showing the order of tasks and length of lines
  • Interdependencies between tasks – by showing the dashed/dotted lines connecting the tasks

There are of course more things you can add to your project plans, like the resources and their allocation %, key milestones, critical paths etc.

project budget is often overlooked as it’s merely an additional dimension

However, project budget is often overlooked as it’s merely an additional dimension bolted onto Gantt charts and not the primary one. Microsoft Project and other project management tools allow you to define the rates for the resources and then assign them to tasks at certain capacity. This will give you the price for the task and combining all tasks together – a total project budget.

What drives the price of the task is not the duration, but the effort for each resource multiplied by their rate. There’s an easy way to see this information in Microsoft Project. The one I personally find the most useful is the list of tasks grouped by resource on the right and their respective effort figures grouped by either day or week on the left. To get to this view – click “Resource Usage” in the VIEW menu. To change the breakdown of effort (days or weeks), do the following:

  1. Once in “Resource Usage” view, right-click on any column header with a date
  2. Select “Timescales…” from the drop-down menu
  3. Switch to the “Middle Tier” tab in the popup
  4. Under “Timescale Options”, find a “Show” dropdown. Select “One tier (MIddle)”
  5. Under “MIddle tier formatting”, select the desired scale, either “Weeks” or “Days”. Set the “Count” to 1

Tip 2: Effort is the best currency

So, now that you have a table of numbers instead of a familiar graph – how is that useful to you, you ask? Well, you see, this is really the only true way to understand how the project pricing works. The project plan creation software will give you a good rough estimate of the total budget and prices for individual tasks, but it guesses how your resources are allocated to tasks day-by-day. It is not uncommon for Microsoft Project to split an 12 hours task that is meant to be spread across 5 days evenly. This will give you 2.4 hours a day. If you work in an agency or a studio environment where resources should be carefully planned and booked in advance – try taking these 2.4h per day requests to your resourcing manager and see what they say 🙂

The reality of the environment with resourcing constraints and competing priorities is different to what a piece of software assumes. If you just go by the figures MS Project gives you and lock this down as your project budget – you are setting yourself for trouble already. What if then you can’t get the resource you need for just 12h, but only get them for 2 full days (16 hours) and these days should be consecutive?

Estimating effort-first allows you to tweak the resourcing to available budget. Click To Tweet

As good as MS Project is for a project plan creation – it’s very limited in allowing you to play around with the effort, and as a result – with project budget. This “Resource Usage” view we looked at earlier is not read-only – you can switch to a daily breakdown and correct the effort figures of each task for every day to bring it closer to reality. But what you are likely to see is that MS Project will start changing the durations of the tasks. Unless you want to be constantly switching task Types for every task back and forth and that way correct software’s thinking process – I suggest you try to separate the effort figure manipulations from the Gantt chart and, in fact, MS Project. I’ll try to explain why.

You are much better off using a Excel spreadsheet to get a final effort breakdowns and, you guessed it right – your project budget.

This way you will be able to easily manipulate the numbers without necessarily affecting the timeline, but you’ll be confident your budget estimations are bullet-proof and aligned to resourcing realities. Please see below for a manual way to transfer the Resource Usage data from Microsoft Project into Excel, or if you are feeling adventurous – smart people have already written a VBA for that.

Manual way to transfer effort from Microsoft Project into Excel.
Manual way to transfer effort from Microsoft Project into Excel.

In addition, having full control over effort figures enables you to work through a number of scenarios. Have you ever spent time crafting a perfect project plan to arrive at a budget figure higher than expected? Estimating effort-first (and ideally outside of MS project) allows you to quickly change a resource or their allocation and if need be – tweak the resourcing to available budget. Once done – you can easily create a project plan in MS project or if you have done already, don’t forget to check if you effort manipulations affected the timelines. But this should be easy as you’ll soon learn to ‘see’ the tasks just by looking at the effort figures.

Tip 3: Project budget tracking

I learned a long time ago, that project manager’s top skill is not just to come up with a meticulously crafted project plan, but to constantly be making small adjustments to the plan to keep the project on track.

There’s a brilliant description I’ve come across recently:

Project Management is not unlike flying an airplane. Flying conditions are perfect as the plane heads toward its destination. However, even on a clear day, wind currents in the air can push the airplane off course. As the pilot monitors the gauges, she will notice if this is occurring and will compensate by steering the plane back in the other direction, toward the intended destination.

 

Project Management Accounting (Amazon.co.uk)

In addition to monitoring the completion of tasks, quality of the deliverables, scope creep, etc. tracking the project financials is essential. Projects exist to deliver value to organisations. And if you’ve delivered a top-notch product that may have been on time and of stellar quality, but costed twice the planned budget – there isn’t much value in that.

Referring to what we said earlier, adjusting to compensate deviation of one aspect will almost certainly have effect on another. And often this is money. If you feel your project is going to be late, you are tempted to just throw more resource in. This may get you on track from a timing perspective but will increase your costs.

The concept we refer to above is called Project Management Triangle. It recognises 3 aspects of a project and describes the connection between them. Read my other post on the importance of Project Management Triangle.

There are 2 aspects to project budget management: knowing where you are now and planning for the future. Once your project has started and you have resources working on the tasks, you accumulate actuals – the actual time people spend on the tasks as part of delivering the project. If you have time-sheeting system in place, make sure people record their time accurately and regularly. If you don’t – I suggest you start using one now.

Using a spreadsheet we’ve created in Tip 2, update it with the data from your timesheeting system (your actuals) for all resources who worked on the project. Just copy the effort figures for individual days/weeks over the ones you had in your estimate. Do this regularly (e.g once a week) and do it from day 1 on the project. This will give you a true picture of your performance against the baseline budget. And next time you’ll have the urge to throw more resource onto the project – you will be able to make an informed decision.

Project forecasting

Tip 4: Project forecasting

Updating actuals and comparing the spend to date to the baseline is just one part of tracking the project budget. As much as you plan upfront before starting the project, things will change. Always. So will your plan for resources and how much of their time you require.

Say, you’ve decided to throw more resource on to the very same task that was late. And you budget seems ok even with the additional resource (maybe you’ve under-burned on previous task). However, armed with the knowledge that the task at hand was underestimated (hence the need for additional resource), your future task of similar nature need to be adjusted as well. This is where forecast adjustments come in.

Adjusting the forecast effort for each resource is no different to adjusting the project plan ever so slightly periodically. You may want to increase or decrease the effort for particular resource for future tasks or may be change the resource all together. There are more complex adjustments you can be making – for example instead of using a senior resource for 5 days you decided you’d be better off having a junior resource for 8 days. This may result is same price, but there are other factors that come into play, like margin, which may increase from this change. However, we’ll leave this subject for another post.

All in all, if you grow a habit of periodically updating the actuals and adjusting the forecast – you will not only know where you stand financially on a project at any given time, but be able to tell the budget at completion. Comparing this to the baseline will give you an instant indication of your project financial health – a sort of reality check.

Tip 5: Post project financial analysis

We all want to get better at what we do over time. And there is no better way, but to learn from you previous experiences. A post project review (or post-mortem or retrospective in Agile) is an essential tool to continuously improve the process and results and take learnings into next projects.

Including a financial analysis of the completed project should be a key part of the review. Try making notes each time you update actuals or adjust forecast – why was the budget to date in a certain period over? Why have you adjusted effort for one or another resource? Studying this log alongside the numbers enables you to fully understand how to improve your estimation and ongoing management of the tasks on future projects.

To sum up

Project budget management can stop being tedious as long as you have the right tools and workflow. Many of us avoid the chore of reconciling the actuals and refining the forecast on a regular basis . However, making project financial management into a habit allows you have all the up to date data to make informed decision and drive value. After all, delivering value is what projects are about.

Filed under Project management

alt=

Andrei Bernovski

Andrei is a project management professional working with some of the world’s most renowned digital agencies and software development consultancies.

Comments

Brought to you by Price&Cost

Project estimation and budget-tracking tool

 

We are almost there

We will let you in soon!
Sign up for early access and we'll place you in the waitlist.