Hey Guys,
Long time lurker, first time poster. I am currently building a 3-way forecast model for a group of 27 Vendors who between them have 54 sites. Each site will require a P&L, BS and CF. I will be consolidating the sites to a Vendor level and then to an overall Group level. I will also be adding Dashboards to the front of this model.
Now the fun part, I have built out the forecast inputs tab and the first lot of PL,BS and CF sheets and my file is 3mb. My issue is these 3 sheets will need to be copied 27 x 3 and 54 x 3 times for a total of 243 calculation tabs. For some idea of size, P&L, BS and CF are around 300 rows x 142 columns with the inputs tab being 1600 rows x 20 columns.
I am after suggestions on how to handle this? should I be creating a split model? I have no experience in this and everything I have done previously tells me that external workbook linking is going to be a real hassle, especially for the end user.
Things I have done to keep the size down:
Any suggestions on how this would best be handled would be appreciated.
Thanks Guys
Long time lurker, first time poster. I am currently building a 3-way forecast model for a group of 27 Vendors who between them have 54 sites. Each site will require a P&L, BS and CF. I will be consolidating the sites to a Vendor level and then to an overall Group level. I will also be adding Dashboards to the front of this model.
Now the fun part, I have built out the forecast inputs tab and the first lot of PL,BS and CF sheets and my file is 3mb. My issue is these 3 sheets will need to be copied 27 x 3 and 54 x 3 times for a total of 243 calculation tabs. For some idea of size, P&L, BS and CF are around 300 rows x 142 columns with the inputs tab being 1600 rows x 20 columns.
I am after suggestions on how to handle this? should I be creating a split model? I have no experience in this and everything I have done previously tells me that external workbook linking is going to be a real hassle, especially for the end user.
Things I have done to keep the size down:
- Short (named) ranges not full columns
- Sumproduct and index match instead of sumif (where possible)
- Single variables instead of repeating (where possible)
- Simple formulas (where possible)
Any suggestions on how this would best be handled would be appreciated.
Thanks Guys
