Chapter 6 -Monthly payments

what are the differences between our sheet Monthly payments and the earned value from Primavera? Actually, there are two main differences, and accordingly reasons why we create such a sheet.

1- Net value of payments: we are looking here at the net cash including the advanced payment -if any-, retention deduction, and advanced payment recovery as well. Therefore, we consider in our data model these values and allowed for terms to be flexible as per project contract conditions

We need to reach this result. So let's see how we can do it.

1- Payments Timing: In order to have accurate cash in, you need to define when each payment will be credited to your account. You have advanced payment, monthly payments, as the release of retention payment. Therefore, we considered all these conditions in our data model.

So, Let us prepare our sheets!

Start with importing these tables. I am sure now you can do it without my instructions; however if you still don't know how to do it, see the next video.

Now, merge cost estimation with monthly costs.

Link the two tables with reference number.

Remove unnecessary columns.

Rename column to work date.

Add new column to calculate monthly gross amount.

Now remove the unnecessary columns.

This is the final result after we load the excel table. Now we need to create

some formulas in excel

We have imported the project information sheet which has the information about the advanced payment and retention. So we will use Vlockup in the next columns.

Payment date = work date + days in payment paid after in the project information column.

Date id is a Vlockup formula using an approximate match.

Advanced recovery is as defined in project information sheet.

Retention is as defined in the project information sheet.

Now we can calculate the net monthly payment using this simple formula.

In this sheet, we will add rows for advanced payments and retention release payments.

Then we will add this new table to our data model.

Now we can append the above two tables to have one table that includes all the

information we need regarding the payments.