D:\_NewAssessments\Completed\3651HUAFAssessment2005.DOC

______

Use Advanced Features – Assessment

Your assessment for this subject consists of the following:

2 Class Practical Tests worth 30 marks each

  • Your teacher will give you an idea of what is to be covered in the lesson before the test. Marks are awarded for each item of the test.

2 Class Exercises worth 15 marks each. You will be given these prior to the day on which they are to be completed. These are NOT practical tests. Your teacher will award you a mark of between 0 and 10 depending upon your understanding and completion of the exercise as well as the amount of help given to you by your teacher and/or class mates. The approximate breakup of marks is as follows:

  • 13-15 Displays clear understanding and competence, needs little help and finishes quickly
  • 10-12 Displays good understanding but requires more time and help than a 9-10
  • 7-9 Displays reasonable understanding, needs help and completes most of exercise
  • 4-6 Displays some understanding, needs help and completes about half of the exercise
  • 1-3 Displays only a little understanding, needs significant help and completes less than half of the exercise

Note that your teacher may substitute any of the class exercises given with exercises of their own which are of equivalent difficulty. If the exercise is on a different topic to that given then the class exercise given will be covered as a “non-assessable” class exercise.

A teacher assessment worth 10 marks based on the following criteria

.

  • Demonstrates high level research and analytical skills to achieve at exemplary level
  • Ability to plan and prioritise workload unsupervised.
  • Demonstrates extensive skills to complete tasks
  • Consistently demonstrates in-depth understanding of subject material
  • Demonstrates at exceptional level the ability to transfer learning to tasks without guidance

The five events that contribute to the assessment total will be conducted approximately at Week 4, Week 8, Week 12, Week 15 and Week 17.

Students who miss any of the assessment events are required to organize with one of the tutorial support teachers as well as their own class teacher to complete the assessment within two weeks of the date of the assessment.

Class Practical Test 1

Word Macros

Create a Microsoft Word macro called SetParagraphIndent which sets up the left indent of the paragraph to be 1cm. (3 marks)

Save the VBA code created by this macro into a Word document called VBASetParagraphIndent (1 mark)

Edit the SetParagraphIndentmacro so that the macro asks the user for the left indent in centimetres and then sets the left indent according to user input (3 marks)

Save the modified VBA code created by this macro into ta Word document called VBASetParagraphIndent (1 mark)

Word Drawing Toolbar

Use the drawing toolbar to create a file like that shown in the attachment. Save the file and call it GraphicsAnswers. Hand this file to your teacher with the other parts of your assessment. (5 marks)

Insert the wateringcan containing flowers (or any other graphic) from clip art. Make the text starting with “This is just plain text” appear over the top of the picture. Turn the picture into a watermark. (2 marks)

Calculations in Word Tables

Create a 2 column by 5 row word table and insert the following information. (2 marks)

January 20

February 50

March 100

In the second column fourth row use a formula in the table to calculate the sum of the values. (2 marks)

In the second column fifth row use a formula in the table to calculate the average of the values. Insert a page break after this information (2 marks)

Miscellaneous Tasks

Create a new Word document called AssessmentAnswers

.

 In 8 lines or less explain the advantages of using Outline View and how you use the styles to move text around the document easily. Insert a page break after this information. (2marks)

Use Online Help to find out how to change the character spacing between characters and then cut and paste at least between 4 and 10 lines of the information that you have found. Insert a page break after this information. (2 marks)

In the AssessmentAnswers document list four things might be stored in a template. Insert a page break after this information (2 marks).

In the AssessmentAnswers document make the information about Kern at 16 pt appear in a page which has landscape orientation, whereas all of the other pages are in portrait. (3 marks)

Class Practical Test 2

The One-Stop-IT-Shop requires that all of the information in their Order Entry System be exported into Microsoft Excel for analysis and charting. This is required because there are some features in Excel that perform analysis and charting better than the features in Microsoft Access.

The company requires that this analysis and charting be operated by means of a menu driven macro system.

The 3651HDataAnalysis macro system in Excel should analyse the data given on the sheet called 3651HDataAnalysis. This is similar to the data that would be exported out of Access in the Order Entry System above.

Data Analysis Macro System

In the creation of the Data Analysis macro system your tasks are as follows:

Download the Excel workbook called 3651HDataAnalysis from the location indicated by your teacher.

Using the MONTH function and a VLOOKUP function use a formula to calculate the Month in column M using the Order Date given in column B – what appears in column M should be January, February, etc. (3 marks)

Use the Freeze Panes option to keep only the top row and the first column of the MainData worksheet locked. (2 mark)

Pivot Tables and Pivot Charts

On the MonthPivot worksheet create a pivot table which analyses the total sales by month (total of line amount). (2 marks)

On the CategoryPivot worksheet create a pivot table which analyses the total sales by category(total of line amount). (2 marks)

On the MonthPivotChart worksheet create a pivot table chart which charts the total sales by month (total of line amount).(1 marks)

On the CategoryPivotChart worksheet create a pivot table chart which charts the total sales by category(total of line amount).(1 marks)

Main Menu and Macros

