Circle Lab Day/Time: Thur 3:30pm Fri 1:30pm

Instructions:

· -1 points for missing any name, seat# or lab information.

· Do not open your exam until it is time to begin.

· Put away all books, papers, and calculators. Hats on backwards!

· Write your name, lab day/time and seat # on the first page (above) AND on the Answer Sheet. ALL sheets must be turned in when handing in the exam or your exam will not be graded.

· Read each question carefully and fill in the answer on the answer sheet. Answers must be legible or they will be marked incorrect.

· You should only have one answer for each problem on your answer sheet. If you have more, be sure to circle the correct answer or it will not be graded!

· Please make sure you have all pages before you start this exam.

· If you have already determined the answer to a previous problem that can be used to solve a current problem, you must use the already solved problem in your answer.

· All answers given should be “updatable” unless otherwise noted.

· Do not use extra IF structures. For example IF(cond,true,false) when the condition would be the correct answer.

· Do not use data that has not been solved for yet.

· Please do not use functions not covered in class.

· Use cell references and named ranges whenever possible.

Excel Radio owns several radio stations. They are interested in keeping track of people, shifts and pay in relation to these radio stations. However, keep in mind that not all the data is shown on the given worksheets.

STATIONS worksheet à The given input data is in gray. FM stands for frequency modulation. FM stations are typically music and public radio stations The FM broadcast band is 88 MHz to 108 MHz. AM stands for amplitude modulation. AM stations typical consist of talk and news radio. The AM broadcast band is a medium wave from 530 to1710 kHz.

PEOPLE worksheet à The given input data is in gray. Notice that the shift data (column C) also exists on the SHIFTS worksheet, and that the pay grade column (column D) also exists on the PAY worksheet.

SHIFTS worksheet à The given input data is in gray. Based on the format of the start and end times, you can subtract the start time from the end time. However, the value comes out as a percent of a 24 hour period which also implies that this value is a fractional portion i.e. less than one. For example, if the difference between two times is 6 hours, then the result of the end-time minus the start-time equation would be .25 or 25% of a 24 hour period. In order to get the answer to compute to 6 hours, which is what we are looking for, you need to multiply the .25 by 24.

UNITS worksheet à All of this data is given. Excel radio assumes that all employees will work 5 days per week (units!B1). The other two values are given for conversion factor purposes. Remember to use cell references whenever possible!

PAY worksheet à All of this data is given. A named range for PAY!B1:F3 has been defined called PAYINFO which must be used whenever you are accessing data on this worksheet.
EXCEL PROBLEMS

(12 points) 1. Write an Excel formula in cell Stations!E3, that can be copied down to E18, to determine if the dial# is an AM or FM station.

(15 points) 2. Write an Excel formula in cell Stations!G5 to determine (true/false) if the person listening (column D) only listens to country stations NOTE: This problem is “not updatable” which means that the answer does not have to work if the worksheet is updated.

(12 points) 3. Write an Excel formula in cell Stations!H12, which can be copied down to H16, to determine the number of radio stations that play Christian music.

(12 points) 4. Write an Excel formula in cell Stations!I12, which can be copied down to I16, to determine what percent of the Excel Radio stations play Christian music.

(8 points) 5. Write an Excel formula in cell Stations!I17 to check that my calculations from the previous problem are correct by making sure the total percent of all the different types of radio stations adds up to 100%.

(8 points) 6. In cell I18, I added up the values in the range Stations!I12:I16 by hand but did not get the same answer as shown in cell I17. Explain why this happened.

(20 points) 7. Write an Excel formula in cell People!G2, which can be copied down to G14 to determine if Jack is eligible for any additional pay:

“bonus” – if the person is a salaried employee, no matter what the other data may say, then a bonus is given; even if the person also qualifies for overtime, if they are salaried, they will get a bonus not overtime pay.
“otime” which stands for overtime – if the person has a paygrade between 3 and 10 inclusive and works either shift A or E, and is NOT salaried.
“none” – if neither of the above two options apply.

(12 points) 8. Write an Excel formula in cell Shifts!D2, which can be copied down to D6, to determine the number of hours, rounded to the nearest hour, for shift A. NOTE: be sure you have read the paragraph description about the SHIFTS worksheet on page 2 of this exam.

