i am trying cleanup or recreate a 5 yr multiple location forecast that pulls from several single site business models where I can trigger the number of each type of model and the period I want new sites to open. i have a current version I have used for several years, but due to the number of single models/scenarios, it is cumbersome and slow.
The basics of the current model are a control tab that has a column for variable inputs for each scenario (revenue volume, employee count,salary, DSO, capex, etc.) that then populates a worksheet for each scenario creating a 5 yr inc stmt, bal sheet and cash flow.
For each scenario there is also an input on the control tab to indicate how many and what future month to add additional locations. Additional tabs take the number and timing of new locations from the control tab to populate a "rollup" 5 yr forecast for each model (one tab per scenario per year; ie, 5 scenarios results in 25 tabs (1 tab for each scenario started each year). Each of those is then consolidated.
Everything is currently done using vlookups, IF formulas and multiplying the number of triggered locations by the control totals.
I just feel there has to be an easier way and have spent hours googling more complex formulas to reduce the multiple tiered way it is currently done. I feel there is probably a way to just populate one 5 yr forecast for each model based on number and when they open and then a consolidated tab.
The piece that is causing the issue is the variable needed for changing when new locations would open.
The basics of the current model are a control tab that has a column for variable inputs for each scenario (revenue volume, employee count,salary, DSO, capex, etc.) that then populates a worksheet for each scenario creating a 5 yr inc stmt, bal sheet and cash flow.
For each scenario there is also an input on the control tab to indicate how many and what future month to add additional locations. Additional tabs take the number and timing of new locations from the control tab to populate a "rollup" 5 yr forecast for each model (one tab per scenario per year; ie, 5 scenarios results in 25 tabs (1 tab for each scenario started each year). Each of those is then consolidated.
Everything is currently done using vlookups, IF formulas and multiplying the number of triggered locations by the control totals.
I just feel there has to be an easier way and have spent hours googling more complex formulas to reduce the multiple tiered way it is currently done. I feel there is probably a way to just populate one 5 yr forecast for each model based on number and when they open and then a consolidated tab.
The piece that is causing the issue is the variable needed for changing when new locations would open.
