Unit 7.4

Extreme Sports (Financial modelling and marketing)

Tutorial 1:Formulae: The basics line

Unit introduction

Sid with a snowboard and Suki with skis

Watch Opener 1 as an introduction to this unit.

Sid and Suki are mad for extreme sports, and they have set up a skate shop and a snow school in Scotland. The businesses are doing quite well, because so many teenagers are getting hooked on snowboarding and skateboarding. But they would like to make a bit more money, and they need your help!

In this unit you are going to help Sid and Suki run their businesses – Sid's Snow School and Suki's Skate Shop. You are going to help them increase their profit (that's the money they make) by using spreadsheets.

divider line

Unit learning objectives

A snowflake silhouette

In this unit, we are learning to:

line

Unit preparation

  1. You will need to set up a folder in your area and name it Unit 7.4 – Extreme Sports.
  2. You will need to understand how the cell referencing system works in a spreadsheet.
A fresh worksheet with Sid snowboarding across the front Start thinking about ...

 

Cell referencing

When you open a spreadsheet, you will see a gridded worksheet.

The worksheet has columns, which go up and down, and rows, which go across the page.

Each column has a letter and each row has a number. This means each cell can be given a reference.

An example cell reference is D12. Find cell D12 on the spreadsheet opposite.

line

Get started

A pair of snowboards

What do the following terms mean? Discuss your ideas as a class.

Now look at the following information:

Product = Elbow pads
Price = 15
Unit sales = 12

What formula would you use to calculate the revenue?

line

Try it out

In this unit, you are going to help Sid and Suki increase their profit by using spreadsheets.

Open a blank spreadsheet in Microsoft Excel and use the arrow keys to move around the spreadsheet. Watch as your teacher demonstrates the basic features of a spreadsheet, including colour fills.

For a fun example of colour fills on a spreadsheet, have a look at Resource 1e.

Spend five minutes exploring a blank spreadsheet yourself. Try out the 'Fill' tool too. Can you colour one column and one row?

Getting around a spreadsheet

Find out more about getting around a spreadsheet and formatting cells by watching Skills Demo 1a.

Now it's your turn. Open Resource 1a to colour cells and find out what the hidden school subject is.

Next, open Resource 1b – and select the tab at the bottom of the spreadsheet to select the worksheet called 'Suki's Skate Shop'. Answer the questions in the pale blue boxes. This will show that you can change font colours, fill cells and identify cell references. Remember to save your version of Resource 1b in your folder.

Tips

The key things to remember are:

  • Click in the cell before using the Fill Colour/Font Colour tools
  • Click and drag on the 'Fill Handle' to copy the contents and formatting of one cell to the next.

Enter a formula

A spreadsheet can work out formulae. Can you remember what formula you used to calculate the revenue under the heading 'Get started' above?

Watch Skills Demo 1b to learn how to enter a formula.

Now return to your saved version of Resource 1b, and the worksheet called 'Suki's Skate Shop'. Work out the revenue for at least three of the other products on the worksheet.

Tips

Don't just type in a number. Use the cell reference either by typing it in or clicking on the cell.

Remember to save your work in the folder you set up at the start of the tutorial.

There is a much easier way to write in the revenue formula for each product. Watch as your teacher demonstrates how to click and drag on the 'Fill Handle' to copy the formula to other cells. This will only work if you have used cell references in your formulae!

Tips

Drag the 'Fill Handle' to copy a formula from one cell to others in a row or column.

Now work out the revenue for the remaining products on the worksheet. Don't forget to save your work!

Work out the total revenue

Before you work out the total revenue, watch Skills Demo 1c.

Now return to your saved version of Resource 1b. Enter a formula to work out the total revenue. How many different formulae can you find to work this out correctly?

Extension

You know how to use the sum function. Can you do something similar to work out the maximum (max) and minimum (min) revenue figures?

'What if?' questions

Still working with Resource 1b, this time select the second worksheet by clicking on the tab that reads 'What If Questions'.

Screenshot showing the 'What If Questions' tab on Resource 1b

Work through the questions. You will need to use the tabs to move between worksheets throughout this task.

line

Round it up

Discuss the possible ways to calculate total revenue. Which method did you use? What is the quickest way to work out total revenue?

Now work through Opener 1b to review your understanding of spreadsheets.

line

Homework

Open Resource 1d to revise how to work out revenue and write a spreadsheet formula. Remember to save your completed worksheet in the folder you set up at the start of this tutorial.