AU02 CIS200 MIDTERM KReeves
Name _____________________________ SS# ________________ Seat# _______ LabTime: _______
Instructions:
· WRITE YOUR NAME, SS# AND SEAT# ON THE TOP OF THIS PAGE
· WRITE YOUR NAME ON THE ANSWER SHEET OF THIS TEST.
· Put away all books, papers, and calculators. Turn off all beepers and cell phones.
· Read each question carefully and fill in the answer in the space provided. Answers must be legible or they will be marked incorrect.
· Use the back of this page as a scratch sheet of paper.
· When you finish your exam bring the exam to the front of the room.
· When time has run out you will be told to put all pens/pencils down. Failure to do so will result in point penalization.
*** For all questions use cell references and named ranges wherever possible.
PROBLEM DESCRIPTION
You work in the advertising department of the local newspaper. It is your job to collect the information for each ad which you have entered onto the DATA! worksheet and includes the following:
· the name of the person placing the ad (column A)
***** the order of DATA!A4:A11 will always be the same as the order of ANSWERS!A3:A10 *****
· type of the ad (column B)
· number of days the ad will run (column C)
· font size for each ad (column D)
· number of words per ad (column E)
· symbols types, if any, that are used (column F) – only one type of symbol/ad is allowed
· number of symbols used in the ad (column G)
· bolding the title costs $2.00 (H2)
· using colored ink for the ad costs $10.00 (I2)
· whether or not the ad wants to bold the title (column H)
· whether of not the ad uses colored ink (column I)
· whether or not the ad includes an image (column J) – only one image allowed per ad
· the length in inches of the image (column K)
Notice that if the data does not exist, the cell remains blank. For instance, Data!G6 has a blank for number of symbols because the symbol type is “none” and there are no symbols in this ad. Also, there are many blanks in column K because the particular ad does not have an image.
FYI: the input/given data is in gray.
On the ANSWERS! worksheet are the locations of most of the answers. The DATA! Worksheet also contains a few answers in cells DATA!F13:F15.
The SYMBOLS! worksheet describes the symbol types available, what each looks like, and the price for each symbol.
The first row of the FONTS! worksheet designates the font sizes available. Associated with each font size are the words per line that fit for the specified range of font sizes. That is, font sizes from 8 to less than 10 will fit 15 words per line. Font sizes from 10 to less than 14 will fit 12 words per line, etc.
The UNITS! worksheet details information used for units problems and other static data. The cell Units!B1 has a named range of “base” and Units!B2 has a named range of “cost” (you must use these named ranges instead of the worksheet!cellname).
Name _______________________________________________ Score _______________/250
1. ( 5 pts) Write an Excel formula in ANSWERS!B3 that can be copied down, to determine the number of words (which includes symbols) in the advertisement placed by the Kissing Bandit. Symbols are counted as words at this point, then are also given an additional charge based on the symbol type in Question#5 for ANSWERS!F3.
2. (15 pts) Write an Excel formula in ANSWERS!C3 that can be copied down, to determine the number of words per line. Remember that the number of words per line depends on the font.
3. (10 pts) Write an Excel formula in ANSWERS!D3 that can be copied down, to determine the number of lines in the advertisement. If the value that you obtain says that you need 4.3 lines, then you really need 5 lines; if the value you obtain says that you need 1.7 lines, then you really need 2 lines. To do this, you need to use a ROUNDUP function to obtain the correct value. The ROUNDUP function works exactly like the ROUND function with one difference. Instead of rounding to the “nearest” position designated by the second argument (i.e rounds up if 5 or greater otherwise rounds down), you round to the next highest position designated by the second argument as long as the rounding position is greater than zero. For example, the value 3.2 rounded up to the nearest whole number will roundup to 4 but the value 3.0 rounded up to the nearest whole number will return 3.
4. (25 pts) Write an Excel formula in ANSWERS!E3 that can be copied down, to determine the total cost of all the words using the named range “cost” (units!b2) and the following:
· if the font is less than or equal to 8, only 75% of the cost/line is charged per line
· if the font is greater than or equal to 14, 150% of the cost/line is charged per line
· otherwise, use the original value for cost/line
Don’t forget that part of the cost for the words in an advertisement includes the base cost of every advertisement (units!b1 defined to be a named range of “base”) as well as the cost for each line.
5. (25 pts) Write an Excel formula in ANSWERS!F3 that can be copied down, to determine the cost of the symbols used in the advertisement. Remember that the SYMBOLS! worksheet has the prices for each of the symbols.
6. (10 pts) I want to write an Excel formula in DATA!F13 to determine how many people placed an ad with symbols. Can I use =count(g4:g11) to do this (yes or no)?
7. (15 pts) Write an Excel formula in ANSWERS!G3 that can be copied down and across to ANSWERS!H10, to determine the cost of bolding the title for the advertisement (remember that not all advertisements request to have the title in bold… or the ad in color).
8. (15 pts) Write an Excel formula in DATA!F14 to determine if at least one pet and one personal ad have an image. For this problem and the next (Q#9), assume that the data in column B will not change.
9. (10 pts) Write an Excel formula in DATA!F15 to determine if only pet ads have color (assumption Q#8).
10. (20 pts) Write an Excel formula in ANSWERS!I3 that can be copied down, to determine the cost of an image which is measured in length only (remember that not all advertisements request an image).
11. (15 pts) Write an Excel formula in ANSWERS!J3 that can be copied down, to determine the total cost for the ad including the number of days the ad is to run.
12. (15 pts) Write an Excel formula in merged cells ANSWERS!B13:D13 that can be copied down, to determine the total cost spent for each type of advertisement (listed in column A13:A16).
13. (15 pts) Write an Excel formula in ANSWERS!E13 that can be copied down, to determine the average total cost for each type of ad.
14. (20 pts) The bank wants to encourage the newspaper to take longer to pay off their $425,000 loan for startup and equipment fees. In doing so, the bank has offered to reduce the interest rate (ANSWERS!G13:G15) compounded quarterly, based on an increasing time frame (H13:H15). Write a formula in ANSWERS!I13 that can be copied down, to determine the monthly payment for the rate of 11.5% and 10 years (remember to use cell reference whenever possible). Notice that the answer to this question has parenthesis around it which denotes a negative number.
15. (15 pts) Instead of paying out monthly payments to the bank for a loan (question#14), you invested that monthly payment amount for half the term designated in the years column at the same interest rate but compounded monthly (instead of quarterly as in the above problem). Write an Excel formula in ANSWERS!J13 that can be copied down, to determine if this would be a worthwhile investment. That is, output the Boolean value TRUE if the investment will be able to earn the borrowed amount of $425,000 or more, and FALSE if the investment will earn less than the borrowed amount.
16. (10 pts) I want to determine what the rate (answers!g13:g15) and years (answers!h13:h15) need to be to make the “worthwhile” column all go to true. What type of analysis is this called?
17. (10 pts) What type of chart is given below? What is the data range used to determine the chart information?
AU02 Midterm Reeves Page 4