This module provides students with an ability to demonstrate good practice spreadsheet design and apply techniques that are used in modern day accountancy and finance , with a view to enhancing their understanding of Data Analytics, as it evolves into a key skill for accounting and finance professionals. The aim of the module is to give the student an understanding of the advanced spreadsheet tools and techniques. In addition, the module will provide a pathway for external accreditation that will enhance employment prospects.
Revisiting Excel basics - Navigate the Excel User Interface
- Use Excel commands, Excel data types - numbers, text, date, etc.
- Relative vs Absolute Cell Referencing
- Creating and copy basic statistics formulas – Totals, Average Min, Max, St.Dev, etc.
Data Preparation and Manipulation
- Importing data from various sources
- Insert, delete, and adjust cells, columns, and rows
- Move and copy data between cells
- Link cells to external worksheets
- Formatting cells and workbooks
- Removing duplicating data
- Using advanced formulas to merge, split and reorganize data.
- Control data entry via data validation rules
Data Analysis and Forecasting
- Introduction to logical functions
- Retrieving data using composite logical and LOOKUP Functions
- Use Data Tables and Scenarios to project potential outcomes
- Use Goal Seek to calculate outcomes
- Solving optimisation problems with “Solver” tool pack for Excel
- Creating and interacting with Pivot Tables
- Format, sort, and filter data using Pivot Tables
- Use conditional formatting to identify duplicates, highlight, sort and filter key data
Data Visualisation and Reporting
- Review of the main Chart types of Excel 2016+
- Create and modify Charts. Use advanced Chart features
- Add Trendlines to Charts to visualise and forecast trends
- Using Sparklines to create a visual summary of data
- Creating and formatting Pivot Charts
Reviewing and Protecting Workbooks
- Protect workbook access. Protect worksheets and cell content
- Add and edit comments. Prepare a workbook for distribution
- Modify Excel's default settings
Using Macros to Automate Workbook Functionality
- Create Macros via recording. Run Macros via buttons and shortcuts
- Assign Macros to the Quick Access Toolbar and Ribbon. View Macro code
Beyond MS Excel.
- Exploring other industry recognised analytical tools.
- Overview of Tableau platform, purposes, capabilities, and differences
The module employs a blended approach of delivery - with a mix of face-to-face and asynchronous activity. Learning is largely collaborative but would also require individual commitment. The mix includes laboratory classes, online content and other resources, tutorial support, class discussion and problem-solving. Formative assessment by means of one individual assessment and one group project are an integral part of delivery where students are required to individually model real world business cases as well as able to analyse and efficiently present data in professional group environments.
| Module Content & Assessment | |
|---|---|
| Assessment Breakdown | % |
| Other Assessment(s) | 100 |