Hi, my name's Mr. Hall, and welcome to this lesson, which is called "Spreadsheet modelling" and is the first lesson from the unit "Spreadsheets." Thank you for joining me for this lesson.

I'm really looking forward to taking you through the content and learning with you today.

The outcome of today's lesson is that you'll be able to identify the key components of a spreadsheet and use a spreadsheet to model a given scenario.

In this lesson, we have four keywords which we'll be coming back to throughout the lesson.

So the first is cell.

A cell is a rectangular box used in a spreadsheet to store data.

So that's cell.

Formula, formula performs calculations in a spreadsheet.

Formatting, so formatting is modifying the appearance of data in a spreadsheet.

And finally, function.

Function is a ready-made instruction that performs a specific mathematical job.

So there's our four keywords, cell, formula, formatting, and function.

We've got two learning cycles that we'll go through today.

So the first is we are going to identify the key components of a spreadsheet, and then after that, you're gonna use a spreadsheet to model for a given scenario.

So let's get started with that first learning cycle.

So here we have a screenshot of a small part of a spreadsheet.

A spreadsheet is a digital document that's used to organise and store data.

They are used to help you analyse and present data in meaningful ways.

In a spreadsheet, a cell is a rectangular box used to store data.

The active cell that you have selected is highlighted.

So in this case, we've got cell A1 highlighted.

A row is a line of data in a table that runs horizontally from left to right.

So here you can see row 1 in this screenshot has been highlighted.

And a column is a group of data in a table that runs vertically from top to bottom.

So this one is column A, which has been highlighted.

You can find the column letters at the top of the spreadsheet and the row numbers on the left-hand side.

So you've got the letters running through A, B, C, D, through to E.

And then the row numbers down the left-hand side, 1, 2, 3, 4, 5, 6, 7.

So there's our column letters and our row numbers.

So, see if you can match the word or phrase to the definition.

So first of all, we've got the four words are cell, column, row, and active cell.

And then the definitions we have are the cell that you have selected, a group of data in a table that runs vertically from top to bottom, a line of data in a table that runs horizontally from left to right, and a rectangular box used to store data.

So match the word or phrase to the definition.

Okay, let's have a look at the answers.

So a cell is a rectangular box used to store data.

A column is a group of data in a table that runs vertically from top to bottom.

A row is a line of data in a table that runs horizontally from left to right.

And finally, an active cell is the cell that you have selected.

Each cell in a spreadsheet has a cell reference.

Cell references combine the column letter and the row number to identify each cell in a spreadsheet.

So here we have two cells selected.

We've got cell A1 and cell B2 selected.

So cell A1 has the word product written in it, and cell B2 has the number 4 written in it.

Using the cell reference makes it easy for you and others to locate a specific cell in a spreadsheet.

So a question from Andeep, "Does it matter which way round I use the letters and numbers for a cell reference?" And Sofia replies, "Yes, you should always say the column first and then the row.

For example, B5 rather than 5B." So that's always the case.

We always go with the column first and then the row, so it's always letter and then number.

When you need to select more than one cell, you can click and drag over multiple cells.

This is called a cell range.

So in this example, there's a cell range selected, and the cell range selected here is B2:B6.

So that means cells B2 through to B6 are selected.

A cell range is typed in the following format, so you have the first cell, then a colon, and then the last cell.

Cell range can include more than one row or column.

And using cell ranges makes it less likely that you'll make a mistake when typing out a long list of cell references.

It's also much faster to refer to a cell range in a formula rather than typing a large list of cell references, and you're less likely to make mistakes as well, once again.

What is the cell range for these cells? Well done, the cell range is A2:D3, so it's cells A2 through to D3.

And remember, a cell range can include more than one row or column.

So the reason this is A2 to D3 is the top left is cell A2, bottom right is cell D3.

So it's all the cells within that range are selected.

You can change the font style, font colour, or background colour of a cell.

