In this article I’ll show you how to create project budget.
What values go into it.
And how they are calculated.
Bonus: Download my budgeting template for Excel here.
What you’ll learn here
- What goes into a budget?
- The types of cost explained (and how they are calculated)
- How to create a budget plan in Excel
- How to check actuals vs. plan every month
- Bonus: Download my project budget template for Excel
Two points you should keep in mind
1. Creating a budget plan is a collaborative task
A project budget is something you create in collaboration with other stakeholders. This includes departments that contribute resources (people), which procure material and services for you and which approve your project.
One of the most important teams to reconcile with is internal and external accounting. The guys from the finance department have to give you green lights for your project, as you cannot spend more money than your company can afford.
2. Consider the reporting requirements
We don’t create a budget just for planning purposes. Later, we also want to use our plan to see if we’re still within budget and for comparing actual against planned cost (usually this is done at the end of every month). When creating a budget plan, make sure to ask yourself “What reports do I need to generate later?”. Is it plan/actuals by month? By department? By project phase? This has implications on how you design the Excel template.
What goes into a budget?
Here are the cost items that usually go into a budget:
- labor cost
- cost for goods and services
- travel cost
- investment cost
- other stuff
Next, I’ll give you some more background about each type of cost. Moreover, I’ll show you how to calculate the cost for each type.
Cost categories and how they are calculated
This is the cost of the time that people spend working on the project. We have to distinguish internal labor cost and cost for hiring external staff, such as consultants or software developers.
Internal labor does not directly create costs in the sense of a money outflow. This is because employees receive a fixed monthly salary and are not paid by the hour. Still, many companies require that you also budget and track internal effort, as it’s a good measure for the quality of a project.
How do you calculate the cost for internal labor? And what rate should you use?
For this, speak to either HR or internal accounting or the controlling team. They should be able to tell you the hourly rates for each department. The higher the job grade of the people, the higher the rate.
For staff you plan to hire on the external market, you should get a quote from the service provide which shows the daily (or hourly) rate.
How about taxes? Should they be included in the budget calculation? I would not include any taxes in the rate. That’s because input taxes are a “transitory” item, and companies can offset tax paid for purchasing against tax collected by selling their own goods and services.
To calculate labour cost, simply multiply planned effort by the daily (or hourly) rate.
COST OF GOODS AND SERVICES
Next, let’s have a look at project-related expenses for products and services.
Does your project require any goods or particular services to be able to reach the target? Then you need to include those in the budget plan.
Examples for goods:
- material required for prototyping (e.g. in engineering)
- low-value items (e.g. office supplies)
- licensing cost for software
Examples for services:
- cost for renting office space
- hiring an attorney
Everything related to business-related travel goes into this bucket:
- flight / train ticket
- reimbursement for mileage
- visa cost
- charges from travel agencies
Depending on the project setup, travel cost can be a huge item on the project budget. What you should consider: ticket prices can vary significantly depending on the time of booking. Therefore it is important to make realistic estimations.
Now we are getting to a very interesting category: The cost of investments, or as they are called in finance: assets.
What is an investment? There are different ways of how to define what is an investment. Generally speaking, an investment is something we purchase in order to receive regular returns in the long run (usually in the form of money). You may think of financial investments such as buying stocks or of physical investments, like the acquisition of new machinery for production.
Why are we treating these items differently in a project budget? We could also put it in the goods and services category, right?
The difference between goods and investments (assets)
The reason is that from an accounting perspective, low-value goods and raw material are treated differently than high-value purchases like equipment for production.
How investments are treated in the project budget
When a company buys new machinery, the cost of the purchase is not posted into the month of the purchase, but it is distributed over the lifetime of the machinery. As an accountant would say, the total value is amortized over the usage time of the machine, and what we have to account for in our budget plan is only the monthly depreciation value.
Examples of assets:
- computers and IT hardware
I hope I haven’t lost you with my explanation so far 🙂 . I just want to help you understand how to set up a project budget plan.
Now let’s get to the point you’ve been desperately been waiting for: creating the actual budget plan in Excel.
Creating a project budget in Excel
Every project has different requirements when it comes to budget planning. That’s why I suggest you create a template specifically for your needs. What I’m going to show you here is how you create a general-purpose budget plan that you can later tweak to fit your requirements.
Step 1: Estimate labor effort
How many days or hours per month do you require each of your team members? Enter the estimated values in the respective columns of the budgeting template.
Whether you plan on hourly or daily basis doesn’t matter, as long as you are consistent throughout the template.
Step 2: Determine daily rates
Effort numbers alone don’t make a budget. What we want are the cost. To get there we must decide which rate to use for valuing the effort.
- What are the cost for an internal team member? (per hour/per day)
- What are the rates for external staff?
Once you have all rates, enter them in the rates tab:
What the above screenshot tells us: A day’s effort of an accounting specialist is worth $ 800. For someone from sales it would be $ 700 per day, and the daily rate for a maintenance specialist is $ 600. Note that for internal team members, these figures are just imputed or “fictitious” values. They are not an expense with money outflow. Only for externally hired resources there’s an actual cash outflow involved.
Step 3: Estimate other cost items: travel, investments etc.
Next, we want to include other cost items in our budget planning sheet.
Travel cost: Estimate the cost for project-related travel:
- In what month will people have to travel?
- How many people will be traveling? And for how long?
- What’s the cost of flight tickets and accommodation?
These are all the points you should clarify.
The template I’m giving you here also allows you to calculate travel cost based on predefined rates. This makes your life much easier for projects where there’s more traveling required.
In the example below we’ve estimated the cost for a return flight from St. Louis to Seattle at $ 400, and our hotel in Chicago charges $ 90 per night.
The Excel template uses those rates to calculate the travel cost budget.
Investments: For our project case study, we have to buy 10 laser printers. Each printer costs $ 1’000. How do we factor in the cost for the printers? We don’t include the total purchase value ($ 10’000). Why? The printers are assets which are written off (or amortized) over the usage lifetime instead of being booked as a one-time expense. The usage time of a printer is 3 years, which is 36 months.
Total purchase value $ 10’000 divided by 36 (the number of months the printers are going to be in use) equals roughly $ 277. This is the cost we incur on a per-month basis.
Other cost: Any other cost items that don’t really fit into the other categories can be put here. The logic is the same: you enter the cost value in the month the cost are incurred.
Step 4: Let the template calculate the total planned budget
Once you have completed the steps before, the budgeting template will automatically add up the numbers for you and calculate the total budget. You can also see a breakdown per year and per cost category.
Video: How to use the template correctly
Here’s a video that explains how to use the budgeting template (download below).
Make sure you change the resolution to 480p.
Comparing planned vs. actual cost
You can also track actual cost in the budgeting template. All you have to do is enter the actual values as soon as they are known.
Personally, I like to block a few hours at the end of each month where I review receipts and actual effort bookings from the HR system.
Actual figures must be entered in the columns titled Actual, as you probably guessed.
As a project manager we regularly have to report the status of our project. This includes a status of the project cost: Are we still within budget? Or are there any areas where we are burning money fast?
In the Reporting tab, you can either use the existing high-level cost summary (see image below) or create your own reports using standard Excel functions.
The report shown above is a summary of all plan and actual values, making a plan/actual comparison very easy.
To make the numbers easier to grasp, you can create charts based on the aggregated cost information.
Download the project budget template here