Cash flow with a twist !!

[arishy]

I have a basic personal cash flow workbook.
Sheet 1 has the bank balance AND the amount I owe to my 2 credit cards.
I pay my CC's in FULL at a fixed monthly due date

The second sheet contains the cash flow for say 3 month ahead.

Any Income I get I record, as well any expense.

Here is the challenge: I use my CC's heavily so If I record each transaction it will ruin by Bank balance ( since they are due 30 days after the date it is recorded)

The rule is : Next CC due payment is KNOWN 25th of the coming month; so if i am recording a cc transaction it will be taken either next cc due date OR the one after it ( a month difference).

HOW can I do that.......

Rather than send you my current workbook which is useless with regard to this issue, or I wait for your input AND THEN send you my new workbook for you to review ??

 

[Chihiro]

Do upload sample of your current set up.

If you track transaction date (i.e. when cc is used) then you can calculate due date based on it. Actual calculation will depend on when the cut-off date is for your cc.

Ex: Assuming Col A has Transaction Date... and that 25th is cut-off date.
=IF(DAY(A2)<25,DATE(YEAR(A2),MONTH(A2),25),DATE(YEAR(A2),MONTH(A2)+1,25))

Will give you due date.

 

[arishy]

The reason for picking the VBA forum, is I need to do some programming to "relocate" the cc transaction on hand. Your formula IS a starting point.
Also, the reason I did not post my current WB; is I did not address the issue on hand. Namely when I record cc transaction It should be checked and relocated to the proper cell ( amount due for payment in a future cell).

I was hoping that someone here came across a similar situation. In my view WE all face this situation when when we forecast the cash flow issue.

 

[Chihiro]

Without knowing where the info is stored and where it should be moved under what condition... It is bit difficult to give specific answer/code.

 

[arishy]

Ok....I will quickly mock up a WB and I will explain the issue inside the WB...
I will be back !!!!

 

[arishy]

Here it is ...I am sure you will have zillion questions...I am ready

 

[Chihiro]

So what exactly are number in red in OUT column of CashFlow sheet?

Is it CC payment? Or is it CC amount used?

 

[arishy]

The short answer: amount used. Payment is Only on the 25th of the month and in FULL ( according to the cc rules).
The long answer is:
The OUT column shows present AND future payments(e.g Cable subscription for the next 3 month) Also includes any future fixed charges. Any expenses current and future not ONLY CC and that where the problem is.

Is it simply BAD design or what ??

 

[Chihiro]

Ok... you should only track one or the other in OUT column.

For forecasting purpose I'd say amount used, since you won't know exact amount of CC payment until you receive statement.

Track that in another column.

Another method is to track amount used in separate sheet and use the formula given in my first post to assign it to specific date. Then use Sumifs to bring the amount into your Cashflow sheet.

 

[arishy]

Great ....So:

I will separate the two kind of expenses and keep anything to do with CC in a separate sheet. The other sheet will take care of else. This one I see no problem with.
Back to the CC sheet, I will record everything present and future AND
at any time I will be able to see the effect of the credit card movement in the else sheet.

The link between the two sheets is the cells (in the else sheet) for the payment of CC ; every 25th of the month.

This is a clean approach, and I will start right away doing it.

Really appreciate your input

 

[Anil V]

Why don't you use some cash book app that helps you to keep expenses in track. Various mobile apps are available in the market and one among them is Desi Hisab-an intuitive, user friendly practical cash book app for common man.

 

[arishy]

Thank you Anil for your followup ; I will look into this app.