This help article gives an overview on how you can leverage the power of Google Sheets to prepare a Financial Statement with Bkper data that you can safely share with your peers. By doing so, you can simplify periodic obligations and have a better understanding of your business's financial position and performance.
This working example, resembles a small team that starts an online subscription-based service. It consists of a Bkper sample book that covers little over two years of operations and a Google Sheets Report that reflects a financial statement which provides a clear insight into the evolution of its financial position on the Balance Sheet, and its performance via the Income Statement.
The concepts covered in this Example
Shareholders
Under the group Shareholders you will find the financial transactions that correspond to the distribution of shares. Two shareholders can be considered as founders and two shareholders buy in their share with a par value and an amount paid in excess.
Investment
Under the group Capital contributions you can find the financial transactions that correspond to an investment.
Reimbursement
Under the group Reimbursements you can find all the expenses paid by the founders before getting any revenue.
Payment Gateway
The group Accounts Receivable represent the use of a Payment Gateway that handles the billing of final customers.
Gross Margin / Net Income
The group Gross Margin shows the results of revenue from regular operations and the cost of goods sold.
The Net Income includes the result of all income and expenses.
Retained Earnings
The Retained Earnings, at the end of the period covered in this example, sum a profit from 2023 and a loss in 2024.
Note: The Payroll is simplified to salary expenses
The Financial Statement
Balance Sheet
The Financial Statement on Google Sheet fetches the Balance Sheet on December 31, 2024 and the previous year from the sample book. Giving the position of Assets, Liabilities and Equity as well as its evolution over the period.
Income Statement
The Financial Statement on the Google Sheet also fetches an Income Statement that covers the performance of the years 2024 and 2023.
Retained Earnings
The Financial Statement fetches the Profit and Loss evolution as Retained Earnings in this book.
Materials and Requirements
To make the most of this guide, you need to have some prior experience with Google Sheets and Bkper. Familiarity with these tools will enable you to understand the concepts covered. Additionally, having a basic understanding of bookkeeping and/or accounting principles will greatly enhance your comprehension of both the book and the Income Statement.
Bkper book: this book is the base to organizing and consistently track all transactions and balance values.
Google sheets: Bkper leverages the power of Google Sheets to create financial statements and perform any related calculations.
The Bkper add-on for Google Sheets: The add-on integrates both platforms seamlessly.
How does it work
The Bkper Add-on for Google Sheets seamlessly integrates both services, enabling effortless retrieval of financial data from your Bkper Book directly to your Google Sheets.
A Financial Statement on a Google Sheet connected with a Bkper Book is updated as new transactions update account balances on the book.
Technically the financial data is fetched from your book using oAuth2 authentication and authorization. The Bkper Add-on and Bkper Functions are open-source projects that leverage the BkperApp library for Google Apps Script, enabling access to Bkper API endpoints.
You can fetch the financial data through the Bkper Add-on wizard or by writing Bkper Functions that come together with the installation of the Add-on.
Financial Statement Details
Sheet FS 2024
The bookid in Cell C6 indicates the book source of the statement.
Sheet Balance Sheet
Although it is not exactly the scope of this help article this is what the dynamic Bkper function looks like in cell B7.
=BKPER_BALANCES_TOTAL('FS 2024'!C6, 1, "group:'Net Assets' on:"& C6, 5, FALSE, FALSE)
Dynamic because if you change the bookid and / or the dates on the FS 2024 tab the entire income statement will dynamically adjust to these new conditions. Ideal to template or standardize books and reports for your customers.
Tab Income Statement
The Bkper Formulas for the Income Statement are on cells
B7 For the extended P&L of 2024
O8 For P&L totals of 2024
Q7 For the extended P&L of 2023
AD8 For P&L totals of 2023
By fetching the same data in different ways (extended and totals) you naturally audit for differences.
Tab Retained Earnings
The retained earnings tab is intended as an example of a wide range of reports that you can derive from the Balance values on a Bkper book. It also includes the feature of comparing data from different points in the book (Net Income and Retained Earnings itself)
Experiment with your own copy
The best way to master these concepts is to experiment with your own copy of this this working example.
Copy this Bkper book template.
Copy this Google Sheets template.
Install Bkper add-on for Google Sheets.
Post transactions on your book.
Open your Financial Statement and on the tab FS 2024
change the bookid to the id of your copy of the book.
change the start and end date according to the period of your transactions.
Note: It can take a while (up to 24 hours) for Google Sheets to recognize the Bkper Functions on copies of a Google Sheet.
Collaboration
This help article counts with numerous links to specific data both on the Book and the Google Sheet, enabling us to point you to exact data without any searching on your part. Imagine how this boosts your productivity when you work with your peers within a clear context. This collaboration empowers your communication with clients, collaborators, CPAs, bookkeepers, auditors, etc. improving financial productivity with exactly the same efficiency.
Getting Help
If you are thinking this is really nice but you will require professional assistance to organize and maintain your books, you may consider reaching out to the following firms:
Contabnet - Located in Brazil, Contabnet provides accounting and bookkeeping services. They offer expertise in local regulations and you can outsource your bookkeeping to them.
RVG - Based in Dubai, RVG offers professional accounting services. Their team can help with bookkeeping, financial reporting, and other related tasks, ensuring compliance with local requirements.
If you are a professional that is already working with Bkper please let us know since we always receive customer requests for professional guidance.
Of course you can always drop us a line via our support channels if you have any questions related to this help article.
Remember to conduct your own due diligence when selecting a professional firm and ensure that they align with your specific needs and requirements.
Disclaimer
Please note that this article is intended to serve as an example and should not be considered as professional advice.
While we have made every effort to ensure that the information provided is accurate and up-to-date, we cannot guarantee its completeness or suitability for your particular circumstances. We strongly recommend that you work with a local professional, such as a tax advisor or accountant, to ensure that you are compliant with local regulations and laws. Using this help article does not establish a professional-client relationship.
By using this help article, you agree that the author and publisher of this article will not be held liable for any damages or losses arising from the use of it.