Month End Projection formula?

[sueshe]

I am trying to set up a formula in an Excel spreadsheet to calculate daily,
the end of month projection for total patient visits. I enter manually a
daily amount, and have the formula currently set up to take the daily average
times the number of days in the month and subtract the month to date days to
get the EOM Projection. This seems to work on most of my departments,
however some of them are showing up with a lesser amount for the EOM Proj
than what is currently already totalled for Month To Date. I can't seem to
find out why??? Any suggestions, or maybe a better formula? I total the
amount at the bottom of each department for what has been entered to date,
and then I have a daily average, then EOM Proj

 

[Arvi Laanemets]

Hi

When you have Analysis Toolpack activated, then you can use EOMONTH
function.

Otherwise
=DATE(YEAR(A1),MONTH(A1)+n,0)
where n is an integer, and n=1 gives the last day of month for date in A1


Arvi Laanemets

 

[sueshe]

Thanks for the reply, and hoping not to sound too stupid, I installed the
Analysis Toolpack, but not sure what to do with it now? Sorry, I don't
understand what to do with the formula you gave me either, not sure where it
applies to what I'm trying to get? I have two fields now that I use to
calculate the month to date days and number of days in the month. This is an
example for the 9th of November for the day before at dayend:

Month To Date Days: 8 Days in Month: 30

Days Department Totals
1 1
2 2
3 2
4 0
5 1
6 4
7 2
8 3

total 15
daily avg 1
EOM Proj 22 =dly avg * days in month - mtd days

Hope this makes more sense.

 

[Arvi Laanemets]

Hi

At 1st glance, enter the formula into field, where days in month are
displayed. And to calculate last day of month (and the number of remaining
days too), you must have some date on your worksheet before.

It's difficult for me to give any more detailed instructions, because your
sheet layout remains in general a mystery for me.
What are Month To Date Days?
Where can I find some info, for which month are data meant to (year and
month, or even a date representing 1st of the month)? Cell reference for
some example, please!

 

[joeu2004]

I am trying to set up a formula in an Excel spreadsheet to calculate
daily, the end of month projection for total patient visits. I enter manually
a daily amount, and have the formula currently set up to take the daily
average times the number of days in the month and subtract the month
to date days to get the EOM Projection. This seems to work on most
of my departments, however some of them are showing up with a lesser
amount for the EOM Proj than what is currently already totalled for Month
To Date. I can't seem to find out why??? Any suggestions, or maybe a
better formula?

I suggest that you post the formula(s) that you are currently using.
What you describe is the correct algorithm. But the devil is in the
details.

One possible mistake might be in determining the number of days in the
month. That is, perhaps you are using to high a number.

Another possible mistake might be that you compute the daily average as
an integer (INT, ROUND or ROUNDDOWN). If that is rounded down from the
exact average, then you will undershoot the monthly estimate when you
multiply by the number of days.

 

[sueshe]

