As a planning engineer, you deal with dates and quantities. Therefore, during your daily tasks, you may need to create a cash flow, manpower histogram, or even a quantity-based small schedule.

This Excel spreadsheet will help you to distribute any quantities, man-hours, or even costs between dates. For example, if you have an activity that starts on 1-Oct-2015 and finishes on 15-Nov-2015, taking the weeks as periods what would be the planned % in Months 1, 2, 3, etc.? I have kept this spreadsheet unformatted so it will be easy to copy, edit, or use the formula only in your works.

Spreadsheet Applications:

1- Create Cash Flow for a project.

2- Create a Manpower Histogram.

3- Create a Quantity Distribution of an activity/activities.

All three of the mentioned items share a common underlying concept. I will now elaborate on the concept of cash flow as an example, and you can apply the same idea to the other aspects. If you possess information such as the activity ID, activity start date, activity finish date, and activity cost from the Primavera P6, you can utilize this spreadsheet to manage cash flow.

Simply, copy the activity ID, activity start date, and activity finish date into the spreadsheet and determine the weight for each month, week, or day, depending on your chosen time frame. The weight value is set at 1000, meaning that if an activity is projected to span two months, weeks, or days, each unit will be assigned a weight of 500.

Once you have established the weight distribution based on the selected time frame, you can employ the Vlookup function in Microsoft Excel. This function will enable you to locate the relevant Activity ID and allocate the appropriate weight to each month, week, or day. You can then proceed to multiply these weights by the respective quantities, man-hours, or costs, and subsequently divide the cumulative result by 1000.

Feeling overwhelmed by the complexity of the process? That’s perfectly okay. It’s important to note that this tool isn’t designed to function in isolation. Instead, think of it as an intermediary tool that assists you in conducting various calculations. The outcome is not obtaining the final results, which you can then integrate into your own customized format or dashboard.

By engaging in this procedure to manage a schedule, you will acquire the ability to generate all three applications—Cash Flow analysis, Manpower Histograms, and Quantity Distribution—using a single file. The only variable lies in how you choose to leverage the weight factor to ascertain costs, hours, or quantities for each component.

---

Download Excel Sheet to Distribute Quantities Between Dates

---

This is the sheet at first sight, you won’t understand it but it is quite easy

1. The “Activity ID” is exported from Primavera P6.
2. Stands for the “Early Start Date” for the activities exported from Primavera P6.
3. Stands for the “Early Finish Date” for the activities.
4. All these columns represent the months of project life and they can be changed to weeks or years as you want.

This is the overview of the first sight of the sheet.

How the Spreadsheet Works

This sheet sees any cost, resources, and quantities as 1000 units regardless of the real unit of the parameter (cost, resources, and quantities).

A-001 Activity

This activity starts on 10 May 2015 and finishes on 10 June 2015, so the Excel sheet distributes the 1000 units in May and June as follows:

It takes 21 days in May and 10 days in June so it distributes the 1000 units at them, so, May got the majority of the units whatever it is (cost, resources, and quantities).

A-002 Activity

This activity starts on 10 May 2015 and finishes on 19 May 2015, so the Excel sheet distributes the 1000 units in May because the activity starts and finishes in May.

It takes 9 days in May, so it distributes the 1000 units in May only.

The privilege of this sheet is that you don’t need to add values, it gives you percentages and you can multiply them with the real values for the 3 parameters (cost, resources, and quantities).

So you can use them in your reports your forms or Power BI formats.

The Bar Chart

Find the total monthly weight and the cumulative monthly weight to draw the chart.

 

The total monthly weight is calculated by the number of added activities.

Total Monthly weight = {number of activities * weight of the activity(1000)}

So the first month’s weight will be calculated by this formula:

= sum of the all weight in this month divided by the all weight of the project.

This will give you the monthly weight.

Apply this equation to all the columns till the end of the project date.

Then, calculate the cumm. monthly weight percentage.

The cumm. monthly weight for the first month will be equal to the first month’s total weight but the cumm. monthly weight for the second month will be equal to the first and second month’s total weight.

Then drag this formula to all the columns.

You will find that the bar chat will be drawn.

You can also use the manhours to make this chart

If the total manhours for the projects are 1000 hours then the above percentage will be multiplied by the monthly percentage.

Monthly hours = Total monthly percentage * Total hours.

Copy the above steps and apply them to create a bar chart with the monthly man-hours in the project.

It can be done to the cash flow as well and have a chart too.

If you are interested in Excel tools here are many of them:

• Paste cells from a Single Cell to a Merged Cell
• Copy Primavera and Paste it into Excel with WBS
• How to make 3 weeks ahead by Excel
• Mini Schedule Plan using Excel

---

Download the PDF version of this article to share with your colleagues