Project Gross Margin

[dufani1]

Gross Margin dose not work with Sales Order, the Sales Order ( like the contract with customer) and has the total of project quoted amount, It should when make any payment through the sales order, this payment should be updated as income for this project not cost.
Please let us improve the cost tracking of projects and I am available for any help.

[superlack]

Admittedly I did this, but didn't follow through with the rest of it. Margin should also reflect employee salaries based chargeout, but I think it's currently set up that it would be based off of activity per employee. An overhaul using the employees salary at time of time log instead of activity cost per employee is a better scenario

[dufani1]

Hi superlack
lets enplane the following example:
Ex :
Project X with Sales Order (contract of project) = 120000 $
Duration of project= 90 days
customer is Beta Company
project start date 1-June-2017
Until now, we gained 60000 $ from customer Beta by submitting partial sales invoice against the Sales order and gained 5000 $ from customer by submitting Sales Invoice against Timesheets.
Until now, we spent 25000 $ against Purchase Invoices and 10000 $ against Expenses Claims and 3000 $ against Timesheets.
So the Project Gross Margin now is (60000+5000)-(25000+10000+3000)=27000 $ (Profit of project X)
It hope clear

[superlack]

Absolutely it's clear.

What I had intended with having the Sales Orders in the project document as a way to view budgets. In example: Deliver estimate or quotation, client accepts, becomes Sales Order which is the "Budget" for the job. Any change orders associated are added (also through the use of Sales Order), and manager is able to keep track of labour, purchase, and expense cost through the project module. The margin field should definitely take into account a lot more factors than it currently does.

For your proposal:

The calculation of $5000 - $3000 through timesheets. I believe that should reflect different pay rates of employees (in HR/Salary module). Right now it is handled as "Activity Cost", and drilling down "Activity Cost per Employee". I think it's more common to have margins handled as a direct relation between employee cost and employee chargeout rate. If there are different rates for activities, it should be formulaic, as the salary slip is.

There are a lot of discussions recently on bolstering the Project module, especially to handle different billing formats, and tracking budgeting and profit margins.

Quoted projects and Time and Material, or "Cost Plus" jobs are a big one, all the way down to the invoice.

Feel free to add to the discussions on these two topics for project management improvements:

#9431

https://discuss.erpnext.com/t/foray-into-other-saas-options-some-return-thoughts/25166

[chdecultot]

Hi,

As discussed in our last community meeting, here is my proposal to provide a quick solution to this issue and to issue #5393.

I propose to add a report that calculates the margin per project. This report could be accessible from the project document directly.
As per the requirements in this issue and #5393, here is an initial report proposal:

As you can see, I propose a three columns scenario, with the Actual, the Budget (based on Sales Orders), and the estimate (based on the estimate field in the project).

For the cost, I propose to deduct actual purchases, actual costing based on timesheets and activity cost, and actual expense claims.
Another scenario could be to deduct the purchase cost based on purchase orders in the budget column for better data consistency. Let me know your thoughts.

Regarding activity cost vs employee salary: Not everyone is using ERPNext to calculate the salary so it should be independent IMHO. Maybe we could have the possibility to calculate the cost based on one or the other.
Anyway we need to rethink the activity cost doctype as it is very difficult to maintain rates for all activity types and all employees currently.
But I guess this is a different topic that we can address after solving this one.

Please let me know your thoughts, so I can start working on a prototype. Thank you.

[dufani1]

Hi @chdecultot first thanks for your efforts and I think this is a good proposal.
Regarding budget and using P.O, I think it is better if we using Purchase Invoice because not always P.O required for small purchases in projects.

[chdecultot]

In the case there is no PO, then I guess there can be no detailed budgeting done.

In this case, the only column of interest for the user will be the first one ("Actual"). Repeating it in a second column called "Budget" doesn't bring any additional value IMHO. They would be identical.

[dufani1]

