Hi Matt,

No file attached. Please click the Start Upload button after selecting your file and wait for the grey check mark beside the file size before submitting your reply.

Mynda

My bad about the attachment tried that again.

Thanks, Matt. I thought the attachment was going to be an Excel file. Can you please upload a sample Excel file containing your formula and the desired result for one row so I can see the logic.

So sorry here is the attachment to use. The tab that I am referring to is Daily CRT Dash and the monthly projected amount. Of course it'll read as what the current amount is because its the end of the month but there is a day left and still some potential for generating revenue.

Hi Matt,

Change the formula in cell M2 to this:

=XLOOKUP(C3,CRTData[Months],CRTData[Combined Running Totals],"")/IF(DAY(C3)=1,
NETWORKDAYS.INTL(EOMONTH(C3,-2)+1,EOMONTH(C3,-1)),
NETWORKDAYS.INTL(EOMONTH(C3,-1)+1,C3))

That said, I don't think it's correct to forecast the current month based on the prior months number of days when the current date is the 1st of the month. e.g. if it's the 1st of September, why are you forecasting based on August's number of days, which are 23, when September only has 22 working days.

I would calculate the average daily CRT to date in cell M2 like this:

=XLOOKUP(C3,CRTData[Months],CRTData[Combined Running Totals],"")/
NETWORKDAYS.INTL(EOMONTH(C3,-1)+1,C3)

And then in cell AC2, I would do this:

=XLOOKUP(C3,CRTData[Months],CRTData[Combined Running Totals],"")+M2*NETWORKDAYS.INTL(C3,EOMONTH(C3,0))

Mynda

I was going to say that I was forecasting the previous month if the current month read the first of the month but as I was thinking it over I realized that that information could just be looked back on. Originally my thought was the data sample was to small to forecast the current month if it was the first of the month.

I really appreciate you being so very generous with your time!

Ah, that makes sense. I agree, one day's worth of values is not much to go on. Maybe you could leave the forecast blank until you have enough data.

So I have one days worth of data for October to work with for the Monthly Projected and because math is not my strongest subject I was wondering if it is not unreasonable could you help me understand the formula that you used for the Monthly Projected? 

=XLOOKUP(C3,CRTData[Months],CRTData[Combined Running Totals],"")+M2*NETWORKDAYS.INTL(C3,EOMONTH(C3,0))

My understanding is you added the current Combined Running Totals (CRT), in this case the new data is $769,855.11, to the average CRT, $384,927.56 and then multiplied that by the number of working days in the current month.

The projected amount that is returned is $8,468,406.21

Now this is brand new to me I haven't ever worked on projected anything before this project so I am not sure what would be the right outcome to look for. Hence why I posed the question here for smarter minds.

Hi Matt,

The XLOOKUP formula finds the CRT to date, plus the average CRT to date * the remaining working days in the month. It's simply extrapolating the actual CRT. Another way to write it is to take the month to date CRT / NETWORKDAYS to date * NETWORKDAYS in the month.

I did notice that the formula should have -1 for the remaining NETWORKDAYS formula, like so:

=XLOOKUP(C3,CRTData[Months],CRTData[Combined Running Totals],"")+M2*NETWORKDAYS.INTL(C3,EOMONTH(C3,0)-1)

Mynda