Profit and Loss

The Profit and Loss Statement, or PnL, is the core of your forecasts. Most forecasts start from or feed into the PnL.

The PnL is split into 2 sections:

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

Actuals

Actuals in your Profit and Loss (PnL) refer to historical financial data. Once you connect QuickBooks, your PnL will initially display only Actuals.

It looks something like this:

Departments

Using QuickBooks classes mapped to Cloudberry departments can offer more detail in your PnL.

Expanding any row in the PnL will reveal the departments associated with that account. For example, your software costs might be divided between Sales & Marketing, Research & Development and General & Admin Departments.

Vendors

Similarly, you can expand any department to reveal information about vendors.

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 advanced formula syntax to pull in values from the Hiring Plan. Include compensation_type: salary in the formula to let Cloudberry know you want to pull from the Hiring Plan. from: and to: can be set to this month, or just "0"

For example, to pull salaries for the Engineering team:

SUM([compensation_type: salary, from: this_month, to: this_month, team: engineering])