The Money Files

I’m delighted that Carl Nixon, The Excel Expert, has offered to answer your questions on spreadsheets, and in particular, Excel.

In my experience project budgets are often done in Excel – it’s certainly the product I turn to. I use it for everything from doing the odd sum instead of loading that calculator desktop app, to building complex financial trackers.

They aren’t really that complex. I’m not an Excel Expert, unlike Carl.

So, without further ado, I’d like to introduce you to Carl in his own words. At the bottom you’ll see how to get in touch with your queries. I’ll collate the questions, feed them back to Carl and in a future article he’ll answer them for you. I’m really excited! I think we could all learn a lot.

Introducing Carl Nixon – The Excel Expert To Answer Your Excel Questions

As a freelance “Excel Expert”, I provide Excel spreadsheet services to companies big and small around the world. I have helped them to save thousands of hours and to reduce their errors to the barest minimum.

Working as a freelancer has resulted in me working on some very unusual and exciting projects. I have helped farmers understand data gathered from drones, supermarkets plan their pricing and global brands to plan billion-dollar expansions.

As well as providing freelance services, I am currently developing a number of off the shelf solutions for smaller companies.

Tell Me About Your Excel Problems

I have teamed up with Elizabeth and ProjectManagement.com to answer any of your questions on Excel spreadsheets. So, if you have any issues in the following areas get in touch:

• Problem / broken formulas
• Problem / broken macros
• Problem reports / charts
• Error prevention / capture
• Security

My aim is not to just look at practical issues of building a spreadsheet (formulas, macros etc); I can also give you advice on more general spreadsheet concepts as well. We can talk about good spreadsheet design, automation, error prevention and even data security.

So fire away with your Excel questions, no matter how big or small they are!

My Bio

Just over 6 years ago, I decided to become a freelance Excel consultant (www.excel-expert.com). My reputation for creating no-nonsense solutions soon spread and I quickly picked up clients that included Pepsico, Walmart, General Mills and many other household names.

When I manage to get away from spreadsheets my life revolves around kids. Especially my four wonderful grandsons who seem to eat up every minute of my spare time.

A real passion of mine is helping children’s charities and causes here in the UK and in Eastern Europe. I have done everything from organising film premieres to delivering aid in war zones (Kosovo).

Thanks, Carl!

Leave a comment below with your Excel queries and we’ll be in touch when the answers are up.

Every so often I’m asked questions about project budget. Here are a selection of your questions and my responses.

How can I manage my project cost when the actual budget is controlled by the functional prime in my organisation?

Marco, thanks for your question. This is a tough one. I don’t think you can be expected to have full control of your project costs when the budget is controlled by a functional manager. In a functional organisation (or even a weak matrix organisation) when you are not in control of the budget or resources, it is very difficult to manage in the way that you would in a projectised organisation where you have complete control over the resources and finances. I have worked in this way and it ended up getting quite messy with the functional manager approving additional work, additional purchases and the use of additional staff (to do what I considered business as usual tasks) and all of this resulted in the budget going over the forecast. However, he didn’t seem to mind, so in a way that’s OK!

What you can do is prepare a budget forecast and meet with your functional manager to go through it. Ask what their understanding of the scope is and whether there are additional tasks that he or she thinks should be included. Then your role is really about tracking the expenditure and reporting the progress against the forecasted budget. All you can do is flag up to your operational manager that it looks like you’ll be going over the predicted project budget. And ask for their help in correcting that, or to approve further spending.

Good luck!

Why is it important to categorise costs between Project Management Costs and Project Deliverable costs?

Diane, this is a great question. And it’s worth starting out by explaining the difference between project management costs and project deliverable costs. Project management costs are the costs of running the project. That includes things like setting up a special project office if you need one, the cost of temporary project staff, overheads, software (if you need to buy a new project management tracking system or a wiki tool etc) and so on. Project deliverable costs are the costs of the stuff needed to produce the project deliverables – what you would traditionally think of as project costs. These are things like equipment (new servers, machinery, furniture for the new store you are opening etc) and software or maintenance costs, such as the cost of purchasing new software and licences for an IT systems project.

You can categorise costs however makes sense to you, your project team and your accountants, but I find the distinction between management costs and deliverable costs very useful. It helps make it clear what the project management overhead is – the cost of doing the project at all. The deliverable costs would be incurred whoever ran the project. The management costs will be different if the project is being run in house or by an external project consultancy.

When budgeting, accounting departments want one number and not a range for their budget accounting systems? How do you choose what number in the range for the total project cost?