On the MainMenu worksheet there should be 5 buttons each of which executes a macro as follows:

  • Jumps to the MainData worksheet (1 mark)
  • Jumps to the MonthPivot worksheet and refreshes the pivot table (you should change some data on the MainData worksheet to test this) (2 marks)
  • Jumps to the CategoryPivot worksheet and refreshes the pivot table (you should change some data on the MainData worksheet to test this) (2 marks)
  • Jumps to the MonthPivotChart worksheet and displays the updated chart (you should change some data on the MainData worksheet to test this) (1 marks)
  • Jumps to the CategoryPivotChart worksheet and displays the updated pivot chart (you should change some data on the MainData worksheet to test this) (1 marks)

Each of the worksheets listed above should have a key press of <control-m> which will return you to the Main Menu (2 marks)

Data Validation and Sheet Protection

In the DataValidation worksheet apply data validation so that the only values that can be entered in cells C4:C6 is one of the campuses shown in the list on the ValidationList worksheet. Apply data validation so that the only values that can be entered in cells D4:D6 are whole numbers between 0 and 100. (2 marks)

Apply any advanced formatting feature and sheet protection so that the only cells in which the user can enter data are cells C4:E6. (Note: Use CAMPUS as the password if appropriate). (2 marks)

Use a function in cells E4:E6 which will combine the firstname and surname (together with a space). For example in cell E4 the value that appears is John Smith (2 marks)

Data Summary and Other Tasks

In the 3651HDataAnalysis workbook there is a sheet called DataSummary which the company uses to summarise the data. In this worksheet you are required to use the SUMIF and COUNTIF functions to total the amount of sales, the total quantity sold and the total number of sales for each of the categories listed. (Note: This could be easily done using a pivot table but you are required to use the SUMIF and COUNTIF functions). (4 marks – 2 each)

Class Exercise 1

Creating Styles

Obtain the 3651SQLPracManual file from your teacher and create or modifying existing styles as follows:

  • Normal style - arial 11pt, flush left, line spacing single, widow/orphan control
  • Heading 1 style - Normal + font 16 pt, Bold, Italic, Shadow, Kern at 16 pt, Left, Space Before 12 pt, Space after 3 pt, Keep with Next, Keep Lines together, Level 1
  • Heading 2 style - Normal + font 14 pt, Bold, Italic, Left, Space Before 12 pt, Space after 3 pt, Keep with Next, Keep Lines together, Level 2
  • Heading 3 style - Normal + font 12 pt, Bold, Left, Space Before 6 pt, Space after 3 pt, Keep with Next, Keep Lines together, Level 3
  • PictureStyle - Normal + Centred, Space before 6 pt, after 6 pt; style for the following paragraph should be caption style
  • Caption style - Normal + Font 10 pt, Centred, Space before 6 pt, after 6 pt; Style for following paragraph is Normal

Once you have created your styles go through the file and use the style box and the styles that you have just created to:

  • Format all of the paragraphs that start with the words Subject Name to be Heading 1 style.
  • Format all of the paragraphs that start with the words Practical Exercise to be Heading 2 style.
  • Format all of the paragraphs that start with the words Summary to be Heading 3 style.
  • Format all of the pictures to be PictureStyle
  • Format all of the captions(paragraphs that begin with Figure) to be CaptionStyle

Under the first screen dump (Title: MSSQLServer Properties) insert a caption which says Figure 1: MSSQL Server Properties. Note that the number 1 should be automatically updated if another figure is inserted before it

Tables and Indexes

Using the heading styles 1 and 2 to create the a table of contents under the main heading SQLPracExercises

After updating all of the Figure numbers create a Table of Figures under the table of contents

Create an index with at least 5 index entries

Bookmarks and Footnotes

Find the text Click Tables to test the connectivity between the linked servers. Insert a bookmark called Current at this point

Find the text Type –x in the Parameter text box and click Add. Insert a footnote which says Other startup parameters are -d, -e, -l

Class Exercise 2

Creating a Customised Toolbar

Create a new Excel workbook called ToolbarBook.

Create a new toolbar called MyToolbar and place the following buttons on it.

From the Edit Catgeory

Select Visible Cells

Select Current Region

From the Window and Help Category

Freeze Panes

From the Format Category

Increase Font Size

Decrease Font Size

Your toolbar should like the following:

Call your teacher over for onscreen marking or take a snapshot using Snagit and paste the picture of the toolbar into the workbook.

Using On-line Help

Create a word document called ExcelButtons.

Use online help to explain the use of the Select Current Region and Select Visible Cells icons

Excel Macros and Data Filters

Using the Data Analysis worksheet create a button on the MainMenu worksheet which will activate a macro which:

  • ask for user input for a particular month,
  • filters out only the data for that month,
  • transfer the information into the Temp sheet overwriting all existing data on this sheet in the process
  • creates a pivot table chart which charts the total sales (sum of line amount) for each category for that month
  • when the macro has finished a message box should appear saying “New Pivot Chart Created”
  • the Temp worksheet should contain a button which returns to the MainMenu.

09/12/18Page: 1