### Present Remotely

Send the link below via email or IM

CopyPresent to your audience

Start remote presentation- Invited audience members
**will follow you**as you navigate and present - People invited to a presentation
**do not need a Prezi account** - This link expires
**10 minutes**after you close the presentation - A maximum of
**30 users**can follow your presentation - Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

### Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.

You can change this under Settings & Account at any time.

# Unit 42 - Spreadsheet Modelling

Unit Introduction

by

Tweet## Ben Holding

on 20 September 2012#### Transcript of Unit 42 - Spreadsheet Modelling

Unit 42 - Spreadsheet Modelling Achieving Like all of the units to achieve a pass you must met all 9 pass criteria, to achieve a merit you must meet all 9 pass criteria and the 4 merit criteria and to achieve a Distinction you must meet all of the 9 pass criteria, all of the 4 merit criteria and the 2 Distinction criteria. Pass Criteria

1 - Explain how spreadsheets can be used to solve complex problems.

2 - Develop a complex spreadsheet model to meet particular needs.

3 - Use formulae, features and functions to process information.

4 - Use appropriate tools to present data.

5 - Customise the spreadsheet model to meet a given requirement.

6 - Use automated features in the spreadsheet model to meet a given requirement (macros, Active X control, Control Toolbox or Visual Basic).

7 - Test a spreadsheet model to ensure that it is fit for purpose.

8 - Use conversion facilities to export contents of a spreadsheet to an alternative format.

9 - Produce user documentation for a spreadsheet model. Merit Criteria

1 -Refine a complex spreadsheet model by changing rules and values.

2 - Analyse and interpret data from a spreadsheet model.

3 - Compare different automation methods.

4 - Produce technical documentation for a spreadsheet model. Distinction Criteria

1 - Discuss how organisations can use interpretation methods to analyse data.

2 - Evaluate a spreadsheet model incorporating feedback from others and make recommendations for improvements. Assignment 1

Hand out October 2012

Hand in November 2012

P2, P3, P5 & P7 Plus M1 Assignment 2

Hand out December 2012

Hand in January 2013

P1, P4, P6, P8 & P9 plus M2, M3, M4 with D1 & D2 Pass Criteria - Elements and Range Distinction Criteria - Elements and Range 1 - Explain how spreadsheets can be used to solve complex problems.

Use of spreadsheets:

manipulating complex data;

presentation to requirements;

supporting decision making e.g. analysis of data, goal seeking, scenarios,

regression, data mining

Complex problems:

types e.g. cash flow forecasting, budget control, what-if scenarios, sales

forecasting, payroll projections, statistical analysis, trend analysis.

Interpretation:

methods e.g. comparisons of totals, trend analysis 2 - Develop a complex spreadsheet model to meet particular needs.

Complexity:

multiple worksheets (with links);

complex formulae e.g. at least two-step process;

large data sets;

cells linkage;

data entry forms e.g. menu systems, list boxes, drop-down boxes,

event controls;

data validation;

error trapping;

lookup tables;

nested IF functions;

templates;

cell protection.

Structure and fitness for purpose:

formatting e.g. integer, real, date, currency, text;

styling e.g. bold, italics, borders, shading, column alignment, consistency;

context. 3 - Use formulae, features and functions to process information.

Formulae:

relative references;

absolute references;

logical functions e.g. IF, AND, OR, NOT, SUMIF;

correct operators.

4 - Use appropriate tools to present data.

Sorting and summarising data:

use of sub-totals and facilities e.g. pivot tables; sorting data on multiple

fields;

filtering data sets

Tools:

charts and graphs e.g. titles;

labels e.g. axis scales, colours, annotation; select appropriate type e.g.

line, bar, column, pie, xy (scatter)

Presenting:

combining information e.g. table of data and chart;

maintaining data e.g. between worksheets, workbooks, packages. 5 - Customise the spreadsheet model to meet a given requirement.

Customisation:

