How to Make a Forecasting Balance Sheet in Excel: 3 Steps

Method 1 – Enter the Assets

Enter the Cash and Cash Equivalents and the Accounts Receivable in the C8 and C9 cells.
Go to the C10 cell and use the SUM function as shown below.

=SUM(C8:C9)

C8 and C9 cells refer to the values of $4,219,625 and $125,000.

Enter the Assets

Enter the Fixed Asset and Accumulated Depreciation in the C11 and C12 cells >> calculate the Net Fixed Assets using the formula below.

=SUM(C11:C12)

The C11 and C12 cells represent the values of $55,000 and -$8,500 respectively.

Using SUM function

Obtain the Total Asset by adding the Total Current Assets and the Next Fixed Assets.

=SUM(C13,C10)

C10 and C13 cells point to the values of $4,344,625 and $46,500.

Obtain Total Assets

Move to the D8 cell >> and insert the following expression.

=C8+J13

The C8 and J18 cells represent the Cash and Cash Equivalents for 2021 ($4,219,625) and the Net Cash Flow (-$144,822) values, respectively.

forecasting balance sheet in excel calculate 2022 values

Compute the Account Receivable using the expression given below.

=J9*J10

J9 and J10 cells refer to the Net Revenue ($3,261,968) and the assumed Account Receivable (4%) for 2022.

calculate account receivables

Calculate the Total Current Assets as shown previously.

=SUM(D8:D9)

D8 and D9 cells refer to the values of $4,074,803 and $130,479.

calculate the Total Current Assets

Obtain the Fixed Asset using the expression given below.

=C11+J14

The C11 cell represents the Fixed Asset ($55,00) for 2021, while the J14 cell refers to the assumed Total Capital Expenditure of $67,579.

obtain the Fixed Asset

Calculate the Accumulated Depreciation by utilizing the formula given below.

=C12-J15

The C12 cell indicates the Accumulated Depreciation for 2021 (-$8,500), and the J15 cell represents the assumed Total Depreciation ($14,887).

calculate the Accumulated Depreciation

Get the Net Fixed Assets for 2022 by applying the following expression.

=SUM(D11:D12)

D11 and D12 cells point to the Fixed Asset ($122,579) and Accumulated Depreciation (-$23,387).

get the Net Fixed Assets

Obtain the Total Asset by inserting the following equation.

=SUM(D13,D10)

The D13 and D10 cells indicate the Net Fixed Assets ($99,192)and Total Current Assets ($4,205,282).

forecasting balance sheet in excel computing the Total Asset

Use the Fill Handle Tool to copy the formula across the cells.

Using Fill Handle

The output should look like the picture given below.

forecasting balance sheet in excel obtaining the Assets for all the years

Similar Readings

Method 2 – Compute the Liabilities

Navigate to the C16 and C17 cells >> enter the Accounts Payable and the Unearned Revenue >> proceed to the C10 cell and obtain the Total Current Liabilities.

=C16+C17

C16 and C17 cells refer to the values of $65,000 and $336,125.

Compute the Liabilities

Type in the Long Term Debt in the C19 cell >> Calculate the Total Liabilities amounting to $2,901,125.

=SUM(C18:C19)

C18 and C19 cells represent the Total Current Liabilities ($401,125) and the Long Term Debt ($2,500,000).

Get the Long Term Debt

Move to the D16 cell >> calculate the Accounts Payable for 2022.

=J9*J11

The J9 and J11 cells represent the Net Revenue ($3,261,968) and the Account Payable (5%).

forecasting balance sheet in excel calculate Accounts Payable

Obtain the Unearned Revenue using the formula given below.

=J9*J12

The J9 and J12 cells represent the Net Revenue ($3,261,968) and the Unearned Revenue (4%).

forecasting balance sheet in excel obtain Unearned Revenue

Jump to the D18 cell to get the Total Current Liabilities valued at $293,577.

=D16+D17

The D16 and D17 cells point to the Accounts Payable ($163,098) and Unearned Revenue ($130,479).

compute Total Current Liabilities

Obtain the Long Term Debt, which amounts to $2,256,775.

=C19+J17-J18

The C19, J17, and J18 cells refer to the Long Term Debt for 2021 ($2,500,000), Net Borrowings ($0), and Debt Repayments for 2022 ($243,225).

forecasting balance sheet in excel get the 2022 long term debt

Compute the Total Liabilities with the given expression >> drag the formula across the other cells.

=SUM(D18:D19)

The D18 and D19 cells represent the Total Current Liabilities and the Long Term Debt.

forecasting balance sheet in excel obtaining all the liabilities

Method 3 – Calculate the Equity

Proceed to the C22 and C23 cells >> enter the amounts for the Common Stock ($40,000) and Retained Earnings ($1,450,000).
Obtain the Total Shareholder’s Equity using the formula below.

=SUM(C22:C23)

C22 and C23 cells indicate the Common Stock and Retained Earnings.

Calculate the Equity