Cybercrime Prevention act of Philippines PDF

Cybercrime Prevention act of Philippines McGraw-Hill Technology Education Copyright © 2006 by The McGraw-Hill Companies, Inc. All rights reserved.  Cybercrime Prevention act of Philippines The Fundamentals of the Act Cybercrime also called computer crime...

Cybercrime Prevention act of Philippines McGraw-Hill Technology Education Copyright © 2006 by The McGraw-Hill Companies, Inc. All rights reserved.  Cybercrime Prevention act of Philippines The Fundamentals of the Act Cybercrime also called computer crime is the use of a computer as an instrument to further illegal ends such as committing fraud trafficking child pornography, intellectual property violations, stealing identities or violation of privacy. 14A-2  The difference between traditional criminal offences from cybercrime is the use of the computer in committing such offences. Majorlycybercrime is an attack on the information of Individuals, governments or corporations. 14A-3  To combat and prevent cybercrime, the government of the Philippines introduced the Republic Act No.10175 or Cyber Prevention Act of 2012. This Act was signed by the President of the Philippines Mr. Benigno Aquino on September 12th of 2012. The original goal of this Act was to penalize acts like cybersex, child pornography, identity theft etc. The key provisions of the Cybercrime Prevention Act of 2012 14A-4  Illegal Access: Access to a computer or any application without permission. Illegal Interception: Interception of any non-public communication of computer data to, from, or within a computer system by an unauthorised person. Data Interference: Unauthorized tampering with, destroying, deleting, or deteriorating computer data, electronic documents, or electronic data messages, as well as the introduction or transmission of viruses. This provision can also cover authorised activity if the person’s behaviour went beyond the agreed-upon scope and resulted in the damages listed in this provision. 14A-5  System Interference: Unauthorized interference with the operation of a computer or computer network, including the introduction or transmission of viruses, by inputting, transmitting, damaging, deleting, deteriorating, altering, or suppressing computer data or programmes, electronic data messages, or by deleting, deteriorating, altering, or suppressing them. This provision can also cover authorised activity if the person’s behaviour went beyond the agreed-upon scope and resulted in the damages listed in this provision. Misuse of devices: Unauthorized use, possession, production, sale, procurement, importation, distribution, or otherwise making available of gadgets, computer programmes, or other materials developed or adapted for committing any of the acts listed in Republic Act 10175. Unauthorized use of a computer password, access code, or similar data that allows the entire or a portion of a computer system to be accessed with the intent of using it to perpetrate any of the offences listed in Republic Act 10175. 14A-6  Cybersquatting: Acquisition of a domain name in bad faith on the internet to profit, deceive, harm reputation, and prevent others from registering it. This includes trademarks that were already registered at the time of registration, names of people other than the registrant, and trademarks that were purchased with intellectual property interests in them. Those who get domain names of prominent brands and individuals who in turn are used to damage their reputation can be sued under this provision. Note that freedom of expression and infringement on trademarks or names of persons are usually treated separately. A party can exercise freedom of expression without necessarily violating the trademarks of a brand or the names of persons. Computer related Forgery: Unauthorized input, alteration, or deletion of computer data that results in inauthentic data with the intent that it be considered or acted on for legal purposes as if it were authentic, regardless of whether the data is directly readable and intelligible; or the act of knowingly using computer data that is the product of computer-related forgery as defined here to 14A-7 perpetuate.  Computer-related Fraud: Unauthorized access to, alteration of, or deletion of computer data or programmes, or interference with the operation of a computer system, with the purpose to cause damage. Computer-related Identity Theft: Unauthorized acquisition, use, abuse, transfer, possession, change, or deletion of an individual’s identifying information, whether natural or legal. Cybersex: For favour or consideration, willful engagement, maintenance, control, or operation, directly or indirectly, of any lascivious presentation of sexual organs or sexual activity via a computer system. 14A-8  Child Pornography: Unlawful or banned activities conducted through a computer system, as defined and punished by Republic Act No. 9775 or the Anti-Child Pornography Act of 2009. Libel: Acts of libel that are illegal or forbidden under Article 355 of the Revised Penal Code, as amended, and are committed using a computer system or any other similar means that may be created in the future. Penal Code Revision According to Article 355 Libel is defined as defamation of character by writings or other means. Libel committed by writing, printing, lithography, engraving, radio, phonograph, painting, theatrical exhibition, cinematographic exhibition, or any other similar means is punishable by prison correctional for the minimum and medium periods, or a fine ranging from 200 to 6,000 pesos, or both, in addition to any civil action that the offended party may bring. 14A-9  Aiding or Abetting in the commission of cybercrime: Anyone who knowingly assists or abets the conduct of any of the offences listed in this Act will be held accountable. Attempt in the commission of cybercrime: Any person who willfully attempts to commit any of the offences enumerated in this Act shall be held liable. All crimes defined and penalized by the Revised Penal Code, as amended, and special laws, if committed by, through and with the use of information and communications technologies shall be covered by the relevant provisions of this Act. 14A-10  FINAL EXAMINATION – DEC 2 TO 7 – PLEASE SECURE FINAL PERMITS – CHECK ALL REQUIREMENTS COMPLETION AND CONSULTATION – DEC. 9-10 BSIT CHRISTMAS PARTY – DECEMBER 11, 2024 – 12:00PM EXCHANGE GIFT RAFFLES GAMES DECEMBER 12-JAN 6 – SEM BREAK ENROLLMENT Understanding Computers: Today and Tomorrow, 13th Edition Cyber Security FINAL EXAMINATION – DEC 2 TO 7 PLEASE SECURE FINAL PERMITS CHECK ALL REQUIREMENTS COMPLETION AND CONSULTATION DEC. 9-10 BSIT CHRISTMAS PARTY DECEMBER 11, 2024 12:00PM EXCHANGE GIFT RAFFLES GAMES DECEMBER 12-JAN 6 SEM BREAK ENROLLMENT Importance of Cyber Security “The only system which is truly secure is one which is switched off and unplugged, locked in a titanium safe, buried in a concrete bunker, and is surrounded by nerve gas and very highly paid armed guards. Even then, I wouldn’t stake my life on it.” - Professor Gene Spafford https://spaf.cerias.purdue.edu/ Importance of Cyber Security The Internet allows an attacker to work from anywhere on the planet. Risks caused by poor security knowledge and practice: Identity Theft Monetary Theft Legal Ramifications (for yourself and your organization) Sanctions or termination if policies are not followed Cyber Security Cyber security refers to the body of technologies, processes, and practices designed to protect networks, devices, programs, and data from attack, damage, or unauthorized access. Cyber Security is Safety Security: We must protect our computers and data in the same way that we secure the doors to our homes. Safety: We must behave in ways that protect us against risks and threats that come with technology. What is a Secure System? (CIA Triad) Confidentiality – restrict access to authorized individuals Integrity – data has not been altered in an unauthorized manner Availability – information Availability can be accessed and modified by authorized individuals in an appropriate timeframe CIA Triad Example: Confidentiality Criminal steals customers’ usernames, passwords, or credit card information Protecting information from unauthorized access and disclosure CIA Triad Example: Someone alters payroll information or a Integrity proposed product design Protecting information from unauthorized modificatio n CIA Triad Availability Example: Your customers are unable to access your online services Preventing disruption in how information is accessed Threats and Vulnerabilities What are we protecting our and our stakeholders information from? Threats: Any circumstances or events that can potentially harm an information system by destroying it, disclosing the information stored on the system, adversely modifying data, or making the system unavailable Vulnerabilities: Weakness in an information system or its components that could be exploited.  Phishing and Spear- phishing Social AEtntgaicnkesering Scams Common WHAT KINDS Malware and OF THREATS Ransomware Business Email ARE THERE? Compromise Fake websites that steal data or infect devices And much more Phishing Phishing refers to the practice of creating fake emails or SMS that appear to come from someone you trust, such as: Bank, Credit Card Company, Popular Websites The email/SMS will ask you to “confirm your account details or your vendor’s account details”, and then direct you to a website that looks just like the real website, but whose sole purpose is for steal information. Of course, if you enter your information, a cybercriminal could use it to steal your identity and possible make fraudulent purchases with your money. Example of Phishing Social Engineering When attempting to steal information or a person’s identity, a hacker will often try to trick you into giving out sensitive information rather than breaking into your computer. Social Engineering can happen: Over the phone By text message Instant message Email Malware Malware = “malicious software” Malware is any kind of unwanted software that is installed without your consent on your computer and other digital devices. Viruses, Worms, Trojan horses, Bombs, Spyware, Adware, Ransomware are subgroups of malware. Viruses A virus tries to infect a carrier, which in turn relies on the carrier to spread the virus around. A computer virus is a program that can replicate itself and spread from one computer to another. Viruses cont. Direct infection: virus can infect files every time a user opens that specific infected program, document or file. Fast Infection: is when a virus infects any file that is accessed by the program that is infected. Slow infection: is when the virus infects any new or modified program, file or document. Great way to trick a antivirus program! Sparse Infection: is the process of randomly infecting files, etc. on the computer. RAM-resident infection: is when the infection buries itself in your Computer’s Random Access Memory. Bombs Logic Bombs: is programming code that is designed to execute or explode when a certain condition is reached. Most the time it goes off when a certain time is reached or a program fails to execute. But it these bombs wait for a triggered event to happen. Most common use of this is in the financial/business world. Most IT employees call this the disgruntled employee syndrome. Trojans Trojan horse: is a program or software designed to look like a useful or legitimate file. Once the program is installed and opened it steals information or deletes data. Trojan horses compared to other types of malware is that it usually runs only once and then is done functioning. Some create back-door effects Another distribution of Trojans is by infecting a server that hosts websites. Downfall of Trojans: very reliant on the user. Worms Worms and viruses get interchanged commonly in the media. In reality a worm is more dangerous than a virus. User Propagation vs. Self Propagation Worm is designed to replicate itself and disperse throughout the user’s network. Email Worms and Internet Worms are the two most common worm. Email Worm Email worm goes into a user’s contact/address book and chooses every user in that contact list. It then copies itself and puts itself into an attachment; then the user will open the attachment and the process will start over again! Example: I LOVE YOU WORM Internet Worms An Internet Worm is designed to be conspicuous to the user. The worms scans the computer for open internet ports that the worm can download itself into the computer. Once inside the computer the worms scans the internet to infect more computers. Adware and Spyware Adware is a type of malware designed to display advertisements in the user’s software. They can be designed to be harmless or harmful; the adware gathers information on what the user searches the World Wide Web for. With this gathered information it displays ads corresponding to information collected. Spyware is like adware it spies on the user to see what information it can collect off the user’s computer to display pop ads on the user’s computer. Spyware unlike adware likes to use memory from programs running in the background of the computer to keep close watch on the user. This most often clogs up the computer causing the program or computer to slow down and become un-functional. Identity Theft Identity Theft Impersonation by private information Thief can ‘become’ the victim Reported incidents rising Methods of stealing information Shoulder surfing Snagging Dumpster diving Social engineering High-tech methods Identity Theft Loss of privacy Personal information is stored electronically Purchases are stored in a database Data is sold to other companies Public records on the Internet Internet use is monitored and logged None of these techniques are illegal Ransomware Ransomware is a type of malware that restricts your access to systems and files, typically by encryption and then demands a ransom to restore access. Often, systems are infected by ransomware through a link in a malicious email. When the user clicks the link, the ransomware is downloaded to the user’s computer, smartphone or other device. Ransomware may spread through connected networks. Business/Official Email Compromise BEC is a big problem for you and your organization: Your email is compromised. Another employee of your organization is compromised Almost always, these emails fall into 2 categories: 1. Downloading and spreading additional malware automatically 2. Urging the customer to perform a financial transaction immediately Tips and Tricks to share with customers: BEC made up half of cyber-crime losses in 2019; $75K per scam Standard phishing email awareness – don’t click links or download attachments Pay attention to the email address Enable MFAfor business email accounts Business Email Compromise COVID-19 Cyber Threats  Cyber Crime Cyber Crime is a generic term that refers to all criminal activities done using the medium of communication devices, computers, mobile phones, tablets etc. It can be categorized in three ways: The computer as a target – attacking the computers of others. The computer as a weapon- Using a computer to commit “traditional crime” that we see in the physical world. The computer as an accessory- Using a computer as a “fancy filing cabinet” to store illegal or stolen information. Hacking Financial (theft, fraud, blackmail) Political/State (state level/military) Fame/Kudos (fun/status) Hacktivism (cause) Pen Testers (legal hacking) Police Insider Business Which hat you want to wear? Ethical Hacking Cybercrime as a Service Global Cyber Security Trends – The next wave Recent studies reveal three major findings: Growing threat to national security - web espionage becomes increasingly advanced, moving from curiosity to well-funded and well- organized operations aimed at not only financial, but also political or technical gain Increasing threat to online services – affecting individuals and industry because of growth of sophistication of attack techniques Emergence of a sophisticated market for software flaws – that can be used to carry out espionage and attacks on Govt. and Critical information infrastructure. Findings indicate a blurred line between legal and illegal sales of software vulnerabilities Mischievous activities in cyber space have expanded from novice geeks to organized criminal gangs that are going Hi-tech How We Protect Information? People Training, education, awareness, repetition Process Governance, oversight, policy, reporting Technology Firewalls, IDS/ISP, SIEM, anti-malware Strong passwords, Logging/monitoring Which is the weakest link? Social Engineering Best Practices USE YOUR SECURITY SPIDER SENSE! ALWAYS validate requests for information if you’re not 100000% sure Call a number YOU know Google it… ALWAYS ASK QUESTIONS! Is this who I think it is FOR SURE? Did someone mention this to me personally, or was it discussed at a staff meeting? Is this the FIRST I’m hearing about this? Cyber Security and Privacy Starts and Ends with Us! Security Tips Commit to a disciplined practice of information security and continue to refresh yourself so you don’t become a point of vulnerability in our security defenses.  7-1 Computerized Accounting  7-2 computer in accounting Uses of computer in accounting and Finance  7-3 Computer is an electronic device which calculates, compares, modifies, stores and retrieve facts and figures, is being widely used in the field of accounting. Application of computers in Accounting: 1. Recording of all business transactions. 2. Preparation of various ledger accounts. 3. Processing the payroll information. 4. Preparation of trial balance. 5. Preparation of final accounts.  7-4 Computerised Accounting System A computerized accounting system is that accounting information system that help in processing the financial transactions and events as per the GAAP and leads to the generation of reports as per the requirements of the users. An accounting system whether manual or computerized has two parameters: a) It has to follow the well defined concepts known as the accounting principles. b) It has to maintain the user-defined structure for maintenance of records and generation of reports.  7-5 Difference between Manual and computerized accounting system Speed The main difference between manual and computerized systems is speed. Accounting software processes data and creates reports much faster than manual systems. Calculations are done automatically in software programs, minimizing errors and increasing efficiency. Once data is input, you can create reports literally by pressing a button in a computerized system. Cost Another difference between manual and computerized systems is cost. Manual accounting with paper and pencil is much cheaper than a computerized system, which requires a machine and software. Other expenses associated with accounting software include training and program maintenance. Expenses can add up fast with costs for printers, paper, ink and other supplies. Backup A third difference between manual and computerized systems is the ease of backup of a computerized system. All transactions can be saved and backed up, in case of fire or other mishap. You cannot do this with paper records, unless you make copies of all pages--a long and inefficient process.  7-6 Financial Statements In Manual accounting systems Preparation of trial balance is essential to produce the financial statements.. With a computerized accounting system, financial statement are generated from the software itself and therefore there is no need to prepare trial. Reports Reports are created in a timely manner when using a computerized accounting system. Reports generated from computerized accounting software allow managers to run the company in a more efficient manner. Creating reports in a manual accounting system may lead to more staff frustration and result in having to work with outdated information.  7-7 Explain what an accounting information system (AIS) is and describe the basic functions it performs.  7-8 An accounting information system is a system of collecting, processing, summarizing and reporting information about a business organization in monetary terms. It gathers data describing the organization activities, maintain a detailed financial records of the organization operations, transforms the data into information and makes the information available to users both inside and outside the organization.  7-9 What important functions does the AIS perform in an organization 1 It collects and stores data about activities and transactions. 2 It processes data into information that is useful for making decisions. 3 It provides adequate controls to safeguard the organization’s assets. 4. Provide the database that is used by other information systems. 5.Producing standard management reports that summarizes the firm’s financial condition.  7-10 Characteristics of AIS 1.Helps in handling and manipulating accounting and financial transactions of an organization. 2.It is responsible for meeting information needs by generating reports. These reports are of two types: a. Reports for outsiders. b. Internal reports. 3. AIS is helpful in producing futuristic data in the form of budget forecasts etc. 4. It helps in maintaining accounting information as per the guidelines of the law.  7-11 Describe the components of a computerized accounting system used to process data and produce useful information.  7-12 Input: The input subsystem-deals with the collection, arrangement and making available all types of transactional, financial and accounting data to the system, required for its various functions. Process: The processing deals with the processing of the information gathered by the input sub- system which is possible with help of INTERNET and Application software. Output: The output subsystem is responsible for producing the processed data in user understandable format.  7-13 Control: This Component is responsible for keeping control over AIS in following manner: a. Follow internal laws and rules of the organization. b. Follow rules laid down by govt. and statutory bodies. c. Ensuring quality of information by setting up performance standards. d. Comparing actual with standard performance. e. Analyzing the variances and take corrective measures. f. Ensure safety and security of the information managed by AIS.  7-14 Purpose of AIS Sales order processing Inventory control Accounts receivables Accounts payables Payroll General ledger.  7-15 Components of an Accounting Information System  7-16 Components of a Computerized Accounting System The sales module receives order data from customers and maintains accounts receivable information.  7-17 Exhibit 1 Components of an Accounting Information System  7-18 Components of a Computerized Accounting System The purchasing and inventory management module provides purchase order data to vendors. Vendors are those who supply specific products to a company.  7-19 Components of a Computerized Accounting System The purchasing and inventory management module maintains accounts payable and inventory information.  7-20 Components of a Computerized Accounting System The human resources module maintains data about employees, including hours worked and wage rates. It is used for preparing payroll and payroll tax information.  7-21 Components of a Computerized Accounting System The production module tracks the flow of costs through the manufacturing process.  7-22 Components of a Computerized Accounting System The asset management module identifies long-term asset costs, their expected useful lives, and where these assets are located in the company.  7-23 Components of a Computerized Accounting System The financial management module keeps track of debt, repayment schedules, interest rates, and shareholder information.  7-24 Components of a Computerized Accounting System The general ledger/financial reporting module provides information for use by external stakeholders, including shareholders and government regulators.  7-25 Subsidiary Accounts Subsidiary accounts include transactions for individual customers, suppliers, or products. Records for all subsidiary accounts of a particular type are maintained in a subsidiary ledger.  7-26 Subsidiary Accounts A subsidiary accounts receivable ledger would consist of all the individual customers accounts, with data about purchases and payments for each customer.  7-27 Control Accounts Control accounts are summary accounts that maintain totals for all subsidiary accounts of a particular type. Example: The balance for Accounts Receivable control account is the sum of the balances of all customer accounts.  7-28 Control Accounts Records for each control account are maintained in a company’s general ledger.  7-29 Accounting Software An accounting software is an integral part of the computerized accounting system. Need OF Accounting Software: a. When computerized system is replacing manual system. b. When computerized system already existing in the organization and it is to be altered according to the recent changes.  7-30 Types of Accounting softwares: a. Ready to use software b. Customized software c. Tailor-made software. Ready to use software: Which are available in market for users at large and not for specific users requirement. e.g. Tally, EX. Busy, etc.  7-31 Advantages Easy to learn Cost of installation is low Better after sale services Take care of problem area. Disadvantages: 1.These software use laser printers which are costly than Dot matrix. 2.Level of secrecy is very low. 3. Offer very little scope of linking with other information system. 4. Suitable for small organization.  7-32 Customized software: means making changes in the ready to use software so as to suit the specific requirements of the user. Advantages 1. Used by large and medium enterprises. 2. Level of secrecy is high. 3. Linkage to other information systems is available on the basis of need of the enterprise.  7-33 Disadvantage Cost of installation is high Training requirement of using this packages is high. Tailor-made Software: means developing a software according to the needs and specifications of the user. These softwares are not available off-the-shelf.  7-34 Advantages: Take care of the specific need of the enterprise. Level of secrecy of data and authenticity checks are robust in such soft wares. They can be effectively linked to some other information systems. Disadvantages: 1. Costly 2. Difficult to learn and require specialized training. 3. If the accounts are grouped in an incorrect manner, the results will be misleading.  7-35 Soft wares used in Business  7-36 Spreadsheets Widely used by finance departments to help manage cash flow, for bank reconciliations and in credit control. It is a type of program on a large sheet of paper. It contains rows and columns for entering character or numeric data. Rows are arranged horizontally, while columns are arranged vertically. The intersection of both produces a cell. A collection of cells is known as a range.  7-37 Utility of Spreadsheets in Accounting Payroll Accounting: It is used for calculating the salary of each employee. In the spreadsheet one row is used for each employee and there are column for basic pay, D.A. , HRA, other allowances, gross salary, deductions and the net amount payable. Recalculation if some values are changed. Graphic representation: it is represented in the form of graphs, charts, and diagrams. Depreciation accounting Budgeting and financial forecasts.  7-38 Electronic Spreadsheets Data is organized into columns and rows in a meaningful way to convey relationships and display information used for both simple and complex “number crunching.” The electronic spreadsheet is a calculating tool.  7-39 Electronic Spreadsheets One of its tremendous advantages is its ability to allow changes in data to affect the entire spreadsheet. Changes in one place are automatically applied to every cell dependent upon the cell changed. Using the manual accountant’s ledger, changes in one place had to be erased and often, this produced errors of omission.  7-40 Spreadsheet Advantages Time savings Creating charts Problem-solving Decision making. Real-world applicability  7-41 Other Softwares in Business Word Processor:-It helps the user to draft the letters, reports and other text material on a video screen with a facility to make changes very conveniently and as many times as desired. Database management. E.g. Software in accounting: General ledger, accounts receivables, accounts payables, Inventory control, Payroll accounting.  7-42 Describe how a database system can be used to create a simple accounting system.  7-43 Data are the facts that can be recorded and that have implied meaning. Data is a set of information which forms the basis of all important managerial decisions. All such information are compiled to produce some useful reports.  7-44 A database is a set of A database computerized files in management system which company data controls database are stored in the functions to ensure form that facilitates data are recorded retrieval and properly and are updating of the data. accessed only by authorized users.  7-45 Importance of DBMS in accounting System Data dictionary management: Any modification ,alteration, addition, deletion made in a database structure is automatically recorded in the data dictionary, hence there exists no need to modify all the programs. Data storage management: it provides the facility for the storage of various data and other related data entry forms, procedure, reports, etc. Security management: Secuity rules establish who shall access the database, which data can the user have access to, which operations (read, add, delete or modify) the user may perform.  7-46 Backup and recovery management: it ensures the data safety and integrity by providing adequate backup and data recovery procedures. Rapid updating of records: as well as related records i.e. with single entry we can simultaneously update all the records affected by the transactions.  7-47 THANK YOU Understanding Spreadsheets  What is it?: Spreadsheets Basics  Spreadsheet is a computerized ledger  Divided into Rows and Columns  Excel is a “spreadsheet” which holds different kinds of information  It performs calculations with mathematical and statistical functions  It presents your information in a variety of ways, with visually interesting charts and graphs  Constants - entries that do not change  Formulas - combination of constants and functions  Spreadsheet is generic term; Worksheet is an Excel term  Workbook contains one or more worksheets What is a spreadsheet? A program that displays data (text & numbers) in a table called a worksheet Why to Study it? Why to Study it? Uses of Spreadsheet Prepare budgets Financial statements Inventory management Create charts What is a worksheet? A grid with columns & rows; the term worksheet is used interchangeably with spreadsheet What is a cell? The intersection of a row and column  What 3 things can you type into a cell? Label = words or letters Value = numbers Formula – statement that performs a calculation What is a cell range? A group of cells that are closely together What are columns? Vertical arrangements of cells; identified by letters What are rows? Horizontal arrangement of cells; identified by numbers What is a worksheet tab? A tab that identifies each open worksheet in a spreadsheet program, located in the lower left corner of the screen  Excel 2003 vs. Excel 2007 256 Columns 16,384 Columns 65,536 Rows 1,048,576 Rows What is a Cell Address (Reference)? This is a column letter & row number combination, such as A1, B2 What is a name box? Displays the name of the active cell or range What is a formula bar? Displays the data or formula stored in the active cell What is an active cell? The cell in which you are currently working (normally the cell is surrounded by a black border) What is a function? A built-in formula that is a shortcut for common calculations such as addition and average. What are Operation Symbols? Instruct the computer as to what mathematical operations to perform Operation Symbols Addition (+) Subtraction (-) Multiplication (*) Division (/) How do you key a formula? You combine numbers, cell addresses (references), Operation symbols and/or functions Ex: =4 + 5; =B1 * B2 Order of Operations  Completes formulas in this order: 1. Parentheses 2. Exponents 3. Multiplication 4. Division 5. Addition 6. Subtraction Example: Formula: 5+2*7 Result: 19 Formula: (5+2)*7 Result: 49 What is a column width tool? Tool that changes the width of the columns on a spreadsheet What is a row height tool? Tool that changes the height of rows on a spreadsheet What is the Fill Handle Tool? Used to copy data & formulas to another cell; and create a series What is Auto Sum Tool? Automatically creates a formula to add a series of numbers in a spreadsheet What is a Chart? Graphic representations used to compare & contrast worksheet data What is sorting? Organizing or rearranging data in either ascending or descending order Definitions…. Sort Ascending – arranges records from A to Z or smallest to largest Sort Descending – arranges records from Z to A or largest to smallest Introduction to Microsoft Excel Business Computer Applications What is it?: Spreadsheets Basics Spreadsheet is a computerized ledger Divided into Rows and Columns Excel is a “spreadsheet” which holds different kinds of information It performs calculations with mathematical and statistical functions It presents your information in a variety of ways, with visually interesting charts and graphs Constants - entries that do not change Formulas - combination of constants and functions Spreadsheet is generic term; Worksheet is an Excel term Workbook contains one or more worksheets Why to Study it? Why to Study it? Absolute vs. Relative references Copying formulas does not always work This formula calculates first-quarter sales as a percentage of total year sales. Not surprisingly, it divides the number in cell B4 ($1,000) by the number in cell F4 ($4,100). However, when you pull the extension handle to copy the formula for Quarters 2,3, and 4, you don’t get the results you want. Press the Page Down key several times to see what happens. #DIV/0! means Excel is trying to tell you that you have asked it to divide by 0 -- an operation for which there is no answer. Why is this happening? Formula references To understand why this may be so, consider what the above formula is really saying. To Excel, B4/F4 actually translates as “when I’m sitting in cell B5, divide the number that is one row above me by the number that is four columns to my right and one row up.” In other words, the formula as written tells Excel to make a calculation relative to the cursor’s current position. Remember: the cursor always tells to Excel its current location (note B5 in the name box above). What you need to do for the formula to copy correctly is to tell Excel always to use the $4,100 (i.e., the contents of cell F4) -- no matter where you copy the formula. To do this, you need to make the reference to cell F4 an absolute reference rather than a relative reference. Use Page Down to see how to do this. Use the F4 key for absolute references The screen above shows the formula corrected so that it will give the desired results after it is copied. To convert a relative reference to an absolute reference while building a formula, press the F4 key immediately after clicking on the desired location with the cursor (in this case, the immediately after selecting the cell that has $4,100 in it). The formula changes to show a $ in front of both the column and the row for that location. You will notice that if you continue to press the F4 key, the command toggles to set the column, then the row, then both as absolute references. Trivia: Excel uses the $ to indicate absolute references because in the early days of spreadsheets PC keyboards had so few keys that software designers had to find multiple uses for each symbol. The company that first did this now out of business, but the $ stuck. Use the F4 key for absolute references This screen shows the result after you copy a formula that is correctly referenced. Use the Page Down key to walk through the steps. When you’re done, use Page Down to see a sequence that shows setting the absolute reference. Setting an absolute reference The F4 key has been pressed to right here to lock this location as an absolute reference. This time, all the copied formulas refer correctly to the $4,100 because that location has been set in the original formula as an absolute reference. You can check the results by looking at the copied formulas. They all refer to $F$4. Using Functions in Excel Use spreadsheets in decision making; use Goal Seek and Scenario Manager to evaluate multiple conditions Use financial functions (PMT, etc.) Use fill handle and AutoFill capability Use pointing to create a formula Statistical Functions — MAX, MIN, AVERAGE, COUNT Use functions over arithmetic expressions Decision making functions (IF and VLOOKUP (vertical lookup)) Excel Built-In Functions SUM AVERAGE SIN IF AND COUNT COUNTIF Many More … (look at Help and fx) SUM(number1, number 2,…) Example =SUM(3, 2) equals 5 If cells A2:E2 contain 5, 15, 30, 40, and 50: =SUM(A2:C2) equals 50 =SUM(B2:E2, 15) equals 150 AVERAGE(number 1, number 2,…) Examples If A1:A5 is named Scores and contains the numbers 10, 7, 9, 27, and 2, then: =AVERAGE(A1:A5) equals 11 =AVERAGE(Scores) equals 11 =SUM(A1:A5)/COUNT(A1:A5) equals 11 COUNT COUNT counts the number of cells that contain numbers & numbers within the list of arguments. Value 1, 2,…, are 1 to 30 arguments that can contain or refer to a variety of different types of data, but only numbers are counted. Ex., If cells A1:A17 contain some data, then =COUNT(A1:A17) equals 17 =COUNT(A6:A17) equals 12 COUNTIF(range,criteria) Counts the number of cells within a range that meet the given criteria. Suppose A3:A6 contain "apples", "oranges", "peaches", "apples", respectively: COUNTIF(A3:A6,"apples") equals 2 Suppose B3:B6 contain 32, 54, 75, 86, respectively: COUNTIF(B3:B6,">55") equals 2  =COUNTIF(A4:A130,1) (A4:A130,1) =D4/D9*100 (A4:A130,2) (A4:A130,3) (A4:A130,4) (A4:A130,5) Some Useful Functions IF TIME functions Conditional Functions Conditional functions allow the software to perform conditional tests and evaluate a condition in your worksheet. Depending on whether the condition is true or false, different values will be returned to the cells. =IF is the most important conditional function If =IF(condition, action if true, action if false) This tests the “condition” to determine if specific results or cell contents are true or false. If the result of the test is true, the “action if true” is executed. If the result is false, the “action if false” portion contains another set of instructions to execute. The instructions to be executed can return cell contents that are labels as well as values. Logical Operators To perform conditional tests, logical operators are required. = Equal < Less than > Greater than = Greater than or Equal to Not Equal Logical Functions And(logical1, logical2) Returns true if each condition is true Or(logical1, logical2) Returns true if either condition is true Not(logical) Returns true if the condition is false True() Always returns true False() Always returns false  Examples =IF(A5>20, B5, 0) means that if the value in A5 is greater than 20, use the value in B5. Otherwise assign the number 0. =IF(AND(B110,G11=1),10,0) means that if the value in B11 is not equal to 0 and the value in G11 is equal to 1, assign the number 10. Otherwise, assign the number 0. =IF(OR(E13=“Profit”,F15>G15),”Surplus”,”Deficit”) means that if either E13 contains the word “Profit” or the contents of F15 are greater than or equal to the contents of G15, assign the label “Surplus”. Otherwise, assign the label “Deficit”. VLOOKUP Function Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find. Syntax: =VLOOKUP(lookup_value,table_array, col_index_num,range_lookup) If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order:..., -2, -1, 0, 1, 2,..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted. VLOOKUP Function (cont’d) Example: On the preceding worksheet, where the range A4:C12 is named Range: VLOOKUP(1,Range,2) equals 2.17 VLOOKUP(1,Range,3,TRUE) equals 100 VLOOKUP(.746,Range,3,FALSE) equals 200 VLOOKUP(0.1,Range,2,TRUE) equals #N/A, because 0.1 is less than the smallest value in column A VLOOKUP(2,Range,2,TRUE) equals 1.71 Functions within functions You can use functions within functions. Consider the expression =ROUND(AVERAGE(A1:A100),1). This expression would first compute the average of all the values from cell A1 through A100 and then round that result to 1 digit to the right of the decimal point Open the Insert Function dialog box To get help from Excel to insert a function, first click the cell in which you wish to insert the function. Click the Insert Function button. This action will open the Insert Function dialog box. If you do not see the Insert Function button, you may need to select the appropriate toolbar or add the button to an existing toolbar. Examine the Insert Function dialog box This dialog box appears when you click the Insert Function button. It can assist you in defining your function. Circular Situation reference when some parameter in the formula refers to the formula itself For example, in cell C5 the following formula is entered: VLOOKUP(C5, F2:G15, 2, TRUE) The address C5 refers to the formula itself. In this case it is a wrong way of writing the formula. In some cases circular references may be used for creating recursive (recurrent) functions. Financial Function descriptions This chart shows some commonly used financial functions and a description of what they do. Excel's financial functions Financial functions are very useful to calculate information about loans. Common functions are FV, IPMT, PMT, PPMT and PV. All these financial functions will use similar arguments that differ based upon which function you are using. Think of the arguments as members of an equation The arguments represent the values of the equation that are known and the function provides the solution for a single variable, or unknown, value Use the financial functions The FV function calculates the future value of an investment based on periodic, constant payments and a constant interest rate per period. The IPMT function provides the interest payment portion of the overall periodic loan payment. The PMT function calculates the entire periodic payment of the loan. The PPMT function calculates just the principal payment portion of the overall periodic payment. The PV function calculates the present value of an investment. Use the financial functions (cont’d) NPER Determines the number of payments needed for an investment to grow or pay back a loan RATE Determines the effective interest rate Use the Insert Function dialog box to enter function arguments This figure depicts how you would enter argument values for the PMT function using the Insert Function dialog box. Annuity - Dictionary Definition An annual allowance or income; also, the right to receive such an allowance or the duty of paying it. (First definition in Britannica World Language edition of Funk & Wagnalls Standard Dictionary, 1966) We allow the payments to be more frequent than yearly Loans and annuities A typical loan is an annuity: Why? The borrower promises to pay a fixed amount every period. When we retire we want to set up a pension. We give a bank some money. In return the bank promises to pay us a fixed payment every month for a given number of years. We can treat this as a loan: We loaned the bank the money. The bank promises to pay us back with a regular payment.  Simple and Compound Interest Simple interest: Interest is not paid on interest Compound interest: Interest is paid on interest Compounding per year: Number of times interest is paid or charged each year Example of savings account Initial amt $2,000 Rate 10% Years 8 Simple interest Compound interest Year Interest Balance Interest Balance 0 $2,000 $2,000 1 $200 $2,200 $200 $2,200 2 $200 $2,400 $220 $2,420 3 $200 $2,600 $242 $2,662 4 $200 $2,800 $266 $2,928 5 $200 $3,000 $293 $3,221 6 $200 $3,200 $322 $3,543 7 $200 $3,400 $354 $3,897 8 $200 $3,600 $390 $4,287 Example of loan You borrow $2000 at 12% annual interest compounded monthly. What is your payment if you pay off the loan in 6 months? Interest rate (APR) 12% per year Years 0.5 Principal $2,000.00 Payments $345.10 Example: Repayment Schedule The repayment schedule Month Old balance Interest Payment New Balance 0 $2,000.00 1 $2,000.00 $20.00 $345.10 $1,674.90 2 $1,674.90 $16.75 $345.10 $1,346.56 3 $1,346.56 $13.47 $345.10 $1,014.92 4 $1,014.92 $10.15 $345.10 $679.98 5 $679.98 $6.80 $345.10 $341.68 6 $341.68 $3.42 $345.10 $0.00  Standard arguments rate: Interest rate (in decimal) per period nper: Number of periods pmt: Regular payment  More standard arguments pv: Present value: The amount the series of future payments is worth now. The beginning value. fv: Future value: The amount the series of future payments will be worth in the future. The final value. type 0 = payment at end of period (default) 1 = payment at beginning PMT() Payments Returns the periodic payment for an annuity or loan PMT(rate, nper, pv, fv, type) The first 3 (red) arguments are required Example : 12% interest compounded monthly, 3 years, borrow $6021.50 Monthly payment = -PMT(.12/12, 3*12, 6021.50) =200.00 PMT() Example Ms JustRetired has $200,000 to invest at 10% annual interest. Her goal is to have $100,000 left after 5 years. If she makes equal withdrawals each year for 5 years, how much can she withdraw each year? PMT(rate, nper, pv, fv, type) = PMT(0.10, 5, -200000, +100000) = $36,380 PV() Present Value Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. That is, it is the beginning value of the investment or loan. PV(rate, nper, pmt, fv, type) PV() Example A person promises to pay you $200 per month for 3 years. If you assume 12% interest compounded monthly, what is this annuity worth today? How much can you borrow at 12% annual interest compounded monthly and repay in 3 years paying $200 per month? = -PV(0.12/12, 3*12, 200) = 6021.50 FV( ) Future Value Returns the future (final) value of an investment. The future value is the total amount including interest that series of payments will be worth. How much money will there be in your account if you make regular payments for a period of time? FV(rate, nper, pmt, pv, type) FV( ) Examples You will make $200 a month payments into a 12% annual interest payable monthly account. How much will you have after 3 years? = -FV(0.12/12, 3*12, 200) = 8615.38 You will put $1000 in your account that pays 5% annually compounded monthly. You will add $100 to the account every month. How much will you have after 10 years? = -FV(0.05/12, 10*12, 100, 1000) = 17175.24 NPER( ) Number payments needed for an investment Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate NPER(rate, pmt, pv, fv, type) Example: Each month you put $200 into bank account. Assuming a 12% annual interest rate payable monthly, how many years will you need to save before you have $8615.38 in the account? = NPER( 0.01, 200, 0, -8615.38)/12 = 3 RATE() Effective interest rate Returns the interest rate per period of an annuity Gives the effective interest rate given the number of periods, the periodic payment, final value and initial value RATE(nper, pmt, pv, fv, type) Example: You bought $1,000 of shares in a mutual fund initially and then $100 more each month. After 10 years, your shares are worth $17,175.24. What was the effective interest rate? (Assume the interest is compounded monthly and paid at the end of the month.) =RATE(12*10, 100, 1000, -17175.24, 0)*12 The Mortgage Worksheet  Date Functions =Date coverts a date into a date number. Excel can represent any given date as a serial number equal to the number of days from Dec. 31, 1899 to the date in question. =DATE(year number, month number, day number) Enter the date into a cell (F5) with the =Date function. Then enter the Report date with the =Date function into another cell (L6). The difference between these dates (in number of days) can be calculated with the formula =L6-F5. Commonly used date functions Since dates are stored as integers, you can subtract one date from another to see how many days there are separating the two dates. The figure below provides additional details about the common date functions in Excel. The TODAY and NOW functions The TODAY and NOW functions always display the current date and time. You will not normally see the time portion unless you have formatted the cell to display it. If you use the TODAY or NOW function in a cell, the date in the cell is updated to reflect the current date and time of your computer each time you open the workbook. Custom functions Some functions can be implemented in Visual Basic. Gaining Proficiency: Formatting © Oleg Vlasov, KIMEP, Fall 2002 Excel Worksheet Modifying the Worksheet Insert command Modifying the Worksheet Delete command Modifying the Worksheet Page Setup command and dialog box Excel Formatting Column widths Row Heights Numeric Format Alignment Fonts Borders, Patterns, and Shading Types of Numeric Formats  General  Percentage  Number  Fraction  Currency  Scientific  Accounting  Text  Date  Special  Time  Custom Comments Right click on the cell & select Insert comment. You may edit or delete the comment by right clicking on the cell & selecting your choice. Y 790,343 695,034 263,448 Y 1,934,349 1,793,090 483,587 Tanya Goette: Y 2,103,049 2,001,304 420,610 This store looks N 1,785,323 1,593,032 357,065 really great! Format Cells Command