General Information

Unit content
1 Understand how spreadsheets can be used to solve complex problems
Use of spreadsheets: manipulating complex data; presentation to requirements; supporting decision making eg analysis of data, goal seeking, scenarios, regression, data mining

Complex problems: types eg cash flow forecasting, budget control, what-if scenarios, sales forecasting, payroll projections, statistical analysis, trend analysis

Interpretation: methods eg comparisons of totals, trend analysis

2 Be able to develop complex spreadsheet models
Complexity: multiple worksheets (with links); complex formulae eg at least two-step process; large data sets; cells linkage; data entry forms eg menu systems, list boxes, drop-down boxes, event controls; data validation; error trapping; lookup tables; nested IF functions; templates; cell protection

Formulae: relative references; absolute references; logical functions eg IF, AND, OR, NOT, SUMIF; correct operators

Structure and fitness for purpose: formatting eg integer, real, date, currency, text; styling eg bold, italics, borders, shading, column alignment, consistency; context

Features and functions: named ranges; file sharing; tracking changes; security issues; user interface; add-ins; built-in functions eg cell functions, lookup functions, text functions, statistical function; finding data

Refine: improving efficiency eg shortcuts, aiding navigation; formatting eg fonts, page orientation, header and footer, print area, use of colour, conditional formatting

3 Be able to automate and customise spreadsheet models
Sorting and summarising data: use of sub-totals and facilities eg pivot tables; sorting data on multiple fields; filtering data sets

Tools: charts and graphs eg titles; labels eg axis scales, colours, annotation; select appropriate type eg line, bar, column, pie, xy (scatter)

Presenting: combining information eg table of data and chart; maintaining data eg between worksheets, workbooks, packages

Analysing and interpreting data: convert data eg charts, graphs; lists eg filtering, sorting; trends; patterns; data analysis; results; conclusions

Customisation: restricting data entry eg hiding; protecting; modifying toolbars; modifying menus; checking data eg data validation, range checking, not NULL; error messages

Automation: methods eg macros, ActiveX control, Control Toolbox, Visual Basic



4 Be able to test and document spreadsheet models

Test: manual calculations eg formula, functions; data entry forms; validation; calculations; correct outcomes eg layout, values; suitability for client; user testing; test plans using normal, extreme and erroneous data

Feedback: methods eg surveys, questionnaire, interview; analyse results; make recommendations

Alternative formats: converting to eg xls, csv, txt, xms, xml, html

Documentation: user documentation eg instructions, guide, troubleshooting; technical documentation eg hardware resources, software resources; instructions; calculations eg formula, functions used; validation procedures