Outcomes
By the end of this training course, trainees will be able to :
- Apply different types of MS Excel functions and applications that are needed to prepare spread sheets.
Target Group
- All administrative, accountants, financials and secretariat employees.
Contents
- Concepts and the basics of the Excel applications:
- Tips and tricks.
- Absolute and relative references .
- R1C1 reference style.
- Functions: ABS, If, Sum if, Round, Round Up, Round Down, And, If and, Or , If or , Match , Index, Index Match, Sum product, Int , Mod , Average If, If Error, aggregate.
- VLOOKUP, HLOOKUP, XLOOK and LOOKUP for Business/Finance/Accounting tasks.
- Preparation of reports.
- Conditional formatting formulas using Excel .
- Manage rules in excel .
- Data validation in Excel.
- Combine values from multiple ranges into one new range.
- Remove duplication.
- Text to columns.
- Using file manager, define name and create from selection in excel to prepare spread sheets .
- Scenario analysis.
- Goal seeking and back solving.
- Using excel to prepare financial statements: Statement of financial position, Statement of comprehensive income and Statement of cash flows.
- Financial statements and ratios .
- Excel and visual basic application (VBA) .
- Recording Macros.
- Excel functions in calculating depreciations: SLN, DB, DDB, SYD.
- Break–even point in Excel .
- Laying out the pivot table.
- Modifying the pivot table.
- Pivot table examples.
- Grouping pivot table items.
- Creating a calculated field or calculated item.
- Referencing cells within a pivot table – GETPIVOTDATA.
- Producing a report with a pivot table.
- Pivot charts power.
- Pivot power.
- Query.
- Loan amortization scheduled.
- Practical exercises.
