1 Advantages and disadvantages of using the Subtotal Tool to analyze data Advantage allows you to quickly organize data into categories that can be expanded and contracted to show more and less detail Disadvantage one category and one subtotal calculation at a time 2 Assume that you are buying a car for 25 500 with 4 000 down payment and you are borrowing the rest from a bank at 5 5 annual interest compounded monthly Your monthly payment are 400 Write an Excel formula to determine the number of years it will take you to pay off this loan NPER 055 12 400 21500 0 3 Assume that you are investing 3 000 in a savings plan today and will make additional contributions of 300 per quarter the plan pays 3 interest per year compounded quarterly at the beginning of each period Write an Excel formula to determine how much your savings will be worth in five years FV 3 0 4 5 4 300 3000 0 4 Assume that you have been left an inheritance and want to save part of it to buy a car which is three years from now Write an excel formula to determine the amount you need to invest now to have 17 000 at the end of the three year period You will place this money in a CD that pays 2 interest compounded quarterly PV 02 4 3 4 0 17000 5 Compare break even analysis and sensitivity analysis Break even analysis is a what if analysis that focus on an activity at or around the point where revenue expenses Sensitivity analysis is a type of what if analysis that attempts to examine how sensitive results of an analysis are to changes in assumption 6 Consider a 150 000 mortgage at 5 annual interest compounded monthly to be paid back over the next 30 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 5 0 12 30 12 150000 5000 7 Create a lookup table for the delivery worksheet so that you can use a lookup function to calculate the delivery cost for each order Organize Horizontally 0 6 10 7 50 40 free Formula to find delivery cost VLOOKUP C13 lookuptable 2 TRUE 8 Describe how SUMPRODUCT works Multiplies values according to their positions in multiple ranges and then adds the results together 9 Explain how you must vary the setup of a two variable data table to run a simulation You must structure it with the two variables perpendicular to each other and the table s output formula is located at the intersection of the two sets of input variables One set of variables must be a number of iterations of the simulation 10 Explain the difference between the lookup table in cells A3 E4 of the Grades worksheet and the lookup table in cells A6 E7 in the same worksheet The lookup table A3 E4 is sorted in descending order whereas the lookup table A6 E7 is sorted in ascending order which allows you to use the HLOOKUP function with a TRUE lookup type 11 Function that indicates which row of data is correct FIND function 12 Function to calculate interest per period of a financial transaction RATE 13 Function to calculate periodic payments in or out of a financial transaction PMT 14 Function to calculate straight line depreciation based on the initial capital investment number of years to be depreciated and salvage value SLN 15 Function to calculate the amount of a period payment that is interest in a given period IPMT 16 Function to calculate the amount of a specific period payment that is principal in a given period PPMT 17 Function to calculate the cumulative principal paid between two periods CUMPRINC 18 Function to calculate the number of compounding periods in a financial transaction NPER 19 Function to calculate the value at the beginning of a financial transaction PV 20 Function to calculate value of the end of a financial transaction FV 21 Function to determine the rate of return where the net present value of cash flows is equal to zero IRR 22 Function to determine the value of a variable set of cash flows discounted to its present value NPV 23 How does a data table help you perform a what if analysis They help you organize and present the results of multiple what if analyses 24 How does excel store date and time values As serial number where day month year are a whole number and hour minute second are a decimals 25 How does the binary comparison operator work It restricts the value of a cell to 1 or 0 26 How do the FIND and SEARCH functions work and how are they different They are used to return the location of a character within a text string FIND is case sensitive SEARCH is not 27 How do you save a solver model Click solver button on data tab click load save select empty range with the number of cells listed in the dialog box click save close the dialog box 28 How is a Pivot table different from a normal excel chart A pivot table is more customizable with collapsible features 29 How many variables can you use in a solver model 200 30 List and describe the four areas of a PivotTable Report filter column labels row labels and values 31 Text function that extracts a unique feature that can be used to identify the correct rows RIGHT function 32 This type of interest is calculated based on original principal regardless of the previous interest earned Simple Interest 33 This type of interest is calculated based on principal and previous interest earned Compound Interest 34 T or F Excel matches the lookup value tom with the entry TOM in a lookup table False 35 T or F In a VLOOKUP formula with a TRUE lookup type the first column of the lookup table referenced must be in ascending order to retrieve the correct value True 36 T or F Reference and Lookup functions may not contain nested functions as arguments False 37 T or F The default range lookup type for the VLOOKUP and HLOOKUP functions is TRUE True 38 T or F The formula AVERAGE CHOOSE 1 B12 D17 B22 D17 averages the value 1 with the values in cells B12 to D17 and B22 to D17 False 39 T or F The formula INDEX B2 D7 B12 D17 B22 D17 2 3 1 returns the value in cell D3 True 40 T or F The formula MATCH 40 10 20 40 50 0 returns the value 3 True 41 T or F The lookup value of a HLOOKUP function can be a contiguous cell range False 42 T or F The result vector of a LOOKUP function must be sorted in ascending order False the lookup vector has to be 43 T or F The row and column arguments in the INDEX function can be numeric values Boolean values or text False …
View Full Document
Unlocking...