CGS2518 Final Conceptual Review Ch 6 9 Chapter 6 pg 422 transaction transaction 1 L Rate Function to calculate the interest percentage per period of a financial 2 K PV Function to calculate the value at the beginning of a financial 3 C FV all say function to calculate the value at the end of a financial transaction I PMT periodic payments into or out of a financial transaction 4 5 F NPER number of compounding periods in a financial transaction 6 P Type use of 1 for this argument to indicate that interest will be paid at the beginning of each compounding period 7 N Simple interest this type of interest is calculated based on original principle regardless of the previous interest earned 8 A Compound interest this type of interest is calculated based on principal and previous interest earned 9 O SLN function to calculate straight line depreciation based on the initial capital investment number of years to be depreciated and salvage value 10 J PPMT Function to calculate the amount of a specific periodic payment that is principal in a given period 11 D IPMT the amount of periodic payment that is interest in a given period 12 B CUMPRINC the cumulative principle paid between two periods 13 G NPV of a variable set of cash flows discounted to its present value 14 E IRR function to determine the rate of return where the net present value of the cash flow is 0 15 Assume that you are investing 6 000 in a savings plan today and will make additional contributions of 200 per quarter The plan pays 5 interest per year compounded quarterly Write an Excel formula to determine how much your savings will be worth in 5 years FV 05 4 20 200 6000 0 16 Write an Excel formula to determine the yearly interest rate being charged by the bank on a 375 000 30 year mortgage You make a monthly mortgage payment of 3 000 and the value of the loan at the end of 30 years is 0 Interest is compounded monthly Rate 360 3000 375000 0 0 Rate nper pmt fv type guess 17 Assume that you are buying a car for 23 500 with a 3000 down payment and you are borrowing the rest from a bank at 6 annual interest compounded monthly Your monthly payments are 370 Write an excel formula to determine the number of years it will take you to pay off this loan NPER 06 12 370 20500 0 0 NPER rate pmt pv fv type 18 Consider a 100 000 mortgage at 5 annual interest compounded monthly to be paid back over the next 15 years The loan will have a 5000 balloon payment due at the end of the loan Write an Excel formula to determine the payment that must be made each month on this loan PMT 0042 180 100000 5000 19 Assume that you have been left an inheritance ad want to save part of it toward the purchase of a car upon graduation which is three years from now Write an Excel formula to determine the amount of money you need to invest now to have 15000 at the end of the three year period Assume that you will place this money in a CD that pays 3 interest compounded quarterly and that you will be making no additional deposits into this account PV 03 4 3 15000 0 20 Write an Excel formula to determine the amount of money that can be depreciated each year using straight line depreciation for a new packaging machine purchased by your company The machine originally cost 250 000 and has a useful life of 10 years and an estimated salvage value of 10 000 SLN 250000 10000 10 Chapter 7 pg 494 1 List and describe the steps you would take to create a structured list of data from a text file that contains values stored on separate lines Step 1 use the CONCATENATE function to combine the text Step 2 use the RIGHT function to extract characters from the text string Step 3 use the TRIM function to remove spaces from the text string use the FIND function to determine the position of a character within the text string 2 Why should you remove unnecessary spaces from data imported from another source How do you remove unnecessary spaces from a text string Spaces can cause errors in Excel formulas To remove the spaces use the TRIM function 3 How do the FIND and SEARCH functions work and how are they different The FIND and SEARCH function do the same thing except the FIND function is case sensitive while the SEARCH function is not These functions both return the starting position of one text value within another text value 4 What options are available for parsing data when you use the Convert Text to Columns Wizard 1 identify the character that delimits the data 2 set field widths to identify the breaks between data that appears in columns 5 What are the advantages and disadvantages of using the Subtotal tool to analyze data Advantages creates summary reports that quickly organize data into categories with subtotal calculations allows you to collapse and expand the level of detail in the report Disadvantages data must be sorted by the chosen category for this tool to work properly not available if you are working with an Excel table 6 What is the difference between an unstructured list and an Excel list P 450 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 List and describe the six available options when using the Autofilter feature in an Excel table sort A to Z ascending order sort Z to A descending order sort by color filter by color if you manually or conditionally formatted the background or font color clear filter form removes and existing filters and displays all data in the column text filters filters alphanumeric text by specific characters number filters filters numeric values using comparison operators such as Equals date filters filters date and time values using comparison operators such as Equals 8 What is the primary advantage of storing data in a database and importing that data into Excel Databases are able to hold much larger and more complex data than Excel and data is protected from accidental changes 9 Explain the steps you would take to import data stored in an Access database into Excel Step 1 open the workbook into which you want to import the database data Step 2 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 Ribbon The Select Data Source dialog box opens Step 3 Navigate to the database from which you will import the data then click Open Click the table that contains the data you want to import
View Full Document
Unlocking...