LastName	FirstName	Department	Rank	From	YearHired	Gender	Salary	Due for Salary Increase
Anderson	Kristen	Accounting	Assistant	Austin TX	1996	F	47,405
Ball	Robin	Accounting	Instructor	Georgetown TX	1995	F	39,973
Blackwell	Dean	Finance	Instructor	Dallas TX	2000	M	42,047
Bordeau	Katherine	Management	Assistant	Tulsa OK	1994	F	46,561
Bressette	Cheryl	Accounting	Instructor	Marfa TX	1995	F	40,832
Chin	Roger	Accounting	Full	Abilene TX	1999	M	63,531
Coats	Bill	Accounting	Assistant	Stephenville TX	1998	M	49,621
Comensoli	Angela	Finance	Associate	Hico TX	1986	F	48,462
Doepke	Cheryl	Accounting	Full	Waco TX	1986	F	56,355
Downs	Clifton	Accounting	Associate	McAllen TX	1990	M	51,137
Fitzgerald	Edmond	Management	Associate	Laredo TX	1998	M	53,007
Gao	Xiaoming	Management	Full	Uvalde TX	1990	M	60,079
Garabizien	Wendy	Management	Instructor	Castroville TX	2002	F	32,750
Garafano	Karen	Finance	Associate	Concan TX	1987	F	50,250
Halpern	Murray	Finance	Associate	Monroe LA	1997	M	50,250
Hill	Trevor	Management	Instructor	Mobile AL	1999	M	39,840
Jackson	Carole	Accounting	Instructor	Orlando FL	2000	F	38,031
Jacobson	Andrew	Management	Full	Memphis TN	1979	M	60,531
Lee	Kell	Finance	Assistant	Nashville TN	2002	M	45,250
Lewis	Karl	Management	Associate	Pittsburgh PA	1987	M	52,637
Lu	Jim	Accounting	Instructor	Phoenix AZ	1998	M	43,131
Mack	Kevin	Management	Assistant	Madison WI	1997	M	46,750
McKaye	Susan	Management	Instructor	Chicago IL	1997	F	38,229
Mikkola	Claudia	Finance	Associate	Detroit MI	1984	F	60,531
Nelsen	Beth	Finance	Full	Columbus OH	1985	F	56,589
Nelson	Dale	Accounting	Full	Alexandria LA	1996	M	58,828
Neumann	Kenneth	Finance	Instructor	Beaumont TX	1996	M	39,631
Palermo	Sheryl	Accounting	Associate	Orange TX	1996	F	49,867
Parker	Mathew	Accounting	Full	Miami FL	1982	M	62,142
Rais	Mary	Finance	Instructor	Macon GA	2001	F	31,250
Rothenberger	James	Management	Full	Alanta GA	1998	M	66,031
Scheib	Earl	Management	Instructor	Shreveport LA	1997	M	41,639
Smith	Alicia	Accounting	Full	Midland TX	1997	F	54,205
Smith	Tom	Finance	Full	Odessa TX	1980	M	61,417
Smythe	Janice	Management	Associate	Canyon TX	1994	F	51,137
Stewart	Mark	Accounting	Assistant	Fredericksburgh TX	1994	M	51,866
True	David	Accounting	Full	Dallas TX	1994	M	57,431
Weaver	Robert	Accounting	Full	Hollywood CA	1984	M	60,025
Weeks	Jodie	Finance	Instructor	Omaha NE	1996	F	37,302
Wolter	Christine	Accounting	Associate	Victoria TX	1991	F	49,168
Young	Jeff	Management	Assistant	Austin TX	1998	M	47,763

Business Prep School

You are the assistant controller at Business Prep School. The controller has asked you to prepare several reports using Excel as she prepares the annual budget for the upcoming board meeting. You will be evaluating the faculty for salary increases, completing a loan amortization schedule for the new bus purchased last month, and planning a spreadsheet to determine options for saving to purchase a used bus.

b. Create a nested IF statement in cell I2 of the Faculty worksheet to determine whether the members of the faculty are due for a 2% salary increase. If a faculty members current salary is less than $40,000 or they were hired after 2000, calculate the 2% salary increase amount. If the faculty member is not due for an increase, leave the cell blank. Format column I for currency, no decimals. Enter Highest Salary: in cell A46 and Lowest Salary: in cell A48. Using a nested function utilizing INDEX, MATCH, MAX and MIN, display the faculty member's Last Name who has the highest salary in cell B46 and the faculty member's Last Name who has the lowest salary in B48.

c. Complete the Bus Loan Schedule on the LoanInfo worksheet. Using formulas, calculate the Rate per Quarter (cell D5) and Total Number of Quarters (cell F5). Use the PMT function to calculate the Quarterly Payment (cell G5). Complete the Amortization Schedule using IPMT, PPMT, relative and absolute cell references as applicable beginning in row 9. Copy D9:F9 to D10:F10. Insert a formula using relative cell references in cell C10 to calculate the Remaining Principal. Select C10:F10 and copy down to complete the table. Enter a formula to calculate the Final Balance in cell C29. Using CUMIPMT and CUMPRINC, type 0, relative and absolute cell references as applicable, calculate the interest and principal for year 1 in cells B36 and B37. Select B36:B37 and copy across the range C36:F37. In cells G36 and G37, use AutoSum to calculate the total interest and principal over the course of the loan.

d. Complete the output cells for the various investment options in the Investment worksheet using the following functions: NPER (cell F5), PV (cell B7), RATE (cell D9), and FV (cell C11) as applicable.

e. Copy the Faculty worksheet and rename FacultyByDept. Clear the data in cells A46:B48. Copy the range A1:I1 to A47:I47 and A56:I56. Using the advanced filter, copy to a new location (row 57), and display only those faculty from the Accounting Department who are female.