Task 04

Document Sample
Task 04 Powered By Docstoc
					   6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                           2/27/2012


 last updated on:                            Thursday, October 28, 2010
       Task                                         Condition
                                           Workbook and worksheet setup


Manage
                    you will create a new workbook from existing worksheets
worksheets


Name
                    you will rename worksheets
worksheets


Cell Formatting     you will rename worksheets


Sheet creation
                    you will create new worksheets
and naming

Sheet creation
                    you will create new worksheets
and naming


Cell Formatting     you will demonstrate cell formatting skills for display purposes




Cell Formatting     you will demonstrate cell formatting skills for display purposes




Cell Formatting     you will demonstrate cell formatting skills for display purposes




   <YourName> Task 04                                                                    1 of 14
   6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                                                                                   2/27/2012


                                                                                                                                           0
                                         Details                                          Subtask Grade   Standard         Points Earned


Open the workbook named 2008-2009.Statistical_Report_of_NC_Libraries.xlsx and
move a copy of all the worksheets to a new workbook named with a standard (i.e.,                      0              5%                    0
YYYYMMDD.yourname.task04.xlsx) file name

rename the worksheets in your workbook so that the name on the tab agrees with the
                                                                                                      0              5%                    0
number and name of the table on the sheet

Set up the worksheets so that the first row of each sheet contains the report name
("Statistical Report of North Carolina Public Libraries, July 1, 2008 - June 30, 2009")               0              5%                    0
in cell A1 and the second row of each sheet contains the table name in cell A2.

Insert a new worksheet, place it first in the sequence of worksheets, and name it
                                                                                                      0              5%                    0
[Summary].

make a copy of the first original worksheet (table 1) and place the copy directly after
                                                                                                      0              5%                    0
the [Summary] worksheet. Rename the new worksheet tab as [Subtotals].

Format the first row on each worksheet to display as
alignment: vertical, center;                                                                          0              5%                    0
text: Courier New font, 16 pt, Bold.
On all worksheets, format row 1 with white font, blue fill, but only for those cells
that have data below them (some worksheets have as few as 6 columns of data,
                                                                                                      0              5%                    0
others have 15 or so columns of data. Format only those cells in row 1 that are above
columns with data in them)

