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

The output should look like the picture given below.

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

• Add the Total Liabilities and the Total Shareholderâ€™s Equity.

`=C20+C24`

C20 and C24 cells represent the Total Liabilities (\$2,901,125) and the Total Shareholderâ€™s Equity (\$1,490,000) respectively.

The Balance Verification becomes zero, which satisfies the equation: Asset = Liability + Equity.

• Calculate 2022 Retained Earnings using the expression below.

`=C23+J16`

The C23 cell refers to the 2021 Retained Earnings (\$1,450,000), whereas the J16 cell points to the assumed Net Income of \$264,122.

• Calculate the Total Shareholderâ€™s Equity.

`=SUM(D22:D23)`

D22 and D23 cells represent the Common Stock and Retained Earnings.

• Compute the Liabilities and Shareholderâ€™s Equity and copy the formula to other cells.

`=D20+D24`

D20 and D24 cells point to the Total Liabilities and Total Shareholderâ€™s Equity.

Your results should look like the screenshot given below.

We skipped the process of making Excel Format for Projected Financial Statements. You may explore it.

## Related Articles

<< Go Back to Balance Sheet | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!