restricting data entry e.g. hiding;

protecting;

modifying toolbars;

modifying menus;

checking data e.g. data validation, range checking, not NULL;

error messages. 6 - Use automated features in the spreadsheet model to meet a given requirement

(macros, Active X control, Control Toolbox or Visual Basic).

Automation:

methods e.g. macros, ActiveX control, Control Toolbox, Visual Basic. 7 - Test a spreadsheet model to ensure that it is fit for purpose.

Test:

manual calculations e.g. formula, functions;

data entry forms;

validation;

calculations;

correct outcomes e.g. layout, values;

suitability for client;

user testing;

test plans using normal, extreme and erroneous data. 8 - Use conversion facilities to export contents of a spreadsheet to an

alternative format.

Alternative formats:

converting to e.g. xls, csv, txt, xms, xml, html 9 - Produce user documentation for a spreadsheet model.

Documentation:

user documentation e.g. instructions, guide, troubleshooting;

technical documentation e.g. hardware resources, software resources;

instructions;

calculations e.g. formula, functions used;

validation procedures Merit Criteria - Elements and Range

1 - Refine a complex spreadsheet model by changing rules and values.

Refine:

improving efficiency e.g. shortcuts, aiding navigation;

formatting e.g. fonts, page orientation, header and footer, print

area, use of colour, conditional formatting.

2 - Analyse and interpret data from a spreadsheet model.

Analysing and interpreting data:

convert data e.g. charts, graphs;

lists e.g. filtering, sorting;

trends;

patterns;

data analysis;

results;

conclusions. 3 - Compare different automation methods.

Automation: methods e.g. macros, ActiveX control, Control Toolbox, Visual

Basic.

Same as P6

4 - Produce technical documentation for a spreadsheet model.

Same as P9

Documentation:

user documentation e.g. instructions, guide, troubleshooting;

technical documentation e.g. hardware resources, software resources;

instructions;

calculations e.g. formula, functions used;

validation procedures. 1 - Discuss how organisations can use interpretation methods to analyse data.

Same as M2

Analysing and interpreting data:

convert data e.g. charts, graphs;

lists e.g. filtering, sorting;

trends;

patterns;

data analysis;

results;

conclusions. 2 - Evaluate a spreadsheet model incorporating feedback from others and make

recommendations for improvements.

Feedback:

methods e.g. surveys, questionnaire, interview;

analyse results;

make recommendations.

Full transcript1 - Explain how spreadsheets can be used to solve complex problems.

2 - Develop a complex spreadsheet model to meet particular needs.

3 - Use formulae, features and functions to process information.

4 - Use appropriate tools to present data.

5 - Customise the spreadsheet model to meet a given requirement.

6 - Use automated features in the spreadsheet model to meet a given requirement (macros, Active X control, Control Toolbox or Visual Basic).

7 - Test a spreadsheet model to ensure that it is fit for purpose.

8 - Use conversion facilities to export contents of a spreadsheet to an alternative format.

9 - Produce user documentation for a spreadsheet model. Merit Criteria

1 -Refine a complex spreadsheet model by changing rules and values.

2 - Analyse and interpret data from a spreadsheet model.

3 - Compare different automation methods.

4 - Produce technical documentation for a spreadsheet model. Distinction Criteria

1 - Discuss how organisations can use interpretation methods to analyse data.

2 - Evaluate a spreadsheet model incorporating feedback from others and make recommendations for improvements. Assignment 1

Hand out October 2012

Hand in November 2012

P2, P3, P5 & P7 Plus M1 Assignment 2

Hand out December 2012

Hand in January 2013

P1, P4, P6, P8 & P9 plus M2, M3, M4 with D1 & D2 Pass Criteria - Elements and Range Distinction Criteria - Elements and Range 1 - Explain how spreadsheets can be used to solve complex problems.

Use of spreadsheets:

manipulating complex data;

presentation to requirements;

