Extreme Sports (Financial modelling and marketing)Tutorial 1:Formulae: The basics
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.
Unit learning objectives
In this unit, we are learning to:
- Format cells in a spreadsheet
- Enter rules and formulae and check they are working
- Explain how rules work
- Predict what will happen when we make changes to variables
- Test models
- Create an advert using images and sounds
- You will need to set up a folder in your area and name it Unit 7.4 – Extreme Sports.
- You will need to understand how the cell referencing system works in a spreadsheet.
When you open a spreadsheet, you will see a gridded worksheet.
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.
What do the following terms mean? Discuss your ideas as a class.
- Unit sales
Now look at the following information:
Product = Elbow pads
Price = £15
Unit sales = 12
What formula would you use to calculate the revenue?
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.
The key things to remember are:
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.
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!
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?
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'.
Work through the questions. You will need to use the tabs to move between worksheets throughout this task.
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.
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.