Financial Statements

Balance Sheet

Balance Sheet is a financial statement showing what your company owns, and what it owes. It displays your month-end Assets, Liabilities, and Equity, and just like other Financial Statements, it’s split into Actuals (historical data) and Forecasts (projected future data).


Actuals

Actuals are your historical data.

After connecting your accounting software to Cloudberry, your Balance Sheet is initially filled in with Actuals only.

Unlike your PnL, every row on your Balance Sheet carries a balance. It’s similar to a balance on your bank account: if there are no expenses this month, your balance would be exactly the same one month from now.

All balances are carried forward to the next month (unless you specifically decide to forecast a specific row). Therefore, your initial Balance Sheet will look like this:

Balance Sheet Actuals Only

Bank account forecasting

All of your forecasts from the PnL or other Balance Sheet rows will eventually roll up to your bank account(s). That is why direct bank account editing on your Balance Sheet is not supported.

Transactions

You can find out more transaction details on every row. Click on a edit icon next to the row name to open the sidebar, then click the Data tab. You'll see something like this:

BS Sidebar Data tab, Transactions List

Forecasts

Your projected future data is called a Forecast. The forecasted months will carry the previous balance from the most recent month of Actuals, unless you decide to edit it.

Similar to the PnL, Cloudberry has three ways of adding a forecast to the Balance Sheet:

  1. Formula Builder (sidebar)
  2. Spreadsheet formulas
  3. Pull in a row from another template

Formula Builder (sidebar)

The automated formula builder is the quickest way to build and maintain simple forecasts in Cloudberry. Using the sidebar is particularly handy for Balance Sheet rows that you want to forecast as a percentage of revenue.

To start, click on the edit icon on row you want to forecast to open the sidebar. In this example, we're going to use Accounts Receivable. Switch “Forecast 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 Percentage (%) of another row. In this case, Revenue.

Balance Sheet Forecast with Formula Builder (right sidebar)

Once you close the sidebar, you will see the newly added values for the 3-month average as a % of Revenue formula, which applies until the end of the forecast period.

First forecast applied until the end of the forecast period.

Edit Formula Builder formulas in-cell

To edit a formula created in Formula Builder (for example, to add in a one-time payment), simply click on the cell you want to edit, and append the formula with the one-time payment.

Manual values

You can also create a forecast by entering values into the cells manually. Start by selecting a cell you want to forecast. Here, March 2023:

Select a cell on the Balance Sheet

In this example, we want to forecast a $6M investment into the company.

First, increase the current Investment account balance from 1,000,000 to 7,000,000 (a six million increase).

Enter values into a Balance Sheet cell

Once you've entered the value manually, press Enter to save your forecast. Note how the new balance of 7,000,000 carriers over to future months beyond March 2023.

Complete your forecast by pressing enter key

You've just completed your first manual Balance Sheet forecast!

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

  • AVERAGE
  • MEDIAN
  • MIN
  • MAX
  • SUMIFS

Start by selecting a row you want to forecast, and click on the cell where you want to start your forecast. Here, we'll pick Computers & Equipment with start in January:

Balance Sheet spreadsheet formula forecast, select a cell

Next, build your formula. Let’s say we want to add a $25,000 monthly investment in your Computers & Equipment.

Start by typing:

=[self,

The equal sign starts the formula, and the square bracket instructs Cloudberry which row you want to reference in your formula.

Here, we use self (instead of a specific account name) to indicate that we're using the same Computers & Equipment row to forecast its future. This makes sense, as we need to know what the balance was in the previous month to forecast the next month.

Type your first formula

Next, we add:

=[self, last_month]

The last_month here refers to the previous month. The value is dynamic, so if you drag the formula forward, it always references the month before.

Balance Sheet Formula last month self reference

Next, we want to add the 25,000 per month investment. Since this is an asset account, we want to increase the balance to indicate we have bought more computers. Add the 25,000 at the end of the formula, like this:

=[self, last_month] + 25000
Add 25,000 to the previous month's balance

Finally, press Enter to save the formula. You can see the result is 150,000 for the first forecasted month. Note that the same balance carries forward until you change it.

Save the Balance Sheet formula by pressing the enter key.

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

Drag the formula forward until the end of the forecast period.

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 initial 150,000 forecast for January grow by 25,000 per month until the end of the forecast period.

Balance Sheet Spreadsheet Forecast Complete

To reference the previous month even faster, try using the number -1.

=[self, -1]

Take a look at the formula bar at the top where the last_month has been replaced with -1.

Balance Sheet Spreadsheet Forecast Complete where last_month is replaced with minus one

This also works for any other time period. A 3-month rolling average written out the long way:

=AVERAGE([self, three_months_ago:last_month])

is the same as

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

You can also ignore the equal sign (=) if you prefer even simpler-looking formulas.

Reference a template or the PnL

Another way to create forecasts in your Balance Sheet is to pull in values from another template or your PnL.

Let’s say you created a forecast for a row called Depreciation in your PnL, and you want to forecast Accumulated Depreciation on your Balance Sheet.

To reference the PnL row Depreciation, type out:

=[profit_and_loss!depreciation, this_month]

In this formula, profit_and_loss refers to the Financial Statement name in a slug format (spaces replaced by underscores), whereas depreciation refers to the row with the same name on the PnL. Just like with a spreadsheet, we use ! to separate the template name and the row name.

Reference a PnL row called depreciation

But if we press Enter and only pull in the PnL value of 10,000, this will not show us the accumulated value we're looking for.

Reference a PnL row called depreciation

Double-click the cell to edit it, and add a reference to the last month's balance at the beginning of the formula. Then, subtract the PnL Depreciation:

=[self, -1] - [profit_and_loss!depreciation]

Note that the formula is using the shorter syntax where this_month is omitted, and we use -1 instead of last_month.

Reference a PnL row called depreciation plus last month's Balance Sheet value

References work like spreadsheets

Referencing another range in Cloudberry works just like referencing in spreadsheets. First use the worksheet name, followed by an exclamation mark and the (named) range on that worksheet.