Hello MyOnlineLearningHub Team,
Background Info
I am using Excel to do my company's annual forecast. As part of the exercise, we are required to calculate the cost per employee for a specific financial year, the available funds and any resulting gap/surplus.
While the document has quite number of other details, 3 columns are the most important being (1) the total cost column, (2) available funds column and the (3) variance (deficit/surplus) column.
The end result of the forecast exercise is to realize how much funded each employee is.
The Challenge
One the requirements of the exercise is to avoid any staff with a surplus (additional funds) beyond their required annual cost. Any surplus arising from any employee should be reassigned to another individual with funding gap. So basically, column J of the attached excel file can only be zero or negative figure and NO POSITVE FIGURE REQUIRED.
For example, if employee A has annual cost of $120,000 but his available budget is $170,000, then that additional $50,000 ($150,000 - $120,000) should not be shown in column J.
Now, we need excel to automatically reject the additional $50k for employee A by simply saying this person is fully funded. Instead, the additional $50k will be automatically allocated to another staff with funding shortage. We are currently doing this manually and the process is so hectic that it requires automation/formulation.
Kindly assist the best approach to resolve such matter using either Power Query, VBA, Office Script, Data Validation, etc. Please see attached sample excel file for your reference.
Best,
Mohamed
Hi Mohamed,
Your file didn't get attached. You need to click the 'start upload' button after selecting your file and then wait for the grey check mark beside the file size before clicking 'submit reply'.
That said, writing a formula that checks if the budget is higher than the annual cost and returning zero gap if it is, is easy. Reassigning the surplus funds to other employees is not. You might be able to use a running total for the gap that is offset against the surplus somehow.
Anyhow, share your file and I'll take a look to see if there is a way it can be done.
Mynda
Hi Mynda,
Thank you so much for your timely response, and apologies for missing the file attachment. Kindly find attached now for your reference. I have also inserted a comment in the first 2 cells of the document for further clarity.
Looking forward to your guidance on this.
Thanks,
Mohamed
Hi Mohamed,
Thanks for providing the file. I've converted the data in the Forecast sheet to a table and replaced the dynamic array formulas with structured references that work in Tables (dynamic arrays do not). Structured references work in a similar way to dynamic arrays, so there's no efficiency loss in using them.
You'll see there are 5 connection only queries in the file:
Absolute Gap_Surplus - this is just the data in the Forecast sheet. This query is referenced by other queries in the file.
Surplus Available - simply sums the surplus values from the Gap/Surplus column of the Absolute Gap_Surplus query.
Total Gap RT - This contains the running total of the Gap values from the Gap/Surplus column from the Absolute Gap_Surplus query.
fxRunningTotal - this function is used to perform a running total on the Total Gap RT query. The function used here is based on this Power Query Running Total tutorial.
Final Gap/Surplus - this query apportions the surplus to the employess with gaps (see Surplus Balance - Gap RT) column. The Final Gap/Surplus column is a logical test that checks if the Surplus Balance - Gap RT is >= zero and if it is, it returns a zero.
Hopefully this points you in the right direction and you can work with it to get your desired result.
Mynda
Hi Mynda,
Thank you very much for your time and guidance on this. Your support is highly appreciated. This gives a good guidance and direct me the right approach in resolving the matter. I will take it from here and will further explore.
Thanks again.
Mohamed
