How about an Excel template that does the job of automatic quantity distribution for you? All you have to give the quantities, and the start and finish dates, and Excel will do the remaining job. Not only it will distribute the quantities month-wise but also would provide you with an activity-wise summary of quantities.
We have prepared a template in Excel that does the same thing. You need to feed it the item-wise quantities and start & finish dates, and it does the rest of the work. Don’t need to manually distribute the quantities anymore. The formula provided here will distribute the item-wise quantities based on the number of days in a month, pro-rate.
Where You Can Use This Template
You would find this template extremely useful while doing budgeting or resource planning for your project. Especially, it’s useful over a long duration, but you can use this for shorter-duration projects also without any change in the template.
You can get the start and finish dates from your MS Project schedule, and the quantities from the project BOQ, or the Quantity Surveyor of the project. Or you can determine quantities from the available drawings.
From this Excel template for automatic quantity distribution, you can get the revenue projection for your project over a period of time.
Example of Distribution with this Template
For example, say one of your concreting work item’s quantity is 100 cubic meters. The start date is 15th December 2022, and the finish date is 20th March 2023. In this case, the distribution formula will act as described below.
Here, the number of days available in December 2022 is 17, January 2023 is 31, February 2023 is 28, and March 2023 is 15.
So, the total duration allocated for this item is 91.
Now, it will allocate (100 / 91 x 17) = 18.7 cubic meters in December 2022.
Further, (100 / 91 x 31) = 34.1 cubic meters in January 2023.
(100 / 91 x 28) = 30.8 cubic meters in February 2023.
and, (100 / 91 x 15) = 16.5 cubic meters in March 2023.
Download the Template for Free
You can download the Excel template of automatic quantity distribution as we discussed here. This template is free and forever will remain. The link is given below for you:
We have made this Excel template for automatic quantity distribution with a view to helping Planning Engineers across the globe to reduce the clerical job and focus more on the planning part. There is no password in this template and you can edit as you wish. You can change the start and finish dates, months, activities, and formatting.
At the bottom of the sheet, we have provided a simple table for summing up the quantities of the same activities. For example, there could be 4 different items of concrete. And you would want to know how much quantity of concrete is planned for a particular month. It will provide you with the month-wise distribution of the planned quantities for each item.
There is a master sheet, where you can add activity groups and units. Don’t add these two things manually as it will increase your hardship for a bigger project schedule. Hence, it is better to enter these in the adjacent sheet and enjoy the advantage of automation.
Get Back to Us
Further, if you have any suggestions to incorporate or modify for improvement on this Excel template for automatic quantity distribution, you may always get back to us through the comments section below.
Happy Planning!
I was curious if you ever thought of changing the layout of your blog? Its very well written; I love what youve got to say. But maybe you could a little more in the way of content so people could connect with it better. Youve got an awful lot of text for only having 1 or 2 images. Maybe you could space it out better?
I am not certain where you are getting your information, but good topic. I needs to spend a while studying much more or working out more. Thanks for fantastic info I was on the lookout for this information for my mission.