DIY digital business transformation

A common mistake of beginning businessmen is that they do not pay enough attention to collecting and analyzing data, optimizing work processes, and monitoring key indicators. This results in reduced performance and a waste of time and resources. With bad processes, you have to correct the same errors several times. As the number of customers grows, service deteriorates, and without data analysis there is no clear understanding of what needs to be improved. As a result, decisions are made on a whim.

In order to be competitive, modern business, in addition to quality products and services, it is necessary to have transparent processes and collect analytical data. Without this, it is difficult to understand the real state of affairs in business and make the right decisions. Therefore, it is important to have the necessary tools in your arsenal, which are not only convenient to use, but also which allow you to simplify the work and create the most transparent processes.

Today there are a huge number of tools and solutions. But most entrepreneurs do not use them, because they either don’t see the values ​​in them, or they don’t understand how to use them, either expensive or complicated, and another 100,500 either. But those who figured out, found or created such tools for themselves already have an advantage in the medium term.
')
For more than 10 years I have been creating IT products and solutions that help businesses increase profits through automation and digital transformation of processes. I participated in the creation of a dozen startups and created dozens of online tools that are used by hundreds of thousands of people around the world.

Here is one of the good examples in my practice that shows the benefits of digital transformation. For one small American law firm, my team and I created a tool for generating legal documents, which allowed lawyers to generate documents faster. And later, expanding the functionality of this tool, we created an online service and completely transformed the company. Now they serve customers not only in their city, but throughout the country. Over three years, the company's capitalization has grown several times.

In the article, I will share with you real experience in creating a transparent system for monitoring basic business indicators. I will try to instill the value of using digital solutions, I will show that it is not difficult and not always expensive. So let's go!

How it all started

If you want to have something you never had, you have to do something you never did.
Coco Chanel

My wife was tired of sitting on maternity leave, and we decided to open a small business - a children's playroom. Since I have my own business, the spouse is completely engaged in the game room, and I help in strategic matters and development.

The details of starting a business is a completely different story, but at the stage of collecting data and analyzing competitors, in addition to highlighting the specific problems of this business, we drew attention to the problems of internal processes that most competitors did not struggle with.

To my surprise, in the 21st century, practically no one did CRM at least in some form, many kept records in writing, in notebooks. At the same time, the owners themselves complained that the employees were stealing, making mistakes when calculating, and they had to spend a lot of time to recount and compare with the entries in the book of account, data about armor, deposits were lost, customers left for reasons incomprehensible to them.

Analyzing the collected data, we realized that we do not want to repeat their mistakes and we need a transparent system that will minimize these risks. The first thing we started to look for ready-made solutions, but we could not find fully satisfying our requirements. And then I decided to make my system, albeit not perfect, but working and inexpensive (almost free).

When choosing a tool, I took into account the following criteria: it should be inexpensive, have flexibility and affordability, and be easy to use. I could write a complete, powerful and expensive system for this business, but we had little time and a small budget, plus we did not understand until the end whether our project would fire, and it would be unreasonable to spend a lot of resources on this system. Therefore, at the time of testing the hypothesis, I decided to start with MVP (Minimum Viable Product - the minimum viable product) and in the shortest possible time with minimal investment make a working version, and eventually modify or redo it.

As a result, my choice fell on Google services (Drive, Sheets, Calendar). The main source of input / output of information is Google Sheets, since my wife has experience working with spreadsheets, then, if necessary, she can make changes on her own. I also took into account the fact that the tool will be used by employees who may not be very good at using a computer, and it will be much easier to teach them how to enter data into a table than to teach them how to work with some specialized program such as 1C.

The data entered in the tables is changed in real time, that is, at any time you can see the situation of the company, built-in security, you can restrict access to certain people.

Designing the architecture and data structure

A children's playroom provides several basic services.

• A standard visit is when a customer buys time spent in his children's playroom.
• Supervised visit - when a client buys time spent in his children’s playroom and pays extra for the supervision. That is, the client can leave the child and go about his business, and the room worker will watch and play with the child during the absence of the parent.
• Open birthday - the client rents a separate table for eating and accommodating guests and pays for a standard visit to the game room, while the room is working as usual.
• Closed birthday - the client rents the entire room, the room does not accept other clients during the rental period.

It is important for the owner to know how many people visited the room, how old, how much time they spent, how much money they made, how much they spent (it often happens that the administrator needs to buy something or pay for something, for example, for delivery or water), how many birthdays were there.

Like any IT project, I started by thinking over the architecture of the future system and developing the data structure. Since the spouse is engaged in business, she knows everything that she needs to see, control and rule, so she acted as a customer. Together we conducted a brainstorm and drew up the system requirements, on the basis of which I thought over the system’s functionality and formed the following file and folder structure in Google Drive:



The Consolidated document contains general information on the company: income, expenses, analytics



The document “Expenses” contains information on the monthly expenses of the company. For greater transparency, broken down into categories: office expenses, taxes, staff costs, advertising costs, other expenses.


Monthly expenses


Summary table of expenses for the year

In the “Revenue” folder are 12 Google Sheets files, one for each month. These are the main working documents that employees fill out daily. They contain the obligatory dashboard tab and tabs for each working day. The dashboard tab displays all the necessary information for the current month for a quick understanding of things, and it is also possible to set prices and add services.


Dashboard tab


Daily tab

In the process of business development, additional needs began to appear in the form of discounts, subscriptions, additional services, events. We also implemented all this over time, but this example shows the basic version of the system.

Functionality Creation

