Test 2

in what order is the date function entered

year, month, day

Are functions static or dynamic

Dynamic

WHAT DOES INFO NUM FILE GEIVE YOU

NUMBER OF SHEETS IN ALL OPEN WORKBOOKS

Find(), Len(), Left(), Right() are called String functions Number functions Business functions Character functions

String functions

PICK THE LARGER IRR

TRUE

ROUNDDOWN function always rounds a given value towards zero. In other words, a positive number becomes less positive and negative number becomes less negative

TRUE

Roundup function always rounds up, away from zero. In other words, a positive number becomes "more positive" and a negative number becomes "more negative".

TRUE

SECOND, MINUTE, HOUR are three separate functions. Each function takes one required argument, a serial number representing time, and returns the corresponding second, minute, or hour

TRUE

You are ready to buy a new car. Given an annual percentage rate of 8%, how much can you borrow if you can afford to repay $300 each month for 60 months (paid at the end of each month)? A: $1197.81 or less B: $3712.96 or less C: $14,795.53 or less D: $14,795.53 or more

$14,795.53 or less

Limitations of VLookup

1: looks in 1st column unless you redefine table array in formula 2: cant search to left of lookup value 3: incorrect match if table not sorted when using "True for approximate" 4: Not case sensitive 5: table inflexibility: if we add a new row/column we would need to redefine the array in formula

A project with a 15-year life and a cost of $200,000 generates revenues of $22,000 each year. If the discount rate is 8%, what is the present value of the project? A: $11691 (negative present value - we should not do the project) B: $11619 (positive value - we should do the project) C: $15312 (negative present value - we should not do the project) D: $12523 (positive value - we should do the project)

A: $11691 (negative present value - we should not do the project)

A company is considering choosing between one of two projects: Project S and Project L. Cash flows for these projects are shown below. YEAR PROJECT S PROJECT L Year 0 ($1,100.00) ($1,100.00) Year 1 $1,000.00 $0.00 Year 2 $350.00 $300.00 Year 3 $50.00 $1,500.00 What is the IRR of the project that the company should choose? a. 20.46% b. 15.53% c. 18.62% d. 19.08%

A: 20.46

In a range, some cells are blank (there is nothing in the cell, not even a space), while some cells contain letters and others contain numbers. Which of the functions below will return the number of cells containing letters and numbers? A: Counta B: Count C: Count blank D: Both a and b

A: Counta

I have three tables. Each table has 6 rows and 5 columns. I want to multiply each value in one of the tables with the values in the corresponding row and column in each of the other two tables. I then want to sum all of these values. A: I can use the SUMPRODUCT function to do this. B: I cannot use the SUMPRODUCT function because there are more than two sets of data. C: I cannot use the SUMPRODUCT function because the three tables have identical dimensions. D: Both b and c are correct.

A: I can use the SUMPRODUCT function to do this.

To use the sumproduct function, the dimensions of two arrays should be identical a. TRUE b. FALSE

A: TRUE

If the third argument in a Vlookup function has a value of 1, what will the function return if the lookup value is present in the first column of the table array and the range lookup is FALSE? A: The lookup value B: A value greater than the lookup value C: A value less than the lookup value D: An error message

A: The lookup value 3rd argument is the column. 4th arguement: "false or 0". "True or 1"

The True 4th argument in gives you exact match or approximate

Approximate

An investment having an initial cash outflow of $213,000 generates returns during the first, second, third, fourth years of $65,200,$96,000,$73,000 and $55,400 respectively. The cash flows and dates are given below. DATE AMT 1/1/13. (213,000) 5/7/14 65,200 4/3/15 96000 6/8/16 73000 2/9/17 55,400 Find the IRR from the following options. A: 11.45% B: 12.32% C: 13.67% D: None of the above

B: 12.32%

In cell A1 I have the name "JOHN SMITH". What formula will I use to change "JOHN SMITH" to "JANE SMITH" (in cell A2, or some other cell)? A: =Replace("JOHN", "JANE") B: =SUBSTITUTE(A1, "JOHN", "JANE") C: =SUBSTITUTE(A1, "John", "JANE") D: =Change("JOHN", "JANE")

B: =SUBSTITUTE(A1, "JOHN", "JANE")

tells us the current column number()? A: Col() B: Column() C: current() D: None of the above

