I have been tasked with calculating the months left on a Task Order based on the Funds Remaining and the amount spent. I wrote queries that give me the TO original value, FundsRemaining, the amount spent, and the percentage spent and how many months the money was spent in but I am having trouble extrapolating how many months of funding remain at the current consumption rate.
-
-
The formula to extrapolate how many months you have left is (funds remaining) divided by (funds spent per month) -
The problem lies in trying to determine funds spent per month. I have the first and last months that funds were spent on a Task Order (and the difference between the two), but that is not necessarily the same as funds spent per month. For example say TO# 0001 has a difference between first and last months that funds were spent of 14, but drilling down further through the data, it can be seen that the funds were actually only spent on two months during this period.Comment
-
Can you determine the Funds Spent Per Month by dividing the Total Spent by the count of months/transactions?FundsSpentPerMo nth = TotalFundsSpent / Count(Transacti ons)Or is this going to give you an erroneous result also?Comment
-
There's nothing you can do about erratic spending. You can only go by the average spending per month.
If there's a pattern to the spending, then you can use the pattern to predict when the funds will run out. If there's no pattern to the spending, then it's impossible to predict anything, so your only option is to go with average spending.Comment
-
Yeah, it is kind of a GIGO scenario. Thanks for the advice.Comment
Comment