Advanced Excel Training: Data Analysis & Reporting (Level 2 – 3 Days) – 20 CPD Hours
Many professionals spend most of their time in the office working with data. It could be to extract data from their operational system to Excel, sorting, filtering, summarizing, writing formulas and so on to produce management reports. This is usually done on a routine basis, like daily, weekly, monthly or quarterly and this process is usually stressful and time consuming. The major reason for the stress and time consumption is because many professionals have never been formally trained on the proper use of spreadsheets such as Microsoft Excel. Most of them have attended Basic Excel training in college or have self-taught themselves on the job. According to the most extensive spreadsheet research carried out by one Dr Raymond Panko of the University of Hawaai, about 99% of spreadsheets he audited contained errors. The fact is that most of the time spent working with data is unnecessary. This course aims to give participants cutting-edge Financial Modelling in Excel skills to help them to be effective, efficient and accurate in their data analysis & reporting according to International Best Practice Standards.
This highly-practical, intensive, hands-on programme is packed with cutting-edge spreadsheet design ideas to take your model structuring skills to the next level. By adopting best practice modelling standards you can develop better models, in less time and with greater accuracy. By the end of this course, you will walk away with the ability to manipulate and analyse data effectively & efficiently in a way that you never imagined possible. You will be able to design a model where your routine data management will be reduced to simple pasting and printing!
The following is guaranteed from this practical, all-in-one workshop:
- Best Practice Modelling – Learn and apply the most effective ways to design, cross-check and structure robust, flexible and dynamic models to get your job done faster and painlessly;
- Excel Efficiency Tools – Learn efficiency tools that will make you super-efficient in Excel;
- Efficient Data Manipulation & Analysis – Extract and present useful information from historical data by applying various data management tools and techniques and formulas to get the job done in minutes.
- Excel Formulas & Functions – Leverage on a wide range of dynamic Excel functions, mathematical calculations, graphics and reporting features to work faster and accurately.
- Macros – Minimize manual labour and automate routine tasks by recording and editing macros to boost your efficiency.
- Report Formatting & Presentation – Effectively communicate the results of your models to team members and senior management by way of dynamic reports that automatically change when source data changes.
- Graphical Analysis – Ability to design and format different kinds of graphs to appropriately present financial information.
- Reconciliations – Ability to reconcile data effectively and efficiently using Excel Formulas.
Who should attend?
This course has been designed for both student and professional financial officers, budget officers, middle and senior management from government agencies, non-government agencies and the private sector who are involved in the use and analysis of financial data and models for decision making including but not limited to:
- Financial Controllers,
- Administrators & Directors,
- Financial & Business Analysts,
- Private Equity and Venture Capitalists,
- Project Managers,
- Lenders & Corporate Treasury Managers ,
- Risk Managers,
- Portfolio Managers,
- Economists & Corporate Finance Professionals,
- Budget Analysts and Financial & Strategic Planners and General Managers,
- Sales, Marketing & Human Resources Managers,
- Middle & Lower level staff whose jobs rely on heavy use of Excel.
Taking the Excel Fundamentals course is a pre-requisite plus basic knowledge of Excel including:
- Navigate confidently in Excel
- Moving around a worksheet and between worksheets in a workbook
- Entering, selecting, copying and editing cell contents
- Saving, closing and retrieving the workbook
- Auto fill
- Simple cell formatting, previewing and printing documents and ranges
- Creating files
- Deleting files and individual sheets
- Changing column width and row height
- Entering simple formulas
- Cell referencing
- Centering titles and merging cells
- Number formats
- Changing font sizes and colours
- Copy, cut and pasting cell contents
- Inserting graphic objects
- Previewing worksheets
- All delegates will receive a “challenges & expectations” form to ensure that the course director gains an outline of delegates’ job functions and the personal objectives for the course. The course is delivered using formal lectures combined with practical and interactive exercises to reinforce the concepts covered in each teaching session.
- Emphasis is placed on delegates gaining practical, hands-on experience of the design and construction of financial models in Excel. Macros will also looked at used extensively. Delegates will also benefit from formal lectures and group discussions. Comprehensive notes will be provided for future reference.
- Case studies runs through the whole course intended to allow delegates to apply course concepts by producing a comprehensive model with a wide range of Excel tools and methods.
The course begins by concentrating on learning key advanced formulas, spreadsheet best practice and the advanced use of Excel. These skills are then applied to the construction of a robust data analysis model. The course is taught using a step-by-step approach to enable delegates to master the concepts.
What to bring for the course
Bring your Laptop or Desktop Computer loaded with Excel 2010 or better. The course will be demonstrated mostly using Excel 2016 or Excel 2019. Rental of Laptop pre-load with Excel maybe available at $50 per day per laptop. Kindly advise us 3 days prior to the day the training commences.
- A folder containing course manuals, exercises and reference models.
- An interactive manual loaded with exercises, solutions and brief notes.
- A pen and a note book for your in-course notes.
Upon completion of each of these courses, you will receive a prestigious Certificate of Attendance, as a recognition of your accomplishment, skills and knowledge learned from this course. If you desire to take our competence tests, upon successful completion of the tests you will receive a Celsoft Certified Technician Certificate. Any of these certificates will bear CPD hours as stated on top.
Support is available by email and telephone after the course to ensure that delegates can continue to develop and apply the principles learnt on the course. Participants can also make use of our support portal, www.mycelsoft.com or simply send an email to firstname.lastname@example.org.
MANAGING DATA, INTRODUCTORY TECHNIQUES
- Dealing with data issues – Covering Text to columns (Fixed width and delimited methods) including the use of the LEFT, MID, RIGHT, TRIM and VALUE functions.
- Sorting Data – Sorting by cell colour, font colour and icons.
- Filtering Data – Simple and Advanced Filtering, including understanding filtering in place and to a new location.
- Identifying & Removing Duplicates — Identify duplicates in your data and remove them to remain with duplicate values.
NAMING DATA RANGES STATICALLY & DYNAMICALLY
- Static Naming of Cells & Ranges (Revision) – Including where static naming is applied and the rules thereof.
- Dynamic Naming of Cells & Data Ranges – The use of the OFFSET function to dynamically name a range and the application thereof.
SUMMARIZING AND ANALYZING LARGE AMOUNTS OF DATA
- Subtotal – Inserting subtotals in large sums of data, auto outlining and “drilling down” in Excel®
- Pivot Tables – Performing multi – dimensional analysis of data, including identifying data dimensions, key features of a Pivot Table, building a Pivot Table from an Excel® list or database, analyze and display data from different points of view, “Drill down” to the underlying data, refresh the Pivot Table data, user slicers to perform data analysis and create a Pivot Chart from your Pivot Table.
- Use of mathematical, trigonometric and statistical functions – Analysing data using SUM, SUMIF, SUMIFS, COUNT, COUNTA, COUNTIF, COUNTIFS
- The PowerPivot – Understanding the powerpivot and how to use it to analyse large sums of data.
CONSOLIDATING LARGE AMOUNTS OF DATA
- Summing through sheets – Summing similar references in multiple sheets for efficiency.
- Consolidation – Understanding consolidation of data in different worksheets using consolidation by category and by position.
- Multiple Consolidation using Pivot Tables – Further consolidation techniques using Pivot Tables.
AUTOMATING YOUR FINANCIAL MODEL
- VBA Macros – Display Visual Basic Editor and Control Toolbox, recording & editing macros and add command buttons.
- Hyperlinks – Accessing and creating hyperlinks within a workbook and to other files and websites.
- Preparation of a model Switchboard – The use of macros and command buttons to prepare a models switchboard as required by best practice modelling standards.
RECONCILING DATA IN EXCEL
- Reconciliations – The use of lookup functions, advanced filtering and macros to perform reconciliations of data.
DYNAMIC PRESENTATION OF MODEL RESULTS
- Working with tables – Converting a normal range to a table including the benefits thereof.
- Grouping and Ungrouping – how to group and hide, considering the benefits of each;
- Conditional formatting – Learn how to have Excel® automate variance analysis using complex conditional formatting
- Charts and graphs – applying Excel® ’s rich contextual tabs to analyse, modify and print a chart, modify titles, labels, axes, gridlines and legends, working with data series and data range, formatting charts, data series and chart area options, changing colour, borders, patterns, working with templates, styles and formats including inserting pictures, text boxes and shapes.
- Sparklines – View trends in data quickly by making use of sparklines (2010).
- Embedding – Learn how to embed an Excel® worksheet in word and vice versa, including linking the packages for faster reporting.
BULLET-PROOFING YOUR MODEL
- Protection – Protect your model so that it can be used by anyone with a limited use of Excel® including locking and protecting cells. Understand how to protect cells, worksheets and workbooks.
- Data Validation – Restriction incorrect data entry with data validation including creating error validation messages.