CONCEPTUAL REVIEWCHAPTER 6:1. RATE Function to calculate the interest percentage per period of a financial transaction2. PV Function to calculate the value at the beginning of a financial transaction3. FV Function to calculate the value at the end of a financial transaction4. PMT Function to calculate period payments into or out of a financial transaction5. NPER Function to calculate the number of compunding periods in a financial transaction6. Type Use a 1 for this argument to indicate that interest will be paid at the beginning of each compounding period7. Simple Interest This type of interest is calculated based on original principal regardless of the previous interest earned8. Compound Interest This type of interest is calculated based on principal and previous interest earned9. SLN Function to calculate straight line depreciation based on the initial capital investment, number of years to be depreciated and salvage value10. PPMT Function to calculate the amount of a specific periodic payment that is principal in a given period11. IPMT Function to calculate the amount of period payment that is interest in a given period12. CUMPRINC Function to calculate the cumulative principal paid between two periods13. NPV Function to determine the value of a variable set of cash flows discounted to its present value14. IRR Function to determine the rate of return, where the net present value of the cash flows is 015. Assume that you are investing $6,000 in a savings plan today and will make additional contributions of $200 per quarter. The plan says 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,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 motnhly 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)*1217. Assume 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(0.06/12,-370,20500,0)/1218. 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(0.05/12,15*12,100000,-5000)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.=PV(0.03/4,3*4,0,15000)20. Write an Excel formula to determine the amount of money that can be depreciated each year, using the straight line deprecation, 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: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.-Import to Excel-Use the CONCATINATE function to specify which cells you want in the same line, separated by commas by inserting “,” after each cell you select-Within the CONCATINATE function, make sure you insert the TRIM function as well for each cell, this avoids having extra spaces.Ex.: =CONCATINATE(TRIM(A1),”,”,TRIM(A2),”,”,TRIM(A3))2. Why should you remove unneccessary spaces from data imported from another source? How do you remove unneccessary spaces from a text string?These spaces can cause errors in Excel formulas such as RIGHT or LEFT. You can remove unnecessary spaces in a text string by using the TRIM function.3. How do the FIND and SEARCH functions work, and how are they different?The FIND function looks the starting position of one text value within another text value, this function is case sensitive so search for a will give you a different answer than searching for A. SEARCH does the same 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?You can divide text in two ways: you can select what character separates the data (like a comma or a tab) or you can separate data by setting field widths to identify where you want the data to be separated into another column.5. What are the advantages and disadvantages of using the Subtotal tool to analyze data?Advantages: it can organize your data into categories with subtotal calculations by creating an outline to expand or collapse as you want.Disadvantages: data must be sorted by the category you want to subtotal; also, this command does not work with Excel tables. Also, can only see one category and one subtotal calculation at a time.6. What is the difference between an unstructred list and an Excel list?If a data list is unstructured it may not be possible to import data into Excel in the format that you need. When you copy and past it into Excel usually it will just import each line in a separate cell. With an Excel list, you have a lot more options to separate your data into columns, categorize them, sort them, find subtotals, etc.7. List and describe the six available options when using the AutoFilter feature in an Excel Table.-Sort data in ascending order, from smallest to largest, or A to Z; sort data in descending order, from largest to smallest, or Z to A.-Sort by color, if you manually or conditionally formatted cells/text-Clear Filter From, lets you remove existing filters-Text Filters, filters alphanumeric text by number of characters, can use greater than, less than, equal to, etc.-Number Filters, filters numbers using equal to, less than, greater than, etc.-Date Filers, filters date and time values by using equal to, less than, greater than, etc. Can also select a particular day, month, year, etc.-Create a custom filter, can use formulas like AND, OR in your filter, search for cells
View Full Document
Unlocking...