I wish I could post my sheet so you could see. Let me see if I can give more
detail. Cell F3 contains the Month to Date Days (=INT(DAY(A3)-1). A3
contains today's date(=TODAY() ). Month to Date days is today's date minus
one because we run our month end as of midnight, so are looking for
yesterday's totals. Cell M3 contains the number of days in the month, so for
November it's 30. Column A gives days of the month numbered down( for this
month 1-30 (cells A8-A38)). Column B has Total Patient Admits for each
day(cells B8-B38)(for example, 14 admits on the first, 19 admits on the 2nd,
9 admits on the 3rd, etc), then admit totals in Cell B39(=sum(B8:B38), Daily
Average is in Cell B41(=B39/F3) and EOM Projection is in cell B42
(B39*M3-F3)-this is the formula I'm having trouble with. Column C has Total
Patient Census days for each day(cells C8-C38), then census totals in Cell
C39(=sum(C8:C38), Daily Average is in Cell C41 (=C39/F3) and EOM Projection
is in cell C42(C39*M3-F3)-this is the formula I'm having trouble with.
Column D starts the department totals, I will use Xray for one example, it is
filled in with total Xray patients for each day, this totals in Cell D39,
Daily Average is in Cell D41 (=D39/F3)and EOM Projection is in cell
D42(D39*M3-F3)-this is the formula I'm having trouble with. Some of the
cells in my EOM formula are showing less than is currently the total or sum
for the month already. Hopefully this makes it a little clearer than mud )

 

[joeu2004]

I wish I could post my sheet so you could see. Let me see if I can give more
detail. Cell F3 contains the Month to Date Days (=INT(DAY(A3)-1). A3
contains today's date(=TODAY() ).

What you posted here is sufficient. But sorry, I should have read your
previous posting in more detail. Your mistake is obvious there. I
will explain in a response to your previous posting.

FYI, I do not believe you need the INT() function here. TODAY()
returns an integer serial number, not fractional time. That is in
contrast to the NOW() function, which does return fractional time.

 

[joeu2004]

Month To Date Days: 8 Days in Month: 30
[....]
total 15
daily avg 1
EOM Proj 22 =dly avg * days in month - mtd days

As I guessed, your error is that when you compute the daily average,
you are truncating the result -- perhaps INT(). This evident by the
fact that you compute 22 for the remainder of the month (1*30 - 8). If
you had used the exact average, you would have computed 48 or 48.25
(1.875*30 - 8). (Which is still wrong!)

Note: In your later posting, you do not show the use of INT() in this
computation. If that is true, then perhaps you have formatted the
cells to show zero decimal places, and you selected the option to
calculate Precision As Displayed (Tools > Options > Calculation). This
is not advisable in your situation, IMHO. Alternatively, you could
change the format for the Average cell in order to show some decimal
places. Or leave the Average cell as is "for show", but recompute the
exact average in the EOM Proj cell.

But you have another mistake: you are subtracting "mtd DAYS" (8)
instead of "mtd TOTAL" (15). If you had used the latter with your
truncated results, you would have computed 15 (1*30 - 15). (Which is
still wrong!)

The correct computation for EOM Proj is: (15 / 8) * 30 - 15. That is
41.25. You can choose whether to truncate (INT), round (ROUND) or
round up (ROUNDUP) the final result -- but not the intermediate average
-- depending on which makes the most sense for your process. There is
no single right answer. I would tend to round up if the purpose is
predict required resources. Better to predict more than less, IMHO.

If you need help with the precise formulas, feel free to ask. But
looking at your later, you seem to have a good grasp on the Excel
formulation. You simply need to apply some of the corrections noted
above.

HTH.

 

[sueshe]

Thanks Joe, I will work with your answer and see if that fixes my problem. I
had someone else set this sheet up and should have checked everything to make
sure it was right....hind sight ya know!! Thanks again!
--
Tasha

Month To Date Days: 8 Days in Month: 30
[....]
total 15
daily avg 1
EOM Proj 22 =dly avg * days in month - mtd days

As I guessed, your error is that when you compute the daily average,
you are truncating the result -- perhaps INT(). This evident by the
fact that you compute 22 for the remainder of the month (1*30 - 8). If
you had used the exact average, you would have computed 48 or 48.25
(1.875*30 - 8). (Which is still wrong!)

Note: In your later posting, you do not show the use of INT() in this
computation. If that is true, then perhaps you have formatted the
cells to show zero decimal places, and you selected the option to
calculate Precision As Displayed (Tools > Options > Calculation). This
is not advisable in your situation, IMHO. Alternatively, you could
change the format for the Average cell in order to show some decimal
places. Or leave the Average cell as is "for show", but recompute the
exact average in the EOM Proj cell.

But you have another mistake: you are subtracting "mtd DAYS" (8)
instead of "mtd TOTAL" (15). If you had used the latter with your
truncated results, you would have computed 15 (1*30 - 15). (Which is
still wrong!)

The correct computation for EOM Proj is: (15 / 8) * 30 - 15. That is
41.25. You can choose whether to truncate (INT), round (ROUND) or
round up (ROUNDUP) the final result -- but not the intermediate average
-- depending on which makes the most sense for your process. There is
no single right answer. I would tend to round up if the purpose is
predict required resources. Better to predict more than less, IMHO.

If you need help with the precise formulas, feel free to ask. But
looking at your later, you seem to have a good grasp on the Excel
formulation. You simply need to apply some of the corrections noted
above.

HTH.