Chapter 6 Conceptual Review1. Function to Calculate the interest percentage per period of a financial transactionAnswer: RATE2. Function to calculate the value at the beginning of a financial transactionAnswer: PV3. Function to calculate the value at the end of a financial transactionAnswer: FV4. Function to calculate periodic payments into or out of a financial transactionAnswer: PMT5. Function to calculate the number of compounding periods in a financial transactionAnswer: NPER6. Use a 1 for this argument to indicate that interest will be paid at the beginning of each compounding periodAnswer: Type7. This type of interest is calculated based on original principal regardless of the pervious interest earnedAnswer: Simple Interest8. This type of interest is calculated based on principal and pervious interest earnedAnswer: Compound Interest9. Function to calculate straight line depreciation based on the initial capital investment, number of years to be depreciated, and salvage valueAnswer: SLN10. Function to calculate the amount of a specific periodic payment that is principal in a given periodAnswer: PPMT11. Function to calculate the amount of a period payment that is interest in a given periodAnswer: IPMT12. Function to calculate the cumulative principal paid between two periodsAnswer: CUMPRINC13. Function to Determine the value of a variable set of cash flows discounted to its present valueAnswer: NPV14. Function to determine the rate of return, where the net present value of the cash flows is 0.Answer: IRR15. 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. How much it’s worth in 5 years?Answer:=FV(5%/4,5*4,-200,-6000,0)16. 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 compoundedmonthly.Answer: =RATE(30*12,-3000,375000,0)17. 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 numberof years it will take you to pay off this loan.Answer:=NPER(.06/12,-370-20500,0)18. 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.Answer: =PMT(.05/12,15*12,100000,-5000)19. Write an Excel formula to determine the amount of money you need to invest now to have $15,000at 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.Answer: =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 purchasedby your company. The machine originally cost $250,000 and has a useful life of 10 years and an estimated salvage value of $10,000.Answer: -SLN(250000,10000,10)Chapter 7 Conceptual Review1. 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. Find out what format you are importing. Ex.CVSb. Import the Data from the CVS work file onto the excel spread sheet. 2. Why should you remove unnecessary spaces from data imported from another source? How do you remove unnecessary spaces from a text string?Answer: This may cause errors on the worksheet. You should remove these using the Trim Function. =trim(text)3. How do the “find” and “search” functions work, and how are they different?Answer: The find function returns the starting position of one text value within another text value. *The FIND function is case sensitive. The search function does the same thing asthe find function, but the search function is not case sensitive. 4. What options are available for parsing data when you use the Convert Text to Columns Wizard?Answer: The first way is to identify the character that delimits or separates the data. The other way to parse data is to set field widths to identify the breaks between data that appears in the columns. 5. What are the advantages and disadvantages of using the subtotal tool to analyze data?Answer: Advantage- creates summary reports that quickly organize data into categories with subtotal calculations, and lets you collapse and expand the level of detail in the report. Disadvantage- One limitation of the Subtotal command is that 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?Answer: An unstructured list of data, also called a flat-file database, is basically a list of data that is all stored into one table of information. An excel table on the other hand is a rage of cells that you formalize as a single until of data.7. List and describe the six available options when using the AutoFilter feature in an Excel TableAnswer: Sort A to Z- sorts the data in the column in ascending order. Sort Z to A- Sorts the data in the column in descending order. Sort by Color- If you have manually or conditionally formatted the background or font color of a range of cells, you can filter these by colors. Clean Filter From- Removes any existing filters and displays all data in the column. Date Filters- Filters date and time values using comparison operators (equals, does not equal, greater than, less than). 8. What is the primary advantage of sorting data in a database and importing that data into excel?Answer: In a data base, the data is protected from accidental changes, yet is available for export into a spreadsheet for analysis. 9. Explain the steps you must take to import data stored in an Access data base into excel.Answer: Open work sheet, click data on the Ribbon, Click the From Other Sources button, then click From Microsoft Query. Click the MS Access Database, click the OK button. 10. What is the Query Wizard and when would you use it?Answer: It is a simpler way to use Microsoft Query. The Query Wizard prompts you to define any criteria for the data you want to import by selection only rows that
View Full Document
Unlocking...