Budget Template Spread Expected cost over a period according to percentages

[Kiriko]

Simply said I need to create a project budget. I want mybudget to be linked to an input sheet where I update the total expected andwhat percentage is expected to be paid in what year and it’s automaticallyallocated to the budget output sheet.
So this is what the input sheet should look like:

		Payment Spread​
Budget Item	Total Expected Amount	Year 1	Year 2	Year 3
Core Banking + Digital	4,230,000​	100%​
Agency Banking	60,000​		60%​	40%​
Business Intelligence	2,538,000​	33%​	33%​	33%​
Web Center, Middleware & SOA	1,015,200​	50%​	50%​
Oracle Database	870,000​	33%​	33%​	33%​
OFSAA	22,254​				*12% of Agency Banking increment for 3 yrs

<tbody> </tbody>

Basically Cost Item, Total Expected Cost for the entireproject life and breakup of that expected cost.
There are four scenarios that all need to “Co- exist” or runat the same time:

1. One – Off payments: Being the total expectedcost to be fully paid in year x
   
2. Up front and at completion: Payment split can be50/50 or 60/40 etc, where a percentage of the cost is expected to be paid inYear x and the remainder at a later date
   
3. Equal split for the duration of the project:Every year pay a fixed amount for example Annual Maintenance which is 20% for 5years total equally spread over the 5 years
   
4. Incremental Costs: The hardest to me, say I haveto pay 12% of 60,000 on the first year and every year it increases by 3% untilthe end of the project (Year 3)

So apart from the last one it’s all pretty straightforward Ijust need a way to get excel to allocated the expected amount to the correctCost item line and then spread it out over the 3 years as per the percentagebreak-up in the input sheet.
I would like to have this as the budget sheet:

​	Total​	YEAR 1​	YEAR 2​	YEAR 3​
​	Total Budget​	FY 2017​	FY 2017​	FY 2018​	FY 2018​	FY 2019​	FY 2019​
Budget Item​	CAPEX​	OPEX​	CAPEX​	OPEX​	CAPEX​	OPEX​	CAPEX​	OPEX​
Core Software	​	​	​	​	​	​	​	​
Core Banking + Digital	4,230,000​	​	4,230,000​	​	​	​	​	​
Licensing	​	​	​	​	​	​	​	​
Agency Banking	60,000​	​	36,000​	​	24,000​	​	​	​
Annual License	​	​	​	​	​	​	​	​
Business Intelligence	​	2,538,000​	​	846,000​	​	846,000​	​	846,000​
Web Center, Middleware & SOA	​	1,015,200​	​	507,600​	​	507,600​	​	​
Oracle Database	​	36,000​	​	12,000​	​	12,000​	​	12,000​
OFSAA	​	22,254​	​	7,200​	​	7,416​	​	7,638​
Total - Core Software Licensing	4,290,000​	3,611,454​	4,266,000​	1,372,800​	24,000​	1,373,016​	0​	865,638​

<tbody> </tbody>

The actual worksheet has a lot more columns and rows but fornow my biggest issue is percentage split in x year and increment costs.