@chdecultot here my notes:
1- I think we should change Budget to Earned (as Budget term mainly used for cost centers).
2- I think the earning or budget should be calculate based on sales invoices submission as most of projects has one sales order (project contract) and many partial sales invoices. For example: I won project with S.O 100000 $ and finished so far 25% of project and according to payment terms now I shall submit to client a partial invoice with amount 25000 $.
3- Regarding the P.O the same thing above if I made P.O for subcontractor with amount 60000 $ for the above mentioned project, now should I pay to subcontractor 25 % of 60000$ by using partial Purchase invoice against P.O.
4- Important note Estimation is dynamic value and changed with time and according to budget project plan and this value shall come from cost center on monthly base or other time bases to compared with actual costs and earning.

[chdecultot]

Hi @dufani1,

Thanks for your comments!

Here are my comments:

1. Sure we can take another term than Budget if it doesn't fit. I'm not in favor of Earned as it may lead to some confusion with Actual... Maybe we can think about a more obvious term.

2. I understand your point, but in my opinion, it is something different from the goal of this report. This report is only about profitability analysis and budgeting analysis.
   The decision to submit an invoice based on the percentage of completion of the project should be linked to payment terms.

Here the point is to look at the actual margin (what is my current real margin) and the budgeted margin (what will my margin be according to the orders I have received and made).

We can also have another specific report to highlight sales and purchasing invoices status based on payment terms. Let's start working on a blueprint after this one is delivered.

3. Same answer as above: It can maybe be done in another report, as it not the purpose of this report.
   POs here could be used only to have a basic budget margin.

4.I agree, we could think about the dynamic generation of the Estimated Costs based on the budget. Can you please elaborate on how you see the flow and the calculation ?

Thanks!

[dufani1]

Dear Charles,
I am a little bit confused, how can you measure the real time profitability with this report? Please explain more.
And regarding the term you can call Actual Costs and Earned Value.

[chdecultot]

Hi @dufani1,

Sorry if I was not clear in my mockup. This proposal is for a simple report that will fetch all documents linked to a project between two dates based on the transaction date for example.

The actual profitability of a project (first column on the mockup) can be measured like that (don't hesitate to tell me if I'm wrong):

Revenue = Total Sales Invoices linked to the project
Cost = Total Purchase Invoices linked to the project + Total Timesheet Cost linked to the project + Total Expense Claims cost linked to the project

So Gross Margin = Revenue - Cost

For a given timeline, we can therefore consider:

Gross Margin (01/01-31/03) = Revenue (01/01-31/03) - Cost (01/01-31/03)

We proceed similarly for the "Budget" and "Estimate".

--

The point behind this report is to be able to compare three things:

1. The actual margin of the project = The reality (Are we profitable yet ?)
2. The planned margin of the project = The budget (Are we going to be profitable or not ?)
3. The actual revenue vs the estimated costs = The estimate (How do we compare our costs and margin vs our estimate)

If my understanding is not wrong (and please correct me if it is the case), what you want to see comes at a previous step, because you want to know if you have reach milestones triggering invoicing or purchasing.
It is also a crucial information, that will make the above report accurate or not, but it should be handled separately unless you have a nice design in mind to display everything on the same report! I just don't see how everything could fit together, but of course any concrete proposition is welcome.

[dufani1]

Hi @chdecultot
Thanks for your reply.
I completely agree with you regarding Revenue and actual costs, but about "Budget" and "Estimate" I did not got your point. Please now let's unite the terms as following:
1- Revenue or earned value is the income of project and equal of total Sales Invoices linked to the project.
2- Actual Costs are total Purchase Invoices linked to the project + Total Timesheet Cost linked to the project + Total Expense Claims cost linked to the project
3- Planned Value or approved budget for activities scheduled (tasks) to be performed during a given period.
Please check the following two links for more info about EVM and what I am trying to explain:
https://en.wikipedia.org/wiki/Earned_value_management
https://edward-designer.com/web/pmp-earned-value-questions-explanined/

