bfaustino711
New Member
- Joined
- Oct 23, 2017
- Messages
- 1
Hello,
My name is Faus from the Philippines and I was looking for excel experts who can help with my report, and I found your web! :D
I have this report where 75% of it is manually inputted. I also have to manually edit the formulas on a monthly basis.
To give you a sample, during January I had $10,000 net income.
But I can only get the cash after 51 days, and at the last day of the month only.
So every last day of every month, I need to compute how much cash I can get from the 10,000 earned last January, based on 48 days. If the 48th day falls in the middle of month, the cash will be divided and paid into two separate months.
Sample computation:
January - 31 Days - 31 days Total 10,000 income - No cash Yet
February - 28 Days - 59 days total (Can now get 11 days worth of cash, 59-48 days) (I get 11*10,000/31 days = 3,548.39)
March - 31 Days - 90 days total (can now get 20 days worth of cash, 31 - 11 days) (I get 20*10,000/31 days = 6,451.61)
Above scenario is repeated every month. Attached Google Sheet is my report with above scenario.
https://docs.google.com/spreadsheet...mT8hHcOjIwrTubeRT63BzDYAqKc/edit#gid=62653612
Also, the 48 days delay, will be changed every quarter.
This is very manual which I'm fine of, but I believe that there are a lot of things excel can do that I haven't discovered yet. So this is why I'm asking for your help. Thanks.
My name is Faus from the Philippines and I was looking for excel experts who can help with my report, and I found your web! :D
I have this report where 75% of it is manually inputted. I also have to manually edit the formulas on a monthly basis.
To give you a sample, during January I had $10,000 net income.
But I can only get the cash after 51 days, and at the last day of the month only.
So every last day of every month, I need to compute how much cash I can get from the 10,000 earned last January, based on 48 days. If the 48th day falls in the middle of month, the cash will be divided and paid into two separate months.
Sample computation:
January - 31 Days - 31 days Total 10,000 income - No cash Yet
February - 28 Days - 59 days total (Can now get 11 days worth of cash, 59-48 days) (I get 11*10,000/31 days = 3,548.39)
March - 31 Days - 90 days total (can now get 20 days worth of cash, 31 - 11 days) (I get 20*10,000/31 days = 6,451.61)
Above scenario is repeated every month. Attached Google Sheet is my report with above scenario.
https://docs.google.com/spreadsheet...mT8hHcOjIwrTubeRT63BzDYAqKc/edit#gid=62653612
Also, the 48 days delay, will be changed every quarter.
This is very manual which I'm fine of, but I believe that there are a lot of things excel can do that I haven't discovered yet. So this is why I'm asking for your help. Thanks.
