Formula to apportion sales based on division & budget

[melvin]

Gday All,

If Sales fcst is inputed for the particular Division. It should use the the same proportion budgeted for each store for the respective division and spread it out

i.e. If Div N is forecasted at - 4000, Budget is 3042 on 24 stores. I have populated the result.

But I am not sure how if all 4 divisions populate their sales, what formula can be used to apportion it according tot heir budget

File attached.

Appreciate any assistance

Melv.

 

[Hui]

E4: =D4/SUMIFS($D$4:$D$68,$C$4:$C$68,C4)*INDEX($K$3:$K$6,MATCH(C4,$I$3:$I$6,0))
Copy down

 

[melvin]

Champion. Thanks mate

Melv.

 

[melvin]

Hi team,

Hui assisted me in apportioning Sales by division. Can I get some assistance to come up with a formula which can apportion by Division & State ?

thanking you in anticipation.

Melv.

 

[melvin]

Just updated the file.

Melv.

 

[Hui]

F4: =E4/SUMIFS($E$4:$E$68,$D$4:$D$68,D4,$C$4:$C$68,C4)*SUMPRODUCT(($J$11:$M$16)*($J$10:$M$10=$D4)*($I$11:$I$16=$C4))
Copy down

 

[melvin]

Hui, What a champ you are.

This is absolutely fantastic...Tried all possible formulas from your initial one which you assisted me with...Just couldn't get it through.

This is great.

Cheers,

Melv.