Hello, I'm Mr. Little.

Welcome to today's computing lesson.

We're gonna be learning about event planning using a spreadsheet from the unit an introduction to spreadsheets.

There's some really interesting and cool things spreadsheets can do and you'll be learning all about them today whilst planning an event.

So get your brains ready, and we're to work hard and try your best, and we'll make today's lesson a success.

Let's go.

By the end of today's lesson, you'll be able to use a spreadsheet to plan an event.

Throughout today's lesson, there are two key words that I want you to look and listen out for.

The first one is budget.

That's an amount of money that's planned to be spent.

The second key word is subtotal and that's a part of an overall total.

We'll learn about these words more as we go through today's lesson.

This lesson is split into two parts.

Before we plan an event using a spreadsheet, we are gonna be describing the benefits of using a spreadsheet.

So let's get cracking.

We are gonna be hearing a lot from Izzy and Alex today and here they're talking about how useful spreadsheets can be.

Izzy says, spreadsheets can be useful for organising and storing data.

Lots of people use spreadsheets in their jobs.

Alex replies with, spreadsheets can do many different calculations and can be used to help plan events.

So let's look at what Izzy says here.

Lots of people use use spreadsheets in their jobs.

That's right, I certainly do as a teacher.

And I'm sure if you asked teachers or adults that you know they might have used a spreadsheet in their job before.

And like Alex says, spreadsheets can do lots of different calculations that might be really, really useful for you when you are planning an event.

And you're gonna plan an event today in today's lesson.

So linking to what Alex said, we know that spreadsheets are useful when planning events.

And you can use them for lots and lots of different things.

Remember, they're like a big table and they can help you create lists of things you need.

It might be a shopping list or a list of scores you've recorded for a game.

They also are useful if you are spending money 'cause they might help keep track of your money and what you've spent.

They can also be really useful because they can use formulas to work out calculations.

And also, one of the most useful things is, you can change things without having to rewrite your entire plan.

And we are gonna be looking at about how to do that today.

Izzy asks here, why is it better to use a spreadsheet rather than a written table or other programmes? Really great question here from Izzy.

So one of the benefits of a spreadsheet is it can automatically calculate, organise, and update data.

So remember, spreadsheets can do complex calculations.

The way they're set out helps to organise the data you have.

And also if you change something, the spreadsheet data will update for you if you formatted it correctly.

Remember you can use formulas to add totals, sort and filter information, so choose information that you might want to see or might not want to see, and make changes again.

And also it helps save time and reduce mistakes.

Because when working with large amounts of data, spreadsheets help keep things organised and nice and neat.

A true or false question now to check your understanding.

Spreadsheets can make planning events more difficult as you can use formulas and other information.

Is that true or false? It's false, but why is that? Remember spreadsheets actually make it more easy to plan an event.

Formulas can speed up difficult calculations and often you only have to enter them once.

Another true or false question now.

A spreadsheet allows you to make changes to some of your data without you having to change all of your data.

Is that true or false? It's true and we're gonna be trying that today.

So when you're planning an event, you need to work out how much money you have to spend and what you are planning or what you are going to spend it on.

And this is called your budget.

And remember, budget is one of our keywords today.

So a budget is an amount of money that is planned to be spent.

Izzy and Alex give a really, a couple of really good examples here of what a budget might be.

So Izzy says, my family has a budget for doing our weekly shopping.

We plan out our meals and how much we can spend on the ingredients.

So Izzy's family here prepares and works out the budget, how much money they've got to spend and how much money they're going to spend for the food they eat each week.

Alex says that my family has a budget for buying Christmas presents.

We plan what to buy each other and how much we spend.

And when you are planning an event, it's really important to have a budget.

And that budget will help you work out how much you've got to spend and what you're gonna spend it on.

So let's check your understanding of what a budget is.

I want you to complete this sentence.

A budget is.

A, the number of items you are going to buy.

B, choosing what to spend your money on.

Or C, an amount of money planned to be spent.

Okay, so now we know what a budget is.

Let's have a look at an example.

And this is Izzy's budget for her food shopping.

So let's take a moment to look at the spreadsheet on the left-hand side here.

Along row 1, we've got the headings.

So we've got the items that Izzy's gonna buy or planning to buy, was planning to spend our budget on.

