**Unformatted text preview:**

Page 1 Final Exam Study Guide Answers Spring 2013 CGS 2518 Chapters 6‐9 Answers to all Conceptual Review Chapter 6 1. Rate ‐ Calculates the interest percentage per period of a financial transaction 2. PV ‐ Calculates the value at the beginning of a financial transaction 3. FV ‐ Calculates the value at the end of a financial transaction 4. PMT ‐ Calculates periodic payments into or out of a financial transaction 5. NPER ‐ Calculates the number of compounding periods in a financial transaction 6. Type ‐ Use a 1 for this argument to indicate that interest will be paid at the beginning 7. Compound Interest – This type of interest is calculated based on principal and previous interest earned 8. Simple Interest ‐ This type of interest is calculated based on original principal regardless of the previous interest earned 9. SLN ‐ Calculates straight line dep r eciation based on the initial capital investment, number of years to be depreciated, and salvage value. 10. PPMT ‐ Calculates the amount of a specific periodic payment that is principal in a given period 11. IPMT ‐ Calculates the amount of a periodic payment that is interest in a given period 12. CUMPRINCE ‐ Calculates the cumulative principal paid between two years 13. NPV ‐ Determines the value of a variable set of cash flows discounted to its present value 14. IRR ‐ Determines the rate of return, where the net present value of the cash flows 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. Formula for savings in 5 years =FV(.05/4, 5*4, ‐200, ‐6000,0) 16. 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(30*12,‐3000,375000,0,0) 17. 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) 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 $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) Page 2 19. 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, 0, 15000) 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)) Page 3 Chapter 7 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. Short Answer: use the CONCATENATE, TRIM, RIGHT, and FIND functions to combine multiple text strings into a single text string, to trim unnecessary spaces from a text string, to find and extract characters from a text string, and to find specific characters in a text string. Detailed Answer: (This question is very vague so the answer is based off of the problem in the book at the bottom of page 434) a. Determine format needed b. Copy all of the data from the file and paste in the workbook c. Use the CONCATENATE function to merge all of the data into one cell. The formula will appear similar to this: CONCATENATE (A1,",",A2,",",A3,",",A4,",",A5). The “,” separates the data by commas. d. Use the RIGHT function to sort the data. The example in the book (Page 437) uses then RIGHT function to sort the data by the dash followed by the four numbers. The formula would appear as follows: RIGHT(CELL,5) e. Insert the TRIM function within the CONCATENATE function in order to remove unwanted spaces in the values. The formula would appear as follows: CONCATENATE (TRIM(A1),”,”,TRIM(A2),”,”…) f. Use FIND to determine if the first letter of the text is a dash (as in the example). If the first letter is a dash, it will return a “1” because the dash is the first letter in the cell. If not, FIND will return #value. The function should appear as follows: FIND (CELL, “‐“) g. Copy the newly sorted data and Paste as Values into a new formula (in order to avoid confusion with formulas) h. With all data selected, sort and filter. 2. Why should you remove unnecessary spaces from data imported from another source? How do you remove unnecessary spaces from a text string? May cause errors within Excel By using the TRIM function. 3. How do the FIND and SEARCH functions work, and how are they different? The FIND function returns the starting position of one text value within another text value and it is case‐sensitive. The SEARCH function does the same thing as FIND but it is not case‐sensitive. 4. What options are available for parsing data when you use the Convert Text to Columns Wizard? a. Identify the character that separates (delimates) the data. In comma‐delimited files, commas separate the data. Another common way is the tab character. b. 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? Advantage ‐ it creates summary reports that quickly organize data into categories with subtotal calculations and allows you to collage and expand the level of detail in a report Disadvantage ‐ it works only with one category and one subtotal calculation at a time. 6. What is the difference between an unstructured list and an Excel list? Page 4 Excel lists are in a structured format, you

View Full Document