Bài giảng Management information systems - Module D: Decision Analysis with Spreadsheet Software

Extended Learning Module D (Office 2010 Version)Decision Analysis with Spreadsheet SoftwareINTRODUCTIONIT plays an important role in aiding decision makingSpreadsheet tools can aid in decision makingFilterConditional formattingPivot tables LISTSList – information arranged in columns and rowsEach column has one type of informationFirst row contains headings or labelsNo blank rowsBlank columns/rows all around LISTSA listTotal number of customersList Definition TableList definition table – description of a list by column (see Figure D.2 on pp. 388-389)CUST ID – Unique ID for customerREGION – North, South, etc.RENT VS. OWN – customer rents or owns a homeAnd so on BASIC FILTERFilter function – filters a list and hides rows that don’t match criteriaGood for seeing only certain rows of informationBasic Filter supports only “equal to” criteriaExample: customers in the North REGION Basic Filter StepsOpen workbook (XLMD_Customer.xls from www.mhhe.com/haag) Click in any cell in the listMenu bar – click on Data and then click on FilterWill see list box arrows next to each label or column heading Basic Filter StepsFrom the menu bar, click on Data and then click on FilterBasic Filter StepsEach column will have a pull-down arrow. Click on it and select the criteriaBasic Filter StepsTo select only customers in the North REGION, you must deselect all other regionsBasic Filter StepsExcel will respond by showing only those records that meet the selection criteria (i.e., North REGION)Turning off Basic FilterPerform either of the followingFrom the menu bar, click on Data and then FilterTurn off selected column filtering by clicking on the appropriate list arrow box and clicking on Clear Filter from “columnname” where columnname is the name of the columnBasic FilterCan also filter on multiple columnsExampleCustomers in North region (select North in REGION)Own a home (select Own in RENT VS. OWN)Only one household member (select 1 in NUM HOUSEHOLD) Basic FilterCustomers in the North REGION who own a home with only 1 household memberCUSTOM FILTERCustom Filter function – hides all rows except those that meet criteria, besides “is equal to”ExampleCustomers with more than 3 household members Custom Filter StepsTurn on FilterClick on pull-down arrow in appropriate columnClick on Number FiltersComplete Custom AutoFilter dialog box with criteriaClick on OK Custom Filter StepsTo use Custom AutoFilter, click on the appropriate pull-down arrow, select Number Filters and boolean operatorCustom Filter StepsYou will then see a Custom AutoFilter boxCustom Filter StepsEnter the appropriate value and click on OKCustom Filter StepsExcel will respond by presenting only the records that meet the selection criteriaAnother Custom Filter ExampleCustomers who spent less than $20 or more than $100 Another Custom Filter ExampleComplete both selection criteria appropriatelyAnother Custom Filter ExampleExcel will respond appropriatelyCONDITIONAL FORMATTINGConditional formatting – highlights the information in a cell that meets some criteriaDoes not hide any rowsLet’s you see the whole listWhile highlighting certain informationExampleCustomers show purchased more than $100 Conditional Formatting StepsSelect entire appropriate columnFrom menu bar, click on Home and then Conditional Formatting within StylesSelect Highlight Cells RulesClick on the appropriate boolean operatorComplete the dialog boxClick on OKConditional Formatting StepsFirst, highlight the appropriate columnConditional Formatting StepsFrom the menu bar, click on Home and then Conditional Formatting within Styles, then select Highlight Cells Rules and click on the appropriate boolean operatorConditional Formatting StepsIn the Conditional Formatting box, enter the appropriate value and click on OKConditional Formatting StepsExcel will highlight those cells meeting your selection criteriaRemoving Conditional FormattingOption #1 (click anywhere in the list)Click on Conditional FormattingSelect Clear RulesClick on Clear Rules from Entire SheetOption #2 (select the entire column)Click on Conditional FormattingSelect Clear RulesClick on Clear Rules from Selected CellsPIVOT TABLESPivot table – enables you to group and summarize informationShows summaries of information by dimensionCan be two-dimensionalCan be three-dimensionalSimilar to data warehouse concept from Chapter 3 Pivot Table ExampleThis is a pivot table2D Pivot Table StepsClick anywhere in listFrom menu bar, click on Insert and then PivotTableClick on OKDrag/drop labels in row and column fieldsProvide appropriate formatting 2D Pivot TableCount of customersBy REGIONBy RENT VS. OWN 2D Pivot Table StepsTo create a 2D pivot table, from the menu bar click on Insert and then PivotTable2D Pivot Table StepsClick on OK2D Pivot Table StepsPivot tablePivotTable Field ListSummary 2D Pivot Table StepsNow, drag and drop appropriate column headings (labels) from the Pivot Table Field List Box to the appropriate places in the pivot table summary areaExample: Number of customers byREGIONRENT VS. OWN 2D Pivot Table StepsDrag and drop REGION hereDrag and drop RENT VS. OWN hereDrag and drop CUST ID here2D Pivot Table StepsThe default aggregation for a pivot table is summing. To change that, click on the pull-down arrow next to Sum of CUST ID2D Pivot Table StepsYou will then see the Value Field Settings dialog box2D Pivot Table StepsTo change it, select another aggregation in the Summarize value field by box and click on OK2D Pivot Table StepsExcel will change the aggregation in the pivot tablePivot TablesCan have multiple pieces of information in the body of the pivot tableExampleCount of customers (already present)Total of purchases (new information)Drag/drop TOTAL PURCHASES into Values in lower right portion of screenPivot Tables3D Pivot TablesDesired dimensionsREGIONRENT VS. OWNNUM HOUSEHOLDDrag/drop NUM HOUSEHOLD into lower right portion of the screen called “Report Filter” 3D Pivot TablesDepth dimension3D Pivot TablesGOAL SEEKGoal Seek works backward from objective to compute an unknown valueScenario (movie posters from Chapter 1):Buy poster for $4 and sell for $9$2 shipping for each posterFixed costs of $1,500 per yearWhat is the breakeven point?GOAL SEEK – BREAKEVEN POINTDownload XLMD_BreakEven.xlsC14 = C13*C7C15 = C13*C8C16 = C13*C9C18 = C14 – C15 – C16 – C17Now, for a given net profit, we want Excel to compute Units Sold (C13)GOAL SEEK – BREAKEVEN POINTFrom menu bar, click on Data and What-If AnalysisSelect Goal SeekFor Set cell, enter C18For To value, enter 30000For By changing cell, enter C13Click on OKGOAL SEEK – BREAKEVEN POINTGOAL SEEK – BREAKEVEN POINTSell 10,500 units to achieve a profit of $30,000