How to create financial statement models in Excel

Written by stephen byron cooper Google
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to create financial statement models in Excel
Follow HMRC's requirements to build your P&L; template. (Microsoft Corporation)

The balance sheet and the profit and loss account constitute your company’s financial statements. Her Majesty’s Revenue and Customs provides a form to fill in for submitting your taxes which details the contents of your profit and loss account. Therefore creating a template in Excel to generate this document following HMRC’s format makes sense. You can also create a template for the balance sheet.

Skill level:
Moderately Easy

Other People Are Reading

Instructions

  1. 1

    Open Excel with a new workbook. Click the right mouse button when the pointer is over the first tab at the bottom of the sheet. Select “Rename” from the right-click menu and type in “Profit and Loss.” Rename the second tab to “Balance Sheet.”

  2. 2

    Stretch the first column so it is the width of three regular columns. Highlight the first row. Increase the font size to 20 and press the “bold” button. Type in A1: Profit and Loss for the year ended 31st March. Highlight the third row and click the right justify button. Click the right mouse button and select “Format Cells” from the pop-up menu. Designate the “Number” category, select brackets to show negative numbers and set decimal places to 0.

  3. 3

    Enter the following values in the stated cells: B3: £ B4: £ A4: Sales A5: Cost of sales A6: Gross profit or (loss) A8: Overheads A9: Distribution costs A10: Administrative expenses A11: Other operating costs A12: Total Overheads A14: Operating profit or (loss) A16: Interest receivable A17: Interest payable A18: Gross profit or (loss) A19: Tax A20: Net profit or (loss)

  4. 4

    Enter your sales figure in C4 and you cost of sales in C5. Enter your costs in B9 to B11. Enter any interest you earned in the year in B16 and all interest you paid in B17 and enter your tax payable in B19.

  5. 5

    Click in C6. Click the arrow next to the borders button in the Home tab of the menu bar and select “Top border.” Enter the formula: =C4-C5. Click in C12 and select the “Top border.” Enter =sum(B9:B11) and press the “Enter” key. Click in C14, select the “Top border” and enter: =C6-C14. Click in C18 and select the “Top border.” Enter the formula: =C14+B16-B17. Click C20, select “Top border,” click the border button again and select “Double bottom border.” Enter the formula: =C18-B19.

  6. 6

    Click on the “Balance Sheet” tab at the bottom of the sheet. Click on the “1” label of the first row, set the font size to 20 and click the bold button. Type “Balance Sheet as at:” in A1 and enter the date of the balance sheet in C1. Highlight columns B and C. Click the right mouse button and select “Format Cells” from the pop-up menu. Designate the “Number” category, select brackets to show negative numbers and set decimal places to 0.

  7. 7

    Type in the following data in the designated columns: B3: £ B4: £ A4: Assets A5: Fixed assets A6: Intangible assets A8: Current assets A9: Bank A10: Stock A11: Debtors A14: Total assets A16: Less A17: Creditors due within a year A18: Creditors A19: Loans A22: Creditors due after a year A23: Loans A27: Net assets A30: Capital and reserves A31: Share capital A32: Retained profit

  8. 8

    Enter data in the following fields: B5: your fixed asset valuation B6: your intangible assets B9: all the cash in the business B10: the value of your inventory B11: the value of your invoices that customers have not yet paid B17: the value of outstanding bill and invoices you have not yet paid B18: total of short term loans and overdrafts C23: Long term loans C31: the total face value of all shares

  9. 9

    Click in C7. Select “Top border.” Enter: =B5+B6. Click in C12. Select “Top border.” Enter: =sum(C9:C11). Click in C14. Select “Top border” and then select “Bottom border.” Enter: =C7+C12. Click in C20. Select “Top border.” Enter =B18+B19. Click in C25. Select “Top border” and then select “Bottom border.” Enter: =C14+C20. Click in C27, select “Top border,” click the border button again and select “Double bottom border.” Enter =C20+C25. Click in C32. Enter: =C34-C31. Click in C34, select “Top border,” click the border button again and select “Double bottom border.” Enter: =C27.

  10. 10

    Review your model with your current figures. Save the workbook and then remove all the figures you entered for the current year’s data. These were tests of the model. Save the workbook as a template to use for future years.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.