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:
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).
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 |
Bhaktiulhas
(SERVICE)
(37 Points)
Replied 20 December 2009
useful ,
we are doing CMA data at off., at that time we need amt. in lacs, so how to format a cell ?
GSTR 9 and 9C for FY 23-24 as amended by Notification 12/2024 dated 10th July 2024(with recording)
CAclubindia
