About Workshop
Microsoft Excel is the most commonly used spreadsheet application in the world. Excel can play a crucial part in your business, and is essential for producing accurate numeric data. The vast majority of businesses use Microsoft Excel every day to produce accounting and financial reports, budgets, and many more crucial numeric data tasks, but few appreciate the extent of the features and functions within the software.
This course will enable you to maximize the potential of the powerful Microsoft Excel software to produce more professional business information. This course is designed to equip participants with the technical knowledge of building financial models in Excel. The aim is to bridge the gap between financial theory and practice. Modeling skills acquired to develop sample applications in this course will provide participants with the tools and confidence to build their own finance applications.
Projects:
Developing a Cash Flow Statement
Developing an Income Statement
Developing a Balance-Sheet
Depreciation Calculation
Workshop topics to be covered:
1. Converting Amount into Words Automatically
2. Formula Auditing
3. Goal Seeking
4. Solver Tool
5. Working with Scenarios
6. What-IF Analysis
7. Ratio Analysis
8. Break-Even Analysis
9. Data Tables
10. Charts in Financial Modeling
11. Loan Repayment Schedule
12. Calculating Interests
13. Discounted Cash Flow Analysis (DCF)
14. Forecasting
15. Pivot Table Report
16. Pivot Table Chart
17. Absolute and relative cell references (use of the $ key)
18. Types of References Allowed In A Formula
o Referencing cells from another worksheet
o Referencing a block of cells
o Referencing non–adjacent cells
o Referencing entire rows
o Referencing entire columns
19. Working simultaneously on cells in different worksheets
20. Data validation
21. Validation List or Combo box
22. Advance Filtering
23. Usages of Wildcard characters in excel functions (CountIFs/SUMIFS/MATCH)
24. Essential functions :IF, Text, COUNTIFs, SUM, SUMIFs, SUMPRODUCT, VLOOKUP, AND, OR, MATCH, SEARCH, MID, LEFT, RIGHT, ROUND, ROUNDUP, ROUNDDOWN, MAX, MIN
25. Error handling functions: ISNA, ISERR, ISERROR, IFERROR (#N/A, #Value!, #Ref!, #Div/0!, #Num!, #Name?, #Null!)
26. Date Functions: DATEDIFF, YEAR, MONTH, DAY, NOW, TODAY
27. Protecting and locking worksheets
28. Names in formula
29. Depreciation
o Depreciation of an asset over a single period
o Straight-line and Sum-of-year’s depreciation methods
o Depreciation of an asset over specified period using declining balance methods
30. Financial Functions: PV, NPV, XNPV, IRR, MIRR, XIRR, RATE, NPER, FV, FVSCHEDULE, PMT, PPMT, IPMT, etc.
![](Templates/"../images/bodyedge_tr.gif"width="2"){kind=small}
![](Templates/"../images/bodyedge_tl.gif"width="2"){kind=small}
