Creating a master average financial statement for multiple companies doesn't work

[davegrun]

I have about a dozen different financial statements (Income Statement, Balance Sheet, Cash Flow Statement) that cover a 10 year period (2014-2023). Half of the companies have numbers for the full 10 years, and the other half are staggered, meaning their first year of operation does not begin until 2015/2016/2017, and the previous years have blank numbers.

I am creating an average financial statement where each cell just runs the average of all the financial statements. It works fine for the first two years, but as soon as the average formula starts factoring in the staggered companies which have a later start year, the cash on the balance sheet no longer matches the cash amount on the cash flow statement for those years going forward. The first two years balance perfectly before any of the staggered companies are factored in.

Does anyone have any idea how to solve this? I am just using a standard average formula and have no idea how I can get the cash flow to match once I factor in the staggered companies.

[FDibbins]

Hi and welcome to the forum

Without seeing a sample of the data you are working with, its really hard to offer suggestions for a question like this But I also understand that the data may be sensitive and it may take some effort to mock-up something similar.

Any chance of a sample workbook?

[davegrun]

Hi FDibbins, thank you so much for following up. To be perfectly honest, the numbers are extremely sensitive, and our models are heavily customized to our needs and completely proprietary, so I don't feel comfortable posting even a mock up.

To try to explain this a bit better, these are projected financial statements. We have about fifteen 10 year projected models (the projected financial statements), and I made one "master" set that averages all fifteen together to give me one average financial statement projection. Half of the projected models do not run the full 10 years, but will start in year 2, year 3, year 4, etc.

Each individual model passes my balance check (checks to make sure assets = liabilities) and my cash check (makes sure that the closing cash on the cashflow statement equals the cash on the balance sheet). But as soon as I run the average projection in my master model, the years that the staggered models come in throw off the cash so it is not balanced. I am not sure what is causing this since each individual model "checks out" by themselves?

[FDibbins]

have you tried using countifs() (although that doesnt work across worksheets
or parhaps do a sum()/a count()

[davegrun]

They are all in the same worksheet.

Can you elaborate?

[FDibbins]

Just to clarify the terminology,
Workbook = file
Worksheet = tab inside a workbook/file