Financial Modelling In Excel

This course is designed for business professionals who need to explore the practical usage of advanced excel functions in a financial model. By creating your own user-friendly financial model, you will learn to translate business concepts into a structured format to identify weaknesses and predict future performance.

During the course, participants will create their own financial model to take away and use for future reference. You will learn how to design and create a user-friendly model which can then be used by anyone with limited knowledge of Excel.

You will learn how to:

  1. Build a financial model from scratch, or modify and improve an inherited model
  2. Select the most appropriate formula to achieve the desired outcome
  3. Identify common errors in modelling & mitigate errors by building in error checks
  4. Prevent incorrect use of your model by protecting worksheets
  5. Validate data entry by setting data entry parameters
  6. Develop manual scenario selection
  7. Mitigate liability by providing assumptions
  8. Gain an in-depth understanding of how to build a business case
  9. Communicate the results of your model clearly and concisely

TRAINING MATERIAL OUTLINE:

A. About Financial Modelling

  1. What is Financial Modelling?
    Definition of a financial model.  Can all spreadsheets be called financial models?
  2. Model Design
    Designing and planning the layout of your model.   Considering factors such as the purpose and audience of the model.
  3. Skills needed for Financial Modelling
    The technical, design, business and industry knowledge required for financial modelling
  4. Best Practice in Financial Modelling
    Overview of the six points of financial modelling best practice

B. Excel Tools

  1. Excel Versions
    Upgrading to Excel 2013 and technical differences between versions. Considerations when building a model for users of different versions.
  2. Conditional Formatting
    Creating an automatic variance alert.  Using icon sets, colour scales and data bars to add visual interest to model outputs.
  3. Hiding and Grouping
    Keep your model tidy and easy to follow by hide unnecessary information or unused parts of the model whilst still following best practice guidelines
  4. Dos and Don’ts for Linking Between Files
    Ways to improve model integrity and reduce errors between linked files

C. Financial Modelling Techniques

  1. What Makes a Good Financial Model?
    Attributes of a good model such as user-friendly and structural features
  2. Strategies for Reducing Errors
    Techniques to employ during the model building process to reduce the potential for formula or logic error
  3. Building Error Checks
    Creating in-built, self-balancing error checks and error alerts
  4. Bullet-Proofing your Model
    Using worksheet protection to prevent entry, and restricting data entries using data validations.  Prevent misuse of your model by restricting incorrect inputs.
  5. What-if Analysis with Goal Seek
    Back-solve from a desired formula output to determine a model input using the goal seek Excel tool
  6. Overview of Scenario Analysis Methods
    Technical methods of creating scenario and sensitivity analysis in Excel

D. Essential Formulas

  1. Cell Referencing & Named Ranges
    Applying absolute and relative cell referencing and understanding its importance in Financial Modelling.  Using named ranges for assumptions reference.
  2. Logical Nested Functions
    Using IF, OR and AND functions, and nesting these together to create simple but intelligent formulas for use within financial models
  3. Aggregation Functions
    Applying the COUNTIF and SUMIF functions to reports and data summaries
  4. Using a VLOOKUP Function
    Correctly building this much-loved and often over-used Excel function
  5. Tiering Tables
    Practical application of one of the more complex and widely used calculations in financial modelling, such as tax calculations and volume break discounting
  6. Using the FORECAST / TREND Function
    Hypothetically forecasting future data based on historical trends using simple regression analysis in Excel
  7. Formula Selection
    Which formula or tool is most appropriate in which modelling situation?

E. Building a Business Case

Case Study: Build an individual business case using a range of financial functions and tools utilising best practice financial modelling techniques

  1. Calculating Staff costs
    Modelling with consistent, nested formulas to calculate costs for employees with variable start and end dates, including compounding inflation
  2. Forecasting customer numbers
    Calculating customer acquisition numbers from the potential pool of customers with documented assumptions. Mitigate liability by including appropriate caveats and key assumptions.
  3. Modelling market penetration in a business case
    Using assumed takeup rates to model market penetration
  4. Forecasting Product Profitability
    Assess business case feasibility cashflow forecast
  5. Project Evaluation
    Evaluate project feasibility with IRR (Internal Rate of Return), NPV (Net Present Value) and the payback period

F.  Analysing & Presenting your Model

Practical: Create a best, base and worst case scenario on your model. Select from the drop-down box and watch the results change

  1. Scenarios and Sensitivity Analysis
    Manual sensitivity analysis, creating drop-down switches for scenario selection. Adjusting inputs variables to impact outcomes.
  2. Model Documentation
    Summarising key assumptions, documentation and source referencing, Writing operation instructions
  3. Presentation of Model Output
    Summarising results and display of findings. Communicate the results of your model clearly and concisely whilst getting the key message across to the audience. Summarising model data into a presentation

RECOMMENDATION FOR PARTICIPANT: Participant must bring Laptop for practice sessions

INSTRUCTOR :   Abdullah Fajar, S.Si.,M.Sc.

VENUE :   Kagum Group Hotel Bandung (Golden Flower, Banana Inn, Serela, Gino Feruci), Amaroossa Hotel, Noor Hotel, Grand Setiabudi Hotel, dll

TRAINING DURATION :   4 days

TRAINING TIME :  

  • 18 Jan 2022-21 Jan 2022
  • 15 Feb 2022-18 Feb 2022
  • 15 Mar 2022-18 Mar 2022
  • 04 Apr 2022-07 Apr 2022
  • 09 May 2022-12 May 2022
  • 14 Jun 2022-17 Jun 2022
  • 19 Jul 2022-22 Jul 2022
  • 30 Aug 2022-02 Sep 2022
  • 20 Sep 2022-23 Sep 2022
  • 18 Oct 2022-21 Oct 2022
  • 15 Nov 2022-18 Nov 2022
  • 05 Dec 2022-08 Dec 2022

INVESTATION PRICE/PERSON :

  1. Rp. 8.500.000/peserta (bayar penuh)  atau
  2. Rp. 8.250.000/peserta (early bird, yang membayar 1 minggu sebelum training)  atau
  3. Rp. 7.950.000/peserta (peserta bergroup yang terdiri dari 3 peserta atau lebih dari 1 perusahaan yang sama)

FACILITIES FOR PARTICIPANTS:

  1. Modul Training
  2. Flash Disk berisi materi training
  3. Sertifikat
  4. ATK: NoteBook dan Ballpoint
  5. T-Shirt
  6. Ransel
  7. Foto Training
  8. Ruang Training dengan fasilitas Full AC dan multimedia
  9. Makan siang dan 2 kali coffeebreak
  10. Instruktur yang Qualified
  11. Transportasi untuk peserta dari hotel penginapan ke hotel tempat training – PP (jika peserta minimal dari satu perusahaan ada 4 peserta)
Pendaftaran Sementara
  1. (required)
  2. (required)
  3. (valid email required)
  4. (required)
  5. (required)
  6. (required)
  7. Mohon tidak memesan tiket perjalanan / akomodasi penginapan sebelum ada kepastian training akan berjalan. Jika ada pertanyaan bisa menghubungi 0851-0249-5051 dan 0899-812-1246
  8. Jika Anda mendaftarkan training ini untuk orang lain mohon cantumkan nama dan email di bawah ini
  9. Captcha
 

cforms contact form by delicious:days