scenario.

China Friends Ltd

China Friends is a small business that sells china models by post. Their current collection is shown in this table

The company updates its prices each year and keeps records of sales of each item. Prices and sales of these items for the last three years are shown below (Year 1 = 2007, Year 2 = 2008 and Year 3 = 2009).

VAT is currently 17.5% of the cost of goods, although that might change in the future. China Friends would like a spreadsheet for their stock records. This should have space for the three years’ prices and sales shown above, plus this year’s prices and space to add this year’s sales at the end of the year. They hope that this will allow them to update the figures at the end of each year, so that it will always show the last three full years’ sales, with room for data about the current year. They are considering expanding their ranges in the future, so they would like space for 30 different stock items.

Your spreadsheet should calculate:

• The total number of each item sold over the four years
• The total number of items sold each year

The amount (£) taken in sales for each item, for each year

• The total amount (£) taken in sales for each item, over the four years
• The total amount (£) taken in sales each year
• The total amount (£) taken in sales over the four years.

The company would also like a summary table showing the ranges (Animal Farm, Cute & Cuddly, Furry Friends, and Wildlife Safari) with:

• The total number of items sold in each range, for each year from year 1 to year 3
• The increase or decrease in sales for each range, from year 1 to year 3
• The total amount (£) taken in sales for each range, for each year from year 1 to year 3
• The increase or decrease in the amount taken (£) for each range, from year 1 to year 3, rounded down to the next whole £.

China Friends would like the spreadsheet to allow them to:

• Sort and search the stock list to find, for example, sales figures for a particular range or to show the list in order of sales in a particular year.
• Use the spreadsheet to help them predict future income.
• Produce some charts and graphs for their annual report – for example, to show the sales of different models in each range during the year, and to compare sales of goods in different ranges over the last few years.

China Friends wants the spreadsheet to be easy to use, as their staff have little or no experience of the use of spreadsheet software, although they are confident in their use of computers.

China Friends would also like the spreadsheet to have additional features but these are not essential:

• The calculation of the percentage increase or decrease in overall takings (£) from year 1 to year 3
• An easy way to see at a glance whether the sales of particular ranges have gone up or down. For example, this might be shown by making sales that have increased from the previous year appear in blue and sales that have decreased appear in red.
• An easy way of creating invoices for customers showing:
• The date
• A space to enter the customer’s name and address
• The stock code, range, description, price, number required and VAT for each item purchased
• The total cost of all the items purchased, the total VAT and the total cost with VAT
• A discount of 5% for all orders with a total value (including VAT) of over £50
• A delivery charge of £5.00 per order
• The final total.

As much as possible should be calculated automatically.

The invoice should be well-presented and should have space for 10 different items. It will be printed on company headed paper, so there is no need to include the company logo or contact details, but you should leave 4cm blank at the top of the page.

When China Friends receive enquiries over the telephone, customers often have a maximum price they are prepared to pay. The company would like to be able to use the spreadsheet to model the total price for different choices.