Project Management: How to Compile a Project Budget in Excel

How to Compile a Project Budget. Key Steps for Success

A well-prepared project budget is essential for successful project management.

It converts plans into a clear financial roadmap, ensuring that resources, timelines, and risks are properly considered.

This lecture explains the key steps in compiling a project budget.

It covers how to estimate costs for labour, materials, equipment, and services, and how to include contingency funds for unexpected issues.

It also introduces practical methods for linking the budget to the Work Breakdown Structure.

By the end, you will be able to develop a reliable budget.

The budgeting process is divided into six parts, in which we will explain step by step.

The first part is setting SMART Objectives, commonly known as S.M.A.R.T, to clearly define project goals.

The second part is defining the project scope.

The third part is to create a Work Breakdown Structure, commonly known as WBS.

The fourth part is making up a list of required resources.

The fifth part is to estimate amounts using multiple methods.

And then the sixth, and the last one, is setting aside a contingency fund.

We will use the Northfield Pedestrian Overpass project as our example, targeting to build a bridge for pedestrians over a busy highway.

S.M.A.R.T. is a method to clearly define project goals.

SMART means Specific, Measurable, Achievable, Relevant, and Time-bound.

This is critical because a budget is not just a set of numbers, it is a financial representation of clearly defined work.

If objectives are unclear, the budget will be inaccurate and difficult to manage.

For the Northfield Pedestrian Overpass project, the SMART objectives are set by the City Project Manager, and stakeholders through brainstorming, and expert consultation.

Defining the project scope.

A clear project scope explains exactly what will be done.

What will not be done.

And how the project will deliver value to the business.

It helps make sure the work supports the main business goals and the stakeholder’s expectations.

If the scope is not clearly defined, the project can quickly go off track, costs can increase, and deadlines can be missed.

First, the project scope defines the work the project team will carry out.

This includes geotechnical surveys, architectural and engineering design, and the construction of the bridge.

It also covers the installation of safety lighting.

Closed Circuit TV systems.

And landscaping after completion.

These activities form the core budget items for the project.

This is the approved project scope agreed upon by the Northfield Municipality, and key stakeholders.

With the scope firmly defined, we can now move on to the third part of our budget.

Work Breakdown Structure breaks a large project scope into smaller, manageable tasks and sub-tasks.

The Work Breakdown Structure for our overpass bridge consists of four high-level sections.

1. First, Project management.

2. Second, Design and Pre-Construction.

3. Third, Construction activity.

4. And last one, the project Closeout.

A Work Breakdown Structure ensures that all tasks, big and small, are included, helping to avoid missed costs.

The Work Breakdown Structure is developed by the City Project Manager in collaboration with stakeholders.

Walking Through the Northfield Pedestrian Overpass Project Work Breakdown Structure.

Let’s now go through the Work Breakdown Structure in detail.

It is essential because it translates the approved scope into executable work.

A well-developed Work Breakdown Structure supports accurate budgeting, realistic scheduling, effective resource allocation, and performance tracking.

The Northfield Pedestrian Overpass Work Breakdown Structure is structured like a family tree of the project.

It uses a numbering system where high-level phases, such as one point zero, two point zero, three point zero, and so on, represent major deliverables.

These high level deliverables are then broken down into detailed sub-levels, such as one point one, one point two, one point three and so on.

Each sub-level can be directly linked to costs, labour, and resources, supporting accurate budgeting and scheduling.

Listing Required Resources.

This section lists required resources.

This part of the budgeting process identifies all resources needed to complete the tasks defined in the Work Breakdown Structure.

This ensures that all associated costs, such as salaries.

Equipment rentals.

Materials.

Permits.

And professional fees, are fully captured in the budget, this reduce the risk of omissions.

The resource list includes.

City Project Manager to manage the project.

Permits and regulatory compliance costs.

Geotechnical engineering services.

Foundation construction resources, such as pile drivers.

Excavators.

Mixers.

Cranes.

Structure installation resources such as pre-fabricated steel spans.

Crane operators.

And ironworkers.

