Cambridge IGCSE0417

Create a data model

Information Communication Technology 0417 Chapter Notes

What this chapter covers

Create a data modelManipulate dataPresent data
ShareWhatsAppPost
Create a data model notes

Unable to load PDF

The notes viewer could not load. Please refresh the page.

Read online free. Download a watermarked copy with a free account.

Read the notes

The full Create a data model notes as text: skim, search, and jump between subtopics.

~14 min read

In this chapter

  1. 1Understanding Spreadsheet Data Models
  2. 2Structuring Your Spreadsheet Model
  3. 3Creating Relationships with Formulas
  4. 4Absolute and Relative Cell References
  5. 5Using the IF Function for Logic
  6. 6Summarising Data with Functions
  7. 7Testing and Validating Your Model

1. Understanding Spreadsheet Data Models

A data model is a way of representing a real-world situation using numbers, text, and formulas. In a spreadsheet, this means creating a simplified, working version of a system, like a business budget or a scientific experiment. You set up input values (variables you can change) and then use formulas to calculate outcomes. The key benefit is that when you change an input, all the calculations that depend on it update automatically. This allows you to perform 'what-if' analysis, exploring different scenarios quickly without any real-world risk.

Key term

Data Model: A way of representing the relationships in a real-life, complex system using text, diagrams, symbols or numbers and formulae.

Fun fact

The first electronic spreadsheet, VisiCalc, was invented in 1979. It was so useful for business modeling that it is often called the Apple II computer's 'killer app', turning it from a hobbyist's machine into a serious business tool.

Worked example 14 marks

A student wants to model their weekly savings. They receive £15 pocket money, spend £4.50 on lunch, and £2.00 on bus fare each week. Create a simple data model to calculate their weekly savings.

  1. 1

    Step 1: Label your data clearly. In cell A1, type 'Income'. In cell A2, type 'Lunch Cost'. In cell A3, type 'Bus Fare'. In cell A4, type 'Total Spending'. In cell A5, type 'Weekly Savings'.

  2. 2

    Step 2: Enter the known values (inputs). In cell B1, enter 15. In cell B2, enter 4.50. In cell B3, enter 2.00.

  3. 3

    Step 3: Create a formula to calculate total spending. In cell B4, enter the formula '=B2+B3'.

  4. 4

    Step 4: Create a formula to calculate the final savings. In cell B5, enter the formula '=B1-B4'. The spreadsheet will now show the result.

Recap

  • A spreadsheet data model uses cells to hold inputs and formulas to calculate outputs.
  • Models allow for 'what-if' analysis by changing input values to see the effect on results.
  • The core components of a model are inputs (data you enter), processes (formulas), and outputs (results).
  • Clear labels are essential to make the model easy to understand.

Quick check

  1. What is the main purpose of creating a data model in a spreadsheet?1 mark
  2. If you change an input value in a well-made model, what should happen to the output values?1 mark

2. Structuring Your Spreadsheet Model

A well-structured model is easy to understand, use, and modify. The best practice is to separate your sheet into distinct areas for inputs, calculations, and outputs. Inputs are the raw numbers you might want to change (e.g., hourly pay rate, tax percentage). Calculations are the formulas that process the inputs. Outputs are the final results (e.g., net weekly pay). Use clear, descriptive labels for all values and format your sheet for readability using borders, colours, and bold text.

Key term

Variable: A value in a data model that can change to test different scenarios, typically stored in a specific cell.

Examiner insight

Examiners award marks for well-structured models that are clearly labelled and easy for another person to interpret. A logical layout is just as important as correct formulas.

Worked example 15 marks

You are creating a model to calculate the cost of a school trip. The inputs are: cost per student (£25), number of students (30), and cost of coach hire (£300). Structure a model to calculate the total cost.

  1. 1

    Step 1: Create an 'Inputs' section. In A1, type 'Inputs'.

  2. 2

    Step 2: Label and enter the input values. In A2, type 'Cost per Student'; in B2, enter 25. In A3, type 'Number of Students'; in B3, enter 30. In A4, type 'Coach Hire Cost'; in B4, enter 300.

  3. 3

    Step 3: Create a 'Calculations/Outputs' section. In A6, type 'Outputs'.

  4. 4

    Step 4: Label the calculation for the total student cost. In A7, type 'Total Student Cost'.

  5. 5

    Step 5: In cell B7, enter the formula to calculate the total student cost: '=B2*B3'.

  6. 6

    Step 6: Label the final output. In A8, type 'Total Trip Cost'.

  7. 7

    Step 7: In cell B8, enter the formula to calculate the total trip cost: '=B7+B4'.

