2Q: About data analysis for my company's departments

[Abdulrahman Ali]

1- I want a method to analyze expenses for each department individually, where I can set a remaining budget, and it is calculated and distributed throughout the entire year?

2- How can I change the budgets of departments using a filter within the same sheet, and have these changes reflected in the table, considering that the table already has columns for budget, actual, reserved, and remaining?

Thanks

 

[ratchet1369]

I think you're trying to create a remaining budget by level loading the remaining months with an even portion of the remaining budget. We're in September now, so maybe you want the remaining $10k to show as $2.5k/ month for the remaining 4 months, for instance. I didn't include a filter and just used =today(). You could change this part of the formula to be based on an input.

=IF(MONTH(DATEVALUE(F13&"1"))>=MONTH(TODAY()),$F$7/(12-MONTH(TODAY()-1)),0)

F13 is where I have the months named. So July, August, Sept, etc
F7 is where I put the remaining amount

Breakdown
IF(MONTH(DATEVALUE(F13&"1"))>=MONTH(TODAY()) - this is just checking whether the month is still outstanding or already passed. If it already passed, I don't want to include additional/potential spend

$F$7/(12-MONTH(TODAY()-1)) - this is just taking the remaining budget in F7 and dividing it by the remaining months left in a fiscal year (12 minus the current date then -1 to include the current month. You can adjust this if your fiscal doesn't start in January

Hope that is close to what you described.