Distribution of Budget based on department performance

[Oak Ville]

Hello,
I am supposed to distribute a budget based on department performance. Can you please help me figure this out.

Thanks in advance

 

[oldchippy]

Hi Oak ville,

Try this in G6 copied down

=($B$2/SUM($D$6:$D$10))*D6

 

[Oak Ville]

Thanks oldchippy, my bad for not explaining it properly. Above formula doesn't take into consideration the HC. Sales Dept. budget with 195 HC comes to $147k vs. HR with 14 HC has ~112k and finance with 17HC has same budget as HR.

 

[kchiba]

Hi Oak Ville,

The problem I am having is seeing how the various Dept performance translates into the Total performance, and without this it would be difficult to allocate the Budget.
I tried to split the Budget of 600,000 into a base of 570,747.66 (600,000/107*100) and the balance of 39,252 and then allocate the 570,747 per the headcount and the balance for performance, but could not come up with a relationship for that.

cheers

kanti

 

[Oak Ville]

Hi Kchiba,
Various Dept. performance is the Dept. P&L (dept names and performance are all fictional) but I can't link the Dept. performance to total revenue. Obviously Sales as an example is going to have more revenue but their performance to plan is 125%.

Thanks you.

 

[kchiba]

Hi Oak Ville,

In that case I do not think we can propose how your company should allocate the budget based on performance

 

[SirJB7]

@SirJB7
Hi, myself!
So long...
Do you remember Richard Feynman's quote sometimes missattributed to & disputed with Albert Einstein that says:?
"You do not really understand something unless you can explain it to your grandmother.

• variant: If you can't explain something to a six-year-old, you really don't understand it yourself.
• variant: If you can't explain it simply, you don't understand it well enough."

Regards!

 

[Oak Ville]

Hi,

I totally agree with you, except I know what they are asking for and if I put it in a spreadsheet that means I know what the end result is it's just a matter of figuring it out. If it is a fair way of distributing the budget or not -line of sight, etc. - is a different discussion.

Thank you for your input.

 

[Oak Ville]

Thanks to everyone who tried to help me with this. I solved it.
see attached.

 

[oldchippy]

Congratulations Oak Ville, now you can celebrate Christmas