JaxWorks
       Small Business Spreadsheet Factory
       Since 1996

 

 

     
     
 



 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 



 

Excel Proficiency Test

There is no scoring for this test. The test is designed to reveal proficiency as the degree of difficulty increases. Make notes of the areas that yield problems and go to our free training page to scan through the indexes for help.

Instructors: You may want to make a scoring result sheet and assign levels of proficiency attained to determine additional training goals for the student.

Proficiency Test Files:

1. artsoft.xls
2. filter.xls
3. refrig.xls

Instructions:
1. All calculations must be done using Excel (formulas or functions), using no other method of calculation
    (calculators, hand-calculations).
2. You may find it easier to print this document so you can refer to it while you complete the test.
3. Read each problem carefully and perform the tasks in each section. 

Section 1 of 3
Open Excel, then open the file artsoft.xls from the list above and make sure you are on the Sales worksheet.

This file contains Sales, Payroll, and Company Savings Plan information for the ArtSoft Company.

1. Enter your full name in cell A1.

2. What are the Net Sales Totals for each of the following cities: Chicago, Decatur, and Champaign (put in cells E8-E10)?

3. What is the average Net Sales for Decatur during the first quarter (use an Excel function and put this figure in cell F9)?

4. Format the sales figures in B8:F10 in the Currency format, with 2 decimal places.

5. Construct a line graph comparing the sales for each city in the first quarter (i.e. construct a line graph of sales by month, with one line for each city) with the following features:

     1. Place the graph in cells A15 to F25.
     2. Make a legend to designate which line color refers to which city.
     3. Enter “ArtSoft First Quarter Sales” as the Chart Title.
     4. Enter “Month” as the x-axis label and “Sales (Millions)” as the y-axis label.
     5. Change the scale of the y axis to make 30 the minimum value, instead of zero.

6. Move to the Payroll worksheet in this workbook. This sheet contains a payroll report for the ArtSoft employees. Insert a new employee in row 13. Enter the following information for this new employee:

     1. In A13 enter Jones, Peggy.
     2. In B13 enter 85
     3. In C13 enter $23.50
     4. In D13 enter 35
     5. Gross Pay for Peggy Jones should appear in cell E13. If it doesn’t, copy the formula from cell E12 to cell E13.

7. Each employee is given a weekly aptitude test, a score of 80 or above on this test entitles them to a bonus (a score below 80 yields a bonus of 0). The test scores are in cells B6:B18. The bonus amount is a percentage of their gross pay. This percentage is given in cell B22. In cell F6 of the Payroll worksheet, use an Excel IF function to compute the bonus amount for Robert Anderson, and then copy the formula from F6 to F7:F18. Your formulas must refer to cell B22, and they must be formulas that were copied, not individually typed in for each cell.

8. In cell G6, compute the Taxable Income (Gross Pay plus Bonus) for Robert Anderson. Copy your formula from G6 to G7:G18.

9. Move to the Savings Plan worksheet. As an employee of ArtSoft, you are considering investing in a company savings plan. In the plan, the employee contributes $200 each month for the next 5 years. The annual interest rate earned is 5%. Assume that payments are made at the end of the period.


     1. In cell B5 of the Savings Plan worksheet, enter the monthly interest rate for the plan.
     2. In cell B6, enter the total monthly contribution for the plan.
     3. In cell B8, use the Excel FV function to determine the future value of this investment. Your function should refer to
         cells B5 and B6.



Section 2 of 3
Open the file filter.xls from above and make sure you are on the Employee Record worksheet.

This file contains employee records for the company Widget World.

10. Filter the data from A4:F295 to find all of the people who have recently been promoted, do not belong to the union,
       and make between $45,000 and $75,000 (inclusive). Copy the filtered data to the range of cells beginning
       in A302:F302.

11. Sort the filtered data that you copied to A302:F302 in descending order by salary.



Section 3 of 3
Open refrig.xls from above.

This workbook contains sales information from the four sales regions of the Freezing Point appliance company: North, South, East and West.

12. Insert a new blank worksheet named “Total Sales” at the end of the workbook.

13. Copy the column and row headings from the East worksheet to the new Total Sales worksheet (cells A1:F2 and A3:A9). You may use the Fill Across Sheets command on the Edit menu to do this, or just Copy and Paste.

14. In the range B3:F9 of the Total Sales worksheet, insert the formula that sums the sales in the corresponding cells of the North, South, West, and East worksheets. (Cell B3 of Total Sales should sum cell B3 from each of the other sheets, North, South, West, and East.)

15. Format the numbers in the five sales worksheets with the Number format, reducing the number of decimal places to zero and adding the 1000 (,) separator.

16. Apply the Classic 2 AutoFormat to the sales data on each of the five sheets.


All compilation and content of this site, including its assembly, design, text, illustrations, photographs, logos, etc. are the intellectual property of JaxWorks. All worldwide rights, titles and interests are reserved.
Privacy Statement      © Copyright, 1996-2013, JaxWorks, All Rights Reserved.     Terms of Use