Spreadsheet Tasks



[pic]

Upskill Central

| |

|Practical Tasks For |

|Spreadsheets |

| |

|Using |

| |

|Microsoft Excel |

This page is intentionally blank

Practical Tasks For

Spreadsheets

Using

Microsoft Excel

Candidate’s Name

___________________________________________________

This page is intentionally blank

Contents

Practical Task 1 8

Student Checklist 1 10

Practical Task 2 11

Student Checklist 2 13

Practical Task 3 14

Student Checklist 3 17

Practical Task 4 18

Student Checklist 4 20

Practical Task 5 21

Student Checklist 5 24

Practical Task 6 25

Student Checklist 6 27

This page is intentionally blank

Please Note

In addition to these exercises, it is essential that you provide examples of real work you have completed in the workplace.

This work could be anything that involves the creation of a spreadsheet.

If you do not use spreadsheets as part of your job currently, please discuss this requirement with your assessor as soon as possible.

Assessment

The evidence you produce at work could include:

Lists of holidays

Travel costs

Petty Cash records

Statistics

Invoices / bills / accounts

You need to produce a minimum of 6 different pieces of evidence. These pieces of evidence must include:

Compulsory evidence

Data entry - text, number, calculation of totals

Page orientation - portrait

landscape

Formatting - setting cells to 0 and 2 decimal places, currency

Optional Evidence

|Use of formula / functions |Displaying / printing formulas |

|Use of Add, Subtract, Multiply, Divide |Deleting columns / rows |

|Column / Row resizing |Inserting columns / rows |

|Bold / underline / borders |Aligning text, numbers |

|Sorting | |

Spreadsheet Unit

Practical Task 1

You are going to construct a spreadsheet that calculates the cost of the petrol bill for a motorist for the year.

|Note: |= something you should do. |

➢ Load Excel

➢ Begin by entering the title Annual Petrol Bill in cell A1. Set underline and bold.

➢ On row 3, column A, enter Name and underline it

➢ On row 3, column B, enter a name.

➢ On row 5, column A, enter Month and underline it. Centre it.

➢ On row 6, column A, enter the month Jan. Replicate it to give 12 months ( see right ) or type the entries.

➢ On row 5, column B, enter £’s and underline it. Centre it.

➢ Enter the rest of the data so that your spreadsheet looks like the one to the right.

