Open the file shop

_____1. Select the field headings and format the headings in 12 pt. font in bold.

_____2. Widen the columns, if necessary.

_____3. Sort the list in ascending order by location, within location by dept., and within dept. by last.

The last record in the sorted list has the last name. _____________________________

_____4. Insert blank lines so that the headings are in row 5

_____5. Insert a page break after row 28.

_____6. Use the advanced filter to filter the shop list. Find the records of people who work in the Dept. admin AND in the location CHI (Chicago.) How many people fit these criteria? __________________

HINT: First create a criteria range in the blank rows above the database (list)

_____7. Show all records. Find the records of people who work in the Dept. Admin OR/ANY in the location CHI (Chicago). How many people fit these criteria? __________________________________

_____8. Show the original list. Change the criteria. Filter the list and extract (copy) to A33 all the people who work in the Dept. Sales AND work in NY or LA. How many people fit these criteria?

________________________________

_____9. The appropriate title for this extracted list is ________________________. T

_________________________________________

For questions 10 and 11, use SUBTOTALS. Remove your criteria list. Show all records. Use the subtotal command to find the total weekly salaries paid to members of each depart.

HINT: Sort by department first. From the subtotal dialog box, set: At each change in Dept., Use function: sum. Add subtotal to wksal.

_____10. The total weekly salaries for the Administration Department is _________________________

_____11. The total weekly salaries for the Sales Department is _________________________

_____12. Undo your totals. Remove all subtotals.

_____13. Use conditional formatting to boldface and show in color the HIRED field for people hired after 10/10/96. How many people are there? _________________________________

_____14. In page setup, prepare a custom header titles EMPLOYEE SUMMARY. The header should be centered and formatted in 12 bold.

_____15. Name sheet2 as formulas.

_____16. On sheet2, in cell B5, calculate the PMT for following: Principal is $8,500. Yearly interest rate is 16.5%. The period of the loan is 40 months. The PMT is _______________________

_____17. In cell B7 calculate the future value if the rate is 7%, the period is 20 years, and the monthly payment is $1,000 per month. The future value is __________________________________________

_____18. In cell B9 calculate the present value if the APR is 6%, the period is 48 months and the PMT is $200. The present value is ____________________________________

_____19. In cell B11 calculate the future value if the APR is 7.5%, the period is 4 years and the PMT is $2000. The future value is _____________________________________

_____20. In cell B13 calculate the PMT if the principal is $8500, the APR is 7%, the period is 72 months. The monthly payment is _____________________________

Submit answers for the blank lines.

Save this exam as exam 2 by _____________. (print full name)

Number your answers to the above questions and put them to the left on the excel document. Email your exam to me as an attachment.