Jeff Huber
New Member
I am trying to build a forecast of production scale up. I am forecasting pounds of product, and as the forecast nears capacity of current plant, I want to trigger the capex of the new plant over several time periods (the amount of time it takes to build the new plant with greater capacity). This also needs to be dynamic. I am having trouble finding a way because the build time is an input, so sometimes the capex may need to hit 4 periods before the plant goes online, and other times 5 or 6. This number also affects the capex input- Capex for period will be total plant capex/build period (evenly distributed).
The Capex is triggered by the forecast, but how far forward the Capex cell look depends on the build time.
I was originally thinking some sort of offset(lookup( but could not get it to work. I also considered setting the trigger to be x% of capacity- however then the model would be prone to error, say, as product maturity is reached because it may reach 80% capacity in period 10 (triggering beginning of Capex), and not grow to 100% capacity until much later. Thus, the plant would be triggered too soon.
I've attached a hardcoded example of the concept.
The Capex is triggered by the forecast, but how far forward the Capex cell look depends on the build time.
I was originally thinking some sort of offset(lookup( but could not get it to work. I also considered setting the trigger to be x% of capacity- however then the model would be prone to error, say, as product maturity is reached because it may reach 80% capacity in period 10 (triggering beginning of Capex), and not grow to 100% capacity until much later. Thus, the plant would be triggered too soon.
I've attached a hardcoded example of the concept.
