Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

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

DeleteCancel

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.

No, thanks

Unit 42 - Spreadsheet Modelling

Unit Introduction
by

Ben Holding

on 20 September 2012

Comments (0)

Please log in to add your comment.

Report abuse

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 transcript