B: Column ()

What day of the week is December 12, 2011? A: Sunday B: Monday C: Wednesday D: Friday

B: MONDAY

If you assign the value 42 to cell E5, the value 11 to cell F5, and cell G5 contains the function =IF(E5>4*F5,"Deductions","No deductions"), then cell G5 displays the message, ____________. a. Deductions b. No deductions c. #REF! (That is, you get an error message #REF!) d. F5

B: No Deductions

A company ABC has two construction project proposals. The investment and cash flows are given below. If the company can invest in only one of the projects, what should the company do? Assume the discount rate to be 12%. Year YEAR PROJECT 1 PROJECT 2 Year 0 ($10,000.00) ($7,500.00) Year 1 $3,000.00 $1,800.00 Year 2 $2,500.00 $1,200.00 Year 3 $1,800.00 $2,200.00 Year 4 $3,200.00 $3,000.00 Year 5 $2,000.00 $2,800.00 A: The company choose project 1 B: The company choose project 2 C: The company should not invest both are not viable D: The company will earn profits if it invest in any of the projects.

B: The company choose project 2

What is the best way to calculate the average of a group of numbers excluding any zero values? A: Use the built in AVERAGE() function, as it already automatically ignores zeros B: Use the AVERAGEIF function C: Count the zero occurrences in the dataset and subtract them for them total number of data points. Now, calculate the average manually without the built-in AVERAGE() function using the sum of all occurrences and the revised number of data points. D: None of the above

B: Use the AVERAGEIF function

A NEG number from EDAte will go forwards or backward

Backward

A project will give you $1000 on April 1, 1999, $2500 on July 1, 1999, $2500 on October 1, 1999, and $5000 on December 31, 1999. What is the net present value of this investment if the interest rate is 6.00%? A: $10,123 B: $11,678 C: $10,678 D: $ 9,678

C: $10,678

Assume cell A1 contains the text string "Naveen Jindal" (with no quotes). Which of the following will find the position of the letter J? A: =LEFT("J",A1) B: =LEFT(A1, "J") C: =FIND("J",A1,1) D: =FIND("J",A1,9)

C: =FIND("J",A1,1)

The Vlookup function can be used to find: A: The exact value corresponding to a given number in a lookup table B: The approximate value corresponding to a number in a lookup table C: Both a and b D: Neither a nor b

C: Both a and b

The Vlookup function can be used to find: A: Text B: Numbers C: BothAandB D: Neither A nor B

C: BothAandB

Which of the following is not an information function? A: Isblank() B: Row() C: goalseek() D: Directory()

C: GOALSEEK

helps find if the input has error or not? A: Error() B: Incorrect() C: Iserror() D: All of the above

C: Iserror()

The Internal Rate of Return (IRR) is the rate at which: A: NPV is maximized B: NPV is minimized C: NPV is equal to zero D: None of the above

C: NPV is equal to zero

ExxonMobil has projected the cash flows - at the end of each year - for three proposed projects for upgrading its refinery facilities. The discount rate for each project is 10%. Which is the most profitable project? YEAR. A B C 1 -12000.00 -22,000 -10,000 2 6,000 11,000 7,000 3 7,000 12,000 8,000 4 5,000 10,000 5,000 Choose from the options below A: Project A B: Project B C: Project C

C: Project C

How do you know the version of excel you are running? A: Excelversion() B: System() C: Version() D: release()

C: Version()

A person is classified as a Junior, Adult, Senior depending on his age as follows: 0 to 18 years: Junior 19 to 60 years: Adult 61 and above: Senior What formula can I use to get this classification? (Assume all ages are integer values). A: If(AGE<19,18<AGE<60, AGE>60,"Minor","Adult","Senior") B:If(AGE<18,"Junior",If(And(AGE>18,AGE<61),"Adult","Senior ")) C:If(AGE<19,"Junior",If(And(AGE>18,AGE<61),"Adult","Senior ")) D: None of the above

C:If(AGE<19,"Junior",If(And(AGE>18,AGE<61),"Adult","Senior "))

SHORTCUT FOR TODAY FUNCTION

CONTROL SEMICOLON