My proposal to handle Planned Value in ERPNext and compare this planed value with Actual Costs:
1- Add new currency field in task doctype called Planned Value (Approved Estimations).
2- When Status of Task is Closed the Planned Value is calculated.
3- Add new currency field or edit the same (Estimated Cost field) in Project doctype, this field working or equal to the following equation:
Estimated Cost= Planned Value for closed task no1 + Planned Value for closed task no2 + Planned Value for closed task no 3 ...... etc
Example:
If we have XYZ project with:
Sales Order= 100,000 $
Planned time to complete the XYZ project= 4 months
XYZ project consists of 4 Tasks each task estimated or planned value with time frame as the following:
Task1 panned value is 15,000 $ after 1 month of execution
Task2 panned value is 25,000 $ after 2 month of execution
Task3 panned value is 20,000 $ after 3 month of execution
Task4 panned value is 20,000 $ after 4 month of execution
Total cost estimation is 80,000 $

Now real Report of XYZ project after 1 month of execution:
We closed tasks 1 and 2 this is better than the plan and we submitted to client a sales invoice with amount 50,000 $ (task 1 and task 2).
And we have 2 purchase invoices (10,000 $ and 25,000 $) .
Then the Actual Cost is 35,000 $ and Planned Value is 40,000 $ and Earned Value is 50,000 %.
Now the analysis of data:
Are we ahead or behind schedule? Earned value - Planned value= 50,000 - 40,000 = 10,000 $ we are ahead of schedule
Are we under or over our budget? Earned value - Actual Cost= 50,000 - 35,000= 15,000 $ so we are under budget.

I sent to you an email with project report template.
If we done this proposal I think project module will be better and can handle project management effectively.
Thanks,

[chdecultot]

Hi @dufani1,

Thanks for your detailed document.

I understand now that we were not talking about the same thing exactly. Your approach is to develop a report with the earned value management methodology, whereas my proposal is a simple Gross Margin calculator (more like this) because it is the initial request of this issue and #5393.

I really like your approach and I think it would be a great addition to ERPNext, so here is my proposal:

1. Make a simple margin calculator to solve the pain explained in this issue quickly.
2. Enhance the cost calculation per task
3. Create a detailed Earned Value report

How does it sound ?

--

Based on your return I have simplified the gross margin report and here is a new simplified proposal:

Instead of having a dedicated report for the gross margin, why not just add a graph in the dashboard that show the burn rate and the margin (similarly to the link posted above).
This way, we could see the evolution of the gross margin and the burn rate against the budget:

We would calculate each value with the following rules:

For past date until current date:

• Revenue = Total Sales Invoices linked to the project

• Cost = Total Purchase Invoices linked to the project + Total Timesheet Cost linked to the project + Total Expense Claims cost linked to the project

• Budget = Sum of all sales orders or manual entry in the project

• Gross Margin (in tooltip) = Revenue - Cost

From currrent date till expected project end date:

• Expected Revenue = Actual revenue + total sales orders not invoiced for this project

