Projected cashfllow for balance of working days left in month

pannells

New Member
Joined
Jun 14, 2009
Messages
41
I have tried with all my knowledge to try and figure this out but with no success.:mad:
Scenario.
March has 31 days in the month (this would be entered into the alllocated cell)
We do not work Sundays so less 5 Sundays (this would be entered into the alllocated cell)= 26 Working days.
Last Cash-up complited was on the 20th of March.(this would be entered into the alllocated cell)
Avererage turnover per day is $1350.00 (This amount would be pulled from an allocated cell)
Result should be the following.
31-20(last cash-up day enteted)-2(two non working days left in month)=9
9 x $1350 = $12,150.00.
I have been using =((EOMONTH($D$45,0)-$D$45)-B47)*(1350)
D45 = last cash-up day (20)
B47 = Number of non working days in month(5)
Result is obviously $8,100 and not % 12,500 as it should be.
I have no idea how to work backwards for sundays or count how many Sundays in month and subtract how many are left or have past.
Please assist.
 
Hello,

There is a roundabout solution, because NETWORKDAYS removes Saturdays as well as Sundays, so the remaining Saturdays have to be added back in.

In order to do this, a two-column table will need to be set up separately, or in a hidden sheet. Mine starts from M3.

A2 = cash-up date
B2 = remaining days
C2 = turnover per day
D2 = result.

M3 onwards = first to last days of the month
N3 onwards = the day of the week.

The formulas are:

A2 = (you add the date)
B2 =NETWORKDAYS(A2+1,EOMONTH(A2,0))+COUNTIF(N3:N33,6)
C2 = (you add the turnover per day)
D2 =B2*C2

M3 (first day of the month) =DATE(YEAR($A$2),MONTH($A$2),1)
M4 (second onwards) =IF(M3=EOMONTH($A$2,0),"",M3+1)
N3 (format as dddd) =IF(M3="","",IF($A$2 > M3,"",WEEKDAY(M3,2)))

I hope this helps.
 
Upvote 0

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