When calculating cash flow it is always important to observe deficitlessness according to its total net accumulated balance (which is Debit the balance of 51 accounts).
If, according to the forecast, a minus appears in the accumulated cache, then it always needs to be blocked in the Inflow by some additional source. If there is no such source initially, then the need for money should be covered by bank loans (with the hope that banks will approve such a scenario), provided that it is necessary to maintain the further activities of this organization in the planned periods. Or, in order not to bring finances to this, it is more logical to simply cut costs then.
But the point, in this case, is not this, but how to automate the process of overlapping such emerging cash flow deficits, built in Excel with the help of a built-in macro feature. Moreover, when the need for additional bank financing also provides an increase in the total expenses of the company in the current period, by the amount of financial costs (percent of the bank) from this manipulation.
Loans taken to cover the deficit of the current period (if any) should be repaid subsequently, taking into account the amount of accrued interest. And such a process from period to period in Excel can be easily and simply configured using a written script in a macro. This video briefly describes the principle and approach to solving this problem.
As a result, this algorithm allows you to correctly calculate the cash flow, with the correct output of the balance of 51 accounts and the amount of credit debt of the organization during financial and economic planning.
An example file can be taken here.
