CONCEPTUAL REVIEW CHAPTER 6 1 RATE transaction Function to calculate the interest percentage per period of a financial 2 PV 3 FV 4 PMT 5 NPER Function to calculate the value at the beginning of a financial transaction Function to calculate the value at the end of a financial transaction Function to calculate period payments into or out of a financial transaction Function to calculate the number of compunding periods in a financial 6 Type Use a 1 for this argument to indicate that interest will be paid at the transaction beginning of each compounding period regardless of the previous interest earned previous interest earned 7 Simple Interest This type of interest is calculated based on original principal 8 Compound Interest This type of interest is calculated based on principal and 9 SLN Function to calculate straight line depreciation based on the initial capital Function to calculate the amount of a specific periodic payment that is 10 PPMT investment number of years to be depreciated and salvage value principal in a given period given period 12 CUMPRINC periods 11 IPMT Function to calculate the amount of period payment that is interest in a Function to calculate the cumulative principal paid between two 13 NPV Function to determine the value of a variable set of cash flows discounted to 14 IRR Function to determine the rate of return where the net present value of the its present value 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 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 12 17 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 12 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 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 Disadvantages it can organize your data into categories with subtotal calculations by creating an outline to expand or collapse as you want also this command does not work with Excel tables Also can only see one category and one subtotal calculation at a time data must be sorted by the category you want to subtotal 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 with a specific word and any other custom criteria that you specify 8 What is the primary advantage of storing data in a database and imoprting that data into Excel A database ensures a
View Full Document
Unlocking...