Lesson video

Hello, my name is Charu.

This is lesson five of six in Data and Information unit.

The skill set we have covered in previous lessons during this unit, such as copying and pasting cells, entering data, creating formulas, using functions such as sum and average, and an ability to apply formulas to multiple cells by duplicating.

These are going to come handy in this lesson, as you will now be able to apply your learning.

You will plan and calculate the cost of an event using a spreadsheet.

For today's lesson, you will need a pen and paper.

You will also need access to spreadsheet application.

You can use Google Sheets, or alternative software, such as Microsoft Excel or Purple Mash, to calculate.

Please take a moment now to clear away any distractions, including turning off the notifications on any apps, and find a comfortable place to work.

Please pause the video and get comfortable, and when you're ready, press Play.

In this lesson, we will create a spreadsheet to plan an event, use a spreadsheet to answer questions, explain why data should be organised, and finally, apply formula to calculate the data you need to answer questions.

You will be using a spreadsheet to plan a celebratory event for the class.

You will need to enter the data that you would like to include, and then use the final spreadsheet to answer the questions.

At the end of the lesson, you should be able to answer this question: What is the total party cost for your event? You have the opportunity to choose from one venue, activities, food, drinks, and what essential decorations would you like.

We will now be planning a party, and we will plan this party keeping in mind that we are planning for 30 children.

You can choose which options you would like for your party.

You can pick one venue, and then select what would you like from other options.

You can choose which activity you would want to include, what hot food you would want to choose, and you could also choose the drinks from the drink options.

Use this information to plan for the party.

P.

p, here, this means per person.

If something does not say per person, then that number would need to be divided by the amount of children in the class to work out the per person cost.

So, for example, if you choose hotel as a venue, you will have to divide 150 by number of children coming to the party.

In this case, you would divide by 30, as we are planning for 30 children.

You should first calculate how much your budget is.

If the budget is 15 pound per person, you should need to multiply this by how many people are coming for the party, which is 30, in this case, so we multiply the budget per person, 15, by number of people coming to the party, 30, which would then give us total budget.

The spreadsheet you will be using already includes data headings such as Item, Spending type, Cost, Quantity, Subtotal.

Why are data headings important? Do you remember? Because they enable us to keep the data organised.

Why should the data be organised? Because it makes it easier to read, understand, and perform calculations using the data.

I'm now going to demonstrate how to enter and organise data in the spreadsheet.

In the spreadsheet, you will need to add the number of people coming for the party, so let's put 30 in the cell G2, because we have decided we are planning for 30 people.

Budget per person is already filled in for you, so this information in cell H2 and G2 is going to help you to find the total budget.

So, we are going to use a formula, total budget equal to number of attendees.

Click on G2, times budget per person.

Click on H2.

Now press Enter.

450 is your total budget.

Now look at this tab two here.

This tab contains all of the choices for the event.

You can copy the information across, or type these.

So, I'm going to tell you now how to copy information from tab two to tab one.

So, for example, you have chosen school hall as your venue, so you would select all the information about school hall, right-click, copy.

Now go to tab one, right-click where you want to paste your information.

There, now we have got school hall as our venue.

Now you can go back to tab two again, and make other choices.

For activity, you can select the information you want to copy, so right-click, copy.

Go to tab one, right-click where you want to paste it.

There.

Likewise, you can go to tab two and copy information about food and drinks.

Choose the options that you want, and then you can copy them across from tab two to tab one.

You can now attempt task one.

Follow the link on the worksheet, enter and organise the data, make your choices and add them to the spreadsheet.

You can now pause the video, complete the task, and resume once you're finished.

Welcome back.

You're now going to use the data in the spreadsheet to calculate the cost for the event.

These two columns here, Quantity and Subtotal, are the ones you need to think about now.

You will need to think about the quantity of each of your items, and then use this to calculate the subtotals.

As you have included the number of attendees, in this case, it's 30 here, you can use this information to support your calculations.

What would happen if I entered this formula in the cell, G2 times one? The cell would show the value in cell G2, 30, multiplied by one, which is 30.

Multiplying by one doesn't change the value.

It's the same as writing G2 to duplicate the number of attendees.

So we will now be filling information in the Quantity column and Subtotal column.

I'm going to demonstrate now how to calculate data.

We will now start populating the spreadsheet and fill in the Quantity and the Subtotal columns.

How many hotels would you need? One.

How many party games would you need? One.

For lemonade here, says per person, we will add the formula =G2, which really means we need as many lemonades as many attendees are coming.

For some food items, what would you write if you wanted each attendee to have two items? So if one attendee's having two colas, you can say =G2 times two, so the quantity now is 60.

Do you know why we are using formula to fill in the quantity? What do you think? Have a moment to think.

Because by using the cell reference, the cell automatically update if you need to increase or decrease the number of attendees.

Let's say I decrease the number of attendees from 30 to 10.

Notice the change in quantity when I click Enter.

Look at this.

I'm going back to put the number of attendees as 30, and you can again notice the change in quantity.

There.

What do you need to do to work out the subtotal? Can you think? To work out the subtotal, you need to multiply the cost of the item and the quantity.

So, item and the quantity.

So I'm going to say equal to cost times quantity.

Press Enter.

150 pounds is the subtotal for the cost of water.

Now, if you remember from previous lesson that we can duplicate the formula.

We need to hover over this right corner until the sign changes to cross, and just drag to fill in your answers.

There we go.

Can you now populate your worksheet? You can now attempt task two for calculating data.

In the same spreadsheet you use for task one, calculate data for the Quantity and Subtotals column.

You may pause the video now, complete your task, and resume once you've finished.

Welcome back.

You now need to calculate your final figures.

To work out the total cost for each spending type, you will need to add all of the subtotals for each.

So for food, add all the subtotals to give you a food total cost.

Likewise, for drinks, would give you a drinks total cost, activity, for activity total cost, and venue, for venue total cost.

Once all of the cells have been completed, you will be able to calculate the budget left over, total party cost, and party cost per person.

You need to add the formulas into the cells underneath Overall party costs.

This handout will help you to complete your final figures.

Remember to format your columns correctly.

For example, the cost column is formatted for currency.

I'm now going to demonstrate final figures in the spreadsheet.

This is my completed party spreadsheet.

To get this venue cost, I looked at the subtotal for the venue.

For activity, the subtotal of activity.

Drinks total included these two costs.

Food total included the cost of food involved, and similarly, the essentials and decoration cost here included these four costs.

So, remember to use formula in these cells to get to these cost.

The budget left over was calculated by Total budget taking away Total party cost.

Total party cost included the sub-totals for all venue, activity, drinks, and decoration.

And then finally, total party cost divided by number of attendees gave me party cost per person.

You can now attempt task three to put in the final figures.

In the same spreadsheet that you have been using for task one and task two, calculate final figures.

Please pause the video, complete your task, and then resume once you're finished.

Welcome back.

Can you now answer these questions looking at your spreadsheet? What is the total party cost for your event? What other information could you tell using the data on your spreadsheet? I hope you enjoyed this task.

That brings us to the end of today's lesson.

Please save your spreadsheet somewhere you can find it, as you will be using this in lesson six too.

Well done on your hard work during this lesson, using a spreadsheet.

If you're able to, please take a picture of your work and ask your parent or carer to share it with your teacher.

And if you'd like, also share a picture of your work with Oak National on Twitter so that I can see your awesome spreadsheets too.

Bye for now, see you next time for lesson six.

Bye.