MS EXCEL FOR FINANCE – 2 DAYS
EF2D
MS EXCEL FOR FINANCE – 2 DAY
EF2D
PROGRAM OBJECTIVE
To enable participants leverage advance Excel functionalities for data analysis, business problem solving, reporting, interpreting results and decision making.
WHO SHOULD ENROLL
Managers and Executives (across industry verticals) in: Accounting, Finance, Treasury, MIS, Project managers, Business analysts who create reports, analyzes data, or use spreadsheets in their work and who need to take business decisions based on analysis of data.
TRAINING METHODOLOGY
Hands – on exercises in Excel 2010 / 2013 using learn-by-doing approach on real world (realistic) data files interspersed with brief lectures and brain storming sessions.
COURSE MATERIAL
Participants shall be given:
- Over 100 MB of realistic data files,
- Comprehensive course material and
- Stationary
INDICATIVE COURSE CONTENT
module # 1
MULTI-DIMENSIONAL ANALYSIS OF DATA & REPORTING
DAY 1 – DURATION: 3.5 HRS.
Use Pivot tables to:
- Make pivots from data exceeding one million rows!!
- Slice & dice data,
- Tabulate, cross-tabulate,
- Get running totals (e.g. running balance of a bank account),
- View amounts / quantities as % of rows / columns,
- Produce Top 3 or Top 10 reports,
- Group sales by weeks, months, quarters, etc.,
- Apply the Pareto’s (80-20) principal to identify high value transactions,
- Analyse large data sets (even more than million rows).
module # 2
THE ECONOMIC ORDER QUANTITY INVENTORY MODEL
DAY 1 – DURATION: 1HR.
Learn to setup an EOQ model in Excel and determine the order quantity that minimizes the sum of annual inventory and order costs for a trader or the batch size that minimizes the sum of annual inventory and setup costs for an organization that manufactures its products in batches.
module # 3
EVALUATE PROJECTS
DAY 1 – DURATION: 1.5 HRS
Learn to evaluate projects using Net Present Value (NPV) and Internal Rate of Return (IRR) criteria in Excel.
Learn how financial functions in Excel like NPV, XNPV, IRR, XIRR, PV, FV, PMT, PPMT and IMPT can help a finance manage make sound finance decisions.
module # 4
ASSET ALLOCATION MODELING
DAY 2 – DURATION: 1.5 HRS.
Learn to determine how to allocate my investment portfolio between stocks, Treasury bills, and bonds. What asset allocation over a 5-year planning horizon will yield an expected return of at least 10 percent and minimize risk?
module # 5
WHAT-IF ANALYSIS
DAY 2 – DURATION: 2.5 HRS.
Use Excel as a Decision Support System (DSS) to support semi-structured or unstructured decisions.
Learn to use goal seek to perform PV (Price-Volume) analysis.
Learn to use data tables to make loan amortisation schedules in Excel.
Learn to use Scenario Manager to create best, worst, and most-likely scenarios for business.
module # 6
OPTIMISATION
DAY 2 – DURATION: 2 HRS.
Use solver to optimise your objective under multiple constraints. E.g. determine which projects to undertake under given multiple constraints and maximize profits.
ADDITIONAL / OPTIONAL MODULES
AOM1 – Macros – a definitive guide
DURATION: 3 HRS.
Learn ALL that is required to automate routine tasks in Excel
- Introduction to Macros,
- Recording Macros,
- Editing recorded Macros,
- Creating functions,
- Creating macros to automate routine tasks,
- Creating Macros that use data ranges which may vary in size,
- Finding free Macro codes, editing them & adapting to your requirement.
AOM2 – MS Excel Top 51 – Tips & Tricks
DURATION: 3 HRS.
This module is a compilation of such questions the answers to which every Excel business user is keenly interested to know. To these have been added lesser know, but extremely powerful, ‘secrets’ of Excel. The result is a listing that is titled “Top Excel Tips and Tricks”. Knowing these ‘Top Excel Tips and Tricks’ would help participant save tens of hours every month.
The tips are carefully categorized and grouped under heads like:
- Data entry and Data Validation,
- Navigation & Selection,
- Insert, Format and Copy paste,
- Creating Custom Lists,
- Filling multiple ranges (including non-adjacent ranges),
- Working with Range Names,
- Cell Comments,
- Conditional Formatting,
- Protecting your worksheet (including accessing data from protected worksheets!!),
- Use the Function Wizard, Common functions (AVERAGE, MIN, MAX, COUNT, COUNTA, ROUND, INT),
- Relative, Absolute, Mixed cell references : >,<,= operators,
- Logical functions using IF, AND, OR, NOT including nesting and combinations thereof,
- The LOOKUP function , Date and time functions ,
- INDEX and MATCH functions,
- Auto Filters and Advanced Filters.
COMMITMENT POLICY
We are committed to your success; It is our policy that every organization / individual who has ever been trained at one of our training programs is entitled to a FREE life-time email support for ideas / trouble shooting on implementing Excel for data analytics.
NO Conditions apply!!