Course Overview
This course is a 3-day intensive, high-impact dive into Excel Skills for Business at the second intermediate level. Spreadsheet software remains one of the most widely used pieces of software in workplaces worldwide. Learning to operate this software confidently means adding a highly valuable asset to your employability portfolio. In Botswana, numerous job advertisements requiring Excel skills are posted every day. Research by Burning Glass Technologies and Capital One shows that digital skills lead to higher income and better employment opportunities at a time when digital skills jobs are growing much faster than non-digital jobs.
In this last course of our Specialisation Excel Skills for Business, you will build on the strong foundations of the first three courses: Essentials, Intermediate I + II. In the advanced level course, we will prepare you to become a power user of Excel – this is your last step before specialising at a professional level. The topics at this level will challenge you as you learn how to use advanced formula techniques and sophisticated lookups. You will clean and prepare data for analysis and learn how to work with dates and financial functions. An in-depth look at spreadsheet design and documentation will prepare you for the big finale, where you will learn how to build professional dashboards in Excel.
Practical examples and activities of real life excel scenarios shall be used throughout this course. This shall be facilitated by the use of group activities, tests, scenario analyses and group discussions to help participants gain a vigorous understanding of Excel at this first intermediate level.
Target Audience
This course is suitable for the following personnel;
- Finance and accounting
- Human resource
- Management
- Sales and marketing
Learning Outcomes
By the end of the course, participants should be able to:
- Design and structure spreadsheets for scalability and longevity
- Apply advanced formula techniques and functions to calculations
- Prepare data for analysis and presentation
- Create a data visualisation on a dashboard
Content Covered
The following content will be covered in the course:
1. Spreadsheet Design and Documentation
- Introduction
- Spreadsheet Design Principles
- Calculations
- Formatting
- Documentation
- Interface and Navigation
2. Advanced Formula Techniques
- Introduction
- Tables and Structured Referencing
- Using Functions to Sort Data
- Introduction to Array Formulas
- Working with an Array Function (TRANSPOSE)
- Solving Problems with Array Formulas
3. Data Cleaning and Preparation
- Introduction
- Replace blanks with repeating values
- Fix Dates (DATE, MONTH, YEAR, DAY, TEXT)
- Remove Unwanted Spaces (TRIM, CLEAN)
- Diagnostic Tools (ISNUMBER, LEN, CODE)
- Remove Unwanted Characters (SUBSTITUTE, CHAR, VALUE)
4. Financial Functions and Working with Dates
- Introduction
- Working with Dates (EOMONTH, EDATE, WORKDAY.INTL)
- Financial Functions (FV, PV, PMT)
- Loan Schedule (PMT, EDATE)
- Net Present Value and Internal Rate of Return (NPV, IRR)
- Depreciation Functions (SLN, SYD, DDB)
5. Advanced Lookup Functions
- Introduction
- INDIRECT
- ADDRESS
- Introduction to OFFSET
- Solving Problems with OFFSET
6. Building Professional Dashboards
- Introduction
- Dashboard Design
- Prepare Data
- Construct Dashboard
- Creative Charting
- Interactive Dashboard
Assessment
A variety of assessment tools will be used throughout the course to gauge the level of understanding of the participants.
(i) Quizzes
After some of the topics have been covered, there will be mini quizzes that will be handed out, and students have to complete them based on the material they just covered.
(ii) Group Discussions
After some of the topics, group discussion activities will be initiated, and the aim of these group discussions is for the participants to share their understanding of concepts covered, and by sharing, they display their understanding of the content
(iii) Scenario Analysis
Scenario analysis will also be used as an assessment tool. Participants will be given a particular scenario pertaining to what has just been covered and then asked how they would navigate through the scenario. The answers they give would indicate how well they understood the material.
(iv) Final Written Test
At the end of the course, there will be a final written test. The final written test takes all the content learned throughout the course and tests the participants’ knowledge all at once.