CIS200 – Homework #1 – Simple Formulas & Functions
CIS 101 - Autumn 00
Final Exam KEY
Points ____________/300
Name _______________________________________________
SS# _____________________ Lecture Time:_________________
Instructions:
• Fill in your name, ss#, and seat number on both the question sheet and answer sheets.
• Do not open your exam until it is time to begin. You will have 1hour and 48 minutes to complete this exam.
• Put away all books, papers, and calculators.
• Read each question carefully and fill in the answer in the space provided (next to the question). Answers must be legible or they will be marked incorrect.
• There are 11 pages in this exam - this sheet, 8 pages of questions plus two pages for spreadsheets and database tables.
• When you finish your exam bring it to the front of the room – all pages must be turned in. Failure to do so will result in a penalty. You may tear off the spreadsheet and table page in order to work – be sure to re-attach it when handing it in.
• When time has run out you will be told to put all pens/pencils down. Failure to do so will result in point penalization.
• It is required that you do not discuss this exam with any other student.
Functions - to be used for Reference:
Financial Functions
FV(rate, nper, pmt, pv, type)
NPER(rate, pmt, pv, fv, type)
PMT(rate, nper, pv, fv, type)
PV(rate, nper, pmt, fv, type)
RATE(nper, pmt, pv, fv, type)
Logical Functions
AND(logical1, logical2,…)
IF(logical_test,value_if_true, value_if_false)
NOT(logical)
OR(logical1, logical2,…)
Other Functions
AVERAGE(number1, number2, …)
COUNT(value1,value2, ...)
COUNTIF(range, criteria)
MAX(number1, number2, …)
MIN(number1, number2, …)
SUM(number1, number2, …)
SUMIF(range, criteria, sum_range)
ROUND(number,num_digits)
Lookup(lookup_value, lookup_vector, value_result_vector)
General Topics/Short Answers (all questions are 2 points each):
Multiple Choice – Fill in the letter of the item which best completes each statement. A space is provided to the left of each question.
_A____1. Protocol on the internet refers to ____.
a. rules of communication
b. backbone architecture of a WAN
c. HTTP protocol
d. none of the above
__C___2.When setting up a relationship between two tables you create a ______.
a. field property
b. auxiliary key
c. foreign key
d. primary key
___D__3. A ______ is a field on a table for which a particular value should give a unique record
a principle key
b field property
c. foreign key
d. primary key
__B___4. To change the toolbars which appear in Word , Excel, etc , you should select the following menus ______.
a. Edit –Toolbars
b. View – Toolbars
c. Insert-Toolbars
d. Tools
___C__5. A record is composed of data in a series of categories referred to as ______.
a. databases
b. files
c. fields
d. rows
__B___6.When you close a document in a windows application ____.
a. it is erased from hard drive disk
b. it is removed from screen
c. both a and b
d. None of the above
___A__7. Use the _______ in Access to modify a query
a. Design view
b. Data view
c. Print Preview
d. none of the above
__C___8. Use the ______ application when managing very large tables of related data
a. Word
b. Excel
c. Access
d. PowerPoint
___D__9. In order of precedence, the _____ is/are evaluated first.
a. + and – operations
b. left most operations
c. * and / operations
d. expressions in ( )
_D__10. A query in access is capable of the following ______.
a. summarizing data by group
b. performing calculations on selected fields
c. selecting specific records
d. all of the above
| |A |B |C |D |
|1 |4 |8 |A1/A$3 | |
|2 |6 |4 | | |
|3 |A1+A2 |1 | | |
|4 | | | | |
_A____11. Using the table above, what value would result in cell D2 if you copy the formula from cell C1 to D2?
a. 4
b. B2/B$4
c. B2/A$3
d. 0.4
__C____12. Using the table above, what is the value of the formula B2>A1
a. #N/A
b. TRUE
c. FALSE
d. 0
Fill in the Blanks
13. This function returns TRUE if one of its arguments is TRUE. ___________OR__
14. Copying the formula =A$1*$B4+B2 in cell C2 to cell E5 results in the formula ____C$1*$B7+D5______
15. What is the name of the tool available in Excel which does the following: _________Goal Seek_______
• allows you to specify a desired result for a calculation
• allows you to specify a variable (cell reference) you wish to change to get that result
• returns the appropriate value for this variable that will ensure your desired result
16. Name the tool available in MS Word which allows you to write a form letter which includes selected fields from a database. This form can then be used to produce multiple letters with the data from the specified fields filled in for
the appropriate record. ___________Mail Merge_
17. Yesterday you placed an Excel spreadsheet into your Word document. This morning you updated the Excel spreadsheet. You now open the Word document and notice that the data in the Word document already includes the changes you made this morning. What feature did you use when copying this data?
______________________Object Linking_______
True or False
Circle T or F for each of the statements below.
18. T F. The clipboard is erased when information is pasted into an application -- F
19. T F To change the precision of a value in Excel you can modify cell display -- F
20. T F A query can refer to more than one table -- T
21. T F The Mail Merge feature in Word will only work with an Access database. -- F
22. . T F It is possible to copy, delete and move files from Windows Explorer. -- T
23. . T F A byte is 10 bits of data -- F
24. T F .A monitor is an input device -- F.
25. T F In a Windows operating system it is only possible to open one application at a time. -- F
Excel Problem Description (100 points):
The spreadsheets for this problem can be found at the back of this exam. Use cell references whenever possible in your answers.
You are going to complete a retirement analysis for the employees of XYZ Corporation. You will be determining each employee’s current annual salary, whether or not they are eligible to retire, and if they are eligible their yearly pension. To be eligible for retirement an employee must be at least 65 years old and have a minimum of 25 years of service. The yearly pension is 50% of the current annual salary. An employee’s salary is based on his or her salary code.
• On sheet salary! there is information regarding monthly salary for a particular salary code.
• On sheet req! there is information on the company’s retirement qualification requirements and the pension due qualified employees as a percentage of annual salary.
• On sheet analysis! is a listing of your employees and their personal information.
1. (15 points) Write a formula in cell analysis!F2, which can be copied down the column to determine the employee’s current annual salary.
_______=LOOKUP(E2, SALARY!A$2:A$7, SALARY!B$2B$7)* REQ!B$6____
2. (10 points)Write a formula in cell analysis!G2, which can be copied down the column, to determine (True/False) if the employee is eligible for retirement.
___________=AND(B2>=REQ!B$3, D2>=REQ!B$4)______________
3. (15 points) Write a formula in cell analysis!H2, which can be copied down the column, to determine the employee’s annual pension after retirement. If an employee is ineligible for retirement the pension amount should be $0 (displayed as “-“ in currency format).
__________IF(G2, F2*REQ!B$5, 0)__________________________
4. (5 points) Write an Excel formula to determine if (True/False) at least one person is eligible for retirement. Use a Boolean function.
________________=OR(G2:G9)___________________________________
5. (10 points) Write an Excel formula to determine how many people are ineligible to retire at the current time?
_______________=COUNTIF(G2:G9, FALSE)___________________________________________
6. (5 points) Write a formula to determine the average annual salary for employees.
_______________________=AVERAGE(F2:F9)_________________________________________
7. (5 points) Write a formula to determine the number of years of service for the employee with the least seniority.
_________________=MIN(D2:D9)_________________________________________
8. (10 points) Jenkins started a college fund for his grandson 18 years ago by investing $200 dollars per month at 9% per year compounded monthly. How much is available for his grandson now?.
_________________=FV(9%/12, 18*12, -200, 0)__________________________________
9. (10 points) Hawkins has decided to buy a new sports car which costs $90,000. She is putting down 20% and financing the rest with a 7% per year loan over the next 5 years. The loan is compounded monthly. What is the monthly payment she will need to make?
____________________=PMT(7%/12, 5*12, 90000- 20%*90000, 0)____________________
10. (15 points) Smith is saving for her retirement. She wants to determine how much she needs to set aside today in order to have $100,000 at the age of 65. She intends to put this money in a money market account which pays 8% per year compounded quarterly. She will be depositing an additional $150 per quarter into this account. Write an Excel formula to determine her investment today to reach this goal.
______________=PV(8%/4, (65-B2)*4, -150, 100000)____________________
Access Problem Description (150 points):
Answer the following questions based on the database provided at the end of this exam (Airline database):
These tables show part of the database for the USA Travel Agency. The “Airlines” table contains the details of the various airlines that it deals with. The “flights” table lists the details of flights that these airlines fly on. The details of passengers confirmed are in the Passengers table. The waitlisted passengers are given in the “Waitlisted” table
1. Draw the relationship diagram among the tables. Indicate the primary key(s) – if any, and the foreign keys that relate the tables. (25)
2. You are doing an analysis of carriers who fly to Columbus. Write a query to list the names of Airlines flying to Columbus. (15)
Tables Required: ______Arline, flights___________
Join on (foreign keys):_______Airline___________
|Field |Name |To | | | |
|Table |Airline |Flights | | | |
|Total | | | | | |
|Sort | | | | | |
|Show |X | | | | |
|Criteria | |“Columbus” | | | |
|OR | | | | | |
3. American and Delta use the same caterer. This caterer needs a list of the Vegetarian passengers (Veg) on Delta Airlines flights and on American Airlines. Write a query to list those names for the caterer. Include the passenger’s Last name, flight number and the airline they are flying. (25 points)
Tables Required: ____________Passengers, Airline, Flights________________________________
Join on (foreign keys):___________Flight, Airline___________________________
|Field |Last Name |Flight |Name |Meal Pref | |
|Table |Pass |Pass |Airline |Pass | |
|Total | | | | | |
|Sort | | | | | |
|Show |X |X |X | | |
|Criteria | | |“Delta” OR “American” |“Veg” | |
|OR | | | | | |
4. TWA has asked you to prepare a list the passengers on TWA flights. The list should include the passenger’s last name, confirmation number and flight number. Sort the list (highest first) by flight number, then alphabetically by passenger’s last name. (25)
Tables Required: ________Airline, Passenger, flights_________________________________
Join on (foreign keys):_____Flight, Airline_______________________________________
|Field |flight |Conf no. |Last Name |Name | |
|Table |Pass |Pass |Pass |Airline | |
|Total | | | | | |
|Sort |Descending | |Ascending | | |
|Show |X |X |X | | |
|Criteria | | | |“TWA” | |
|OR | | | | | |
5. As part of your industry analysis you would like to compile a list summarizing the number of passengers for each flight. You list should include the flight number and total of passengers on that flight. (20 points)
Tables Required:_________Passenger____________________
Join on (foreign keys):______________________________________________________
|Field |Flight |Conf No. | | | |
|Table |Pass |Pass | | | |
|Total |Group By |Count | | | |
|Sort | | | | | |
|Show |X |X | | | |
|Criteria | | | | | |
|OR | | | | | |
6. In this query you will try listing all passengers whose last name begins with in the letter M or D. (20 points)
Tables Require: __________Passenger_________________
Join on (foreign keys):________________________
|Field |Last Name | | | | |
|Table |Pass | | | | |
|Total | | | | | |
|Sort | | | | | |
|Show |X | | | | |
|Criteria |Like “M*” OR Like “D*” | | | | |
|OR | | | | | |
7. Using the results of query 5 and any other tables – create a list that includes the flight number and total dollar amount of revenue (ticket price times number of passengers on that flight) (20 points)
Tables Require: _________Query 5, Flights_____________
Join on (foreign keys):_______flight_______
|Field |Flight |Revenue * | | | |
|Table |Query 5 / Flights | | | | |
|Total | | | | | |
|Sort | | | | | |
|Show |X |X | | | |
|Criteria | | | | | |
|OR | | | | | |
Additional room for calculations if required:
*Revenue : [Query 5] ! [Count Of Conf No] * [Flights] ! [Ticket Price]
USA Travel Agency Database
The following tables are part of the database for the USA Travel Agency. The “Airlines” table contains the details of the various airlines that it deals with. The “flights” table lists the details of flights that these airlines fly on. The details of passengers confirmed are in the Passengers table. The waitlisted passengers are given in the “Waitlisted” table.
Airlines
|Airline# |Name |
|1 |Delta |
|2 |United |
|3 |American |
|4 |USAir |
|5 |TWA |
Flights
|Flight No.|Airline |From |To |Model |Departure |Arrival |Ticket Price |Available |
|6785 |4 |Pittsburg |Seattle |737 |19.40 |23.50 |$489 |No |
|8888. |5 |Pittsburg |Seattle |737 |9:25 |10:25 |$525 |Yes |
|6754 |3 |New York |LA |747 |17:35 |20:53 |$399 |Yes |
Passengers
|Conf. No. |Flight |Seat No. |Last Name |First name |Meal Pref |Phone number |
|456789 |6785 |19 |Clinton |Hillary |Veg |8888888 |
|907865 |8888 |32 |Coleman |Michael |Non-Veg |7777777 |
|678954 |6754 |44 |Blair |Tony |Non-Veg |6666666 |
|678956. |6754. |13 |Spears |Brittany | |3843849 |
|948583 |8888 |33 |Voinovich |John |Veg |3948322 |
Waitlisted
|Flight |Waitlist order |Last name |First name |Phone number |
|2345 |1 |Bush |George |6789543 |
|2345 |2 |Cheney |Dick |6789321 |
|6785 |1 |Gore |Al |5678912 |
|6785 |2 . |Lieberman |Joe |2847384 |
-----------------------
Sheet:analysis!
Sheet:salary!
Sheet:req!
Airlines
Primary key(s):
Airline#
Foreign Key:
none
Passengers
Primary key(s):
Conf No.
Foreign Key:
Flight
Waitlisted
Primary key(s):
None
Foreign Key:
Flight
Flights
Primary key(s):
Flight No.
Foreign Key:
Airline
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- excel tricks and tips ucl
- microsoft excel vocabulary words weebly
- excel basics university of washington
- excel chapter 2 formulas and functions performing
- compare two lists of data
- introduction pennsylvania state university
- spreadsheet introduction computer science
- cis200 homework 1 simple formulas functions
- introduction to excel formulae and functions
Related searches
- excel functions and formulas pdf
- photosynthesis homework 1 answer key
- formulas and functions in ms excel
- lesson 1 homework practice
- simple excel formulas pdf
- advanced excel formulas and functions pdf
- lesson 1 homework practice lines
- 2 1 relations and functions worksheet
- homework 1 problems timothy johnson
- unit 3 relations and functions homework 4
- lesson 1 1 functions and continuity
- simple harmonic motion formulas pdf