Unit 4 Outcome 1, Spreadsheet software

Organisations and Information Needs, Ch 5

Goals of information systems • Types of decisions: (revised) strategic, tactical

and operational decisions.• Strategic Plan

– strategic plan is a process for identifying long term goals within an organisation

• Mission Statement– establishing a set of common goals that will help

accomplish the organisations aims – Objectives are small achievable tasks undertaken to

accomplish a big task– Distinguish b/w goals of non-profit & for-profit

organisations

IS goals: efficiency, improving effectiveness, improving decision-

making• Efficiency:

– Speed, Cost, Time & Effort• Effectiveness:

– Quality of output, accuracy, presentation & comprehensiveness

• Improved decision-making– Requires 3 factors, sufficient information,

communication and time

Role of H/W & S/W components of IS

• Spreadsheet Software, formats & conventions:– numbers right aligned in columns– -money values usually have two decimal places or none– -subtotals have a single line above the total– -grand totals have a single or double line below the total– -grand totals are in bold– -graphs and charts must have titles identifying the name of the

organisation and purpose of the graph or chart– -x and y axis must be labelled– -limit the number of items represented in a chart to five or six– Reports: centre a page on a sheet horizontally– -briefly explain the purpose of the spreadsheet and identify the author– -where cells require user input, clearly label what has to be entered, left

side of cell– -list all equations used in a separate document.

Spreadsheets, Validation

• Range checking, use of IF statements• Existence checking• Data type checking, eg. integers, date,

alignment of column• Restricted data entry, drop down menu• Validation alerts, pop up dialog boxes

Validation & Testing

• Validation – is involved with input; • testing is output; testing also checking

electronic validation • Testing:

– Functionality, eg. test formulas & functions, eg. test both upper & lower boundaries of range

– Presentation:• Appropriate audience, difficult to test• Test for formats & conventions• White space, fonts, consistent fonts & sizes, background

colors, etc

Testing• Usability testing; must be user friendly

– Easy accessible to users; – Hyperlinks work properly– Amount of scrolling– Worksheets clearly labelled– Can users accidentally delete formulas or chance

validation rules; input cells unprotected• Accessibility testing

– Does solution open at right w/sheet?– Font sizes easy to read; limited use of red & green

• Communication of message– Important information clear & obvious; keep it simple

Testing

• Difference b/w usability testing & accessibility testing:

• Usability testing focuses on whether all buttons & functions work correctly;

• Accessibility is focused on ease of use of spreadsheet and whether it conveys the message.

Efficient software functions:

• If function:– =IF(D2>=2000,"Gold",IF(D2>=1000,"Silver",IF

(D2>=50,"Bronze","")))• Sum• Count If

– =COUNTIF(E2:E19,"Gold")• Sum If

– =SUMIF(Fundraising_Homeroom,"7A",Fundraising_Amount)

Efficient software functions

• Use of naming ranges and Vlookup:– =VLOOKUP(E10,homeroom_totals,3)

• Cell protection• Graphs• Insert notes/comments• Relative & absolute cell references• Electronic validation

Efficient software functions:

• Sheet referencing• Conditional formatting: