How I did management accounting in Excel
I work as a regular analyst and it so happened that in the summer of 2014, participating in one e-commerce project, I did management accounting in MS Excel in 3 weeks on my knee. Long planned and finally decided to lay out on Habr. I think it will be useful for small entrepreneurs who understand the importance of managing financial flows, but who do not want to spend a significant amount of time and money on management accounting. I do not pretend to be the ultimate truth and I will be glad to other decisions proposed by the community members.
The business I was involved with in the summer was an ordinary online clothing store of premium and higher segment 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 a financial director (analyst / manager ...), since the previous one went out of business 3 months before my arrival. Actually, the hole of the same duration was in the conduct of management accounting. Looking ahead, I will say that I did not eliminate the hole (we decided not to stir up the past), but created a system that works successfully with minimal effort and to this day.
My predecessor led the management in Fingrad , which turned out to be a very powerful tool. For example, he allowed to automatically load information from 1C and statements of various bank clients, creating transactions according to pre-defined rules. A thing that is certainly useful, however, subject to the double-entry system, increased the work time by several times. To avoid increasing the work, this tool allowed us to generate “dependent postings”. In creating these additional entries, the dog was buried. And then it turned out that beyond the full power of Fingrad there was a uniqueness that caused a complete lack of expertise in the public domain. Regular users (who paid, by the way, 3000 rubles per month for access to the system) were only available in the “User’s Guide” on the official website, and 6 video lessons in the same place. Youtube, which gave access to a couple more dozens of video lessons, also did not help much. There were no forums with information about “how to ...”. Support, for specific questions about the rules of creating “dependent postings” and requests for help in my case, I froze with the phrases “we do not have an agreement for support, 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 hands, but one wonders, why should one pay at all for a tool that greatly increases the time required for management and does not provide any advantages for small businesses?
Having convinced the owner of the inexpediency of using “Fingrad” in such volumes of business and having unloaded all the information from the system, I put on it a BIG and fat cross. The decision to leave it in MS Excel was not spontaneous. Thoroughly talking about management accounting, he found monsters similar to Fingrad , or links to web applications for personal finance, while the main requirements for the system were:
')
- the possibility of maintaining BDDS and MDM on the basis of a variable chart of accounts;
- simplicity in further management accounting (including by “financially illiterate” users);
- flexibility (the ability to expand / remove functionality on the go);
- no tool / interface congestion.
To begin with, let's clarify the terms: being a non-financier, by BDDS I mean “Balance of Movement of Funds”, BDR - “Budget of Incomes and Expenditures”. We consider BDDS as a cash method (day of operation - column “Date of operation”) and use for operational day-to-day planning, and MDD accrual method (column “Accrual period”) for strategic, within a year or more.
So, how everything works and how it works (ideally):
1. Management accounting is collected based on information entered by end users using a form in Google Docs. The names of fields and coding of options in the final management accounting file — a kind of field mapping — are marked in red.
2. In the end, it looks like this (green is flooded with what is transferred to the final management file).
3. Management accounting is based on the .xls download from Fingrad (hence the strange names for third-party users and, in general, an excessive number of columns). We kindly ask you not to take seriously the values ​​of the columns “Arrival”, “Expenditure” - a lot of things are randomly changed.
The filling mechanism is simple: we accurately transfer to the “General Book” tab from the Google Docs form and bank statements. Red highlighted lines used to form the BDR, green - BDDS., Which are summary tables and are based on intermediate tabs with talking names. The only columns in which information is not related to other sources are: “Original ID” (unique values ​​of strings) and “Date of creation” (= TATA (), and then copy and paste as value)
4. Articles DDS (cash flow) are located on a separate tab "PS_suschebny" and may well be reviewed regularly, depending on the specific needs (do not forget to update the formulas on the sheets "Data_BDDS", "Data_BDR").
5. The sample BDDS in the picture, in the default format, is minimized to weekly "relevance."
6. Sample BDS (monthly). Pay attention to the above mentioned thesis on the use of lines from the “General Book”: Budget and Fact for the CRA, Plan and Fact for the BDDS.
7. Working with BDDS implies keeping the “Plan” lines as up-to-date as possible. I am quite pedantic in working with primary information and the comments made by me kept the entire history of changes. How will you have - a question for you. My approach allowed me to catch about 1 significant mistake per week, which threatened to differ by tens to hundreds of thousands of rubles. Time, by the way, was eaten a little.
8. Actually the file of management accounting .
PS: Long thought about how to automate the process of “spillover” of information from the Google Docs form, until I came to the idea of ​​the necessary manual control of the entered heterogeneous information (many people fill out forms + having at least one client bank + 1C). Moreover, I don’t know VBA ... I give it to the habrasoobschestva as it is, I hope someone will help or just be interested.
The business I was involved with in the summer was an ordinary online clothing store of premium and higher segment 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 a financial director (analyst / manager ...), since the previous one went out of business 3 months before my arrival. Actually, the hole of the same duration was in the conduct of management accounting. Looking ahead, I will say that I did not eliminate the hole (we decided not to stir up the past), but created a system that works successfully with minimal effort and to this day.
My predecessor led the management in Fingrad , which turned out to be a very powerful tool. For example, he allowed to automatically load information from 1C and statements of various bank clients, creating transactions according to pre-defined rules. A thing that is certainly useful, however, subject to the double-entry system, increased the work time by several times. To avoid increasing the work, this tool allowed us to generate “dependent postings”. In creating these additional entries, the dog was buried. And then it turned out that beyond the full power of Fingrad there was a uniqueness that caused a complete lack of expertise in the public domain. Regular users (who paid, by the way, 3000 rubles per month for access to the system) were only available in the “User’s Guide” on the official website, and 6 video lessons in the same place. Youtube, which gave access to a couple more dozens of video lessons, also did not help much. There were no forums with information about “how to ...”. Support, for specific questions about the rules of creating “dependent postings” and requests for help in my case, I froze with the phrases “we do not have an agreement for support, 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 hands, but one wonders, why should one pay at all for a tool that greatly increases the time required for management and does not provide any advantages for small businesses?
Having convinced the owner of the inexpediency of using “Fingrad” in such volumes of business and having unloaded all the information from the system, I put on it a BIG and fat cross. The decision to leave it in MS Excel was not spontaneous. Thoroughly talking about management accounting, he found monsters similar to Fingrad , or links to web applications for personal finance, while the main requirements for the system were:
')
- the possibility of maintaining BDDS and MDM on the basis of a variable chart of accounts;
- simplicity in further management accounting (including by “financially illiterate” users);
- flexibility (the ability to expand / remove functionality on the go);
- no tool / interface congestion.
To begin with, let's clarify the terms: being a non-financier, by BDDS I mean “Balance of Movement of Funds”, BDR - “Budget of Incomes and Expenditures”. We consider BDDS as a cash method (day of operation - column “Date of operation”) and use for operational day-to-day planning, and MDD accrual method (column “Accrual period”) for strategic, within a year or more.
So, how everything works and how it works (ideally):
1. Management accounting is collected based on information entered by end users using a form in Google Docs. The names of fields and coding of options in the final management accounting file — a kind of field mapping — are marked in red.
2. In the end, it looks like this (green is flooded with what is transferred to the final management file).
3. Management accounting is based on the .xls download from Fingrad (hence the strange names for third-party users and, in general, an excessive number of columns). We kindly ask you not to take seriously the values ​​of the columns “Arrival”, “Expenditure” - a lot of things are randomly changed.
The filling mechanism is simple: we accurately transfer to the “General Book” tab from the Google Docs form and bank statements. Red highlighted lines used to form the BDR, green - BDDS., Which are summary tables and are based on intermediate tabs with talking names. The only columns in which information is not related to other sources are: “Original ID” (unique values ​​of strings) and “Date of creation” (= TATA (), and then copy and paste as value)
4. Articles DDS (cash flow) are located on a separate tab "PS_suschebny" and may well be reviewed regularly, depending on the specific needs (do not forget to update the formulas on the sheets "Data_BDDS", "Data_BDR").
5. The sample BDDS in the picture, in the default format, is minimized to weekly "relevance."
6. Sample BDS (monthly). Pay attention to the above mentioned thesis on the use of lines from the “General Book”: Budget and Fact for the CRA, Plan and Fact for the BDDS.
7. Working with BDDS implies keeping the “Plan” lines as up-to-date as possible. I am quite pedantic in working with primary information and the comments made by me kept the entire history of changes. How will you have - a question for you. My approach allowed me to catch about 1 significant mistake per week, which threatened to differ by tens to hundreds of thousands of rubles. Time, by the way, was eaten a little.
8. Actually the file of management accounting .
PS: Long thought about how to automate the process of “spillover” of information from the Google Docs form, until I came to the idea of ​​the necessary manual control of the entered heterogeneous information (many people fill out forms + having at least one client bank + 1C). Moreover, I don’t know VBA ... I give it to the habrasoobschestva as it is, I hope someone will help or just be interested.
Source: https://habr.com/ru/post/241031/
All Articles