CGS FINAL EXAM CHAPTERS 6 9 CHAPTER 6 CONCEPTUAL REVIEW Match the following lettered items with Question 1 14 A Compound Interest E IRR B CUMPRINC C FV D IMPT F NPER G NPV H Payback Period I PMT J PPMT K PV L RATE M ROI N Simple Interest O SLN P Type 1 Function to calculate the interest percentage per period of a financial transaction RATE 2 Function to calculate the value at the end of a financial transaction PV 3 Function to calculate the value at the end of a financial transaction FV 4 Function to calculate periodic payments into or out of a financial transaction PMT 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 7 This type of interest is calculated based on original principal regardless of the previous NPER compounding period Type interest earned Simple Interest Compound Interest 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 SLN 10 Function to calculate the amount of a specific periodic payment that is principal in a given period PPMT Function to calculate the amount of a periodic payment that is interest in a given period IMPT 11 Function to calculate the cumulative principal paid between two periods CUMPRINC 12 Function to determine the value of a variable set of cash flows discounted to its present 13 Function to determine the rate of return where the net present value of the cash flows is value NPV 0 IRR Short Answer 14 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 five years FV 0 05 4 5 4 200 6000 0 12 204 82 15 Write an Excel formula to determine that 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 30 12 3000 375000 0 0 16 Assume that you are buying a car for 23 500 with a 3 000 down payment and you are borrowing the rest from a bank at 6 0 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 17 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 5 000 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 05 12 15 12 100000 5000 18 Assume that you have been left an inheritance and 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 15 000 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 4 15000 19 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 250000 and has a useful life of 10 years and an estimated salvage value of 10 000 SLN 250000 10000 10 CHAPTER 7 CONCEPTUAL REVIEW 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 a Determine format needed most likely Comma Separated Values CSV b Copy data from file and past into workbook c In the column next to the data use CONCATENATE A1 A2 A3 to merge data into one cell By inserting the data in the CONCATENAE function will be separated by commas In an adjacent cell use the RIGHT function to sort through the data using a common text d e To ensure the right function returns the values desired embed 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 Why should you remove unnecessary spaces from data imported from another source How do you remove unnecessary spaces from a text string It may cause errors in Excel the trim function a 3 How do the FIND and SEARCH functions work and how are they different a 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 What options are available for parsing data when you use the Convert Text to Columns Wizard a 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 What are the advantages and disadvantages of using the Subtotal tool to analyze data a The advantages are that it creates summary reports that quickly organize data into categories with subtotal calculations …
View Full Document
Unlocking...