supporting decision making e.g. analysis of data, goal seeking, scenarios,

regression, data mining

Complex problems:

types e.g. cash flow forecasting, budget control, what-if scenarios, sales

forecasting, payroll projections, statistical analysis, trend analysis.

Interpretation:

methods e.g. comparisons of totals, trend analysis 2 - Develop a complex spreadsheet model to meet particular needs.

Complexity:

multiple worksheets (with links);

complex formulae e.g. at least two-step process;

large data sets;

cells linkage;

data entry forms e.g. menu systems, list boxes, drop-down boxes,

event controls;

data validation;

error trapping;

lookup tables;

nested IF functions;

templates;

cell protection.

Structure and fitness for purpose:

formatting e.g. integer, real, date, currency, text;

styling e.g. bold, italics, borders, shading, column alignment, consistency;

context. 3 - Use formulae, features and functions to process information.

Formulae:

relative references;

absolute references;

logical functions e.g. IF, AND, OR, NOT, SUMIF;

correct operators.

4 - Use appropriate tools to present data.

Sorting and summarising data:

use of sub-totals and facilities e.g. pivot tables; sorting data on multiple

fields;

filtering data sets

Tools:

charts and graphs e.g. titles;

labels e.g. axis scales, colours, annotation; select appropriate type e.g.

line, bar, column, pie, xy (scatter)

Presenting:

combining information e.g. table of data and chart;

maintaining data e.g. between worksheets, workbooks, packages. 5 - Customise the spreadsheet model to meet a given requirement.

Customisation:

restricting data entry e.g. hiding;

protecting;

modifying toolbars;

modifying menus;

checking data e.g. data validation, range checking, not NULL;

error messages. 6 - Use automated features in the spreadsheet model to meet a given requirement

(macros, Active X control, Control Toolbox or Visual Basic).

Automation:

methods e.g. macros, ActiveX control, Control Toolbox, Visual Basic. 7 - Test a spreadsheet model to ensure that it is fit for purpose.

Test:

manual calculations e.g. formula, functions;

data entry forms;

validation;

calculations;

correct outcomes e.g. layout, values;

suitability for client;

user testing;

test plans using normal, extreme and erroneous data. 8 - Use conversion facilities to export contents of a spreadsheet to an

alternative format.

Alternative formats:

converting to e.g. xls, csv, txt, xms, xml, html 9 - Produce user documentation for a spreadsheet model.

Documentation:

user documentation e.g. instructions, guide, troubleshooting;

technical documentation e.g. hardware resources, software resources;

instructions;

calculations e.g. formula, functions used;

validation procedures Merit Criteria - Elements and Range

1 - Refine a complex spreadsheet model by changing rules and values.

Refine:

improving efficiency e.g. shortcuts, aiding navigation;

formatting e.g. fonts, page orientation, header and footer, print

area, use of colour, conditional formatting.

2 - Analyse and interpret data from a spreadsheet model.

Analysing and interpreting data:

convert data e.g. charts, graphs;

lists e.g. filtering, sorting;

trends;

patterns;

data analysis;

results;

conclusions. 3 - Compare different automation methods.

Automation: methods e.g. macros, ActiveX control, Control Toolbox, Visual

Basic.

Same as P6

4 - Produce technical documentation for a spreadsheet model.

Same as P9

Documentation:

user documentation e.g. instructions, guide, troubleshooting;

technical documentation e.g. hardware resources, software resources;

instructions;

calculations e.g. formula, functions used;

validation procedures. 1 - Discuss how organisations can use interpretation methods to analyse data.

Same as M2

Analysing and interpreting data:

convert data e.g. charts, graphs;

lists e.g. filtering, sorting;

trends;

patterns;

data analysis;

results;

conclusions. 2 - Evaluate a spreadsheet model incorporating feedback from others and make

recommendations for improvements.

Feedback:

methods e.g. surveys, questionnaire, interview;

analyse results;

make recommendations.