BSCI 271 Digital Notes.pdf

BSCI 271: Digital Data and Analysis Key Learning Outcomes 1. Understand basic data concepts and their interactions. 2. Identify and evaluate data sources (structured/unstructured). 3. Access and store data efficiently. 4. Recognize challenges in data analytics (volum...

BSCI 271: Digital Data and Analysis Key Learning Outcomes 1. Understand basic data concepts and their interactions. 2. Identify and evaluate data sources (structured/unstructured). 3. Access and store data efficiently. 4. Recognize challenges in data analytics (volume, quality, privacy, ethical issues). 5. Understand how finance uses data and required competencies. 6. Apply data analysis and visualization tools like Excel. (separate heading) Key Topics Covered 1. Basic data concepts a. What is Data? → Data: Raw facts (e.g., numbers, stats) used for calculations and reasoning. → Information: Data that’s processed into meaningful insights for decisions. → Digital Data: Stored in binary (0s and 1s), often measured in bytes (e.g., 1024 bytes = 1 KB). b. Types of Data → Financial Data: Helps in analyzing finances and forecasting. → Big Data: Large sets of data analyzed to understand trends and customer needs. The collection and analysis of large amounts of data to find trends, understand customer needs and help organisations to focus resources more effectively. → Enterprise data :Financial data plus broad operational and transactional data that bolsters analysis and forecasting c. Data Collection → Informal: Continuous learning (e.g., reading newspapers). → Formal: Specific collection for a purpose (e.g., surveys, accounting) d. Information : → Quantitative Information : Can be given a value, e.g. $100m Financial statements: Balance sheets, income statements, and cash flow statements that provide numerical data on a company's financial performance. Stock prices: The price of a company's stock can be measured and tracked over time. Interest rates: The rate at which money can be borrowed or invested can be measured and analysed to determine financial strategy. → Qualitative Information : Cannot be given a value, colour, subjective rating Market sentiment: The overall mood of investors and traders can influence stock prices and financial trends. Brand reputation: A company's brand reputation can affect its long-term financial success and value. Customer satisfaction: The opinions and experiences of customers can influence a company's financial performance → Qualities of information : 1. Accurate 2. Complete 3. Cost-effective 4. Understandable 5. Relevant 6. Accessible 6. Accessible 7. Timely 8. Easy-to-use 2. Data Sources → Internal: Accounting, HR, production data. → External: Government agencies, newspapers, customer feedback. → Structured Data: Stored in databases with predefined formats (rows/columns). → Unstructured Data: Raw, unformatted data (e.g., text from social media). → Metadata : additional details about files (eg. type of file) a. Qualities of Good Data 1. Accurate 2. Complete 3. Timely 4. Relevant 5. Understandable. Data manipulation : Reorganisation or transformation of data to make it easier to read or more meaningful 3. Data Analysis Methods Overall process of collecting, cleansing, manipulating and modelling data to support decision making. → Exploratory: Finding patterns in data. → Confirmatory: Testing hypotheses. → Predictive: Forecasting trends using models. → Text Analysis: Extracting data from text sources. 4. Data Modelling Analysis of an organisation’s data needs to support its business processes → Conceptual: Identifying data needs by consulting with stakeholders. → Logical: Documenting requirements formally. → Physical: Managing relationships between datasets. a. Advantaged of data modelling : Foundation for handling data effectively Business rules enforced on data and security Quality of data is enhanced Consistency is improved 5. Role of Finance in Data → Finance uses data for decision-making, forecasting, and reporting. → Collaboration between finance and data scientists is essential. → Connect this to the stages of data modelling. 6. Key Concepts in Data Analysis 1. Methodical Approach to Data Analysis Plan the analysis: → Understand the business problem and define the objective of the analysis. → Identify necessary data, data owners, locations, formats, and required fields. → Request data formally, ensuring permissions and approvals are in place. Collect and enhance the data: → Maintain a data collection register. → Choose appropriate tools (e.g., Excel) for analysis. → Assess data quality, reconcile data received vs. requested, and ensure all necessary fields are included. Perform the analysis: → Analyze the relevance, quality, and completeness of the data. → Use suitable analysis techniques to achieve the analysis objectives. Review and communicate results: → Ensure the data and methods used are documented. → Communicate findings clearly and effectively. 2. Data Cleansing in Excel Functions for cleaning and organizing data include: → CLEAN, CONCATENATE, LEFT/MID/RIGHT, TRIM, etc. This ensures data is accurate, relevant, and easy to work with. 3. Qualities of Good Information Data must be accurate, complete, cost-effective, understandable, accessible, timely, and easy to use. Technology enhances the process by collecting data more efficiently, freeing time for value-added tasks. 4. Data Extraction, Transformation, and Loading (ETL) Extraction: Identifying and pulling relevant data from a source. Transformation: Converting data into a format suitable for analysis. Loading: Storing the transformed data in a new system for further use. 5. Activities of Finance Professionals Assembling Information: Organizing and connecting financial and non-financial data. Analysis for Insights: Analyzing data to provide insights and recognize patterns. Advising to Influence: Sharing insights to guide decision-making. Applying for Impact: Supporting actions that drive the organization toward desired outcomes. Here is a simple explanation of each Excel function we had to practise and how to use them: 1. CLEAN Purpose: Removes all non-printable characters from text. How to use: =CLEAN(text) Example: =CLEAN(A1) removes non-printable characters from the value in cell A1. 2. CONCAT Purpose: Joins two or more strings together. How to use: =CONCAT(text1, text2,...) Example: =CONCAT(A1, " ", B1) combines the text in A1 and B1 with a space in between. 3. EXACT Purpose: Compares two strings to see if they are exactly the same (case-sensitive). How to use: =EXACT(text1, text2) Example: =EXACT(A1, B1) returns TRUE if A1 and B1 are exactly the same. 4. FIND Purpose: Finds the position of a substring within a string (case-sensitive). How to use: =FIND(find_text, within_text, [start_num]) Example: =FIND("apple", A1) returns the starting position of the word "apple" in A1. 5. SEARCH Purpose: Finds the position of a substring within a string (not case-sensitive). How to use: =SEARCH(find_text, within_text, [start_num]) Example: =SEARCH("apple", A1) returns the position of the word "apple" in A1, ignoring case. 6. FIXED Purpose: Rounds a number to a specified number of decimals and formats it as text. How to use: =FIXED(number, [decimals], [no_commas]) Example: =FIXED(A1, 2) rounds the value in A1 to two decimal places. 7. LEFT / MID / RIGHT Purpose: Extracts parts of a string. → LEFT: Extracts characters from the left. → MID: Extracts a substring starting at a specific point. → RIGHT: Extracts characters from the right. How to use: → =LEFT(text, num_chars) extracts from the left. → =MID(text, start_num, num_chars) extracts from the middle. → =RIGHT(text, num_chars) extracts from the right. Example: =LEFT(A1, 3) returns the first 3 characters in A1. 8. LEN Purpose: Returns the length of a string. How to use: =LEN(text) Example: =LEN(A1) returns the number of characters in cell A1. 9. LOWER / UPPER Purpose: Converts text to lowercase (LOWER) or uppercase (UPPER). How to use: → =LOWER(text) converts to lowercase. → =UPPER(text) converts to uppercase. Example: =LOWER(A1) converts the text in A1 to lowercase. 10. PROPER Purpose: Capitalizes the first letter of each word. How to use: =PROPER(text) Example: =PROPER(A1) capitalizes the first letter of each word in A1. 11. REPLACE Purpose: Replaces part of a string with another string. How to use: =REPLACE(old_text, start_num, num_chars, new_text) Example: =REPLACE(A1, 1, 3, "Hi") replaces the first 3 characters in A1 with "Hi." 12. SUBSTITUTE Purpose: Replaces occurrences of a substring with another substring. How to use: =SUBSTITUTE(text, old_text, new_text, [instance_num]) Example: =SUBSTITUTE(A1, "apple", "orange") replaces "apple" with "orange" in A1. 13. T Purpose: Returns the text if the value is text; otherwise, it returns an empty string. How to use: =T(value) Example: =T(A1) returns the text in A1 if it contains text, or an empty string if it doesn’t. 14. TEXT Purpose: Converts a number to text in a specified format. How to use: =TEXT(value, format_text) Example: =TEXT(A1, "0.00") formats the value in A1 to two decimal places as text. 15. VALUE Purpose: Converts text that appears like a number into a number. How to use: =VALUE(text) Example: =VALUE(A1) converts the text in A1 to a number. 16. TRIM Purpose: Removes extra spaces from text (except for single spaces between words). How to use: =TRIM(text) Example: =TRIM(A1) removes any extra spaces in the text of cell A1. 17. IF STATEMENTS Purpose: Performs a logical test and returns one value if TRUE and another if FALSE. How to use: =IF(logical_test, value_if_true, value_if_false) Example: =IF(A1 > 10, "Yes", "No") returns "Yes" if A1 is greater than 10, otherwise it returns "No".