Designed for the busy support professional, this course will improve your financial knowledge as well as your technical Excel skills. You will gain an overview of fundamental accounting and financial concepts, and learn how to present the data available to you into a concise report. This is a practical course in which you will develop the skills to build your own financial reports which can be taken away for future reference.
This course is part of the Essential Excel Series:
- Essential Excel for Finance
- Essential Excel for Business Administration
- Financial Reporting for Executive Assistants
Who should attend this course?
This course is aimed at Executive, Administrative, Team or Personal Assistants, Secretaries, Administrators, Coordinators, Office Managers and others in corporate operational roles.
Research has shown that the role of the Executive Assistant has evolved considerably in recent years. Support staff are often expected to initiate their own report writing and even perform some level of financial analysis. This course is designed to arm the modern Executive Assistant with the essential financial and technical knowledge to perform key tasks with accuracy and efficiency.
Prerequisites
This specialised course is not a general Excel training course; rather it teaches the use of Excel in a Reporting context. It is assumed that participants have either undertaken a basic Excel Introduction course, or else are able to:
- Navigate confidently in Excel
- Create and use simple formulas e.g. =A1+A2, =SUM(A1:A3)
- Link between workbooks
Software used
Public courses are run in a computer laboratory with workstations provided. Participants may bring their own laptops if they prefer. The course is demonstrated using the latest version of Microsoft Excel, but students can use other versions of Excel if they prefer. Course materials contain instructions for all versions, and Mac users are also catered for. PCs are used during this course.
Learning Objectives
During the course, participants will create their own Excel reports to take away with them. Each participant is provided with files which they will be able to take their files away for future reference.
You will learn to:
- Tailor the data available to you in the most concise and meaningful way
- Understand basic accounting and financial concepts
- Know the difference between a Profit and Loss Statement, Balance Sheet and a Cashflow Forecast
- Select the most appropriate Excel formulas
- Identify common errors in building reports
- Use techniques to prevent errors
- Create a dynamic chart which changes according to variables
- Display report results clearly in PowerPoint
This course builds on participants’ basic knowledge of simple formulas and incorporates these into Excel based reports.
Course Content
Overview of Excel
Make Excel work for you. Love it or hate it, Excel is the most commonly used corporate reporting tool. We will explore the use of Excel for reporting, and the issues with upgrading to the newest version of Excel.
- Major differences between the Excel versions
- Tool selection – is Excel the best solution?
- Advantages and disadvantages of Excel as a reporting tool
Overview of Financial Reporting
Become familiar with the “language of accounting and finance”. What is a P&L? What’s the difference between a budget and a forecast?
- Management reporting
- Financial Statements
- Balance Sheet
- Profit&Loss (P&L)
- Cash flow Statement
- Capital expenditure (capex) and Operating expenditure (opex)
- Budgeting&Forecasting overview
Other Reports
We’ll discuss other non-financial reports you may be required to build or maintain.
- Using reporting to promote accountability
- Key Performance Indicators (KPIs) and Metrics reporting
- Overview of Dashboards and Scorecards
- Pros & Cons of Pivot tables
Formulas & Other Excel tools
Essential formulas & tools to know and how to use them.
- Applying absolute and relative cell referencing (e.g. A1, $A$1, or A$1)
- Named Ranges
- Basic formulas: SUM, IF, AND, SUMIF and VLOOKUP/XLOOKUP
- Selecting the correct formula
- Adjusting formulas manually
- Working with dates
Error Prevention
Research has shown that more than 90 per cent of corporate spreadsheets have material errors in them*, and many disasters in corporate history have been blamed on formula error! Learn how to avoid errors when building your Excel reports.
- Modelling techniques to reduce potential for error
- Identifying common errors
- Building a simple error check formula
Assumption Documentation
Important decisions may be based on the outputs of your reports. Detailed and well-documented source referencing and assumptions are a crucial part of a well built report.
- Methods of assumptions documentation
- Linking to source referencing
- Writing operation instructions
Budget Reporting
How to build a basic budget report in Excel
- Variance analysis
- Creating an actual vs budget vs forecast report
- Building a variance analysis with “alert flags”
Presenting the Results and Charting
Learn the importance of displaying results clearly and concisely in order to get your message across.
- Using charts or tables to display data
- Creating a dynamic chart which changes according to variables
- Summarising report data into a few key points
Displaying Data in PowerPoint
The results of many reports need to be displayed in PowerPoint. Learn the best way to summarise your data into a PowerPoint Presentation.
- Displaying results in PowerPoint
- Linking Excel and PowerPoint
* “Managing Spreadsheets” Whitepaper by Philip Howard, April 2005
