Finances for PMa in the presale: how to quickly calculate the budget and Cash Flow in MS Project
Among the main questions that need to be answered by company management before deciding to take on a project, the following stand out:
Of course, you can assume that this is not your headache like RMa, but ... If your company is small, then all the financial problems of the company will affect you and your team in the most direct way, in the form of salary delays. Even if the company is standing firmly on its feet, a minus project inevitably brings an element of sadness to the team atmosphere. Therefore, it is better to take the financial part into account initially, since it takes a little time and a one-time benefit, and the profit is big and constant - in terms of project profit, and therefore your karma in the head of your leadership. Well, or the ability to recognize financially failed projects at the presale stage and to avoid participating in them, as far as possible.
Interested in - welcome to cat
Before you begin.
When writing this article, it was taken into account that the average RM, who grew up from a senior, has absolutely no desire to delve into the financial details tightly. Therefore, it is shown how to get financial calculations for a presale with minimal time and mental effort — such as getting credit for the subject “Ecology” at a technical university, ecologists forgive me.
It is assumed that you can do the schedule in MS Project yourself - enter tasks, build connections between them, organize a hierarchy, etc. What we are talking about is the quick preparation and input of financial information in MS Project in order to get a working financial model for the presale that you can play with by answering the “what-if” question with great accuracy online.
There are no financial revelations in the article, everything is standard, any version of MS Project can be used since 2003. The article used MS Project 2019, but there is not much difference in the interface. Alas, the work uses the English version, I did not take the risk and change it to Russian only for writing this article.
Let's get to the point.
Let's look at the presale of the project for developing aspherical horse in a vacuum of a piece of iron under Linux, which monitors important parameters for the customer and issues control actions based on the analysis of the data received. Development includes the development of the board, system and application software, no FPGA, the case was taken ready. Our goal is to roll out an adequate amount to the customer, taking into account the percentage of our profits and evaluate the payment schedule for the project, so as not to go into the cash gap.
What specialists are needed for this educational project:
This does not include directly the secretary, accounting, lawyers, purchasers, sellers, office rental, electricity, tea with cookies - it does not depend on you and you can not manage it. All that is in your power is to add costs to this is all, in the form of an increase in the developers' hourly rate. This is what we will do now.
Personally, I prefer in this case to simply multiply the salary by “net” by 2. Yes, that’s so simple. Since the company pays tax of 43% on hand salary, and the remaining 57% goes as overhead. These remaining 57% also include implicit details that affect the final rate: there is a vacation of 28 days, developers are sick regularly, they ask for time - we can assume that they work 10.5 months a year, etc. If the director / financier does not agree with 57%, then this is definitely not your headache - you do not have and, in principle, cannot have access to the entire accounting department of the company, so ask the director / financier to give your figure, but for now it’s not set - put 57 %
Calculation example: the applied programmer receives 82 000 rubles per hand. On average 164 working hours (including holidays), i.e. his hourly salary is 82,000 / 164 = 500 rubles. We set the rate of 1000r / hour. All.
Before continuing, it is worth describing another way of accounting for overhead costs. As you probably already noticed, the overhead costs for the office, cookies and lawyers are the same for the junior and senior, so it will not be entirely correct to calculate the invoices as a% of their salary. Therefore, you can ask the director to set the task for the financiers to calculate the average number of office expenses per hour for the period (year / quarter / month - it doesn’t matter, divide by the number of hours). Then we get the figure of, say, 300r / h of invoices separately and the hourly cost of salaries (with taxes) separately 82,000 * 1.43 / 164 = 715r / h, and this will be more accurate, we will get in the amount of 715 + 300 = 1015r / hours The only but big problem here is that it’s very difficult to get the exact amount of overhead for the period from the accounting department. I only succeeded once, so I prefer to add interest on salaries. Yes, and the project is pulled mainly by middles with more or less the same salary, so the difference between the two accounting methods is not very large. In the end, you can agree, again, to put now 57% of the salary invoices, and when (more precisely, if) the accounting department provides the invoice number per hour - quickly change several rates, it takes 5 minutes from the force, everything will be recalculated automatically.
Simply? In my opinion, yes.
We now know how to calculate rates from salaries, so it makes no sense to give calculations here for other developers, to save time. Just put down the rates right away:
Programmer - 1300r / h
Circuit designer - 800r / h
PCB breeder - 800r / h
Tester - 600r / h
Implementer - 1000 r / h
PM, i.e. you yourself - 1200r / h, while you are 30% involved in the project, the rest are other projects, presale load, etc.
With the calculation of rates completed. Here is the original schedule:
According to the schedule - I’m not going to argue about how it should be in reality. The schedule is extremely simplified, to the point of breaking with reality, and is made with the aim of showing the impact of tasks on money, rather than giving a standard template for the development of a software and hardware complex. In general, "I am an artist, I see so."
Ok, click on the “Resource Sheet” button and in the opened view we enter the received rates:
Pay attention to the line "Plant". These are articles of purchases where all money must be given immediately, or rather, a little in advance, since your bookkeeping does not transfer money instantly, but periodically, once or twice a week, plus the money will go for some time.
Therefore, here we set the type of resource for making a purchase as “Material”, set the amount 40,000 in “Cost / Use” and set the payment (Accrue At) at the start of the task. Of course, in Feng Shui, you need to divide the “Making a Board” task into two Milestones: payment and receipt, but more complicated - two lines instead of one, add a temporary offset that is not visible on the schedule, etc. So, how is done - it is more visual and more difficult to forget. You can still tint this task with orange, but this is already too much.
Now let's switch back to the Gantt chart view. To see the resulting value, add a column called “Cost” or “Cost” and this is what we get:
The project cost is now transparent with accuracy to work. The management sees exactly how his money will be spent in your project, and this distinguishes you from other RMs that do not give such information. And disputes about people in the project are much easier to translate into a constructive way: everything is transparent, if you want cheaper and faster - let's instead of middlemen for key tasks in the project, let’s recount now.
Actually, the main part is finished on this, and you can play with the resulting model in a fairly large range, getting an instantaneous value for any changes. This is good, but there is more to it than that. We are faced with the task of removing the second bunt from the leadership - to calculate the cash flow on the project in order to give an argumented answer to the question: how much to request prepayments and how to break it down by stages.
This method of calculation can be used in a variety of options, ranging from the already stated goal of calculating the required percentage of prepayment and the distribution of payments in stages, so that the cache flo does not go negative, to a very convenient way in an already ongoing project, with many outsourcers, to quickly give out the amount to the financiers external payments by stages planned for completion in future months.
Let's put in the project a profitability of 30%, which gives us 1,233,600 * 1.3 = 1,603,680. Round up to 1,600,000 so as not to get confused in a penny. On this amount, you need to calculate how much to ask in advance, and how much - at the end of the stages. It is standard for the development of software and hardware systems to have an advance of 40% at the start, since the purchase of equipment and components occurs at the first stage, and 30% at the end of the first and second stages. We get in this way:
640 thousand - prepayment
480 thousand - at the end of the first and second stages.
We put in the project Milestones (milestones), corresponding to the moments of payment of the stages. To rename, so as not to produce unnecessary lines, the task “Start the project” in “Prepayment”, and “End of the project”, respectively, in “Payment of the second stage”. And we add the milestone “Payment of the first stage” after the task “Transferring software to the target board” - it is easier to convince the customer to part with the next money at the moment of seeing the product that has started working. In the column “Resource Names” we put “Payment 1”, “Payment 2” and “Payment 3”. It should look like this:
Click on the “Resource Sheet” again and enter the received prepayment values. This time, of course, with a negative sign, since we used to count the money leaving the company’s pocket, and now vice versa. And we will change the type of resources “Payment x” from “Work” to “Materials” by setting the above amounts (with a negative sign, because we are paid, not us) in Cost / Use and setting the payment (Accrue At) at the start of the task:
Please note that only MS Project 2019 displays the value with a negative sign, in previous versions negative values ​​are enclosed in parentheses. Although everything is considered correct.
Introduced. Switch back to Gantt and see this picture:
The total project costs are expressed in negative terms and this is undoubtedly nice.
Ok, move on to the cache flo for months. Here, alas, you already have to export the data to Excel and look in it. You can, of course, rename several fields in the names of the month and year, display each month in your own column and build your own monster formula for each column, taking into account the year and month ... I did it once, but it looks disgusting, especially if the project is long. Where it is easier to use the built-in reporting tools and output them to Excel, we will go this way.
As always, everything is very simple - go to the “Reports” tab and click on the “Visual Reports Export” button:
Next, in the window that opens, select "Cash Flow Report", set the level to "Months":
Click on “View” and in the Excel window that opens, select the second tab (sheet) - “Task Usage”. We get such a window, but so far it is uninformative:
On it, put the “Monthly Calendar” checkmark in “Fields of the Pivot Table” and click on the “+” icon marked with red ovals in the figure. We are facing what we wanted: a monthly cache-flo in our project with cumulative information, i.e. We understand on a monthly basis the overall result - we are in plus or minus this month, and the dynamics - we have already reached a plus in the project for money, or not yet:
Let's interpret the data. Red ovals indicate places where you need to be careful.
In March, our total profit is negative (we do not forget to invert the sign in our minds, since we are talking about expenses), expenses exceed revenues by 67 360 rubles. For April - positive, but only at 13280r, a delay of just a day will make it negative. Therefore, you must either insist on increasing the advance up to 45%, or return from the world of numbers to the real world and understand that these amounts do not exist by themselves, they are only information for making decisions. Mentioned + 57% to the rates of engineers - an abstraction, lawyers and accountants receive their salaries due not only to this, but also to other projects. Now, if there will be a cash gap for all of them in March, then this is an occasion to insist either on increasing the advance payment on the project, or deferring your payments for this month, and if not, look for other ways to plug the hole, for example, take a loan from the bank for a couple months. But these decisions are made by the financier / director, and not you as RM. You already gave them very valuable information: in two months there is a risk of a cash gap, you have time, think. This is much better than learning about this problem in two weeks.
That's all. Good luck with your financial planning!
- Will it be profitable to carry out the project under discussion at a suggested price?
- What will the financial situation look like during the project? Will there be a cash gap?
Of course, you can assume that this is not your headache like RMa, but ... If your company is small, then all the financial problems of the company will affect you and your team in the most direct way, in the form of salary delays. Even if the company is standing firmly on its feet, a minus project inevitably brings an element of sadness to the team atmosphere. Therefore, it is better to take the financial part into account initially, since it takes a little time and a one-time benefit, and the profit is big and constant - in terms of project profit, and therefore your karma in the head of your leadership. Well, or the ability to recognize financially failed projects at the presale stage and to avoid participating in them, as far as possible.
Interested in - welcome to cat
Before you begin.
When writing this article, it was taken into account that the average RM, who grew up from a senior, has absolutely no desire to delve into the financial details tightly. Therefore, it is shown how to get financial calculations for a presale with minimal time and mental effort — such as getting credit for the subject “Ecology” at a technical university, ecologists forgive me.
It is assumed that you can do the schedule in MS Project yourself - enter tasks, build connections between them, organize a hierarchy, etc. What we are talking about is the quick preparation and input of financial information in MS Project in order to get a working financial model for the presale that you can play with by answering the “what-if” question with great accuracy online.
There are no financial revelations in the article, everything is standard, any version of MS Project can be used since 2003. The article used MS Project 2019, but there is not much difference in the interface. Alas, the work uses the English version, I did not take the risk and change it to Russian only for writing this article.
Let's get to the point.
Part one. Rates and future expenses
Let's look at the presale of the project for developing a
What specialists are needed for this educational project:
- Programmer.
- Circuit Technician.
- Scout board.
- Tester
- Implementer.
- You yourself as a PM. Your time also costs money.
This does not include directly the secretary, accounting, lawyers, purchasers, sellers, office rental, electricity, tea with cookies - it does not depend on you and you can not manage it. All that is in your power is to add costs to this is all, in the form of an increase in the developers' hourly rate. This is what we will do now.
Personally, I prefer in this case to simply multiply the salary by “net” by 2. Yes, that’s so simple. Since the company pays tax of 43% on hand salary, and the remaining 57% goes as overhead. These remaining 57% also include implicit details that affect the final rate: there is a vacation of 28 days, developers are sick regularly, they ask for time - we can assume that they work 10.5 months a year, etc. If the director / financier does not agree with 57%, then this is definitely not your headache - you do not have and, in principle, cannot have access to the entire accounting department of the company, so ask the director / financier to give your figure, but for now it’s not set - put 57 %
Calculation example: the applied programmer receives 82 000 rubles per hand. On average 164 working hours (including holidays), i.e. his hourly salary is 82,000 / 164 = 500 rubles. We set the rate of 1000r / hour. All.
Before continuing, it is worth describing another way of accounting for overhead costs. As you probably already noticed, the overhead costs for the office, cookies and lawyers are the same for the junior and senior, so it will not be entirely correct to calculate the invoices as a% of their salary. Therefore, you can ask the director to set the task for the financiers to calculate the average number of office expenses per hour for the period (year / quarter / month - it doesn’t matter, divide by the number of hours). Then we get the figure of, say, 300r / h of invoices separately and the hourly cost of salaries (with taxes) separately 82,000 * 1.43 / 164 = 715r / h, and this will be more accurate, we will get in the amount of 715 + 300 = 1015r / hours The only but big problem here is that it’s very difficult to get the exact amount of overhead for the period from the accounting department. I only succeeded once, so I prefer to add interest on salaries. Yes, and the project is pulled mainly by middles with more or less the same salary, so the difference between the two accounting methods is not very large. In the end, you can agree, again, to put now 57% of the salary invoices, and when (more precisely, if) the accounting department provides the invoice number per hour - quickly change several rates, it takes 5 minutes from the force, everything will be recalculated automatically.
Simply? In my opinion, yes.
We now know how to calculate rates from salaries, so it makes no sense to give calculations here for other developers, to save time. Just put down the rates right away:
Programmer - 1300r / h
Circuit designer - 800r / h
PCB breeder - 800r / h
Tester - 600r / h
Implementer - 1000 r / h
PM, i.e. you yourself - 1200r / h, while you are 30% involved in the project, the rest are other projects, presale load, etc.
With the calculation of rates completed. Here is the original schedule:
According to the schedule - I’m not going to argue about how it should be in reality. The schedule is extremely simplified, to the point of breaking with reality, and is made with the aim of showing the impact of tasks on money, rather than giving a standard template for the development of a software and hardware complex. In general, "I am an artist, I see so."
Ok, click on the “Resource Sheet” button and in the opened view we enter the received rates:
Pay attention to the line "Plant". These are articles of purchases where all money must be given immediately, or rather, a little in advance, since your bookkeeping does not transfer money instantly, but periodically, once or twice a week, plus the money will go for some time.
Therefore, here we set the type of resource for making a purchase as “Material”, set the amount 40,000 in “Cost / Use” and set the payment (Accrue At) at the start of the task. Of course, in Feng Shui, you need to divide the “Making a Board” task into two Milestones: payment and receipt, but more complicated - two lines instead of one, add a temporary offset that is not visible on the schedule, etc. So, how is done - it is more visual and more difficult to forget. You can still tint this task with orange, but this is already too much.
Now let's switch back to the Gantt chart view. To see the resulting value, add a column called “Cost” or “Cost” and this is what we get:
The project cost is now transparent with accuracy to work. The management sees exactly how his money will be spent in your project, and this distinguishes you from other RMs that do not give such information. And disputes about people in the project are much easier to translate into a constructive way: everything is transparent, if you want cheaper and faster - let's instead of middlemen for key tasks in the project, let’s recount now.
Actually, the main part is finished on this, and you can play with the resulting model in a fairly large range, getting an instantaneous value for any changes. This is good, but there is more to it than that. We are faced with the task of removing the second bunt from the leadership - to calculate the cash flow on the project in order to give an argumented answer to the question: how much to request prepayments and how to break it down by stages.
Part two. Cash Flow and Future Income
This method of calculation can be used in a variety of options, ranging from the already stated goal of calculating the required percentage of prepayment and the distribution of payments in stages, so that the cache flo does not go negative, to a very convenient way in an already ongoing project, with many outsourcers, to quickly give out the amount to the financiers external payments by stages planned for completion in future months.
Let's put in the project a profitability of 30%, which gives us 1,233,600 * 1.3 = 1,603,680. Round up to 1,600,000 so as not to get confused in a penny. On this amount, you need to calculate how much to ask in advance, and how much - at the end of the stages. It is standard for the development of software and hardware systems to have an advance of 40% at the start, since the purchase of equipment and components occurs at the first stage, and 30% at the end of the first and second stages. We get in this way:
640 thousand - prepayment
480 thousand - at the end of the first and second stages.
We put in the project Milestones (milestones), corresponding to the moments of payment of the stages. To rename, so as not to produce unnecessary lines, the task “Start the project” in “Prepayment”, and “End of the project”, respectively, in “Payment of the second stage”. And we add the milestone “Payment of the first stage” after the task “Transferring software to the target board” - it is easier to convince the customer to part with the next money at the moment of seeing the product that has started working. In the column “Resource Names” we put “Payment 1”, “Payment 2” and “Payment 3”. It should look like this:
Click on the “Resource Sheet” again and enter the received prepayment values. This time, of course, with a negative sign, since we used to count the money leaving the company’s pocket, and now vice versa. And we will change the type of resources “Payment x” from “Work” to “Materials” by setting the above amounts (with a negative sign, because we are paid, not us) in Cost / Use and setting the payment (Accrue At) at the start of the task:
Please note that only MS Project 2019 displays the value with a negative sign, in previous versions negative values ​​are enclosed in parentheses. Although everything is considered correct.
Introduced. Switch back to Gantt and see this picture:
The total project costs are expressed in negative terms and this is undoubtedly nice.
Ok, move on to the cache flo for months. Here, alas, you already have to export the data to Excel and look in it. You can, of course, rename several fields in the names of the month and year, display each month in your own column and build your own monster formula for each column, taking into account the year and month ... I did it once, but it looks disgusting, especially if the project is long. Where it is easier to use the built-in reporting tools and output them to Excel, we will go this way.
As always, everything is very simple - go to the “Reports” tab and click on the “Visual Reports Export” button:
Next, in the window that opens, select "Cash Flow Report", set the level to "Months":
Click on “View” and in the Excel window that opens, select the second tab (sheet) - “Task Usage”. We get such a window, but so far it is uninformative:
On it, put the “Monthly Calendar” checkmark in “Fields of the Pivot Table” and click on the “+” icon marked with red ovals in the figure. We are facing what we wanted: a monthly cache-flo in our project with cumulative information, i.e. We understand on a monthly basis the overall result - we are in plus or minus this month, and the dynamics - we have already reached a plus in the project for money, or not yet:
Let's interpret the data. Red ovals indicate places where you need to be careful.
In March, our total profit is negative (we do not forget to invert the sign in our minds, since we are talking about expenses), expenses exceed revenues by 67 360 rubles. For April - positive, but only at 13280r, a delay of just a day will make it negative. Therefore, you must either insist on increasing the advance up to 45%, or return from the world of numbers to the real world and understand that these amounts do not exist by themselves, they are only information for making decisions. Mentioned + 57% to the rates of engineers - an abstraction, lawyers and accountants receive their salaries due not only to this, but also to other projects. Now, if there will be a cash gap for all of them in March, then this is an occasion to insist either on increasing the advance payment on the project, or deferring your payments for this month, and if not, look for other ways to plug the hole, for example, take a loan from the bank for a couple months. But these decisions are made by the financier / director, and not you as RM. You already gave them very valuable information: in two months there is a risk of a cash gap, you have time, think. This is much better than learning about this problem in two weeks.
That's all. Good luck with your financial planning!
All Articles