hi Excel gurus,
I am trying to spread the total variance between budget & actual spend for two items in the attached spread sheet.
The way it works is as:
total variance calculated as: (Actual FO% - Budget FO%) * Actual total spend = -$200 in the attached.
There are two items.
I need to allocate this -$200 to these two items. When I use the above formula for each item, I get the allocation as $500 each (as shown)
Is there an excel formula that can calculate this allocation amount for each item so that the total allocation comes to -$200. This is more of a complex exercise when there are around 1000 items involved.
Thank you.
SBook1.xlsx
Bookmarks