CGS 2518 Final Study Guide Chapter 6 1 L 2 K 3 C 4 I 5 F 6 P 7 N 8 A 9 O 10 J 11 D 12 B 13 G 14 E 15 FV 0 05 4 20 200 6000 16 RATE 30 12 3000 375000 0 12 17 NPER 0 06 12 370 20500 0 12 18 PMT 0 05 12 15 12 100000 5000 19 PV 0 03 4 3 4 0 15000 20 SLN 250000 10000 10 Chapter 7 1 Copy and paste the data from the text file and paste it into a new workbook Each line appears in a separate row Change data to comma separated values You can now use sorting tools in excel You can now use the concatenate function to combine values in a range of cells into one text item in a new cell 2 Because they can cause errors 3 The find Function returns the starting position of one text value within another text value It is case sensitive The search function does the same thing as find but it is not case sensitive 4 The first way is to identify the character that delimits separates the delimiter Another common delimiter is the tab character The second way to parse data is to set field widths to identify the breaks between data that appears in columns Use the fixed width method when the data to parse does not include consistent character to separate the field values 5 Subtotal command creates summary reports that quickly organize data into categories with subtotal calculations and lets you collapse and expand the level of detail in the report It is not available if you are working with an excel table One limitation of the subtotal command is that it works only with one category and one subtotal calculation at a time However you can change the category by opening the dialog box again to change Use Function and Add subtotal to values 6 7 8 In a database the data is protected from accidental changes yet it is available for export into a spreadsheet for analysis Database programs are also able to hold much larger and more complex data sets than excel Many people use excel as a quick and dirty database because it is easy to enter data that might be better suited for a database in a workbook 9 Open the workbook into which you want to import the database data or open a new workbook Select the first blank cell where you want the data to appear and then click the From Access button in the Get External Data group on the Data tab on the Ribbon Then Select Data Source when the dialog box opens Navigate to the database from which you will import the data and then click the Open button If the database you select contains more than one table select Table dialog box opens Click that table that contains the data you want to import and then click the OK button The Import Data dialog box opens 10 Query Wizard lets you choose you data source and select the database table and fields you want to import into a workbook The query Wizard prompts you to define any criteria for the data you want to import by selecting only rows that meet criteria you specify 11 Excel uses serial numbers to save dates and times January 1 1990 is represented as 1 January 2 1990 is represented as 2 Time is stored as decimals Noon is 5 So twelve noon January 1 1990 is 1 5 12 In the yearfrac function the start date is the date from which you want to start counting the end date is the ending date and the basis is the argument that lets you establish the length of months and years Using 1 for the basis calculates months and years with actual values Using a 0 as the basis calculates months using 30 days and years using 360 days Some people consider a standard month to be 30 days even though a month can have 28 days to 31 days except for leap years which have 366 days 13 You can easily rearrange its fields and change its data to analyze data from different perspectives The ability to pivot summaries of the data by rows and columns is what gives this tool its name Pivot tables are best used to analyze data that can be summarized multiple ways 14 The Values are where you place the fields you want to summarize this area usually includes field values rather than categories When you place fields in either the Row labels area or the Column label area the Pivot Table report displays all values of those fields The Report Filter area you can display data in the Pivot Table Field report ungrouped by the equivalent pages 15 Major Indicies and Stock quotes 16 HTML or Hypertext Markup language is the markup language that creates Web pages also called HTML documents In HTML you embed tags in the documents to describe how to format its content When a web browser reads an HTML document it uses the tags to format text according to a set of predefined descriptions of those tags Example b and b is used to define bold text XML is the acronym for Extensible Markup language It is a fairly recent innovation created through the efforts of the World Wide Web Consortium It was designed to combine the markup power of SGML with the ease of use of HTML The result is a language that defines the structure and rules for creating markup elements In a way it just provides grammatical rules and the alphabet 17 The Xml document must be created in Notepad first It must define the data and include at least two records XML files with repeating elements are imported into Excel as an XML table format and the element names appear as column headings Those with nonrepeating elements are imported as individual cells 18 Chapter 8 1 Break even analysis is a type of what if analysis that concentrates on an activity at or around the point at which a product breaks even or the point at which expenses of creating and selling a product equals the revenue it produces Sensitivity analysis is another type of what if analysis that attempts to examine how sensitive the results of an analysis are to changes in assumptions 2 A data table is a range of cells containing values and formulas When you change the values the data table shows you how those changes affect the results from the formulas Data tables allow you to organize and present the results of multiple what If analyses 3 One variable data table allows you to compare results calculated from changes made to one input value In a two variable data table you can vary the results of two input variables but show the results for only one input value 4 5 The Row input values and the Column input values that indirectly refer to the formula in the top corner 6 You should use the scenario manager to describe …
View Full Document
Unlocking...