For a period of 10 years, on the first of January of each year, a company has cash flows of: $1000, $2312, $1212, $631, $7892, $5212, $7782, $2355, $8234, $2346). The IRR for this series of cash flows is: A: $28,561 B: $27,226 C: Zero D: Cannot be calculated

Cannot be calculated

If it is 1265 minutes today in Excel, this is equivalent to? A: 0.325 B: 0.524305556 C: 4:55 PM D: 0.878472222

D: 0.878472222

In vLookup, to make the function return an approximate match, if the lookup column is sorted in ascending order: A: The default argument can be used for the range lookup argument B: I can use 'TRUE' for the range lookup argument C: I can use '1' (one) for the range lookup argument D: All of the above

D: All of the above 4th arguement: "false or 0". "True or 1"

Which of the following are true A: We can show the month and day in letters B: Dates are considered numbers in Excel C: Days are counted as numbers starting from 1/1/1900 D: All of the above statements are true

D: All of the above statements are true

I have a first name and last name in cell A2. The two names are separated by a single space. How do I get the last name from cell A2? A: =RIGHT(A2,LEN(A2)-FIND(" ",A2)) B: =LEFT(A2,LEN(A2)-FIND(" ",A2)) C: =MID(A2,FIND(" ",A2)+1,LEN(A2)-FIND(" ",A2)) D: Both A and C

D: Both A and C

What does the Excel function SUMPRODUCT(array1, array2) do? A: Sums each range and multiplies the sums B: Sum each pair of cells and multiples each sum C: Multiplies the contents of cells containing the =SUM() function D: Multiplies each pair of cells in the given ranges and sums the products

D: Multiplies each pair of cells in the given ranges and sums the products

Positive number =edate(today, 5) will give you what result

December since today is July. 5 months back

Returns the date (in date or serial number format) of the last day of the month before or after a specified number of months.

EOMONTH function

The 4th argument in VLookup OF FALSE gives you exact match or approximate?

Exact Match

The PRODUCT function returns the product of the supplied set of values. As for the SUM function, these arguments can be numbers or can result in numbers.

TRUE

DATAVALUE

It converts a date in the form of text to a serial number.

The internal rate of return (IRR) is sometimes referred to as the economic rate of return (ERR).

TRUE

The start date can be given as text or as a serial number. WITH EDATE FUNCTION

TRUE

VLOOKUP REQUIREMENT

REQUIREMENT: 1: lookup value 2: table array 3: col index num Optional 1: range lookup

LOOKUP FUNCTION

Required: lookup value lookup vector Optional: result vector: range that must be same size as lookup vector

what does INFO Directory

Tells you path of saved file

The Excel Power function calculates a given number, raised to a supplied power.

TRUE

The Excel Roundup function rounds a supplied value up, away from zero, to a specified number of decimal places.

TRUE

Max made an investment of $1500 on 1/1/2014. From this investment, he is scheduled to receive returns for the next 5 years as shown below. If the discount rate is 5%, what will the net present value be? DATE AMT 1/7/15 $200 2/8/16 $300 3/9/17 $400 4/10/18 $500 5/11/19 $400 a. $52 b. $17 c. $21 d. $89

b. $17

You deposit $500.00 in your bank account today. If the interest rate is 2%, and interest is paid once per year, how much would you have in your account after 2 years? a. $510.10 b. $520.20 c. $530.30 d. $540.40

b. $520.20

You have just won the state of Kentucky's lottery for $1 million. This amount will paid to you in instalments of $50,000 each year for the next 20 years. How much does the state of Kentucky have to put in the bank today to allow you to withdraw $50,000 per year, assuming a 6% interest rate? a. $1,000,000 b. $573,496 c. $15,590 d. $11,469,921

b. $573,496

Approximately how many years must one wait for an initial investment of $10,000 to triple in value if the investment earns 9% compounded annually? a. 13.75 b. 11.45 c. 14.25 d. 12.75

d. 12.75

If you wish to search for an exact match, you have to specify the range_lookup as FALSE.

True statement

CHOOSE function does what

This function chooses a value or action to perform from a list, based on an index number.

Functions can have arguments

True

Returns the date (in serial number or date format) of the date before (negative number) or after (positive number) a specified number of workdays

WORKDAY FUNCTION

The sum of $42,200 is invested at the end of every year for 25 years at a rate of 5% a year. What will be the value of this investment at the end of 25 years? a. $2,014,084 b. $1,234,412 c. $1,625,345 d. $1,912,823

