CGS2518 FINAL EXAM REVIEWChapters 6, 7, 8, & 9Chapter 6RATE – 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 7CONCATENATE – 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 inthe 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 8Date 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 formulasRAND – 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 9Goal 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. Becauseyou 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 Questions1. 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 orcollapse 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 AB) 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 > Verify11. 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 FiltersB) Column LabelsC) Row LabelsD) Values15. 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
View Full Document
Unlocking...