I am preparing a summary net cash flow sheet.
I have total bank balance as at TODAY () which needs to be added to (accounts receivables) or subtracted from (accounts payables) on a daily basis projecting forward 30 60 and 90 days net cash position.
Each day the sheet needs to adjust for items that move from one period of time to another.
I have organized my workbook as follows:
1. first sheet has the database or "details" here lies all my data i.e. accounts receivables and account payable.
This data is arranged as follows:
first colum is titled "code" indicated what type of item it is. It could be an expense such as salary or tax payment or out of pocket expenses. I have a different code for each type of expense or income. For example travel expenses is code =1, salary =2 etc.
Next to each item there is the amount and the date the payment is due.
I want to summarize my data so that there are subtotals for each category of payable/receivable. Thus I need to add rows that all have the same "code" but divided up based on dates i.e. payable within 30 days; payable within 60 days and payable within 90 days.
I want to set it up so that all I have to do is type in new items to the "details" list by entering in the code, amount and date payable and the sheet calculates subtotals and totals automatically. When I need to report net cash all I do is go to my online bank account and enter in the cash balance on that day to the excel sheet and automatically I can present the net cash position for 30/60/90 days.
The only problem I am having is setting up the date criteria so that the sum is only on those amounts whereby the code is the same for example 1 and the dates fall within the range FROM TODAY() TO TODAY()+30. THANKS DANIEL
Please let me know what email address I can send the excel sheet to.
