Unit introduction

In all aspects of life, individuals are constantly faced with situations where they must weigh up the available information in order to produce alternatives and make decisions. In the working environment, effective decision making can ensure the successful development of organisations.

Poor decision making can have significant negative consequences and can even lead to the demise of an organisation. In this unit, you will investigate the fundamentals of the decision-making process. You will find out how using data modelling provides the computational ability to compare consequences, and determine a preferred course of action. You will develop the skills and techniques necessary to create complex spreadsheets in order to produce accurate information that informs decision making. You will examine a scenario and then design, develop and test a spreadsheet; you will review your spreadsheet and make refinements based on user feedback, providing an evaluation of the effectiveness of the alternatives produced.

The skills developed in this unit are useful for progression to computing or business-related higher education courses and for use in decision making in the workplace.

Learning aims

In this unit you will:

A - Investigate data modelling and how it can be used in the decision-making process

B - Design a data model to meet client requirements

C - Develop a data model to meet client requirements.

Pass, Merit and Distinction Criteria

A1 Stages in the decision-making process

• Understanding the scenario.

In any given project or situation, it is key to FULLY understand the "scenario", this is the situation or setting of the problem that needs to be referred to in order to provide a solution. In this unit, you will, for the most part, be providing solutions to enable "data" to be turned in to "information"

When provided with your assignment your scenario will be set and appear in the beginning part of the brief.

Identifying information and sources

In any data model, the need to know where your data is coming from is vital as you will need to ensure that it is accurate and trusted. If your sources are incorrect and inaccurate they will output information that is flawed, GIGO stands for Garbage In Garbage Out.

information required

This is the required information that is needed for the spreadsheet, this could be information such as Profit, Stock levels, Salary, Costs of products and services in a given company.

information that is already available

Information may already be available, this could be things like the tax percentages that are used to calculate VAT(Value Added Tax), or a data set that the client has made available for use by the individual creating the spreadsheet to model the data.

additional information needed

Once you are in a position to FULLY understand the clients data sets and requirements you may find that there are further information requirements that are required to be included in the model, this could be historical spending patterns, "footfall" (the number of people walking in to a shop) the weather over a given period. There may be in some/most situations a requirement to purchase these data sets.

sources of additional information

Identification of the location and source of data and information is vital as the credibility of these could be called in to question. Consider the fact that not all data and information sources are internet based, there are libraries with data is stored and accessed. In university's students use "Athens" accounts to source journals and publications to reference within their work. 

Other places of information are personally sourced, this can be collected via surveys of users and people within an institution, again caution should be applied here because the sample of people being surveyed may not be appropriate for the area being reviewed or reflected.

requirements for verifying the information sources.

As discussed above credibility of data and information is key to the successful generation of information from data sets. The reliability of this can have a huge impact on a company's direction and profits. There are obvious measures that can be used to protect companies gathering data and information from external sources, this would be, using trusted sources, for example, BARB in the media sector. Additional to thetrustworthinesss of the information source the age of the data and information, as it may be outdated and useless.

Factors affecting the quality of information

currency of data

The currency of data relates to the age of the data, this is important as mentioned in previous areas the older the data and information the less reliable or useful the results output from the system become.

 accuracy of data

Ensuring the accuracy of the data is a simple win or a company, however, the flip of this is that it is easy to have small inaccuracies within data, such as typos or a negative number instead of a positive value.

external factors.

External factors that can impact on may be beyond the control of the data sources control, for example a drugs company may have a data set or model that predicts the sales of a particular drug that they are tracking over the year and they are using old data from the previous 5 years to enable them to predict a trend however an alternative drug has been released by another company that is fractionally cheaper in the past year this could make the data model inaccurate

Analysing the information.

The process of analysing information is done throughout most jobs, and can enable managers and leaders to make informed decisions based on the data presented. Examples of this in an ICT sector could be the Network manager where these need to understand the needs of the users on the system, and through the use of helpdesk requests or issue reports they are able to suggest or impliment solutions and or improvments to the systems to enable the users to be more efficient and effective in thier duties.

Identifying alternatives.

When reflecting and using information and data it is common to make a decision on the face of the outputted results, however, careful consideration should be taken when using this information, could it have been presented in another way? could alternative sources have been used instead? These are factors that have been mentioned throughout this weeks content that could have an impact on a company or institutions decisions. 

