Study Guides (238,466)
United States (119,812)
CGS 2060 (18)
Jay (16)

CGS 2060 Final: EXAM GUIDE

10 Pages
Unlock Document

Florida State University
Computer Science
CGS 2060

CGS FINAL EXAM STUDY GUIDE (Memorization) Ch 5 - Car Model =VLOOKUP(Model #,Models! (table)A4:C12,3 (column),FALSE) - Average Maint. =AVERAGE(VLOOKUP(Model #,Models table,5 (column),FALSE),VLOOKUP(Model #,Models table,6 (column),FALSE),VLOOKUP(A11,Models,7,FALSE)+$I$4/Models!$D$17*Models!$E$17) - Average Depreciation = AVERAGE(VLOOKUP(A11,Models,8,FALSE),VLOOKUP(A11,Models,9,FALSE),VLOOKUP(A1 1,Models,10,FALSE)) Ch 6 - Sales Volume for each quarter = ROUND(4625,-1) FOR FIRST; =ROUND(C3*(1+projectedgrowth),-1) FOR SECOND - Selling Price per Pair =ROUND(C4*(1+Inflation),-1) FOR SECOND Gross Revenue = Sales Volume * Selling Price Costs of Goods sold/Marketing cost/shipping cost/cost per pair = sales volume * cost - Interest Expense = CUMIPMT(Interest rate/per year, duration*per year, original loan,'Cash Flow' (quarter 1)*12-11,'Cash Flow'(quarter 1)*12,0) - Depreciation = -SLN(COST,SalvageValue,LIFE) - Taxable Income = (gross revenue + cost of goods sold + marketing cost + shipping cost + cost savings + interest expense + depreciation) - Sales and Federal Taxes Owed = Taxable Income*(VLookup(Taxable income,IncomeTax,2,True) - Income After Taxes = Taxable Income - Sales/Federal Taxes owed - Add Back Depreciation = -(SUM(depreciation)) - Subtract Principle Payments =(CUMPRINC(interest rate/periods per year,loan duration*period per year,original laon,'Cash Flow'(quarter 1)*12-11,'Cash Flow'(quarter 1)*12,0)) - Projected Cash Flow = (Income after taxes + add back depreciation + subtract principle payments) - Quarterly Payment = PMT(interest rate / periods per year,(12*period per year),original loan) Ch 7a - Full name temp = TRIM(CONCATENATE(last name cell,", ", First name cell," ",middle name cell) - Contains Null = IFERROR(FIND("NULL", full name temp),"Valid Name") - Full Name = (CONCATENATE(last name,", ",PROPER(first name)," ",If(MiddleName ="null","",SUBSTITUTE(MiddleName,",",""))) - Usernames = LEFTorRIGHT(email,FIND("@",email)-1) Ch 7 - Day Since Last Order =TODAY()-[@ShipDate] - Years Since = ROUND(YEARFRAC([@ShipDate],TODAY(),1),0) Ch 8 - Unites sold = VLOOKUP(random#,cummulative probability:...,2(units sold),TRUE) --> the next =ROUNDUP((# given)*units sold,0) - Cummulative Prob = above percent + individual prob -Sales = SUMPRODUCT(units sold(ALL),selling price (ALL)) -COGS = -SUMPRODUCT(cogs(All),units sold)) - Gross Profit = sales+COGS - Delivery Expense = -SUMPRODUCT(delivery(ALL),units sold) - Contribution MArgin = gross profit + delivery expense - Total Fixed Expenses = SUM(market + savings(retained earnings) + payroll + overhead) - Marginal Income before taxes = contribtiion margin + total fixed expenses - Estimated income tax expense = IF(marginal>0,-marginal*marginal tax rate,0) -marginal net income = marginal income before taxes +estimated income tax expense - breaking even = COUNTIF(#:#,">0")/rows - Simulation {=TABLE(COGS,empty cell) CGS Extra Credit 1. ­What is the name of the Seminole Indian leader that one selected FSU portrays at the various FSU events? a. Osceola 2. How many different FSU students have portrayed this famous Seminole a. 16 3. What is the name of the horse that he rides? a. Renegade 4. What is the breed of horse? a. Appaloosa 5. How many different horses have had this honor since the tradition started in 1978? a. 6 Chapter 5 1. The lookup value of a HLOOKUP function can be a contiguous cell range a. FALSE 2. 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 a. TRUE 3. The result vector of a LOOKUP function must be sorted in ascending order a. FALSE 4. Reference and Lookup functions may not contain nested functions as arguments a. FALSE 5. The default range lookup type for the VLOOKUP and HLOOKUP functions is TRUE a. TRUE 6. Excel matches the lookup value “tom” with the entry “TOM” in a lookup table a. TRUE 7. The row and column arguments in the INDEX function can be numeric values, Boolean values, or text a. FALSE 8. The formula =INDEX((B2:D7,B12:D17,B22:D17),2,3,1) returns the value in cell D3 a. TRUE 9. 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 a. FALSE 10. The formula =MATCH(40,{10,20,40,50},0) returns the value 3 a. TRUE 11. What happens when Excel is solving a HLOOKUP formula with a FALSE range lookup type and does not find an exact match in the lookup table? a. #N/A 12. What is the difference between the LOOKUP function and the VLOOKUP or HLOOKUP function a. The LOOKUP function looks up the greatest value that doesn’t exceed any specified value anywhere in a table or range. It doesn’t have True/False. It always gives an estimate, not an exact match. 13. Which Excel function should you use when you want to look up a value from a two- dimensional table, where both the columns and rows can be varied? a. INDEX function 14. Write a formula to choose the name of the third day of the week from the list starting with Sunday, Monday, Tuesday, …Saturday a. =CHOOSE(3,"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") 15. What function returns the relative position of an item from a list? a. MATCH 16. Write a formula in cell C13 in the Pricing worksheet to determine the total cost of making copies for this order (275 copies). Write the formula so that it works when copied into cells C14:C15. a. =VLOOKUP(B13,$B$2:$C$7,2,TRUE)*B13 17. Write a formula in cell D13 in the pricing worksheet to look up the correct delivery cost using the lookup table you created in question. Write the formula so that it works when copied down the column a. =VLOOKUP(A2,$A3:$B3,3,True) 18. As shown in the Grades worksheet, final grades are determined using the following grading scheme: a. Students earning over 900 points receive an A. b. Students earning less than 900 points but at least 800 points receive a B. c. Students earning less than 800 points but at least 700 points receive a C. d. Students earning less than 700 points but at least 600 points receive a D. e. Students earning less than 600 points receive an F. Write a formula in cell C3 in the Scores worksheet that determines the final grade for the first student based on the grading scheme. Use the appropriate Reference and Lookup function and write the formula so that it can be copied down the column. = HLOOKUP(B2,Grades!A$5:E$7,2,TRUE) 19. Explain the difference between the lookup table in cells A3:E4 of the Grades worksheet and the lookup table in cells A5:E7 in the same worksheet. a. The lookup table in A3:E4 of the Grades worksheet is sorted in descending order, and the lookup table in cells A5:E7 is sorted in ascending order, which allows you to use the HLOOKUP function with a TRUE lookup type. Chapter 6 1. Function to calculate the value at the end of a financial transaction a. FV 2. Function to calculate the interest percentage per period of a financial transaction a. RATE 3. Function to calculate the value at the beginning of a financial transaction a. PV 4. Function to calculate the number of compounding periods in a financial transaction a. NPER 5. Function to calculate periodic payments into or out of a financial transaction a. PMT 6. Use a 0 for this argument to indicate that interest will be paid at the end of each compounding period a. Type 7. This type of interest is calculated based on principal and previous interest earned a. Compound interest 8. This type of interest is calculated based on original principal regardless of the previous interest earned a. Simple interest 9. Function to calculate straight line depreciation based on the initial capital investment, number of years to be depreciated, and salvage value a. SLN 10. Function to calculate cumulative interest paid between two periods a. CUMPRINC 11. Function to calculate the amount of a periodic payment that is interest in a given period a. IPMT 12. Function to calculate the amount of a specific periodic payment that is principal in a given period a. PPMT 13. Function to determine the value of a variable set of cash flows discounted to its present value a. NPV 14. Function to determine the rate of return where the net present value of the cash flows Is 0 a. IRR 15. 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 3 years from now. Write an Excel formula to determine the amount of money you need to invest now to have $17,000 at the end of the 3 year period. Assume that you will place this money in a CD that pays 2% interest compounded quarterly and that you will be making no additional deposits into this account. a. PV(.02/4,4*3,0,17000) 16. Write an Excel formula to determine the yearly interest rate being charged by the bank on a $400,000, 20 year mortgage. You make a monthly mortgage payment of $3,400, and the value of the loan at the end of 20 years is 0. Interest is compounded monthly. a. =RATE(20*12,-3400,400000,0) 17. Assume that you are buying a car for $25,500 with a $4,000 down payment, and you are borrowing the rest from a bank at 5.5% annual interest compounded monthly. Your monthly payments are $400. Write an Excel formula to determine the number of years it will take you to pay off this loan. a. =NPER(.055/12,-400,25500,0)/12 18. 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. a. =PPMT(.05/12,30*12,150000,-5000) 19. Assume that you are investing $3,000 in a savings plan today and will make additional contributions of $300 per quarter. The plan says 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 5 years. a. =FV(.03/4,5*4,300,-3000) 20. Write an Excel formula to determine the amount of money that can be depreciated each year, using straight line depreciation, for a new pa
More Less

Related notes for CGS 2060

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.