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:

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:

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.

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.

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:

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

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.

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:

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.

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.

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

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.

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

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.

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.

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.

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.