Recap

  • Always use labels to describe the data in adjacent cells.
  • Separate your model into logical sections for inputs, calculations, and outputs.
  • Use formatting like bold text and borders to improve readability.
  • Place constants, like a VAT rate, in their own cell so they can be easily updated.

Quick check

  1. Why is it a good idea to put a value like a tax rate in its own cell instead of typing it directly into a formula?2 marks

3. Creating Relationships with Formulas

Formulas are the engine of your data model. They create the relationships between your input values and your results. Every formula in a spreadsheet starts with an equals sign (=). After the equals sign, you can use cell references (e.g., A1, B2) and mathematical operators to perform calculations. When the value in a referenced cell changes, the result of the formula automatically updates.

Addition: =A1+B1

Subtraction: =A1-B1

Multiplication: =A1*B1

Division: =A1/B1

Key term

Formula: An expression which calculates the value of a cell, always beginning with an equals sign (=).

Common pitfall

Forgetting the equals sign '=' at the start of a formula is a very common mistake. If you omit it, the spreadsheet will treat your entry as plain text (e.g., 'A1+B1') instead of calculating a result.

Worked example 12 marks

A spreadsheet contains the price of an item in cell C2 (£50) and the quantity sold in cell D2 (10). Write a formula in cell E2 to calculate the total revenue.

  1. 1

    Step 1: Select the cell where you want the result, which is E2.

  2. 2

    Step 2: Type the equals sign (=) to begin the formula.

  3. 3

    Step 3: Click on cell C2 (or type C2) to reference the price.

  4. 4

    Step 4: Type the multiplication operator (*).

  5. 5

    Step 5: Click on cell D2 (or type D2) to reference the quantity.

  6. 6

    Step 6: Press Enter. The final formula in cell E2 is '=C2*D2', which will display the result 500.

Recap

  • All formulas must start with an equals sign (=).
  • Use cell references (e.g., B4) instead of typing numbers directly into formulas where possible.
  • The main arithmetic operators are + (add), - (subtract), * (multiply), and / (divide).
  • Formulas create dynamic links between cells.

Quick check

  1. What character must every formula begin with?1 mark
  2. Cell A1 contains 10 and cell A2 contains 5. What is the result of the formula '=A1/A2'?1 mark

4. Absolute and Relative Cell References

Understanding cell references is crucial for building efficient models. By default, references are 'relative'. When you copy a formula with a relative reference (e.g., A1) down a column, the reference adjusts automatically (to A2, A3, etc.). An 'absolute' reference (e.g., $A$1) is fixed. When you copy a formula with an absolute reference, it always points to that exact cell. This is essential when a formula needs to consistently refer to a single cell, such as a tax rate or a discount percentage.

Relative Reference: A1

Absolute Reference: $A$1

Mixed Reference (Absolute Column): $A1

Mixed Reference (Absolute Row): A$1

Key term

Absolute Reference: A cell reference in a formula that remains fixed on a specific cell when the formula is copied, denoted by dollar signs (e.g., $A$1).

Examiner insight

Correctly using an absolute reference for a single value (like a tax rate) that applies to a whole list of items is a key skill. Examiners frequently set questions to test this, and it separates proficient students from novices.

Fun fact

The dollar sign ($) used for absolute referencing has no connection to currency. It's simply the symbol chosen by the developers of early spreadsheet software.

Worked example 13 marks

A worksheet has a list of prices in cells A2 to A10. The VAT rate of 20% is in cell E1. Write a formula in B2 that calculates the VAT for the price in A2, ensuring the formula can be copied down to B10 correctly.

  1. 1

    Step 1: In cell B2, start the formula with '='.

  2. 2

    Step 2: Reference the price in cell A2. The formula is now '=A2'.

  3. 3

    Step 3: Add the multiplication operator '*'. The formula is now '=A2*'.

  4. 4

    Step 4: Reference the VAT rate in cell E1. The formula is '=A2*E1'.

  5. 5

    Step 5: Make the reference to the VAT rate absolute by adding dollar signs. This is because when we copy the formula down, we always want to refer to E1. The final formula is '=A2*$E$1'.

  6. 6

    Step 6: Press Enter. You can now drag the fill handle from B2 down to B10, and the formula will work correctly for each row.

Recap

  • Relative references (A1) change when a formula is copied.
  • Absolute references ($A$1) do not change when a formula is copied.
  • Use absolute references for constants like tax rates, discount percentages, or fixed costs.
  • You can press the F4 key after selecting a cell reference to cycle through reference types.
  • Mixed references ($A1 or A$1) lock either the column or the row.

Quick check

  1. What symbol is used to create an absolute reference?1 mark
  2. When would you use an absolute reference instead of a relative one?2 marks

