I need help with the following situations and hope you can help. I am writing a cash flow spreadsheet and am having a proble with the reliance of each item.
Here are the scenarios:
Lets say I have three months (January, February and March). At the bottom of each month is the net cash flow.
Jan Feb March
Net Cash Flow 4000 -5000 2000
I would like to take that net cash flow and have it populate three buckets below and they are (Working Capital, Line of Credit and Take Home)
So now the sheet looks like this:
Jan Feb March % to save = 20%
Net Cash Flow 4000 (10000) 2000
Working Capital 2800 0 0
Line of Credit 0 7200 6800
Take Home 3200 0 1600
In the above example lets say I started out with 2000 in working capital for the year. Since I cashed flowed in January $4,000 I do not need to take anything out of working capital instead I can put some in. I already have set up the sheet to add all the positive cash flow months and all of the negative cash flow months to give me a percentage that I need to save each good month to prepare me for the bad months which is above. So for January I will take 20% of the $4000 and put it back into working capital and the 80% I can take home. I do not need to use my LOC because I still have working capital.
In February though I am negative in Cash Flow. I need to first exhaust my working capital and then engage my line of credit for the differeence and obviously I am not taking any money home.
In March I cash flowed again so 80% went to Take Home and the 20% went to pay the LOC down. In my business the summer months are great and I will easily repay the LOC but once the LOC is paid off the 20 % needs to go back to working capital each positive month.
If you have gotten this far then maybe you can help. I greatly appreciate it.
Thanks!
Here are the scenarios:
Lets say I have three months (January, February and March). At the bottom of each month is the net cash flow.
Jan Feb March
Net Cash Flow 4000 -5000 2000
I would like to take that net cash flow and have it populate three buckets below and they are (Working Capital, Line of Credit and Take Home)
So now the sheet looks like this:
Jan Feb March % to save = 20%
Net Cash Flow 4000 (10000) 2000
Working Capital 2800 0 0
Line of Credit 0 7200 6800
Take Home 3200 0 1600
In the above example lets say I started out with 2000 in working capital for the year. Since I cashed flowed in January $4,000 I do not need to take anything out of working capital instead I can put some in. I already have set up the sheet to add all the positive cash flow months and all of the negative cash flow months to give me a percentage that I need to save each good month to prepare me for the bad months which is above. So for January I will take 20% of the $4000 and put it back into working capital and the 80% I can take home. I do not need to use my LOC because I still have working capital.
In February though I am negative in Cash Flow. I need to first exhaust my working capital and then engage my line of credit for the differeence and obviously I am not taking any money home.
In March I cash flowed again so 80% went to Take Home and the 20% went to pay the LOC down. In my business the summer months are great and I will easily repay the LOC but once the LOC is paid off the 20 % needs to go back to working capital each positive month.
If you have gotten this far then maybe you can help. I greatly appreciate it.
Thanks!