a. $2,014,084

An employee's retirement account currently has a balance of $612,000. Suppose the employee contributes $3322 at the end of each month. The annual rate of interest is 5%. If interest is compounded monthly what will be the future value of the account in 15 years? a. $2,181,521 b. $1,592,880 c. $702,695 d. $887,933

a. $2,181,521

Allen wants to have a balance of $50,000 in his account at the end of ten years. If he will earn interest at 7% annually how much does he need to deposit today? a. $25,417 b. $27,197 c. $29,100 d. $31,137

a. $25,417

If the current value of an asset is $200000, what will be its value at a rate of 24% after 2 years? a. $307,520 b. $345,363 c. $126,933 d. $212,964

a. $307,520

Consider an investment that will pay $680 per month for the next 15 years and will be worth $28,000 at the end of that time. How much is this investment worth to you today at 5.25% annual discount rate? a. $97,351.34 b. $78,188.26 c. $81,523.12 d. $92,837.54

a. $97,351.34

Determine the NPV for a chemical plant project that costs $104,000 today. The plan will yield cash flows (at the end of each year) of $16,000 the first year, $18,000 the second year, $21,000 the third year, $23,000 the fourth year, $27,000 the fifth year, and $33,000 the sixth year. Your plant's cost of capital is 12.00% a. ($13,761) b. $90,239 c. $138,000 d. $34,000

a. ($13,761)

A project with a 12-year life and a cost of $250,000 generates revenues of $32,000 each year at the end of the year. If the discount rate is 8%, what is the NPV of the project? a. ($8,846) b. $8,458 c. $7,235 d. $9,732

a. ($8,846)

An annual payment of $500 is made at the end of each year. How many years, approximately, will it take for the investments to reach $35,000 if the rate of interest is 9.845%? a. 22 b. 17 c. 31 d. 28

a. 22

A project requires an investment of $100,000 on March 1, 1999 and will give returns as follows: $60,000 on August 31, 1999 and $35,000 on November 1, 1999. In addition to the original investment, $50,000 in capital was invested in the project on January 1, 2000. The equipment used in the project had a salvage value of $75,000 on April 15, 2000. Choose the closest rate of return from the following options. a. 26.05% b. 28.90% c. 23.20% d. 24.30%

a. 26.05%

Suppose a venture capitalist makes an investment of $100,000 on March 1, 1999. The business he invests in returns $60,000 on August 31, 1999, and $35,000 on November 1, 1999. Another $50,000 was invested by the venture capitalist on January 1, 2000. The investment is terminated on April 15, 2000 when the business pays the capitalist $75,000. Calculate the internal rate of return. a. 26.05% b. 27.01% c. 24.90% d. 21.56%

a. 26.05%

What is the internal rate of return on an investment with the following cash flows? 1/2/1990 (123,400) 9/15/1990 36,200 3/22/1991 54,800 10/24/1991 48,100 Choose from the following options a. a. 9.82% b. 10.12% c. 9.43% d. None of the above

a. 9.82%

What command is used to add the contents of cells A1, A2, and A3? a. =A3+A1+A2 b. =Add(A3:A3) c. =TOTAL(A3:A3) d. =PRODUCT(A3:A3)

a. =A3+A1+A2

CFO Azar is trying to decide whether to make an investment that requires an initial investment of $9 million. Investment A will return $923,812 at the end of each year for three years, and then $1,226,640, four times, once every 90 days. At the end of this period, CFO Azar can sell the assets of the company for $4 million. Should CFO Azar make the investment if the discount rate is 5%? (Hint: Count each year from the time of initial investment. If initial investment is on July 10, 2015, end of one year is July 10, 2015 + 365 days = July 9, 2016). a. Yes, the investment generates a profit of more than $900,000 b. Yes, the investment generates a profit of $192,412 c. No, the investment makes a loss of $78,212d. d: It makes no difference; the investment breaks even

a. Yes, the investment generates a profit of more than $900,000

At which of the following numbers does the clock reset to 12:00 AM? a. 1 b. 1.2 c. 1.4 d. 1.6

a: 1

