Using Excel for Business Analysis
- Understand that Excel is one of the best analytical tools available to you
- The powerful Excel tools available for Business Analysis
- Ways that Excel can take the guess-workout of decision making
An Overview of Financial Modelling
- What is Financial Modelling and why is it important?
- Understand the common uses of financial models and the steps involved in a building a model
- Overview of the best practice financial modelling techniques
- Attributes of a good financial model
- Critique of a poor financial model
Saving Time by Importing Financial Data Directly into Excel
- How to import in to Excel from Pastel and other Financial packages
- Importing data from Main Frames/Mid-Range sources
- Relational Database Queries: SQL
- Moving data between Windows programmes using OLE and DDE
- Utilising the Universal User Interface in Windows
Writing Macros to Perform Multi-Stepped Tasks
- Macro driven refresh and iteration functions
- Macro driven report updating and printing
- Macro driven pivot table analysis and update
Using Excel to Streamline your Work with Costs and Costing
- Ways of identifying, highlighting and eliminating unnecessary costs
- Estimating Processing costs
- Problems with Mark- up calculations
- Monitoring Production Variations
- Conducting Empirical Analysis
- Considering loan risk management
Creating Practical Business Forecasts and Projections
- Forecasting with Excel’s regression functions
- Excel’s smoothing functions
- Different approaches to business planning including deterministic and probabilistic models
- Trends
- Regression analysis
- Smoothing techniques
- Seasonal analysis
Protecting the Security and Integrity of your Data
- Protect your model so that it can be used by anyone with a limited use of Excel
- Locking and protecting cells
- Restricting incorrect data entry with data validations
- Creating error validation messages
- Applying form controls such as spin buttons and combo boxes
An Introduction to Creating and Using Dashboards
- Introduction to the information dashboard
- Do’s and don’ts of dash board design
- Creating fit-for-purpose dashboards by identifying relevant metrics and drivers for different target audiences
- Tying the dashboard back to the business analysis objectives
- Using Excel tools to construct and layout a professional looking report
About the Course: Excel for Financial Managers
In business, every decision has an impact on the overall performance of the organisation. To ensure success it is vital that you are able to manipulate and analyse large amounts of data effectively and efficiently, and this requires you to be highly proficient in MS Excel. This highly practical and intensive
Course will demonstrate how MS Excel can be used effectively for Data Consolidation & Manipulation, Financial Modelling, Scenario Analysis as well as creating professional Reporting Dashboards containing vital business data, forecasts and projections.
The ever-changing business environment has made it more important than ever for business professionals to accurately forecast volatile economic in- puts. Being able to better analyse financial data, predict revenues and costs and assess risks to justify business decisions will give your organisation the best chance of success in today’s competitive environment.
This course is aimed at Financial Managers and other business professionals who need to develop their understanding of MS Excel to better analyse the financial drivers of their business, and understand the impact that business decisions can have on an organisation’s financial performance.
NOTE: At least 2 years’ experience working with Excel in a financial function is a pre-requisite for this course
What can you expect to Learn
- Easily monitoring financial performance by implementing best practice for creating spreadsheets that track profit and cost variation, sensitivity and margin analysis.
- Understanding the most common uses of Financial Modelling, and how to develop models to enable you to make better business decisions
- Streamlining your data management, importing and analysis process by using advanced features such as DDE and OLE to link data from a variety of sources
- Learning to write Macros that will enable you to regularly perform complex multi-stepped tasks in a fraction of the time
- Introducing some of the advanced Excel Formulas that you will find you can’t live without
- Creating powerful Pivot Tables that will enable you to analyse data for trends and make more accurate forecasts
- Mitigating risk and making better business decisions by learning various approaches to“ What-If” analysis that will enable you to play out a variety of financial scenarios
- Using“ Regression Analysis” and“ Smoothing Techniques ”to create practical business forecasts and projections that will lead to better business decisions
- Creating easy-to-read reports using dashboards and charts that link back to the business analysis objectives
- Logical and information functions
- Text functions
- Look up functions
- Date and time functions
- Mathematical functions
- Statistical functions
- Arrays and metrices
- Solving systems linear equations: Matrix algebra
- Formula nesting
- What is a pivot table?
- Understanding the elements of a pivot table
- Creating a simple pivot table
- Changing the layout of a pivot table
- Adding formulas to a pivot table
- Some pivot table tricks
- Various approaches to what-if analysis, including: Data Tables ,Sensitivity Analysis and goal seeking
- Show multiple scenario outcomes simultaneously with one and two dimensional data tables
- Using Goal-Seek
- Using the Scenario Manager
- Manual scenario building
- In-cell drop-down boxes
- Combo-box drop down boxes
- Use of data tables in a marketing context
- The power of the solver function
- LPS Simplex, GRG Non- Liner and Evolutionary Solver