This can make the data much easier to read.

So here, the font colour and the background colour have been changed in the headers.

Let's have a look at why we might do that.

So by changing row 1 to have a different background colour and font colour, you can make the table heading much clearer.

To make these changes, use the background colour icon and the font colour icon.

So the font colour one is the A with an underlined black bar below it, and the background colour icon is like the tipped-up pot of paint with a white bar underneath it.

Those icons are similar to icons you'll see in other packages such as word processors or paint packages or even desktop publishing, so you should be familiar with those icons.

When you enter data into a cell in a spreadsheet, you can choose the data format of the cell.

The data format is the style and format of the data in the cell.

So in this example, formatting these cells to a currency will automatically add the pound sign, add decimals, and make the numbers look much neater.

As well as currency, there are lots of other data formats you can choose.

Let's have a look at some common data formats that you might use.

So you may use plain text, which is a data format for letters or words.

So for example, the word trainers will be in plain text.

You can use a straightforward number data format, so a data format just for numbers.

So here we've got 10.

5 as an example of a number.

You could use percent, which is a data format for percentages.

Currency you've already seen, which is a data format for numbers.

So this example has the currency of pound with a decimal point and two decimal places after the decimal point.

You can use a data format for date, so a data format for dates with a day, month, and year.

So an example there, 25/12/2024 in date format.

Finally, there's a data format for time, which is a data format with hours, minutes, and seconds.

In this example, we've got 10:30:01 seconds.

So, how do you change the data format in a spreadsheet? Let's have a look.

First of all, you select the cells you want to format.

So in this example, we have selected the five cells below cost, so the cells with the numbers 20, 12, 5, 8, and 8.

You click on the Format menu, and then you choose a format from that list.

So what's wrong with this spreadsheet, and how would you fix it? So the totals are correct, but column B has been formatted as a number, and it should be a currency.

Column C has been formatted as a currency, but it should be a number.

Answer these questions using the spreadsheet below.

A, which cell has the word trainers in it? B, write the word that is in cell C1.

C, write the number that is in cell B6.

D, which cell tells you the cost of shorts? E, what is the cell range of the cells that have been formatted as currency? And F, what is the cell range of the headings in the spreadsheet? So let's go through those answers.

So the cell with the word trainers in it for question A is cell A2.

The word that is in cell C1 is cost.

The number that's in cell B6 is the number 7.

Which cell tells you the cost of shorts? That's cell C5.

What's the cell range of the cells that have been formatted as currency? So that's cells C2 through to C6.

And what is the cell range of the headings in the spreadsheet? So that's cells A1 through to C1.

Now, explain which formatting tools have been used on this spreadsheet.

So background fill has been used on the titles.

The font colour has been changed on the titles, so the background fill is grey and the font colour is white.

The cost cells have been formatted as currency, so you can see the pound sign before those cells.

Now we can move on to the second learning cycle in this lesson, which is to use a spreadsheet to model for a given scenario.

When do you use spreadsheets? See if you can think of some examples.

So Andeep has an example.

So he says, "My mum uses one to keep track of her small business costs, like stock, prices, and profits." Sam says, "We used one in science last term to plot our experiment results and to draw graphs." And Sofia says, "My brother uses them at work all the time.

He tracks budgets and project timelines with them." So there's three really good uses for spreadsheets.

A spreadsheet model can be used to represent a real-world situation using numbers, data, and calculations.

A model helps you to understand what's happening in a real-life scenario, test out different ideas or decisions, or make predictions or plan ahead.

So models are really, really helpful.

Here's some key features of a spreadsheet model.

So it will definitely contain data, which is information entered into the spreadsheet.

So that could be things like prices, quantities, or dates.

It will include formulas.

Those are instructions that tell a spreadsheet how to calculate something, and that can be totals, profits, averages.

So we'll look at some examples of those in a little while.

And there'll be some rules, which are guidelines or constraints used in the model.

