I received a question from one of the TPC newsletter members. Rob thanks for this, hope you can use this post for your question. Rob wanted to know how I would approach this situation:
With the projects I work on some costs are liable to a value added tax charge and others are not. How do I get Project 2016 to calculate VAT on those charges to which it applies while omitting it from those which do not so that I get an accurate overall cost budget.
There are two options that came to mind when thinking about the situation. And I would like to share both with you, including pro’s and con’s for both solutions.
Option 1: Custom task fields
The first option concerns two custom fields on the task level on the schedule. We will need a Flag field and a Cost field for this.
The Flag field we will name “VAT liable costs”.
The Cost field will be a calculated field that will perform a calculation based on the default Cost field and if the custom Flag field is set to Yes. We will call this field “VAT included costs”.
The formula we will use for “VAT included costs” will be:
Please be aware of formula syntax, some language versions use a , instead of a ; in the formula’s. Also, be aware of the number in the formula “1,025” means that there is a 2,5% VAT included into the Costs.
The result of this formula can be seen in this simple schedule example:
As you can see the costs of Dave haven’t changed. But the costs of Travel have increased by 62,50 euro’s.
As an added bonus you could include another custom field that subtracts Cost1 by Cost and get the actual VAT value. Make sure to role the formula up to summary if you want to get a summarized total on the Project Summary Task. We should use “Sum” role up for Cost1, because we don’t want all costs to be recalculated as VAT included costs.
The VAT value formula would be created on another Cost custom field and would look like this:
The whole result will be:
Pro’s for using custom fields
It’s very easy to create.
It gives you a nice and easy to read GANTT view.
Con’s for using custom fields
Let’s assume you have multiple resources on 1 task. Resource 1 has the VAT situation, but Resource 2 doesn’t have VAT values. You will need to create a task for each resource if you would like to use this solution. Making your schedule a lot bigger.
There is just one VAT percentage that you can include (without much more formula changes).
These are Task level formula fields. Microsoft advises us that we should not consider more than 5 Task level custom field formula’s in a Project Online / Project Server instance. This has to do with performance of loading and storing the data.
Option 2: Custom Rate on resources
Each Work resource has 5 Standard rates that can be used on assignments. These rates are labeled A, B … E. By default we only use Rate A. This is also the rate that’s visible when we have the Resource sheet view open.
But, we can create a different rate that will include the VAT costs. Let’s use rate B.
This will give me power over the exact assignment that I want to change into a VAT included cost. Changing the value’s is done on the Task usage or resource usage view and can be made visible by including the column called “Cost Rate Table”.
Pro’s for using the rates table
With this technique you can pinpoint VAT liable costs on a assignment level, compared to the previous option where it was just task level. This can be a huge improvement of detail when multiple resources are working on tasks.
This solution doesn’t use custom formula’s but the native functionality of Project. This will be the better solution if you are using Project Online/Server because it will limit the amount of calculations that will be done on.
Con’s for using the rates table
The solution doesn’t include the option to calculate the different rates. That means that you need to find a solution outside the tool to get the actual VAT value. For instance a Power BI query.
You will need to set the rate for each assignment in the Task usage or resource usage view. A view that not many will be familiar with. And there’s no default method on showing that a task contains VAT liable resources from the GANTT.
Both options can be used in Online/Server and single schedule files. This blog post was created because a subscriber to my newsletter asked me the question. Rob, I hope you like what I’ve done with the question, let’s talk some more.
If you would like to ask me a question, don’t hesitate to sign up to the TPC newsletter and mail me.
Also, if you know of more options that I didn’t tackle in this article I would welcome you to contribute to the post by adding a comment.
Erik van Hurck