Use my project budget template for tracking expenses.
It works with Excel and you can download it here.
Read the this article before downloading it.
Budget template features:
- plan project cost (on a monthly basis)
- track actual values
- compare plan vs. actual cost
- create budget reports
In short, the template helps you stay organized and have 100% cost transparency.
People love the template. Here’s why:
- it works for any project size – from small projects to million-dollar projects
- it’s easily customizable
- it has a smart (but clear) logic to calculate costs for labor and travel using predefined rates (more info on that later)
- it’s EASY TO USE
- I’ve used it dozens of times in my own projects
By the way, the template works great in combination with my project plan template. If you haven’t checked it out, go get it!
How the rate-based calculation works
Setting up a project budget involves a lot of calculation. Not only do you have to estimate quantities (e.g. number of days), but you also have to put a cost behind each task and each item that’s used in the project.
Without a good budget template this would mean a great deal of manual work. Luckily, I implemented a feature that will reduce the need for manual calculations to a minimum – and help you avoid errors at the same time.
Here’s the feature: You can define default rates for resources used in the project, and thereby automate a huge part of the calculation. The rate-based calculation works for labor resources and travel cost.
Examples of rates you can define:
- 1 day of work by a software developer costs $1000
- 1 day of work by your accounting team member costs $700
- 1 night in a hotel costs $80
- a return ticket for a flight from your city to Seattle costs $300
Why is the rate-based budgeting so useful?
Instead of entering dollar amounts manually all across your spreadsheet, you can simply use a predefined rate as a placeholder. And if, for instance, you want to change the price of a flight ticket from $300 to $350, all you have to do is change ONE cell value (the default rate set in the rates tab). The budget template will pick up the new rate and update all related fields automatically.
USING THE BUDGET TEMPLATE
Let’s go trough the template step-by-step. First we’ll take a look at each tab in the spreadsheet.
The rates tab: maintain rates for labor and travel here
COST RATES FOR LABOR
The template will only calculate the cost of labor once you have maintained cost rates. Make sure you enter the same department identifier as in the Labor – effort tab.
Daily rates should be defined both for internal and external resources. For internal team members these are of course “fictitious” (or imputed) values. For external services you enter the fee charged by the service provider per day.
COST RATES FOR TRAVEL
Here you can set default rates for flights, hotel stays and other travel-related expenses.
Make sure you enter the same type as in the Travel cost tab.
The labor – effort tab: enter labor effort here
First, estimate how many days per month each of your team members will work on the project. Then enter this information in the below tab of the budget template. Your planned efforts go into the Plan columns. The Actual columns is where you track the actual number of days spent.
The labor cost tab: see how much money is spent on labor
This tab is a 100% copy of the labor-effort view. The only difference is that it shows labor as cost, not days.
Cost is calculated using the cost rates you defined previously.
The travel cost tab: cost for hotels, flights go here
Each cost item must be given its own row. Hotel cost go in one row, airfare cost in another one and so on.
Enter a description in column A. For column B, use the same type that you have used in the rates tab. The rate is pulled automatically into column C using the common description as a bridge.
To calculate the cost for each item, a formula is used in columns D, F and onward. You can simply copy this formula to other rows.
The other cost tab
Any cost that is not labor, travel or investment, is tracked in the tab Other cost.
Assume you intend to buy printing paper for $2’000. Then you would enter 2’000 in the month of purchase.
The investments tab
Investments are goods that are purchased to be used in a company’s production process, where production can mean both manufacturing of physical goods or provision of services.
Examples of investment goods:
- software licenses
Investment expenditures are not booked immediately at full cost. Instead the cost is spread over the lifetime of the item.
Example: Assume you have to buy ten printers for your project. The total purchasing value is $10’000, and the printers will be used over 3 years. In this case you would NOT write $10’000 into your project budget. Instead, the total cost must be spread across 3 years (equal to 36 months) and you would record $10’000 / 36 = $277 as monthly expense (as so-called called depreciation):
For details on how to budget investment goods, talk to your accounting department.
Easy management reporting
Let’s say your manager wants an update on the project cost. Then you can immediately pull this information from the project budget template, under the Reporting tab.
PLAN VS. ACTUAL COST REPORT
Usually I just take a screenshot from the cost summary (see image below) and put it into whatever document I need it for: A management presentation or the project charter for example.
If you want, you can also create a chart using the table above as data source.
To analyze cost on a more detailed level, go into the tab of the respective cost category and compare plan vs. actual values. This can be done on monthly or yearly basis.
VIDEO: HOW TO USE THE PROJECT BUDGET TEMPLATE CORRECTLY
I absolutely recommend you watch the video before working with the budget template.