• Acquired one of the must-have skills of every accountant. One that is efficient and dynamic and leaving them with more time to collaborate with their CEOs and business owners in making better business decisions to propel the business towards greater growth and excellence;
• Fully understand essential Excel functions and used them in dynamic mega-formulas;
• Create one report for all reporting months. Search for past reports will be a thing of the past. Just by the click of the mouse and the numbers, charts and commentaries of the selected business unit and reporting month / year will be displayed instantaneously;
• Monthly preparation of the reporting pack will no longer be an energy draining exercise but one that can be done in a breeze;
• Generate helpful business reports where business health could be assessed and analysed by looking at the visually stunning charts and graphs;
• Develop interactive models for decision making;
• Perform simulations, sensitivity and scenario analysis (best, expected and worst case) for project / investment evaluation;
• Work with macro and VBA to bullet-proof macro;
• Use pivot table to generate ad-hoc reports. Users of these pivot tables can easily click on the mouse to get different views, without having to drag and drop the field items.
• Generate impressive boardroom presentations with ease.

• Create workbooks, insert / delete row, column, and worksheets
• Save files, copy and paste, and other basic tasks
• Navigate through a workbook
• Use Excel’s menus, toolbars and dialog boxes.

 

Day 1:

 

Day 1 - Essential Excel functions

Providing in-depth knowledge of advanced Excel functions and how they could be combined to provide dynamic mega functions to enable automation in reporting, budgeting, decision making and project / investment evaluations.

This is because some of these formulas on its own are useless. But if we were to combine these formulas, they can become very powerful.

Lessons

§         Viewing Excel Functions §         Cell and Range Referencing §         Range Name §         Lookup & Reference o        Choose o        Match o        Offset o        Vlookup o        Hlookup o        Index o        Indirect §         Mathematics o        Sumif o        Rounding §         Date & Time o        Date §         Information o        IS §         Logic o        IF o        And & Or §         Text o        Left o        Right o        Mid o        Len o        Find o        Search o        Concatenate & Text §         Forms o        Spinner o        Scroll Bar o        Combo Box o        Option Button o        Check Box

After completing Day 1, participants will be able to:

§         Fully appreciate the Excel formulas taught and combine them into mega-formulas to generate dynamic reports, charts, commentaries, controlling and decision making tools.

 

 

 

Day 2:

 

Day 2 - Apply the formulas learnt on Day 1 to generate reports and charts.

 

Lessons

§         Introduction to the importance of building a well structured workbook; §         Understand the main and sub-components of a spreadsheet solution; §         Compilation of Raw Data; §         Set up control block for the workbook; §         Use of form controls to make reports user friendly; §         Set up interactive reports with multi-variables (year, month, reporting country and KPIs); §         Apply conditional formatting to give visual effect to reports; §         Use Indirect within hlookup to arrive at a perfected formula; §         Create 12 month moving chart; §         Present financial data on maps or pictures; §         Set up dynamic picture chart, one which permits user to choose what they wish to see on the chart (e.g. revenue, gross margin or operating margin). §         Set up dynamic text box permitting text change in text box; §          Set up interactive commentaries with proper use of thousand separator (,) and currency sign.

After completing Day 2, participants will be able to:

§         Appreciate the importance and usefulness of a well structured worksheet solutions;

§         Understand the significance of control block in keeping the whole workbook in sync and control;

§         Get the perfected formula, one that can be easily replicated to the rest of the report;

§         Present a series of moving numbers or bar chart;

§         Set up dynamic and interactive reports with dynamic charts and pictures.

 

 

Day 3:

Day 3 - Harness the concepts learnt on Day 2 and provide further practice.  Learn how Excel can be used as a powerful presentation tool.  One that permits the user to swiftly move from one report to the next with drill down capability to detailed reports.

 

Lessons

§         Import data from internet; §         Consolidation of data; §         Use Dates with hlookup; §         Create waterfall chart for variance analysis; §         Working with dynamic chart title; §         Set up dynamic doughnut chart. §         Get images from the internet; §         Work with pictures to create virtually stunning reports; §         Set up dynamic chart title.  Where chart title will change according to the selected variable. §         Working with Macro and VBA §         Prepare automatic cell referencing in documentation; §         Manage range and tab name used in workbook for efficient documentation and control.

After completing Day 3, participants will be able to:

§         Work with information from internet;

§         Set up macro and perform VBA editing;

§         Create waterfall chart;

§         Prepare dynamic documentation where cell reference in documentation is updated when the reference worksheet is modified.

 

 

 

 

Day 4:

Pivot Table and Investment Appraisals / Project Evaluation

 

Lessons

§         Pivot Table a.      Introduction to Pivot Table; b.      Pivot Table terminology; c.      Generate quick reports using Pivot Table; d.      Create calculated field and item; o        Create Pivot Table for use by users without Pivot Table knowledge.  No drag and drop needed to view different set up. o        Avoid setting up multiple Pivot Tables for user without Pivot Table knowledge; o        Create Pivot Table with a click of the mouse. §         Investment Appraisals / Project Evaluation e.      Investment Appraisals / Project Evaluation using DCF and NPV; f.        Handle uncertainty in investment / project evaluation g.      Sensitivity Analysis h.      Scenario Analysis i.         Working with Macro and VBA

After completing Day 4, participants will be able to:

§         Create Pivot Table for use by users without Pivot Table knowledge.  No drag and drop needed to view different set up.

§         Perform investment appraisals using sensitivity and scenario analysis

§         Work with macro and VBA

 

 Home