Financial Statements

Profit and Loss

The Profit and Loss Statement (PnL) is the core of your forecasts. Most other forecasts either flow into your PnL (your PnL uses data from that forecast) or originate from your PnL (the forecast uses data from your PnL).

Profit and Loss Statement (PnL) forms the core of your forecasts. Most forecasts either flow into your PnL, or originate from here.

The Profit and Loss Statement is split into 2 sections:

  1. Actuals (your historical data), and;
  2. Forecasts

Actuals

In your PnL, Actuals are your historical data.

After connecting your accounting integration (such as QuickBooks Online), you will see a Profit and Loss Statement filled with your past data only: this is called your Actuals.

It should look something like this:

Profit and Loss Actuals Only

Transactions

You can find out more transaction details by clicking on the row name. Click on the Data tab on the right sidebar, and you'll see something like this:

PnL Sidebar Data tab, transactions

Forecasts

Your projected future data is called a Forecast. The forecasted months will be empty until you add your first forecast.

Cloudberry has three ways of adding a forecast into the PnL:

  1. Formula Builder
  2. Spreadsheet formulas
  3. Pull in values from elsewhere in the financial model, such as the Hiring Plan or Revenue Forecast Worksheet

Formula Builder (sidebar)

The automated formula builder is the quickest way to build and maintain simple forecasts in Cloudberry.

To start, click on the row you want to forecast. In this example, we're going to use Advertising. Switch “Formula Type” from Custom to Automatic, and select the type of formula you want to forecast. The options are

  • Average
  • Median
  • Percentage (%) of another row

Here, let's select Average

Forecast with Formula Builder (right sidebar)

Once you close the sidebar, you will see the newly added 3-month average, which applies until the end of the forecast period.

First forecast applied until the end of the forecast period.

Spreadsheet formulas

If you need more customization than the simple sidebar options, you can create your own formula. Cloudberry supports many of the common spreadsheet formulas, such as SUM, AVERAGE, MEDIAN, PERCENTILE, MIN, MAX, and others.

Start by selecting a row you want to forecast, and click on the first forecasted cell. Here, January:

Profit and Loss First Formula select a cell

Next, build your first formula. In this example, we will create a “3-month average” formula. Type

=AVERAGE([

Parentheses work the same as in any spreadsheet formula (to group a formula together), whereas the square bracket instructs Cloudberry which row and time period you want to reference in your formula.

Type your first formula

As we're building a forecast that's an average of itself, start by adding:

=AVERAGE([self,
Continue by adding a time period such as -3a:-1a to refer to past three months of Actuals.

Continue by adding a time period, such as the past three months of Actuals.

=AVERAGE([self, -3a:-1a

The -3 refers to three months ago, whereas -1 is one month ago. The colon in the middle indicates a range, just like it does for spreadsheets. Finally, the "a" indicates we want to lock the formula to Actuals. In other words, this is not a rolling average, but an average of the last three months of your historical data.

Finish the formula by closing the bracket and the parenthesis.

Close the bracket, then close the parentheses, and hit enter. The finished formula is

=AVERAGE([self, -3a:-1a])
Close the bracket followed by closing the parenthesis, and hit enter.

The result of the formula is 29,000 for the first forecasted month.

Profit and Loss First Formula continue by adding a time period such as -3a:-1a to refer to past three months of Actuals.

Next, drag the formula forward until the end of the forecast period to apply it everywhere.

Profit and Loss First Formula continue by adding a time period such as -3a:-1a to refer to past three months of Actuals.

You can also use a keyboard shortcut to apply the formula to other Forecast months. Select the entire row you want to forecast with:

CTRL/CMD + SHIFT + Right Arrow Key

followed by

CTRL/CMD + R

to fill in the formula.

You should see the 29,000 per month applied until the end of your forecast period.

First forecast applied until the end of the forecast period.

Reference Hiring Plan or Worksheets

Another way to create forecasts in your PnL is to pull in values from elsewhere in the financial model, such as the Hiring Plan or Worksheets.

Let’s say you created a forecast for your Monthly Recurring Revenue (MRR) using the Revenue Forecast tab. You could pull this into the PnL by typing:

=revenue_forecast!mrr

In this formula, revenue_forecast refers to the Worksheet name in a slug format (spaces replaced by underscores), whereas mrr refers to the row on the Revenue Forecast tab. Just like with a spreadsheet, we use ! to separate the Worksheet name and the row name.

Here's what it would look like:

Pull in MRR forecast from Revenue Forecast Worksheet

You can use the same method to pull in values from the Hiring Plan, for example. Before you begin, make sure that your Hiring Plan Teams are mapped to your PnL accounts first.

Use hiring_plan!payment_debit_row in the first part of the formula to let Cloudberry know you want to pull from the Hiring Plan. The second part (separated by two colons, or ::) refers to the row you're editing. For example, Contractors:

=sum([hiring_plan!payment_debit_row::contractors])