Basic Financial Statements
In this part of the Excel Virtual Workshop series we will look at using Excel to create a couple of simple financial statements, namely the profit and loss statement and a balance sheet. This will introduce the principles that can be used across a number of different financial modelling situations.
Profit and Loss
Start Excel and ensure that you have a blank sheet, and enter the following on the sheet:
To achieve the title spanning the columns A1-E1 we need to select them and
then use the merge and center button 
Next we are going to start entering the numerical data.
We can format these numbers to appear on screen however you want. Select cells C4-D23 then Format / Cells and select custom.
Select the '#,##0' type and click OK. The numbers should now have been formatted for thousands.
Next we need to add the missing figures, which are derived from calculating other values. This done by using formula similar to that we used in Part 1. Enter the following formula in each of the cells.
| Cell | Formula | Purpose |
| C7 | =SUM(C5+C6) | Adds Opening Stock and Purchases... |
| D9 | =SUM(C7-C8) | ...subtracts the Closing Stock to get Cost of Goods Sold |
| D11 | =SUM(D4-D9) | Produces Gross Profit on goods sold |
| D21 | =SUM(C13:C21) | Adds all the expenses |
| D23 | =SUM(D11-D21) | Subtracts expenses from Gross Profit to get Net Profit |
Next want to format a few cells, making things easier to read by inserting accounting lines to signify totals. Select Cell C6, then Format /Cells and then Border tab.
Ensure that the underline box is depressed and click OK. You should now see the underline in cell C6 signifiying a total. Repeat this formatting until you have a worksheet which looks like the one below.
Finally we are going to set a print area so that only the data between A1-E23 on the worksheet is printed (useful if we have other information or graphs on the worksheet). Select cells A1-E23 and then File / Print Area /Set Print Area. This can obviously be removed by using File / Print Area /Clear Print Area.
The Balance Sheet
The Balance sheet is similar to the financial statement we have produced above, except the idea here is to have Assets = Liabilities at the end and thus have the company's 'Balance'. On a new sheet fill in the data as shown.
Next select Cells B5-C24 and format these cells the same way as you did previously, but choose the '#,##0.00' type this time.
We are now going to add the formulae to the empty cells to generate the final balances. (Note: creating the 2000 formula can be done by dragging the corner of the adjacent 2001 cell, as done in part 1).
| Cell | Formula | Purpose |
| B8 | =SUM(B5:B7) | Adds Current Assets for 2001 |
| C8 | =SUM(C5:C7) | Adds Current Assets for 2000 |
| B11 | =SUM(B9-B10) | Subtracts depreciated value from the original cost - 2001 |
| C11 | =SUM(C9-C10) | Subtracts depreciated value from the original cost - 2000 |
| B12 | =SUM(B8,B11) | Adds Current and Fixed Assets - 2001 |
| C12 | =SUM(C8,C11) | Adds Current and Fixed Assets - 2000 |
| B17 | =SUM(B15:B16) | Adds Current Liabilities - 2001 |
| C17 | =SUM(C15:C16) | Adds Current Liabilities - 2000 |
| B19 | =SUM(B17:B18) | Generates Total Liabilities - 2001 |
| C19 | =SUM(C17:C18) | Generates Total Liabilities - 2000 |
| B23 | =SUM(B21:B22) | Adds money owed to the owners - 2001 |
| C23 | =SUM(C21:C22) | Adds money owed to the owners - 2000 |
| B24 | =SUM(B19,B23) | Produces total owed - 2001 |
| C24 | =SUM(C19,C23) | Produces total owed - 2000 |
Once you have entered all the data you should see that the Total Assets and Total Liablities are the same figure and thus 'Balance'.
Most financial modelling in Excel follows this formula, of creating a layout and entering formulae to generate totals automatically.
Lastest 10 Threads - view all
Linking and IF Statment
Posted By: Jay at 16:02:35 on Monday the 12th of September 2005
Hi, I was wondering how I could link an IF statment when a cell was Filled in black? Can this be done? If so, I'd love to know because I've been trying for some time and can't find any help on this situation. I can link numbers and text, but can figure out how to link it when a perticular cell is filled in. Thanks again.
macro in drop down list
Posted By: Kitz at 10:06:25 on Saturday the 3rd of September 2005
hi
i have create a drop down list with 36 entries. I am trying to create individual macro for each entry. So if i select entry 1 it will populate data related to it. (eg, contact name, tel no, email address)
Every macro i create over rides the pervious one, i cant get it to work as individual one
please help
thanks
financial ratio & statment
Posted By: eklili at 08:51:42 on Saturday the 23rd of July 2005
hello how are you ? i need a excel format of balance sheet and profit and loss and cosh flow and retained earning statment and financial ratio for dummy company thanks bye
Re: financial ratio & statment
Posted By: Dilshad at 17:25:20 on Tuesday the 6th of September 2005
i need a excel format of balance sheet and profit and loss and cosh flow and retained earning statment and financial ratio for dummy company
Re: financial ratio & statment
Posted By: Suresh at 22:59:45 on Friday the 19th of January 2007
i need a excel format of balance sheet and profit and loss and cash flow and retained earning statment and financial ratio for dummy company
black jack card counting
Posted By: black jack card cou at 11:10:32 on Wednesday the 20th of July 2005
For example draw black jack card counting flop push tight stack http://www.theaddedtouch.net/black-jack.html sports black jack http://www.theaddedtouch.net/black-jack.html progressive tipping player!
Statement of Accounts
Posted By: Antonette at 15:09:34 on Monday the 18th of July 2005
Please could you send me the layout of the Financial Statement
sending all accounting terms
Posted By: Areef at 07:56:49 on Wednesday the 13th of July 2005
Hi, with reference to the question
question
Posted By: RajaKamakshi.M at 15:35:38 on Monday the 20th of June 2005
how to consolidate the dates.like say for example. if we have date greater than or equal to 15 that shud be consolidated to the end of the same month and if we have date lesser than 15 then it shud be consolidated to end of the previous month. it shud be arranged according to the leap year also.
send all accounting terms for me
Posted By: PETER OBI at 15:48:31 on Sunday the 22nd of May 2005
i need 50 accounting assets and liabilities.
h&V look up
Posted By: sajeev at 10:01:30 on Wednesday the 6th of April 2005
please tell me on the utility of horizontal and vertical look up in financial modelling. further, pls tell me to trap the month based on financial year. Eg ; suppose the operation time of project starts at say jun3 2005 where in the next financial ending would take place on 31/03 /2006. in this case how we could trap the revenues and expenses during the period ie from june 2005 to march 2006 to acess the project return in that particular FY.
with thanks & regards
Shari Ballard
Posted By: Alice Graham at 11:10:27 on Wednesday the 5th of September 2007
uttermost hyperadenosis wainrope mansarded virtuosa unrodded condylome statute 4 http://www.angelfire.com/ahfohm/1.html 7 http://www.angelfire.com/ahfohm/8.html 6 http://www.angelfire.com/ahfohm/5.html 15 http://www.angelfire.com/ahfohm/9.html 10 http://www.freewebs.com/ielohv/12.html 2 http://www.freewebs.com/ielohv/15.html 14 http://www.freewebs.com/ielohv/13.html 12 http://www.angelfire.com/ahfohm/4.html 7 http://www.angelfire.com/ahfohm/8.html 3 http://www.freewebs.com/ielohv/18.html
Reply to this comment