CSE200 SP07 KREEVES QUIZ#1 SEAT# ___________

CSE200 AU10 KREEVES QUIZ#3 SEAT# ____________

NAME _______________________________________________ Lecture: MW 8:30-10:18am

Lab section (check one): _______ R 9:30am-11:18am ________ R 11:30am-1:18pm

Instructions:

· Filling out the correct seat# and lab section on both the test and the answer sheet is worth 2 points.

· Turn off all beepers, cell phones and anything else that you can turn off that might make noise.

· Put away all books, papers, calculators, cell phones and music devices. There WILL BE a point penalization with the possibility of a score of ZERO for not adhering to these directions.

· When time has run out you will be told to put all pens/pencils down. There WILL BE a point penalization with the possibility of a score of ZERO for not adhering to these directions.

· Read each question carefully and fill in the answer in the space provided. Answers must be legible or they will be marked incorrect. If there are multiple answers to choose from, please CIRCLE the correct answer. The question will not be graded at all if there are multiple answers to choose from.

· Please stay in the answer box for each question.

· Use cell references whenever possible and appropriate.

· Don’t use a $ if NOT copying.

· Do NOT put quotes around Boolean values.

· Only use the functions given.

· Do not use unnecessary functions, for example =average(sum(a1:a5)). If you use an extra function that is not needed (the “average” function in the given example), it is assumed that you thought it was necessary thus will be marked incorrect.

· Caveat/Reminder to above: Do not use =IF(cond, TRUE, FALSE) as the IF structure in this case is unnecessary.

· Be sure to use values as determined by previous problems and do not use values from problems that have not yet been solved per the ordering of the questions.

· Your answer should update correctly when additional input data is added to the problem or when input data is changed.

· Do NOT use a nested IF function when a reference function will work since the reference function is a better more updatable less complex solution.

WORKSHEET DESCRIPTIONS

The Excel-lent summer job options file has all the summer job opportunities that you are considering as well as calculations that create new information to help you determine the best job from the list.

Q3cse –The input data is given for this worksheet in the range A4:C19. The purchase order number, description, and date are given for each supply/item ordered. The remaining information will be determined in the problems given below.

Q3supplies – This worksheet contains the number of days to reorder for each type of supply.

CSE200 AU10 KREEVES QUIZ#3 SEAT# ____________

NAME _______________________________________________ Lecture: MW 8:30-10:18am

Lab section (check one): _______ R 9:30am-11:18am ________ R 11:30am-1:18pm

Q#	PTS	MINUS	QUESTION and ANSWER
1	8		Write an Excel formula in cell Q3cse!D4, which can be copied down to cell Q3cse!D19, to determine the number of days to re-order the cse1 binder clips. NOTE: the number of days to reorder for each supply is given on the supplies worksheet. =VLOOKUP(B4,q3supplies!A$2:B$12,2,FALSE) Optional $ on column
2	8		Write an Excel formula in cell Q3cse!E4, which can be copied down and across to cell Q3cse!G19, to determine the date to re-order cse1 binder clips if they need to be re-ordered in 45 (see cell E3) days. Notice that a character dash is designated in a cell if that supply/item is not to be re-ordered in the timeframe given in E3:G3. =IF($D4=E$3,$C4+E$3,"-") =IF($D4<>E$3, "-", $C4+E$3) Okay if used $D4 instead of E$3 No optional $
3	8		The idea that most companies follow is that the more of a particular item you order, the better discount you get (i.e. the less you pay for each item). In this case, the more often that you re-order a supply/item, the better discount you get. Based on the days to reorder, this would mean that the lower the # days to reorder, the better discount you get. Write an Excel formula in cell Q3cse!H4, which can be copied down to cell Q3cse!H19, to determine the discount for the cse1 binder clips would receive based on the following: · 20% discount for less than 50 days to reorder · 5% discount for more than 200 days to reorder · Otherwise, the discount is 10%. =IF(D4<50,20%,IF(D4<=200,10%,5%)) =IF(D4<50,20%,IF(D4>200,5%,10%)) =IF(D4>200,5%,IF(D4<50,20%,10%)) =IF(D4>200,5%,IF(D4>=50,10%,20%)) Optional $ on column Ok if put .10 instead of 10% (etc)
4	8		Solve the above problem using a reference function. First, you will need to fill out the given Q3DISC worksheet cell references (given below), then write the function necessary to answer the above question. =HLOOKUP(D4,q3disc!B$1:D$2,2) Okay if D1 = 200 (question was a bit fuzzy!) Optional $ on column Optional TRUE 4^th argument
5	8		The college of engineering (COE) has decided to create their own office supply company to obtain supplies cheaper. After getting a business license, it’s just a matter of paying off the cost of having a business in order to at least break even. The estimated costs to start a business is $10,000. The savings per month for owning their own business instead of ordering from another supply company is 35% which amounts to an average of $300/month; this will be used to pay off the loan (i.e. the monthly payment) the COE received from the bank. How long will it take in years to break even and start actually saving money based on the fact that the loan has an annual percentage rate of 12% compounded monthly? NOTE: the answer is 3.4 years. =NPER(12%/12,-300,10000)/12 .12 okay instead of 12% No $ allowed (in this case designating money) No extra commas allowed i.e. 10,000 is invalid (i.e. an error)
6	8		After paying off the loan (above), how much will the COE make over the next 5 years now that they can invest that $300/month savings at an annual percentage rate of 10% compounded quarterly? NOTE: they will make (i.e. the answer is) almost $23,000 :o) *=FV(10%/4,54,-3003)* .10 okay instead of 10% No $ allowed (in this case designating money)

SCORE _____________/50