Construction Company Financial Statements in Excel

Financial statements are very important in business and financial activities to show the financial status of an organization at any specific time. Although it is just a statement containing different incomes and expenses, you can prepare and present one in Microsoft Excel. This article will discuss how to prepare construction company financial statements in Excel.

What Is Financial Statement?

Financial statements are records that portray all the business activities and the financial activity of a person, organization, or other entity. It has three portions – a balance sheet, an income statement, and a cash flow statement.

A balance sheet consists of assets, liabilities, and shareholder’s equity for the business or organization. The income statement consists of revenues and expenses of the same for a particular period. Income is determined here by subtracting expenses from revenues. Meanwhile, the cash flow statement, or CFS is a measurement of a company’s cash generation ability.

Financial statements can be of many kinds. Regardless of the type, a financial statement can be used for many purposes. Such as lenders investors, or other partners to understand the status of the business. A financial statement is also important for planning the business’s future. Moreover, they can be used to assess annual or periodic tax fillings.

Step-by-Step Procedure to Prepare Construction Company Financial Statements in Excel

As mentioned earlier, a financial statement must consist of three parts. A construction company’s financial statement is no exception. So, to prepare complete construction company financial statements in Excel, we need to prepare three sheets- balance sheet, income sheet, and cash flow sheet. For better presentation purposes, we are putting them into three different spreadsheets.

Step 1: Enter Values of Current and Non-current Assets

First, we need to prepare a balance sheet for the financial statement. A balance sheet has three parts- records of assets, liabilities, and stakeholders’ equity. To prepare a balance sheet we first need to enter the values of all the current and non-current assets.

We are taking two data points for two years. This helps better comparison of the organization’s financial status. The sheets can also be prepared for one entry. Either way, you can add all the assets in a separate category or in one- depending on how you want to present it.

We have added cash, certificate of deposit, accounts receivable, inventory, and prepaid expenses as the current assets. And added net property and equipment as the non-current asset. The asset chart of the balance sheet will look like this.

Step 2: Calculate Total Assets

It is important to calculate the total assets in the balance sheet to compare with the liabilities to find out whether it is balanced or not. We need the help of the SUM function for this.

To calculate the total assets in the sheet for the year 2020, select cell C12 and write down the following formula.

Now press Enter and you will have the total asset for the year 2020.

Now select the cell again and click and drag the fill handle icon to the cell beside. You will have the value for the year 2021.

Step 3: Document Current and Long-Term Liabilities

The next logical step would be to enter the values of liabilities in the balance sheet. Entering the values of the liabilities the balance sheet will now look like this.

We have included accounts payable, income tax payable, and accrued liabilities as the current liabilities and only one section for long-term liabilities.

Step 4: Estimate Total Liabilities

We are gonna need the SUM function again to estimate the total liabilities in the balance sheet.

To calculate total liabilities in the year 2020, select cell C20 first. Then write down the following formula.

Now press Enter and you will have the total liabilities for the year.

After that, select the cell again and click and drag the fill handle icon to the right of it to replicate the formula for the later year.

Step 5: Record Stakeholder’s Equity

Another portion of the balance sheet is the stakeholder’s equity. Generally, a balance sheet is balanced by the liabilities and stakeholders’ equity sum and assets. This portion can also include various sub-portions. But for the demonstration, we have included only one section for this.

You can document all the different parts if you have many sub-parts and add them to find the final stockholder’s equity too.

Step 6: Calculate Total Liabilities and Equity

As the summation of liability and equity should be equal to the assets, we must find out the total liabilities and equity too. This way we can find out if the balance sheet is indeed balanced. We can do this with the help of the SUM function.

To do that, select cell C22 and write down the following formula.

After that, press Enter and you will have the summation for the year 2020.

Now select the cell again. Then click and drag the fill handle icon to the left to replicate the formula for the year 2021.

As we can see here, the values of total assets are balanced with total liabilities and stockholder’s equity. Also, the balance sheet is finally completed at this step.

Step 7: Record Gross Profits

As the balance sheet is completed, our next task would be to prepare an income statement for the financial statement. The income statements have the records of income, expenses, and tax records. So, first, let’s record and calculate gross profits made by the construction company.

Let’s assume, the company profits from contracts revenue directly. Document this and the contract cost first in the income statement.

Then subtract it to find gross profit. To do that, select cell C7 and write down the following formula.

Then press Enter.

