Worksheet 2 Computer models
Spreadsheet modelling
Name:...................................................................................................... Class: ......................
Task 1
1. Save a copy of the spreadsheet TBNT Series1 USA [Link] in your own area. This
spreadsheet will hold the weekly voting figures of TNBT Series 1 in the USA.
2. Copy the following voting figures into the spreadsheet. The phone votes have already
been added for you.
Internet & App
Phone Votes Text Votes
Votes
Week Votes Week Votes Week Votes
1 289081 1 260148 1 574988
2 203697 2 180306 2 406966
3 239365 3 212520 3 474176
4 236140 4 207621 4 470488
5 219279 5 194426 5 436954
6 266922 6 237497 6 533646
7 222712 7 197138 7 444414
8 201476 8 179606 8 401966
9 265036 9 237065 9 526991
10 280512 10 249161 10 556052
Tip: When pasting data, you may want to paste the values only
to avoid any formatting such as gridlines being pasted.
1
Worksheet 2 Computer models
Spreadsheet modelling
3. In cell C12, enter a formula to calculate the income from Phone votes in Week 1. Try
using the formula =B12*0.55
What would be a better formula to use?
Why is this a better formula?
4. Copy the formula in C12 formula into cells C13 to C21. You can do this by dragging the
bottom right corner of the cell down through cells C13 to C21.
Notice that the formula automatically adjusts B12 for each row (relative cell referencing),
but $B$5 doesn’t change (absolute cell referencing).
5. In cell G12, enter a formula to calculate the income from Text votes in Week 1. Copy the
formula to the cells for weeks 2 to 10.
6. Enter the formula needed in K12 and copy into the cells for weeks 2 to 10.
7. In cell C22, you need a SUM function to calculate the total. Enter the formula
=SUM(C12:C21).
8. Enter a similar formula in cells G22 and K22.
9. In cell B8, enter the formula for Overall Income. This is the sum of the phone, text and
internet/app incomes. The formula is: =C22+G22+K22
10. Save your spreadsheet.
2
Worksheet 2 Computer models
Spreadsheet modelling
Task 2
1. Look at the screenshot of the formatted spreadsheet below. Experiment with different
formatting options to make your spreadsheet look similar.
Features that you might use:
Font colour
Font and font size
Merge cells
Background fill
Borders
To change cells to have a currency
format:
Select the cells.
Right click the cells.
Select Format Cells… from the menu.
Select Currency from the Category.
Select $ English (United States) from the
Symbol drop down.
3
Worksheet 2 Computer models
Spreadsheet modelling
2. If you need to add a new row, right click the number on the left, the select Insert from
the sub-menu.
3. Add the TNBT logo graphic to the spreadsheet. Use Insert > Pictures > This Device…
4. Save your spreadsheet.
Task 3 - Extension
Next year, TNBT would like to generate more than $3 million in income from voting.
1. Making changes to the phone charges in cell B5. How much would you need to charge
for each telephone vote in order to generate more than $3 million.
2. Reset the phone charges to $0.50.
Write down at least two different values that could be changed in the spreadsheet model
to help increase the overall income.