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.
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 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).
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).
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.
useful ,
we are doing CMA data at off., at that time we need amt. in lacs, so how to format a cell ?
CAclubindia