5. Using the IF Function for Logic

The IF function allows your data model to make decisions and perform different actions based on a condition. It checks if a condition is TRUE or FALSE and then returns a value you specify for each outcome. The syntax is: IF(logical_test, value_if_true, value_if_false). The logical test uses comparison operators to compare values, such as = (equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), and <> (not equal to).

=IF(logical_test, value_if_true, value_if_false)

Key term

Logical Test: An expression that evaluates to either TRUE or FALSE, used as the first argument in an IF function.

Common pitfall

Forgetting to put double quotes around text strings in an IF function (e.g., writing =IF(A1>10, Yes, No) instead of =IF(A1>10, "Yes", "No")) will cause an error.

Worked example 13 marks

In a student exam model, the score is in cell C2. The pass mark is 50. Write a formula in cell D2 to display the text 'Pass' if the student's score is 50 or more, and 'Fail' otherwise.

  1. 1

    Step 1: Select cell D2 and start the formula with '='.

  2. 2

    Step 2: Type 'IF(' to begin the function.

  3. 3

    Step 3: Create the logical test. We need to check if the value in C2 is greater than or equal to 50. So, type 'C2>=50'.

  4. 4

    Step 4: Type a comma, then specify the value if the test is true. We want to display 'Pass', so type '"Pass"'. Text values in formulas must be enclosed in double quotes.

  5. 5

    Step 5: Type another comma, then specify the value if the test is false. We want to display 'Fail', so type '"Fail"'.

  6. 6

    Step 6: Close the bracket. The final formula is '=IF(C2>=50, "Pass", "Fail")'.

Worked example 23 marks

A company pays a 5% sales bonus if an employee's sales in cell F5 are greater than £10,000. Otherwise, the bonus is £0. Write a formula to calculate the bonus amount.

  1. 1

    Step 1: The logical test is whether sales in F5 are greater than 10000: 'F5>10000'.

  2. 2

    Step 2: If the test is true, the bonus is 5% of the sales amount. The calculation is 'F5*5%'.

  3. 3

    Step 3: If the test is false, the bonus is 0.

  4. 4

    Step 4: Combine these into a single IF function: '=IF(F5>10000, F5*5%, 0)'.

Recap

  • The IF function has three parts: the test, the true value, and the false value.
  • Comparison operators like >, <, and = are used to create the logical test.
  • Text outcomes in an IF function, like 'Pass' or 'Fail', must be enclosed in double quotes.
  • The true/false part of an IF function can be a number, text, or even another formula.

Quick check

  1. How many arguments (parts separated by commas) does a standard IF function have?1 mark
  2. Write the comparison operator for 'not equal to'.1 mark

6. Summarising Data with Functions

While formulas work on individual cells, functions are pre-built operations that can perform calculations on a whole range of cells at once. This is much more efficient than adding up lots of cells manually. Functions like SUM, AVERAGE, MAX, MIN, and COUNT are essential tools for analysing the data in your model and producing summary statistics.

=SUM(range)

=AVERAGE(range)

=MAX(range)

=MIN(range)

=COUNT(range)

=COUNTA(range)

Key term

Function: A predefined formula that performs a specific calculation on a range of values, such as SUM or AVERAGE.

Examiner insight

Using a function like =SUM(A1:A50) is always better than writing =A1+A2+A3... Examiners look for this efficiency. It's faster to write, easier to read, and less likely to contain errors.

Worked example 13 marks

A list of monthly sales figures is located in the range B2:B13. Write formulas to find:(a) the total annual sales,(b) the average monthly sales, and(c) the best sales month.

  1. 1

    Step 1: To find the total annual sales, use the SUM function. In a suitable cell, enter the formula '=SUM(B2:B13)'.

  2. 2

    Step 2: To find the average monthly sales, use the AVERAGE function. In another cell, enter '=AVERAGE(B2:B13)'.

  3. 3

    Step 3: To find the best sales month (the highest value), use the MAX function. In a third cell, enter '=MAX(B2:B13)'.

Recap

  • Use SUM to add up all numbers in a range of cells.
  • Use AVERAGE to calculate the mean of the numbers in a range.
  • Use MAX and MIN to find the highest and lowest values in a range, respectively.
  • Use COUNT to count how many cells in a range contain numbers.
  • Use COUNTA to count how many cells in a range are not empty (contain numbers or text).
  • A range is specified with a colon, for example, A1:A10 means all cells from A1 to A10.

Quick check

  1. Which function would you use to find the lowest price in a list of items?1 mark
  2. What is the difference between the COUNT and COUNTA functions?2 marks

