Chapter 6 Conceptual Review 1 Function to Calculate the interest percentage per period of a financial transaction Answer RATE transaction Answer PV transaction Answer FV financial transaction Answer PMT a financial transaction Answer NPER 2 Function to calculate the value at the beginning of a financial 3 Function to calculate the value at the end of a financial 4 Function to calculate periodic payments into or out of a 5 Function to calculate the number of compounding periods in 6 Use a 1 for this argument to indicate that interest will be paid at the beginning of each compounding period Answer Type 7 This type of interest is calculated based on original principal regardless of the pervious interest earned 8 This type of interest is calculated based on principal and Answer Simple Interest pervious interest earned Answer Compound Interest 9 Function to calculate straight line depreciation based on the initial capital investment number of years to be depreciated and salvage value 10 Function to calculate the amount of a specific periodic payment that is principal in a given period 11 Function to calculate the amount of a period payment that is Answer SLN Answer PPMT interest in a given period Answer IPMT two periods Answer CUMPRINC 12 Function to calculate the cumulative principal paid between 13 Function to Determine the value of a variable set of cash flows discounted to its present value Answer NPV Answer IRR 14 Function to determine 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 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 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 Answer 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 Find out what format you are importing Ex CVS b 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 as the 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 Table Answer 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 meet criteria that you specify 11 How does Excel store date and time values Answer Excel uses serial numbers to represent dates and times 12 What are the
View Full Document
Unlocking...