Professional Certificate in Business Analytics & Statistics with Excel

1. Introduction to Business Statistics & Application Fundamentals

• Types of Business Statistics 
• Descriptive Statistics 
• Excel Interface 
• Giving Commands 
• Using Command Shortcuts 
• Creating a New Workbook 
• Opening a Workbook 
• Saving a Workbook 
• Closing a Workbook 
• Using Help 
• Exiting Excel 

2. Getting Started with Worksheets

• Navigating a Worksheet 
• Entering Labels and Values 
• Editing Cell Contents 
• Selecting Cells and Ranges 
• Entering Data in a Selected Range 
• Entering Content Automatically 
• Importing Data from a Text File and an Access Database 
• Importing Data from the Web

3. Introduction to Formulas & Functions

• Overview of Formulas and Cell References 
• Entering Formulas 
• Absolute and Relative References 
• Formulas with Multiple Operators 
• Using Essential Statistical Functions 
• Referencing External Data 

4. Editing a Worksheet

• Copying and Moving Cells 
• Controlling How Cells Are Pasted 
• Checking Spelling 
• Insert Cells, Rows, and Columns 
• Delete Cells, Rows, and Columns 
• Using Undo and Redo  
• Finding and Replacing Content 
• Notes 

5. Formatting a Worksheet

• Formatting Text 
• Formatting Values 
• Adjusting Column Width and Row Height 
• Working with Cell Alignment 
• Adding Cell Borders and Background Colours 
• Copying Formatting 
• Applying and Removing Cell Styles 
• Using Document Themes 
• Applying Conditional Formatting

6. Managing Workbooks

• Using Workbook Views 
• Selecting and Switching Between Worksheets 
• Inserting, Renaming and Deleting Worksheets 
• Moving and Copying Worksheets 
• Changing Worksheet Tab Colour 
• Splitting and Freezing a Window 
• Working with Multiple Workbook Windows 
• Hiding and Unhiding Rows, Columns and Worksheets

7. Managing Page Setup & Printing

• Previewing and Printing a Worksheet 
• Setting the Print Area 
• Adjusting Page Margins and Orientation 
• Adjusting Paper Size and Print Scale  
• Adding Print Titles, Gridlines and Headings 
• Creating Headers and Footers 
• Printing a Selection, Multiple Worksheets and Workbooks

8. Working with Data Ranges

• Sorting by One Column 
• Sorting by Multiple Columns 
• Filtering Data

9. Introduction to Charts

• Choosing and Selecting the Source Data 
• Choosing the Right Chart 
• Inserting, Moving, Resizing and Deleting Charts 
• Changing Chart Layout and Styles 
• Changing Chart Type 
• Working with Chart Labels

Assessment

• Written Examination: 20 minutes
• Practical Assessment: 40 minutes

At the end of the module, you should be able to::

• Use Excel for statistical analysis by organising cell ranges, managing formula errors, applying conditional logic, and employing lookup functions for data search and matching. 
• Manage large Excel data sets, including multiple worksheets and workbooks, and application of protection measures for the interpretation of the categorised data. 
• Organise data in Excel by sorting, filtering, and separating the contents into multiple sets to effectively interpret the collected data for categorisation of meaningful identified data. 
• Use features in Excel to create tables, including managing data sets and removing duplicate records. 
• Optimise Excel functionalities for process improvement in business statistics by creating and formatting charts, including displaying data through histograms. 

1. Formulas & Functions

• Cell and Range Names
• Displaying and Tracing Formulas
• Understanding Formula Errors
• Using Logical Functions
• Using Conditional Count, Sum and Average Functions
• Using Lookup Functions
• Using Database Functions

2. Managing Workbooks & Protection

• Splitting and Freezing a Window
• Working with Multiple Workbook Windows
• Protecting Workbooks
• Protecting Workbook Structure
• Protecting Worksheets and Worksheet Elements

3. Working with Data Ranges

• Creating Custom AutoFill List
• Sorting by Colours or Icons
• Sorting by Custom List
• Filtering Data
• Creating Custom AutoFilter
• Using Advanced Filter
• Using Text to Columns

4. Working with Tables

• Creating a Table
• Adding and Removing Data
• Working with the Total Row
• Sorting a Table
• Filtering a Table
• Removing Duplicate Rows of Data
• Slicers
• Formatting the Table
• Summarizing a Table with a PivotTable
• Converting to a Range

5. Working with Charts

• Using Quick Analysis
• Elements of a Chart
• Filtering Chart
• Working with Chart Labels
• Changing the Chart Gridlines
• Changing the Scale
• Emphasizing Data
• Using Chart Templates
• Using Sparklines
• Creating Histogram

Assessment

• Written Examination: 20 minutes
• Practical Assessment: 40 minutes

At the end of the module, you should be able to:

• Use a series of business statistical formulas and functions for summarising and analysing categorical or numerical data sets, including utilising nested conditions, lookup, text, date, and time functions. 
• Harness the skills needed to use Excel’s built-in data analysis tools to analyse data sets and identify trends and patterns. 
• Distinguish between Excel’s built-in statistical functions and tools to conduct statistical analyses, taking into account the needs and objectives of stakeholders. 
• Utilise PivotTable to understand and analyse the core data structures that include summarising datasets for the presentation of actionable business insights to stakeholders. 
• Adopt relevant statistical software techniques to perform data queries across multiple sources to extract pertinent data for stakeholders. 
• Apply macro recording functionality to aid automation of working with data sets and performing repetitive tasks. 

