### 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

# 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.