MIS171 Tutorial 1 - Students copy PDF

Preview

Summary

Lecture 1 - mis171 lecture notes from 2021...

Description

BUSINESS ANALYTICS BUSINE SS A NALYTICS WEEK 1

Introdu Introduction ction to Exc Excel el Part 1: DeskPlace Pty Ltd case The DeskPlace Pty Ltd case will be the main seminar exercise during the trimester. Please make sure you read the case thoroughly before coming to the seminar.

Part 2: An introduction to Excel 1. Why Spreadsheets? •

Many commercial software packages can be used for Business Analytics.

•

Spreadsheet software, such as Microsoft Excel, is widely available and used across all areas of business.

•

Spreadsheets provide a flexible modelling environment for manipulating data and developing and solving models.

2. Windows vs. Mac •

In many cases Excel functionality in Mac versions is the same as Windows (PC) versions. However, there are also differences in approach. In this unit we will use the Windows version of Excel for classes and seminars.

•

The unit Discussion Board will be a good place to post any questions you may have about performing Excel functions on a Mac. There are also many sources of information on the Internet which provide guidance for completing Excel functions on a Mac. If you have a Mac version of Excel, please use the Discussion Board to share your knowledge of performing specific functions with other students.

•

While Mac versions of Excel do not have the full functionality of Windows versions, including some statistical features, the key functions covered in this unit can be performed by Mac versions of Excel. DEAKIN UNIVERSITY CRICOS PROVIDER CODE: 00113B | Unit Chair: Dr Reza Kachouie

MIS171 nalytics MIS 171 – Business Analyt ics •

All students enrolled at Deakin are entitled to a free copy of the Microsoft Office suite of applications. To find out more, in your internet browser search for “How do I get Microsoft Office for free through Deakin University”, or: https://support.deakin.edu.au/kb_view_customer.do?sys_kb_id=cba3b0ded0b6d90008800ceda200d9df

•

The student IT support desk can help if you are having trouble accessing the software.

•

While the unit team will take all reasonable steps to support students it is each student’s responsibility to find a pathway to build the Excel skills necessary for this unit. Given the widespread use of Excel in business, there is a substantial benefit from being able to use Excel on either a PC or Mac environment.

3. Understanding the Dataset To perform any statistical analysis in Excel, the data must be organized as a dataset or data matrix. See figure below (DeskPlace, Employees survey sheet). Note the following: •

Rows: Each row holds a complete record of an employee. In this case, an employee’s position, department, age, job satisfaction etc.

•

Columns: Each column represents one attribute or one variable. For example, DysAbsnt column holds an employee’s absent days as taken as personal leave.

•

Headings: To identify each variable, it is usual to place a heading at the top of each column. Sometimes row headers are also included to identify individual records.

•

Data: The actual data is located in the body of the table

Heading

Re Record cord

Data

Variable Page 2 of 6

MIS171 nalytics MIS 171 – Business Analyt ics 4. Basic Excel Skills •

Opening, saving, and printing files

•

Using workbooks and worksheets

•

Moving around a spreadsheet

•

Selecting cells and ranges

•

Inserting/deleting rows and columns

•

Entering and editing text, data, and formulas

•

Formatting data (number, currency, decimal)

•

Working with text strings

•

Formatting data and text

•

Modifying the appearance of a spreadsheet

5. Excel 2016 Ribbon •

Tabs - Home, Insert, Page Layout, Formulas, …

•

Groups - Font, Alignment, Number, Styles, …

•

Buttons and Menus o Buttons appear as small icons. o Menus of additional choices are indicated by small triangles.

6. Excel Formulas Common mathematical operators are used. For example: a − bP5 + c/d would be entered into Excel as:

Page 3 of 6

MIS171 nalytics MIS 171 – Business Analyt ics 7. Relative and Absolute References •

In Excel, cell references can be either relative or absolute. A cell with a relative reference changes its formula when copied elsewhere. A cell with an absolute reference does not change even if copied elsewhere.

•

Using a dollar sign ($) before a row and/or column label creates an absolute reference.

•

Relative references: A2, C5, D10

•

Absolute references: o Using a $ sign before a column label (for example, $A2) keeps the reference to column A fixed but allows the row reference to change (e.g. when the cell formula is copied to another to another cell in the spreadsheet). o Using a $ sign before a row label (for example, C$5) keeps the reference fixed to row 5 but allows the column reference (e.g. if a new row is inserted into the spreadsheet). o Using a $ sign before both the row and column labels (for example, $D$10) keeps the reference to cell D10 fixed no matter where the formula is copied. The F4 function on Excel allows a user to toggle through the different absolute cell reference options.

8. Copying Formulas Formulas in cells can be copied in many ways. •

Use the Copy button in the Home tab, then use the Paste button

•

Use Ctrl-C (to copy), and then Ctrl-V (to paste)

•

Hover the cursor over the bottom right corner of a cell until the cursor adopts the “crosshairs” shape (the fill handle). Than click-and-drag the cursor across a row or column (or both).

9. Other Useful Excel Tips •

Freeze columns and/or rows

•

Split Screen

•

Paste Special

•

Column and Row Widths

•

Editing and Displaying Formulas in Worksheets

•

Displaying Grid Lines and Column Headers for Printing

•

Filling a Range with a Series of Numbers Page 4 of 6

MIS171 nalytics MIS 171 – Business Analyt ics 10. Basic Excel Functions •

=MIN (range)

•

=MAX (range)

•

=SUM (range)

•

=AVERAGE (range)

•

=COUNT (range)

•

=COUNTIF (range, criteria)

•

=COUNTIFS(range1, criterion1, range2, criterion2,… range n, criterion n)

•

Other IF-Type Functions o SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS can be used to embed IF logic within mathematical functions. o For instance, the syntax of SUMIF is SUMIF (range, criterion, [sum range]). "Sum range" is an optional argument that allows you to add cells in a different range.

11. Logical Functions •

=IF (condition, value if true, value if false) – returns one value if the condition is true and another if the condition is false. o Conditions may include the following: 

= equal

e.g. =if(A5=10,…)



not equal to

e.g. =if(A510,…)



> greater than

e.g. =if(A5>10,…)



>= greater than or equal to

e.g. =if(A5>=10,…)



< less than

e.g. =if(A5...