On all worksheets, format the cells below row 1 using Calibri font, 11 point. (if you
                                                                                                      0              10%                   0
don't have Calibri font available, use any other san-serif font other than Ariel)




   <YourName> Task 04                                                                                                                            2 of 14
   6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                                  2/27/2012


      Task                                          Condition


                  you will demonstrate cell formatting skills based on the type of data the
Cell Formatting
                  cells contain




Row height        you will demonstrate the ability to control cell display


Column width      you will demonstrate the ability to control cell display

Cell Formatting   you will demonstrate cell formatting skills for display purposes


Cell Formatting   you will demonstrate cell formatting skills for display purposes

Freeze Panes      you will demonstrate understanding of how and why to freeze panes
Freeze Panes      you will demonstrate understanding of how and why to freeze panes

Validate data     you will validate data in a column


Make it look
                  where you can, make the sheets look better
professional

                  Set up skills
                                                Hiding Data
Hiding Data       you will demonstrate how and why to hide data

Hiding Data       you will demonstrate how and why to hide data

                  Hiding data skills
                                              Functions & Formulas



   <YourName> Task 04                                                                           3 of 14
   6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                                                                                     2/27/2012


                                         Details                                           Subtask Grade   Standard          Points Earned
On all worksheets, format the
cells containing names and data about county systems with a light green fill;
                                                                                                       0               5%                    0
cells containing names and data about regional systems with a light red fill; and
 cells containing names and data about municipal systems in a light blue fill
on all worksheets, format the data cells that show dollar values as currency with a $
symbol. Be careful, not all the values that initially look like dollars are dollars. And
                                                                                                       0               5%                    0
do not format columns where the data looks like dollars, but is expressed as a range
and thus cannot be formatted as currency.
On all worksheets, set the row height for row 1 to 25                                                  0               5%                    0

Set the column width for columns A through C on all sheets to fit the data in the cells                0               5%                    0

Where a worksheet has header rows with subdivisions on a lower row, format the
header cells so that the groupings are clear to the viewer. Use fill colors where it                   0              15%                    0
seems useful to do so
freeze all worksheets so that the header rows always appear                                            0               5%                    0
freeze all worksheets so that the type and name columns always appear                                  0               5%                    0
Validate the data in column D on the salaries and wages worksheet (the original
                                                                                                       0               5%                    0
[Table 2 (2)]to that new data added to this column cannot be less than 50,000
Where appropriate, put all the relevant header cell data into a single cell atop the
relevant data column without altering the integrity of each row on the worksheet.
                                                                                                       0               5%                    0
Where it makes sense, remove cell borders for ease of viewing, but retain them
where they are needed to discriminate among the data.
                                                                                                                      100%                   0

On the Library Staff (the original [Table 2]) worksheet, hide columns E and I. The                     0              50%                    0
result should be an eight column table.
On the Operating Expenditures (the original [Sheet 1]) worksheet, hide all the rows                    0              50%                    0
relating to municipal systems.
                                                                                                                      100%                   0




   <YourName> Task 04                                                                                                                              4 of 14
   6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                                  2/27/2012


      Task                                          Condition
Functions         you will demonstrate facility with functions




Functions         you will demonstrate facility with functions




Functions         you will demonstrate facility with functions




Formulas          you will demonstrate facility with formulas




Formatting        you will demonstrate cell formatting skills based on the type of data the
                  cells contain
Dragging values   you will demonstrate understanding of how and why to drag formatting
                  information from one cell to another
                  Functions & formula skills
                                                    Sorting Data
Sort              you will do a single level sort




   <YourName> Task 04                                                                           5 of 14
   6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                                                                                     2/27/2012


                                          Details                                         Subtask Grade    Standard          Points Earned
On the [Summary] worksheet, in cell A4 enter the words "Highest Minimum MLS                            0              20%                    0
Salary." In cell F4, insert a function that will return the highest minumum MLS salary
from the data on the SALARIES & WAGES (the original [Table 2 (2)]) worksheet.


On the [Summary] worksheet, in cell A5 enter the words "Lowest Total Operating                        0               20%                    0
Income." In cell F5 insert a function that will return the lowest total operating
income from the data on the OPERATING INCOME (the original [Table 4]) worksheet.


On the [Summary] worksheet, in cell A6 enter the words "How many systems have e-                      0               20%                    0
books?" In cell F6, display the number of library systems that have e-books on the
ELECTRONIC TECHNOLOGY (the original [Sheet1 (9)]) worksheet. Use your help tool
to find the right function.
On the SERVICE MEASURES: USERS, VISITS, REFERENCE, ILL (the original [Sheet1 (6)])                    0               20%                    0
worksheet, replace the data in cell G6 with a formula that will result in the ratio of
"Registered Users" on the original [Sheet1 (6)] worksheet to "2008 Legal
ServicePopulation" on the original [Table 1] worksheet. [Note: before you replace
the existing data, write down the data you are replacing to see if your formula
returns the same values. Don't worry if the initial result of the formula does not look
correct. The subsequent step in cell B32 below should correct that.]


Once you have created the formula, format the cell to display percentages to two                      0               10%                    0
decimal places.
Once you have formatted the data in the cell, drag the cell down the column to apply                  0               10%                    0
the same formula and formatting for all library systems
                                                                                                                      100%                   0

Sort the data on the SALARIES & WAGES (the original [Table 2 (2))] worksheet by                       0               50%                    0
Minimum MLS Salary [you will not use header row data to sort in this case]. Have the
sort display from largest to smallest.




   <YourName> Task 04                                                                                                                              6 of 14
    6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                                      2/27/2012


       Task                                          Condition
Multi-level sort   you will do a multi-level sort



                   Sorting Skills
                                                   Filtering Data
Filtering 1        you will demonstrate facility with a number filter



Filtering 2        you will demonstrate facility with a number filter



Filtering 3        you will demonstrate facility with a number filter



                   Filtering Skills
                                                 Subtotalling Data
Subtotals 1        you will demonstrate facility with one of the functions in the subtotal tool.
                   To see the data you are using, you may have to adjust the zoom for the
                   Subtotals worksheet to 75%.


Subtotals 2        you will demonstrate facility with another function in the subtotal tool




Subtotal display you wil demonstrate facility with minimizing the subtotaled data
                   Subtotalling Skills
                                                    Comments




    <YourName> Task 04                                                                               7 of 14
   6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                                                                                   2/27/2012


                                         Details                                        Subtask Grade    Standard          Points Earned
Sort the data on the CIRCULATION: SERVICE OUTLETS & SERVICE MEASURES (the                            0              50%                    0
original [Sheet1 (5)]) worksheet first by Central, from smallest to largest, and then
by Branch from largest to smallest.
                                                                                                                    100%                   0

Filter the data on the SERVICE MEASURES: USERS, VISITS, REFERENCE, ILL (the                         0               30%                    0
original [Sheet1 (6)]) worksheet to find the bottom 10 top systems in terms of
library visits per capita.
Use a multilevel filter on the LIBRARY PROGRAMS & ATTENDANCE (Part I) (the original                 0               35%                    0
[Sheet1 (7)]) worksheet to find out which of the County and Municipal systems only
had total program attendance between 10000 and 20000
Use a filter on the LIBRARY PROGRAMS & ATTENDANCE (Part II) (the original [Sheet1                   0               35%                    0
(8)]) worksheet to find out which systems had above average attendance by adults at
in-house programs.
                                                                                                                    100%                   0

Using the [Subtotals] worksheet, hide column B. Then calculate subtotals consisting                 0               40%                    0
of sum of annual hours. You may have to find the subtotal label in the hidden column
to be able to use it to draw the viewers' attention to the subtotal.


Using the [Subtotals] worksheet, calculate a second subtotal consisting of average of               0               40%                    0
2008 Legal ServicePopulation. Be sure not to delete the previous subtotal. You may
have to find the subtotal label in the hidden column to be able to use it to draw the
viewers' attention to the subtotal.
Minimize the data so that only the subtotals are visible                                            0               20%                    0
                                                                                                                    100%                   0




   <YourName> Task 04                                                                                                                            8 of 14
   6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                       2/27/2012


    Task                                        Condition
Comment         you will comment on a cell, format the comment, and have it show




Comment         you will comment on a cell, format the comment, and hide it




                Comments skills
                                                Data Display
Chart 1         you will create a chart that compares one data set over time




Formatting      you will demonstrate facility with the chart formatting tools


Formatting      you will demonstrate facility with the chart formatting tools




   <YourName> Task 04                                                                9 of 14
   6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                                                                                  2/27/2012


                                         Details                                       Subtask Grade    Standard          Points Earned
On the Library Profile (the original [Table 1]) worksheet, insert a comment on cell D5              0              50%                    0
stating which system has the smallest legal service population. Then insert a
comment on cell I5 stating which system has the smallest number of operating hours.


When that is done, format both comments so that they are automatically sized to the                0               50%                    0
data inside the comment; fill the comment with a dark yellow fill color and a white
font color. Make the comment in cell D5 visible and the comment in cell I5 hidden.


                                                                                                                   100%                   0

Create a chart to look at the total income per capita of the county systems. Arrange               0               25%                    0
the data from lowest per capita on the left to highest on the right (this will require
sorting). Use a chart that compares values (per capita) to categories (county
systems). Place the chart on a separate worksheet named Per Capita Funding and
insert the sheet right after the OPERATING INCOME: PER CAPITA MEASURES &
PERCENT TOTALS (the original [Table 3b]) worksheet. You may use the chart wizard,
but do not use the defaults.

You will need to work with the chart tools to ensure that the X or Y axes (depending               0               10%                    0
on whether you use a column or a bar chart) are labeled.
Include a chart title and modify the display in some fashion to make it unique to your             0               10%                    0
style.




   <YourName> Task 04                                                                                                                          10 of 14
   6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                                   2/27/2012


      Task                                       Condition
Chart 2         you will create a chart that compares multiple data sets over time




Formatting      you will demonstrate facility with the chart formatting tools




Text Box        you will annotate the chart using a drawing object


Formatting      you will demonstrate facility with the chart formatting tools
                Data display skills
                                                  Page Setup
Page Setup      you will set up the printed versions of the workbook so that the header rows
                will display on each printed page




                Suggestion: Wait until you have all your worksheets done, then group them
                and do the next four things to all of them at the same time.




   <YourName> Task 04                                                                           11 of 14
   6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                                                                                   2/27/2012


                                          Details                                       Subtask Grade    Standard          Points Earned
We want to compare the Operating Funds as a Percent (%) of Total Income of the                       0              25%                    0
regional systems. Use the data from the Operating Income (the original [Table 4])
worksheet, but restrict the chart to only data about the regional systems. Create a
chart that compares the percentage of each value contributes to a total across
categories and place it on a separate worksheet named Funding Sources. Arrange the
data by State Aid, Federal, Other, Local - in that order and sorted so that the
regional systems are arrayed by the percentage of state aid first from highest to
lowest (this will require sorting). Use a chart that compares values (per capita) to
categories (regional systems). Use any appropriate chart style that pleases you, but
again here, do not settle for the defaults. Customize the chart in some fashion. This
will be a 100% stacked chart of some type.



Make it very clear in the formatting which values are which. If you use a legend, be                0               10%                    0
sure to have the legend pull its name from the appropriate cell in the worksheet.
Format your axes and axes labels so that the viewer is in no doubt about what is
being depicted. (If the data format is self-explanatory, you may not need a label for
an axis)

Annotate the chart by including a free-floating objects to point out the system who                 0               10%                    0
receives the highest percentage of funding in the "other" category.
put the legend at the bottom of the chart without overlapping the data                              0               10%                    0
                                                                                                                    100%                   0

Recognizing that the Summary worksheet is only one page long and thus needs no                      0               20%                    0
special page formatting, set up all other worksheets so that the appropriate header
rows will display on each printed page. You may have to do this for each sheet since
you may not necessarily have your headers on the same row on each sheet.




   <YourName> Task 04                                                                                                                           12 of 14
   6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                               2/27/2012


      Task                                         Condition
Page Setup       you will include footers on each worksheet page

Page Setup       you will include headers on each worksheet page


Page Setup       you will demonstrate facility with placing the data on the printed page


Page Setup       you will demonstrate facility with basic page layout

                 Page Setup Skills
                                                  Admin tasks
Save and store   Save the workbook with a standard (i.e., YYYYMMDD.yourname.task04.xlsx)
                 file name and provide a link to the file on your Task 4 web page.


Timeliness       25% reduction in points for each day beyond due date
                 Admin tasks
                 Points for Task 4




   <YourName> Task 04                                                                       13 of 14
   6243ffdd-b5e4-4317-9f6b-0a48ff5cd1e2.xlsx                                                                                                      2/27/2012


                                       Details                                             Subtask Grade    Standard          Points Earned
Use a footer to include your name on the left and the date on the right on each page                    0              15%                    0
in the workbook.
Use a header to include the worksheet tab name centered and bold on the top of                         0               15%                    0
each printed page.
Set the margins for 1 inch top and bottom, left and right, and .5 inches for header                    0               15%                    0
and footer
Ensure that all pages are laid out in landscape orientation for printing on letter sized               0               15%                    0
paper and that the page order is over, then down.
                                                                                                                       80%                    0

                                                                                                       0               50%                    0



                                                                                                       0               50%                    0
                                                                                                                       100%                   0
                                                                                                                                              0




   <YourName> Task 04                                                                                                                              14 of 14

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:2/27/2012
language:
pages:14