CGS2518 FINAL EXAM REVIEW Chapters 6 7 8 9 Chapter 6 RATE Interest rate per period NPER Number of periods PMT Value of payment per period Compound Interest The interest calculated by including any previously earned owed interest CUMIMPT Calculates the accumulated value of the interest payments of a financial transaction between two specific periods IRR Calculates internal rate of return at which discounted cash flows in and out are equal ROI Return on Investment The ration of profits to initial investment CUMPRINC Calculates the accumulated value of the principal portion of the period payments of a financial transaction between two specified periods PPMT Calculates the value of the principal portion of a loan payment based on the original loan amount periodic interest rate and payment number Simple Interest Interest calculated without including any previously earned owed interest SNL Calculates straight line depreciation IPMT Calculates the value of the interest portion of a loan payment based on the original loan amount periodic interest rate and payment number Payback Period Number of periods years it will take to pay back the original investment DDB Calculates double declining balance depreciation SYD Calculates sum of the years digits depreciation NPV Evaluates the hurdle rate and the series of cash flow values and automatically calculates the discounted value Chapter 7 CONCATENATE Used to combine up to 255 strings into one text string FIND Returns the starting position of one text value within another text value it is case sensitive LEFT Extracts characters from the beginning or left of a text string RIGHT Returns the final character s in a text string SEARCH Returns the starting position of one text value within another text value in the same manner as the FIND but SEARCH isn t case sensitive TODAY Returns the current date s serial number based on the internal clock TRIM Removes all the spaces in a text string except for single spaces between words YEARFRAC Calculates the number of years between two dates Subtotal Command Allows you to create summary reports that quickly organize data with subtotals and lets you collapse and expand the level of detail in the report XML Extensible Markup Language defines the structure and rules for creating markup elements Chapter 8 Date Tables A range of cells containing values formulas When you change the values the data table shows you how those changes affect the results of the formulas RAND Generates a random number real number between 0 1 Scenario Manager A tool that allows you to create edit and summarize scenario models STDEV S Estimates standard deviation based on a sample ignoring logical values and text SUMPRODUCT Used to sum a series of products VLOOKUP Looks up a value stored in the first column of a vertical lookup table and retrieves data stored in the same row of a subsequent column One Variable Data Table Has only one input cell and can have many result cells i e the effect of different interest rates on a loan payment Two Variable Data Table Has two input cells but only one result cell i e effect of different interest rates and loan terms affect a loan payment Defined Name Range Name Can refer to a cell a range of cells formula or a constant value Chapter 9 Goal Seek Determines the value of a single input to obtain a specified output Solver Add In program used for optimization You can set up complex business models in a worksheet and use the Solver to determine the optimal set of decision inputs to meet an objective such as minimizing costs or maximizing profits Because you can use Solver to answer very complex questions about allocation and optimization it is an excellent tool for determining the best way to apply resources to a particular problem Chapter 7 Questions 1 Steps to create a structured list of data from a text file that contains values stored on separate lines Copy and paste text file into Excel and then use the CONCATENATE function 2 Why remove unnecessary spaces from data imported from another source How do you do this Use the TRIM function to remove spaces so that the data can be sorted without bringing up errors 4 What options are available for parsing data when you use the Convert Text to Columns Wizard Identify the character that delimits or separates the data i e comma semicolon tab etc or you can set field widths to identify breaks between data i e column 1 is characters 1 10 and column 2 is characters 11 20 5 Advantages and Disadvantages of Subtotal Command Creates an outline on the left side of the worksheet that allows you to expand or collapse the groups It only works with one category and one subtotal calculation at a time 6 Difference between an Excel Table and an Unstructured Table An Excel Table is a range of cells that you formalize as a single unit of data 7 The AutoFilter Options A Sort A to Z or Z to A B Sort by Color background or font color C Clear Filter From removes any existing filters and displays all data D Text Filters filters alphanumeric text by specific characters E Number Filters filters numeric values using comparison operators D Date Filters filters date and time values using comparison operators 9 To import from Access to Excel Data Ribbon From Other Sources Access Use Query Wizard to Select Information Desired Set Filters in Query Wizard Click Finish Verify 11 How does Excel store Date and Time Values Excel uses serial numbers to represent dates and times 12 Explain YEARFRAC function Calculates number of years between two dates Using a 1 for the basis calculates months and years with actual values Using 0 for the base uses a 30 day month to calculate 13 When should you use a PivotTable report When attempting to analyze data that can come from a variety of sources i e Analyzing sales data by month quarter year product line and or sales region 14 The four areas of a PivotTable Report A Report Filters B Column Labels C Row Labels D Values 15 Other than currency what types of Web queries are useful Investor indexes stock quotes 16 XML vs HTML XML are user defined documents in which the user develops a DTD or schema that defines the elements contained in the document and descriptions of how those elements are related In HTML you embed tags in the document to describe how to format its content 17 Import an XML Document as an XML Table Select a Blank Cell From Other Sources From XML Data Import Select the XML Document Click Open Click OK Click the Options Button and Select a
View Full Document
Unlocking...