1. Advanced Formulas and Functions

• Using Nested Logical Functions
• Using Conditional Summary Functions
• Analysing Data Set using Statistical Functions
• Performing Advanced Lookups
• Using Text Functions
• Using Date and Time Functions

2. Managing and Analysing Data Ranges

• Using Flash Fill
• Using Data Validation
• Generating Descriptive Statistics
• Using Histogram Analysis Tool
• Performing Regression Analysis

3. Organising and Summarising Data

• Working with Data Tables
• Working with Scenarios
• Creating a Scenario Summary Report
• Using Goal Seek
• Grouping and Outlining Data
• Using Subtotal
• Consolidating Data by Position or Category
• Consolidating Data Using Formulas
• Sharing Workbooks
• Tracking Changes

4. Working with PivotTables

• Creating a PivotTable
• Specifying PivotTable Data
• Using Multiple PivotTable Fields
• Changing a PivotTable’s Calculation
• Filtering and Sorting a PivotTable
• Working with PivotTable Layout
• Grouping PivotTable Items
• Updating a Pivot Table
• Formatting a PivotTable
• Creating a PivotChart
• Using Slicers
• Sharing Slicers Between PivotTables

5. Working with Web and External Data

• Inserting a Hyperlink
• Importing Data from a Text File and an Access Database
• Importing Data from Web
• Working with Existing Data Connections

6. Working with Macros

• Recording a Macro
• Playing and Deleting a Macro
• Adding a Macro to the Quick Access Toolbar

Assessment

• Written Examination: 20 minutes
• Practical Assessment: 40 minutes

At the end of the module, you should be able to:

• Distinguish essential probability computations, distributions, and hypothesis testing logic for statistical analysis.
• Analyse relationships and trends in data using advanced statistical functions such as quartiles, correlations, and value deviation.
• Apply statistical tools including ANOVA, moving averages, rank/percentile, and sampling to extract insights from large data sets.
• Create integrated PivotTable reports using data models, built-in and custom-calculated fields, and report drill-downs.
• Evaluate and use array and summary functions (e.g. SORT, UNIQUE, XLOOKUP, AGGREGATE) to optimise complex data layouts and summarise insights dynamically.
• Interpret statistical results to support strategic decisions and facilitate advanced analytics discussions in a business context.

Probability and Statistics
Starting with the fundamentals of probability and statistics provides a solid foundation for understanding data analysis. It includes the basics of probability, how to calculate probability, and the concepts of probability distribution, which are crucial for analysing data variability and making predictions. This knowledge is essential for effectively utilising advanced statistical functions and tools in Learning Units 2 and 3.

Advanced Statistical Functions
Building on the foundational knowledge, this unit focuses on using advanced statistical functions to identify value deviation, determine the degree of variation, compute quartiles, and analyse correlations between variables. Additionally, learners will learn to identify ranked high and low values, enhancing their ability to perform sophisticated data analysis in Excel.

Advanced Statistical Tools
This unit provides practical learning to learners to perform natural language queries on data for intuitive analysis, conduct ANOVA (Analysis of Variance) to compare datasets, and calculate moving averages to identify trends. It also covers rank and percentile calculations to determine data positions and sampling techniques for accurate data representation.

Managing PivotTable Reports
This unit dives deeper into leveraging PivotTables for robust data analysis and reporting. The learners will learn to create integrated reports using data models, generate multiple reports from a single dataset, and drill down into details for deeper insights. It also covers applying built-in analysis calculations and working with custom-calculated fields to tailor reports to specific needs.

Array and Summary Functions
Leveraging the knowledge acquired from previous learning units, the learners will learn array functions and spill ranges, change data layouts using TRANSPOSE, and sort data dynamically with SORT and SORTBY. They will also learn to extract unique values with UNIQUE, perform data lookups using XMATCH and XLOOKUP, and compute summary values with AGGREGATE and SUMPRODUCT.

Trainee is 40 years old and above, and a Singaporean Citizen, will be eligible for 70% SkillsFuture Funding.

Nett course fee payable (including 9% GST): S$760.50

Course fees after subsidies can be further offset by SkillsFuture Credit.

Enhanced Training Support for SMEs (ETSS) – 70% Funding

Eligible for SME Company-Sponsored, 21 years old and above, and a

• Singapore Citizen; or
• Permanent Resident; or
• Long-Term Visitor Pass Plus (LTVP+) Holder

Nett course fee payable (including 9% GST): S$760.50

Course fees after subsidies can be further offset by Skillsfuture Enterprise Credit (SFEC)

SkillsFuture Baseline Funding – 50% Funding

Eligible Non-SME Company-Sponsored, 21 years old and above, and a

• Singaporean Citizen; or
• Permanent Resident; or
• Long Term Visitor Pass Plus (LTVP+) Holder

Nett course fee payable (including 9% GST): S$1,150.50

Course fees after subsidies can be further offset by Skillsfuture Enterprise Credit (SFEC)

SkillsFuture Mid-Career Enhanced Subsidy (MCES) – 70% Funding

Eligible for Non-SME Company-Sponsored, Singapore Citizens, aged 40 years old and above

Nett course fee payable (including 9% GST): S$760.50

Course fees after subsidies can be further offset by Skillsfuture Enterprise Credit (SFEC)