Spreadsheet tasks

Spreadsheet tasks

1. Task 1: Create a spreadsheet to meet user requirements

   • Create a spreadsheet model that will allow you to store details of the products as it is developed. To make sure it works is should be as efficient as possible.
     • TASK A: create a spreadsheet model that will allow you to store details of the products and customers and create invoices. Add any formulae that are needed to make the system as efficient as possible, making sure you use appropriate functions where necessary. enter relevant data that is provided by Sam and Sonia
       • TASK B: Add validation to your spreadsheet where it is appropriate
         • TASK C: Apply appropriate formatting to your sheet(s) and make sure that you have suitable headings to make it as clear as possible for Sam and Sonia to understand and what the different parts of the system do and where the data needs to be entered.
           • TASK D: You may want to add other features, such as comments,macros, input messages, conditional formatting to help Sam and Sonia use the system as easily as possible.

2. Task 2: Explain the formulae used in the spreadsheet model

   • Consider each part of your spreadsheet model in turn, and the requirements of "Make The Grade" as outlined in the scenario
     • identify where you have used formulae and explain why you have used them, including any functions you have used and say why you have used them

3. Task 3: Use the spreadsheet model to present information to support decision making

   • Task A: Sam and Sonia have given some additional user requirements. You need to show how your spreadsheet can be used to help them meet the requirements
     • 1: They promote their products at the local teachers' events. They want to be able to send out promotional material to customers in the local area. this could mean their next event will be in Birmingham, sothey need to identify customers in the Midlands area.
       • 2: They sometimes receive answer phone messages from customers, who do not always leave their telephone numbers. They need to be able to easily find a customers telephone number.
         • 3: They would like to include some quotes from satisfied customers in some of their advertisements. They would like to see their customer list sorted so that those who have made the most orders are at the top of the list. They can then start to contact those who have made the most orders to see if they would like to help.
           • 4: They sometime have enquiries from schools who are not currently customers. It would be helpful if they could find out about any of the existing customers in their area, especially if they have made than one order. they have just had an enquiry from a school in the North of England.
             • 5: They are thinking of having a special promotion for customers who have only made one order and who have to yet ordered for ore than a year. They want a list of those customers.

4. TASK B: Sam and Sonia want to see the break down of where their customers come fro, so they can see clearly how many customers come from each of the different areas. these are ( North, The Midlands and Wales). This could help them look for promotional opportunities.

   • Create a chart to show Sam and Sonia the information they want to see. Make sure you format and label the chart as clearly as possible.

5. TASK C:

   • Show how you can use your spreadsheet model to solve the following problems for Sam and Sonia. for each problem the results should be explained.
     • 1: A customer contacts the company. The customer wants to buy revision guides on DVD for Biology, Chemistry and Physics. They want two prices one fore buying all of these with a site licence and one for buying ten single-user licences for each.
       • A second customer contacts the company for a quote: - This customer would like English, Maths and ICT revision guides. For each subject the customer wants a DVD with site licence, also 30 student workbooks. If they do not choose next day delivery, find the cost for this customer.
         • You notice that this customer’s order will almost be sufficient to earn 10% discount. Try out some different scenarios to help this customer get what they want for the least amount of money, by purchasing additional student workbooks to give them 10% discount.
           • 3: A third customer contacts the company for a quote. This customer is interested in Maths and English. The customer would ideally like, for each subject, 12 single-user DVDs and 50 student workbooks but only has £1500. Find a range of different options to offer this customer, within the budget of £1500 and present them in a way that will help the customer choose easily between the different options. They will not need next day delivery.
             • Another customer contacts the company for a quote. They want to buy revision guides on DVD for all available subjects, each with a site licence. They also want 200 student workbooks for Maths. The customer has a grant of £5000 to spend and cannot go over this total. They will not need next day delivery. - Find the total cost of the goods wanted by this customer. - Find the maximum number of workbooks that could be ordered within the customer’s £5000 limit. - As this is such a large order, Sam and Sonia would like to be able to meet the customer’s request in full. Find out how much discount they would need to apply to bring the original order under £5000. OCR Level 1/2 Cambridge Nationals in ICT]