Projecting Cashflow based on Starting month, Project Duration and Spend Profile

I’m working on Excel 2013 and really looking for an answer to a complicated (for me) calculation.

The purpose of the workbook is to allow various projects to report onto a template to project cash flow for construction works. The numbers that will be inserted into the sheet are

1) Start of Work Date (In this case 3 for period 3)

2) Duration of Works (Nr of months, for this example 5) - from this we will get an End Date for
works.

3) Project Value (£1m)


I have a spend profile which id like to apply. i.e. Month 1 (10% of Project Value, Month 2 (20%), Month 3 (40%), Month 4 (20%), Month 5 (10%)

Input Table

Start Period Duration (Periods) End Period Project Value
Project Dates 3 5 8 £1,000,000




I have these currently set up in a table so that I can pick them up

Spend Profile Matrix
Period
1
2 3 4 5 6 7 8
Duration % Spend % Spend % Spend % Spend % Spend % Spend % Spend % Spend
1 100 0 0 0 0 0 0 0
2 50 50 0 0 0 0 0 0
3 25 50 25 0 0 0 0 0
4 15 35 35 15 0 0 0 0
5 10 20 40 20 10 0 0 0
6 7.5 15 27.5 27.5 15 7.5 0 0
7 5 10 15 40 15 10 5 0

Timeline 1 2 3 4 5 6 7 8 9 10
Spend % 0 0 10 20 40 20 10 0 0 0
Value 0 0 £100K £200k £400k £200k £100k 0 0 0

Output Table



The difficult part is to pick up the correct figures from the spend profile based on the information entered into the Input Table and be calculated into the correct month in the Output table. For example if Start date was now changed to Period 2 and for a duration of 3 periods. I would want the table to read


Timeline 1 2 3 4 5 6 7 8 9 10
Spend % 0 25 50 25 0 0 0 0 0 0
Value 0 £250k £500K £250k 0 0 0 0 0 0



Any help or advice or solution would be hugely appreciated as this is driving me mad and unfortunately I don’t seem to have the skills to resolve it.