➢ Place your cursor in cell B19 and enter a formula using an =SUM( function that adds all of the monthly bills. The total should be 469

➢ Save the spreadsheet as PetrolBills1.xls

(remember to use the correct drive and folder)

➢ Do a Print Preview

➢ If satisfactory, print the file.

Oops, I made a mistake for the September sales.

➢ Change the Sep figure to 37

Check that the correct figure for the total is 467

➢ Move to cell A20 and enter the word Average

➢ Move to cell B20 and write a formula that uses the total in cell B19 and divides it by 12.

➢ The average should be 38.916667

➢ In cell A22, enter your name

➢ Save the file as PetrolBills2.xls

➢ Do a Print Preview

➢ If satisfactory, print the file.

➢ Close the spreadsheet

Your spreadsheet should look something like the one opposite.

Now, go to the next page and complete the Student Checklist for this activity.

|Note: Some performance criteria may be difficult to prove as the process cannot be printed. In these cases: |

|Whilst the process or data is on screen, press the key once. |

|Use a blank word document and press + V |

|Print the image that appears. |

Spreadsheet Unit

Student Checklist 1

Student Name: ____________________

|Task 1 |Evidence to Support |

|Load Excel | |

|Move the cursor around the screen | |

|Place text and numeric data on the sheet | |

|Edit entries to text and data | |

|Delete entries on the sheet | |

|Use simple addition formulae | |

|Use the SUM function | |

|Save the sheet to disk / hard drive | |

|Preview the sheet before printing | |

|Print the sheet | |

|Close the sheet | |

|Exit the application | |

Spreadsheet Unit

Practical Task 2

You are going to construct a spreadsheet that calculates the cost of duvets that are given a £5.00 discount each if 2 or more are bought.

|Note: |= something you should do. |

➢ Load Excel

➢ Select a new spreadsheet

➢ In cell A1, enter the title All Season Goosedown Duvets. Set underline and bold.

➢ On row 1, column D, enter 13.5 TOG and set bold.

➢ On row 3, column A, enter Code and set bold.

➢ On row 3, column B, enter Item and set bold.

➢ On row 3, column C, enter Buy 1 and set bold. Centre it.

➢ On row 3, column D, enter Buy 2+ and set bold. Centre it.

➢ Enter the following data into your sheet:

|Code |Item |Buy 1 |

|W174 |Single Duvet |£99.95 |

|W175 |Double Duvet |£120.95 |

|W176 |King Duvet |£179.95 |

|W177 |SuperKing Duvet |£194.95 |

|W178 |Goosedown Pillow |£39.95 |

Note: you will need to adjust the width of column B, and perhaps others, to complete this task.

➢ Place your cursor in cell D4 and enter a formula using the =SUM() function that takes the cost of the duvet on row 4 (99.95) and subtracts 5 from this figure. The total should be 94.95

➢ Replicate the formula in cell D4 down to cell D7

➢ Do a Print Preview

➢ If satisfactory, print the file.

➢ Oops, I made a mistake for the Double Duvet. The cost should be 137.95. Please correct this error.

➢ In cell A12, enter your name

➢ Save the file as duvets.xls

➢ Do a Print Preview

➢ If satisfactory, print the file

➢ Close the spreadsheet

Your spreadsheet should look something like the one below.

[pic]

Now, go to the next page and complete the Student Checklist for this activity.

|Note: Some performance criteria may be difficult to prove as the process cannot be printed. In these cases: |

|Whilst the process or data is on screen, press the key once. |

|Use a blank word document and press + V |

|Print the image that appears. |

Spreadsheet Unit

Student Checklist 2

Student Name: ____________________

In addition to the skills in Task 1, the following skills were used:

|Task 2 |Evidence to Support |

|Apply Bold and Underline | |

|Centred data within a column | |

|Adjust column width | |

|Edit entries on the sheet | |

|Use the SUM function | |

|Replicate (copy) formulas | |

|Save the sheet to disk / hard drive | |

|Preview the sheet before printing | |

|Print the sheet | |

|Close the sheet | |

|Exit the application | |

Spreadsheet Unit

Practical Task 3

You are going to construct a spreadsheet that calculates the sales of newspapers from the newsagents for a month.

|Note: |= something you should do. |

➢ Load Excel

➢ Select a new spreadsheet

➢ In cell A1, enter the title High Flats Newsagents. Set underline and bold.

➢ On row 3, column A, enter Newspaper and set bold.

Note: you will need to adjust column widths to make things fit.

➢ On row 3, column B, enter Week 1 and set bold. Centre it. Enter weeks 2, 3 and 4 for columns C, D and E.

➢ On row 3, column F, enter Price and set bold. Centre it.

➢ On row 3, column G, enter Total Value and set bold.

➢ Enter the following data into your sheet:

|Newspaper |Week 1 |Week 2 |Week 3 |Week 4 |Price |

|Daily Mail |223 |219 |231 |216 |0.45 |

|Sun |332 |349 |327 |341 |0.38 |

|Daily Telegraph |125 |131 |120 |128 |0.55 |

|Times |98 |87 |88 |93 |0.68 |

|Daily Express |265 |276 |266 |284 |0.40 |

|The Guardian |112 |114 |112 |111 |0.55 |

Note: you will need to set the decimal places to 2 for the prices.

➢ Go to cell A10. Enter Total Sales and set bold.

➢ Go to cell B10. Enter a formula to add all the sales of newspapers for the week. The total should be 1155.

➢ Replicate this formula across to column E.

➢ Insert a new column B. Move the Prices data to this new column.

Your spreadsheet should look like the one below:

[pic]

Note: you may need to adjust the width of column B to complete this task.

➢ Select Column G. Delete this column.

➢ Place your cursor in cell G4 and enter a formula using the =SUM() function that takes the sales for the month and multiplies the answer by the price of the newspaper to calculate the value of he sales for each newspaper. The total in cell G4 should be 400.05.

The formula will look something like this: =SUM(B1:D1)*A1

Note: you will need to set the decimal places to 2 in this column.

➢ Replicate the formula in cell G4 down to cell G9.

Your spreadsheet should look like the one below:

[pic]

➢ In cell A12, enter your name

➢ Save the file as newsagent1

➢ Do a Print Preview

➢ If satisfactory, print the file

➢ Close the spreadsheet

Now, go to the next page and complete the Student Checklist for this activity.

|Note: Some performance criteria may be difficult to prove as the process cannot be printed. In these cases: |

|Whilst the process or data is on screen, press the key once. |

|Use a blank word document and press + V |

|Print the image that appears. |

Spreadsheet Unit

Student Checklist 3

Student Name: ____________________

In addition to the skills in Tasks 1 & 2, the following skills were used:

|Task 3 |Evidence to Support |

|Move blocks of data | |

|Insert a column | |

|Adjust column width | |

|Delete a column | |

|Use the SUM function | |

|Replicate (copy) formulas | |

|Set decimal places | |

Spreadsheet Unit

Practical Task 4

You are going to modify the newsagent1 spreadsheet.

|Note: |= something you should do. |

➢ Load Excel

➢ Locate and open the newsagent1 spreadsheet

➢ In cell A1, set the font size to 14

➢ On row 10, insert a new row.

Note: the existing contents of row 10 will move to row 11

➢ On row 3, column H, enter Average and set bold. Centre it.

➢ On row 4, column H, enter a formula to take the total sales of each newspaper and divide the answer by 4 (the number of weeks) to get the average sales per week. Your answer should be 100.01

➢ Replicate the formula in cell H4 down to cell H9.

Note: you may need to set decimal places to 2 in this column.

➢ Go to cell A12. Enter Average and set bold.

➢ Go to cell C12. Enter a formula that calculates the average sales for week 1. The answer should be 193 when set to 0 decimal places.

➢ Replicate the formula in cell C12 across to F12.

Your spreadsheet should look like the one below:

[pic]

➢ Select the range A4:F9 and sort the data into ascending order by Newspaper. Use Method 2 – page 23 or the data will not sort correctly. Make sure you select No Header Row before sorting.

➢ Delete the row 10 as we will not need it after all.

➢ Open Header and Footer and put your name in the header – centre box.

➢ Delete your name entry from the sheet if present.

➢ Save the spreadsheet as newsagent2

➢ Do a Print Preview

➢ If satisfactory, print the file

➢ Close the spreadsheet

Now, go to the next page and complete the Student Checklist for this activity.

|Note: Some performance criteria may be difficult to prove as the process cannot be printed. In these cases: |

|Whilst the process or data is on screen, press the key once. |

|Use a blank word document and press + V |

|Print the image that appears. |

Spreadsheet Unit

Student Checklist 4

Student Name: ____________________

In addition to the skills in Tasks 1, 2 & 3, the following skills were used:

|Task 4 |Evidence to Support |

|Select a file from amongst others | |

|Open an existing spreadsheet | |

|Sorted data in ascending order | |

|Delete a row | |

|Delete entries | |

|Set a header entry | |

Spreadsheet Unit

Practical Task 5

You are going to modify the newsagent1 spreadsheet.

|Note: |= something you should do. |

➢ Load Excel

➢ Locate and open the newsagent2 spreadsheet

➢ Select the range A3:F9 and apply a heavy border to this area

➢ Select the range A10:F11 and apply a heavy border to this area

➢ Select the range G3:H9 and apply a heavy border to this area

Your spreadsheet should look like the one below:

[pic]

➢ Save the spreadsheet as newsagent3

➢ Do a Print Preview

➢ If satisfactory, print the file

➢ Select the correct command to display formulas.

➢ Select the correct command to change to Landscape view.

➢ Correct any column widths making sure that no data are hidden.

➢ Do a Print Preview

➢ If satisfactory, print the file

➢ Remove the Display Formulas command and readjust column widths if required.

➢ Set the page to Portrait view again

➢ Select rows 3 to 9

➢ Set the row height to 17

➢ Save the spreadsheet with the same name

➢ Do a Print Preview

➢ If satisfactory, print the file

➢ Close the spreadsheet

➢ Exit Excel

Your spreadsheet should look like the example below:

[pic]

Now, go to the next page and complete the Student Checklist for this activity.

|Note: Some performance criteria may be difficult to prove as the process cannot be printed. In these cases: |

|Whilst the process or data is on screen, press the key once. |

|Use a blank word document and press + V |

|Print the image that appears. |

Spreadsheet Unit

Student Checklist 5

Student Name: ____________________

In addition to the skills in Tasks 1, 2, 3 & 4 the following skills were used:

|Task 5 |Evidence to Support |

|Open an existing spreadsheet | |

|Select and apply borders | |

|Set row height | |

|Display formulas | |

|Set page to Landscape view | |

|Save without re-naming file | |

|Reverse the effects of a previous command | |

|Set a header entry | |

Spreadsheet Unit

Practical Task 6

You are going to modify the duvets spreadsheet.

|Note: |= something you should do. |

➢ Load Excel

➢ Locate and open the duvets spreadsheet

➢ Select the range A1:D8 and copy this data

➢ Go to cell A10 and paste the data

➢ Select the range A13:C17 and delete this data

➢ Enter the new data below to the range A13:C16

|Code |Item |Buy 1 |

|W1719 |Single Duvet |67.95 |

|W1720 |Double Duvet |92.95 |

|W1721 |King Duvet |107.95 |

|W1722 |SuperKing Duvet |134.95 |

➢ Go to cell D10 and change the entry to 10.5 TOG and set bold

Your spreadsheet should look like the example below:

[pic]

➢ Save the spreadsheet as duvets2

➢ Do a Print Preview

➢ If satisfactory, print the file

➢ Close the spreadsheet

➢ Exit Excel

Now, go to the next page and complete the Student Checklist for this activity.

|Note: Some performance criteria may be difficult to prove as the process cannot be printed. In these cases: |

|Whilst the process or data is on screen, press the key once. |

|Use a blank word document and press + V |

|Print the image that appears. |

Spreadsheet Unit

Student Checklist 6

Student Name: ____________________

In addition to the skills in Tasks 1, 2, 3 & 4 the following skills were used:

|Task 6 |Evidence to Support |

|Open an existing spreadsheet | |

|Select and delete a range of cells | |

|Look at files on your disk | |

|Copy and Paste data and formulas | |

This page is intentionally blank

Your Comments Please…

In order to help us improve our materials for the course we need your help. Please think about the materials you have used and tell us how you rate them.

|Question |V. Good |Good |Poor |V. Poor |

|Do the materials cover all the topics you needed to complete the | | | | |

|assessment? | | | | |

|Were there any topics or materials that the materials did not | |

|cover well? | |

|If so, please identify topics, pages etc. | |

|Did you find the pictures useful? | | | | |

|Did the instructions go at the right pace for you? | | | | |

|Was the language simple enough to understand easily? | | | | |

|Was the layout of the material clear and logical? | | | | |

|Did the tasks and exercises help you learn? | | | | |

|Were the tasks well explained and easy to follow? | | | | |

|Was the contents page detailed enough? | | | | |

|Would you say the material was suitable for an absolute beginner? | | | | |

|Any other comments? |

| |

| |

| |

| |

Thank you for taking the time to reply to this questionnaire. Your replies will help us to constantly improve the course and learning materials.

Thank You

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download