Hi All,

I need some help spreading some costs and making it a dynamic formula. Please use the attachment to reference the text below:

Row 16 shows the incremental inventory that is available.


Row 13 shows the cost of construction

I need to spread the incremental cost of construction among a number of years of construction period (in this case cell C7, 3 years)

Example: In year 2021 there are 20 units. Each unit costs 3,000. However the total cost of 20 units (60,000) needs to spread by the previous 3 years equally (2018, 2019, 2020). In 2022, there are 5 more units that come available. This cost is 15,000. This needs to be spread equally over the previous 3 years (2019, 2020, 2021). And so on and so forth

Can you please help me create a formula I would insert into D9 and drag across

Thanks

Attached Files

Excel formula for spreading cost.xlsx (23.5 KB, 16 views)

Try:

=$C$13*SUM(E16:G16)/3