How To Meet the Criteria - A Guide

A Guide to Meeting the Criteria

1A How spreadsheets are used

Describe how spreadsheet software can be used in four areas of office or business administration:

• to present information 
• to perform calculations accurately 
• to perform repeated processes 
• to manage lists of information 

Finance

Used for accounts, calculations, predicting cash flow, invoices, statements and many more activities involving money to which spreadsheets are ideally suited

Customer Service
Customer address lists, details of what they bought stored on a spreadsheet used as a database, share information about purchases, refunds etc with Finance. Quick search of records to make dealing with customers more efficient

Sales

Product sales and popularity figures, displayed as charts, records of how individual products sell in different areas, sales people's activities, commission calculations, records of customer information

Personnel / HR
Staff details stored on spreadsheets used as a database, records of salary changes, tax codes, qualifications etc., Telephone contact list can be easily sorted in A-Z order. Quick search of who works in which department or who has particular qualifications etc

1B Spreadsheet file types P2

You will be provided with an original spreadsheet file to be converted into a variety of formats. Save the data in three different formats and explain why each could better meet a particular purpose.

.xlsx is the normal Excel 2007-2010 version but not everyone may be able to open this if they have non-Microsoft or older software

.csv is a comma separated value type of file where each record (or row on a sheet) is stored separated by commas. This type of file is smaller as it has no graphic effects and can be opened and read by almost anyone so is ideal for sharing widely where the data may need to be used with other or limited programmes. Only one sheet can be saved in this way at a time, though, and links between sheets may be lost.

.pdf can be used to publish all the sheet data (including graphs, colours etc) as a multi-page document. this type can be read by almost anyone anywhere but cannot be edited, only viewed.

Here is a sample completed task

P3 Football League Table (Enter and update data) (2A)

Print out the football league table with the two extra match results added to the data

P4 Football League Table (Formulae) (2A)

Copy the sheet for 2A
Add formulae for Goal difference and Points
Print the formulae sheet

P5 Football League Table (Formatting data) (2B)

Copy the sheet again
Add a column for Position
Enter the final positions
Change the display to make it attractive / interesting and sort so that No 1 team is at the top

P6 Football League Table (Presenting data) (2B)

Make charts from the data to show:

the Team and total Points
Goals For and Goals Against for each team

P7 Testing your sheets (3A)

Use data validation to restrict the numbers that can be put in some cells (e.g. number played has to be less than 8 or numbers won, drawn or lost must be between 0 and 7)
Do some calculations to check your sheet formulae for points totals

P8 Instructions for using the tables (3A)

Your guide for a new user that shows how to set up data validation or how to put the data in a particular order. Screen prints and some explanation will be good for this.
Add something about the importance of copying sheets before making changes so that you always have a back-up.

Choosing the right chart

In this task you will be deciding which type of chart is most suitable to display all of parts of the various figures shown.

1. How people spent their time!

Make a chart to show how one of these people spend their time.


Make a chart to compare how much time all 5 people spend on eating.

Make a chart to compare how each person's day is spent.

2. Student results

Make a chart to compare how these 5 students did in each subject.

3. Progress with jobs

Make a chart to compare how these 5 people are progressing with the three jobs they have to do.

4. Profit and loss

[Click to enlarge this chart so you can read the data]

Make a chart to show the profit or loss for each month. Illustrate the difference between loss and profit months with a colour or effect.

Make a chart to show how the bank balance changes over the year.

Changes colours, fill effects, lines etc. to make these charts more attractive and clear. Make the final set of charts full size displays on their own sheets.

Name the sheets suitably and save the spreadsheet file.

Task Sheet for Advising Peter

Peter's budget tasks

Peter is a carpenter. He makes kitchen cabinets and also does general building work. He is planning to introduce a new product – oak gazebos which are like permanent wooden tents people can have in their garden.

Your task is to help him work out how his cash flow will change over the year for different numbers of jobs that he hopes to get.

You are provided with a spreadsheet that has been partially completed. Download that at this link and save it.

1. You will need headings for the other months. Add February to December. (Remember, there is an easy way!)

2. Add a formula in cells G5 to G7 which will show the profit on each type of job. (Ignore other expenses at this stage, just take the cost away from the price)

3. Add the number of jobs he expects to get each month from this table.

4. Enter a formula to show the total number of jobs each month

5. Enter a formula to enter the income for January from each type of job. This will be the job price x number of jobs

6. Show the total income figures in row 20, the sum of the three figures above

7. In the Expenditure section, the cost of the Oak timber will be the number of Oak gazebo jobs x the Oak gazebo cost. Similarly, you can enter formulae to show Kitchen cabinet materials and General building costs.

8. Wages are for someone to help with the building jobs. He pays £100 per building job

9. The other expenses, drawings to sundry expenses, can all be assumed to be the same each month. Use either a formula or other method to copy these across.

10. Show the total expenditure figures in row 38.

11. The monthly profit will be his total income – total expenditure

12. The bank balance for January is entered already. From February it will be January’s balance + February’s profit. That formula can be copied for the other months.

13. Create a chart that shows Peter’s total jobs each month

14. Create a chart that shows his profit or loss for each month. Change the colours displayed so that the months with a loss stand out (eg profit in green, loss in red)

15. Create a chart that shows how his bank balance will change over the year.

16. Create three charts which indicate the cost and profit components of each type of job, expressing each as a percentage.

17. Copy the spreadsheet and display the formulae used.

