Lesson video

Hi, I'm Cash, your computer science teacher for the spreadsheets unit.

In this first lesson, we're going to have a warmup.

We're going to see where your skills are and what we need in order to complete the remaining lessons.

For this lesson, you're going to need a pen, some paper, and you're going to need to remove any distractions that are going to get in your way of focusing.

Once you've done that, let's begin.

In this lesson, you will create a spreadsheet model for a given scenario, utilise a formulae to calculate income, and apply cell formatting.

Spreadsheets data.

So how much do you remember about spreadsheets? The Starter activity is a labelling exercise to help you remember common spreadsheet terminology.

On the left-hand side, we've got a spreadsheet model with the labels one to six.

On the right, we've got some key words that correspond to the labels one to four.

And number five and six are to do with formulae that you can use within the spreadsheet.

So let's have a go.

So have a go at labelling the spreadsheet model.

Okay.

Let's see how you got on.

So number one, it corresponded to column.

Did you get that? So columns are often alphabetical.

So it's A to Z, it even goes past A to Z as well.

And number two was a row.

So rows are numerical.

Number three was cell B10.

Number four, the last one that's left, is the active cell because that's the cell that's being clicked on.

So number five, what is the formula in this cell? So as we can see a total's being calculated.

There are a couple of ways you could have done this, but the most efficient way would have been =SUM E2:E6.

If you did E2 plus E3 plus E4 plus E5 plus E6, it will give you the correct answer, but it's not the most efficient way.

Number six, the correct formula for that was C5 times B5, because we're times-ing the, we're multiplying the price by the quantity.

So task one, enter data and formulae.

Open the spreadsheet RSC Voting.

Pause the video to complete your task.

Task one, check the specs.

Using the worksheet and the spreadsheet RSC Voting, complete task one.

Resume once you're finished.

Task one, enter data and formulae solutions.

So as you can see, we've got a completed spreadsheet there with the correct formulae in there because we can see the total income is correct.

We can see total income from text messages and telephone is correct.

The problem that you probably encountered was the following.

So cell E9 was originally relative.

To change that to absolute, in the formula there it was D9 times, and then we've got the dollar symbol there before the column and the row.

Task two, charity donation and profit.

Charity donation.

I'm a Celebrity 2019 donated 15p from every vote to the Make-A-Wish charity.

Of course, Rock Star Challenge wants to do the same.

For every vote, RSC will donate 15p to charity.

Calculate the profit.

Once we know how much will be donated to charity, we can calculate how much profit RSC will make.

Pause the video to complete your task.

Task two, charity donation and profit.

Using the worksheet and the spreadsheet RSC Voting, complete task two.

Resume once you're finished.

Task two, charity donation and profit solutions.

So this task, if you found it challenging, that's perfectly fine.

It was to get you back into using spreadsheets and to really get you thinking.

So cell K4 was the first cell where we entered some information.

So 15 pence is represented as 0.

15.

Then we went into the donation to charity, and we looked at the telephone votes in cell L8.

Now, L8, the formula for it would.

The theory behind it is we're going to look at the votes for telephone, we're going to add all of them up, and then we're going to multiply it by the 0.

15.

So how we would do that is =SUM, and that'd be D9 all the way to D18.

All that would be in a bracket.

And then we would multiply by cell K4.

We wouldn't multiply it by 0.

15.

Now they both give us the correct answer.

However, when we multiply it by 0.

15, in the future, if we were to change the amount per vote, that wouldn't, it wouldn't get factored in, okay? So if we reference the cell, when we change the amount in the future, it will change the calculations within the spreadsheet model as well.

For text votes, in cell L9, we will do something very similar.

So it'd be =SUM, bracket open, H9 all the way to H18, bracket closed, multiply that by K4.

The total in L10, is it L10? So this spreadsheet is a little bit difficult to follow because the formatting is not very good.

That's going to be our next task, by the way.

So in the cell for the total, which is L10, we would just add L8 plus L9 together.

And it gives us the total of 559,735 pounds and 35 pence.

For the profit in cell L13, we would look at the total income that's coming in.

So we would add cell E19 plus cell I19.

So E19 plus I19, put that all around the bracket.

And then we would subtract the total that we give to charity, which is in cell L10.

And that gives us the total profit there.

How did you get on? It was a little bit challenging, but I hope you found it okay.

And even now that you know the solutions, maybe you can go back and you can improve your spreadsheet model.

Okay, formatting.

Formatting makes data more readable.

Cells containing money should be formatted as currency.

Some headings which cover multiple columns could be merged, and it might help to add a background fill colour to headings.

So for example, you can see how I've now formatted that spreadsheet.

Looking at it previously, it was difficult to follow.

So the easier something is to follow, the better it is, especially if it's going to be used by somebody else.

Pause the video to complete your task.

Task three, formatting.

Using the worksheet and the spreadsheet RSC Voting, complete task three.

Resume once you're finished.

Task three, formatting solutions.

For this task, I thought I'd show you the actual spreadsheet itself.

So looking at the spreadsheet here, we've got a range of options at the top to help us with the formatting.

So here I added a fill colour.

Here we've got some borders.

And here we've got certain words that are in bold.

Even here, so we can't really see some of the writing.

So as you can see V-O-T, that's being chopped off, so we can move that across.

If I double click on it, it usually gives it the right distance from the border there.

To manipulate the borders is the option here.

If I click onto it, I could choose what kind of border I'd like.

I can choose the line colour for the border.

And then I can even choose the thickness of the lines as well.

I can even have the dashed lines as an option.

Here we've got the fill colour that we can choose.

And the other options are similar to Word processes.

We've got our bold, italics, underline, and so forth.

Scenarios.

By changing the values in cells B4, B5, and K4, you can affect the amount of income from votes and how much will be donated to charity.

Spend a short time changing these values to see what happens.

What value does K4 have to be so that RSC donates 500,000 pounds to charity? What values would B4 and B5 have to be so that the total income was one million pounds? Have a go at this.

Pause the video and have an attempt.

Okay, these scenarios are there to get you thinking about how you can manipulate spreadsheet models and how different factors are taken into account, and making one change can have a knock on effect as well.

So that's the end of the lesson.

Thank you very much for joining me on our journey to learning more about spreadsheets.

Share your work with Oak National.

If you'd like to, please ask a parent or carer to share your work on Instagram, Facebook, or Twitter, tagging @OakNational and #LearnwithOak.

Thank you very much for your time today.

I look forward to seeing you on the next lesson.

Thank you and goodbye.