Class Notes (834,627)
Canada (508,661)
Administration (2,694)
ADM1370 (108)
Umar Ruhi (38)
Lecture

Module 2 Notes ADM1370

17 Pages
280 Views
Unlock Document

Department
Administration
Course
ADM1370
Professor
Umar Ruhi
Semester
Winter

Description
Lecture 9 Why are we learning Excel? Why Learn Excel?  Excel is the most popular spreadsheet tool today.  Excel has been regarded as the true “killer app” by many experts.  You probably already use or will soon be using Excel at work, and possibly at home.  Excel can be used for simple data management and problem-solving, as well as complex decision making  From a learning standpoint: excel can be used to cultivate Critical Thinking and Decision Making Skills Based on Robert Reich (former US Secretary of Labour):  Four Skills required for future job roles:  A good information Systems education is comprehensive across these skills Introduction to Microsoft Excel Microsoft Office Excel 2010 (Excel):  A computer program used to enter, analyze, and present (quantitative) data   A spreadsheet program  A spreadsheet is a collection of text and numbers laid out in a rectangular grid.  Often used in business for accounting, budgeting, financial analysis, inventory management and other functions. An electronic spreadsheet program such as Excel aids a multitude of problem- solving & decision-making processes through providing:  data management features  automatic calculation functions  presentation tools  decision analysis functions Worksheets and Workbooks Excel  a computerized spreadsheet application used to build and manipulate worksheets and workbooks Worksheet  a spreadsheet that may contain data including text, numbers, formulas, charts etc.  Sometimes a charts based worksheet is referred to as a “Chartsheet” Workbook  a collection of related worksheets within one file ADM1370 QUIZ #2 1 Basic Components of the Excel Interface File Tab:  Consists of file operations commands such as opening, closing, saving, printing, and sharing files.  The options and features available here are part of the “Backstage view” Tabs & Ribbon:  Each tab corresponds to sets of features displayed horizontally as a ribbon.  A ribbon consists of groupings, and controls.  Tabs are designed to be task-oriented and consist of several logical groupings of controls that perform similar functions. Quick Access Toolbar:  Contains controls / commands that are most commonly used. Additional controls can be added through Excel Options settings through the Office Button. Select All Button:  Used to select all elements of the worksheet. Status Bar:  Displays information about a selected command or operation in progress.  Also displays basic summary information about a selected range of values. HANDY HACK  You can customize the status bar to show the types of summary information you are interested in.  Just right-click the status bar and select the options you wish to use. Planning for Good Workbook and Worksheet Design Plan before you start entering data  Decide on the purpose of the spreadsheet and how it will be constructed  Make it obvious where data is to be entered:  Use titles, headings, instructions, color schemes, and forms to designate areas for data-entry.  Wherever possible, setup formulas and use cell references for calculations  Allow Excel to do what it was designed for, i.e., automatic calculations o Try to minimize hard-coded numbers in your calculations. Test multiple times to make sure the results are what you expect:  Check your results against your mental model of what the results should be. Format the worksheet so it looks aesthetically pleasing. Document the worksheet as thoroughly as possible:  Provide summaries or comments in a separate worksheet including ranges being used for calculations and analysis. Save your work regularly:  Modify the default save intervals if necessary. Cells, Ranges, and References Each cell is referenced by its intersecting column (letter) and row (number): 2 ADM1370 QUIZ #2  e.g. cell A1 is the topmost left cell in the spreadsheet.  The cell reference is shown in the name box beside the formula bar. A range is a rectangular group of cells in a worksheet:  can be one single cell or may be entire worksheet. Selecting a range:  Click and hold left mouse button and drag from beginning of range to end  Select first cell, then hold the Shift key while clicking the last cell Can consist of contiguous (together) or non-contiguous (not together) cells:  Hold the Ctrl key to select non-contiguous ranges. A contiguous range of cells is referenced by the top left cell reference and the bottom right cell reference:  e.g. B2:D5 Entering & Editing Data Entering Data in Cells:  Text, Numbers, Formulas can be entered either directly in each cell or through the formula bar. Editing Data in Cells:  Data in cells can be modified using one of three ways:  Selecting the cell you want to edit, clicking the Formula Bar, making changes, pressing Enter or the Check symbol.  Double clicking the cell to be edited, making the changes, pressing Enter  Selecting the cell, pressing the F2 key, making the changes, pressing Enter Auto-Fill Feature:  Used to repetitively copy contents of one cell or a range of cells.  Used to complete a sequence, pattern or lists like numbers, increments, months  Enables you to copy or continue the content of a cell or a range of cells to its adjacent cells (below or to the right).  Select a cell or range of cells to be copied or continued,  Drag the “fill handle” over an adjacent cell or range of cells  The fill handle is a small black square appearing in the bottom-right corner of a cell HANDY HACK:  You can define your own custom lists in Excel to give more power to the Auto-Fill feature.  Use the Excel Options control in the backstage view and look under Advanced  Edit Custom Lists. Using Formulas Formulas are used to perform operations and arrive at a calculated result.  A formula is an expression that returns a value through performing operations on literal values specified in the formula itself or referenced values from other cells Must begin with an equals (=) sign. ADM1370 QUIZ #2 3 Formulas mostly contain mathematical operators. Used to automate calculations that were once done manually. Rather than typing a cell address, use an alternative method that involves minimal typing:  Pointing uses the mouse or arrow keys to select the cell directly when creating a formula. Auto-Fill feature can also be used to duplicate formulas:  Formula references are updated according to the target cell for the formula results. What are they?  A formula is an expression that returns a value through performing operations on literal values specified in the formula itself or referenced values from other cells  The most commonly used operators are arithmetic operators Complex Formulas:  Formulas follow “order of precedence” (BEDMAS)  A set of predefined rules used to determine the sequence in which operators are applied in a calculation Copying and Pasting Formulas  With formulas, Excel adjusts the formula’s cell references to reflect the new location of the formula in the worksheet To view formulas in a workbook, switch to formula view:  press the Ctrl and ~ keys  A view of the workbook contents that displays formulas instead of the resulting values Using Functions What are they?  A function is a named operation (predefined formula) that returns a value.  A function take values (literal values or referenced values), performs operations, and returns results:  The values are passed to the function as parameters enclosed in brackets.  For example, to add the values in the range A1:A5, You could enter the following long formula: =A1+A2+A3+A4+A5 OR You could use the SUM function to accomplish the same thing by entering: =SUM(A1,A2,A3,A4,A5) =SUM(A1:A5) Creating Effective Formulas:  Do not embed important data in a formula (rather, put that data in a separate cell)  Using referenced values is better than using literal values  This prevents hiding important data  This prevents easily changing important data without the need to edit formulas 4 ADM1370 QUIZ #2  Keep formulas simple  Sometimes it’s useful to break formulas into intermediate results Ranges and Excel Tables A range of cells with column headings (arranged as a flat table) is a good starting point for analysis of data in the range. An Excel Table can enhance the presentation and facilitate sorting and filtering of data in the ranges. Steps to Convert a Range into an Excel Table:  Make sure the range is arranged as a flat table with column headings.  Click anywhere inside the range, and click the Table button in the Insert tab.  Dropdown handles with every column heading allow the use of sorting and filtering functions. Steps to Convert an Excel Table back into a Range:  Click anywhere in the excel table.  Select: Table Tools  Design  Convert to Range (Table Tools) Sorting and Filtering  Sort data in ascending or descending order  Filter data to display the portion that meets the criteria specified  Data can be sorted or filtered by selecting the sorting arrow filtering arrow. ADM1370 QUIZ #2 5 Lecture 10 Relative & Absolute Cell References Relative Cell References:  Cell addresses that are adjusted as a formula containing these addresses is copied within the spreadsheet  Used by default in most spreadsheet calculations  e.g. B6 copied over two columns to the right will become D6  e.g. K12 copied over one column to the left and one row above will become J11 Absolute Cell References:  Cell addresses that stay fixed & are not adjusted as a formula is copied  Used when the formulas should always use the value in a particular cell  Used for constant values (e.g. sales tax rate etc.)  e.g. $B$6 Mixed Cell References:  Cell addresses where either the row or the column is fixed  e.g. $B6 refers to a cell address where the column is fixed to B but the row can increase or decrease as the formula is copied vertically  e.g. J$11 refers to a cell address where the column can vary as the formula is copied horizontally but the row is fixed to 11. Data Delivery Functions in Excel Data Delivery functions do not perform calculations per say:  These functions can be used to verify data, search for data, transform data or deliver data from one point to another. e.g. Filter Unique Values:  Used to filter and deliver unique values from a list e.g. Data Validation & Drop-Down Menus:  Used to allow end-users to select from a predefined lists of values e.g. Lookup Functions:  Used to retrieve values from tables based on lookup values Using Lookup Tables and Functions A lookup table is a table that organizes data you want to retrieve into different categories The categories for the lookup table, called compare values, are located in the table’s first column or row To retrieve a particular value from the table, a lookup value (the value you are trying to find) needs to match the compare values  =VLOOKUP(lookup_value,table_array,col_index_num[,range_lookup])  =HLOOKUP(lookup_value,table_array,row_index_num[,range_lookup])  The last parameter (shown in square brackets) is optional:  By default, it is assumed that you need a closest match to the compare value  Set this parameter to FALSE if you need an exact match to the compare value. 6 ADM1370 QUIZ #2 Lecture 11 More Examples of Functions: Statistical Functions  =AVERAGE(number1,number2,..)  Excel sums the values in the range and then divides by the number of non-blank cells in the range  =STDEV(number1,number2,..)  =MAX(number1,number2,..)  =MEDIAN(number1,number2,..)  =PEARSON(number1,number2,..)  Pearson correlation coefficient Counting Functions  =COUNT(range)  Counts the number of cells that contains numbers  =COUNTA(range)  Counts the number of cells that are not empty  Both numeric and text entries are included  =COUNTBLANK(range)  Counts the number of empty cells  =COUNTIF(range,criteria)  Counts the number of cells within a range that meets the condition Summarizing Data Through Subtotals & Grouping Subtotals:  Excel provides a number of features that enable you to organize large groups of data into more manageable groups.  Data in a list can be summarized by inserting a subtotal.  Before you can subtotal, however, you must first sort the list by the field on which you want the list subtotaled. Grouping:  If you have a list of data that you want to group and summarize, you can create an outline.  Grouping refers to organizing data so that it can be viewed as a collapsible and expandable outline.  To group data, each column must have a label in the first row and the column must contain similar facts. The data must be sorted by the column or columns for that group. Problem-Solving & Modeling Process Phases in Problem-Solving & Modeling Process:  Formulation  Transformation of a real problem scenario into a mathematical model ADM1370 QUIZ #2 7  Solution  Solving the model to obtain the optimal solution  Interpretation  Analyzing results and implementing solution Modeling Approach to Problem-Solving Models are usually simplified versions of the things they represent:  a valid model accurately represents the relevant characteristics of the object or decision being studied Types of models:  Mental (e.g. arranging furniture)  Visual (e.g. blueprints, road maps)  Physical/Scale (e.g. aerodynamics, buildings)  Mathematical (e.g. financial analysis) A Computer Model:  A set of mathematical relationships and logical assumptions implemented in a computer as an abstract representation of a real-world object or phenomenon.  Spreadsheets provide the most convenient way for business people to build computer models. Benefits of the Modeling Approach:  Economy:  It is often less costly to analyze decision problems using models.  Timeliness:  Models often deliver needed information more quickly than their real- world counterparts.  Feasibility:  Models can be used to do things that would be impossible.  Models give us insight & understanding that improves decision making. Example of a Mathematical Model:  Specifically: Profit = Revenue – Expenses Or Profit = f (Revenue, Expenses) Or Y = f (1 ,2X )  Generically: Y = f(X 1 X 2…,X n Y = dependent variable Xi= independent variables (inputs having an impact on Y) f() = function defining the relationship between the X & Y i  Most spreadsheet models are very similar to the generic mathematical model: = f(X 1 X 2…,X )n  Most spreadsheets have input cells (representing X) toiwhich mathematical functions ( f()) are applied to compute an output variable (or Y). 8 ADM1370 QUIZ #2 Examples of Functions Financial/Annuity Functions  An annuity is a series of constant cash payments made over a continuous period  A car loan or a mortgage is an annuity  Benefits received each period is an annuity  Cash you pay out is represented by a negative number; cash you receive is represented by a positive number  A $1,000 deposit to the bank would be represented by the argument - 1000 if you are the depositor  A $1,000 deposit to the bank would be represented by +1000 if you are the bank Future Value Function: Syntax  =FV(rate,nper,pmt,pv,type)  Rate  Interest rate per period – e.g. 8 percent annual interest rate  Nper  Total number of payment periods in an annuity – e.g. 10 years  Enter 10 into the formula for nper  Pmt  Payment made each period  Cannot change over the life of the annuity  Pv  (optional) present value or the lump-sum amount that a series of future payments is worth right now  Type  (optional) the number 0 or 1 indicates when payments are due. If type is omitted, it is assumed to
More Less

Related notes for ADM1370

Log In


OR

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


OR

By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.


Submit