4. A university begins Year 1 with 80 faculty. They hire 4 faculty each year. During each year 10% (rounded to the nearest integer) of the faculty present at the beginning of the year leave the university. For example, in a year where there are 73 faculty at the beginning of the year, 7 would leave the university at the end of the year. The university wants to know how many faculty they will have at the end of year 10. The resulting spreadsheet can be found below. The formula copied down from E10 to E11:E18 was ___________.
Note: If the answer is a formula, there is no need to begin that answer with =. Please do not put any unnecessary spaces or () in your response.
Note: If the answer is a formula, there is no need to begin that answer with =. Please do not put any unnecessary spaces or () in your response.
16. You would like to show how the number of faculty at the end of year 10 depends on the quit rate and number of annual hires. We would like for example, to have cell L10 give the number of Year 10 faculty if we hire 1 faculty member a year and 10% quit each year. Using the information in the spreadsheets below, what would be the row input cell for a two-way data table? Note: If the answer is a formula, there is no need to begin that answer with =. Please do not put any unnecessary spaces or () in your response.
20. Consider the cash flows seen here. If we discount cash flows at 10% per year, the Net Present Value (rounded to the nearest dollar) of these cash flows as of 4/10/2011 is _____. (Note that date is in MM/DD/YYYY format.)
22. Suppose you have listed Le Napoleon’s monthly sales of pear tortes in a twelve-sheet workbook. The first worksheet contains January sales, the second worksheet February sales, etc. The pear torte sales are always listed in cell F7. What multi-sheet (three-dimensional) formula would give the total pear torte sales for the months March- July, inclusive? Assume that each worksheet is named with the month name only, spelled out in full. Include the equal sign at the beginning—for example, =SUM(A1:A24)—but do not include any unnecessary spaces or parentheses in your response. Also do not anchor any cells—i.e., do not use any $ characters.
23. A company has revenue of $1000 in 2009. Our current estimate is that revenues will grow 25% per year. Our profit each year will equal 20% of revenue. What is the total value (rounded to the nearest dollar) of all revenues earned during the years 2009-2015? Enter just the number; e.g., 18491.
26. Four relatives are going to give you money for your birthday. The amount each relative gives you is equally likely to be any number between $0 and $200. The chance that the total amount of money you receive is at most $300 is ______.
Select the number closest to the following choices. •0.10 •0.20 •0.30 •0.40
Select the number closest to the following choices. •0.10 •0.20 •0.30 •0.40
27. In the spreadsheet shown below, we computed profit earned by selling houses by entering the formula =F8-E8 in cell G8 and copying it down to G9:G11. We used the word open to denote a house that is not yet sold. Unfortunately, when a house has not yet been sold our spreadsheet shows the #VALUE error, because you cannot subtract the buy price from the word open. We would like to “error trap” Column G so that when a house has been sold the correct profit has been computed and if the house has not yet been sold a _ will be entered in Column G. How many of the following formulas could be entered in cell G8 and copied down to G9:G11 to achieve this goal?
• I. IFERROR(F8-E8,”_ “) • II. ISERROR(F8-E8,” _”) • III. IF(COUNT(G8)>0,F8-E8,”_”) • IV. IFERROR(F8-E8,_) • V. IFERROR(E8-F8,”_ “)
•Select the correct answer below: •Just I •Just I and II •Just III •Just I and III •Just IV •Just I and IV •Just I and V
• I. IFERROR(F8-E8,”_ “) • II. ISERROR(F8-E8,” _”) • III. IF(COUNT(G8)>0,F8-E8,”_”) • IV. IFERROR(F8-E8,_) • V. IFERROR(E8-F8,”_ “)
•Select the correct answer below: •Just I •Just I and II •Just III •Just I and III •Just IV •Just I and IV •Just I and V
28. See the screenshot below. What function can be used in cell E5 to find the unit sales of the person selected in cell E4? (Assume that the range A1:B20 has been named lookup.)
•Select one of the following answers:
•=LOOKUP(E4,lookup,B,TRUE) •=LOOKUP(E4,lookup,B,FALSE) •=VLOOKUP(E4,lookup,2,TRUE) •=VLOOKUP(E4,lookup,2,FALSE) •=HLOOKUP(E4,lookup,2,TRUE) •=HLOOKUP(E4,lookup,2,FALSE)
•Select one of the following answers:
•=LOOKUP(E4,lookup,B,TRUE) •=LOOKUP(E4,lookup,B,FALSE) •=VLOOKUP(E4,lookup,2,TRUE) •=VLOOKUP(E4,lookup,2,FALSE) •=HLOOKUP(E4,lookup,2,TRUE) •=HLOOKUP(E4,lookup,2,FALSE)
31. Cell F7 tells whether or not we own or not own a stock at the beginning of a day Cell G7 gives the price of the stock at the end of the day. We decide whether to buy the stock, sell the stock or take no action based on the following rule: • If end price >=$50 and we own stock we will sell. • If end price <=$35 and we do not own the stock we will buy. • Otherwise we take no action. Choose the formula that will correctly implement this strategy.
•Select one of the following answers:
•IF(AND(F7=”not own”,G7>=50),”sell”,IF(AND(F7=” own”,G7<=35),”buy”,”none”)) •IF(AND(F7=”own”,G7>=50),”sell”,IF(AND(F7=”not own”,G7<=35),”buy”,”none”)) •IF(AND(F7=”own”,G7>=50),”sell”,IF(AND(F7=”not own”,G7<=35),”sell”,”none”)) •IF(F7=”own”,G7>=50),”sell”,IF( (F7=”not own”,G7<=35),”buy”,”none”)) •None of the above
•Select one of the following answers:
•IF(AND(F7=”not own”,G7>=50),”sell”,IF(AND(F7=” own”,G7<=35),”buy”,”none”)) •IF(AND(F7=”own”,G7>=50),”sell”,IF(AND(F7=”not own”,G7<=35),”buy”,”none”)) •IF(AND(F7=”own”,G7>=50),”sell”,IF(AND(F7=”not own”,G7<=35),”sell”,”none”)) •IF(F7=”own”,G7>=50),”sell”,IF( (F7=”not own”,G7<=35),”buy”,”none”)) •None of the above
36. Refer to the screenshot below. Which of the following is another way of retrieving the appropriate value for unit sales, given a name entered in cell E4?
•Select one of the following answers:
•=INDEX(B1:B20, MATCH(E4,A2:A20,0)) •=INDEX(B2:B20, MATCH(E4,A2:A20,0)) •=INDEX(B1:B20, MATCH(E4,A2:A20,1)) •=INDEX(B2:B20, MATCH(E4,A2:A20,1)) •=MATCH(E4,INDEX(B1:B20),0) •=MATCH(E4,INDEX(B2:B20),0) •=MATCH(E4,INDEX(B1:B20),1) •=MATCH(E4,INDEX(B2:B20),1)
•Select one of the following answers:
•=INDEX(B1:B20, MATCH(E4,A2:A20,0)) •=INDEX(B2:B20, MATCH(E4,A2:A20,0)) •=INDEX(B1:B20, MATCH(E4,A2:A20,1)) •=INDEX(B2:B20, MATCH(E4,A2:A20,1)) •=MATCH(E4,INDEX(B1:B20),0) •=MATCH(E4,INDEX(B2:B20),0) •=MATCH(E4,INDEX(B1:B20),1) •=MATCH(E4,INDEX(B2:B20),1)
37. Suppose cell B10 contains the price of a stock in 5 months. Consider a 5 month European call option with an exercise price of $40. The value of the call option in 5 months is $0 if the stock price in 5 months is <=$40. Otherwise the value of the call option in 5 months is the Stock Price in 5 months – 40. How many of the following formulas will yield the correct value of the call option?
• I. IF(B10>40,40-B10,0) • II. IF(B10>40, B10-40,0) • III. MAX(B10-40,0) • IV. MIN( 0, 40-B10)
•Select one of the following answers: •I •I and II •II and III •I and III •IV •I and IV
• I. IF(B10>40,40-B10,0) • II. IF(B10>40, B10-40,0) • III. MAX(B10-40,0) • IV. MIN( 0, 40-B10)
•Select one of the following answers: •I •I and II •II and III •I and III •IV •I and IV
39. A company produces 3 products that use labor and raw material. The labor usage, raw material usage, and profit per pound produced of each product are seen below. 500 hours of labor and 300 pounds of raw material are available. If fractional pounds of each product can be produced, what is the maximum profit the company can earn (rounded to the nearest dollar)?
Assume demand for each product is unlimited. Enter just the number; e.g., 450.
Assume demand for each product is unlimited. Enter just the number; e.g., 450.
40. The value in cell A1 of a spreadsheet is needed to compute the value in cell E3. The value in cell E3 is needed to compute the value in cell E10. The value in cell E10 is needed to compute the value in cell A1. This spreadsheet contains a __________ reference.
41. A company produces products at 2 plants each of which have a capacity of producing 75 units. 50 units of each product must be shipped to each of three customers. We know the cost of shipping a unit of the product from each plant to each customer. Our goal is to minimize the total cost of shipping the needed units to the customers. If we use the Excel Solver to try and minimize the total cost of meeting customer demand we will need how many variable cells?
•2 •6 •3 •None of the above
•2 •6 •3 •None of the above
42. You have been handed a spreadsheet that contains a formula in cell D10 that computes Year 10 profits. You want to display all cells that are used to compute Year 10 Profits. You should select which tool from the Ribbon (two-word answer)?
43. You are working with a file and want to save it as a .csv file, rather than .xlsx. Where can you click to access this option?
•A. The File tab •B. The Save icon •C. The Data tab •D. The Review tab •E. The View tab •F. The Add-Ins tab
•A. The File tab •B. The Save icon •C. The Data tab •D. The Review tab •E. The View tab •F. The Add-Ins tab
44. Suppose we want to highlight the year number for each year during which sales were at least 5% higher than the previous year. We would begin by selecting the cell range D6:D12 and choose Conditional Formatting from the Ribbon. Then you would select new rules followed by the Use a Formula option. Finally you would enter the following formula.
•(E5/E4)>1.05 •($E$6/E5)>1.05 •(E6/E5)>1.05 •(E6/E5)>.05
•(E5/E4)>1.05 •($E$6/E5)>1.05 •(E6/E5)>1.05 •(E6/E5)>.05
Select one of the answers below:
•Demand…Elasticity •Price…Elasticity •Demand…Price •Demand…Variable Cost
45. Based on the influence chart below, in order to compute revenue, it is sufficient to know the values of _____ and _____.
•Demand…Elasticity •Price…Elasticity •Demand…Price •Demand…Variable Cost
45. Based on the influence chart below, in order to compute revenue, it is sufficient to know the values of _____ and _____.
50. Refer to the screenshot below. Suppose we want to use the Subtotal feature to create a summary of our sales data that lists the total units sold by each salesperson in January, followed by the total units sold by each salesperson in February, etc. What sort order should be implemented before running the subtotals?
•Sort by Month •Sort by Name •Sort by Units Sold •Sort by Name, then Month •Sort by Month, then Name •Sort by Units Sold, then Month
•Sort by Month •Sort by Name •Sort by Units Sold •Sort by Name, then Month •Sort by Month, then Name •Sort by Units Sold, then Month
51. An airline sells 120 tickets for a flight that seats 100. Each ticket is non-refundable and costs $200. The unit cost of flying a passenger (fuel, food, etc.) is $80. If the flight is overbooked, each person who does not find a seat is given $300 in cash. Assume it is equally likely that any number of people between 91 and 120 show up for the flight. Rounded to the nearest thousand (e.g., 18500 rounds to 19000), on the average how much expected profit (ignoring fixed cost) will the flight generate? Enter just the number; e.g., 11000.
52. Refer to the screenshot below. What function can be used in cell G3 (and then copied through the range G3:L9) to summarize the total number of transactions (assume that each row in the dataset represents one transaction) by month and salesperson? Again assume that the three columns of the dataset have been named Name, Month, and Units_Sold.
•=SUBTOTAL(Name,$F3,Month,G$2) •=SUBTOTAL(Name=$F3,Month=G$2) •=COUNTA(Name,$F3,Month,G$2) •=COUNTA(Name=$F3,Month=G$2) •=COUNTIF(Name,$F3,Month,G$2) •=COUNTIF(Name=$F3,Month=G$2) •=COUNTIFS(Name,$F3,Month,G$2) •=COUNTIFS(Name=$F3,Month=G$2)
•=SUBTOTAL(Name,$F3,Month,G$2) •=SUBTOTAL(Name=$F3,Month=G$2) •=COUNTA(Name,$F3,Month,G$2) •=COUNTA(Name=$F3,Month=G$2) •=COUNTIF(Name,$F3,Month,G$2) •=COUNTIF(Name=$F3,Month=G$2) •=COUNTIFS(Name,$F3,Month,G$2) •=COUNTIFS(Name=$F3,Month=G$2)
53. You would like to show how the number of faculty at the end of year 10 depends on the quit rate and number of annual hires. We would like for example, to have cell L10 give the number of Year 10 faculty if we hire 1 faculty member a year and 10% quit each year. Using the information in the spreadsheets below, what would be the column input cell for the two-way data table?
Please do not put any unnecessary spaces or () in your response.
Please do not put any unnecessary spaces or () in your response.
55. Based on the influence chart below, what are the decision variable(s) for this model?
•Price and elasticity •Price and fixed cost •Price •Elasticity and fixed cost
•Price and elasticity •Price and fixed cost •Price •Elasticity and fixed cost
56. See the screenshot below. What function can be used in cell G3 (and then copied through the range G3:L9) to summarize the total number of units sold by month and salesperson? Assume that the three columns of the dataset have been named Name, Month, and Units_Sold.
•=SUBTOTAL(9,Units_Sold,Month,G$2,Name,$F3) •=SUBTOTAL(9,Units_Sold,Month=G$2,Name=$F3) •=SUMIF(Month,G$2,Name,$F3,Units_Sold) •=SUMIF(Month=G$2,Name=$F3,Units_Sold) •=SUMIFS(Units_Sold,Month,G$2,Name,$F3) •=SUMIFS(Units_Sold,Month=G$2,Name=$F3) •=AUTOSUM(Units_Sold,Month,G$2,Name,$F3) •=AUTOSUM (Units_Sold,Month=G$2,Name=$F3)
•=SUBTOTAL(9,Units_Sold,Month,G$2,Name,$F3) •=SUBTOTAL(9,Units_Sold,Month=G$2,Name=$F3) •=SUMIF(Month,G$2,Name,$F3,Units_Sold) •=SUMIF(Month=G$2,Name=$F3,Units_Sold) •=SUMIFS(Units_Sold,Month,G$2,Name,$F3) •=SUMIFS(Units_Sold,Month=G$2,Name=$F3) •=AUTOSUM(Units_Sold,Month,G$2,Name,$F3) •=AUTOSUM (Units_Sold,Month=G$2,Name=$F3)