Identifying the consequences of implementing the alternatives.

The implementing alternatives approached to analysing the data is a decision that requires a careful thought process.

Considering the consequences of the use of the alternative approaches would throw up questions around, the reliability of the data, its relevance, whether it is current, would it take longer for a data model to be produced, and the cost implications. 

Making a decision.

Once the possible solutions have been reflected on and discussed a decision should be able to be made on which model would be meet the needs and would have the best balance of quality and practicality.

Justifying the decision.

It is important in any project to be able to justify your choice, as this may link to the increased cost of a project. If you are able to agree/justify why you have made this selection users/owners and companies will be able to quantify why a decision has been made and the suggested benefit of using option A over B.

Just a Minute - At the end of the lesson teachers ask learners to summarise their learning. The comparison of these summaries against learning objectives informs planning.

A2 Spreadsheet features used to support data modelling

Entering and editing data.

Formatting data.

Using formulae and functions.

Validation (and verification) of data.

Analysing and interpreting data.

Presenting data.

A3 Using data modelling to consider alternatives

Identifying the inputs required for the model.

The range of outputs that can be produced.

Benefits and limitations of alternative solutions.

Impact/consequences of alternative solutions.

Identifying the alternative solution(s) that produce the best decision or compromise.

More Than Just a Minute - Learners assess themselves against key learning points indicating a level of understanding to inform teacher planning.

A4 Evaluating models

Factors to consider in the evaluation of the model:

how well the model reflects the scenario being modelled

the decisions that can be made, using the model

whether the model can be improved

whether or not there are other factors that could be used to extend the model.

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

A5 Documenting and justifying decisions

Requirements for documenting and justifying the model:

summarising the situation

identifying information sources used

indicating the factors considered

indicating method used to reach a decision

justifying the choice of information sources, factors considered and methods used.

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

B1 Functional specification

Designing a functional specification to meet requirements:

nature of the problem

functions the model must perform

user interface

constraints

success criteria.

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

B2 Spreadsheet model design

A design which shows how the spreadsheet model will look and work.

Producing worksheet structure diagrams that show:

layout and presentation

A layout and presentation diagram is important, this diagram will enable users to use the model effectively and efficiently so that there is reduced opportunities for miss-interpretation or understanding of data entry requirements or output. Each entry point must be clear and labeled with understandable titles and headings. Within this diagram, the selection of any graphs should be identified.

processing

The processing diagram is needed to present the formulae and functions, this will outline the calculations that will happen and where. It is advised that you explain what these calculations are doing.

The use of macros will need to be presented in a diagrammatic manner also, this can be using program flowchart or the use of JSP (Jackson Structured Programming) diagrams.

data entry and validation

Diagrams to identify the data entry points of the data model are required, these will also identify information on the validation rules and alerts that will be implemented in the system. 

navigation

output.

The definition of the outputs of a data model needs careful consideration. The presentation of the information derived from the model will enable users to make an informed decision based on it. In your diagrams, you should clearly identify the information that is being outputted.

Producing a test plan to show:

Your test plans should follow a clear structure to enable fair testing and recording of the results of the tests undertaken.

Here you can see a proposed structure for your plan.

test data

When creating or using test data, you need to be clear that you understand the output of this data in calculations and functions to enable you to be able to qualify the results are accurate and correct. Your test data must also push the system to check for any potential failures. For example, inputting text data in to an integer expected cell.

purpose of the test

You should ensure that the test data has meaning and purpose and is not just to fill a section of the model as this is where you will be able to refine and improve the model based on its output.

expected result.

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

B3 Reviewing and refining data model designs

Working with clients and others to improve the quality, effectiveness and appropriateness of designs.

Gathering feedback from client(s) and potential users on the extent to which the design meets requirements.

Communicating with clients, e.g. email, verbal communication.

Scheduling and documenting meetings.

Agreeing and adjusting timescales.

Refining ideas and solutions.

The feedback that is acquired from your client and users will inform you of areas for development or refinement to enable a better data model to be produced and for the potential of improved data output that will benefit the organization. 

Updating design specification documentation, based on review and feedback.

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

C1 Developing a data model solution

Sub FileQuote()
'
' FileQuote Macro
'
'
    Sheets("Quotation Record").Select
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheets("Data").Select
    Range("A2:N2").Select
    Selection.Copy
    Sheets("Quotation Record").Select
    Range("A2:N2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Quotes").Select
    Range("C3").Select