Peter now wonders what would happen if he had to take July off too and would not be able to do any jobs that month. He realises that he will have to reduce his monthly drawings (what he pays himself each month) as it is important that he does not go overdrawn in December. By changing his monthly drawings determine the most he can pay himself each month and still be in the black at the end of the year.

Keep a separate copy of the chart showing this.

Output

• Sheet with complete data entered 
• A chart of total jobs each month 
• A profit and loss chart 
• A bank balance chart 
• Job cost / profit charts 
• Formulae sheet 
• Effect of July holiday and lower drawings chart

[Word version available here] | [Download the draft spreadsheet here]

Choosing the Right Chart

Spreadsheets may be great for analysing data but rows and columns of figures may not tell the story very effectively. Above is some data about how confident staff at institutions surveyed, in various age groups, feel about using different applications and equipment. The ‘Benchmark’ is the level expected for their roles. It’s not terribly obvious at first glance what these results mean.

The chart below, however, makes it much clearer.

The under 25s are generally pretty confident whilst the 45-54 group would benefit from some training in finding and utilising images effectively. Similar charts could be produced for the other categories.

Not all charts would work though.

This pie chart, for instance is pretty meaningless!

The line graph looks OK at first glance. However, joining the dots imples that there are people between, say, the 35-44s and 45-54s with a level of about 2.7. There is no data for this. In fact, in this example, there couldn’t actually be anyone between 44 and 45 as only ages in years are included!

This area chart looks impressive and could, perhaps, with a bit of work, be made to make some sense but the Word Processing level data has been almost completely obscured.

A bar chart, however, could be very illustrative, especially with the use of appropriate colours and, in this example, the vertical axis has been shifted to the ‘Benchmark’ position (2.9 in this case) so some can be seen as behind and others ahead. A column chart would work well too.

In general
Pie charts show distribution of things within a whole set of data, or the composition of something or compares the size of items making up the whole. They can be good for showing proportions – for example, an illustration of the spread of chosen colours of new cars.

Column charts compare data. They have many uses and can provide meaningful illustrations nearly all the time.

Bar charts are really the same as column charts (and often column charts are called bar charts too!). They show data horizontally which can be better for progress or time-related things.

Line graphs are excellent for showing how results change over time or where there is a continuous flow of data. It is important, though, to be careful about whether you can ‘join the dots’ – is there actually any data that could fit in between one and the other? Even if there is, can you be sure that the line doesn’t leap up or down to that intermediate value instead of the gradual flow that joining the dots implies.

If in doubt, don’t join the dots.

There are lots more but these will cover most needs.

Presenting Information with Charts Task Sheet

Presenting Information with Charts

Click sheet to read data

Spreadsheets may be great for analysing data but rows and columns of figures may not tell the story very effectively. Above is some data about how confident staff at institutions surveyed, in various age groups, feel about using different applications and equipment. The ‘Benchmark’ is the level expected for their roles. It’s not terribly obvious at first glance what these results mean.

The chart below, however, makes it much clearer.

The under 25s are generally pretty confident whilst the 45-54 group would benefit from some training in finding and utilising images effectively. Similar charts could be produced for the other categories.

Not all charts would work though.

1. Your task is to create suitable charts for each of the 9 skill categories in the data above. They do not need to be blobs like this illustration but you do need to check that the type you have used does actually show sensible and meaningful comparisons between each age group for each skill.

2. Label the chart suitably with a title ‘Using [Skill]’ and ‘Confidence level’ on whichever axis you have used for the scores. There should be clear identification of the different age groups.

3. Either add a line or use colours (or both) to show whether each age group’s score is below, at or above the ‘Benchmark’ figure.

4. Copy the charts you create to a document (as small images) or presentation (as larger images) and ensure that all your files are saved.

5. For one of the 9 skills (your choice), create an alternative type of chart to display the data. Add this to your document or slideshow together with your summary of which type you feel illustrates the data best.

Output 

Data table

Charts type 1 with labels

Adjustments to include visual comparison to a Benchmark

Document or slides with charts

Chart with alternative display

Summary of reasons for display 

General information

Unit introduction
Although traditionally associated with financial applications and mathematical calculations, spreadsheet software is very versatile. A spreadsheet can be used to store, manipulate and analyse data and to present it in easy to understand formats, such as charts and graphs. Many people also use spreadsheets in preference to word processing software when creating complex tables. Learners will investigate different ways of using spreadsheets, find examples of real uses and develop the practical skills needed to use spreadsheet software effectively.

Spreadsheets can be used in many different situations, such as finance and engineering and the unit content includes a wide range of spreadsheet features, formulae and functions. In addition, spreadsheet users need to be able to choose from a variety of different ways of presenting and analysing spreadsheet data, including producing graphs and filtering and sorting lists of data. Learners will be able to combine the skills developed to produce a spreadsheet model that is tailor made for the specific needs of the user.

To speed up the use of spreadsheets, shortcuts are often used to enter data or duplicate particular combinations of keystrokes. Learners will be able to create such shortcuts and recognise their potential in improving efficiency.

In the IT industry, documentation is necessary for every application and system so that they can be maintained and adapted over time. It is important that creators of spreadsheets are able to check and if necessary sort out problems with their solutions to ensure that everything works as it should and that it is fit for the intended purpose.

Learning outcomes
On completion of this unit a learner should:

1 Know what spreadsheets are and how they can be used

2 Be able to develop spreadsheet models

3 Be able to test and document spreadsheet models.