Date: Sunday, 12 February 2017 - 9:00am - Thursday, 16 February 2017 - 5:00pm
Duration: 5 days
This five-day intensive public course is being hosted by a third party training provider. Contact us for more information on attending this event in Dubai. To run a course like this at your organisation, contact us for quote.
The course material includes extensive use of Excel and participants will gain the maximum benefit from this course if they are already competent spreadsheets users. It is designed for users who do use (or will use) Excel on a regular basis, and are comfortable with using its tools and functions.
The course will be taught using the latest Excel 2016 but the models used will also function with any version of Excel. Excel 2003 users will have a small amount of limitation on functionality. The text book also contains instructions for Mac users.
Due to the pragmatic nature of this course, you will need to have access to a computer and therefore be able to practice and discuss with examples. It is imperative that delegates bring their own laptop computers for the duration of the course. Sharing of laptops is not advised.
Specifically designed for business professionals who need to take their Excel skills to the next level, this intensive full day course focusses on the technical Excel skills that will be required for the Financial Modelling course. This very practical course covers everything you need to perform your job and nothing you don’t!
For those who don’t have time to sit through extensive Excel “how to” training, this course will arm you with a range of tools, techniques and formulas essential for Finance. Guaranteed to contain “no fluff”, this optional day will equip you with the skills to produce more efficient and accurate Excel models and increase productivity.
1. What is Financial Modelling?
Typical examples and purposes of financial models
2. Excel or Access?
Tool selection and advantages and disadvantages of Excel and other software for the purpose of Financial Modelling
3. Model Design
Model planning and steps in building your model
4. Skills needed for Financial Modelling
The technical, design, business and industry knowledge required for financial modelling
5. Model Tools & Functions
Which formula or tool is most appropriate in which modelling situation?
6. Building the business case: issues and logic surrounding the calculation of customer acquisition
Practical exercises: Modelling exercises using a range of financial functions and tools. Building a business case utilising financial modelling techniques
1. Modelling Techniques
2. Bullet-proofing your model
Retain model integrity and prevent misuse of your model by learning to restrict incorrect inputs
3. Making a model user-friendly
4. Dealing with uncertainty and risk
5. Create a best, base and worst case scenario on your model. Technical tools for creating scenarios:
6. Displaying final model results and findings
Communicate the results of your model clearly and concisely whilst getting the key message across to the audience
Practical exercise: Building scenarios and perform sensitivity analysis on financial models. Build a property development scenario model. Build a cash flow funding model, including a P&L, Cash flow and Balance Sheet including scenario analysis.
Practical application of modelling in Excel for the purpose of budgeting for organisations
1. Advanced forecasting functions; Time series, exponential smoothing and regression analysis.
2. Modelling for “Stepped” costs when forecasts include both fixed and variable costs.
3. Sales forecasting
Predication of likely sales revenue based on drivers
Simply and easily spread revenue or expenses throughout the budget period based on season variations
5. Building an expense budget
Based on given assumptions, we will build a budget for 12 months
6. Contingency Planning
Calculating a contingency based on assumptions about probability and impact of unexpected events
7. Budgeting for capital expenditure
Capital expenditure is budgeted for cashflow and then depreciated
8. Cash flow budgeting
Translating our profit and loss statement into a cashflow forecast
9. Reporting against budget; variance analysis
Creating a template into which actual and budget can be compared and analysed
Practical Exercise: Create a model in Excel which will calculate volume-dependent “stepped” costs.
1. Charting and graphing fundamentals – how to build charts
2. When to use a table or charts; combining tables and charts using chart data table and data bars
3. Choosing the correct chart to display your findings
4. Visual Design - dos, don’ts and common mistakes in graphical presentation of data and reports
5. How to present easy to follow information concisely and clearly
6. Pivot Tables and their role in financial modelling
Practical exercise: Build your own flexible report including a dynamic chart
Demonstration and overview of the following:
1. Tiering tables
One of the more complex and widely used calculations in financial modelling. Discussion of two different methods of calculation; a simple and progressive table
2. Pricing models
Calculating profitability and break even points at various prices
3. Dynamic charting
Create a user-friendly report with a dynamic chart based on drop-down selections
4. Modelling for risk assessment
Assess project risk with a risk assessment model using conditional formatting
Practical Exercise: Forecast a sales cycle based on historical data by calculating the seasonality index and adjusting the forecast accordingly.