Essays /

139187 Level 3 Unit 19 Spreadsheet Essay

Essay preview

Cambridge
TECHNICALS

OCR LEVEL 3
CAMBRIDGE TECHNICAL
CERTIFICATE/DIPLOMA IN

IT
SPREADSHEET MODELLING
Y/601/6637
LEVEL 3 UNIT 19
GUIDED LEARNING HOURS: 60
UNIT CREDIT VALUE: 10

SPREADSHEET MODELLING
Y/601/6637
LEVEL 3 UNIT 19

AIM OF THE UNIT
The success of a business relies on its methods of recording business transactions. With the use of complex spreadsheets
this task is made relatively easy for businesses. A business therefore requires employees to have knowledge of how to
create a spreadsheet and how to use formulae and functions
effectively. This unit will provide the learner with the skills to enable them to create and use a complex spreadsheet
model.
The aim of this unit is to help the learner understand how
spreadsheets can be used to solve complex problems.
Learners will learn how to create a complex spreadsheet
model and to use a range of formulae and functions.
Learners will also learn how to automate and customise their spreadsheet models. Finally learners will learn how to test
and create user and technical documentation.

www.ocr.org.uk

2

Spreadsheet Modelling Level 3 Unit 19

ASSESSMENT AND GRADING CRITERIA

Learning Outcome (LO)

Pass

Merit

1 Understand how
spreadsheets can be
used to solve complex
problems

P1 explain how spreadsheets
can be used to solve
complex problems

2 Be able to develop
complex spreadsheet
models

P2 develop a complex
spreadsheet model to
meet particular needs

M1 use formatting to
enhance the complex
spreadsheet model

P3 use formulae, features
and functions to process
information

M2 use advanced formulaic
functions to process
information

The assessment criteria are
To achieve a merit the

the pass requirements for
evidence must show that,

this unit.
in addition to the pass

criteria, the learner is able to:
The learner will:
The learner can:

Distinction
To achieve a distinction
the evidence must show
that, in addition to the pass
and merit criteria, the
learner is able to:

P4 use appropriate tools to
present data
3 Be able to automate and
customise spreadsheet
models

M3 use summarising tools to
P5 customise the
provide results for a given
spreadsheet model to
requirement
meet a given requirement
P6 use automated features in
the spreadsheet model to
meet a given requirement

4 Be able to test and
document spreadsheet
models

M4 improve spreadsheet
model as a result of
testing

P7 test a spreadsheet model
to ensure that it is fit for
purpose

D1 evaluate the spreadsheet
model suggesting
improvements

P8 export the contents of the
spreadsheet model to an
alternative format
D2 summarise how the
spreadsheet model can
resolve the complex
problem

P9 produce user
documentation for a
spreadsheet model

3

TEACHING CONTENT
The unit content describes what has to be taught to ensure that learners are able to access the highest grade. Anything which follows an i.e. details what must be taught as part of that area of content. Anything which follows an e.g. is illustrative, it should be noted that where e.g. is used , learners must know and be able to apply relevant examples to their work though these do not need to be the same ones specified in the unit content. •







LO1 Understand how spreadsheets can be used
to solve complex problems
Use of spreadsheets
• business uses (e.g. cash flow forecasting, budget control, sales forecasting, payroll, stock control)
• personal uses (e.g. budgeting, tracking finances)
• problem solving (e.g. trend analysis, comparing data,
predictions)
• decision making (e.g. analysis of data, goal seek, scenarios).

LO3 Be able to automate and customise
spreadsheet models

LO2 Be able to develop complex spreadsheet
models

Customisation
• toolbars
• menus
• hiding rows/columns
• set print area
• error messages
• shortcuts
• start-up
• validation
• protection and security.

Complex model
• linked cells, worksheets (minimum three), workbooks
• complex formulae
• data validation
• worksheet protection and security options
• navigation links between sheets.
Formulae, Functions and Features
• relative and absolute cell references
• mathematical operators (e.g. +, -, /, *)
• simple functions (e.g. SUM, AVERAGE, COUNT, MIN, MAX)
• logical functions (e.g. IF, nested IF, AND, OR, NOT, SUMIF) • financial functions (e.g. ISPMT, PMT, PV)
• text functions (e.g. UPPER, LOWER, CONCATENATE)
• date and time functions (e.g. TODAY, DAY, HOUR)
• lookup and...

Read more

Keywords

02476 1 10 139187 19 2 3 4 5 5.30 6 60 7 8 851509 8am 9 abl absolut access account achiev across activ activex actual ad add added/updated addit adjustments/improvements advanc advic aim align allow along alongsid also altern alway analys analysi and/or annot answer anyth appear appli applic appreci appropri area around ask aspect assess assessor assist audienc autom avail averag awar axe axi base basic benefit bold border bracket budget busi call cambridg [email protected] captur carri cash cashflow ceil cell centr certificate/diploma chang charg chart check class clear client code colour column comment compar comparison complet complex concaten concept conclus condit consid contact contain content context control correct could count countif cover creat creation creativ credit criteria criterion csv currenc custom customis d1 d2 data date day debt decid decis defin delight deliv deliveri demonstr describ detail develop differ discuss distinct divid document done e.g earlier easi easili edit effect email employe enabl encourag enhanc ensur enter error essenti establish etc evalu evid evidenc exampl exercis exist expect explain export extend extens fault featur feedback file filter final financ financi find fit five flow follow font footer forecast form format formula formulae/functions four friday fuel full fulli function functions/formulae gain give given go goal grade graph graph/chart graphs/charts group guid guidanc hardwar header help hide higher highest hlookup hour howev html i.e idea identifi if illustr imag impact implement import improv includ increas inform initi instruct integ interfac internet interpret interview introduc ispmt ital item key know knowledg label larg layout learn learner least led level like link list lo lo1 lo2 lo3 lo4 log logic look lookup loss lower m1 m2 m3 m4 macro made make manipul manual map match math mathemat max may meet menus merit messag method min minim minimum misrepres model monday multipl multipli must name navig need nest note number object observ obtain occas ocr one oper opportun option order orient outcom overcom p1 p2 p3 p4 p5 p6 p7 p8 p9 page part particular pass password payrol person pivot place plan plan/table plus pm pmt possibl potenti practic predict prepar present previous price print prioriti problem process produc profit project protect provid purpos put pv qualif question questionnair quiz rand rang re receiv record refer referenc reinforc relat relev reli report requir research resolv resourc result review round row rows/columns sale save saving/exporting say scale scenario scope screen secur see seek select set shade sheet shortcut shot show shown simpl simplifi situat skill small softwar solut solv sort specif specifi spend spent spreadsheet staf staff stage start start-up stock store strength style sub sub-tot subtract success suggest suit suitabl sum sumif summari summaris supermarket support tabl table/plan table/plans take target task taught teach technic telephon test text therefor think though three time titl today togeth tool toolbar toolbox total track transact trend trends/patterns trig tutor two txt type unclear underlin understand understood unit unlock upper us usag use user valid valu view visibl visual vlookup want well whether widen wider within word work workbook worksheet would www.ocr.org.uk xls xml xms y/601/6637