Financial Modelling for Planning, Forecasting & Financial Analysis

Financial modelling is extremely useful for planning and forecasting to make business decisions as well as performing financial analysis. In this practical program, participants will learn practical skills to construct robust financial models that are properly structured.
Excel is the de facto modelling language today, so in order to get the best out of a model a modeler would be wise to have a good understanding of the functions in Excel, what they do, and where they can be used in a financial model Delegates will be taken through various features & functions of Excel

INTRODUCTION

MODELLING TIPS, TOOLS & ROUTINES

• Useful Excel tips and tricks
• Naming cells, ranges & formulas
• Formatting cells with suitable number formats
• Using conditional formatting
• Data validation and drop-down lists
• Hyperlink for source referencing
• Importing text files into Excel (e.g. from the stock exchange)

MAINTAINING THE INTEGRITY OF THE MODEL

• Spreadsheet structure
• Protecting your worksheets & workbooks
• Locking cells and hiding formulas
• Restricting data entry
• Using data validation drop-down boxes to assist the user in entering data
• Using data validation to restrict data entry
• Setting up warning messages

USEFUL MODELLING FUNCTIONS

• Logical tests
• Choosing between alternatives
• Summarizing data
• Looking up data
• Working with words (e.g. preparing reports with reference to your model results)

DATABASE TECHNIQUES

• Searching (filtering) for data
• Subtotaling the filtered data
• “Advanced filtering”
• Creating pivot table reports to easily analyze a large amount of data

CONSOLIDATIONS

• Consolidations with pivot tables
• Using Data/Consolidate
• Recording macros to do the consolidation

TABLES OF RESULTS

• Preparing data tables showing a table of results based on changing input
• What ifs
• Goal seeking

SETTING UP “CONTROLS”

• Inserting Option buttons and spinners into your model

Practical Exercise:

• building a monthly budget model
• Revenue, cost and profit modeling
• producing cash flow statements
• financial ratios
• viewing financial ratios graphically & Dashboard
• Column chart
• Line graph
• Pie chart

At the program completion participants will be able to:

• Understand how to structure financial models properly.
• Understand how to maintain the integrity of the model.
• Understand the link between Income Statement, Cash Flow Statement and Balance Sheet.
• Build and manipulate a budget model using what-if and goal seek.
• Consolidate files and record macros to do so.
• Understand the prime financial ratios, and how to graph them out.
• Greatly improve their knowledge of the Excel functions and tools available for modelling and general spreadsheet applications.

Business owners, directors, managers, commercial managers, and professionals (financial accountants, business analysts, finance managers, financial analysts, management accountants, financial controllers and budget managers) who uses financial models for business decisions, financial planning and analysis.