This unit aims to enable learners to use complex spreadsheet modelling in order to support organisational
activities such as credit control, sales forecasting and stock analysis. Spreadsheets are key software for many businesses and organisations, helping them to keep track of numerical information and analyse it quickly and more easily than with paper records.
As IT practitioners, learners will need to be able to use spreadsheet software competently as well as being able to support users as part of a technical or helpdesk role.
| Course Code: | NE49 | Fee €350 |
Exam fee:€35 |
| Duration: | Six classes |
Awarding Body |  |
| Start Dates: 2 intakes/yr | January/February | Next Starts Date: 23rd/11/2011 | |
| Entry Requirement: | Applicants must be a minimum of 18 years old |
||
| University Progression Route |
|
||
| Award |
|
||
Learning outcomes:
On completion of this unit a learner should:
Unit Content
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.
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
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.
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.
Unit Assessment
Assessment takes the form of written assignments, observations, in-class tests, verbal assessment and projects
To gain the unit learners must achieve, as a minimum, the Pass grade; the Pass grade is in effect the gaining of the credit for the unit, and this contributes to the overall qualification grade. All units must be passes within the rules of combination to achieve the overall qualification.
The table below shows the number of points scored per credit at the unit level and grade
| Level | Points per credit | ||
| Pass | Merit | Distinction | |
| 5 | 7 | 8 | 9 |
| 6 | 9 | 10 | 11 |
Learners who achieve the correct number of points within the ranges shown in the 'qualification grade' tables below will achieve the qualification Pass, Merit, Distinction or Distinction* grades (or combinations of these grades appropriate to the qualification).
