|
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.
|
