Dynamic formula needed please

[targus92]

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

File can be seen here: Imgur: The most awesome images on the Internet

Thanks

 

[LakevilleRDY]

This should work. Assuming unit cost is stored in cell C13.

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