Many mine and other professionals use Microsoft Excel daily. In most cases these professionals have had limited training and, as a result, create spreadsheets that are potentially excessively manual, user-unfriendly, cumbersome to maintain and, more importantly, error prone.
This master course is targeted at professionals who create/develop Excel spreadsheets used by themselves or their colleagues on a routine basis. This course provides insights into how Excel can be used in a logical, structured, automated and user-friendly manner, to create spreadsheets that are, automated, user-friendly and error-free.
The approach of the course is largely from a ‘system modelling perspective’ where a system is a collection of models or
processes. Hence there is some preliminary discussion on modelling frameworks. These modelling frameworks can be represented via:
- Diagrams (flowsheets)
- Descriptions (documents)
- Mathematics (equations)
- And calculations (Excel formulae)
Most users of Excel focus on using Excel as a collation of equations which, when lacking visualisation, documentation and structure, creates ‘messy’ spreadsheets. By following a more systemic approach the application of Excel becomes logical, user-friendly and automated.
In this phase, many of the concepts taught into the previous two days will be used as the basis for creating professional workbooks. However here focus is given to flowsheets, which for many engineers are used as a method of process understanding, but here is used for data-process modelling.
Flow-sheeting is therefore a skill that is straightforward for engineers and scientists to master; and serves as an introduction to data analytics.
- Flow-sheeting In Excel
- Flow-sheeting in Visio
- Manual Hyperlinking from flowsheets to named ranges, and vice
- Automated Hyperlinking from flowsheets to named ranges, and vice
- Assigning macros to shapes as the basis for simulation
- Automated table
- Automated charting
- Developing standards in workgroups. Standards are a method to ensure there is consistency between spreadsheets within working groups facilitating user-friendliness.
One of the key issues with Excel is that when a staff-member leaves it is often difficult (or practically impossible) to understand an Excel spreadsheet created by such person, so that often the work is discarded, and new staff start from scratch. Learning the concepts in this course will provide a basis to ensure that spreadsheets are more understandable and portable within a group.
Another specific objective is that participants will be able to generate a dataflow diagram (flowsheet) of a workbook and automatically hyperlink from the data flow diagram to the workbook enabling efficient and structured workbook navigation.
What will delegates achieve with this course?
It must be emphasised that Excel has numerous functionalities, and this course cannot aim to provide participants to be over-night experts. What the course does provide is that participants will develop a strategic understanding of the capabilities of Excel (not taught in any course elsewhere).
Users should come away with an appreciation of how many of their routine tasks can be automated, how to increase user-friendliness, how to reduce errors, how to develop Excel spreadsheets more efficiently and allow spreadsheets developed by previous staff to be maintained rather than discarded.
There is some emphasis and discussion on optimal ways to integrate Excel and databases, and participants should develop enough understanding to identify when to use a relational database.
Course participants will develop skills in data analytics, modelling and software design. This should allow them to communicate their requirements more effectively with professional software developers.
Although the course was originally designed for the mining industry, participants from diverse backgrounds such as business analytics and financial modelling will also find the course of value.
The course is most effective when ALL members of a production section or group attend, including the Manager.
What will the delegates employer achieve with this course?
Companies will increase the professionalism of their staff enabling more efficient utilisation of time in creating Excel workbooks, more organised data handling, speedier reporting strategies, less errors, and better continuation and maintenance of existing Excel spreadsheets. Efficient use of spreadsheets provides the basis of fast decision strategies, which for large Mining Companies can substantially assist in increasing profitability.