(35 points) 9. Write an Excel formula in cell People!H2, which can be copied down to H14, to determine how much Jack gets paid per shift. Hint: some people are salaried and some people are hourly.

(15 points) 10. Write an Excel formula in cell People!I2, which can be copied down to I14, to determine the yearly pay for Jack. Hint: You already have the pay/shift which now needs to be converted to pay/year. Note: Each person works only one shift on each work day.

(15 points) 11. Write an Excel formula in cell Shifts!E2, which can be copied down and across to F6, to determine the total cost of all people working the A shift.

(8 points) 12. Write an Excel formula in cell Shifts!D7, which can be copied across to F7, to determine longest shift at a radio station.

(8 points) 13. Given the chart below, determine what type of chart it is and what range(s) was (were) used to plot the data.

(16 points) 14. Write an Excel formula in cell People!I16 to determine (true/false) if the males at Excel Radio earn a higher average yearly pay than the females.

(12 points) 15. When opening a new radio station, Excel Radio needs to buy $10,000 worth of equipment. A 5-year loan is taken out to pay up front for this equipment at 10% interest compounded yearly. What is the yearly payment amount for this loan?

(25 points) 16. A $1,000,000 donation was given to Excel Radio and the owner of Excel Radio wants to use this money for some of the monthly costs. Currently, the monthly payments include a total rent amount for all the stations of $800 as well as the monthly pay of all the employees (i.e. people). How many years will the $1,000,000 last if it’s used to pay for these monthly costs? Assume the interest rate is 6% compounded monthly.

(8 points) 17. Explain what the possible values are for the type argument of a financial function and the meaning of each.

(8 points) 18. I want to see what will happen if each employee were to work 6 days a week instead of 5 and how it would affect their yearly pay. What would need to be changed and would this be considered a goal-seek or a what-if type of problem? Explain your answer.

Name: ________________________ Lab Day/Time ______________ Seat# ________

Answer Sheet CSE 200 Midterm WI06 TR 1:30-3:18pm Lecture Class

(-)	Pts	#	Answer:
	12	1.	=IF(B3>108,"AM","FM") =IF(B3<=108,"FM","AM") \
	15	2.	=AND(OR(D8,D9,D12,D17), NOT(OR(D3:D7,D10:D11,D13:D16,D18)))
	12	3.	=COUNTIF(C$3:C$18,G12)
	12	4.	=H12/COUNT(B$3:B$18)
	8	5.	=SUM(I12:I16)
	8	6.	The 100% is generated by summing up the precision values The 101% is generated by summing up the formatted values
	20	7.	=IF(F2,"bonus",IF(AND(D2>=3,D2<=10,OR(C2="A",C2="E")),"otime","none")) =IF(AND(D2>=3,D2<=10,OR(C2="A",C2="E"),NOT(F2)),”otime”,IF(F2,”bonus”,”none”))
	12	8.	=ROUND((C2-B2)*24,0)
	35	9.	=IF(F2,HLOOKUP(D2,payinfo,3,TRUE), HLOOKUP(D2,payinfo,2)*VLOOKUP(C2,shifts!A$2:D$6,4,FALSE))

(-)	Pts	#	Answer:
	15	10	=H2units!B$1units!B$3
	15	11	=SUMIF(people!$C$2:$C$14,$A2,people!H$2:H$14) No optional $
	8	12	=MAX(D2:D6)
	8	13	Line chart =shifts!$A$1:$A$6,shifts!$F$1:$F$6
	16	14	=SUMIF(B2:B14,"M",I2:I14)/COUNTIF(B2:B14,"M") > SUMIF(B2:B14,"F",I2:I14)/COUNTIF(B2:B14,"F")
	12	15	=PMT(10%,5,10000)
	25	16	=NPER(6%/12,-(800+SUM(I2:I14)/12),1000000)/12
	8	17	0 and 1 are the 5^th argument options for financial functions 0 = compounding done at the end of the period (default) 1 = compounding done at the beginning of the period
	8	18	UNITS!B1 is changed from 5 to 6. A what-if analysis is done since don’t know what the goal is, but trying to update one of the input values to check the output change that resulted; in this case, the yearly pay (goal seek is knowing the resulting value and asking what the input value should be to get that result).

Points: ____ /250