Excel repeat cost in cash liquidity forecast based on start date

%3CLINGO-SUB%20id%3D%22lingo-sub-1924459%22%20slang%3D%22en-US%22%3EExcel%20repeat%20cost%20in%20cash%20liquidity%20forecast%20based%20on%20start%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1924459%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20budget%20cash%20liquidity%20forecast%20for%20a%205%20year%20plan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20cost%20group%20has%20it's%20own%20sheet%20with%20information%20and%20calculation.%3C%2FP%3E%3CP%3ESome%20cost%20is%20fixed%20month%20by%20month.%3C%2FP%3E%3CP%3ESome%20cost%20are%20based%20on%20production%20rate%20month%20by%20month%3C%2FP%3E%3CP%3ESome%20cost%20are%20based%20on%20amount%20of%20resources%20(i.e.%20aircraft%2C%20truck%2C%20car%2C%20...)%3C%2FP%3E%3CP%3E%E2%80%83%E2%80%83%3C%2FP%3E%3CP%3EThe%20cash%20flow%20is%20also%20different.%20Some%20are%20yearly%2C%20quarterly%20and%20monthly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20cost%20start%20at%20different%20dates.%20To%20control%20this%20I%20made%20one%20seperate%20sheet%20where%20I%20add%20the%20resources%20at%20the%20forecasted%20buisness%20plan.%26nbsp%3BEach%20added%20resource%20trigger%20a%20cost.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tempsnip1.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235594iDE1C36679F360F6E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22tempsnip1.png%22%20alt%3D%22tempsnip1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EOn%20my%20summary%20sheet%20I%20need%20to%20find%20a%20formula%20that%20only%20takes%20the%20cost%201.%20starting%20at%20the%20month%20I%20entered%20as%20start%20month.%202.%20then%20automatically%20repeat%20the%20cost%20every%20month%2C%20quarterly%20or%20yearly.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tempsnip2.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235595iEBEBA0BF0F018C51%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22tempsnip2.png%22%20alt%3D%22tempsnip2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAbove%20sample%20I%20have%20manually%20added%20the%20cost%20from%20the%20correct%20start%20period%20and%20manually%20updated%20every%20quarter.%20Very%20labor%20heavy%20if%20we%20run%20different%20scenario%20where%20we%20move%20the%20start%20date%20of%20various%20resources.%3C%2FP%3E%3CP%3EAny%20good%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1924459%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1924686%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20repeat%20cost%20in%20cash%20liquidity%20forecast%20based%20on%20start%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1924686%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F878987%22%20target%3D%22_blank%22%3E%40Lambeck760%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20not%20clear%20how%20rows%20in%20summary%20code%20are%20connected%20to%20rows%20in%20planning%20sheet%20-%20by%20position%2C%20by%20ID%20or%20how%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWithout%20that%20you%20base%20the%20formula%20on%20SUMIFS%2C%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIFS(%20horisontal%20range%2C%0A%20%20%20%20%20%20%20%20%20alldates%20headers%2C%20%22%26gt%3B%3D%22%20%26amp%3B%20start%20of%20period%2C%0A%20%20%20%20%20%20%20%20%20alldates%20headers%2C%20%22%26lt%3B%3D%22%20%26amp%3B%20end%20of%20period%0A)%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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. 

tempsnip1.png

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.

tempsnip2.png

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?

1 Reply

@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
)