Lesson video

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

Welcome back to lesson three, where we going to be learning all about formatting cells correctly.

So in terms, if you've got currency, how to format them and then also a little bit more on conditional formatting.

So for this lesson, you got your pen, some paper and you need to remove any destructions that going to get in the way of you focusing.

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

In this lesson you will, use common formulae to model sales of merchandise, implement conditional formatting techniques and format cells correctly, e.

g cells representing money should be formatted as currency.

Starter.

There's always something to buy.

Any event, concert, gig or occasion will have its own collection of collectibles.

In this lesson, you'll be looking at the different types of merchandise that could be offered to visitors to the Rock Star Challenge Live events.

Spend a short amount of time researching the merchandise available for your favourite band.

Pause the video to complete your task.

Task one- Merchandise.

Using the worksheet complete task one.

Resume once you're finished.

Task one merchandise solution.

So this is a possible solution that you could have got.

It'd be interesting to see what you've got and at the end of the lesson I'll share with you how you can share your list of merchandise with us as well.

So we've got for example, Taylor Swift, Stormzy and so on the prices are there, the web address links there as well.

We going to use this data later, when we are doing certain calculations.

Complete the merchandise model.

Open the RSC merchandise spreadsheet, the item and cost price column have been populated.

Pause the video to complete your task.

Task two, complete the model.

Using the worksheet and the spreadsheet, "RSC merchandise" complete task two.

Resume once you're finished.

Task two, complete the model solution, how did you get on? Here's an example of a spreadsheet that's been completed.

So the forecast might be a little bit challenging.

It is cell C eight minus B eight.

So, it's the selling price minus the cost price.

As you can see, the cost price is usually considerably less than what the item's being sold for.

So that was a point out there, and then we're multiplying it by the target that we're projecting to sell and that gives us the forecast.

A couple of pointers here, ensure that the cells are in the correct format.

So the cost and the selling should be in the currency format.

And the target price is fine and forecast is in currency as well.

Calculate forecast profit.

Using the RSC merchandise spreadsheet, calculate the total forecast and add conditional formatting.

Pause the video to complete your task.

Task three, calculate forecast profit.

Using the worksheet on the spreadsheet "RSC merchandise" complete task three.

Resume once you're finished Task three, calculating total forecast profit solution.

As you can see in cell G five, we've got the total forecast profit.

So that's done by using the formula =sum So what that's doing is adding up, all the cells from E8, all the way open tailed E22.

We could have used = but that's not the most efficient way of doing it.

What else we can see is that the cell is currently green and that's because it's meeting that target there.

If I was to change one of the, some of the figures here.

So if I was to change this target for the baseball caps to a 100.

We can see that that total forecast now has changed to red.

So how's all being done.

Let's have a look behind the scenes.

So that's all done using conditional formatting, if I click onto format-conditional formatting, we'll be able to see the two rules that are in play at the moment.

So if the value is less than whatever is in cell C5, it's going to turn red as the value is greater than whatever is in cell C5 is going to turn green.

How we did that, was click on the add another rule, we then chose the cell that we're looking at, we then chose the rule that's applicable which was less than, we then clicked on to the pin bouquet tool, to choose the colour.

So we're going to have red, and if it's less than, we can specify a figure.

So we could have written a one, five, zero, zero, zero, zero, zero.

We could have written in that way however, we made it a little bit more efficient by directing it to the cell.

That's because if we now change the target from here it will affect the remaining spreadsheet.

Format the spreadsheet.

To make the RSC spreadsheet more readable, we can add appropriate formatting to include things like borders, fill headings, bold headings, centre headings, and warp heading text.

Pause the video to complete your task.

Task four, Format the spreadsheet.

Using the worksheet on the spreadsheet, "RSC merchandise" complete task four.

Resume once you're finished.

Format the spreadsheet solution.

So as you see, the spreadsheet is much more readable.

It's easy to see the different headings that the boarders make it easy to see the different fill colours, they really add a lot of impact to that spreadsheet.

We'd love to see how you have format your spreadsheet.

So at the end, I'll share with you how you can get in touch.

Can you make a profit? You should have been testing your spreadsheet as each section was completed.

Now it's finished you can test it further.

Change the sales of items, change the prices of items in column C, you could experiment with changing the target profit and does the conditional formatting work? Pause the video to complete your task.

Task five- Can you make a profit? Using the worksheet and the spreadsheet, "RSC merchandise", complete task five.

Resume once you're finished.

Thank you for joining me on this third spreadsheets lesson.

I hope you've got a much better understanding of conditional formatting and formatting the spreadsheet.

We'd love to see what you've done, so we'd love to see the merchandise that you came up with and how you've formatted your own spreadsheet.

Share your work with us.

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.