CGS 2518 Gower Winter Final Review CH 6 9 Chapter 6 Conceptual Review 1 Function to calculate the interest percentage per period of a financial transaction 2 Function to calculate the value at the beginning of a financial transaction 3 Function to calculate the value at the end of a financial transaction 4 Function to calculate periodic payments into or out of a financial transaction a RATE a PV a FV a PMT a NPER 5 Function to calculate the number of compounding periods in a financial transaction 6 Use a 1 for this argument to indicate that interest will be paid at the beginning of each compounding period a Type interest earned a Compound Interest a Simple Interest a SLN 7 This type of interest is calculated based on original principal regardless of the pervious 8 This type of interest is calculated based on principal and previous interest earned 9 Function to calculate straight line depreciation based on the initial capital investment number of years to be depreciated and salvage value 1 CGS 2518 Gower Winter Final Review CH 6 9 10 Function to calculate the amount of a specific periodic payment that is principal in a given period a PPMT a IPMT value a NPV a IRR a CUMPRINCE 11 Function to calculate the amount of a periodic payment that is interest in a given period 12 Function to calculate the cumulative principal paid between two periods 13 Function to determine the value of a variable set of cash flows discounted to its present 14 Function to determine the rate of return where the net present value of the cash flows is 0 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 2 CGS 2518 Gower Winter Final Review CH 6 9 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 j i home tab 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 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 in 3 CGS 2518 Gower Winter Final Review CH 6 9 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 uses serial numbers to represent dates and times For dates Excel represents it as the number of days since January 0 1900 Therefore January 1 1900 would be represented by the serial number 1 Times are represented by decimals Noon on January 1 1900 would be represented by the serial number 1 5 12 YEARFRAC start date end date …
View Full Document
Unlocking...