Creating powerful dashboard reports using Excel

How to use Microsoft Excel to create reports that will wow your audience

This article introduces the main spreadsheet modelling techniques used in financial accounting and preparation of financial statements. At the end of this article, you will be able to:

  • Use look-ups and other logical formulae and functions
  • Manipulate data, including preparing advanced reports and pivot tables
  • Use systems data to prepare financial statements
  • Prepare practical models for accounting functions like fixed assets and monthly reports
  • Perform complex functions and array formulae

Most companies and organisations keep most of their accounting records on accounting packages such as SAP, Oracle and Sage. Whilst these systems can produce basic reports such as Income Statements and Balance Sheets, the output is often not in the form needed for external reporting and analysis. More often than not, the Finance Department will then use spreadsheet applications such as Excel to represent the financial results in a format that can best serve the needs of the users. With Excel, financial statements can be manipulated to produce particular data sets, and the layout can be structured to suit the organisation’s reporting framework.

Advancements in financial modelling require modern day finance professionals to be able to present financial information in a clear, informative, and appealing format. It is now common for periodic financial and operational performance reports to be accompanied by graphs, charts and tables, to enhance visual analysis. It is widely accepted that the way in which information is presented can have a significant influence on the way in which the recipients interpret, make perceptions and judge the information presented.

Throughout this section, we will go through 3 case studies, in which you will be asked to prepare three reports:

  • Monthly Performance Report
  • Capital Expenditure (Capex/Fixed Assets) Report
  • Weekly Debtors Report

During this process, we will use the various Excel tools and techniques used by modern-day finance professionals in their day to day work.

This course material comes with a companion guide, which is available from our bookstore for only £20. To order your copy please call us on 0800 865 4770 or email us at [email protected]

Please click this link for the rest of the article.