I have a large workbook of statements and I am trying to decide what formula combinations would work the best.
The entity has 30 partners and 10 products we report on. The main body of the statement has the same information (income statement by product) on every single tab. Each partner has their own tab with their name and their investment information (percent ownership and contribution amounts). So the only differences on each tab is the name an a single calculation multiplying the Net Income(Loss) by the partner percentage for their share.
The month end data is downloaded from the accounting software, run through Power Query to clean it up in to an Excel table format. There is also a table of product names and our entity's ownership percentage.
Each partner tab has two statements (Since Inception IS and YTD IS).
Currently we use SUMPRODUCT with the criteria that looks up the month end data and sums by general ledger account number and product name. The Since Inception report sums all the values going all the back to the beginning of the entity, for example: =-SUMPRODUCT((Balance[Amount])*(Balance[GL]=4100)*(Balance[Product]=L$17)*(Balance[Date]<=Period))
The YTD statement only sums the values that equal to the current year =-SUMPRODUCT((Balance[Amount])*(Balance[GL]=4000)*(Balance[Product]=L$17)*(Balance[Date]<=Period)*(Balance[Year]=YEAR(Period)))
Each tab has these SUMPRODUCT functions, totaling 440 SUMPRODUCT calculations on each of the 35 tabs.
Until now, it worked fine. The longer the entity lasted, the slower the spreadsheet would be to open.
Well, now we have a new entity have has 49 partners....that is a lot of SUMPRODUCT functions to calculate every month.
Is there a better calculation I can use? I thought about using GETPIVOT on each tab instead of SUMPRODUCT, would these make the spreadsheet run faster? I also thought about having the main partner (owner) have the SUMPRODUCT functions and then just refer to the cells on his tab for the remaining partners.
My new spreadsheet takes nearly a minute to open. I have attached a sample spreadsheet. The items in Yellow are what repeat on every single partner tab.
I find it difficult to relate your (perhaps overly simplified) example file to your questions/remarks. It seems you have all data (PQ cleaned) in several tables. How much data? You mention the GETPIVOTDATA function. Do you have pivot tables in the file already? If so, are they sharing the same pivot cache? Are you using Power Pivot (the Data Model)?
The final question that comes to my mind is why you want a tab for each partner to begin with. The summary doesn't look all that complex, and you could perhaps create a calculation template for a partner statement and create one for a specific partner on demand by filtering (data validation, slicers etc.) relevant data for a partner. Then you don't have to save each statement for each partner including all formulas.
A bit more context would be helpful.
Within PQ, I am connected to our accounting software via the Dataverse, since our software is using Power Platform.
It downloads all journal entry lines (for the past 5 years). It's cleaned up and the lines I don't need are filtered out using PQ. Then the lines I require are loaded to an Excel table called Balances. All of my reports are generated from this table based on the YTD Quarter.
I do have pivot tables in the file, which I use as a check to make sure my SUMPRODUCT formulas pick up the right amounts. I was just wondering if instead of using the SUMPRODUCT would it be better (easier) to use the pivot table to generate my tabs instead.
I am not familiar with Power Pivot or the pivot cache.
We create a tab for each partner because I PDF each tab and then mail them out to the partners. It is easier to select all sheets and PDF the file then it is to have one sheet and then use a data validation list or slicer and then PDF each report. Each sheet has 2 separate reports and what I provided was just a sample of the report. The are simple but they are long.
MY gut feeling says that if you can use pivot tables, go for it. I would not put any effort into creating complicated formulas.
Mynda has a blog post on formatting pivot tables that will completely transform the look of them. You'll not recognise them as pivot tables.
https://www.myonlinetraininghub.com/12-pivottable-formatting-tips
And just to be sure, you are aware that you can automatically create multiple pivot tables based on the content of the Filter field, with 'Show report filter pages' in Pivot Table options. Create one PT with Partner in the Filter field and generate a PT for each partner in separate tabs in a few seconds. Print each to PDF and delete them to keep your file (c)lean.