Tom wants to open a company to manufacture and sell widgets. The initial investment is $5,000,000. He can earn $800,000 in the first year. He can earn $1,500,000 in the second year. After that, the number will increase 5% until year 5. What is the IRR in year 5? a. 4% b. 12% c. 8% d. 19%

b. 12%

How many months to pay off credit card debt of $4,000 paying $250 at the end of every month at 2% interest? a. 15.9 b. 16.2 c. 17.2 d. 12.5

b. 16.2

I have a name in a spreadsheet in cell A2. The name is in lower case letters. I want to convert the entire name into upper case. E.g., 'john doe' will become 'JOHN DOE'. How do I do this? a. =UpperCase(A2) b. =Upper(A2) c. =Ucase(A2) d. =Caps(A2)

b. =Upper(A2)

Max is scheduled to receive the following returns on the dates shown below. DATE AMT 1/7/15 $200 1/8/16 $300 1/9/17 $400 1/10/18 $500 1/11/19 $400 What is the present value of the investment, if the interest rate is 6.00%? a. $1,150.00 b. $1,775.00 c. $1,575.00 d. $1,350.00

c. $1,575.00

Tom invested $20,000 in a project on January 2, 2000. He got returns of $15,000 on February 4, 2001; $5000 on April 1, 2004; $8000 on October 9, 2006. Calculate the IRR. a. 22.23% b. 16.15% c. 12.12% d. 18.80%

c. 12.12%

In the Vlookup function, the 'range lookup' argument: a. Is optional b. Has a default value of TRUE c. Both a and b d. Neither a nor b

c. Both a and b VLookup function will automatically look for an approximate match. If you wish to search for an exact match, you have to specify the range_lookup as FALSE. In the VLookup function, the optional argument can be shown as TRUE or FALSE; it can also be indicated by 0 or 1.

Which of the following are valid outputs of recalc()? A: Automatic B: MANUAL C: Both a and b D: Neither A nor B

c: both a and b

Row function

cell reference is only argument and is optional

Column Function

cell reference is only argument and is optional.

How much will you have in your savings account at the end of five years if you deposit $1,000 now and it compounds at a rate of 1.5% quarterly? a. $1,364.86 b. $1,075.00 c. $1,338.23 d. $1,346.86

d. $1,346.86

A bond is currently selling for $1210. It pays the amounts listed below at the end of each year for the next 6 years. Year Payment Year 1 $100 Year 2 $100 Year 3 $100 Year 4 $100 Year 5 $100 Year 6 $1,200 What is the net present value if the rate is 7.8%? a. ($74.00) b. ($64.00) c. ($54.00) d. ($43.95)

d. ($43.95)

For a project the initial investment is $50,000. For five years, at the end of each year, the investment generates $8,000 in the first year, $10,000 in the second year, $13,000 in the third year, 16,000 in the fourth year and $18,000 in the fifth year. What is the internal rate of return for this project? a. 9.2% b. 6.8% c. 7.4% d. 8.2%

d. 8.2%

Consider the following series: a payment of $65,000 today (assumed as April 18, 1995), and cash inflows of $23,000 on August 1, 1995; $5,010 on September 15, 1996; $17,500 on January 23, 1997; $2,000 on April 1, 1998; and $13,400 on September 28, 1999. Find the present value of this series of payments at a discount rate of 5%. a. $124,768 b. $118,935 c. $120,924 d. None of these

d. None of these

What is the date 82 months before 03/01/15? A: 1/1/2008 B: 9/1/2008 C: 5/1/2008 D: 12/1/2008

edate function C: 5/1/2008

The information function

system: memory available workbook: path of save file cell / range: whether a cell is empty or data error: type of cell error

Whats an example of a function without an argument

the TODAY function

What does INFO Version tell you

the operating system.

What do lookup functions do?

to find some value in relation to other values in a dataset to identify the address of a cell in relation to other cells

Numbers in a string are treated as text and we cannot perform mathematical operations on them.

true

The DATE function has three required arguments: year, month, day. There are no optional arguments.

true

Vlookup enables us to look 'vertically' up or down a list and find a corresponding value.

true

In the VLookup function, the optional argument can be shown as TRUE or FALSE; it can also be indicated by 0 or 1.

true statement

The Now() function does not take any arguments, required or optional.

true.

Converts a serial number to a number representing where the week falls numerically with a year.

weekNUM