Forum Discussion

James_Buist's avatar
James_Buist
Brass Contributor
Oct 06, 2024

Another Dynamic Spilled Array challenge - Multiply 2 spilled arrays while matching criteria

Unfortunately I am faced now with yet another challenge. Trying to build this fully integrated forecasting model for a small firm that is going through multiple rounds of funding. It is a full set of integrated financial statements but I am building it fully with Dynamic arrays so the entire model will be dynamic. It’s a challenge but it’s progressing well.

 

The next challenge is this:

As this firm and many like it have multiple entities around the world, I decided to build the model with sections for items such as Staff costs, overheads, revenue etc but for all entities together and under each section a summary for that line item by entity. This was instead of a sheet per entity.

 

Thus I have say IT expenses and a table of line items which can include licence subscriptions, outsourcing costs etc and against each one, an entity code. So if two entities both have an expense for say Office365 there will be two line items, each one allocated to a different entity. This means that entities that do not really have any costs in that area or perhaps revenues as they may be just an R&D unit, will have no line items under that section.

Now, when building out the forecasts for the overheads, each is driven by certain drivers. In the case of the IT costs, many are by Headcount. So I want to multiply the table of these line items each with it’s own entity code by the table of headcount per entity – all across the months or quarters. I have attached an xlsx file with an example.

I want the solution to read one table and multiply it by the other (in effect) but by matching the entities ie. UK entity has 10 people in Oct 24 and US entity has only 2.  The costs for Office365 licenses for the UK entity are GBP46 per person and the ones for the US are 60USD per person. The costs for each line item are in the local currency of that entity.

 

The additional challenge is that some of the line items will be fixed costs and not based on headcount. So a code in a column will determine that. So only the items marked as per head should be multiplied by the headcount for that entity.

Please find attached the sample sheet. My brain is cooked!!

 

Many thanks in advance

PS If I have said table anywhere, I meant spilled array

  • djclements's avatar
    djclements
    Bronze Contributor

    James_Buist For this one, you can use INDEX-XMATCH (with SEQUENCE-COLUMNS) to spill a 2D array of head counts by entity for each expense item, then use a simple IF test to multiply the cost by 1 if "Fixed" or by head count if not:

     

    =IF(C10:C16="Fixed",1,INDEX(H5:L6,XMATCH(E10:E16,E5:E6),SEQUENCE(,COLUMNS(H5:L6))))*D10:D16

     

    You can also wrap INDEX within IFNA to handle non-matches, if necessary. Cheers!

    • James_Buist's avatar
      James_Buist
      Brass Contributor

      Sorry. Huge apologies. I messed up in my explanation. Too late at night I think and brain fried. Thanks for solving this. I think I could have got a solution, maybe not as clean as yours so this is still very useful. But what I missed out was the most crucial part. The table of monthly amounts is already completed. Its not taken from the left hand column but generated from input tables with stop and start dates and manual override price changes. You actually helped me solve that in the first place!! So what I need to do is to multiply the two tables together. SO ignore the column with monthly prices (In red in the attached). It's a case of multiplying the green and blue tables together with the condition of matching the Entity and whether fixed or per headcount.

Resources