Forum Discussion

Lambeck760's avatar
Lambeck760
Copper Contributor
Nov 23, 2020

Excel repeat cost in cash liquidity forecast based on start date

Hi,

I am trying to create a budget cash liquidity forecast for a 5 year plan

 

Each cost group has it's own sheet with information and calculation.

Some cost is fixed month by month.

Some cost are based on production rate month by month

Some cost are based on amount of resources (i.e. aircraft, truck, car, ...)

  

The cash flow is also different. Some are yearly, quarterly and monthly.

 

All cost start at different dates. To control this I made one seperate sheet where I add the resources at the forecasted buisness plan. Each added resource trigger a cost. 

On my summary sheet I need to find a formula that only takes the cost 1. starting at the month I entered as start month. 2. then automatically repeat the cost every month, quarterly or yearly.

Above sample I have manually added the cost from the correct start period and manually updated every quarter. Very labor heavy if we run different scenario where we move the start date of various resources.

Any good suggestions?

excel
Formulas and Functions
Macros and VBA
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Nov 23, 2020

    Lambeck760 

    That's not clear how rows in summary code are connected to rows in planning sheet - by position, by ID or how?

     

    Without that you base the formula on SUMIFS, like

    =SUMIFS( horisontal range,
         alldates headers, ">=" & start of period,
         alldates headers, "<=" & end of period
)

     

Resources