Financial model - workbooks vs worksheets

[shakethingsup]

Hi - I've recently joined a company. We won't be able to implement any robust budgeting and planning tool so we are in excel. There is an extremely tight deadline and I'm just beginning to learn my predecessors excel financial models. from the existing files, I see 5 or 6 workbooks with about 50-60 worksheets across them (maybe more as I think some are hidden). Some more background: 1 consolidated entity, 3 divisions comprised of 10 separate legal entities, assumptions for headcount and other line items, sensitivity analysis is required.

I hope in the next 12-15 months we are able to implement a new ERP and budgeting and modeling tool.

My questions are:

1. is it better to have multiple workbooks or house everything in worksheets?
2. is there a maximum number worksheets?
3. what else would come to your mind with structuring this? or what advice can you provide? or what resources/websites could you recommend?
4. what more information can I provide to you gurus?

Please and thanks!

 

[TheVillageIdiot]

Lots of assumptions in the following, so look at this with an open mind.

1) Assume moving to database and querying is not available due to the tight time deadlines.
2) Multiple workbooks vs worksheets is dependent upon data amounts and any need for protection (e.g. managers do not have access to departments not under their purview, etc.). Multiple worksheets in one workbook (or one per division) would be better for a basic refactoring.

Above all, separate data from primary calculations from reporting. Input >> Black Box >> Output (though the calcs need not be a black box). If you can, get the data in one data dump, or have the ability to append to existing data.

The data should be inserted in an identical manner -- the ETL process should be simple at this point. Get the input for each individual entity at the lowest lowest level and make sure each one is sent identically -- every single column is the same. Every single line item is the same. The rollup structure can be independent of the data (stats and targets might not be able to roll up, but handle those as needed -- calendar days in a period doesn't roll up, for instance).

The output should be identical as well. You could have a single report with selections for what data is shown. The key is that the output is identical. Gross revenue is one line 50 for every single output you use. Current period is in Column AA, for every single report, with future projections in the subsequent columns, and historical values in the earlier columns.

The structure is identical -- rolling 12 months history, current period, future periods, for example. Not, "Start at the FY beginning for Entity 1, Two years history for Entity 2, etc."

HTH for a start. Good luck.

 

[shakethingsup]

Lots of assumptions in the following, so look at this with an open mind.

1) Assume moving to database and querying is not available due to the tight time deadlines.
2) Multiple workbooks vs worksheets is dependent upon data amounts and any need for protection (e.g. managers do not have access to departments not under their purview, etc.). Multiple worksheets in one workbook (or one per division) would be better for a basic refactoring.

Above all, separate data from primary calculations from reporting. Input >> Black Box >> Output (though the calcs need not be a black box). If you can, get the data in one data dump, or have the ability to append to existing data.

The data should be inserted in an identical manner -- the ETL process should be simple at this point. Get the input for each individual entity at the lowest lowest level and make sure each one is sent identically -- every single column is the same. Every single line item is the same. The rollup structure can be independent of the data (stats and targets might not be able to roll up, but handle those as needed -- calendar days in a period doesn't roll up, for instance).

The output should be identical as well. You could have a single report with selections for what data is shown. The key is that the output is identical. Gross revenue is one line 50 for every single output you use. Current period is in Column AA, for every single report, with future projections in the subsequent columns, and historical values in the earlier columns.

The structure is identical -- rolling 12 months history, current period, future periods, for example. Not, "Start at the FY beginning for Entity 1, Two years history for Entity 2, etc."

HTH for a start. Good luck.

Okay great ! I started that today - standardizing, same rows, same columns, etc.

Im going to go with one workbook, multiple sheets.

and keep thinking about the design !

Thanks !!!