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
Sorry, this event currently has no dates scheduled.
Register now
This event currently has no scheduled dates.
