How to Create Format for Projected Financial Statements in Excel

Projecting the financial statements can be a daunting task. Numerous systematic and unsystematic risks exist that could prevent the projection. However, we will not consider those risks to keep things simple. In this article, we will show you how to create a format for projected financial statements in Excel.

Financial Statement

There are four main types of financial statements: balance sheet, income statement, cash flow statement, and owner’s equity statement. These statements summarize the financial situation of an organization or company. They are the written documents or records that the company keeps throughout the year. Here, we will talk about the first three main parts of a financial statement report:

Income Statement

It reports the profit and loss of a company over a certain period. So, the income statement is made up of three parts: 1. Revenue 2. Expense 3. Profits. So, it contains all income and expenses for a certain period and calculates net profit.

Balance Sheet

It summarizes the financial position of the company after a certain period and is also known as a Statement of Financial Position/condition. In general, a balance sheet consists of three parts. These are: assets, liabilities, and owners’ equity. A balance sheet provides a glimpse of the company’s finances. The balance sheet consists of the company’s liabilities, assets, and owner’s equity. Balance sheets are organized according to the equation:

• Assets: These are the main resources owned by the company. Assets can be categorized into many types. Example- current and fixed assets, tangible and intangible assets, etc.
• Liabilities: They are things that the company owes to a person or another company, like cash, loans, etc.
• Owner’s Equity: It represents the value for a company’s shareholders after all the company’s assets have been sold off and all company liabilities have been paid off.

Cash Flow Statement

Cash flow statements are the bridge between the income statement and the balance sheet.  There are also three parts to it:

• Operating Activity: It is the main source of revenue for a company or organization. Cash flows regarding main operations will also be included here.
• Investment Activity: Cash received or paid due to buying or selling any assets, taking loans, paying interest on loans, etc. is included here.
• Financing Activity: Cash flows regarding any changes in equity earning or borrowing entities like bonds, stocks, or dividends.

We will prepare the format for three projected financial statements in Excel. Firstly, we will create the income statement. Following that, we will create the format for the projected balance sheet. Lastly, we will create the format for the cash flow statements. In this process, we will use the SUM function to calculate the total amount. Everything is linked, so changing one cell will change the relevant linked values. Moreover, the assumed values will be in blue font color; therefore, you will easily know which values to change as per your requirements. Moreover, the statements are created on the same sheet. This will speed up our navigation time. Additionally, this practice will reduce the risk of linking to unintended cells.

1. Creating Projected Income Statement Format

In this first example, we will create a format for the projected income statement. We will assume several values for forecasting. These values will be easily recognizable by their blue font colors. The depreciation and interest expense will be obtained from the second example. Without further ado, let us go through the steps to project the income statement.

Steps:

• To start with, type all the assumptions. These values will be referenced in the income statement.

• Next, type all the fields for the income statement. We will forecast for the years 2022, 2023, and 2024.

• After that, type this formula in cell C9 to calculate the gross revenue and drag this using the Fill Handle to the right side to AutoFill the formula.

• Then, type another formula in cell C10 to calculate the refunds. Remember to use the negative sign here.

• After that, type another formula in cell C11 to find discounts.

• Then, type this formula in cell C12 to find the net revenue.

• Afterward, type this formula in cell C14 and drag this down and then to the right side to fill the formula. We have fixed the row number using the dollar sign.

• Next, type another formula to calculate the cost of goods sold.

• Type this formula to find the gross margin.

• After that, type another formula in cell C20 drag this down, and then to the right side to fill the formula.

• Then, type this formula to calculate the total operating expense. We will find the values for the depreciation and interest expense later, and we will keep those blank for now.

• Type this formula in cell C25 to find the operating income.

• Type another formula to find the net income before tax.

• Then, type this formula in cell C28 to find the tax amount.

• Next, type this formula in cell C29 to find the net income without considering depreciation and interest.

• Now, from this part, we will find the values of the depreciation. So, we link to those values by typing this formula.

• Lastly, from this part, we will find the interest amount, and we reference it by typing this formula.

• Finally, this step will complete the projected income statement.

2. Making Format for Projected Balance Sheet

For the second example, we will create a format for the projected balance sheet. For this, we need historical data from last year, which is 2021 for this article. We will need the “net cash flow” from the cash flow statement to calculate the “cash & cash equivalents” on the balance sheet. We will also need to refer to the income statement for other values.

Steps:

• Firstly, we have projected the capital expenditure and depreciation. Remember, the blue colored text indicates assumed values. We have the total depreciation values from this in our income statement.

• Secondly, list all the assumptions for the balance sheet. From these assumptions, we have found the interest expenses for the income statement.

• After that, type all the fields for the balance sheet. Additionally, we have used last year’s (2021) values. Notice there is a field called “balance verification” that we will use to check if the balance sheet balances.

• Next, type this formula in cell D52 and drag it to the right side to fill out the formula. We will find the accounts receivable amounts from this.

• After that, type this formula to find the total current assets. We will find the amount of “cash and cash equivalents” from the cash flow statement and keep it empty for now.

• Then, type another formula to find the fixed assets.

• Then, type this formula to find the accumulated depreciation.

• Next, type this formula in cell D56 to get the values of net fixed assets.

• Then, type this formula to calculate the total assets.

• Similarly, we will type formulas to calculate the values for the liabilities and equity parts. Moreover, we will see that the balance verification is not zero yet (0 means the balance sheet balances). This is because we have kept the cash and cash equivalents empty.

• Now, from the cash flow statement, we will get the net cash flow values. Input those values by typing the following formula in cell D51 and filling in the formula on the right side.

• Finally, the format for the projected balance sheet is complete.

3. Preparing Projected Cash Flow Statement

In this last example, we will prepare a format for a projected cash flow statement in Excel. We will take most of the items from the balance sheet. Net income will come from the income statement, and depreciation will come from the “capital expenditure and depreciation” that we created in the second example.

Steps:

• First of all, type all the fields for the cash flow statement.
• Secondly, link the known values:
  • Net Income → Income Statement
  • Depreciation → Projected Capex and Depreciation
  • Capital Expenditure → Projected Capex and Depreciation
  • Debt Repayment → Balance Sheet Assumption (negative value)
  • Net Borrowings → Balance Sheet Assumption

• Thirdly, type this formula in cell C78 to find the change in accounts receivable and drag it to the right side to fill it in.

• Next, type another formula to calculate the change in accounts payable.

• Then, type this formula to find a change in unearned revenue.

• Afterward, type this formula in cell C81 to find the operating cash flow.

• Then, type this formula to find free cash flow.

• After that, type another formula to calculate the net cash flow from financing.

• Lastly, type this formula in cell C89 to find the net cash flow.

• Thus, we will complete creating the format for projected financial statements in Excel.

Projected Financial Statements for Five Years

We have projected the financial statements for three years in this article. You can easily extend this for two more years to make it a five-year projection. You can choose to insert new columns and drag the formulas to the right side. This will create the five-year forecasting for the financial statements. The model’s accuracy will decline as there are more years for forecasts in Excel, so keep that in mind.

Download Practice Workbook

You can download the Excel file from the link below.

Conclusion

We have shown you three quick examples of how to create projected financial statements in Excel format. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Thanks for reading, keep excelling!

Related Articles

• Construction Company Financial Statements in Excel

<< Go Back to Financial Statement | Finance Template | Excel Templates