Construction Company Financial Statements in Excel
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 we have included only one section for demonstration.
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
- Select cell C22 and insert the following formula.
=SUM(C20:C21)
- Press Enter and you will get the result for the year 2020.
- Select the cell again.
- Click and drag the fill handle icon to the right to replicate the formula for the year 2021.
Step 7 – Record Gross Profits
The income statements have the records of income, expenses, and tax records.
- Let’s assume the company profits from contracts revenue directly. Document the contract revenues and costs in the income statement.
- Select cell C7 and use the following formula to subtract the costs from the revenue.
=C5-C6
- 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 the Operating Profit
- Add the expenses under the profit section.
- Select cell C9 and use the following formula.
=C7-C8
- 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
- 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. Putting these cells in negative and summing them up makes it easier later on.
Step 10 – Evaluate Expense Before Tax
- Select cell C17 and use the following formula.
=SUM(C12:C16)
- Select the cell again.
- Click and drag the fill handle icon to the right to replicate the formula for the next year.
Step 11 – Calculate Earnings Before Tax
- Select cell C18 and use the following formula.
=C9+C17
- Select the cell again and click and drag the fill handle icon to the right to replicate the formula for the next year.
Step 12 – Determine Net Earnings
- Enter tax expense values in the income statement below the previous portion.
- Select cell C20 and use the following formula.
=C18-C19
- Select the cell again and click and drag the fill handle icon to the right to find the net earnings for the next year.
Step 13 – Record Net Earnings for the Cash Flow Statement
- Put the net earning values in the cash flow statement.
Step 14 – Enter the Adjusted Values
- Enter all the adjusted values for the cash flow segment.
We have added a plethora of values in this section. There are some negative values for an easier summation later on.
Step 15 – Compute the Net Cash Provided by Operating Activities
- Select cell C19 and insert the following formula.
=SUM(C5:C18)
Press Enter.
- 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
- Put investing activities under the cash flow statement.
We have added the purchase cost as a negative value since it’s going to be subtracted from the sale values.
Step 17 – Calculate the Net Cash in Investing Activities
- Select cell C24 and use the following formula.
=SUM(C22:C23)
- Select the cell again and click and drag the fill handle icon to the right of it to replicate the formula for the next year.
Step 18 – Record Financing Activities
- Put financing activities under the previously created section of the cash flow statement.
Step 19 – Determine the Net Cash in Financing Activities
- Select cell C29 and use the following formula.
=SUM(C27:C28)
- Select the cell again and click and drag the fill handle icon to the right to replicate the formula for the next year.
Step 20 – Document the Net Increase in Cash
- Record the net increase in cash in the next section of the cash flow statement.
Step 21 – Record Cash at Beginning of the Year
- 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
- Select cell C32.
- Insert the following formula.
=SUM(C30:C31)
- Select the cell again and click and drag it to the right to get the value for the next year.
Download the Practice Workbook
