4 steps to a finished project cost plan in MS Excel

Even smaller projects require cost planning. This does not have to be time-consuming. A structured Excel table according to work packages can be sufficient. I offer a ready-made Excel template for download. This is included in my project management template set.

Cost planning is a must even for smaller projects - even if the effort involved should not be too great. This article shows you how to solve planning of costs in smaller projects easily. The basis is an Excel template. While the template itself is easy to use, I explain in this article how to create a meaningful cost plan step by step.

Stay "in the budget" with your project! Regularly monitor the actual project costs and follow the cost trend to be able to control the cost development in good time.

Using the procedure described below, cost planning is also easy for beginners - regardless of whether it is about the introduction of new equipment, a renovation or building alteration project, or the organization of an event. The template offers cost planners flexibility, for example through the possibility of taking a risk budget into account.

Content

Bottom-up and top-down planning

The basis for cost planning is the work breakdown structure (WBS), which - broken down into the individual work packages - provides information on needed resources (human, tools & equipment, and material and the required effort. If you calculate the costs for each work package based on the underlying effort, the result is what is known as "bottom-up" planning.

However, if a detailed WBS is not yet available at the time of cost planning, "bottom-up" planning is not possible. In this case, you proceed the other way around and divide the available budget "top-down" among the sub-projects or work packages that have not yet been planned in detail.

Another essential element for cost planning is the schedule, with which you set the cost plan in relation to the timing of the project. In this way, you have important information for later cost control.

Step 1 - initial data and structure

Before you can start to estimate the project costs, you need to collect or prepare a few things:

  • The work breakdown structure (WBS),
  • Resources (human, tools/equipment, material), and
  • Durations,
  • The strategy of whether you are planning bottom-up or top-down.

Of the steps mentioned, it is, in particular, the WBS that you have to create. Other data may be already available from previous comparable projects, or sometimes you first have to obtain offers, etc.

Occasionally I am asked whether that would not be too much of a hassle. Well, of course, that is up to you and your organization, but in principle, I would recommend you to look carefully at the costs.

Part of my template set is, among other things, a template for the WBS. Usually, you start by dividing the already known deliverables into tasks to simplify further management and planning. You can then break up the tasks into activities.

WBS

Step 2 - estimate unit rates

You can now use the structure created in the first and figure out rates. What you need are:

  • Calculations for internally provided works and services.
  • Offers for externally awarded items or, current market prices.

In the case of external work/services, you usually get either a complete price for a specific package or otherwise a unit price. As far as avoidable, do not rely on a single provider or a single source of information. Get several offers and calculate an average price. How you go about determining the average (traditional or weighted average) is up to you or your organization. The use of the formula (‘best case’ + 4 x ‘most likely’ + ‘worst case’) / 6 is recommended. As soon as you have decided on a particular supplier, you will of course refine your calculation and replace the average price with the agreed offer price.3 point cost estimate

For the estimation of internal costs, I have included a calculation in my template set. With the help of that template, you work out the detailed costs for each activity, including the cost of human resources, tools & equipment, as well as material. Occasionally I am asked whether that would not be too much of a hassle. Well, of course, that is up to you and your organization, but in principle, I would recommend you to look carefully at the costs.

I recommend that you create a resource list in advance with the respective rates. This makes it easier to calculate the costs for each activity.

Activity Cost Estimate

Step 3 - cost calculation

It is now time to put it all together. For the sake of simplicity, I have included columns in the WBS for the cost per activity. This saves you from having to transfer the whole structure to another table. It's also easier to update if necessary. All you have to do is transfer the costs that you have calculated for each activity from the activity cost estimation sheet.

Step 4 – missing parts

As mentioned at the beginning, it is recommended to also consider the risks or more specifically the costs associated with it. By the way, costs for quality control, such as testing materials or the like, are often overlooked.

Conclusion

A complete and realistic cost planning is possible for projects and can be implemented quickly with the Excel template. Good cost planning is necessary to track costs throughout the project life cycle, to react to deviations early, and to complete the project successfully and profitably.

Even this Excel solution works, I suggest you also take a look at my articles on project management software such as Oracle Primavera and Microsoft Projects.

Primavera P6

Leave a comment

Your email address will not be published. Required fields are marked *