Help with organising data using pivot table

[King100]

I have a large amount of data which I would like to summarise. The data relates to investment consortiums, each of which comprises of a number of companies which have a % stake in the consortium. Each consortium is in a separate row of the table. The columns of the table contain the names of the companies within each consortium and the % stake each has in the consortium. There are also columns showing the amount each consortium will invest under various scenarios. I have shown a sample table using dummy data below.

Consortium	Company 1	Company 1 %	Company 2	Company 2%	Investment Amount (Scenario 1)	Investment Amount (Scenario 2)
101	AB Co	50%	CD Co	50%	500,000	1,000,000
105	CD Co	70%	EF Co	30%	500,000	-
107	GH Co	100%			300,000	-
103	EF Co	50%	GH Co	50%	250,000	-
104	AB co	100%			450,000	1,000,000
				Total	2,000,000	2,000,000

<tbody>
</tbody>

I would like to summarise the data by company as shown below. So total invested for company AB Co for Scenario 1 is sum for all consortiums of the % of each consortium AB Co owns multiplied by the corresponding Scenario 1 investment amount for that consortium.

Company	Total Invested (Scenario 1)	Total Invested (Scenario 2)
AB Co	700,000	1,500,000
CD Co	600,000	500,000
EF Co	275,000	0
GH Co	425,000	0
Total	2,000,000	2,000,000

<tbody>
</tbody>

For the sample this result can be achieved conditional sumproduct formulas but the actual data set has many companies per consortium and there are potentially a large number of scenarios. This makes the calculation route cumbersome (unless someone has an elegant solution) and I was thinking a pivot table may be the best route.

I have not however successfully been able to se tup the pivot table to give me the desired results and would appreciate any assistance with this.

Many thanks