Need to calculate estimated end date based on rate of spend

DamianK

New Member
Joined
Feb 5, 2023
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I apologize if I missed threads that discuss this, but I'm trying to calculate the estimated end/depletion date of available money based on current rate of spend.

Here's the scenario (per example screenshot):

Column C has a list of PO's.
Column D-O shows amount of money spent against each PO in a given month (again, just an example)
Column P shows the remaining amount of each PO.
Column Q is what I'm trying to figure out.

I need to figure out the formula that calculates the estimated date Column P for each PO will reach zero.

Any help would be much appreciated!
 

Attachments

  • Excel Sample.jpg
    Excel Sample.jpg
    161.3 KB · Views: 30

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
What are you using as the current rate of spend? The average over the prior 11 months?
 
Upvote 0
based on average:

Mr Excel Questions2.xlsm
CDEFGHIJKLMNOPQ
1
2PO AMOUNTJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberRemainderEst End Date
31/31/20232/28/20233/31/20234/30/20235/31/20236/30/20237/31/20238/31/20239/30/202310/31/202311/30/202312/31/2023
4$100,000$2,251$3,958$4,431$9,551$6,849$11,671$9,378$6,122$11,948$9,685$11,160$1,794$11,2022/29/2024
5$100,000$9,981$11,648$9,206$11,592$8,099$3,351$8,826$4,231$7,346$10,753$9,105$7,177$0 
6$100,000$11,698$11,855$2,020$10,456$9,665$10,790$8,490$6,290$799$10,252$9,417$11,647$0 
7$100,000$5,786$6,982$11,764$5,216$7,625$10,679$990$8,422$11,821$4,888$8,396$9,779$7,6521/31/2024
8$100,000$1,353$2,220$5,705$2,361$1,758$975$2,391$1,409$9,613$10,607$7,059$2,010$52,5392/28/2025
Sheet13
Cell Formulas
RangeFormula
E3:O3E3=EOMONTH(D3,1)
P4:P8P4=MAX(C4-SUM(D4:O4),0)
Q4:Q7Q4=IF(P4=0,"",EOMONTH($O$3,ROUNDUP((P4/ AVERAGEIF(D4:O4,">"&0,D4:O4)),0)))
Q8Q8=EOMONTH($O$3,ROUNDUP((P8/ AVERAGEIF(D8:O8,">"&0,D8:O8)),0))
 
Upvote 0
What are you using as the current rate of spend? The average over the prior 11 months?
Great question!

I forgot to mention that the number of months, or the time period the money was spent doesn't matter.

For example, if a PO for 100K was received March 14, 2023, and X amount was spent in April, May and June (or however many months), how would I calculate the approximate date the available funds would reach zero?

As money is spent each month, providing there's still funds remaining, the estimated end date would likely change depending on how much (or little) is spent in each successive month.
 
Upvote 0
So, in my example above you could have "0.00"amounts beginingin January, or will it be blank until receipts come in?
But you want the average of the months since the PO, right? and Average over those months?
So, what I have done is one scenario of many, but is it correct for that scenario?
 
Upvote 0
I went back an looked to see if the worksheet formulas I posted early worked with Blanks, Text, or Zeros begining in January, and it seems to work as well Please let me know if you have any errors with it.
 
Upvote 0
So, in my example above you could have "0.00"amounts beginingin January, or will it be blank until receipts come in?
But you want the average of the months since the PO, right? and Average over those months?
So, what I have done is one scenario of many, but is it correct for that scenario?
I've attached a new example. In it, I adjusted the start of the spending to March and ended it in June. It doesn't really matter when the spending begins because it could vary. And though it may matter when creating the formula, knowing the average spend rate doesn't matter to me.

I also limited the number of PO's in the new example to two, and I adjusted the spend rate of the PO in row 5 to show a faster rate than row 6. Based on current spend rate, the money in row 5 will run out long before the money is row 6.

Eventually there will be new spending each month until each PO is empty. But how do I calculate it?
 

Attachments

  • excel sample 4.jpg
    excel sample 4.jpg
    89.2 KB · Views: 25
Upvote 0
Okay, this is a new twist. The impression I had was that this all reviewing things after December. I need to look at it more
 
Upvote 0
how is this:
Mr Excel Questions2.xlsm
CDEFGHIJKLMNOPQ
1
2PO AMOUNTJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberRemainderEst End Date
31/31/20232/28/20233/31/20234/30/20235/31/20236/30/20237/31/20238/31/20239/30/202310/31/202311/30/202312/31/2023
4$100,000$2,251$3,958$4,431$9,551$6,849$11,671$9,378$6,122$45,7893/31/2024
5$100,000$11,592$8,099$3,351$8,826$4,231$63,9015/31/2024
6$100,000$2,020$10,456$9,665$10,790$8,490$6,290$52,2893/31/2024
7$100,000$0$0$0$0$0$10,679$990$8,422$79,9098/31/2024
8$100,000$301$1,409$98,2903/31/2033
Rate of Spend Question
Cell Formulas
RangeFormula
E3:O3E3=EOMONTH(D3,1)
P4:P8P4=MAX(C4-SUM(D4:O4),0)
Q4:Q8Q4=EOMONTH( MAX((--(D4:O4>0))*($D$3:$O$3)), ROUNDUP(P4/AVERAGEIF(D4:O4,">"&0,D4:O4),0))
 
Upvote 0
how is this:
Mr Excel Questions2.xlsm
CDEFGHIJKLMNOPQ
1
2PO AMOUNTJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberRemainderEst End Date
31/31/20232/28/20233/31/20234/30/20235/31/20236/30/20237/31/20238/31/20239/30/202310/31/202311/30/202312/31/2023
4$100,000$2,251$3,958$4,431$9,551$6,849$11,671$9,378$6,122$45,7893/31/2024
5$100,000$11,592$8,099$3,351$8,826$4,231$63,9015/31/2024
6$100,000$2,020$10,456$9,665$10,790$8,490$6,290$52,2893/31/2024
7$100,000$0$0$0$0$0$10,679$990$8,422$79,9098/31/2024
8$100,000$301$1,409$98,2903/31/2033
Rate of Spend Question
Cell Formulas
RangeFormula
E3:O3E3=EOMONTH(D3,1)
P4:P8P4=MAX(C4-SUM(D4:O4),0)
Q4:Q8Q4=EOMONTH( MAX((--(D4:O4>0))*($D$3:$O$3)), ROUNDUP(P4/AVERAGEIF(D4:O4,">"&0,D4:O4),0))
Thanks!

For column P though, I currently have a formula that auto inputs the remaining money of each PO as money is spent each, so I'm not sure what your formula for that column is for.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top