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.

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.

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.

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

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.

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

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

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

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

The output should look like the picture given below.

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.

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

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

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

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

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

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.

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.