How to distribute/spread/map cost of project across different year

[Dream]

Hi all,

I appreciate any help you can offer with the following. I am trying to
distribute or spread the cost of projects across different years given
specified ratios.

The ratios change according to project duration. If it is 3 quarters long,
the formula is 30%,40%,30% of the cost so that the total is 100%. If it is 4
quarters long, it becomes 20%,30%,30%,20% and so forth. I have the ratios
arranged by duration from 3 quarters until 24 quarters. so I already have 1
column showing duration, while the next columns showing the percentages..

Project starting dates are formatted as quarter number then year number
(such as, Q2 1999) and so on.

My data look like the following:
Project Name Cost Start Date Duration(Quarters) Q1 1995..Q1 1999 Q2 1999 Q3
1999..
Project1 $100 Q1 1999 3
Project2 $200 Q4 2000 4

As you can see, next to the table, I made a huge list of years from 1995
until 2020 split into quarters so that the costs can be distributed under
each one.
So, I would like the formula/function/macro to read the duration, then apply
the respective formula to the cost of that project and return the values
under each respective quarter which are listed in order to the right of that
table. For example, for project 1 above, the following cells should be filled:
Q1 1999 Q2 1999 Q3 1999
30 40 30

and for project 2, the following should take place:
Q4 2000 Q1 2001 Q2 2001 Q3 2001
40 60 60 40

I appreciate your help and thank you in advance..
regards,

 

[Joel]

You didn't give a lot of info but I can get you started.

You can get the number of quarts a project need by counting the number of
headers in the table


For eaxample Project 1
Q1 1999 Q2 1999 Q3 1999
30 40 30

Has 3 columns in the header so you need 3 quarters. To get the number of
quarters use this formula

=COUNTA(1:1)

The results will be 3. So you know know which row you are going to use with
the percentages.


Now you column in the table shoulbe be the same as you project results

Q1 1999 Q2 1999 Q3 1999
30 40 30

The first column in the reuslting table should be getting the first column
from your percentage table. The 2nd column from the 2nd column in the
percnetage table. You basically need an index function

INDEX(array,row_num,column_num)

INDEX(Sheet2!$A$1:$Z$100,COUNTA(1:1),Column())

 

[Dream]

Hi Joel and thanks for your reply

Please find below more information hope that clarifies the question.

Sheet1 has information about the projects, such as:
project name|cost |start date| duration
project 1 $100 Q1 2009 3

also, i another sheet i have information about the percentage for each
duration:
duration (in quarters) percentages (in adjacent cells formatted as %)
3 30 40 30
4 20 30 30 20
and so forth there are different rations for each duration until 24 which is
24 1 2 2 3 4 5 5 6 6 7 8 8 7 6 6 5 5 4 3 2
2 1

in a new sheet i have the following table that i would like the formula or
function to fill:
project name cost distribution in each quarter
Q1 1995 Q2 1995 Q3 1995 Q41995 Q1 1996 Q2 1996....until
Q42020
project 1 (here i would like it to assign costs to the years on
which the project is awarded...so for this project it will only return
numbers under Q1 2009 Q2 2009 Q3 2009)

for example, for project 1, the idea is that the fuction will look at the
project duration (3 quarters), then look under the ratios specific for that
duration (30 40 30), then look at the starting date of the project (Q1 2009),
then go to the table that i made and go to the starting date of that project
in that table (Q1 2009), and began applying the ratios under that data (30% *
project cost), then move to teh adjacent cell which is the next quarter (Q2
2009) and apply the next ratio in the formuila (40% * project cost), then
move to the adjacent cell in the table whic his the next quarter (Q3 2009)
and apply the next ratio in the formula (30% * project cost), and it will
stop there because the proejct duration is 3 quarters. and then, do the same
for the next project....I hope that helps...I appreciate your help.Many thanks

 

[Joel]

You have a header row and a header column so just adjust the index numbers

INDEX(Sheet2!$A$1:$Z$100,COUNTA(1:1)+1,Column()+1)

Sheet 2 is the table with the percentages

If you have this table which is 6 quarter
Q1 1995 Q2 1995 Q3 1995 Q41995 Q1 1996 Q2 1996
Index(5,2) Index(5,3) Index(5,4) Index(5,5) Index(5,8) Index(5,9)

Note: I didn't include the table Sheet2!$A$1:$Z$100 in the index formula above

the index above shows the row and column in the table you are getting the
percentages from. The row is row 5 because

Row 1 is the header row
Row 2 has 3 quarters.
Row 3 has 4 quarters.
Row 4 has 5 quarters.
Row 5 has 6 quarters.
Row 6 has 7 quarters.
Row 7 has 8 quarters.