Design Efficient Templates in Excel is the beginner level course for “Data Reporting on Spreadsheet” series, which aims to introduce practical techniques that can be used to setup an efficient template in Excel. By having Excel templates with adequate efficiency, Course Participants are able to improve quality of work in their daily work, as well as saving time by reducing the need of manual work on spreadsheets.
This course is suitable for users from insurance, takaful, reinsurance & retakaful companies who are looking for techniques to improve their existing Excel templates.
This course is organized using “building-block” approach, starting with introducing the characteristics of an efficient Excel template, as well as recommended best practices. This course provides systematic learning by guiding the Course Participants to plan their templates, populate required data and automate required analysis (in visual presentations or wordings, such as providing early warning message on a dashboard).
To enhance the quality of reports, Course Participants also learn the techniques in setting up validation and reconciliation in the reports (such as to eliminate issues of data omission).
To allow Course Participants to master relevant techniques better, they are provided with many exercises and case studies, which they need to modify partially completed Excel templates or setup a template from scratch according to the requirements given.
Through this solving-based course, the Course Participants will learn techniques that are useful and relevant to their daily work, as the problems presented during this course are simulated from the real-life works.
Course Level: Beginner
Target Audience: Business users from insurance, takaful, reinsurance & retakaful companies who are looking for techniques to improve their existing Excel templates
Chapter 1: How an Efficient Template Looks Like?
This Chapter covers a walkthrough on the key characteristics of an efficient Excel template, especially from the perspectives of systematicness, reusability and human readability. Furthermore, it also recommends best practices to Course participants, so that they can apply and practice throughout the entire courses.
Chapter 2: Plan Your Template
This Chapter introduces the recommended structure for an Excel template, by guiding Course Participants to segregate worksheets into the following categories, including report parameters, data inputs, calculations and reports.
Furthermore, Course Participants learn to identify and manage report parameters (such as report date and reporting period covered) in an Excel template, which are important elements of reusable template. By using Excel functions like TEXT and CELL as well as custom formatting of numbers, Course Participant can customize how report parameters that appear on the reports.
Chapter 3: Populate Your Data
This Chapter starts with explaining the general concepts of handling data in Excel, which highlights the importance of splitting templates that are used to manipulate source data and prepare reports. To instil a better understanding on data manipulation, this Chapter also includes a simple demonstration on the use of Power Query , without needing another application such as Microsoft Access.
By populating data in an appropriate way, Course Participants also learn to make their templates more flexible, such as using the same template to create reports for different distribution channels or product groups, by just simply changing the selection in a drop-down list!
Chapter 4: Automate Your Analysis
This Chapter explores further on techniques that make the reports more comprehensive, by automating analysis made through visual presentations (e.g. chart) or wordings (i.e. the wordings will be automatically adjusted in accordance to the results).
Course Participants shall explore the functionality of Conditional Formatting in details, by working on various case studies. For those Course Participants who involve in project management activities, they can create a simple Gantt Chart with automation (that reflects timeline of planned activities) by Excel – without needing to learn another application for project management.
Chapter 5: Validation & Reconciliation
Omission and duplication are common human errors that are found in report preparations. To improve the quality of reports, this Chapters explains how to setup validation and reconciliation in the report template – so that users can identify and rectify the errors easily.
Furthermore, Course Participants also learn how to apply some controls in the reports, such as version control and change control. By applying adequate protections on worksheets and workbooks, we can mitigate the risk of altering the formulas setup in the templates.