Excel Financial Model - 1000rows, 400 columns x 50 tabs

neomilan

New Member
Joined
Oct 26, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello All

My financial model is 100mb, it has 50 identical tabs which calculate cashflows monthly for 25 years (so around 350+ columns) and 1000 rows in each tab.
This means it is slow to open, close and to make any changes to the 1 generic template tab, it means i have to copy it again 49 times!

I have been careful not to use any volatile formula, no tables, no conditional formatting, no vba, no links, no named ranges.
The model is for a commercial real estate development. It's difficult to have the data/calculations in another format like a database because it will be used by Excel able only persons, it has calculations in each of those 50 tabs.

What else can i do to increase the efficiency of this file?

Thank you for any helpful suggestions :)
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

RockEd

New Member
Joined
Aug 13, 2021
Messages
40
Office Version
  1. 365
Platform
  1. Windows
Without seeing it, it's hard to imagine where to improve. But 50 tabs sounds completely crazy. Unless you have some time on your hands as well I think it will take a fair amount of time to sort out.

A few thoughts:

  • I think the lack of VBA is one of your issues (coupled with the number of tabs).
  • Where I use big data sets, I will try where possible to keep things into one tab, and only more where it is really necessary.
  • There's a chance you are using formula's that are slow and old.
  • Do need monthly cashflows or is this just to help to work out if you are calculating things correctly? It might be best to rethink of formulas that can calculate the whole year without the need for 12 columns (for each month).
  • You could have it to calculate manually, so it's not recalculating the whole spreadsheet when you open it.
  • If you are copying and pasting between 50 tabs; the very least you can do is automate this with VBA. You are prone to make mistakes (missing some out for example) by doing this.
Good luck...
 

neomilan

New Member
Joined
Oct 26, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Without seeing it, it's hard to imagine where to improve. But 50 tabs sounds completely crazy. Unless you have some time on your hands as well I think it will take a fair amount of time to sort out.

A few thoughts:

  • I think the lack of VBA is one of your issues (coupled with the number of tabs).
  • Where I use big data sets, I will try where possible to keep things into one tab, and only more where it is really necessary.
  • There's a chance you are using formula's that are slow and old.
  • Do need monthly cashflows or is this just to help to work out if you are calculating things correctly? It might be best to rethink of formulas that can calculate the whole year without the need for 12 columns (for each month).
  • You could have it to calculate manually, so it's not recalculating the whole spreadsheet when you open it.
  • If you are copying and pasting between 50 tabs; the very least you can do is automate this with VBA. You are prone to make mistakes (missing some out for example) by doing this.
Good luck...

thank you for your response, thats helpedul
- the actual input data is very small and is contained in 1 tab, this feeds into the other 50 tabs which have the calculations
- i have used very basic formula across that template tab (which is copied 50 times) and nothing volatile or longer than 1 line
- yes unfortunately it needs to be a monthly cashflow that runs from 2021 to 2045 !!
- always set to manual yes
- the copy and paste only needs to be done once, i just make sure i have the 1 template tab set up to be correct and then when happy with that i copy it 50 times and it then looks at 1 cell in thetab to draw the input data from the input tab
 

RockEd

New Member
Joined
Aug 13, 2021
Messages
40
Office Version
  1. 365
Platform
  1. Windows
Perhaps your formula is too basic and more advanced formula would condense it. Again, without seeing it, it's hard to really comment. Then again, I won't personally have the time to analyse it but someone else might.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,825
Messages
5,766,666
Members
425,367
Latest member
Boboka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top