CGS 2518 Gower-Winter Final Review CH 6-9 1 Chapter 6 Conceptual Review 1. Function to calculate the interest percentage per period of a financial transaction. a. RATE 2. Function to calculate the value at the beginning of a financial transaction. a. PV 3. Function to calculate the value at the end of a financial transaction. a. FV 4. Function to calculate periodic payments into or out of a financial transaction. a. PMT 5. Function to calculate the number of compounding periods in a financial transaction. a. NPER 6. Use a 1 for this argument to indicate that interest will be paid at the beginning of each compounding period. a. Type 7. This type of interest is calculated based on original principal regardless of the pervious interest earned. a. Simple Interest 8. This type of interest is calculated based on principal and previous interest earned. a. Compound Interest 9. Function to calculate straight line depreciation based on the initial capital investment, number of years to be depreciated, and salvage value. a. SLNCGS 2518 Gower-Winter Final Review CH 6-9 2 10. Function to calculate the amount of a specific periodic payment that is principal in a given period. a. PPMT 11. Function to calculate the amount of a periodic payment that is interest in a given period. a. IPMT 12. Function to calculate the cumulative principal paid between two periods. a. CUMPRINCE 13. Function to determine the value of a variable set of cash flows discounted to its present value. a. NPV 14. Function to determine the rate of return, where the net present value of the cash flows is 0. a. IRR 15. =FV(rate,nper,pmt,pv,type) is used this way…. =FV(5.0%/4, 5*4, -200, -6000, 0) 16. =RATE(nper,pmt,pv,fv,type) this way… =RATE(30*12,-3000,375000,0,0) 17. =NPER(rate,pmt,pv,fv,type) this way… =NPER(6.0%/12,-370,-20500,0) 18. =PMT(rate,nper,pv,fv,type) this way… =PMT(5.0%/12,15*12,100000,-5000) 19. =PV(rate,nper,pmt,fv,type) this way… =PV(3.0%/4, 3*4, 0, 15000) 20. = =-SLN(cost,salvage,life) this way… =-SLN(250000,10000,10)CGS 2518 Gower-Winter Final Review CH 6-9 3 Chapter 7 Conceptual Review 1. Listed steps a. Determine format needed (most likely Comma-separated values - CSV) b. Copy data from file and paste into workbook c. In the column next to the data, use: CONCATENATE(A1,“,”,A2,“,”,A3,“,”,A4,“,”,A5,“,”,A6) to merge data into one cell. By inserting “,” the data in the CONCATENATE function will be separated by commas. d. In an adjacent cell use the RIGHT function to sort through the data to sort the data using a common text. In this case the data would be sorted out by a dash followed by the last four numbers of the phone number. The function would look as follows RIGHT (cell selected, 5). e. To ensure the right function returns the values desired, next a TRIM function into the concatenate function in order to remove unnecessary spaces in each value. CONCATENATE(TRIM(A1),”,”,TRIM(A2),”,”,TRIM(A3)….)) f. Use the FIND function in order to sort through the data. The function FIND(cell selected, “-“) will search through the columns with the RIGHT functions and return a value of 1 if the first letter of the text in that cell is a dash. Otherwise if the first letter of that text is not a dash, it will return #VALUE. g. Select all data and copy and paste values only onto a new worksheet to avoid potential problems with the functions updating. h. Once again select all data and click the sort and filter button on the ribbon on the home tab. i. In the sort dialog box, sort the values by column D in ascending order, which would place the values of 1 at the top of the data. j. Delete all other data except the data in column B that has a value of 1 in its corresponding D column cell. 2. It may cause errors in Excel, the trim function 3. The FIND function returns the starting position of one text value within another text value. It is also case-sensitive. The SEARCH function does the same thing as find, however it is not case-sensitive. 4. There are 2 ways to parse (divide) the data when using the wizard. The first way is to identify the character that separates the data. In comma-delimited files, commas separate the data (also another common way to separate data is through spaces made using the tab key.) The second way is to set field widths to identify the breaks between data that appears in columns. For example: if the first field uses the character 1-10 and the second field 11-15, you set a column break between 10 and 11 and another break between 15 and 16. 5. The advantages are that it creates summary reports that quickly organize data into categories with subtotal calculations and lets you collage and expand the level of detail inCGS 2518 Gower-Winter Final Review CH 6-9 4 the report. A disadvantage of the subtotal command is that it works only with one category and one subtotal calculation at a time. 6. The difference between an unstructured list and an Excel list is that because all the data in an excel list are in a structured format, one can use the filter feature to sort through the data using different methods. 7. Sort A to Z, Sort Z to A, Sort by Color (if cells are manually or conditionally changed to have background color, it filters these colors), Clear Filter From (removes any filters and restores original data), Text Filters (filters alphanumeric text by specific characters), Number Filters, Date Filters (filters by date and time values),(Custom) 8. The primary advantage of storing data in a database and importing that data into Excel is that it ensures that your data is secured and protected but can be analyzed using a spreadsheet easily, thus reducing redundancy. 9. Open a workbook where you want to import the database data. Select the first blank cell, and then click the From Access button in the Get External Data group on the Data tab on the ribbon. In the dialog box, find the file, select it, and click to open it. 10. The query wizard lets you choose your data source and select the database table and fields you want to import into a workbook. It prompts you to define any criteria for the data by selecting row to meet those criteria. You would use the query wizard when you are trying to select specific data prior to importing it into Excel. 11. Excel
View Full Document
Unlocking...