Management accounting presented inunderstandablehumanaccessiblelanguage.
Management accounting presented inhumanaccessiblelanguage.
This is another version of the operation plan table (a continuation of the previous article about the operation plan).
Operation plan for the period
Let’s overview it in detail.
Line 2 “Current date” – the current date automatically updates in this table in cell D2.
Cell D3 “Beginning of the planned period” – you indicate the date of the beginning of the period here.
Cell D4 “End of the planning period” – you indicate the date of the end of the period.
When you have indicated these dates, the names of the tables and the data in columns A and I “Date” are automatically changed.
The table of cash balances – in line 3 you indicate the balances of the bank and cash desk at the beginning of the planning period. The balance at the end of the period is calculated automatically and colored in red if the balance is less than zero.
Now we turn on to the “Planned income” table.
Column A “Date” – the date of payment is indicated here. Originally, the column automatically shows the first date of the planned month. In the process of month planning, you need to indicate required dates on top of the formula in the table cell. All cells below corrected one will show the date you have already specified. Exception: cell A9. The date cannot be changed here.
Columns В “Bank” and С “Cash desk” – you write here the amount of the expected income.
Column D – the counterparty is indicated here.
Column E – you describe here the purpose of the payment. For example, “For provided services.”
Column F “Date of actual payment” – here you need to indicate the date when the operation was made. That is an important column, and if you do not fill it in, the file hints will not work correctly and you will be confused by the file.
Column G “Payment status” – the status of the planned operation is automatically indicated. Depending on the data in this column, the corresponding line is colored either in red or in green.
The next table is “Planned expenses.”
The meaning of the columns is exactly the same as in the previous table, only with the condition that expenses are planned.
Example of filling
I scheduled payments in April with the operation plan template shown in Table 1. First of all, I selected the planned period in table cells D3 and D4. After that, the table headings change to the current ones, and all data in the column “Date” takes on the value of the planned period. Then, I plan payments. Moreover, at the time of planning, for income transactions was the status “Awaiting receipt” and for expenses transactions “Payable.”
The current date changes in table cell D2 and the status of the payment changes accordingly in the process of fulfillment of the April plan. Pay attention to line 10 in the income table and line 9 in the expense table. These payments were not received and made as planned on 14/04/20 and 13/04/20 respectively. Therefore, there is no date in the column “Date of actual payment,” and the current date (table cell D2) is already 15/04/20. That is why the payment status automatically changes to “Payment is not received” in the income table and “Overdue payment” in the expenses table and these lines turn in red.
There are dates of actual payments for all fulfilled transactions and they should be entered in the appropriate column. When you set the date of the actual payment, the transaction receives the status “Paid up” or “Payment received” and is automatically colored in green.
If the amount of planned payments exceeds the balance of funds in the cash register or on the current account, then table cells I4 and J4 will turn in red.
In addition to that, look at line 7. Table cells B7, C7, E7, F7 contain a formula that automatically calculates the amount in the column below for the filtered lines. You can filter using the auto filter set in line 8. For example, in the column “Payment status” you can select only those payments that are overdue or only those that have been paid up. The above table cells will calculate only the selected amounts according to the filter. It is also possible to filter according to the color of the table cells.
When you need to prepare a file for the next month, it is enough to delete the data from the columns В, С, D, Е, F, J, K, L, M, N, in the main table, and change bank and cash desk balances at the beginning of the new period in the table cells I3 and J3, as well as remove the dates of the planned period from table cells D3 and D4.
You also need to control the columns A and I “Date.” While scheduling the previous month, you changed dates in many columns over formulas. To return the dates to their original condition, you should:
After that, this file is ready to use again.
Preparing a file for a new month, do not change anything in the name of the tables, just change the beginning and end of the planned period in cells D3, D4 and the names will be changed to the actual one automatically.
Pay attention, this file was made in Excel 2016. In older versions of this program, some of the conditional formatting functions may not work and the lines in the table may not be colored on your device.
You can view another version of the operational plan here.