Thursday 27 October
Expand All- 8.15 am – 9.00 am: Registration and arrival refreshments
- 9.00 am: Morning master classes
-
MC57: PivotTables using Excel 2007 – level one
9.00 am – 12.30 pm
Who should attend: those who are comfortable using Excel and have some familiarity with Excel 2007, and who need to quickly organise data and extract and summarise informationView MoreSession Details
PivotTables are the single most powerful feature in Excel. The concept of pivoting data refers to the ability of the PivotTable tool to arrange information in a meaningful way while linking directly with the data source. Each new version of Excel has further enhanced this powerful feature.
All of the familiar features of PivotTables are present in Excel 2007 with some important changes and extensions. Excel 2007 is distinguished by the new Ribbon interface, replacing the more familiar toolbars and buttons. Some familiarity with the Excel 2007 Ribbon is required as attention is given to this feature throughout the session.
Microsoft has streamlined the PivotTable interface to make it easier to use—in Excel 2007 it is possible to build a PivotTable by checking a few boxes. Beginners will find that it is easier and simpler to construct meaningful tables using Excel’s IntelliSense to guide the process. To modify the default structure of the table, field names may be dragged around the PivotTable Field List.
Attend this master class to lean how to:
- employ the new Ribbon interface to develop and format PivotTables
- create PivotTables and PivotCharts from data lists
- dynamically update PivotTable reports when the source data changes
- understand the anatomy of PivotTables and reporting limitations
- customise PivotTables and be able to sort and filter the table
- use PivotTables to organise and summarise large data sets
- group PivotTable fields for hierarchical viewing
- add calculated fields to a PivotTable
-
MC58: VBA using Excel 2007 – level one – sold out
9.00 am – 12.30 pm
Please refer to: MC64 and MC86 for further informationView MoreSession Details
As our skill with the spreadsheet reaches a critical level of mastery, the next logical step to improving workplace productivity is through Visual Basic for Applications (VBA): a programming language which extends the functionality of the Microsoft Excel spreadsheet. Having built an efficient spreadsheet model to solve a business problem, it is often necessary to replicate such models using VBA.
This introductory course develops the foundation for modular programming using VBA. The ability to create simple sub-procedures provides the important first step in automating routine tasks in Excel. With practice and experience it is then possible to develop more complex procedures that will ultimately lead to dramatic productivity improvements.
Attend this master class to learn about:
- the basics of VBA and how it adds functionality to Excel
- the structure of the VBA language
- using the macro recorder to record simple tasks in Excel
- using the macro recorder to assist with understanding VBA code
- the Visual Basic Editor, Project Explorer window and the Object Browser
- creating procedures to solve modularised tasks
- debugging VBA procedures with the aid of built-in tools
- the concept of loops and program control
You will receive detailed notes and a set of example files to enable you to perfect your technique after the session.
Complimentary member resources
-
MC59: Designing and building spreadsheet models using Excel 2003
9.00 am – 12.30 pm
Who should attend: those who have had some exposure to a spreadsheet package and wish to develop a scientific approach towards model buildingView MoreSession Details
This master class will introduce you to the concept of an efficient spreadsheet model and the procedures which can be used to develop and build them. The presenter will lead you through the techniques involved in specifying problems in a structured format; identifying the variables required to solve the problem; classifying the variables as input, calculation and result variables; considering the mathematical relationships between variables; and building the spreadsheet model. The use of appropriate data to test the completed model and appropriate interrogation of the model will be considered.
Attend this master class to:
- implement the procedures for developing spreadsheet models
- build efficient spreadsheet models
- appropriately test the model
- carry out sensitivity analysis on the completed model
The session will be carried out using Excel 2003. You will receive detailed course notes and a set of files to enable you to perfect your technique after the session.
Complimentary member resources
- Back to top ▲
- 10.30 am – 11.00 am: Morning tea and networking break
- 12.30 pm – 1.30 pm: Lunch and networking break
- 1.30 pm: Afternoon master classes
-
MC60: PivotTables using Excel 2007 – level two
1.30 pm – 5.00 pm
Who should attend: those who have a basic familiarity with PivotTables and Excel 2007, and who need to quickly extract and organise data from various data sourcesView MoreSession Details
- This workshop builds on the course: PivotTables Using Excel 2007 – level one. Participants are required to have completed the introductory course or have a basic working knowledge of PivotTables and have some familiarity with Excel 2007.
PivotTables in Excel 2007 are easier to create, have increased flexibility for pivoting the data and are supported by an improved range of presentation styles. The use of OLAP cubes increases the richness of views available within a PivotTable. Cube functions, not available in previous versions of Excel, may be employed to access OLAP data outside a PivotTable object.
PivotTables may be automated using VBA by enabling the Developer Ribbon to improve efficiency and simplify the creation of routine PivotTables. Some of the newer VBA features available only in Excel 2007 include the new label and value filters, conditional formatting, table formatting and layout views.
Attend this master class to learn how to:
- extract and organise data from external data sources
- create and use OLAP cubes
- create OLAP cube functions
- use Microsoft Query to access and extract filtered records from databases
- employ PivotTables for data consolidation and create customised Page fields
- use compatibility mode to publish an interactive PivotTable to the internet
- create PivotTables and PivotCharts using the macro recorder
-
MC61: VBA using Excel 2007 – level two
1.30 pm – 5.00 pm
Who should attend: those who have advanced spreadsheet skillsView MoreSession Details
This workshop builds on concepts introduced in the course: VBA using Excel 2007 – level one. It is recommended that participants have advanced spreadsheet skills and have completed the level one course.
In this session, modular programming is emphasised as an efficient method of handling projects that can be separated into component parts. The use of master procedures will be introduced as a project management device.
Excel-based spreadsheet models are employed for routine analysis and minimal changes are required for such models to generate reports from data that is updated periodically. The most effective method of improving productivity in such situations is to automate these models using Visual Basic for Applications (VBA).
Attend this master class to learn how to:
- add functionality to Excel using VBA
- understand the structure of the VBA language
- use the VB Editor and module sheets to store various VBA procedures
- automate common Excel tasks that can be implemented by a mouse-click
- automatically allow user interaction and incorporate new information
- use loops as an efficient method of communicating with worksheets
- automate many of Excel’s model interrogation tools
You will receive detailed notes and a set of files to enable you to perfect your technique after the session.
Complimentary member resources
-
MC62: Business forecasting models using Excel 2003
1.30 pm – 5.00 pm
Who should attend: those who have had some exposure to a spreadsheet packageView MoreSession Details
Business forecasting models can help a business to understand its environment, improve its decision making and make better use of one of its key resource: its customer database. Excel has a range of built-in functions and optimising routines that may be used to build effective forecasting models.
Attend this master class to learn:
- a variety of common forecasting models
- how to identify and model trend and seasonality in business data
- how to choose appropriate models to deal with common business data
- optimisation techniques to obtain the best possible model to use with a particular data set
- how to integrate forecasts with the budget process
This hands-on session will be conducted in a computer lab using Excel 2003. Following a discussion on forecasting issues and models, you will consider the three most common types of data in a business environment: data without trend; data with trend; and data with trend and seasonality. You will learn to identify the three types by graphing the data and then fit appropriate smoothing models to each.
You will be introduced to a number of techniques that you can apply during the session. You will receive a detailed set of course notes and a collection of example files to enable you to continue to refine and develop your skills after the session.
Complimentary member resources
- Back to top ▲
- 3.00 pm – 3.30 pm: Afternoon tea and networking break
| CPA Congress sessions are suitable for all competency levels as per the Career Guidance System. | |
 |
Leadership |
|
Business |
 |
Technical |
 |
Personal effectiveness |