Financial Statements in Excel

Whether you plan to start a new business and apply for a loan or analyze a company’s performance you need to submit or study the following financial statements:
Balance sheet: The balance sheet describes the financial state of the company on a particular date like 31st December 2009. It lists the assets and liabilities of the company. Assets are things that the company owns and liabilities are things that the company owes others. Assets examples are cash in Bank, plant and machinery, airplanes, loans given by the company, etc. Examples of Liabilities are loans taken from a bank, equity, loans taken for purchase of equipment, etc. The total of assets must equal total of liabilities in a balance sheet.
Income statement or Profit & Loss statement
: This statement is easy to understand and contains all the data about the revenues of the company like fees received for services like computer training or from sales of products of the company. The expenses include salaries of the employees, electricity and telephone bills, advertisement, rent for the office, depreciation, etc. The revenues minus the expenses gives the net income or profit. If the expenses are more than the revenues you have a loss. Some people call the revenues the ‘top line’ and the profit as the ‘bottom line’.
Cash flow: This statement contains all the cash that comes in and all the cash that goes out. Non-cash expenditures like depreciation is not included in this statement because you don’t really make a cash or cheque payment for the depreciation amount. It is a legitimate expense for the wear and tear of equipment used in the financial year or years and the income tax department publishes the percentage of depreciation that can be deducted on various kinds of equipments. The closing cash flow amount for the last month becomes the opening balance for the next month. If your cash flow is negative for a longer period, your business is in trouble! Also did you notice that we used simple functions like sum and pmt or ipmt to calculate totals, loan repayment per month and the interest repayment component on loans per month?

For many people who work in the stock market it is important to analyze such financial statements and be aware of their manipulations by unscrupulous business people before making an investment. Otherwise you might be stuck with paper that has no value.

In our example we have made assumptions like depreciation of 50% per year for the computers. We have also ensured that the students pay their fees on joining the course. In the beginning the computer training center has less students and with advertisements in the right places and word of mouth, the business gets more students. Excel provides a plan and makes calculations easy but the business has to work the plan!

Further reading:
Steve’s Financial Modeling Tutorial

Leave a Reply

Your email address will not be published. Required fields are marked *