I have a workbook with products and services spec sheets and a summary sheet.
On the summary sheet, each line correspond to a product. I am trying to make projections of human resources used each month in function of units sold.
On any given spec sheet, there is a table which lists the resources required to provide that service with the following columns: total RH time required, spread factor (1 means that all the resources are consumed during the month the service is sold, while 2 would mean that the rh are going to be used on the month of the sale and the following month, and so on), and number of hours it represents for one month.
On the summary sheet, you have four columns under each month. First is for number of units sold, second total sales(not relevant), third is total RH for this month, fourth is not relevant.
The RH total is the (((number of unit sold) * (total of non spread (factor 1) RH time))+ ((number of unit sold)*(monthly RH value for (factor n) spread))) + the sum of all monthly RH value that spread over current month (this is where i can't figure what the formula can look like)
https://onedrive.live.com/redir?resid=B87CCD033A9C7FBE!7285
Here we can see that in January, RH is 36 because 35 hours that will be consumed that month plus 1 of the 3 that will be spread over 3 months. In february, you have a spread RH from january plus those of the current month, In March, there are no sales so the only RH will be the spread from January and February. In april, there is one sale so the RH will be the current month plus the spreads from February but since there were no sales in March there are no additional spread for that month. January's sale spread finished in March.
How could I put that in a formula? Or even vba.
On the summary sheet, each line correspond to a product. I am trying to make projections of human resources used each month in function of units sold.
On any given spec sheet, there is a table which lists the resources required to provide that service with the following columns: total RH time required, spread factor (1 means that all the resources are consumed during the month the service is sold, while 2 would mean that the rh are going to be used on the month of the sale and the following month, and so on), and number of hours it represents for one month.
On the summary sheet, you have four columns under each month. First is for number of units sold, second total sales(not relevant), third is total RH for this month, fourth is not relevant.
The RH total is the (((number of unit sold) * (total of non spread (factor 1) RH time))+ ((number of unit sold)*(monthly RH value for (factor n) spread))) + the sum of all monthly RH value that spread over current month (this is where i can't figure what the formula can look like)
Here we can see that in January, RH is 36 because 35 hours that will be consumed that month plus 1 of the 3 that will be spread over 3 months. In february, you have a spread RH from january plus those of the current month, In March, there are no sales so the only RH will be the spread from January and February. In april, there is one sale so the RH will be the current month plus the spreads from February but since there were no sales in March there are no additional spread for that month. January's sale spread finished in March.
How could I put that in a formula? Or even vba.
Last edited:
