Study Guides
(238,466)

United States
(119,812)

Florida State University
(1,400)

Computer Science
(34)

CGS 2060
(18)

Jay
(16)

Final

Unlock Document

Florida State University

Computer Science

CGS 2060

Jay

Spring

Description

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