7. Testing and Validating Your Model

Creating a model is only half the job; you must also test it to ensure it is accurate. Testing involves using 'what-if' analysis by entering a range of input values and checking if the results are what you expect. Use simple values (like 0, 1, 10), typical values, and extreme or unusual values. Does the model behave correctly if sales are zero? What if costs are higher than revenue? Data validation is a feature that helps prevent errors by restricting the type of data that can be entered into a cell, for example, only allowing whole numbers, dates, or items from a specific list.

Key term

What-if Analysis: The process of changing values in cells to see how those changes affect the outcome of formulas on the worksheet.

Common pitfall

Only testing a model with one set of 'normal' data is a major pitfall. Models can often appear to work correctly for typical inputs but fail with edge cases like zero or negative numbers.

Worked example 14 marks

You have a model that calculates profit with the formula =Revenue-Costs. Revenue is in B1, Costs are in B2. How would you test that the model works correctly?

  1. 1

    Step 1: Test with normal data. Enter Revenue=1000 and Costs=600. Check that Profit correctly shows 400.

  2. 2

    Step 2: Test a break-even scenario. Enter Revenue=1000 and Costs=1000. Check that Profit correctly shows 0.

  3. 3

    Step 3: Test a loss-making scenario. Enter Revenue=1000 and Costs=1200. Check that Profit correctly shows -200 (a negative value).

  4. 4

    Step 4: Test with zero values. Enter Revenue=0 and Costs=500 (representing fixed costs). Check that Profit correctly shows -500.

Recap

  • Always test your data model to ensure the formulas are correct.
  • Test with normal, extreme, and zero values to check all possibilities.
  • 'What-if' analysis is the process of changing inputs to observe changes in outputs.
  • Data validation can be used on input cells to reduce the chance of incorrect data entry.
  • Check your results against calculations done by hand or with a calculator to verify accuracy.

Quick check

  1. Give one example of an 'extreme' value you might use to test a model that calculates student exam percentages.1 mark
  2. What is the purpose of using data validation on an input cell?1 mark

End-of-chapter exercise

Test yourself on the whole chapter. Work through these before moving on.

  1. Explain what a spreadsheet data model is and describe one benefit of using it.3 marks
  2. Cell A1 contains the value 100. Cell A2 contains the value 25. Write down the spreadsheet formulas to calculate: (a) The sum of the two values. (b) The result of dividing A1 by A2.2 marks
  3. A list of 20 product prices is in cells C2:C21. A single cell, F1, contains the discount rate of 15%. Write a formula for cell D2 to calculate the discounted price for the product in C2. The formula must be able to be replicated down to cell D21.3 marks
  4. Explain the difference between a relative cell reference (e.g., B4) and an absolute cell reference (e.g., $B$4).2 marks
  5. A model calculates a delivery charge. The order total is in cell G5. Write a formula that charges £5 for delivery if the order total is less than £50, and charges £0 for delivery otherwise.3 marks
  6. A teacher has a list of test scores for 30 students in the range B2:B31. Write the functions needed to find: (a) The average score. (b) The highest score. (c) The number of students who took the test.3 marks
  7. Describe two different types of test data you would use to check that a data model is working correctly, and explain why each is important.4 marks
  8. A small business models its weekly profit. The model has the following inputs: 'Items Sold' (in B1), 'Price per Item' (in B2), 'Cost per Item' (in B3), and 'Weekly Rent' (in B4). Create the formulas to calculate: (a) Total Revenue (in C1), (b) Total Costs (in C2), and (c) Final Profit (in C3).4 marks
  9. A spreadsheet is used to determine a salesperson's commission. Their total sales are in cell A2. They earn 5% commission on all sales up to £20,000, and 8% commission on any sales *above* £20,000. Write a single formula using an IF function to calculate their total commission.5 marks
  10. You are asked to create a data model for a mobile phone contract. The inputs are 'Monthly Fee' (B1), 'Minutes Included' (B2), 'Cost per extra minute' (B3), and 'Minutes Used' (B4). Write a formula in B5 to calculate the total monthly bill. The bill is the monthly fee plus any charges for extra minutes used.4 marks

Go deeper

Practise and revise with member-only material for this chapter.

Workbook

Exam-style practice questions with full worked solutions.

Unlock with a plan

Chapter at a Glance

A single-page summary of every key idea for fast revision.

Unlock with a plan

Free notes are just the start.

Unlock every Workbook and Chapter at a Glance, and generate your own worksheets and predicted papers.

Explore plans

Related chapters

Hardware and softwareInput devices and their usesStorage devices and mediaNetworksMicroprocessor-controlled devicesCommunication