Excel Statistical Error - The sum is not reconciling to the total

[20corcaigh]

Hi all,
I have been starring at the screen for 2 hours and this is sending me crazy.
I would upload the excel file but I don't see an option for it.
Essentially I am look at our budget cost for 2021 and looking to increase it based on the tonnage increase we are seeing.
You can see both sites are increasing in tonnage by 20% and 50% respectively.
However when I use these percentages to calculate the "updated Budget" the sum of the factories equal 4200 whilst the TOTAL equals 4,154.
I know it is related to how the % on each line is calculated vs. the total but I don't know how to adjust the individual lines so they sum to 4,154.
Any help greatly appreciated.

 

[Toadstool]

Hi 20corcaigh,

You can't use the total Actual v Budget as the individual budget % values have different impact depending on the original budget value.

 

[20corcaigh]

I know that. That's why I'm on here. I'm seeking a solution.

 

[Toadstool]

20corcaigh.xlsx
	A	B	C	D	E	F	G
1	Factory	Budget	Actual	Budget Tonnes	Actual v Budget	Actual v Budget %	Updated Budget
2	A	1000	60	50	10	20%	1200
3	B	2000	120	80	40	50%	3000
4	Factory Total	3000	180	130	50		4200
5	TOTAL	3000	180	130	50	40%	4200
Sheet1 (2)

Cell Formulas
Range	Formula
E2:E3	E2	=C2-D2
F2:F3	F2	=E2/D2
G4,B4:E4	B4	=SUM(B2:B3)
G5,G2:G3	G2	=B2+(F2*B2)
B5:E5	B5	=SUM(B2:B3)
F5	F5	=(SUM(G2:G3)-SUM(B2:B3))/B5

 

[20corcaigh]

Very close but I need the sub total of factories to equal 4154 not 4200.