Guided Learning Project: Virtual Overseas Community Project (OCP) (Vietnam) & Excel mini Project
Why did I choose Excel as my Mini Project?
According to Alliance Career Training Solutions (ACTS) research in 2019, recent studies have shown that 82% of jobs require Excel skills or experience with productivity software. Therefore, by using Excel as my mini project, I can further explore and understand the different Excel functions that are available which will be useful and relevant to me when I apply for my internship and job in future.
​
Studying in Business Process & Systems Engineering (BZE) requires me to use my knowledge in Excel to complete many of my assignments for modules such as Quantitative Methods (QM) and Process Optimisation & Improvement (PROCOI). By using Excel as the topic for my mini project, I would be able to better understand how the Excel functions can be applied to the real working world by creating guided exercises.
Examples of Internship Posters For BZE diploma
Internship 1: Changi Airport Group (Singapore) Pte Ltd Loading/Unloading Bay Operations
Internship 2: DBS Bank e-Banking Department
Excel Mini Project
For my Excel Mini Project, I have decided to work on 4 main Topics that focuses on the fundamentals of Excel. The 4 Topics are:
1. Essential Concepts and Basic Skills
2. Logical Functions
3. Financial Functions
4. What-If Analysis Tools
Things I have done to complete the project:
1. Conducting research online (E.g. Watching tutorials on YouTube, googling articles on relevant Excel functions).
​
2. Seeking help from peers and lecturers. They might even be able to explain the purpose of Excel functions better than some online articles!
​
3. Reading up on Excel guidebooks borrowed from the library. It is very useful as they tend to go more in-depth about the topic and provide examples of how the functions can be used.
1. Essential Concepts and Basic Skills (Chapter 1)
In this topic, we explored the fundamentals of Excel such as navigating, creating and saving an Excel workbook. We also look at formatting the cell and worksheet through the use of Borders, Colours, Style, and Themes. Finally, we looked at using Formulas and Functions in our Excel worksheet.
1.1 Navigating, Creating, and Saving an Excel workbook
Excel is an incredibly powerful tool which is used to do all of the following, for example:
-Organise data in an easy-to-navigate way
-Do basic and complex mathematical functions for you without the need of a calculator
-Use large sets of data to create helpful graphics and charts
-Analyse data and make forecasting predictions
-Create, build, and edit pixelated images
​
However, to be able to use Excel software to its fullest potential, we must first understand how to use and navigate the Excel worksheet.
1. Rows
2. Columns
3. Cell
4. Active Cell
5. Worksheets
6. Insert Worksheet
7. Zoom Slider
8. Scrollbar
9. Formula Bar
10. Title Bar
11. Ribbon
A guided exercise is created to guide the reader on how to create and save an Excel workbook, to view the whole step-by-step guided exercise (Pg 3-6), click on the link below to download the Excel guide book.
1.2 Formatting a cell in Excel
When we format a cell in Excel, we change the appearance of a cell without changing the value in the cell. There is a variety of options which can be used to format a cell such as Borders, Colours, Style, and Themes.
​
For each option, a guided exercise is created to teach the reader how to apply each option to format a cell or worksheet. To view the whole step-by-step exercise, it is on page 6 to 17 of the Excel guidebook.
1.3 Formulas and Functions in Excel
Formulas and Functions are used in Excel to calculate large set of data easily without the need to calculate it manually. This is very useful as helps to save a lot of time and effort.
​
A guided exercise is provided to teach the reader 3 different ways to insert a formula into a cell. The first way is to manually type a formula into a cell, the second way is to use the Insert Function Option from the Formula Tab. Finally the last way is to select a formula from the one of the groups in the Formula Tab and insert it into the cell. The step-by-step guided exercise is from page 17 to 21.
2. Logical Functions (Chapter 4)
In this topic, we explore the Logical Functions that are available in Excel and how to use them. Logical functions are used in spreadsheets in Excel to test whether a situation is true or false. You can choose to do one thing or another depending on the results of the test. For example, these decisions can be used to display information, perform further test, or to perform different calculations.
​
Logical functions provide decision-making tools for information in a spreadsheet. They allow you to look at the contents of a cell, or to perform a calculation, and then test that result against a required figure or value.
A guided exercise is created to teach the reader on how to use 5 different Logical functions individually.
The 5 Logical functions are:
IF Function
IFERROR Function
OR Function
AND Function
NOT Function
​
The step-by-step guided exercises can be viewed from page 26 to 32 in the Excel guide book.
A logical operator is used in Excel to compare two values. Logical operators are sometimes called Boolean operators because the result of the comparison in any given case can only be either TRUE or FALSE. In total, there are only 6 Logical operators available in Excel.
3. Financial Functions (Chapter 7)
In Excel, financial functions are used to carry out a variety of financial calculations such as calculating yield, interest rates, internal rate of return, investment valuations, asset depreciation, and payments.
​
In Excel, when using the financial functions, any money that is paid out (outflows) should be a negative number while any money that is being received (inflows) are positive numbers.
In financial function, variables within the squared brackets [ ] are optional to include in the function. Omitting any variable that is in the squared bracket in the function will not affect the overall function.
In this guide book, we will be looking at 6 Financial Functions that is widely used in many companies.
List of Financial Functions:
A step-by-step guided exercise with real life scenarios is created for each Financial Function to help the reader to understand how and when to apply these functions in Excel. To view the guided exercises, visit page 33 to 41 in the Excel guide book.
4. What-If Analysis Tool
What-If Analysis is the process of changing the values in the cells to see how those changes will affect the outcome of formulas on the worksheet.
In Excel, there are three different kinds of What-If Analysis tools: Scenarios, Goal Seek, and Data Table. Scenarios and Data Table takes sets of input values and determine different possible results based on those values.
You can install additional add-ins that can help you perform What-If Analysis in addition to the three tools. For example, the Solver add-in is similar to Goal Seek, but it can accommodate more variables
Scenario:
A Scenario is a set of values that Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios and then switch between these scenarios to view the different results. Scenarios are managed with the Scenario Manager from the What-If Analysis group on the Data tab.
​
Goal Seek:
Goal Seek is used to find the input value the formula needs to get the result you want from the formula. Goal seek can be used to determine the interest rate also.
Data Table:
A data table is a range of cells in which you can change the values in some of the cells and come up with different answers to a problem.
In Excel, depending on the number of variables and formulas that you need to test, you can create either one-variable or two-variable data tables.
One-variable data table
Two-variable data table
A step-by-step guided exercise is created for each analysis tool to help the reader to understand how and when to apply these tools for analysis in Excel. To view the guided exercises, visit page 43 to 55 in the Excel guide book.