Lighting and Closed Circuit TV installation materials and services such as.

Fixtures.

Conduit.

Wiring

And cameras.

The list of required resources acts as a checklist to ensure nothing is missed.

With that checklist in hand, let’s move on to our fifth part of the budget.

Cost Estimating.

What is Cost Estimating?

Cost estimating is a key control function in project management.

It converts the Work Breakdown Structure into financial values by assigning realistic costs to all activities, resources, and deliverables.

Since project success depends on cost, time, and scope, accurate estimating is essential.

Reliable estimates are developed using multiple methods such as expert judgment, historical data, unit rates, supplier quotations, and bottom-up calculations.

Combining these approaches improves accuracy and reduces uncertainty.

For the Northfield Pedestrian Overpass project, estimated costs are.

City Project Manager budgeted for eighteen months. Forty five thousand dollars. This is based on municipality internal costing.

Design and Pre-Construction. One hundred and forty five thousand dollars, this is based on vendor quotes.

Construction. One million two hundred thousand dollars, this is based on historical contractor bids data.

Contingency is one hundred and eighty thousand dollars, which is 15% of one million two hundred thousand dollars.

The total estimated cost is one million two hundred and forty five thousand dollars.

However, this amount reveals a key issue.

The estimate exceeds the municipality’s budget allocation of one million two hundred thousand dollars, resulting in an overrun of forty-five thousand dollars.

This indicates the need for corrective action such as value engineering, cost reduction strategies, or requesting additional funding.

As a project manager, you will need to resolve this, or alternatively, you may wait for a further lecture on how to request additional funding.

Now, let’s turn to our last part, part six of our budget.

Contingency Fund

A contingency fund is a planned financial reserve used to manage known risks that cannot be precisely predicted.

It is not extra spending money, but a controlled allowance to protect the project’s cost and schedule.

In construction projects, common risks include weather delays, ground conditions, design changes, and minor scope adjustments.

Setting aside a contingency helps the project absorb these disruptions without exceeding the budget.

A typical benchmark is around 15% of the total project cost.

Let’s consider the contingency fund for the Northfield Pedestrian Overpass project.

Our construction cost is one million two hundred thousand dollars.

Contingency is one hundred and eighty thousand dollars, which is 15% of the allocated construction budget of one million two hundred thousand dollars.

This means one hundred and eighty thousand dollars is allocated to protect the main construction budget from unforeseen costs.

This completes the full project budget process, covering all six stages of budgeting with practical application.

Consistent practice using the templates will strengthen your understanding and improve budgeting accuracy over time.

Cost Control System

Budget Performance and Control.

Let's begin by exploring project budget cost performance control.

We will be using a real-life project as our case study, along with actual figures, to calculate all our performance indicators. Brush up on your calculation skills. Pause or restart the video as you wish.

Cost control is a continuous process of monitoring expenses against allocated budgets to ensure that financial objectives are achieved.

It requires tracking expenditures, identifying variances, and applying corrective actions to prevent overspending.

Project managers play a central role in this process, while finance departments handle cost accounting, and compliance reporting.

We will consider several performance indicators as part of cost control and then explore others in greater depth under Earned Value Management (EVM) second lecture. Let’s explore the performance indicators we will examine.

In the case of the Northfield Pedestrian Overpass, the project is six months into an 18-month schedule and has just completed the Design and Pre-Construction phase.

With construction about to begin, this is a critical point for evaluating financial health before major costs are incurred. It is important to recognize that cost monitoring reports play a vital role in this oversight.

They capture actual expenditures and allow for early detection of variances.

The Expense Report records all costs incurred and reconciles invoices and payments against budget items.

The Burn Rate Report is a key component of cost control.

Burn rate measures how quickly the project budget is being spent relative to the elapsed timeline.

Six months into the 18-month Northfield Pedestrian Overpass project, this report provides insight into whether spending is on track, ahead, or behind schedule.

The first phase, Design and Pre-Construction, has been completed.

Planned value

Planned value is the total budget to be spent on an activity over a given period. Our planned value (budget) for the six-month Design and Pre-Construction phase is $195,000.

