The VAT liable resources case

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:

VAT formula in MS Project

IIf([Flag1]=Yes;[Cost]*1,025;[Cost])

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:

VAT formula's in Microsoft Project

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:

[Cost1]-[Cost]

The whole result will be:

Calculating VAT in MS Project

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.

Different cost rates for VAT in MS Project

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.

Final notes

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.

Kind regards,

Erik van Hurck

2 thoughts on “The VAT liable resources case”

  1. Hi Erik,

    Nice article! Personally I prefer option 2, which in my opinion is not the easiest one to implement, but it’s the most reliable and flexible solution for me.

    1. Hi André,

      I agree, it’s the better solution for Server/Online solutions. But the other solution is still a viable alternative, with a big upside that you have the VAT in a separate column.

      Thanks for responding, and if you have a third option I’d love to hear it.
      Erik

Comments are closed.