Online Research Project from the field of CS – Business Computing. The guidelines are included in the pdf file attached down below. In addition, there is an example of how the project should (ideally) look like(excel and word file).
CS201 – Business Computing (Level 5)
Fall-2022 Online Research Project
1. Project Objectives The research project aims at enhancing your skills in view of your term project through
online research and further cultivating your research skills.
1.1. Research data management applications
With this project you are to investigate literature on how Excel is used in Business
environments in order to support specific operations of the Business Life Cycle. You
need to concentrate on features and functionalities of Excel not covered in class. Specific
handouts have been posted with a list of activities that are encountered in an enterprise.
The keywords therein will form the basis of your search.
Note 1: I have added a list of keywords at the end of this document.
Your research project will result in a report. In this report you will present the techniques
that you learned, speak about their utility and show usage examples.
2. Conducting your Research Rather little academic literature exists on application of Excel in business. Most
academic papers focus on processes or follow a different direction. However, with the
appropriate keywords, you can find some papers, which can form the basis of your
further search. In addition, you can research online for a, which will most likely contain
most of the knowledge at the technical level. I recommend the following sequence of
2.1. Academic publications
Discover some papers on academic publications sites. These papers will likely explain
the problems tackled, their complexity, and how these tools can help in Problem Solving
in the indicated domains.
2.2. Online fora
These are likely the richest source. In there you will see what problems people put up for
answers; the solutions that experts suggested. You will see several examples of formulas,
at times more sophisticated than those covered in class.
Note 2: An indicative list of online resources can be found later in this document.
2.3. Final report
The culmination of your research work will be a report. In the report you will list what
you have learned, how the formulas work and site examples of applications
Important Note!!! It is easy to detect a page copied directly from an internet site. You
really need to comprehend enough of how the technique works. You need to understand
from the examples found in the fora in which cases these techniques work and provide
your own examples of usage.
3. Submit Submission for stage 1 will be your proposal report.
3.1. Stage 2
Submission for stage 2 will be your design, as reflected in the structure of the tables.
Using the data generator you should also populate the tables. Submission for stage 2 will
be an Excel file that should contain the data and the queries.
4. Marking Criteria
Breadth and depth of research 25
Comprehension of researched techniques is reflected in writeup 30
Domain of applications of these techniques is clearly understood 20
Quality of student-provided examples 25
Online Resources Sites of Excel gurus
Chip Pearson's site: http://www.cpearson.com/
John Walkenbach's site: http://spreadsheetpage.com/
5. Indicative Keyword List
• Conditional sum
• SUMIF, SUMIFS, *IF, *IFS functions
• Conditional sum with two criteria – SUMPRODUCT
• D* functions (DSUM, DCOUNT)
• Advanced lookup
• Excel lists (built in feature after Excel 2003, which we do not cover in class)
• Custom Data Validation (based on formulas, but also investigate built-in
• If you research Finance function, your research ought to be comprehensive.
Identify groups of related functions.
• GETPIVOTDATA: Learn how to use it with cells instead of the constants that
Excel automatically supplies when you click.
• Additional Text or Date functions not covered in class.
• Comprehensive study of all rounding functions.
• Range returning functions: OFFSET, INDIRECT, INDEX – Provide equivalent
examples (e.g. do something with INDIRECT, do it also with OFFSET).
• Names: Workbook and Worksheet names. Constants, functions, dynamic ranges.
• Functions (Undocumented by Excel) which automate Solver. Find their
CUSTOM DATA VALIDATION
Computer Science 201
Custom data validation
Custom Data Validation is a feature of Excel that regulates the data a user can insert in certain cells (Cheusheva, 2017). The main advantage of this technique is that it provides accuracy and consistency of the inputs, since only particular type of data can be inserted in the cells (Cheusheva, 2017). For example, a user might want all cells between B1:B10 to contain numeric values. In case someone attempts to input a text value Excel will not accept it, because Custom Data Validation allows only numeric values.
Closely related to Custom Data Validation is another feature of Excel, Conditional Formatting. This characteristic helps individuals to distinguish and analyze data by formatting certain cells (Use conditional formatting to highlight information, 2020). In the previous example the user inserted numeric values between B1:B10. Now these numbers have to be analyzed. All cells that contain values from one to five will be colored green, while the rest will be colored red. Conditional Formatting is the feature that will help tackle this issue. By setting parameters and categorizing each cell in one of the two colors, users will be able to analyze the data based easier and more efficiently.
How the formulas work
Custom data validation guide
If a user aims to use Custom Data Validation, has to go to data page, and click Data Validation. It has specific options ready to use, such as whole number, decimal, date, time, list, and text length. Users can implement the parameters they want in each one of these choices (Cheusheva, 2017). Apart from that, there is also another criterion which is called “custom”. The main idea is that individuals can insert parameters with the use of formulas (Cheusheva, 2017). In case the criteria a user sets are not met, Excel will provide a message that informs about the wrong input. Finally, a person can make a message appear in the beginning to inform users what type of data to insert, while at the same time can regulate what kind of message will appear in case of invalid inputs (Cheusheva, 2017).
Conditional Formatting guide
The main characteristic of this feature is that it categorizes existing data based on the user's parameters. Conditional Formatting can be found in the home tab of Excel a click “Conditional Formatting” (Use conditional formatting to highlight information, 2020).. At the same time, numerous choices will appear which will suggest how to categorize data.
The first one is Highlighted Cell Rules, which uses comparison criteria (<,=,>) and formats only the cells that are TRUE to the condition a user sets (Use conditional formatting to highlight information, 2020). Furthermore, it’s the Top Bottom Rules, which format only cells that have the top ten values from the bottom or the top(Use conditional formatting to highlight information, 2020). Also Top Bottom Rules have above and below average rules. In addition, Data Bars highlight with a bar the cell and can show at once how big the value is compared to the others which have the same formatting (Use conditional formatting to highlight information, 2020). Color Scale provides a variety of colors that fill cells based on how high or low the value they contain is(Use conditional formatting to highlight information, 2020). Finally, Icon Sets allow users to use icons to divide data in different categories (Use conditional formatting to highlight information, 2020).
Similarly with Custom Data Validation, users can insert their own formulas in order to achieve the desired result. All they have to do is to click on New Rules (under the Icon Sets), and then select the last option, which is “Use a formula to determine which cells to format”. This can result in determining the color of the cells by using various formulas and data from other columns.
Examples of application
These two features can be used in various situations. For example, businesses that sell products to their customers on credit can keep track with the payment activity. If a customer pays then the cell in column sales will turn green, otherwise it will remain empty and red. Also, companies tend to keep large books that contain various data, like customer information, revenues and expenses, sales etc. Such companies can use the capabilities of Excel and regulate which values each cell can accept, in order to avoid confusion.
An issue that might occur and can be solved with the help of Custom Data Validation might be the following: “If A1 is answered yes, then B1 requires a yes/no response. If A1 is answered No, then B1 should be blank.” (Excelforum, 2020). In order to solve such problem with this feature of Excel, individuals have to go to Custom and insert the following formula:” =AND (A1="Yes", OR (C1="Yes", C1="No"))” (Excelforum, 2020).
Cheusheva, S., 2017. Data Validation In Excel: How To Add, Use And Remove. [online] Excel tutorials, functions and formulas for beginners and advanced users – Ablebits.com Blog. Available at: <https://www.ablebits.com/office-addins-blog/2017/08/16/data-validation-excel/#what-is-data-validation> [Accessed 4 June 2021].
Excelforum.com. 2020. If/Then. [online] Available at: <https://www.excelforum.com/excel-formulas-and-functions/884456-if-then.html> [Accessed 4 June 2021].
Support.office.com. 2020. Use Conditional Formatting To Highlight Information. [online] Available at: <https://support.office.com/en-us/article/use-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f> [Accessed 4 June 2021].