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
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.
Under the group Capital contributions you can find the financial transactions that correspond to an investment.
Under the group Reimbursements you can find all the expenses paid by the founders before getting any revenue.
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.
The Retained Earnings, at the end of the period covered in this example, sum a profit from 2023 and a loss in 2024.
The Financial Statement
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.
The Financial Statement on the Google Sheet also fetches an Income Statement that covers the performance of the years 2024 and 2023.
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.
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.
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.