Earned Value

Earned value is the percentage of work completed during a given period.

It is calculated by multiplying the percentage of work completed by the planned value.

Cost Performance Index (CPI).

The Cost Performance Index is used to estimate the projected cost of completing the project.

A CPI value of less than 1 (or less than 100%) means the project is over budget. If the CPI is greater than 1 (or more than 100%), the project is under budget.

Scheduled Performance Index.

The Schedule Performance Index is used to estimate the projected time to complete the project.

An SPI of 1 means the project is on schedule; greater than 1 means the project is ahead of schedule; and less than 1 means the project is behind schedule.

Earned Value Management.

Earned Value Management is a project management technique that focuses on schedule and cost performance, providing early warning of performance problems while also delivering useful progress updates to stakeholders.

EVM answers two fundamental questions:

Are we getting the value we planned for the money spent? (Cost performance).

Are we completing work at the rate we planned? (Schedule performance).

Action Plan for the Project Manager.

Let's now synthesize the data into an action plan.

At the six-month mark of the Northfield Pedestrian Overpass project, the Design and Pre-Construction phase is complete on time as planned.

The planned budget for this phase was one hundred and ninety five thousand dollars with actual costs totalling one hundred and eighty three thousand dollars.

To recap the key metrics.

The Expense Report shows one hundred and eighty three thousand dollars spent of a one hundred and ninety five thousand dollars budget, leaving twelve thousand dollars under budget.

The Variance Report highlights a ten thousand dollars overrun on Architecture and Engineering Design.

The Burn Rate is 0.34, which is favourable.

The Cost Performance Index is 1.07 0r 107%, meaning the project spent less than budgeted.

Estimate at Completion is projected at approximately one million four hundred and seventy one thousand dollars, suggesting a potential of one hundred and three thousand dollars under budget.

Retention Cost in Project Management.

What is Retention?

Retainage—also referred to as retention or holdback—is a common practice in the construction industry where a portion of payment, typically 5–10%, is withheld until a predefined milestone has been achieved on a construction project.

Retainage has a long history in the industry and can apply to both general contractors, and subcontractors.

The Purpose of Retainage.

When properly applied, retainage is intended to address several issues inherent to construction projects.

Quality Assurance.

Retainage incentivizes general contractors, along with any subcontractors, to complete work to the satisfaction of the owner.

It encourages contractors to meet, or exceed project quality, and performance standards.

Defect Correction.

By withholding part of the payment, retention provides the owner, or general contractor with financial leverage.

This ensures that contractors correct defects, deficiencies, or incomplete work.

Project Completion.

Retainage motivates contractors to complete their work within the agreed-upon schedule, reducing the risk of abandoned, or unfinished projects.

Benefits of Retainage.

Retainage provides several advantages for both owners and contractors:

Risk Mitigation.

It helps reduce financial risks associated with contractor or subcontractor defaults, delays, or defective work.

Quality Control.

Retainage encourages contractors to maintain high-quality work throughout the project, since payment release is contingent on meeting agreed standards.

Congratulations on successfully completing the Project Management.

Project Budgeting and Cost Estimation Using Excel course.

Thank you for your commitment, participation, and effort throughout this learning journey.

By completing this course, you have gained practical skills in defining SMART project objectives, developing project scope, creating a Work Breakdown Structure (WBS), identifying resources, estimating costs, calculating contingency funds.

And compiling professional project budgets in Excel.

These skills will help you plan projects more effectively, improve cost control, support informed decision-making, and contribute to successful project delivery in the workplace.

Learning is a continuous journey, and I encourage you to apply the knowledge and techniques you have gained in this course to real-world projects whenever possible.

The more you practice, the more confident and proficient you will become.

Thank you once again for choosing this course.

I hope you found it valuable and rewarding. If you enjoyed this course, I invite you to explore my other courses, where you can continue developing your project management, engineering, and professional skills.

I wish you every success in your career and future projects, and I look forward to supporting your continued learning journey.

Best wishes.

Your Instructor