The meal that it links to.

The quantity, that's how many of each item she's gonna buy.

And the cost.

Let's look at row 2.

So Izzy on Monday is planning to buy some or planning to have some carrots.

So she's gonna buy them and she's gonna buy two of them.

And each one costs 50p.

Also for Monday, she's planning to have some potatoes.

So she's gonna, the quantity of them is gonna be two and each one of those is 75p.

Izzy's gonna have some beans on Tuesday and that's 1 pound 10.

Beef on Tuesday and that costs 5 pounds.

And then some custard on Friday that costs 2 pounds.

Have a look at cell C7 now.

That's the total spend.

And D7 is the amount that Izzy's planning to spend off her budget.

So the total of all of her items here costs 10 pounds 60.

That's the total she'll spend if she buys all of these items. Izzy's budget for her food shopping is 15 pounds.

So she's got 15 pounds to spend on the items she needs.

So she's planning to spend 10 pounds 60, which means she's got 4 pounds 40 left over to spend.

So a reminder, Izzy's keeping track of what she wants to buy in column A, which meal it's for and that's column B, how much of each item she needs to buy in column C, and the cost of each item in column D.

And she's used functions to work out how much she has spent and how much she's got left to spend.

So Izzy and Alex have been set a task by their teacher.

They're in charge of planning the end of year party for their class.

Lucky them.

Izzy says, we could use the spreadsheet to help organise and plan what we need.

That's a great idea from Izzy.

Alex says, we can also use the spreadsheet to plan how much we can spend on the party.

Another great idea.

Now I'm sure Izzy and Alex would like to have hundreds, thousands, millions of pounds spent on a party.

But a budget is a great way of organising how much they have and how much they're planning to spend.

So let's take a look at what they're gonna do.

Here's part of the spreadsheet that Alex and Izzy have used to plan their party.

I'll give you a moment to take a look at it.

And let's zoom in on some of the rows and the columns.

So column A shows the items that they want to buy.

So they're planning to buy or planning to have some party games, crisps, cake, plates, cups, and prizes.

Column B shows the categories for each of the items. So party games falls under an activity category.

Crisp sand cake fall under a food category.

Plates and cups fall under the essentials category.

And prizes fall under the activity category.

And column C shows the cost of each item.

Column D shows how many of each item they want to buy.

Column E shows the subtotal for each item.

And Izzy and Alex have used functions here, which is the item cost multiplied by the quantity.

Columns F and G show the total number of attendees and how much per person Izzy and Alex can spend, how much they've spent, and how much they've got left over.

So let's look at column, or sorry, cell G3 there on attendees.

There are 30 children in Alex and Izzy's class.

They've got 5 pounds per person.

That's their budget per person.

Multiplying that together gives 150 pounds in total.

And they haven't quite worked out how much they've spent and how much they've got remaining yet.

So now we know what a budget is and how useful a spreadsheet can be.

I want you to put that knowledge into practise by deciding whether these statements are true or false when you're planning an event with a spreadsheet.

So the first statement, spreadsheets can make event planning more difficult.

Is that true or false? Using functions in a spreadsheet helps with event planning.

A budget is an amount of money planned to be spent.

A spreadsheet doesn't allow you to change data if you've made a mistake.

Now there were some false statements in part one of the task.

So now I want you to rewrite the false statements to make them true.

So you might have to change or edit some words to make the statement true about event planning with a spreadsheet.

So let's take a look if the statements were true or false to start with.

So spreadsheets make an event planning more difficult is false.

Using functions in a spreadsheet helps with event planning is true.

A budget is an amount of money planned to be spent is true.

And a spreadsheet doesn't allow you to change data if you make a mistake is false.

There are two false statements for you to rewrite and let's have a look at an example of what you might have rewritten.

So for that first false statement, spreadsheets make event planning more difficult, hopefully you've rewritten something like spreadsheets make event planning easier.

And for the second false statement, a spreadsheet doesn't allow you to change data if you make a mistake, hopefully you've written something along the lines of a spreadsheet does allow you to change data if you make a mistake.

So we've learned about the benefits of using a spreadsheet to make a party plan and now we're gonna put that into practise and we're gonna plan an event using a spreadsheet.

Izzy and Alex here are talking about their party planning spreadsheet and it gives a really good summary of what we've learned so far about spreadsheets.

