Make a P&L Statement on a Google Sheet to summarize your business’ revenues, costs and expenses of a specific period by querying the balance values from your Bkper book.
What you need:
- Bkper book
- Google sheets
- The Bkper add-on for Google Sheets to work with the Bkper Functions
Helpful:
- assist the Bkper functions video
Revenues
On Bkper the revenues from regular operations are represented as incoming type accounts (green) and they are grouped into a revenues group (Net Revenues).
On the Google Sheet you fetch the balance values for a specific period in the following way.
On cell F6 you place the account name “Subscription”
On cell G6 you construct the query with a regular Google Sheet formula.
On cell B19 you run the Bkper Function to fetch the balance value.
The query:
account:'Subscription' after:01/2019 before:01/2020The regular Google Sheet formula to create the Bkper query:
="account:'"& $F6 &"' after:"&TEXT($B$7, "mm/yyyy")&" before:"&TEXT(DATE(YEAR($B$8),MONTH($B$8),DAY($B$8)+1), "mm/yyyy")The formula uses a Start (B7) and End date (B8) to define the specific period.
The Bkper Formula:
=BKPER_BALANCES_TOTAL($B$5, 1, G6, true, false)The Bkper formula uses the bookid (B5) to fetch the balance values from your Bkper book.
Create a Revenue block, by repeating these steps for all the operational revenue accounts.
COGS
On Bkper the Costs of Goods Sold are represented as outgoing type accounts (red) and they are grouped into a COGS group.
On the Google Sheet you fetch the balance values of COGS for a specific period in the following way.
On cell I6 you place the account name “Fees”
On cell J6 you construct the query with a regular Google Sheet formula.
On cell B25 you run the Bkper Function to fetch the balance value.
The query:
account:'Fees' after:01/2019 before:01/2020The Formula:
="account:'"& $I6 &"' after:"&TEXT($B$7, "mm/yyyy")&" before:"&TEXT(DATE(YEAR($B$8),MONTH($B$8),DAY($B$8)+1), "mm/yyyy")The Bkper Formula:
=BKPER_BALANCES_TOTAL($B$5, 1, J6, true, false)
Create a COGS block, by repeating these steps for all the cost of goods sold accounts.
Gross Profit (loss)
Subtract the COGS from the Revenue to get to your Gross Profit (loss) from your regular operations.
Expenses
On Bkper the Expenses are represented as outgoing type accounts (red) and they are grouped into an Expenses group (Total Expenses).
On the Google Sheet you fetch the balance values of Expenses for a specific period in the following way.
On cell L6 you place the account name “Accounting”
On cell M6 you construct the query with a regular Google Sheet formula.
On cell B33 you run the Bkper Function to fetch the balance value.
The query:
account:'Accounting' after:01/2019 before:01/2020The Formula:
="account:'"& $L6 &"' after:"&TEXT($B$7, "mm/yyyy")&" before:"&TEXT(DATE(YEAR($B$8),MONTH($B$8),DAY($B$8)+1), "mm/yyyy")The Bkper Formula:
=BKPER_BALANCES_TOTAL($B$5, 1, M6, true, false)
Create an Expenses block, by repeating these steps for all the cost of goods sold accounts.
Net Ordinary Income (Loss)
Subtract the Total Expenses from the Gross Profit (loss) to calculate the Net Ordinary Income (Loss).
Note: - This is an example for explanation purposes, check your Profit and Loss with a local professional.
- Financial Statements on Google Sheet is a more complex example of reporting.
If you need help to set up your own P&L Report? Feel free to contact us!