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:

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:

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

Once you close the sidebar, you will see the newly added 3-month average, which applies 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:

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.

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 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.

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

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

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

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

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.

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:

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])