October 21, 2014 at 10:38How I did management accounting in Excel
I work as an ordinary analyst and it so happened that in the summer of 2014, participating in one e-commerce project, I made management accounting in MS Excel on my knee for 3 weeks. I have long planned and finally decided to put it on Habr. I think it will be useful for small entrepreneurs who understand the importance of managing financial flows, but do not want to spend a significant amount of time and money on managing accounting. I do not pretend to be the ultimate truth and will be happy with other decisions proposed by the community.
The business to which I had a relationship in the summer was the usual online store of premium and higher clothing with a turnover of about 1 million rubles a month. The business worked, not to say that it was very successful, but it worked and continues to work. The owner understood the need for management accounting and, with this understanding, took me as financial director (analyst / manager ...), since the previous one left the business 3 months before my arrival. Actually, a hole of the same duration was also managed by management accounting. Looking ahead, I’ll say that I didn’t eliminate the hole (we decided not to tear the past), but I created a system that successfully works with minimal labor to this day.
My predecessor led a manager in Fyngrad, which turned out to be a very powerful tool. For example, it allowed you to automatically load information from 1C and statements of different bank clients, creating postings according to predefined rules. The thing is certainly useful, however, subject to the double recording system, it increased the operating time by several times. To avoid an increase in work, this tool allowed the generation of “dependent transactions”. The dog was buried in the creation of these additional postings. And then it turned out that behind all the power of Fingrad was unique, which caused a complete lack of expertise in the public domain. Ordinary users (who, incidentally, paid 3,000 rubles a month for accessing the system) had access only to the “User Guide” on the official website, and 6 video lessons there. Youtube, which provided access to a couple of dozen video tutorials, also did not help much. There were no forums with information on how to ... in principle. Support, specific questions about the rules for creating “dependent transactions” and requests for help in my case, were frozen with the phrases “we have not concluded a support agreement, so we are not ready to answer such specific questions”. Although it would seem - what is specific in such requests, and even with screenshots on my part? It is clear that everything can be beaten with your hands, but the question is, why then generally pay for a tool that greatly increases the time required for managing and does not give any advantages for small businesses? therefore, we are not ready to answer such specific questions. ” Although it would seem - what is specific in such requests, and even with screenshots on my part? It is clear that everything can be beaten with your hands, but the question is, why then generally pay for a tool that greatly increases the time required for managing and does not give any advantages for small businesses? therefore, we are not ready to answer such specific questions. ” Although it would seem - what is specific in such requests, and even with screenshots on my part? It is clear that everything can be beaten with your hands, but the question is, why then generally pay for a tool that greatly increases the time required for managing and does not give any advantages for small businesses?
Having convinced the owner of the inappropriateness of using Fingrad with such volumes of business and unloading all the information from the system, I put a BIG and fat cross on it. Moreover, the decision to leave for MS Excel was not spontaneous. Good googling on the subject of management accounting, I found monsters similar to Fingrad , or links to web applications for maintaining personal finances, while the main requirements for the system were:
- the ability to maintain BDS and BDR based on a revised chart of accounts;
- simplicity in the further management accounting (including by the forces of "financially illiterate" users);
- flexibility (the ability to expand / remove functionality on the go);
- lack of congestion of the tool / interface.
To begin with, we’ll clarify the terms: being a non-financier, I mean “Balance of Cash Flows of Funds” by BDS, BDR - “Budget of Income and Expenses”. We consider the BDDS to be the cash basis (the day of the operation — the column “Date of Operation”) and use it for operational day-to-day planning, and the BDR by the accrual method (column “The accrual period”) for strategic, within a year or more.
So, how it works and how it works (ideally):
1. Management accounting is collected on the basis of information entered by end users using a form in Google Docs. The field names and encoding options are marked in red in the final file of management accounting - a kind of mapping of fields.
2. In the end, it looks like this (the green is transferred to the final management file).
3. Management accounting is built on the basis of .xls unloading from Fyngrad (hence the strange names for external users and, in general, an excessive number of columns). We kindly ask you not to take seriously the values of the columns "Parish", "Consumption" - much has been randomly changed.
The filling mechanism is simple: we carefully transfer it to the “General Book” tab from the Google Docs form and bank statements. The rows used for the formation of the BDR are highlighted in red, the BDSF., Which are pivot tables and are built on the basis of intermediate tabs with speaking names, highlighted in green. The only columns in which the information is not related to other sources are: “Original ID” (unique row values) and “Date of creation” (= ТДАТА (), and then copy and paste as the value)
4. Articles of cash flow statement (cash flow) are located on a separate tab "PS_service" and may well be regularly reviewed depending on specific needs (do not forget to update the formulas on the sheets "Data_BDDS", "Data_BDR").
5. In the picture, a BDSDS sample, in the default format, minimized to a weekly “relevance”.
6. Sample BDS (monthly). Pay attention to the thesis mentioned above about using lines from the General Book: Budget and Fact for BDR, Plan and Fact for BDS.
7. Work with BDSDS means maintaining the lines of the "Plan" in the most current state. I am quite pedantic in working with primary information and the comments made by me kept the entire history of changes. How will you be - a question for you. My approach allowed me to catch about 1 significant error per week, threatening discrepancies of tens to hundreds of thousands of rubles. By the way, time was eaten a little.
8. Actually the file management accounting .
PS: I thought for a long time about how to automate the process of “overflowing” information from the Google Docs form, until I came to the thought of the necessary manual control of the heterogeneous information entered (many people fill out the forms + the presence of at least one client bank + 1C). Moreover, I don’t know VBA ... I’ll give it to the habrasociety community as it is, I hope it helps someone or it will just be interesting.
The business to which I had a relationship in the summer was the usual online store of premium and higher clothing with a turnover of about 1 million rubles a month. The business worked, not to say that it was very successful, but it worked and continues to work. The owner understood the need for management accounting and, with this understanding, took me as financial director (analyst / manager ...), since the previous one left the business 3 months before my arrival. Actually, a hole of the same duration was also managed by management accounting. Looking ahead, I’ll say that I didn’t eliminate the hole (we decided not to tear the past), but I created a system that successfully works with minimal labor to this day.
My predecessor led a manager in Fyngrad, which turned out to be a very powerful tool. For example, it allowed you to automatically load information from 1C and statements of different bank clients, creating postings according to predefined rules. The thing is certainly useful, however, subject to the double recording system, it increased the operating time by several times. To avoid an increase in work, this tool allowed the generation of “dependent transactions”. The dog was buried in the creation of these additional postings. And then it turned out that behind all the power of Fingrad was unique, which caused a complete lack of expertise in the public domain. Ordinary users (who, incidentally, paid 3,000 rubles a month for accessing the system) had access only to the “User Guide” on the official website, and 6 video lessons there. Youtube, which provided access to a couple of dozen video tutorials, also did not help much. There were no forums with information on how to ... in principle. Support, specific questions about the rules for creating “dependent transactions” and requests for help in my case, were frozen with the phrases “we have not concluded a support agreement, so we are not ready to answer such specific questions”. Although it would seem - what is specific in such requests, and even with screenshots on my part? It is clear that everything can be beaten with your hands, but the question is, why then generally pay for a tool that greatly increases the time required for managing and does not give any advantages for small businesses? therefore, we are not ready to answer such specific questions. ” Although it would seem - what is specific in such requests, and even with screenshots on my part? It is clear that everything can be beaten with your hands, but the question is, why then generally pay for a tool that greatly increases the time required for managing and does not give any advantages for small businesses? therefore, we are not ready to answer such specific questions. ” Although it would seem - what is specific in such requests, and even with screenshots on my part? It is clear that everything can be beaten with your hands, but the question is, why then generally pay for a tool that greatly increases the time required for managing and does not give any advantages for small businesses?
Having convinced the owner of the inappropriateness of using Fingrad with such volumes of business and unloading all the information from the system, I put a BIG and fat cross on it. Moreover, the decision to leave for MS Excel was not spontaneous. Good googling on the subject of management accounting, I found monsters similar to Fingrad , or links to web applications for maintaining personal finances, while the main requirements for the system were:
- the ability to maintain BDS and BDR based on a revised chart of accounts;
- simplicity in the further management accounting (including by the forces of "financially illiterate" users);
- flexibility (the ability to expand / remove functionality on the go);
- lack of congestion of the tool / interface.
To begin with, we’ll clarify the terms: being a non-financier, I mean “Balance of Cash Flows of Funds” by BDS, BDR - “Budget of Income and Expenses”. We consider the BDDS to be the cash basis (the day of the operation — the column “Date of Operation”) and use it for operational day-to-day planning, and the BDR by the accrual method (column “The accrual period”) for strategic, within a year or more.
So, how it works and how it works (ideally):
1. Management accounting is collected on the basis of information entered by end users using a form in Google Docs. The field names and encoding options are marked in red in the final file of management accounting - a kind of mapping of fields.
2. In the end, it looks like this (the green is transferred to the final management file).
3. Management accounting is built on the basis of .xls unloading from Fyngrad (hence the strange names for external users and, in general, an excessive number of columns). We kindly ask you not to take seriously the values of the columns "Parish", "Consumption" - much has been randomly changed.
The filling mechanism is simple: we carefully transfer it to the “General Book” tab from the Google Docs form and bank statements. The rows used for the formation of the BDR are highlighted in red, the BDSF., Which are pivot tables and are built on the basis of intermediate tabs with speaking names, highlighted in green. The only columns in which the information is not related to other sources are: “Original ID” (unique row values) and “Date of creation” (= ТДАТА (), and then copy and paste as the value)
4. Articles of cash flow statement (cash flow) are located on a separate tab "PS_service" and may well be regularly reviewed depending on specific needs (do not forget to update the formulas on the sheets "Data_BDDS", "Data_BDR").
5. In the picture, a BDSDS sample, in the default format, minimized to a weekly “relevance”.
6. Sample BDS (monthly). Pay attention to the thesis mentioned above about using lines from the General Book: Budget and Fact for BDR, Plan and Fact for BDS.
7. Work with BDSDS means maintaining the lines of the "Plan" in the most current state. I am quite pedantic in working with primary information and the comments made by me kept the entire history of changes. How will you be - a question for you. My approach allowed me to catch about 1 significant error per week, threatening discrepancies of tens to hundreds of thousands of rubles. By the way, time was eaten a little.
8. Actually the file management accounting .
PS: I thought for a long time about how to automate the process of “overflowing” information from the Google Docs form, until I came to the thought of the necessary manual control of the heterogeneous information entered (many people fill out the forms + the presence of at least one client bank + 1C). Moreover, I don’t know VBA ... I’ll give it to the habrasociety community as it is, I hope it helps someone or it will just be interesting.