Izzy says spreadsheet are useful because I can use formulas and functions to work at how much each item costs.

And Alex replies with, spreadsheets are useful when planning an event because I can use my spreadsheet to manage my budget.

And remember, budget is one of our keywords today.

Now let's learn about our second keyword which is subtotal.

So a subtotal is used before working out a grand total.

And let's look at, let's break subtotal down into two parts.

So we've got the prefix sub and the word total.

And the prefix sub actually comes from the Latin which means under, below, or part of.

So here it means part of, part of the total.

And the total part of subtotal comes from the Latin word totalis which means the whole.

So what we are looking for here is part of the whole and that's what subtotal means.

The subtotal is a part of an overall or the whole total based on some of the values.

Let's check your understanding with a missing word activity here.

There are two words for you to fill in for this sentence.

The subtotal is a, of an overall.

The first word is part and the second word is total.

So the subtotal is a part of an overall total.

So knowing how much of your budget you are planning to spend involves working out the subtotals of the items you want to buy.

And remember, a subtotal is part of an overall total.

So let's look at column E here.

We've got the subtotals, 20 pounds, 9 pounds, and 20 pounds again.

And let's look at row 2.

So for party games, that's part of the activity spending, and the cost is 20 pounds for one of them that looks like there's gonna be one party game.

So the function here to work out the subtotal is 20 multiplied by 1.

The subtotal of party games is 20 pounds.

Let's look at row 3 for crisps.

That's part of the food spending type.

Looks like each bag of crisps costs 30 pence.

Now because there are 30 children in Alex and Izzy's class, they want to buy a packet of crisps for everyone.

So the subtotal of that is 30 pence multiplied by 30.

And that, the subtotal of that is 9 pounds.

And row 4 for cake, that's part of the food spending type.

The cost of each cake is 10 pounds.

Izzy and Alex want to buy two of them for their class.

So 10 pounds multiplied by 2 is 20 pounds.

So we've worked out the subtotals here for three of the items for the party.

Let's take a closer look at the formula that Izzy and Alex might use in their party planning spreadsheet.

And remember to work out the subtotal of each item, you're multiplying the cost by the quantity.

So the formula that Izzy and Alex would've typed in to cell E2 would be equals.

Remember all of our formulas start with equals.

Then we've used cell references C2.

We've used the asterisk, which is the operator for multiply.

So we've done equals C2 multiply it by D2 and we type that into cell E2.

And then remember, we can replicate that formula.

We don't have to type it in for each time we want to work out a subtotal.

We can replicate that formula down column E.

And you do that by hovering over the bottom right corner and dragging the black cross down.

So let's look a bit further down Izzy and Alex's spreadsheet into column E.

And they want to buy some squash and that's obviously part of the spending type drink for their spreadsheet.

The cost of a bottle of squash is 3 pounds and they need three of them.

They think that'll be enough for their class.

So what might they have to type into column E for their subtotal? Imagine they haven't got, they haven't replicated a formula yet.

They'd have to type in the formula, equals C5 multiplied by D5 into cell E5.

Remember that formula can be replicated down for any more items. So let's check your understanding now.

Imagine you are planning part of Izzy and Alex's party.

What formula is needed in cell E2 for the subtotal of plates? The formula I hope you've come up with is equals C2 multiplied by D2.

And remember we've used the asterisk as the operator for multiply here.

And you might have even worked out the answer here because we've done 10 pence multiplied by 30 and that is 3 pounds.

So in this check for understanding, you'll need to do a little bit of maths to work out the overall subtotal of these items. So if you're doing this on a spreadsheet, you'd use the function equals SUM.

That works out the total of whatever's in the range, cell range E2 to E4.

So I want you to add up cells E2, E3, and E4 to work out the subtotal of these three items. And the answer is 49 pounds.

So if you did this on a spreadsheet using that formula, you'd get 49.

But here we've done 20 pounds, add 9 pounds, add 20 pounds, I know two lots of 20 is 40 and add my 9 pounds, that gives you 49 pounds.

So it is time for you to plan your own party.

Now we've provided a spreadsheet for you to plan a party for your class.

Now lucky you, we've given you 15 pounds per person to spend.