By a range, you mean a total project cost figure that is between $x and $y. In the early days of putting your budget together I do recommend that you use a range instead of presenting a single figure. This is because it highlights the fact that there are certain unknowns at this point and it encourages stakeholders and project sponsors to think widely about the likelihood of achieving an individual target figure.

To answer your question, I think you should work with your finance department to enable them to record a range in their systems! However, I know this isn’t going to be practical in many cases, so it is your choice which number from your range you choose. Being quite conservative, I would suggest that you choose the largest figure. That gives you a bit to play with even if you don’t ever intend to use it. And it can always be released or amended later. So, if your project budget forecast is between $700k and $900k, I would tell the finance team to use the $900k figure in their accounting systems.

Remember to keep an eye on how your actuals are shaping up so that you can reforecast and amend this appropriately later.

Last year I gave a webinar on managing project budgets, which also included the answers to many questions. You can see the whole presentation online here, via a recording of the webinar. I’ll have some more Q&A for you soon! Got any questions? Leave me a comment and I’ll answer them in a future post.

Every so often I’m asked questions about handling project finances. Here are a couple of questions from reader James about contingency budgets:

“In our construction project I put certain percentage of contingency into the budget for each individual job. This varies from one job to another in the budget, say 10% for job A, 8% for job B, 5 % for job C and etc. By adding each percentage, we can get to an overall figure, let's say $2 million. My questions are:
 
“1. When job A completed and no contingency budget for this job was used, (say we had $500k contingency set for this job), are we going to take out this $500k from total contingency budget and reduced it to $1.5
million for the rest of jobs, or should we still keep the $2 million as it is even though job A has completed successfully?
 
“2. When job A completed over budget by $600k ($500k contingency spent and $100k additional on top of the contingency budget), but at the same time there is still $200k contingency left for job B, can we offset job A's $100k by using the remaining amount in the contingency of job B, assuming job B also completed, or will not use full contingency as we predicted?”
 

Thanks, James, for getting in touch with your questions. My views on this are as follows.

If job A completed and did not use the contingency budget, you should release it. If the contingency was set aside for a specific task and that task is now completed, you no longer need it. That is the 'proper' way to do it, because contingency is linked to risk. I assume that other project tasks are no more risky now, so you don’t need to keep that contingency to offset increased risk on the project.

However, in real life if your project sponsor will allow you to keep the money and you think it will come in handy then hang on to it! You never know what is coming up later, but don’t assume that you can spend it just because you have it.
 
To answer your second question, if you have contingency per task then you shouldn't move it between one task and another. However, in real life (again) provided your sponsor is happy for you to do so then you can.
 
It sounds as if you actually have one pot of contingency money and are using it to offset changes as and when they occur, regardless of the task. This is fine: you do not have to have contingency per task, although you should have an idea about how much you need overall so that you don't spend all your contingency budget on the first task and 
then have nothing left for later tasks.

Hope that helps!

If you have a question, drop me a line and I’ll try to include it next time. Or check out the previous episodes of the FAQ (links below) or the webinar on managing project budgets (which is free to watch).

Every so often I’m asked questions about handling project finances. Here is another batch of Q&A. If your question isn’t answered here, drop me a line and I’ll try to include it next time.

Today we are focusing on tolerance.

You've recommended ranges for budgets as well as tolerances - can you recommend whether to combine these or keep them separate?

Estimates are better if they are ranges. You might estimate your budget to be £90-110k, for example. This will become a narrower and narrower estimate as the project progresses. But when you are discussing budgets at the business case stage, a range is a more appropriate response to uncertainty.

Tolerance is the amount by which the project can be delivered over (or under) budget without anyone being concerned.  It’s usually a small amount represented as a percentage. If your tolerance is a percentage, you will express it as something like +/-5% i.e. you can be up to 5% under budget or up to 5% over budget and still be delivering the project appropriately.

I would not combine tolerance and budget estimates. Set your budget. Then agree a tolerance level with your project sponsor. Apply the tolerance to the budget. You will end up with a statement like:

“The budget for Project Banana will be £90-110k with a tolerance of +/-5%.”

Is tolerance the difference between total cost and contingency?

No. Don’t think of tolerance as ‘real money’. It is simply a parameter in which you operate. It’s the same as saying, “I’ll aim to be at the cinema to meet you at 7pm but the trains aren’t running to the timetable because of engineering works so I could be there anytime between 6.45pm and 7.10pm. That should still give us plenty of time to get tickets and sit down for the film.”

