Excel Test

Instructions

  • All calculations must be done using formulas or functions.
  • Do not use manually typed in values within formulas or functions.
  • Example: =SUM(A1:A3)+42.83
  • These instructions have been provided to you in hard copy and can also be found in the Excel Test.xls workbook on the Instructions worksheet.
  • Read each problem carefully and perform the tasks in each section. Good Luck!!
  • Open Excel Test.xls.

Payroll

  1. Go to the Payroll worksheet.
  2. Enter your full name in cell A1.
  3. Insert a new employee in row 13. Enter the following data points for this new employee:
  4. Employee is Jones, Peggy
  5. Aptitude Test Scoreis 85
  6. Pay Rate is 23.5
  7. Hours are 35
  8. Each employee is given a weekly aptitude test, a score of 80 or above on this test entitles them to a bonus calculated as follows: bonus percentage times gross pay. A score below 80 yields a bonus of 0. Calculate the bonuses for each employee in the Bonus column.
  9. Compute the Taxable Income (Gross Pay plus Bonus) for each employee.
  10. Calculate the Average of each column.
  11. Format values as needed.

Savings Plan

  1. Go to the Savings Plan worksheet.
  2. Cell reference your name in cell A1.
  3. ArtSoft employees may participate in a company savings plan which requires the employee to contribute $200 monthly for 5 years. The annual interest rate earned is 5%.
  4. Enter the monthly interest rate for the plan.
  5. Enter the total monthly contribution the employee must make.
  6. Determine the future value of this investment.
  7. Format values as needed.

Sales

  1. Go to the Sales worksheet.
  2. Cell reference your name in cell A1.
  3. Complete columns Total and Average.
  4. Create a line graph comparing the sales for each city in the first quarter
  5. construct a line graph of sales by month, with one line for each city, with the following features:
  6. Place the graph below the Net Sales table
  7. Make a legend to designate which line color refers to which city.
  8. Enter “ArtSoft First Quarter Sales” as the Chart Title.
  9. Enter “Month” as the x-axis label and “Sales (Millions)” as the y-axis label.
  10. Change the scale of the y axis to make 30 the minimum value
  11. Format values as needed.

Filter

  1. Go to the Filter worksheet.
  2. Cell reference your name in cell A1.
  3. Freeze panes so column headings remain visible.
  4. Filter the data to find all employees who
  5. have recently been promoted, and
  6. do not belong to the union, and
  7. earn between $45,000 and $75,000 (inclusive).
  8. Sort the filtered data in descending order by salary.

Lookup

  1. Go to the Lookup worksheet.
  2. Cell reference your name in cell A1.
  3. Make the Widget World Raise %s table a named range.
  4. Using a formula or function, bring in the following values from the Filer worksheet: Salary and Years with Widget
  5. Using the named range you created above and the Salary values, calculate the Raise % and Raise $ for each employee
  6. Format values as needed.

Wine

  1. Go to the Wine worksheet.
  2. Cell reference your name in cell A1.
  3. Calculate the Total Revenue using Bottles Sold, Price per Bottle, less Discount.
  4. Calculate the Subtotal, Tax, and Total.
  5. Create a pie chart to the right of the table with a pie wedge for each Winery and the size of the pie wedge based on the Number of Bottles Sold.
  6. Format values as needed.

Data

  1. Go to the Data worksheet.
  2. Cell reference your name in cell A1.
  3. Place a Filter on the column headings.
  4. Sort the data by the column Credits, large to small values.
  5. Format the Credit and Debit columns as comma and two decimal places.
  6. Filter the BAI Type Code for numbers greater than 500.
  7. Adjust all columns’ width as needed
  8. Insert a worksheet and name it Data Results.
  9. Cell reference your name in cell A1.
  10. Create numbers one through three beginning in cell A3 and ending 2 rows below.
  11. Input a formula on the Answers sheet next to number one which counts how many transactions have a 0 Day Flt Amt greater than 0.
  12. Input a formula on the Answers sheet next to number two which calculates the sum of 1 Day Flt Amt and 2 Day Flt Amt
  13. Input a formula on the Answers sheet next to number three which calculates the sum of one and two day float for all BAI Types less than 500
  14. Format values as needed.

Mortgage

  1. Go to the Mortgage worksheet.
  2. Cell reference your name in cell A1.
  3. Harold bought a house and his first mortgage payment is due August 1, 2007. Harold's mortgage payment is due on the 1st of every month. The monthly principal amount is $100.00. The annual interest rate is 5.0% and the monthly interest is calculated using a 30/360 year. The principal balance of the mortgage loan on July 31st, 2007 is $350,000.00.
  4. Input the loan variables from the above sentence into the column Loan Variable according to the description in the Loan Item column.
  5. Calculate the following:
  6. August 1, 2007 Monthly Principal Due
  7. August 1, 2007 Monthly Interest Due
  8. August 1, 2007 Mortgage Payment Due
  9. September 1, 2007 Principal Balance Outstanding
  10. Number of years to pay off Principal Balance assuming Harold pays exactly $100 per month towards Principal
  11. The monthly mortgage payment if Harold wanted to pay off his loan in 30 years
  12. Which is the higher monthly mortgage payment?
  13. $100 per month in principal plus interest, OR
  14. Paying off the loan in 30 years
  15. Which paydownscenario is less costly over the life of the loan?
  16. $100 per month in principal plus interest, OR
  17. Paying off the loan in 30 years
  18. Format values as needed.

Save As and Exit The File

  1. Save As the file to the desktop with the following nomenclature:
  2. Your full name, no spaces, all lower case, ExcelTest
  3. Example: snowwhiteExcelTest.xls

Page 1 of 3