From the long list of items on tab 1, I want you to select one venue, one activity, five food items, two drinks, and up to five essentials.

And I want you to add these items to your party planner on tab 2.

Then in tab 2, I want you to use formulas to work out the subtotal for each item and the total party cost.

And you have to be really clever here because if you go over your total budget, you'll have to change some of your items or the quantity of some of your items. On tab 3, I given you a completed example.

And to help you already, some cells have formulas already typed in.

Let's see an example of this on a spreadsheet.

Using the spreadsheet provided, you need to plan a party for your class.

You have 15 pounds per person to spend.

From the long list of items on tab 1, you must select one venue, one activity, two drinks, five food items, and up to five essentials and decorations.

Now let's work on the second tab, the party planner tab.

So you need to choose items from the long list and put them into your party planner.

So I'm gonna choose the school hall as my venue.

I'm gonna highlight what I need.

I'm gonna right-click and copy and I'm gonna paste it into my second tab, which is the party planner tab.

And you can see my first item is the school hall, that's my venue.

And the cost is nothing, 'cause we are at school and we don't have to pay for the school hall.

Lucky us.

Then I'm gonna choose one activity.

So let's say we have a disco, I'm gonna right-click, I'm gonna copy, and I'm gonna paste into my party planner.

And I'm gonna do that for drinks, food, and essentials and decorations.

Now I'm gonna add in the quantity I need for each of the items. So I need one school hall.

I need one disco.

The blackcurrant juice is per person, so I need 30 of those.

30 orange juice.

I don't think everyone's gonna eat mozzarella bites, so I'm gonna go for 20.

Same for pizza.

Same for chips.

I think 15 people might have vegetable sticks.

15 people might have sausage rolls.

We need 30 paper plates, 30 cups, 30 napkins.

Let's have 20 balloons.

And let's have 30 party bags.

So I've got my cost and quantity of each item.

Let's use a formula now to work out the subtotal of each item.

So in column E, I'm gonna type in equals C2 multiplied by D2.

So I'm multiplying the cost by the quantity.

I'm gonna press Enter.

And luckily, the school hall for me costs nothing.

Now instead of typing the formula in for each of these sales, remember I can hover over my small blue dot on the right-hand side of a cell, and I can replicate my formula down.

And you can see very quickly it's worked out the subtotals for each of my items. Now I'm gonna add in the number of attendees.

I've got 30 people in my class.

My budget is 15 pounds per person.

Now I need to use a formula to work out my total budget.

So in cell I2, I'm gonna type in equals, and I'm gonna multiply cell G2 by H2 'cause I'm doing 30 multiply by 15 pounds.

And that's worked out, I've got 450 pounds to spend.

And here we've added in some formulas pre-made for you to work out the overall costs.

So here you can see the total venue cost is zero.

The total activity cost is 150 pounds.

The total drinks cost is 1 pounds 80.

The total food cost is 150 pounds 65 pence.

My essentials and decorations total 29 pounds.

I've got 118 pounds left over.

My party costs 331 pounds 45.

And my party cost is 11 pounds 5p per person.

Now if I want to here, I can spend more of my money, I've got some budget left over.

So I'm gonna increase the total amount of balloons I've got.

Let's say go from 20, let's say we have now we want 40 balloons.

You can see that's changed.

It's taken some money away from my budget, it's increased my party cost, and increased my cost per person.

If you want to, you can save your budget.

So your party plan will probably be different to Izzy's party plan.

And let's look at what Izzy's done with her party.

Looks like she's used formulas to work out the subtotal of each item.

And she's not gone over her budget.

It looks like she's got 4 pounds 37 in her budget left over.

Her total party cost is 445 pounds 63p.

And for each person she's 15p under.

So she spent 14 pounds 85 per person.

We've come to the end of today's lesson, so let's summarise what we've learned together.

Spreadsheets can be really useful when planning events.

And there's lots of great features that make event planning easier.

The organisation, the calculations, the formulas you use, all examples of those.

When you are planning an event, it's really important to think about your budget.

And a budget is a value of money that can be spent as part of a plan.

So in today's lesson, you've used a budget to plan a party.

And part of that budget is working out subtotals.

And subtotals are used to work out overall totals.

So it's important to have those in mind when you're planning an event using a budget.

Hope you've enjoyed today's lesson.

See you again soon.