So that could be budgets or stock limits.

And again, we'll look at some examples of those in a short while.

So one of the key things spreadsheets can do is to perform calculations.

To do this, you need to use a formula, and formulas are special rules that use operations.

And Lucas points out that "a spreadsheet is like a calculator.

I can use formulas in a spreadsheet to perform calculations." So have a look at this table in a spreadsheet.

So we've got game 1, game 2, and total across the top, and then the numbers in row 2, 8, 2.

And then in C, we've got this =A2+B2.

So Lucas wants to calculate the total score from game 1 and game 2.

So he needs to use the operation + and type the formula =A2+B2 into the cell C2.

So that's where that =A2+B2 comes from.

Once you've typed a formula into a cell and pressed enter, the answer will be displayed in the cell.

So Lucas types the formula =A2+B2 in cell C2 and presses enter.

And Sam asks, "What's the formula, operation, and calculation?" So let's have a look.

So Lucas replies, "The formula is A2+B2.

The operation is + and the calculation the spreadsheet's doing is 8 + 2 = 10." So there's our formula again, which is =A2+B2.

And once you press enter, the total will appear in the cell.

Time for a quick knowledge check.

A formula performs a what in a spreadsheet.

Is it A, cell reference, B, task, C, calculation, or D, column? Well done, the answer is calculation.

A formula performs a calculation in a spreadsheet.

In formulas used in spreadsheets, some of the operators are the same as those used in maths, but some are different.

So to add, you use the mathematical symbol plus sign, and the spreadsheet operator is the same, plus sign.

For subtract, you use the minus sign, and again, that's the same in a spreadsheet operator.

For a multiply, the mathematical symbol is a times sign or a kind of an x, whereas a spreadsheet operator is an asterisk.

And to divide, so there's the divide sign as the mathematical symbol, the spreadsheet operator for divide is a forward slash.

So let's go through all those again.

So add in a spreadsheet is the add sign or the plus sign.

Subtract is the minus sign.

Multiply is an asterisk.

And divide is a forward slash.

In addition to calculation, formulas can also perform other functions.

Formulas in a spreadsheet always start with the equals sign.

This tells the spreadsheet that you want it to perform a calculation.

When you write a formula in a spreadsheet, it's best to use cell references instead of typing values indirectly.

This way, if the values in the cells change, the formula will automatically update the answer.

A function in a spreadsheet is a ready-made instruction that performs a specific mathematical job.

Functions can be used within formulas.

They save time by doing calculations instantly, reducing errors and making more complex tasks easier.

You should use preset functions wherever you can.

So here's some examples.

So SUM adds all the numbers in a range, so that's the function SUM.

AVERAGE works out the mean of the numbers in a range, so that's the function AVERAGE.

And COUNT counts how many numbers are in a range, so that's the function COUNT.

So you've got examples of three functions there, SUM, AVERAGE, and COUNT.

When using functions, you can either type the function you require in the formula or select the cell range and use the function button to select the function.

You need to use brackets when typing the cell reference.

So here's some examples of some functions.

So if you wanted the sum of the cells A2 to B2, you'd write SUM(A2:B2).

So for average, you say AVERAGE with equals on the front, =AVERAGE(A2:B2).

And then finally to count, you say =COUNT(A2:B2) So there's three examples of functions, one for SUM, one for AVERAGE, and one for COUNT.

And on the right-hand side, that's what the function button looks like.

So it kind of looks like an M on its side, so that's the function button.

So a question from Lucas, "Can spreadsheets automatically write formulas?" Well, spreadsheet software can sometimes offer suggestions to enter formulas, but you still need to write your own formulas correctly to make sure the data will be displayed in the exact way you need it to be.

Software doesn't always get this right.

Let's have a look at an example of a spreadsheet and see if you can work out what the formulas are in some of the cells.

So have a look at cell D5.

