Forum Discussion
Solved
Proportional Distribution of Purchase to Several Projects
I need to proportionally distribute the cost of a purchase to several projects, taking into consideration the project start/end dates and total project award amount. Please let me know what formula can be used for this. Thanks.
Hi Johanny,
I'd take in proportion of Award per project day for all projects (A/D), but your boss knows better. Perhaps he just wants to have clean formula for the cost distribution to follow it in case of any changes in project parameters.
3 Replies
Hi Johanny,
Afraid that's not only Excel question, i have no idea what's the business logic behind such distribution.
If you distribute proportionally to (Award Amount)/(# of Award Days)
for each project, when you calculate above proportion for the project and divide on sum of proportions for all projects which is calculated as
=SUMPRODUCT((AwardAmountA:AwardAmountD)*(1/(AwardDaysA:AwardDaysD)))
In terms of you worksheet entire formula for the Project A will be
=$C$11*(I11/G11)/SUMPRODUCT(($I$11:$I$14)*(1/($G$11:$G$14)))
, copy/paste for other projects
If it's proportionally to
(Award Amount)*(# of Award Days)
when for Project A
=$C$11*(G11*I11)/SUMPRODUCT($G$11:$G$14,$I$11:$I$14)
Please see attached file for details
Hi Sergei,
Thank you for your response. I appreciate that you took the time to come up with a formula that would work.
Our clients generally tell us to equally distribute the cost of an item to several projects. However, my employer does not accept equal distribution and requires we provide the specifics of what formula was used to make sure the distribution of an expense is proportionate. Since projects have different duration and award amounts, it seems to me both must be in the equation in order to arrive at a proportional distribution. Unfortunately, there are two possible formula (A*D or A/D) that provide different distribution amounts and I wouldn't know which to apply or how to explain why one was chosen over the other.
Thanks again for your time and input.
Johanny
Hi Johanny,
I'd take in proportion of Award per project day for all projects (A/D), but your boss knows better. Perhaps he just wants to have clean formula for the cost distribution to follow it in case of any changes in project parameters.
