Task 4: Create a simple business spreadsheet
Assessment Objective 5 is assessed in this task.
In this task you will create a business spreadsheet for Phones Your Way which will allow you to track the income and expenditure, so that the company accounts can be produced at the end of each financial year.
You can recap the key elements of spreadsheets here.
The data below shows the recent income/expenditure for Phones Your Way. .
Income
Phones Your Way
SALES |
Order No |
Date Received |
Amount |
Vat |
1001 |
18-Apr |
15 |
2.63 |
1002 |
26-Apr |
8 |
1.4 |
1003 |
04-May |
23 |
4.03 |
1004 |
12-Ma y |
18 |
3.15 |
1005 |
20-May |
23 |
4.03 |
1006 |
28-May |
29 |
5.08 |
1007 |
05-Jun |
|
36 |
1009 |
13-Jun |
|
8 |
1010 |
21-Jun |
|
6.5 |
1011 |
29-Jun |
|
23 |
1012 |
07-Jul |
|
18 |
1013 |
15-Jul |
|
23 |
1015 |
23-Jul |
|
6.5 |
Expenditure
Phones Your Way
EXPENDITURE |
|
|
|
|
|
Date |
Description |
Amount |
18-Ap |
Web Host |
99 |
26-Ap |
Samsung |
504 |
04-May |
Nokia |
900 |
12-Ma |
Sony-Errikson |
640 |
11-Jul |
Motorola |
340 |
19-Jul |
Radio Promotion |
700 |
A Create a spreadsheet based on the figures provided, to calculate the income and expenditure. The spreadsheet should include appropriate titles and row and column headings. Ensure that you enter text and numeric data accurately. The following information is given to help you:
B Format the cells in your spreadsheet appropriately. Consider:
• Number format (decimal places, percentage, currency, date/time)
• Text formatting (font, size, style, alignment)
• Borders and shading.
C Use appropriate formulae/functions to calculate the income and expenditure for the business to date.
D Print out your spreadsheet(s) in full showing figures. 
E Print out your spreadsheet(s) showing the formulae in full to show the formulae used 
(The evidence could be in the form of annotated spreadsheet printouts/screenshots)
F The owner has noticed that order number 1001 has been entered incorrectly. The amount of
£15 should be £18 and the VAT should be £3.15. Change this entry.
G Reprint the spreadsheet highlighting the changes you have made and annotate to show how this has affected the totals.
H The owner has requested a password be applied to your electronic workbook. Produce a screenshot showing that the password has been set up.
(The evidence could be in the form of annotated spreadsheet printouts/screenshots)
To achieve the higher grades you will also need to undertake the following tasks:
I Order 1014 has now been paid and needs to be entered in an appropriate place in the spreadsheet
Order No |
Date Received |
Amount |
Vat |
1014 |
28-Jul |
16 |
2.8 |
J Order 1006 has been returned by a dissatisfied customer. Delete the entire row for this order from your spreadsheet
K Calculate the income/expenditure for each of the four months for Phones Your Way. You will need to display this data by adding new rows/columns to your spreadsheet as appropriate
L Use the average function to calculate the average income over four months
M Use appropriate headers and footers
N Print out the spreadsheet(s) using appropriate orientation and number of pages. You will also need to select the most appropriate print options (eg fit to page, print area, gridlines, page order)
O Print out the spreadsheet showing the formulae in full (The evidence could be in the form of annotated spreadsheet printouts) 
|
