Unit 1 AO5

Business Spreadsheet

Every year, companies must produce a set of business accounts. They will do this using a spreadsheet or a special accounting program. For this task you will need to create a spreadsheet for Downloadable Tunes to track their sales and expenditure, which will help them to produce their accounts at the end of the year.

Use the template here to complete your work to pass standard. 

FOLLOW THESE STEPS CAREFULLY…

(A) Download and open this

YOU MUST PRINT SCREEN THE TABLE WITH NO CHANGES DONE TO A BLANK WORD FILE!!!

AO5 Screenshot 1

The is the spreadsheet without any changes done to it. Take a screenshot of this before doing anything to it.

here is an example of what it might look like once you have completed all the tasks
(B)

Format your spreadsheet to make it look more professional and easy to read. See an example (taken from ulearnit)

  • Your titles and column headers should be bold.
  • Titles could also have a slightly bigger font size.
  • You could use borders to create a grid around the table (not the titles).
  • You could add shading (cell colour) to header rows.
  • You could make sure that the cell format is correct (eg text, number, currency – with ‘£’ sign and 2 decimal places, dates as shown).
  • The text in each column could align accurately (eg all left-aligned / all centered / all right-aligned).

PRINT SCREEN ANY CANGES YOU HAVE DONE TO THE SAME WORD FILE UNDER THE LAST SCREEN SHOT

spreadsheet screenshot 2

Here I have formatted using some of the techniques and not all. This makes the headings stand out & cells that a money are made clear.

Click here to see videos of how you can format your spreadsheet. (from teach-ict.com)

(C)

Add formulae to calculate the profit/loss for the business. To do this, you should do the following:

  1. Add a Total Sales row to the bottom of the Sales table (the sum of the Amount and VAT columns).
  2. Add a Total Expenditure row to the bottom of the Expenditure table (the sum of the Amount column).

SCREEN SHOT THAT YOU HAVE ADDED THE TOTALS TO YOUR WORD FILE

speadsheet screenshot 3

In this screen shot I have just added the totals and also given them a row heading. They have been coloured to make them stand out.

(D) Make the following changes to your spreadheet and than comment about the changes it makes to your total. Screenshot any changes you made and use comments in your annotation to show changes.

SCREEN SHOT EACH ONE OF THESE TASKS NOTING THE DIFFERENCES THEY ARE MAKING TO YOUR TOTALS AS YOU MAKE THE CHANGES

1. The owner of Downloadable Tunes has made a mistake on order number 1001 – the Amount should be £18 (not £15) and the VAT should be £3.15 (not £2.63).

2. Order 1014 has now been paid and needs to be entered into the spreadsheet. You will need to add a new row and make sure that the formulae still work (check they include the new row).

Order No Date Received Amount VAT
1014 28-Jul 16 2.8

3. Delete order 1006 as the customer has returned the item. You simply need to delete this row.

spreadsheet screenshot4

This screen shot shows I have changed order 1001. You should comment on what changes it made to your totals

spreadsheet screenshot 5

Here I have highlighted that I have inserted a row and added order number 1014.

Spreadsheet screenshot 6

Here I have highlighted that I have deleted order number 1006 which as been cancelled by a customer.

Inserting a new row

Deleting a row

NOW PRINT YOUR SPREADSHEET OUT IN EXCEL AFTER making sure you have used PRINT PREVIEW to check your work fits on one page.
You must also print your work in formula view so that we can see what formulae or functions you may have used. See the video below on how to do this.

How to use formula view in Excel

embedded by Embedded Video

How to use the sum function

embedded by Embedded Video

Download Video

How to format your cells

embedded by Embedded Video

Download Video

How to use the average function

embedded by Embedded Video

Download Video

How to insert headers and footers in Excel

embedded by Embedded Video

Download Video

Posted in OCR and tagged , , .