• Expected Cost = Actual Cost + total purchase orders not invoiced for this project (If you have a way to predict timesheet cost + expense cost don't hesitate!)

• Gross Margin (in tooltip) = Expected Revenue - Expected Cost

The idea is to provide a tooltip on the graph, so the user can check all values at any point in time.

The only issue with this approach would be the permissions... I think we would need a way to assign permission to this graph as it should be restricted to some users only. But that's more a technical problem.

Would this approach be better ?

I will also start working on a proposal for the task cost calculation enhancement and the earned value report.

Thanks!

[dufani1]

Dear @chdecultot first thanks for your email and I appreciate your efforts to make ERPNext better.
I think if we start to work with Earned Value Management technique is better and easy way to reach our goals and this is an international technique.
Following international technique unites our efforts and helps us to get more contributors and makes documentation easier.
The earned value technique easy to implements on ERPNext. Just do the following and then we can get any type of report and any info about project:
1- Add new currency field in task doctype called Planned Value (Approved Estimations).
2- When Status of Task is Closed the Planned Value is calculated.
3- Add new currency field or edit the same (Estimated Cost field) in Project doctype, this field working or equal to the following equation:
Estimated Cost= Planned Value for closed task no1 + Planned Value for closed task no2 + Planned Value for closed task no 3 ...... etc
And considering the following:
1- Earned value (EV) is the Revenue of project and equal of total Sales Invoices linked to the project.
2- Actual Costs (AC) are total Purchase Invoices linked to the project + Total Timesheet Cost linked to the project + Total Expense Claims cost linked to the project
3- Planned Value (PV) or approved budget for activities scheduled (tasks) to be performed during a given period.
Please explain the following more:
Budget = Sum of all sales orders or manual entry in the project???

Thanks and please if you have any other ideas do not hesitate to contact me.

[dufani1]

Dear @chdecultot first thanks for your email and I appreciate your efforts to make ERPNext better.
I think if we start to work with Earned Value Management technique is better and easy way to reach our goals and this is an international technique.
Following international technique unites our efforts and helps us to get more contributors and makes documentation easier.
The earned value technique easy to implements on ERPNext. Just do the following and then we can get any type of report and any info about project:
1- Add new currency field in task doctype called Planned Value (Approved Estimations).
2- When Status of Task is Closed the Planned Value is calculated.
3- Add new currency field or edit the same (Estimated Cost field) in Project doctype, this field working or equal to the following equation:
Estimated Cost= Planned Value for closed task no1 + Planned Value for closed task no2 + Planned Value for closed task no 3 ...... etc
And considering the following:
1- Earned value (EV) is the Revenue of project and equal of total Sales Invoices linked to the project.
2- Actual Costs (AC) are total Purchase Invoices linked to the project + Total Timesheet Cost linked to the project + Total Expense Claims cost linked to the project
3- Planned Value (PV) or approved budget for activities scheduled (tasks) to be performed during a given period.
Please explain the following more:
Budget = Sum of all sales orders or manual entry in the project???

Thanks and please if you have any other ideas do not hesitate to contact me.

[chdecultot]

Hi @dufani1,

Sure no problem to work on the Earned Value report and I fully agree that it is better to follow international standards. I have created a new Github issue (#11910) in order to not get things mixed up, since the purpose of this issue is to get a Gross Margin report.

As you are more proficient with this methodology, can you please provide a detailed functional specification (specific layout for the report in ERPNext/ detailed calculation logics and formulas for each field/...) ?
You can add it in the other ticket and I will gladly start the implementation as soon as we have all the elements.

Regarding the "Budget" question: Usually the budget for a projet is equal to the total of the sales order(s) linked to this project, as a sales order is firm commitment from a client.
In order to allow some flexibility for small companies who don't systematically enter sales orders in ERPNext, I propose to leave the possibility to enter the budget amount manually.
This is not a best practice in terms of financial management, but I think it is quite necessary given the practical usage of ERPNext.

As for this specific issue, I will try to find a solution for the permissions and work on the small chart above as soon I have one.

[chdecultot]

I have just pushed a PR to correct some of the inconsistencies spotted in the issue regarding the margin calculation: #11911

[dufani1]

Dear @chdecultot I am really happy to work with person like you, all of our discussion is to share the knowledge between us, and all of our work is to make ERPNext is better to help others around the world.
Regarding Earned Value report OK ...I will prepare detailed design document then I will share it with you to be discussed.
Regarding " Budget" how budget for a projet is equal to the total of the sales order(s)? This means that the profit / margin of project is zero!!!

Dear Charles the Budget is estimation for each task or phase of project, and estimations not related to any project real document (likes; PO, SO... etc), these estimations prepared in period between winning the project and starting the execution of projet.
Please clarify if I have misunderstood of your point of view.
Thanks again

[dufani1]

Dear @chdecultot sorry I closed the issue by mistake and reopen it again.

[dufani1]

Dear @chdecultot
Regarding the PR that you pushed #11911, why did you call the sales order with planned value?
Please be aware that the Sales Order is real document, S.O the same project contract and if you don't have S.O it means that you are not be able to start the project.
I think there is some misunderstanding, please note that the using of project model is just for won projects, and for internal projects that already decided to start. In other words the project model (with current status ) not used in planning phase. So please change or remove planned word and leave just total placed sales.
Please clarify if you have another point of view.
Regards,

[chdecultot]

Hi @dufani1,

You are entirely right, it's a silly mistake from my end with a poor choice of words.
I think our misunderstanding regarding the term estimate or planned, comes from the fact that I should have named it "total planned billing" or "total sales" as you have correctly proposed, instead of "total planned sales".

I have modified the PR by adopting the term "Total Sales Amount" as proposed. Let's keep this term for future improvements of the module.