Budget Template Spread Expected cost over a period according to percentages

K

Kiriko

New Member
Joined
May 15, 2018
Messages
16
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:
[TABLE="width: 649"]
<tbody>[TR]
[TD="width: 208"]
[/TD]
[TD="width: 113"]
[/TD]
[TD="width: 198, colspan: 3"]
Payment Spread​
[/TD]
[TD="width: 345, bgcolor: transparent, colspan: 2"][/TD]
[/TR]
[TR]
[TD="width: 208"] Budget Item
[/TD]
[TD="width: 113"] Total Expected Amount
[/TD]
[TD="width: 57"] Year 1
[/TD]
[TD="width: 76"] Year 2
[/TD]
[TD="width: 66"] Year 3
[/TD]
[TD="width: 345, bgcolor: transparent, colspan: 2"][/TD]
[/TR]
[TR]
[TD="width: 208, bgcolor: transparent"] Core Banking + Digital
[/TD]
[TD="width: 113, bgcolor: transparent"]
4,230,000​
[/TD]
[TD="width: 57, bgcolor: transparent"]
100%​
[/TD]
[TD="width: 76, bgcolor: transparent"]
[/TD]
[TD="width: 66, bgcolor: transparent"]
[/TD]
[TD="width: 345, bgcolor: transparent, colspan: 2"][/TD]
[/TR]
[TR]
[TD="width: 208, bgcolor: transparent"] Agency Banking
[/TD]
[TD="width: 113, bgcolor: transparent"]
60,000​
[/TD]
[TD="width: 57, bgcolor: transparent"]
[/TD]
[TD="width: 76, bgcolor: transparent"]
60%​
[/TD]
[TD="width: 66, bgcolor: transparent"]
40%​
[/TD]
[TD="width: 345, bgcolor: transparent, colspan: 2"][/TD]
[/TR]
[TR]
[TD="width: 208, bgcolor: transparent"] Business Intelligence
[/TD]
[TD="width: 113, bgcolor: transparent"]
2,538,000​
[/TD]
[TD="width: 57, bgcolor: transparent"]
33%​
[/TD]
[TD="width: 76, bgcolor: transparent"]
33%​
[/TD]
[TD="width: 66, bgcolor: transparent"]
33%​
[/TD]
[TD="width: 345, bgcolor: transparent, colspan: 2"][/TD]
[/TR]
[TR]
[TD="width: 208, bgcolor: transparent"] Web Center, Middleware & SOA
[/TD]
[TD="width: 113, bgcolor: transparent"]
1,015,200​
[/TD]
[TD="width: 57, bgcolor: transparent"]
50%​
[/TD]
[TD="width: 76, bgcolor: transparent"]
50%​
[/TD]
[TD="width: 66, bgcolor: transparent"]
[/TD]
[TD="width: 345, bgcolor: transparent, colspan: 2"][/TD]
[/TR]
[TR]
[TD="width: 208, bgcolor: transparent"] Oracle Database
[/TD]
[TD="width: 113, bgcolor: transparent"]
870,000​
[/TD]
[TD="width: 57, bgcolor: transparent"]
33%​
[/TD]
[TD="width: 76, bgcolor: transparent"]
33%​
[/TD]
[TD="width: 66, bgcolor: transparent"]
33%​
[/TD]
[TD="width: 345, bgcolor: transparent, colspan: 2"][/TD]
[/TR]
[TR]
[TD="width: 208, bgcolor: transparent"] OFSAA
[/TD]
[TD="width: 113, bgcolor: transparent"]
22,254​
[/TD]
[TD="width: 57"]
[/TD]
[TD="width: 76"]
[/TD]
[TD="width: 66"]
[/TD]
[TD="width: 123, bgcolor: transparent"] *12% of Agency Banking increment for 3 yrs
[/TD]
[TD="width: 222, bgcolor: transparent"]
[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 533"]
<tbody>[TR]
[TD="width: 151, bgcolor: transparent"]
​
[/TD]
[TD="width: 151, colspan: 2"]
Total​
[/TD]
[TD="width: 144, colspan: 2"]
YEAR 1​
[/TD]
[TD="width: 132, colspan: 2"]
YEAR 2​
[/TD]
[TD="width: 133, colspan: 2"]
YEAR 3​
[/TD]
[/TR]
[TR]
[TD="width: 151"]
​
[/TD]
[TD="width: 151, colspan: 2"]
Total Budget​
[/TD]
[TD="width: 76"]
FY 2017​
[/TD]
[TD="width: 69"]
FY 2017​
[/TD]
[TD="width: 64"]
FY 2018​
[/TD]
[TD="width: 69"]
FY 2018​
[/TD]
[TD="width: 75"]
FY 2019​
[/TD]
[TD="width: 58"]
FY 2019​
[/TD]
[/TR]
[TR]
[TD="width: 151"]
Budget Item​
[/TD]
[TD="width: 76"]
CAPEX​
[/TD]
[TD="width: 76"]
OPEX​
[/TD]
[TD="width: 76"]
CAPEX​
[/TD]
[TD="width: 69"]
OPEX​
[/TD]
[TD="width: 64"]
CAPEX​
[/TD]
[TD="width: 69"]
OPEX​
[/TD]
[TD="width: 75"]
CAPEX​
[/TD]
[TD="width: 58"]
OPEX​
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"] Core Software
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
​
[/TD]
[TD="width: 64, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
​
[/TD]
[TD="width: 75, bgcolor: transparent"]
​
[/TD]
[TD="width: 58, bgcolor: transparent"]
​
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"] Core Banking + Digital
[/TD]
[TD="width: 76, bgcolor: transparent"]
4,230,000​
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 76, bgcolor: transparent"]
4,230,000​
[/TD]
[TD="width: 69, bgcolor: transparent"]
​
[/TD]
[TD="width: 64, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
​
[/TD]
[TD="width: 75, bgcolor: transparent"]
​
[/TD]
[TD="width: 58, bgcolor: transparent"]
​
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"] Licensing
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
​
[/TD]
[TD="width: 64, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
​
[/TD]
[TD="width: 75, bgcolor: transparent"]
​
[/TD]
[TD="width: 58, bgcolor: transparent"]
​
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"] Agency Banking
[/TD]
[TD="width: 76, bgcolor: transparent"]
60,000​
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 76, bgcolor: transparent"]
36,000​
[/TD]
[TD="width: 69, bgcolor: transparent"]
​
[/TD]
[TD="width: 64, bgcolor: transparent"]
24,000​
[/TD]
[TD="width: 69, bgcolor: transparent"]
​
[/TD]
[TD="width: 75, bgcolor: transparent"]
​
[/TD]
[TD="width: 58, bgcolor: transparent"]
​
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"] Annual License
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
​
[/TD]
[TD="width: 64, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
​
[/TD]
[TD="width: 75, bgcolor: transparent"]
​
[/TD]
[TD="width: 58, bgcolor: transparent"]
​
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"] Business Intelligence
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 76, bgcolor: transparent"]
2,538,000​
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
846,000​
[/TD]
[TD="width: 64, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
846,000​
[/TD]
[TD="width: 75, bgcolor: transparent"]
​
[/TD]
[TD="width: 58, bgcolor: transparent"]
846,000​
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"] Web Center, Middleware & SOA
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 76, bgcolor: transparent"]
1,015,200​
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
507,600​
[/TD]
[TD="width: 64, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
507,600​
[/TD]
[TD="width: 75, bgcolor: transparent"]
​
[/TD]
[TD="width: 58, bgcolor: transparent"]
​
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"] Oracle Database
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 76, bgcolor: transparent"]
36,000​
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
12,000​
[/TD]
[TD="width: 64, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
12,000​
[/TD]
[TD="width: 75, bgcolor: transparent"]
​
[/TD]
[TD="width: 58, bgcolor: transparent"]
12,000​
[/TD]
[/TR]
[TR]
[TD="width: 151, bgcolor: transparent"] OFSAA
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 76, bgcolor: transparent"]
22,254​
[/TD]
[TD="width: 76, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
7,200​
[/TD]
[TD="width: 64, bgcolor: transparent"]
​
[/TD]
[TD="width: 69, bgcolor: transparent"]
7,416​
[/TD]
[TD="width: 75, bgcolor: transparent"]
​
[/TD]
[TD="width: 58, bgcolor: transparent"]
7,638​
[/TD]
[/TR]
[TR]
[TD="width: 151"] Total - Core Software Licensing
[/TD]
[TD="width: 76"]
4,290,000​
[/TD]
[TD="width: 76"]
3,611,454​
[/TD]
[TD="width: 76"]
4,266,000​
[/TD]
[TD="width: 69"]
1,372,800​
[/TD]
[TD="width: 64"]
24,000​
[/TD]
[TD="width: 69"]
1,373,016​
[/TD]
[TD="width: 75"]
0​
[/TD]
[TD="width: 58"]
865,638​
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
You must log in or register to reply here.

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top