Microsoft Excel Diploma Course

Develop your skills in best practice Excel development, for robust, transparent and flexible spreadsheets which communicate your analysis clearly to decision makers

• Understand the 10 principles of Excel best practice – the foundation for the Mazars Financial Modelling methodology – and how this applies to your day-to-day reporting, analysis and forecasting
• Learn techniques for transparent Excel development including formulae structure and presentation
• Understand the key components required for professional presentation of your calculations and analysis
• Learn how to manage Excel files for version control and efficiency – naming conventions, log of changes, model ownership
• Understand the spreadsheet development process and the governance required to achieve a lower risk of errors and confusion
• Review examples of good Excel spreadsheet practice compared to bad practice – visual examples highlighting the risk and reward of good/bad behaviour

Master essential Excel functionality – hands-on Excel development training course module

• Essential calculation functions for numerical calculations such as SUM, AVERAGE, COUNT
• Date calculations in Excel – powerful date manipulation formulas including EOMONTH, EDATE, YEAR, MONTH, DATE
• Logical statements for more structured analysis and filtering– understanding IF, AND, OR, and how they can be applied transparently in a flexible Excel environment
• Lookup functions – learning appropriate function selection between LOOKUP, INDEX and MATCH, and when to avoid VLOOKUP and HLOOKUP functions
• Financial functions – Understand limitations of NPV and IRR function in Excel, and review the analytical (from scratch) options to net present value
• Presentation of the more dynamic and powerful time-dependent functions for financial return analysis XNPV and XIRR
• Text functions for manipulation and analysis of text strings, which is frequently required (“cleansing”) in reporting and analysis with imported Excel data.– LEFT, MID, RIGHT, FIND, VALUE
• Overview of more powerful data functions in Excel, useful for custom analysis and flexible spreadsheet structures such as SUMIF, SUMPRODUCT, OFFSET

Review of powerful functionality in Excel – hands of Excel training course workshop components

• Use pivot tables for ultra-flexible and insightful analysis and reporting
• Develop data tables for structured one or two-dimensional analysis (examples include sensitivity analysis on a key model driver)
• Filtering is an efficient tool for segmented analysis of Excel data which can save hours of work
• Understand the power of conditional formatting and its applications in Excel dashboards, management reports and business analysis to ‘make the numbers come alive’
• Develop data validation functionality to avoid errors caused by erroneous user inputs, or use it to give helpful tips to a user of your Excel spreadsheets
• Explore the functionality and risks with Named Ranges in Excel (while extremely powerful, there are also risks of confusion by other parties, and finding the right balance and structure is critical)
• Use refined Styles in Excel to achieve perfect consistency in presentation across a spreadsheet, a team or an organisation

Learn to work efficiently with the Excel user interface in this training course

• Learn Excel keyboard shortcuts for powerful and quick use of essential functionality – detailed review of the Mazars one pager for fast implementation “All the Excel keyboard shortcuts you will ever need”
• Identify where to find core functions and tools such as charts, dropdown boxes, objects and macros and how to use these efficiently
• Review common areas of Excel functionality to quickly identify required functionality
• Customise your Excel interface to easily access your commonly used functions and tools
• Tips and trick on how to work better in Excel on a day-to-day basis

Understand the different categories of Excel spreadsheets in business applications

• Management reporting and dashboards – visual representation and communication of key outputs
• Cash flow forecasting – dynamic and robust forecasting based on key business drivers
• Budgeting – detailed spreadsheet exercise with many numerical (“hardcoded”) inputs
• Business analytics – analysis of (often) historical data to identify trends, anomalies, significant segments, profitability challenges
• Scenario management – forecasting segment review potential future outcomes linked to commercial scenarios – often referred to as “what-if analysis”

• Learn the key components of Excel charts and how they interact with underlying data
• Master essential chart functionality to have the skills to tailor the chart to your audience/purpose
• Master the magic of F11, for customised instant analysis
• Gain insights from charting of multiple data series on separate axes which is a powerful way of highlighting correlated drivers and results
• Review tips and tricks to give you increase confident in advanced Excel charting techniques

• Understand the different categories of errors and how they can be identified (before it is too late!)
• Develop a structured framework for Excel spreadsheet review which can be incorporated into your day-to-day job, which reduce the risk of making errors, and assist you in identifying the errors that do occur
• Understand the types of “Model audit software” and “Excel review add-ins” available in the market to assess if these are beneficial to your organisation