End Sub

How to clear a excel data sheet.
 
Sub ClearScreen()
'
' ClearScreen Macro
'
'
    Range("C3").Select
    Selection.ClearContents
    Range("C4").Select
    Selection.ClearContents
    Range("C5").Select
    Selection.ClearContents
    Range("C6").Select
    Selection.ClearContents
    Range("C7").Select
    Selection.ClearContents
    Range("D9").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("D11").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("C13").Select
    ActiveCell.FormulaR1C1 = "17"
    Range("D15").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("D17").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("D19").Select
    ActiveCell.FormulaR1C1 = "FALSE"
    Range("C21").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("C3").Select
End Sub

Processing features and requirements:

formulae, e.g. add, subtract, divide, multiply

Week 1  Web view

functions e.g. SUM, AVERAGE, COUNT, COUNTIF, LOOKUP, INDEX, GOAL SEEK

Week 1  Web view

Week 7 Look-up Functions  Web view

logical functions e.g. IF, NOT, AND, OR, WHATIF

Week 5 Nested If Functions  Web view

nested IF functions

Week 5 Nested If Functions  Web view

data manipulation, e.g. sorting, grouping, filtering, pivoting data

Week 12 Sorts Filters  Web view

Week 13 Pivot Tables  Web view

importing and exporting data

Importing 

1. Go to File > Open.

2. Select Text Files from the Open dialog box.

3. Locate and double-click the text file that you want to open.

Exporting

You can convert an Excel worksheet to a text file by using the Save As command.

1. Go to File > Save As.

2. The Save As dialog box appears.

3. In the Save as type box, choose the text file format for the worksheet.

   • For example, click Text (Tab delimited) or CSV (Comma delimited).

autofill

replication

relative and absolute cell referencing

Week 2 Calculations  Web view

using macros and buttons to initiate procedures.

Week 10 Macros  (Web view)

Data entry and validation requirements and methods:

use of data entry forms

Week 9 Form Controls  Web view

restricting data input to acceptable values

Week 3 Validation  Web view

protecting cells by hiding, locking and password protecting

ease of use techniques, e.g. list boxes and drop-down menus

automated data transfer between sheets or applications

adding user prompts and messages.

Week 3 Validation  Web view

Layout and presentation considerations:

font size and style

merging cells

colours, borders, shading

conditional formatting

headers and footers

graphics

Output requirements:

worksheet layout

graphics

colours, borders and shading

charts and graphs.

Week 4 Charts and Graphs  Web view

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

C2 Testing the data model solution

Using formative and summative testing to test data models, including functionality and acceptance.

Testing to establish whether:

the solution meets all of the requirements of the functional specification

the underlying logic of the model is correct

all the functions and formulae work correctly.

Other factors to consider:

selection and use of appropriate test data, e.g. valid, invalid, erroneous, extreme

selecting suitable users for solution testing

gathering feedback from users, e.g. effectiveness, presentation, performance and purpose

designing and completing test documentation.

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

C3 Reviewing and refining the data model solution

Making improvements and/or refinements to the model in order to meet client requirements.

Refining the model to take account of issues raised during testing.

Refining the model to take account of the feedback and client requirements.

Factors that could be used to extend the model.

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

C4 Skills, knowledge and behaviours

Planning and recording, including setting relevant targets with timescales, how and when feedback from others will be gathered.

Reviewing and responding to outcomes, including the use of feedback from others, e.g. professionals who can provide feedback on the quality of the data model and its suitability against the design requirements.

Demonstrate own behaviours and their impact on outcomes to include professionalism, etiquette, support of others, timely and appropriate leadership, accountability and individual responsibility.

Evaluating outcomes to help inform high-quality, justified recommendations and decisions.

Evaluating targets to obtain insights into own performance.

Media and communication skills, including:

the ability to convey intended meaning, e.g. written (email, design documentation, recording documentation, reports, visual aids for presentation use); verbal communication requirements (one to one and group informal and formal situations)

use of tone and language for verbal and written communications, to convey intended meaning and make a positive and constructive impact on audience, e.g. positive and engaging tone, technical/vocational language suitable for intended audience, avoidance of jargon

responding constructively to the contributions of others, e.g. supportive, managing contributions so all have the opportunity to contribute, responding to objections, managing expectations, resolving conflict.

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.