|
|
The Spreadsheet Scenario
John
Brown is the manager of JB Sports Limited, and you are the ICT technician.
There are 23 other employees totalling 25 in the business.
The
company employees always go on a group holiday over Easter when the
shop is closed for three days. In the past the holidays have been
a success apart from the money calculations always being a problem.
You
are now going to organise the holiday and take control of collecting
the money from the employees who attend. This is where your spreadsheet
skills will be of use.
John
Brown has asked you to give him a report (about half a page) of how
you will use the spreadsheet to make accurate calculations of cost
and breakdown so that the correct money can be collected from each
employee this year. The total cost for each employee should not be
more than £350 otherwise many will not go. Many employees have
talked about changing the Disney holiday for a skiing holiday in the
Alps, you will need to use a temperature chart to decide what week
in January, February or March where there will be the most chance
of snow.
Mr
Brown has already started making a template for this task, it is in
the Public area under ICT and GNVQ. You will need to develop this
into a proper spreadsheet.
The items that need costing are below with the current costs.
You will need to
set up the spreadsheet to calculate the costs for 2 days and 2 nights
in Disneyland.
The employees may pay a deposit and the balance over two other payments.
Not everyone goes on holiday, you will need to show this in the payments
sheet.
Write
a paragraph on what you will need to do to complete this part of the
Unit 2. Where do we get the data from, the template spreadsheet or
from J Brown. What are we going to produce and how will it be produced.
The
Complete Task list
for the Spreadsheet.
|
|
Task
1
Open
the template Disney Holiday
from the Public area.
- Save the spreadsheet
in a folder GNVQ Unit2.
- Open the Payments
worksheet, enter the names of the 25 employees, include Mr John Brown
and yourself. You already have received some payments choose who is
going and enter some payments for them.
- Enter a formula
in the Totals row to total each column. Enter a formula in the Totals
column for each individual. Do the first one and copy the formula
down.
- In the final
column for Outstanding, enter a formula that calculates what is left
to pay from the budget amount of £350.00. (i.e. if a person
has paid a deposit of £50 only they need to pay the balance
of £300). Copy the formula down for each individual.
- Hint: (You will
need to use the Cell reference with the £350 as an absolute
cell reference just in case the budget amount changes).
- Use the sort
function to show all the employees in alphabetical surname order.
- Save your work
so far as DisneyHoliday.xls, (do not overwrite the template).
- Print the Payments
worksheet, with your name and the file name, and Task 1 typed into
the footer.
- Show the formula
by using the Options menu, and print, ensure it all shows on a single
sheet.
Top
|
|
Task
2
Open
the worksheet Costs, from the DisneyHoliday.xls.
- In the Number
of Units column enter the amount of times the item will be paid for:
EuroStar = 1, Bus = 1, Accommodation, Tickets and Lectures = 2, Meals
= 6, Entertainment = 1, Other expenses = 2.
- Use a formula
to calculate the cost per item and sum a total.
- What is the total
individual costs, is this within the £350 budget?
- In a cell on
the worksheet enter "Total participants", in the next cell
enter the number going, use the Payments worksheet find this number.
- We need to know
what the Group total calculation to know how much to pay EuroStar
and Disneyland. Use the Total participants number in a formula to
calculate this, use an absolute cell reference for Total participants.
- Save your work.
- Print the Costs
worksheet, with your name and the file name, and Task 2 typed into
the footer.
- Show the formula
by using the Options menu, and print, ensure it all shows on a single
sheet.
Top
|
|
Task
3
Open
the worksheet Costs, from the DisneyHoliday.xls.
- If your calculations
were done correctly the individual cost is more than the budget of
£350. How can this be reduced?
- After a meeting
it was decided that the Other expenses item can be deleted as the
employees would be responsible for their own spending money. They
will only attend one lecture not two, and only spend £15 on
cheaper entertainment. Effect these changes onto the worksheet.
- Save your work.
- Print the amended
Costs worksheet, with your name and Task 3 typed into the footer.
|
|
Task
4
Open
the DisneyHoliday.xls spreadsheet.
- Enhance the looks
of the spreadsheet, use colour shading, border style, font size and
colour and enter a logo or company slogan on each worksheet.
- Save your work.
- Print the new
look design worksheets, with your name and the file name, and Task
4 typed into the footer.
|
|
Task
5
Open
the worksheet Average Temperature, from the DisneyHoliday.xls.
- Each day for
the first 12 weeks has an average daily temperature recorded. In row
13 below the weekly list create a function that calculates the average
temperature for the week.
- Print the Average
Temperature worksheet, with your name and the file name, and Task
5 typed into the footer.
- Show the formula
by using the Options menu, and print.
|
|
Task
6
Create
a new worksheet, and rename it Skiing Holiday, and drag to make it the
last worksheet.
- Entitle the worksheet
Skiing Holiday and enhance the looks similar the the other three worksheets.
Draw a new table for each week and its average weekly temperature.
- The weeks where
the average weekly temperature is -3 degrees or below will be ideal
weeks to go skiing. In the next column create an IF function to write
the message GO SKIING for temperatures at -3 degrees or below, or
GO TO DISNEYLAND for temperatures higher than -3 degrees.
- Save your work.
- Print the Skiing
Holiday worksheet, with your name and the file name, and Task 6 typed
into the footer.
- Show the formula
by using the Options menu, and print, ensure it all shows on a single
sheet.
|
|
Task
7
Open
the Skiing Holiday worksheet, create a chart of the weekly average temperatures.
- Use the weekly
average temperatures to create a line graph, give it a title and name
the axis, change the colours.
- Use the totals
information from the Payments worksheet to create a column chart of
how much each employee has paid, give it a title and name the axis
and change the colours, create the chart in a new worksheet named
chart.
- Print the Skiing
Holiday worksheet with the graph, with your name and the file name,
and Task 7 typed into the footer.
|
|
Task
8
Annotations,
Evaluation, and Print out File and Back-up structures.
- Annotate each
print out to explain the actions you took to develop that particular
task.
- Write an evaluation
of the work you have produced, include a summary of your work, what
you have learnt, what you would have liked to improve. Did the work
done match the user needs mentioned in the introduction.
- Print screen
your files structure: DisneyHoliday template, and spreadsheet, and
a copy kept on a floppy disk.
- Finally look
at the Marks check sheet
and ensure that you have completed all that is required.
Top
|
Mathstutor
2003-07