Workshop in Financial Applications and Modelling in MS Excel

Most financial and administrative personnel work on MS Excel® daily. However, they do not utilise MS Excel® 's full scope, range and power because they are unfamiliar with the available functions. This is the only tailor-made workshop focusing on financial applications and examples.

Staff are confronted with a resource-intensive ordeal during each reporting and planning period. The management function and budget planning, using a combination of computer programs and spreadsheets, place a resource drain on the accounting and administrative staff. Too often, producing reports involves manually calculating, cutting and pasting figures into the various required company formats. The situation can, however, be very different.

A thorough grasp of the principles of financial applications is a key competency needed to manage a business or unit effectively. This two-day interactive workshop uses both the financial principles and the power of MS Excel® that can enhance your organisation's stakeholder value and improve the individual's productivity.

The workshop focuses on MS Excel® 365 but will also cover the elements of Excel 2016.

Purpose of the course

On completion of this workshop, participants will be able to:
• Use MS Excel® with more confidence and flexibility,
thereby saving significant amounts of time and enhancing productivity.
• Be more proficient in the basic MS Excel® functions needed for financial applications.
• Gain skills to perform the most complex calculations and functions.
• Use the powerful tools in MS Excel®, such as the Financial and Statistical functions and tools such as the Data Analysis toolbox and Sensitivity analysis tools and Scenarios.
• Analyse data accurately using pivot tables.
• Analyse time series data and increase their ability to forecast outcomes more accurately.

Admission requirements

Admission requirements
Grade 12 or equivalent and basic computer literacy.
Learning assumed to be in place
• Appropriate communication skills at NQF level 4.
• Computer literate at NQF level 4.
• Mathematical literacy at NQF level 4.
• It is suggested that participants have a basic working knowledge of MS Excel®.

Course outcomes and assessment criteria

Course outcomes and the associated assessment criteria


Assessment Criteria

After completion of this course, participants will have: Participant will be assessed on the following criteria if they are able to:
  • Knowledge of and the ability to apply basic modelling principles.
  • Knowledge of and the ability to apply the basic need-to-know functions of modelling in Excel.
  • Knowledge of and the ability to format data in an existing spreadsheet.
  • Knowledge of and the ability to create and use logical functions; mathematical functions; statistical functions; lookup functions; date and time functions; text functions; financial functions, and graphs.
  • The ability to group and outline items together.
  • The ability to format data as tables.
  • The ability to apply data validation to data in spreadsheets.
  • The ability to apply data protection to spreadsheets.
  • The ability to apply what-if analysis to data.
  • The ability to analyse data.
  • Knowledge of and the ability to create and use information functions.
  • The ability to record macros.
  • The ability to manipulate data using control toolboxes and PivotTables.
  • Name and create the three basic sheets to set up a model.
  • Apply basic modelling techniques.
  • Walk through model development.
  • Explain the types and uses of different financial
  • models.
  • Navigate through the menus in MS Excel®.
  • Use formulas absolute referenced.
  • Name ranges and use named ranges in formulas.
  • Use Paste Special and Go To Special.
  • Apply filters to data and manipulate data using filters.
  • Sort and filter data.
  • Insert comments.
  • Make a copy or move a sheet.
  • View more than one sheet simultaneously.
  • Print a spreadsheet using headers and footers, repeating rows and columns.
  • Centre headings across columns.
  • Copy formatting across cells.
  • Adjusting column width and row height.
  • Apply conditional formatting to cells.
  • Transpose rows and columns.
  • Create and use the IF function in spreadsheets.
  • Create and use the following mathematical functions in MS Excel®: SUM, SUMIFS, SUBTOTAL, ROUND, ROUNDDOWN and ROUNDUP.
  • Create and use the following statistical functions in MS Excel®: AVERAGE, COUNT, COUNTA, COUNTIF,  MIN, MAX, LARGE and SMALL.
  • Create and use the following lookup functions in MS Excel®: HLOOKUP, VLOOKUP and XLOOKUP.
  • Create and use the following date and time functions in MS Excel®: DATE, DAY, MONTH, NOW, YEAR.
  • Create and use the following text functions in MS Excel®: LEFT, LEN, MID, RIGHT, LOWER, UPPER, PROPER, TEXT, TRIM, CONCATENATE and other text functions.
  • Convert text to columns.
  • Create and use the following financial functions in MS Excel®: IRR, NPV, XIRR, XNPV, PV, FV, PMT, RATE, IPMT and PPMT.
  • Create, use and manipulate the Charts in MS Excel®.
  • Group and outline items together.
  • Format data as a table and use the formatted table to analyse data.
  • Set up cells in such a way that only certain entries are allowed in those cells.
  • Display a message when the relevant cell is selected.
  • Give a message when the wrong entry is made.
  • Protect sheets so that only pre-set cells can be adjusted.
  • Change values to get an answer using Goal Seek.
  • Solve for optimal solutions using Solver.
  • Create different outcomes using Scenario Manager.
  • Use the FORECAST function.
  • Create samples from a dataset.
  • Create and use the following information functions in MS-Excel: ISERROR, IFERROR, ISNUMBER, ISTEXT, ISBLANK.
  • Record Macros for repeated tasks.
  • Link a Macro to an object.
  • Change data using Scrollers and Spinners.
  • Create a PivotTable.
  • Create dashboards using PowerPivot and PowerQuery.
  • Apply different filters, row labels and column labels to data in a pivot table.
  • Change, add and manipulate values in a pivot table.


Method of assessment
Assessment will take place through a practical simulation being done on MS Excel by the participant.
To pass the SLP, participants must obtain a final mark of 50% or more on a practical simulation model. The weight of the simulation test is 100% in determining the final mark. If participants fail the simulation, they will be allowed the opportunity to redo the simulation of which the maximum mark of the instance will be a 50% allocation as a final mark.

If a participant is not assessed, a certificate of attendance will be issued.

Additional information

Programme number
J16 100 1
Mode of delivery
Target group
Financial professionals, financial managers, management accountants, general accountants, department heads, internal auditors, project managers, administrative officials & those responsible for ensuring the effective use & management of corporate resources.
2.5 days

Contact us

Contact person name
Ms Janine Erasmus
Contact person e-mail
Contact person telephone number