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

​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 advancePS If I have said table anywhere, I meant spilled array  Read More

​