What do you think the formula is in the cell D5? Well done, it's B5 multiplied by C5.

So the price multiplied by the quantity gives the total price.

What's the formula in cell D7? Well done, that is =SUM(D2:D6) So that's the sum of all the total prices for the items all totaled together to give your grand total, which is 9 pound 57.

So that's sum =SUM(D2:D6).

Here's another example of a spreadsheet.

Sofia has created a model for her weekly food shop.

Which functions has Sofia used to work out how much she spent? How did she calculate what she has left to spend? So she's used a few here, so the first one we can look at is =CD*D2.

So that calculates the total cost for each item, and she's repeated that for each row where there are items. So we've also got C3*D3 through all the way to C6*D6.

So that's each row, she's multiplied the quantity by the cost.

She's also done a sum in column E.

So she's done a sum of column E2 through to E6, so that's =SUM(E2:E6) to add up the total amount spent.

And she's done =E8-E7, so that's her total budget minus her total spend to calculate the budget she's got left over.

Rockstar Challenge is a televised competition where people can text or phone to vote for who they want to win.

Rockstar Challenge receive income from the texts and the phone calls, and they also want to donate a portion of this to charity.

A spreadsheet model can be used to track this.

Open the RCS Voting spreadsheet.

For cells E19 through to E18, use a formula to calculate the income from the telephone votes.

And for cells I9 through to I18, use a formula to calculate the income from text votes.

So here's what you may have come up with.

So for cells E9 through to E18, use a formula to calculate the income from telephone votes.

So that should look like this.

So it should be D9*B4 all the way through to D18*B4, making sure that you include all the rows from 9 through to 18.

So for cells I9 to I18, use a formula to calculate the income from text votes.

And that should look like this.

So it should be H9*B5 all the way through to H18*B5.

And once again, remember to include every row between H9 and H18.

So your next task, for cell E19, use a formula to calculate the total income from telephone votes.

And for cell I19, use a formula to calculate the total income from text votes.

Okay, so to calculate the total income from telephone votes, this is the formula that you should use.

So it's =SUM(E9:E18) So that's our range, E9 through to E18.

And then for the text votes, it should be SUM(I9:I18).

So once again, that's a SUM formula, and the range is I19 to I18.

Okay, next part of the task.

So use the value in K4 to calculate the following: In cell L8, use a formula to calculate the amount to be donated from telephone votes.

And in cell L9, use a formula to calculate the amount to be donated from text votes.

Okay, well done.

These are the formulas that you should have.

So, for the amount to be donated from telephone votes, you should have =SUM, and then inside the brackets, the range of the sum, which is D19:D18, multiplied by the cell K4.

And then in cell L19, we want the formula for the amount to be donated from text votes, so that should be =SUM, and then in brackets the range, which is H9 through to H18, so H9:H18, once again multiplied by the cell K4.

Your next task is in cell L10, calculate the total donated to charity.

Then in cell L13, calculate the profit.

Then format the cells that have charging or income values as currency.

And finally, for this part of the task, format any headings in the spreadsheet to have a background fill colour and a different font colour from the rest of the data.

So for question 6, in cell L10, calculate the total donated to charity.

So for that, you could use =SUM and then brackets the range, D9 through to D18, multiplied by K4.

And to calculate the profit, so in cell L13, you could have = and then in brackets E19+I19, minus L10 outside the brackets.

So these are the cells that you should have formatted so all the header or the total cells are formatted differently to make them nice and clear.

And this is what your spreadsheet should look something like.

Here's a summary of today's lesson, so spreadsheet modelling.

Key components of a spreadsheet include cells, rows, columns, and sheets.

Spreadsheet formatting can be used to visualise data effectively.

Mathematical operations can be performed within cells using formulas and functions.

And finally, a spreadsheet model represents real-world scenarios using data, formulas, and rules.

Thank you for joining me for today's lesson and learning along with me.

I really hope to see you again in the near future.