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:

Helpful:

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/2020

The 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/2020

The 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/2020

The 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!

Did this answer your question?