Now select the cell again and click and drag the fill handle icon to the right to replicate the formula for the year 2021.

Step 8: Calculate Operating Profit

Of course, there are expenses associated with the revenues, which should be entered into the income statements. Now add the expenses at this point under the profit section.

To calculate operating profit now, select cell C9 and write down the following formula.

Then press Enter.

Select the cell again and click and drag the fill handle icon to the right to replicate the formula for the next cell.

Step 9: Determine Other Income/Expenses

Now, let’s add all the expenses below the previous section in the income statement.

We have put in all the income values in positive numbers and the expense values in negative numbers. We haven’t done this up until now in the previous steps. Because we only did subtract for one cell. Putting these cells in negative and summing them up with a large number of data makes it easier for us later on.

Step 10: Evaluate Expense Before Tax

Now it is time to evaluate expenses before tax. This can be done easily with the SUM function. To do that, select cell C17 and write down the following formula.

After that, press Enter.

Now select the cell again. Then click and drag the fill handle icon to the right to replicate the formula for the year 2021.

Step 11: Calculate Earning Before Tax

Next, calculate the earnings before tax in the income statement. For this statement, select cell C18 and write down the following formula.

Then press Enter.

After that, select the cell again and click and drag the fill handle icon to the right of the cell to replicate the formula for the next year.

Step 12: Determine Net Earnings

Now it is time to determine the net earnings in the income statement. But first, we need tax expenses for that. So enter tax expense values in the income statement below the previous portion.

Now select cell C20 and write down the following formula.

Now select the cell again and click and drag the fill handle icon to the right of the cell to find the net earnings for the year 2021.

The income statement is complete at this instant.

Step 13: Record Net Earnings for Cash Flow Statement

Finally, it is time to prepare a cash flow statement for the construction company’s financial statements in Excel. To do that, first, we need to record the net earning values in the cash flow statement.

Step 14: Enter Adjustment Values

After that, enter all the adjusted values for the cash flow segment of the financial statements for a construction company in Excel.

We have added a whole plethora of values in this section. Note that, there are some negative values here also. As mentioned earlier, we did this for an easier summation later on. This helps avoid individual subtraction from the list.

Step 15: Compute Net Cash Provided by Operating Activities

Next, calculate the next cash for operating activities. Do that by adding all the adjustments. Excel has the SUM function to do that easily.

To calculate the net cash provided by operating activities in this cash flow statement, select cell C19 and write down the following formula.

Then press Enter.

After that, select the cell again and click and drag it to the right to replicate the formula for the next cell.

Step 16: Record Investing Activities

Next up, record investing activities under the cash flow statement.

We have added the purchase cost as the negative value as it is supposed to be subtracted from the sale values.

Step 17: Calculate Net Cash in Investing Activities

Now you can use the SUM function to do that or use the additional formula for this directly.

To calculate the net cash in the investing activities next for this statement, select cell C24 and write down the following formula.

Then press Enter.

Now select the cell again and click and drag the fill handle icon to the right of it to replicate the formula for the year 2021.

Step 18: Record Financing Activities

Next, record financing activities under the previously created section of the cash flow statement segment of the construction company’s financial statement.

Step 19: Determine Net Cash in Financing Activities

We are gonna need the SUM function again for the purpose of determining net cash in the statement.

To determine the net cash in this cash flow statement, select cell C29 and write down the following formula.

Then press Enter.

After that, select the cell again and click and drag the fill handle icon to the right to replicate the formula for the year 2021.

Step 20: Document Net Increase in Cash

Next, record the net increase in cash in the next section of the cash flow statement.

Step 21: Record Cash at Beginning of the Year

Finally, to summarize, record the cash amount at the beginning of each year in the final portion of the cash flow statement.

Step 22: Evaluate Cash at End of the Year

Then calculate the cash at the end of the year of this statement. We can do that easily with the help of the SUM function. Now select cell C32 first. After that, write down the following formula.

Then press Enter.

Finally, select the cell again and click and drag it to the right to get the value for the year 2021.

The cash flow statement is now finally complete.

Download Practice Workbook

You can download the workbook used for the demonstration from the link below.

Conclusion

That concludes our step-by-step guide to preparing construction company financial statements in Excel. Hopefully, you have understood the concepts and can apply them to make your own financial statements in Excel. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

Related Articles

• How to Create Format for Projected Financial Statements in Excel

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