Advanced Excel Spreadsheet Skills for Non Financial Managers

Are you required to formulate departmental budgets or feasibility testing on new markets? Do you already have a basic working knowledge of Excel but need more advance tools and techniques to gain more from your spreadsheet data? There are heaps of amazing things you can derive from spreadsheet data – if you know how to.

It can get quite frustrating to have all the data yet not having them structured into relevant and useful formats. Having clearly structured data can clearly help you make meaningful and useful decisions about your department, product cycles, sales/marketing projections etc.

Instead of learning everything that aren’t entirely relevant to you. Advanced Excel Spreadsheet Skills for the Non Financial Manager has been designed to help you zero-in and get a better handle of Excel’s features and techniques used by advanced users

You will emerge from the course with the ability to apply and build spreadsheets that will capture the right data thus helping you with your decisions, forecasting futures and mitigate risks.

About

COURSE OBJECTIVE
This course is aimed primarily at non financial business managers who wish to be able to use Excel to analyse and manipulate data into meaningful information. The course is presented with realistic worked examples to aid understanding. The course covers:
• Excel features and techniques for building durable business models,
• Making business projections
• Presenting information in a concise and professionally formatted report.

WHO SHOULD ATTEND?
Non financial business managers with a basic working knowledge of Excel who wish to build on that knowledge to apply Excel’s powerful tools and features to their business. Some financial managers may also benefit.

Outline

DAY ONE

Introduction
• Establishing attendees’ individual objectives
• Review of attendees’ Excel working practices

Reviewing the Fundamentals
• The purpose of Excel
• The Excel bag of tools
• The Excel environment
• Customising Excel to suit the user

Setting Up a List of Data
• Fundamentals of setting up a list of data
• Controlling data entry using data validation
• Using Drag and Fill options

Viewing and Formatting Data
• Applying the Autofilter to data
• Using Advanced Autofilter to extract unique records
• Applying conditional formatting to a range
• Understanding custom number formats
• Understanding custom date formats

Formulas and Functions
• Using built in functions:
     - SUMPRODUCT
     - SUBTOTAL
     - IF
     - SUMIF
     - COUNTIF
     - ISERROR
     - ISNA
     - VLOOKUP
• Creating a nested function illustrated using INDEX and MATCH
• Identifying and correcting common errors with look up functions
• Using formulas across worksheets
• Using formulas across workbooks
• Controlling data updates
Auditing Formulas
• Using Smart Tags
• Displaying all formulas on a worksheet
• Using Formula Evaluation
• Tracing a formula’s dependent references
• Tracing a formula’s precedent references
• Common spreadsheet errors
Range Names
• Creating and editing range names
• Understanding range name rules
• Creating dynamic ranges using OFFSET
• Applying named ranges to formulas
Importing Non-Excel Data
• Importing text file formats
• Importing data from the Web
• Tricks and shortcuts for reformatting imported files

Analysing Data Using Pivot Tables
• What is a pivot table?
• Understanding the elements of a pivot table
• Creating a simple pivot table
• Changing the layout of a pivot table
• Adding formulas to a pivot table
• Some pivot table tricks

DAY TWO

Representing Data with Charts
• Commonly used business chart types
• Charting do’s and don’ts
• Creating charts using shortcuts
• Combining 2 chart types on one chart
• Shortcuts for adding new data to a chart
• Using a chart with a dynamic range
• Formatting charts for professional results

Planning a Business Model
• Considerations before building a business model
• Components of an enduring business model

Building a Business Model
• Determining the objectives and designing the required outputs
• Determining the desired variables
• Gathering and setting up the data
• Processing the data using a pivot table
• Extracting information from a pivot table
• Tips for avoiding calculation bottlenecks
Extending a Business Model
• Using Scenarios and Scenario Manager
• Using sensitivity analysis to determine possible outcomes
• Forecasting by applying a trendline to a chart

Creating a Professional Report
• Introduction to the information dashboard
• Do’s and don’ts of dashboard design
• Determining the information to display and choosing the display media
• Using Excel tools to construct and layout a professional looking report
• Importing text from Word

Facilitator

Leigh Drake

Leigh qualified as a chartered accountant in UK in 1992 and immediately moved into increasingly senior financial roles in industry. Over the years he has worked within the manufacturing and service industries with companies ranging from start ups to listed corporations.

With a previous scientific background, Leigh naturally brought well-considered, simple and logical solutions to many of the problems encountered in business - particularly in the areas of finance and administration and has first hand experience of many of the concerns currently held by businesses.
Leigh’s skills have been widely used in business planning, risk assessment, cash management and regulatory compliance.

In 2005 he moved into a consulting role, building a client base entirely through referrals before co-founding arc Business Processes to meet a demand for a wider range of business services.

In-house Training

Prices and Registration

Sorry, this event currently has no dates scheduled.