After I found out the main indicators, worked through the architecture and data exchange between entities, I started implementation. First of all, in the “Income” folder, I created a Google Sheet document. He created two tabs in it: dashboard and the first day of the month, into which he added the following table.


Main worksheet

This is the main worksheet that the Administrator will work with. He just needs to fill in the required fields (marked in red), and the system will independently consider all the necessary indicators.

To reduce input errors and convenience, the field "Type of visit" has implemented a drop-down list of the services that we can edit on the dashboard page. To do this, add data verification to these cells and indicate the range from where to get data.



To reduce human error in the calculations, I added an automatic calculation of the hours spent by the client in the room, and the calculation of the amount of money payable.

To do this, the Administrator should simply mark the time of arrival of the client (column E) and the time of departure (column F) in the format HH: MM. To calculate the total time spent by the client in the game room, I use the following formula:

=IF(ISBLANK($F8); ""; $F8-$E8) 

For the automatic calculation of the amount of money for using the services, I had to use a more complex formula, since the price of an hour can vary depending on the type of service. Therefore, I had to bind the data with the service table on the dashboard page using the QUERY function:

 =ROUNDDOWN(G4*24*IFERROR(QUERY(dashboard!$G$2:$H$5; "Select H where G = '"& $D4 & "'");0) 

In addition to the main actions, I added additional functions to eliminate unwanted IFERROR or ISBLANK errors, as well as the ROUNDDOWN function - so as not to mess with the little things, I rounded the final amount down, towards the client.

In addition to the main income (renting time), in the children's play room there is additional income in the form of services or selling toys, and employees make some small expenses, for example, pay for drinking water or buy sweets for compliments, all this must also be taken into account.

Therefore, I added two more tablets in which we will fix this data:



To make it easier to work with tablets, I colorized them and added conditional formatting of the cells.

The main tables are ready, now you need to summarize the main indicators in a separate table so that you can clearly see how much has been earned per day and how much of this money is on hand and which is on the card.



For the total calculation of money by type of payment, I again used the QUERY function:

 =QUERY(I8:J;"SELECT sum(J) WHERE I=''"»  «=QUERY(I8:J;"SELECT sum(J) WHERE I=''") 

At the end of the working day, the administrator only needs to double-check the revenue and not have to recalculate manually. We do not force the person to do extra work, and the owner can look at and control the situation at any time.

All the necessary tables are ready, now just duplicate the tab for each day, number them and get the following.



Fine! Almost everything is ready, it remains to display all the main indicators for the month on the dashboard tab.

To get the total amount of income per month, you can write the following formula

 ='1'!D1+'2'!D1+'3'!D1+'4'!D1+'5'!D1+'6'!D1+'7'!D1+'8'!D1+'9'!D1+'10'!D1+'11'!D1+ '12'!D1+'13'!D1+'14'!D1+'15'!D1+'16'!D1+'17'!D1+'18'!D1+'19'!D1+'20'!D1+'21'!D1+ '22'!D1+'23'!D1+'24'!D1+'25'!D1+'26'!D1+'27'!D1+'28'!D1+'29'!D1+'30'!D1+'31'!D1 

where D1 is the cell with daily revenue, and '1', '2' and so on is the name of the tab. In exactly the same way, I get data on additional income and expenses.

For clarity, I decided to display the total yield by category. To do this, I had to make a complex selection and grouping from all tabs, and then filter and remove empty and unnecessary lines.


Profitability by category

The basic income accounting tool is ready, now just duplicate the file for each month of the year.

After I created the tool for accounting and control of income, I set about creating a table of expenses in which we will take into account all monthly expenses: rent, payroll, taxes, procurement of goods and other expenses.

In the current year’s folder, I created a Google Sheet document and added 13 tabs, a dashboard and twelve months to it.


Dashboard tab

For clarity, the dashboard tab has summarized all the necessary information on financial spending for the year.

And in each tab of the month I created a table in which we will keep all expenses of the company’s money by category.


Month tab

It turned out very conveniently, now you can see and control all the expenses of the company, and if necessary, look at the history and even do analytics.

Since the information on income and expenses is in different files and it is not very convenient to monitor, I decided to create one file in which I brought together all the relevant information necessary for the owner to control and manage the company. I called this file "Consolidated."


Summary table

In this file, I created a table that receives monthly data from tables, for this I used a standard function:

 =IMPORTRANGE("url";"dashboard!$B$1") 

where the first argument is the ID of the document, and the second parameter is the imported range.

Then he brought together the annual balance: how much was earned, how much was spent, what profit, profitability. Visualized the necessary data.

And for convenience, so that the business owner can see all the data in one place and not run through the files, I integrated the ability to select any month of the year and display the main indicators in real time.

To do this, I created a bunch of month and document ID



Then I created a drop-down list using "Data -> Data Validation", specified the range of the link and configured the import with a dynamic link to the document

 =IMPORTRANGE("'"& QUERY(O2:P13;"SELECT P WHERE O ='"& K7 &"'") &"'"; "dashboard!$A1:$B8") 

Conclusion

As you can see, improving the processes in your business is not as difficult as it might seem, and for this you do not need to have any super-skills. Of course, this system has a large number of shortcomings, and with the growth of the business it will be impossible to use it, but for a small business or at the start when testing a hypothesis, this is an excellent solution.

This game room has been working on this decision for the third year, and only this year, when we already clearly understand all the processes, we know our client and the market. We decided to create a complete online business management tool. Demo app in Google Drive

PS

It’s not very convenient to use Google spreadsheets to monitor your business, especially from your phone. Therefore, I made a PWA application that displays all the main business indicators in a convenient format in real time