Financial Modeling

Financial Modeling


 

Excel is a combination of various spreadsheets that can be used for storing, organizing and working on data. This course is designed for professionals who want to explore this dynamic tool and learn practical usage of basic and advanced excel functions for constructing various dashboards & models. By creating your own user-friendly models, you will be able to express your thoughts and findings in a more effective way.

 

This program will teach the participants how to build their own interactive dashboards/models “from scratch” to practice blending Accounting, HR, Marketing or Banking concepts and Excel skills.

 

Learning Objectives

After completing this course, the participants will be able to:

  • Build different dashboards from scratch
  • Modify and improve an inherited model
  • Select the most appropriate formula to achieve the desired outcome
  • Identify common errors in data analysis models
  • Mitigate errors by building in error checks
  • Prevent incorrect use of their model by protecting worksheets
  • Validate data entry by setting data entry parameters
  • Create a navigation page to help users find their way around their model
  • Develop drop-down boxes which enable a model to produce a series of results depending on the selected scenario variable
  • Gain an in-depth understanding of how to build a business case
  • Communicate the results of their model clearly and concisely
  • Make use of Excel and implement best practices, efficient formula construction, and appropriate driver selections
  • Use Advanced Excel functions to present various sensitivities to projected metrics
  • Fix circularity problems, iteration, and other common modeling troubleshooting

 

 

Course Sections

The program consists of the following three sections:

Section 1 – Basic Excel

  • Introduction to Excel
  • Navigate Worksheets and Workbooks
  • Purpose of modeling & constructing dashboards

Section 2 – Exploring Excel as a Tool for Financial Modeling

Part-1 Using Excel Functions

  • Mathematical Functions
  • Statistical Functions
  • Financial Functions
  • Logical Functions
  • Lookups & Reference Functions
  • Text Functions
  • Date & Information Functions
  • Offset, Index, Match & Indirect Function
  • Error Function

Part-2 Working with Charts

  • Creating a Chart with Chart Wizard
  • Moving a Chart
  • Resizing a Chart
  • Changing a Chart Type
  • Editing Chart Text
  • Modifying Chart Options
  • Formatting Category & Value Axis Data
  • Formatting a Data Series
  • Changing a Chart’s Source Data
  • Using Combo box for creating Dynamic chart

Part-3 Working with Graphics

  • Using the Drawing Toolbar
  • Formatting Objects
  • Inserting WordArt
  • Inserting an Organization Chart
  • Modifying an Organization Chart

 

Part-4 Working with Excel Application

  • Creating Data Validation
  • Creating Hyperlinks
  • Using Conditional Formatting
  • Using Paste Special
  • Freezing & Unfreezing Panes
  • Putting the data from Text to Columns
  • Inserting, Viewing & Editing Comments
  • Removing Duplicates

Part-5 Importing Data

  • Importing Data from Web into Excel
  • Importing Text Data into Excel

Part-6 Working with Ranges

  • Naming a Range
  • Using a Named Range
  • Deleting a Named Range
  • Using Name Manager for Dynamic Range
  • Using Name Manager for Dynamic Graphs
  • Create Table
  • Using Array Functions

Part-7 Pivot & Data Analysis Tools

  • Tracing Formula Precedents
  • Tracing Cell Precedents
  • Tracing & Fixing Errors
  • Creating a Pivot Table
  • Rearranging a Pivot Table
  • Filtering Pivot Table Data
  • Creating a Pivot Chart
  • Adding Formula in Pivot Table
  • Using Covariance
  • Using Correlation
  • Using Regression Analysis
  • Using Moving Averages
  • Using Descriptive Analysis

Part-8 Summarizing Data

  • Adding Subtotal
  • Sorting
  • Applying Auto & Advance Filter
  • Adding Grouping & Ungrouping
  • Using Fill Series

Part-9 Sensitivity Analysis

  • Using Goal Seek
  • Using Data Tables
  • Creating & Displaying Scenarios
  • Using Solver
  • Using Form Controls – Spinner, Scroll bar etc.

Part-10 Workgroup Collaborations

  • Locking & Unlocking Cells
  • Protecting a Worksheet
  • Protecting a Workbook
  • Creating a Shared Workbook
  • Password Protecting a Workbook
  • Linking between various Worksheets
  • Linking between various Workbooks
  • Creating Web Linking

Section 3 – Developing Models/Dashboards

Part-1 Modeling Techniques

  • Using Excel best practices, foundation and exercises
  • Gathering historical documents/information
  • Improving the finished product
  • Performing Scenario Analysis

Part-2 Constructing the Model

  • Building the model step-by-step
  • Creating Marketing Dashboards
  • Creating HR Dashboards
  • Creating Financial Dashboards
  •