Total cost is the amount (in range form if your sponsor will let you) your project will cost. Contingency is a pot of money put aside in case of unforeseen developments. It is not part of your overall budget and with any luck you won’t have to draw on it. It is for managing project risk.

What is the normal level of tolerance as a percent?

That’s a difficult question! It depends on the project. A project that has many elements that have been done before, an experienced team who have put together comprehensive and accurate estimates, with low levels of innovation and without the risk of new technology could easily have very low or no tolerance. This is because you’ll already know enough to ensure that you won’t need the ‘safety blanket’ of tolerance in delivery as you’ll be pretty sure that you will hit the estimates straight on.

The ‘right’ answer is whatever your sponsor says is appropriate. For organizational politics reasons, or financial constraints, the answer might be zero.  Talk to your sponsor about what tolerances they consider acceptable for the budget.

If you are pushing me for an answer I would say +/-5%. That gives you a window of 10%. If you are swinging outside of that window you will either be tying up money that you don’t really need for the project that could be better spent elsewhere, or you’ll need to go back and ask for more money as your project costs have risen.

Is it customary to add % of budget as ‘miscellaneous etc’ to the project budget and is this the tolerance you are talking about?

Yes, it is customary to add a budget line for unforeseen items. But no, it isn’t tolerance. This is your contingency fund.

Is it any different for PRINCE2?

No. PRINCE2 doesn't specify any particular way to manage project finances. You can read more about project tolerances in PRINCE2 here.

Last year I gave a webinar on managing project budgets, which also included the answers to many questions. You can see the whole presentation online here, via a recording of the webinar.  I’ll have some more Q&A for you soon!   Got any questions?  Leave me a comment and I’ll answer them in a future post.

Read Part 1 here
Read Part 2 here
Read Part 3 here
Read Part 4 here
Read Part 5 here

It seems ages ago now that I gave a webinar on managing money on projects, but it is taking a long time to answer all the questions that I didn’t get round to doing during the live session.  Thanks to all the fabulous participants, who asked such brilliant questions!  I am still trawling through them hoping to answer them all, and here is today’s batch of managing money Q&A. 

Is there an industrial standard for a ball-park of the Project Management cost during estimation for all IT projects? Does it vary for Application Development projects versus Application maintenance projects? Also, does platform matter?

There is no industry standard for ball-park project management costs for IT projects.  It depends on the complexity of the project.  A small project that requires very little oversight, with competent and experienced developers and engineers, may only need a small amount of project management time.  A complex, never-been-done-before project might need project management effort at practically full time.  I don’t think the type of project, or the platform, matters much.  What matters is the skill of the project manager and the complexity of the project.  Look back at the statistics that your PMO has about percentage of project management time per developer day.  If you really, really, don’t have a clue, start with 20%, which is one day of project management time per developer week.

Should I track project costs if I have never been required to do this? And if so, where would I begin since this would be for my information only, for now, with the intent of presenting to upper management (sponsors) to show value?

If you want to do this as a learning exercise, then go ahead.  You are not required to do it, so don’t let it take up too much of your time.  Are any other project managers required to do it?  If so, why?  Maybe they work on projects of greater strategic significance or with higher external costs.  Ask your PMO why you don’t have to track costs – you might be reassured by the answer.

If you’d still like to do so, you could start with tracking your time, and then talking to the Finance department about the daily rate for resources so you can put a financial value on that.  If you are only managing a project with people resources, and not other expenditure, you can track how much effort it is taking to complete the project. If you are spending money on other things, like buying computers or hiring a contractor, you can add those in too.

Do you have publicly available SharePoint templates (e.g. budget tracker)? Or url?

No, sorry. Although this is an excellent suggestion.  I’ll see if I can sort something out. On another note, why would you want to use a SharePoint template as a budget tracker?  It’s not designed to be a spreadsheet so that is going to give you a lot more work to do.  Use a spreadsheet programme, and get it to do all the maths for you.

Salary and contractor costs are sensitive information.  How do you share the entire budget with the team without divulging this sensitive information?

You don’t.  Sharing capital expenditure – buying things – is different from sharing operating expenditure.  To be honest, not many project mangers have access to salary information for the people on their teams anyway.  That’s normally data belonging to the line manager.  You may have day rates set by the company for different types of resources, which are not the same as salary figures.  These will be publicly available.

Read Part 1 here

Read Part 2 here

Read Part 3 here

Read Part 4 here

You can see the whole presentation online here, via a recording of the webinar.  I’ll have some more Q&A for you soon!   Got any questions?  Leave me a comment and I’ll answer them in a future post.