With the huge amount of data available to us every day, a well-built and designed dashboard report is one of the best ways to interpret and communicate large quantities of information. Using new Modern Excel tools, learn how to synthesise information into a logical framework, summarise it into a meaningful format, and then display the summary into easy-to-read tables and graphs using Excel or Power BI.
What is Modern Excel?
Any version of Excel released from Excel 2010 onward is referred to as “Modern Excel” because it introduced new tools such as Power Pivot, Power Query and Power BI. The introduction of these tools was the most exciting thing to happen in the Excel world since the PivotTable! Many of us struggle to make sense of all the data that is available in this information age and with such a deluge of reports and statistics which don’t mean anything or can’t be made sense of, it’s no wonder that data is often misinterpreted or not communicated at all.
Understanding how Modern Excel tools can help analyse data and create dashboards to communicate your message is a critical skill for Excel users today.
Prerequisites
The course is designed for users who do use Excel on a regular basis and are comfortable with using its tools and functions. At minimum, it is assumed that participants will know how to:
- Navigate confidently in Excel
- Create and use Excel tools and functions
Prior knowledge of Power Pivot, Power Query or Power BI is not required
Who should attend this course?
This course is aimed at managers and other professionals who need to create dashboards and other reports in order to analyse interpret and present information. It is expected that students should have had some exposure to business, finance and reporting principles. This course builds on students’ existing knowledge of Excel tools and functions and incorporates these into complex and dynamic reports and dashboards.
Pre-Course Workshop
In preparation for this online course, please register for the free pre-course workshop. This is a live, interactive session, so please have your cameras switched ON and your microphone ready!
Delivery Format
This course is conducted as 3 x 3-hour online sessions on three consecutive days. At the conclusion of each formal session, a one-hour clinic is held where the instructor gives individual assistance and feedback. Each session is highly interactive, and participants are expected to complete each task independently and then share with their break-out group, or the class as a whole.
A download folder is provided, loaded with examples and templates. Students are encouraged to share their own experiences with the class and are welcome to bring examples of their own data or reports to discuss during the course.
All sessions are recorded, and participants can access recordings for 12 months following the course. As with all Plum Solutions courses, participants also receive access to the Excel Modelling Support Centre for 12 months following the course.
At each step during the course, participants build and practice each formula, tool and technique and will build their own dashboards to take away for future use.
Software used
You must have a Windows laptop or workstation (not Mac) with your preferred version of Excel installed; Excel 365 is recommended to get the best from this training. Please load Power BI Desktop (free install) prior to the course. A good Internet connection is required, along with a quiet room, microphone and webcam (preferable).
Learning Objectives
This course is very hands-on and practical. Each participant will be expected to create their own reports, charts and models utilising the tools and techniques covered during the course. Participants will:
- Understand the principles of data analysis
- Learn to synthesise and summarise information into a logical framework.
- Know how to use visual effects to improve their reports and presentations
- Explore how to summarise, present and communicate data clearly and concisely
- Consider the principles of good dashboard design and presenting data graphically
- Evaluate the new Modern Excel tools available for data analysis and dashboards
- Discover the tools to analyse data & build reports using your existing Excel tools and skills (and perhaps a few you didn’t know you had!)
DAY ONE (3 hours + clinic)
Data Visualisation for Dashboards
- Turning data into information
- Debunking the big data myth
- Presenting financial data
- How dashboards combat information overload
- Find the story. Tell the story.
Data Analysis Tools
- What’s new in Excel 2019 / Excel 365?
Technical differences between versions. Considerations when building data models for users of different versions.
- Shortcuts
Speed up your data manipulation in Excel with the use of shortcuts
- Tables & PivotTables
Analysing data more quickly and efficiently using structured reference table and summarise using PivotTables
Power Excel Tools
- Introduction to Power Pivot, Power Query and Power BI
Evaluation of Excel vs. other BI alternatives in terms of speed, usability and cost
- Data modelling
building relationships between multiple tables
- Get and Transform your data
extracting and cleansing data with Power Query
- Power Pivot
introduction to data warehousing
- Dashboarding with Power BI
creating visuals in Power BI Desktop
Using Excel as a Data Analysis Tool
- Essential Excel tools & functions
Overview of must-know tools and functions for data analysis
- Creating a robust formula
Nest functions together to create succinct and robust formulas
DAY TWO (3 hours + clinic)
Practical Dashboard Building 1
- Building a dashboard using standard Excel, PivotTables and PivotCharts
- Preparing data using Power Query and then building a dashboard in Excel
- Building, modelling and sharing reports in Power BI
Charting & Table Design
- Working with charts in Excel 2019 / 365
Assess the use of waterfalls, treemaps and sparklines
- Data visualisation for visual analytics
Show your message such as correlations by charting on different axes and chart types
- Tables vs. Charts/Graphs
Deciding the best method for visual display
- Selecting the correct chart to display data
Getting your message across most effectively by choosing the correct medium of display
DAY THREE (3 hours + clinic)
Dashboard Reporting & Design
- Purpose and benefits of Dashboards
Why dashboards are so popular
- Common mistakes when building a Dashboard
Critique of poorly built dashboards
- Principles of dashboard design best practice
Dashboard layout, colour choice and display principles
- Adding icons and images to dashboards
Quick and easy ways to add automated colour and icons to your reports
- Effective use of colour and logos
Ways to make an impact with colour
Practical Dashboard Building 2
- Using your own data, or examples provided, we spend the last part of the course creating dynamic dashboards using Excel, Power Query, Power Pivot or Power BI incorporating the tools and techniques discussed during the course.
Comments from past students
“It was an outstanding course with relevant and dynamic content, tips and shortcuts to creating interesting and informative dashboards.”
“The course covered a wide range of topics related to Excel data processing and modelling.”
“Structured over 3 half days & very professionally run and interesting with proper workshops & exercises.”
“Danielle knows her stuff and imparts with humour; Susan administered excellently.”
“Great introduction to Power BI – I feel more likely now to be able to progress setting up monthly reports in Power BI and export to pdf for distribution.”
“I liked that the course didn’t just take us through the technical aspects of how to build a dashboard, but also included info such as which chart type to use, how visual information is interpreted, dos and don’ts and simple, understandable dashboards vs bad ones.”
Session Times
Wednesday 9th – Friday 11th Jun 2021
3 x 3 hour sessions
plus clinic each day
- 3-6pm Sydney
- 3-6pm Brisbane
- 5-8pm Auckland
- 1-4pm Perth / Singapore
- 9am-12pm Dubai
- 6-9am London
- 10pm – 1am Los Angeles
PRICE is AUD$445 plus GST (approx US$350)
GST is only applicable to Australian residents
* By signing up to this course, you agree to the terms and conditions
Contact us about completing this as an When embarking upon the budget process for your organisation this year, practical Microsoft Excel tools and strong financial modelling skills will be critical for managing the budget process. It’s never been more important to build robust, flexible and dynamic budget models to accurately predict business outcomes and handle the volatile economic inputs likely to emerge in these uncertain times. Historically, the budget process involves pulling out the financials and rolling them forward, but last year’s actuals can no longer be an automatic target. Learn how to use standard Excel tools to improve and streamline your budgeting and forecasting process.
Course Outline
- Using Excel for the budgeting process
- How we use Excel for budgeting & forecasting
- Overview of commonly used Excel tools and techniques
- Applying phasing and seasonality to model drivers
- Using time series and linear regression
- Calculating seasonality
- Creating a “rolling” forecast with live data
- Budgeting Techniques
- Building flex budgets
- Top down versus bottom up
- Zero based budgeting
- Critique of the budget process
- Behavioural impact of budgeting
- Budgeting “soft” skills
- Capital expenditure budgeting
- Building capex and opex into the budget model
- Calculating depreciation
- Using management reporting to promote accountability
- Building dynamic variance analysis reports
- Scenarios – Stress-testing, break-even and sensitivity analysis
Danielle Stein Fairhurst is the Principal of Plum Solutions, a Sydney-based consultancy specialising in financial modelling and analysis. She is the author of “Using Excel for Business and Financial Modelling”, Third Edition published by Wiley Finance 2019 and “Financial Modeling in Excel for Dummies” published by Wiley in 2017.
Pre-Course Workshop
In preparation for this online course, please register for the free pre-course workshop. This is a live, interactive session, so please have your cameras switched ON and your microphone ready!
Delivery Format
This course is conducted as 3 x 3-hour online sessions on three consecutive days. At the conclusion of each formal session, a one-hour clinic is held where the instructor gives individual assistance and feedback. Each session is highly interactive, and participants are expected to complete each task independently and then share with their break-out group, or the class as a whole.
A download folder is provided, loaded with examples and templates. Students are encouraged to share their own experiences with the class and are welcome to bring examples of their own data or reports to discuss during the course.
All sessions are recorded, and participants can access recordings for 12 months following the course. As with all Plum Solutions courses, participants also receive access to the Excel Modelling Support Centre for 12 months following the course.
At each step during the course, participants build and practice each formula, tool and technique and will build their own models to take away for future use.
Key Takeaways
- Limitations of budgets and critique of the budget process.
- Calculating and applying phasing and seasonality to model drivers.
- Tried and true budgeting techniques such as top-down, bottom-up, zero-based and flex budgeting.
- Tools to handle uncertainty building scenarios, sensitivities and break-even analysis into planning projections.
- Budgeting for capital expenditure and depreciation.
- Creating a “rolling” forecast with live data.
- Building dynamic variance analysis reports.
Technical Requirements
You should have Microsoft Excel (any version) installed on your machine prior to the beginning of the class.
Session Times
Wednesday 5th – Friday 7th May 2021
3 x 3 hour sessions
plus clinic each day
- 3-6pm Sydney
- 3-6pm Brisbane
- 5-8pm Auckland
- 1-4pm Perth / Singapore
- 9am-12pm Dubai
- 6-9am London
- 10pm – 1am Los Angeles
PRICE is AUD$445 plus GST (approx US$325)
GST is only applicable to Australian residents
* By signing up to this course, you agree to the terms and conditions
WAITLIST ONLY – This course is now full. Please contact us to go on the waitlist
PRICE is AUD$515 plus GST (approx US$380)
GST is only applicable to Australian residents
Receive an ebook version of Using Excel for Business and Financial Modelling (RRP$115+GST) as well as Financial Modeling in Excel for Dummies (RRP$43+GST) ! If you are based in Australia, we can provide a hardcopy version of the books instead.
* By signing up to this course, you agree to the terms and conditions
Course Outline
- Using Excel for the budgeting process
- How we use Excel for budgeting & forecasting
- Overview of commonly used Excel tools and techniques
- Applying phasing and seasonality to model drivers
- Using time series and linear regression
- Calculating seasonality
- Creating a “rolling” forecast with live data
- Budgeting Techniques
- Building flex budgets
- Top down versus bottom up
- Zero based budgeting
- Critique of the budget process
- Behavioural impact of budgeting
- Budgeting “soft” skills
- Capital expenditure budgeting
- Building capex and opex into the budget model
- Calculating depreciation
- Using management reporting to promote accountability
- Building dynamic variance analysis reports
- Scenarios – Stress-testing, break-even and sensitivity analysis
Danielle Stein Fairhurst is the Principal of Plum Solutions, a Sydney-based consultancy specialising in financial modelling and analysis. She is the author of “Using Excel for Business and Financial Modelling”, Third Edition published by Wiley Finance 2019 and “Financial Modeling in Excel for Dummies” published by Wiley in 2017.
The Webinar
In preparation for this online course, please watch the pre-course webinar.
The Masterclass
When embarking upon the budget process for your organisation this year, practical Microsoft Excel tools and strong financial modelling skills will be critical for managing the budget process. It’s never been more important to build robust, flexible and dynamic budget models to accurately predict business outcomes and handle the volatile economic inputs likely to emerge in these uncertain times. Historically, the budget process involves pulling out the financials and rolling them forward, but last year’s actuals can no longer be an automatic target. Learn how to use standard Excel tools to improve and streamline your budgeting and forecasting process.
Key Takeaways
- Limitations of budgets and critique of the budget process.
- Calculating and applying phasing and seasonality to model drivers.
- Tried and true budgeting techniques such as top-down, bottom-up, zero-based and flex budgeting.
- Tools to handle uncertainty building scenarios, sensitivities and break-even analysis into planning projections.
- Budgeting for capital expenditure and depreciation.
- Creating a “rolling” forecast with live data.
- Building dynamic variance analysis reports.
Technical Requirements
You should have Microsoft Excel (any version) installed on your machine prior to the beginning of the class.
Session Times
Wednesday 2nd – Friday 4th December
3 x 3 hour sessions
plus clinic each day
3-6pm Sydney
2-5pm Brisbane
12-3pm Perth
12-3pm Singapore
5-8pm Auckland
8-11am Dubai
4-7am London
11pm-2am (-1) New York
PRICE is AUD$445 plus GST (approx US$320)
GST is only applicable to Australian residents
* By signing up to this course, you agree to the terms and conditions
PRICE is AUD$515 plus GST (approx US$375)
GST is only applicable to Australian residents
Receive an ebook version of Using Excel for Business and Financial Modelling (RRP$115+GST) as well as Financial Modeling in Excel for Dummies (RRP$43+GST) ! If you are based in Australia, we can provide a hardcopy version of the books instead.
* By signing up to this course, you agree to the terms and conditions
THIS EVENT WAS HELD IN NOVEMBER 2020. PLEASE REGISTER FOR THE NEXT SESSION HERE.
Further dates can be found here.
With the huge amount of data available to us every day, a well-built and designed dashboard report is one of the best ways to interpret and communicate large quantities of information. Using new Modern Excel tools, learn how to synthesise information into a logical framework, summarise it into a meaningful format, and then display the summary into easy-to-read tables and graphs using Excel or Power BI.
What is Modern Excel?
Any version of Excel released from Excel 2010 onward is referred to as “Modern Excel” because it introduced new tools such as Power Pivot, Power Query and Power BI. The introduction of these tools was the most exciting thing to happen in the Excel world since the PivotTable! Many of us struggle to make sense of all the data that is available in this information age and with such a deluge of reports and statistics which don’t mean anything or can’t be made sense of, it’s no wonder that data is often misinterpreted or not communicated at all.
Understanding how Modern Excel tools can help analyse data and create dashboards to communicate your message is a critical skill for Excel users today.
Prerequisites
The course is designed for users who do use Excel on a regular basis and are comfortable with using its tools and functions. At minimum, it is assumed that participants will know how to:
- Navigate confidently in Excel
- Create and use Excel tools and functions
Prior knowledge of Power Pivot, Power Query or Power BI is not required
Who should attend this course?
This course is aimed at managers and other professionals who need to create dashboards and other reports in order to analyse interpret and present information. It is expected that students should have had some exposure to business, finance and reporting principles. This course builds on students’ existing knowledge of Excel tools and functions and incorporates these into complex and dynamic reports and dashboards.
Delivery Format
This course is conducted as 3 x 3-hour online sessions on three consecutive days. At the conclusion of each formal session, a one-hour clinic is held where the instructor gives individual assistance and feedback. Each session is highly interactive, and participants are expected to complete each task independently and then share with their break-out group, or the class as a whole.
A download folder is provided, loaded with examples and templates. Students are encouraged to share their own experiences with the class and are welcome to bring examples of their own data or reports to discuss during the course.
All sessions are recorded, and participants can access recordings for 12 months following the course.
At each step during the course, participants build and practice each formula, tool and technique and will build their own dashboards to take away for future use.
Software used
You must have a Windows laptop or workstation (not Mac) with your preferred version of Excel installed; Excel 365 is recommended to get the best from this training. Please load Power BI Desktop (free install) prior to the course. A good Internet connection is required, along with a quiet room, microphone and webcam (preferable).
Learning Objectives
This course is very hands-on and practical. Each participant will be expected to create their own reports, charts and models utilising the tools and techniques covered during the course. Participants will:
- Understand the principles of data analysis
- Learn to synthesise and summarise information into a logical framework.
- Know how to use visual effects to improve their reports and presentations
- Explore how to summarise, present and communicate data clearly and concisely
- Consider the principles of good dashboard design and presenting data graphically
- Evaluate the new Modern Excel tools available for data analysis and dashboards
- Discover the tools to analyse data & build reports using your existing Excel tools and skills (and perhaps a few you didn’t know you had!)
DAY ONE (3 hours + clinic)
Data Visualisation for Dashboards
- Turning data into information
- Debunking the big data myth
- Presenting financial data
- How dashboards combat information overload
- Find the story. Tell the story.
Data Analysis Tools
- What’s new in Excel 2019 / Excel 365?
Technical differences between versions. Considerations when building data models for users of different versions.
- Shortcuts
Speed up your data manipulation in Excel with the use of shortcuts
- Tables & PivotTables
Analysing data more quickly and efficiently using structured reference table and summarise using PivotTables
Power Excel Tools
- Introduction to Power Pivot, Power Query and Power BI
Evaluation of Excel vs. other BI alternatives in terms of speed, usability and cost
- Data modelling
building relationships between multiple tables
- Get and Transform your data
extracting and cleansing data with Power Query
- Power Pivot
introduction to data warehousing
- Dashboarding with Power BI
creating visuals in Power BI Desktop
Using Excel as a Data Analysis Tool
- Essential Excel tools & functions
Overview of must-know tools and functions for data analysis
- Creating a robust formula
Nest functions together to create succinct and robust formulas
DAY TWO (3 hours + clinic)
Practical Dashboard Building 1
- Building a dashboard using standard Excel, PivotTables and PivotCharts
- Preparing data using Power Query and then building a dashboard in Excel
- Building, modelling and sharing reports in Power BI
Charting & Table Design
- Working with charts in Excel 2019 / 365
Assess the use of waterfalls, treemaps and sparklines
- Data visualisation for visual analytics
Show your message such as correlations by charting on different axes and chart types
- Tables vs. Charts/Graphs
Deciding the best method for visual display
- Selecting the correct chart to display data
Getting your message across most effectively by choosing the correct medium of display
DAY THREE (3 hours + clinic)
Dashboard Reporting & Design
- Purpose and benefits of Dashboards
Why dashboards are so popular
- Common mistakes when building a Dashboard
Critique of poorly built dashboards
- Principles of dashboard design best practice
Dashboard layout, colour choice and display principles
- Adding icons and images to dashboards
Quick and easy ways to add automated colour and icons to your reports
- Effective use of colour and logos
Ways to make an impact with colour
Practical Dashboard Building 2
- Using your own data, or examples provided, we spend the last part of the course creating dynamic dashboards using Excel, Power Query, Power Pivot or Power BI incorporating the tools and techniques discussed during the course.
Comments from past students
“It was an outstanding course with relevant and dynamic content, tips and shortcuts to creating interesting and informative dashboards.”
“The course covered a wide range of topics related to Excel data processing and modelling.”
“Structured over 3 half days & very professionally run and interesting with proper workshops & exercises.”
“Danielle knows her stuff and imparts with humour; Susan administered excellently.”
“Great introduction to Power BI – I feel more likely now to be able to progress setting up monthly reports in Power BI and export to pdf for distribution.”
“I liked that the course didn’t just take us through the technical aspects of how to build a dashboard, but also included info such as which chart type to use, how visual information is interpreted, dos and don’ts and simple, understandable dashboards vs bad ones.”
DOWNLOAD THE COURSE OUTLINE
THIS EVENT WAS HELD IN NOVEMBER 2020. PLEASE REGISTER FOR THE NEXT SESSION HERE.
Further dates can be found here.
The recent sessions have now finished. Please join the waitlist here for our next round of sessions.
* By signing up to this course, you agree to the terms and conditions
THIS EVENT WAS HELD IN JUNE 2020. PLEASE REGISTER FOR THE NEXT SESSION HERE.
Further dates can be found here.
With the huge amount of data available to us every day, a well-built and designed dashboard report is one of the best ways to interpret and communicate large quantities of information. Using new Modern Excel tools, learn how to synthesise information into a logical framework, summarise it into a meaningful format, and then display the summary into easy-to-read tables and graphs using Excel or Power BI.
What is Modern Excel?
Any version of Excel released from Excel 2010 onward is referred to as “Modern Excel” because it introduced new tools such as Power Pivot, Power Query and Power BI. The introduction of these tools was the most exciting thing to happen in the Excel world since the PivotTable! Many of us struggle to make sense of all the data that is available in this information age and with such a deluge of reports and statistics which don’t mean anything or can’t be made sense of, it’s no wonder that data is often misinterpreted or not communicated at all.
Understanding how Modern Excel tools can help analyse data and create dashboards to communicate your message is a critical skill for Excel users today.
Prerequisites
The course is designed for users who do use Excel on a regular basis and are comfortable with using its tools and functions. At minimum, it is assumed that participants will know how to:
- Navigate confidently in Excel
- Create and use Excel tools and functions
Prior knowledge of Power Pivot, Power Query or Power BI is not required
Who should attend this course?
This course is aimed at managers and other professionals who need to create dashboards and other reports in order to analyse interpret and present information. It is expected that students should have had some exposure to business, finance and reporting principles. This course builds on students’ existing knowledge of Excel tools and functions and incorporates these into complex and dynamic reports and dashboards.
Delivery Format
This course is conducted as 3 x 3-hour online sessions on three consecutive days. At the conclusion of each formal session, a one-hour clinic is held where the instructor gives individual assistance and feedback. Each session is highly interactive, and participants are expected to complete each task independently and then share with their break-out group, or the class as a whole.
A download folder is provided, loaded with examples and templates. Students are encouraged to share their own experiences with the class and are welcome to bring examples of their own data or reports to discuss during the course.
All sessions are recorded, and participants can access recordings for 12 months following the course.
At each step during the course, participants build and practice each formula, tool and technique and will build their own dashboards to take away for future use.
Software used
You must have a Windows laptop or workstation (not Mac) with your preferred version of Excel installed; Excel 365 is recommended to get the best from this training. Please load Power BI Desktop (free install) prior to the course. A good Internet connection is required, along with a quiet room, microphone and webcam (preferable).
Learning Objectives
This course is very hands-on and practical. Each participant will be expected to create their own reports, charts and models utilising the tools and techniques covered during the course. Participants will:
- Understand the principles of data analysis
- Learn to synthesise and summarise information into a logical framework.
- Know how to use visual effects to improve their reports and presentations
- Explore how to summarise, present and communicate data clearly and concisely
- Consider the principles of good dashboard design and presenting data graphically
- Evaluate the new Modern Excel tools available for data analysis and dashboards
- Discover the tools to analyse data & build reports using your existing Excel tools and skills (and perhaps a few you didn’t know you had!)
DAY ONE (3 hours + clinic)
Data Visualisation for Dashboards
- Turning data into information
- Debunking the big data myth
- Presenting financial data
- How dashboards combat information overload
- Find the story. Tell the story.
Data Analysis Tools
- What’s new in Excel 2019 / Excel 365?
Technical differences between versions. Considerations when building data models for users of different versions.
- Shortcuts
Speed up your data manipulation in Excel with the use of shortcuts
- Tables & PivotTables
Analysing data more quickly and efficiently using structured reference table and summarise using PivotTables
Power Excel Tools
- Introduction to Power Pivot, Power Query and Power BI
Evaluation of Excel vs. other BI alternatives in terms of speed, usability and cost
- Data modelling
building relationships between multiple tables
- Get and Transform your data
extracting and cleansing data with Power Query
- Power Pivot
introduction to data warehousing
- Dashboarding with Power BI
creating visuals in Power BI Desktop
Using Excel as a Data Analysis Tool
- Essential Excel tools & functions
Overview of must-know tools and functions for data analysis
- Creating a robust formula
Nest functions together to create succinct and robust formulas
DAY TWO (3 hours + clinic)
Practical Dashboard Building 1
- Building a dashboard using standard Excel, PivotTables and PivotCharts
- Preparing data using Power Query and then building a dashboard in Excel
- Building, modelling and sharing reports in Power BI
Charting & Table Design
- Working with charts in Excel 2019 / 365
Assess the use of waterfalls, treemaps and sparklines
- Data visualisation for visual analytics
Show your message such as correlations by charting on different axes and chart types
- Tables vs. Charts/Graphs
Deciding the best method for visual display
- Selecting the correct chart to display data
Getting your message across most effectively by choosing the correct medium of display
DAY THREE (3 hours + clinic)
Dashboard Reporting & Design
- Purpose and benefits of Dashboards
Why dashboards are so popular
- Common mistakes when building a Dashboard
Critique of poorly built dashboards
- Principles of dashboard design best practice
Dashboard layout, colour choice and display principles
- Adding icons and images to dashboards
Quick and easy ways to add automated colour and icons to your reports
- Effective use of colour and logos
Ways to make an impact with colour
Practical Dashboard Building 2
- Using your own data, or examples provided, we spend the last part of the course creating dynamic dashboards using Excel, Power Query, Power Pivot or Power BI incorporating the tools and techniques discussed during the course.
DOWNLOAD THE COURSE OUTLINE
THIS EVENT WAS HELD IN JUNE 2020. PLEASE REGISTER FOR THE NEXT SESSION HERE.
Further dates can be found here.
The recent sessions have now finished. Please join the waitlist here for our next round of sessions.
Prepare for the Level 1 “Advanced Financial Modeler” (AFM) certification exam with this practical online exam prep course from FMI Master Financial Modeler, Danielle Stein Fairhurst
This intensive course is very hands-on and each participant will be expected to create their own financial models. During the course, participants will use the Excel tools and techniques learned as well as financial modelling best practice to build interactive and dynamic financial models that adhere to the highest industry standards.
Delivered 100% online, this course builds on students’ knowledge of Excel formulas and incorporates these into fully integrated financial models. At each step during the course, participants build and practice each formula, tool and technique.
What’s included
Sign up to start straight away and gain 12 months access to:
- FMI exam registration (worth US$695)
- 7+ hours of on-demand instructional video on the Plum Portal dedicated to FMI Level 1 exam prep
(includes subtitles in English, French, Hindi and Korean)
- Excel exercise template and solutions downloads
- Quiz questions to test your understanding
- Mock exam case study and solution models
- Live mock exam virtual sitting two weeks prior to final exam with individualised feedback (worth US$175)
- The Plum Alumni support centre (worth US$240)
- Weekly one-on-one live coaching sessions with your instructor in the weeks leading up the exam (worth US$310)
Copies of the books written by your instructor and FMI MFM, Danielle Stein Fairhurst are optional. These are on the FMI recommended reading list and referred to during the course, but are not required for the exam.
Who this qualification is for
This course is suitable for those who wish to earn the Level 1 AFM Certification from the Financial Modeling Institute (FMI). For more information on the certification, see www.fminstitute.com/afm The AFM program is open to anyone who has an interest in or currently works in the financial markets. From finance students to seasoned industry professionals, the AFM designation is ideal for those who:
- Understand the fundamentals and methodologies of financial modelling
- Want to hone and validate their skills
- Work as part of a larger team to provide support for improvement or new projects
- Work extensively with Excel in their day-to-day work to make material financial decisions
The FMI exams are held a couple of times a year online. Your purchase includes one sitting of the exam but you must independently register for this exam directly with the institute. This course will prepare you for the FMI exam but your attendance will in no way guarantee your exam results.
Prerequisites
According to FMI, candidates are expected to devote between 25 and 100 hours to preparation in total and attendees should already be familiar with the FMI Body of Knowledge and the skills checklist prior to attending this course.
Software used
To undertake this course, you need Microsoft Excel. Excel 365 is preferred, but participants can use any version of Excel if they prefer, including a Mac. More detail about how the online courses work
About your instructor
Danielle Stein Fairhurst holds a Level 3 Master Financial Modeler qualification (MFM) from the Financial Modeling Institute. In 2021 she first became a Microsoft Most Valued Professional (MVP) and in 2022 she received the “Lifetime Achievement Award” at the Financial Modelling Summit.
Modules in this Course
About the Level 1 (AFM) Exam
- Learning Materials (watch a preview)
- What to expect on Exam day
- The Grading Process
Basic Excel skills
- What’s New in Excel 365
- Linking for Best Practice
- Absolute & Relative Cell Referencing
- Calculating Project Costs
- Logical Nested Functions
- Mixed Referencing and Named Ranges
Essential Tools & Formulas
- Aggregation Functions
- Formula Selection
- Data Validations & Drop-Downs
- Form Controls
- Macros in Financial Modelling
Financial Modelling Techniques
- Skills Needed for FM
- What Makes a Good FM?
- Building Error Checks
- Escalation Methods
Financial Modelling Best Practice
- Model Design
- Anatomy of a Financial Model
- Best Practice
- Shortcuts
Three Way Modelling
- What is a 3 way financial model?
- Dummies Case Study: Financial Statements Part 1
- Dummies Case Study: Financial Statements Part 2
- Dummies Case Study: Financial Statements Part 3
- Preparatory Case Study: Building a simple vertical model
Model Planning, Setup and Assumptions
- Model setup and layout
- Inputs and assumptions
- Assumptions Documentation Methods
Designing for scenario analysis
- Scenarios, Sensitivities and What-if Analysis
- Building Scenarios Exam Prep
The Revenue Schedule and COGS
- Setting up the Revenue Schedule
- Completing the Revenue Schedule
- Cost of Goods Sold (COGS)
Building the Depreciation Schedule
- Depreciation in Financial Modelling
- Modelling for a Capital Purchase
- Modelling Depreciation (End of Useful Life)
- Depreciation Schedule Waterfall
Income Tax Expense and Working Capital
- Building the Tax Schedule
- Setting up Working Capital Assumptions
- Calculating Working Capital
Building the Debt Schedule
- Debt Definitions
- Dealing with Circularity in Debt Calculations
- Modelling a Revolver Line of Credit
- Components of the Debt Schedule
- Adding the Revolver
- Completing the Debt Schedule
Equity & Completing the Financial Statements
- Equity Schedule Case Study
- Building the Equity Schedule
- Piecing together the final components of the financial statements
- What to do when the balance sheet doesn’t balance
- Adding Error Checks to your model
Summary Page, Formatting, Presentation and Scenarios
- Summary page Setup
- Which Ratios to Include
- Adding Scenarios
- Formatting and presentation
Mock Exam case study
- “Cake to Bake” case study time lapse video (see a shortened preview here)
- Vertical solution model
- Horizontal solution model
Mock Exam
Two weeks prior to the exam, you’ll sit an additional Mock exam (a new case study not available elsewhere) and will receive individualised feedback before sitting the final exam.
Price: USD$895 ex GST (GST not applicable outside Australia)
Can’t make it to a
workshop? Enrol in the online version of the Financial Modelling course, and start straight away! Complete the course at your own pace.
This course is part of the
Financial Modelling Online Series:
- Financial Modelling for Non Financial Modellers
- Financial Modelling in Excel (Intermediate)
- Advanced Financial Modelling in Excel
Buy all three courses at once and save, PLUS you’ll get a copy of
Using Excel for Business and Financial Modelling included as well as Financial Modeling in Excel for Dummies! Or, get the online Intro course included if you sign up for the
Financial Modelling Workshop bundle.
In the ever-changing business environment, being able to accurately model and forecast the volatile economic inputs is a critical skill for business professionals. Build on your existing modelling skills and apply new techniques to better analyse financial data, predict revenues and cost and assess risks to justify business decisions in the most time-efficient and effective way.
More detail about how the online courses work
Who should complete this course?
The course material includes extensive use of Excel and participants will gain the maximum benefit from this course if they are already competent spreadsheet users. It is designed for those who have already undertaken the
Introduction and/or
Intermediate Financial Modelling in Excel courses or users who do use Excel on a regular basis, and are comfortable with using its tools and functions.
Download the course outline here
What are the prerequisites for taking this course?
At minimum, it is assumed that participants will know how to:
• Navigate confidently in Excel
• Use absolute cell references (e.g. =$A$1)
• Create and use advanced Excel tools and functions on a regular basis
This course builds on students’ advanced knowledge of Excel tools and functions and incorporates these into complex and dynamic financial models.
More detail about how the online courses work
How do I purchase?
Scroll to the bottom of this page, tick the box and add it to your cart. Once you’ve gone through the shopping cart, you will gain access to the course. You’ll need to be logged in in order to view the content.
Testimonials
Hear what past students have to say about the online
Advanced Financial Modelling course:
Modules in this Course
3.1 Theory and Best Practice
• Choosing between Excel versions
• Overview of best practice financial modelling techniques
• Common Excel error values in financial modelling
• Correcting and suppressing errors
• Locating and fixing circular references
• Allowing iterative calculations when circular references are maintained
3.2 Advanced Tools and Functions
• Selecting the formula or tool which is most appropriate for each modelling situation
• Nesting formulas
• Useful functions in financial modelling such as COLUMN, CHOOSE, OFFSET, LOOKUP and TRANSPOSE
• Using LOOKUP functions in tiered pricing models (volume break discounting)
• Pros and cons of using array functions and their use in financial modelling
• Use of form controls such as spin buttons and combo boxes
3.3 Advanced Techniques
• Rebuilding an inherited model
• How long should a formula be? Deciding when to break a complex formula in several steps to maintain transparency and allowing ease of model auditing
• Comparison of different methods of modelling escalation and growth calculations
• Presenting model output using a chart on two different axes and chart types
• Creating a Waterfall Chart in Excel
• Pivot tables – summarise, dissect and analyse large amounts of data
• Why pivot tables are not commonly uses in financial modelling, and other alternative tools
• Automate your financial model for the user with Macros. Recording and modifying VBA code and creating macro buttons
3.4 Scenarios & Sensitivity Analysis
Learn different methods of what-if, scenario and sensitivity analysis in Excel using:
• Show multiple scenario outcomes simultaneously with one and two-dimensional Data Tables
• Learn how to automatically highlight selected scenarios using complex conditional formatting
• Using the Scenario Manager
• Manual scenario building
– In-cell drop-down boxes
– Combo-box drop down boxes
At each step during the course, participants build and practice each formula, tool and technique. Record your own macro with buttons, build a pivot table, and create drop-down boxes.
3.5 Practical Financial Modelling
Building on the tools and techniques covered, we will learn to apply advanced modelling skills to build complex, yet robust and user-friendly financial models.
Factory Rollout Model
Working on a startup funding model from start to finish we will evaluate input assumptions, calculate revenue and costs of production to calculate cash requirements. From this, we build a full set of financial statement with particular emphasis on linking the profit & loss statement, cash flow statement and balance sheet together.
Utilising the tools and techniques covered in the first part of the course, participants will build their own financial model case study, performing stress testing, scenario and sensitivity analysis on their model.
Price: $240.00 ex GST
Can’t make it to a workshop? Enrol in the online version of the Financial Modelling course, and start straight away! Complete the course at your own pace.
This course is part of the Financial Modelling Online Series:
- Financial Modelling for Non Financial Modellers (Financial Modeling for Dummies)
- Financial Modelling in Excel (Intermediate)
- Advanced Financial Modelling in Excel
Buy all three courses at once and save, PLUS you’ll get a copy of Using Excel for Business and Financial Modelling included as well as Financial Modeling in Excel for Dummies! Or, get the online Intro course included if you sign up for the Financial Modelling Workshop bundle.
Learning Objectives
During the course, participants will create their own financial model to take away and use for future reference. You will learn how to design and create a user-friendly model which can then be used by anyone with limited knowledge of Excel.
You will learn how to:
- Build a financial model from scratch, or modify and improve an inherited model
- Reduce errors by building in error checks
- Prevent incorrect use of your model by protecting worksheets
- Validate data entry by setting data entry parameters
- Create a navigation page to help users find their way around your model
- Create drop-down boxes which enable a model to produce a series of results depending on scenario variable selected
- Mitigate liability by providing assumptions
- Present findings in a concise and meaningful way
Prerequisites
Students are expected to be reasonably regular Excel users, and should be comfortable with simple formulas and linking between workbooks.
More detail about how the online courses work
What does the course content look like?
If you’d like to see a 5 minute preview of one of the videos, take a look at the Skills Needed for Financial Modelling section. Each of the six modules in this course contains downloadable PDF course notes of between 15 and 30 pages long which contain extracts from Using Excel for Business Analysis, and step by step instructions.
For a shorter overview, watch here for a 70 second preview to give you an idea of the quality of the videos:
Modules in this Course
2.1 About Financial Modelling
- Introduction to Financial Modelling course (Intermediate)
- Anatomy of a Financial Model
- Excel Versions
- What’s New in Excel 2016
- Best Practice in FM
- Overview of Scenario Analysis Methods
2.2 Excel Tools
- Mixed Referencing and Named Ranges
- Conditional Formatting
- External File Links
- Protecting, Hiding & Grouping
2.3 Essential Formulas
- Aggregation Functions
- LOOKUP Fundamentals
- Practical Capital Budgeting with LOOKUP functions
- Using FORECAST / TREND functions
- Formula Selection
2.4 Financial Modelling Techniques
- Skills Needed for Financial Modelling
- What Makes a Good Financial Model?
- Building Error Checks
- What-if Analysis with Goal Seek
- Calculating the WACC
- Calculating NPV & IRR
2.5 Building a Business Case
- Business Case – Calculating Staff Costs
- Business Case – Adding Compounding Inflation
- Business Case – Forecasting Customer Numbers
- Business Case – Forecasting Customer Revenue
- Business Case – Modelling Market Penetration
- Business Case – Project Evaluation (NPV & IRR)
- Business Case – Project Evaluation (Payback Period)
2.6 Analysing and Presenting your Model
- Assumptions Documentation Methods
- Business Case – Scenario Analysis
- Charting on two axes and Chart Types
- Business Case – Dynamic Charting
- Presentation of Model Output
Price: $240.00 ex GST
Can’t make it to a workshop? Enrol in the online version of the Financial Modelling course, and start straight away! Complete the course at your own pace.
This course is part of the Financial Modelling Online Series:
- Financial Modelling for Non Financial Modellers
- Financial Modelling in Excel (Intermediate)
- Advanced Financial Modelling in Excel
Buy all three courses at once and save, PLUS you’ll get a copy of Using Excel for Business and Financial Modelling included as well as Financial Modeling in Excel for Dummies! Or, get this online course included if you sign up for the Financial Modelling Workshop bundle.
Learning Objectives
This course introduces the fundamentals and introductory concepts of building a financial model using Excel. During this practical course in which you will develop the skills to build your own financial model which can be taken away for future reference. You will learn first principles of modelling techniques, best practice and how to design and create a user-friendly model.
Prerequisites
Who should complete this course? Those who:
- Are new to Financial Modelling and its concepts, and;
- Have a basic knowledge of Excel
It is assumed that you have either undertaken a basic Excel Introduction course, or else are able to:
- Navigate in Excel
- Use absolute cell references (this is covered in the course in case you are a bit rusty!)
- Create and use simple formulas
It is also assumed that you have had some exposure to financial concepts, but limited experience in Financial Modelling.
More detail about how the online courses work
What does the course content look like?
For a short overview, watch here for a 60 second preview:
Modules in this Course
1.1 Financial Modelling Introduction
- What is Financial Modelling?
- Model Design
- Excel Versions 2013
- What’s New in Excel 2016 *New*
- Overview of Scenario Analysis Methods
- Common Excel Error Values
1.2 Fundamental Excel Tools
- Linking for Best Practice
- Absolute, Relative and Mixed Cell Referencing
- Calculating Project Costs
- Logical Nested Functions
- Assumptions Documentation Methods
1.3 Building a Financial Model
- Model Build – Inputs & Assumptions
- Model Build – Revenue
- Model Build – Expenses
- Model Build – Profit & Loss
- Model Build – Cashflow
1.4 Model Analysis Tools
- Bullet-Proofing your Model
- Data Validations & Drop-downs
- Conditional Formatting
- Model Build – Charting Cash vs Profit
- Model Build – Modelling Scenarios
Price: $240 ex GST
