GCSE ICT Coursework Booklet
2 0 0 6
"MUSIC SHOP"
SOUND ADVICE
Contents
Contents 2
Situation - P1 3
Problem - P1 4
Brief - P1 4
Analysis+Research-P1+P12 5
The Shop Layout: 5
Dis/Advantages of using ICT - P1 6
Dis/Advantages of using ICT - P1 7
ADVANTAGES: 7
DISADVANTAGES: 7
User requirements - P2 8
Diagram of information flow - P2 9
Specification - P2 9
Specification - P2 10
System requirements - P3 11
Hardware 11
Input devices 11
Output devices 12
Software Required 13
Training Required 14
Diagram of system links - P3 15
Social, moral + cultural issues - P7 16
Social Issues 16
Moral Issues 17
Cultural Issues 18
Identification of task subsystems - P4 19
Examples of work completed in the main applications 20
Microsoft Word 20
Microsoft Excel 21
Microsoft PowerPoint 22
Microsoft Access 23
Situation 25
Problem 25
Brief, Analysis and Research 26
. For the DATABASE 26
2. For the SPREADSHEET 30
3. For the WEBSITE 35
4. For the Advertisement 40
Time Management Table 43
Database 45
Spreadsheet 47
Spreadsheet 48
Website 52
Mail Merged Letters 56
Advertisement 59
Supporting Work/Communications 60
Evaluation tests - TPT3 + P11 62
Negative Results 62
Effectiveness report - TPT3 + P6 + P11 63
Note Play- TPT Work Produced 64
Database 64
Correspondence 64
Spreadsheet 64
Website 64
Independent working 65
Planning 65
Additional information 65
Situation - P1
The shop is situated in West Street in the prosperous city of Chichester. The shop is situated next to Costa Coffee and Waterston's book shop. It is a busy city on the south coast. Two music shops already exist in Chichester, HMV and MVC.
The name of the shop is Sound Advice(tm). It is a small independent business.
The shop is small and has little colour on the front. The sign is printed in white on to a black background. The layout of the shop is simple yet effective with three aisles in the centre of the store. On the whole, the store is used by a steady stream of customers between the ages of thirty and sixty, most of whom come no more than once every four weeks.
The staff consists of one manager and two shop assistants. They sell a wide range of music CDs of all genres. Also on sale is a small amount of DVDs and videos.
Currently no computer system has been installed. The staff use an electronic till and record all sales in a book. At the end of each month stock is recorded due to what purchases have been made.
Problem - P1
The shop does not appeal to a wide enough age range; this is perhaps due to the old fashioned methods. The stock management is not good enough as stock runs out after new releases. This leads to unhappy customers, as they do not get what they want. The shop is also a small business and is under competition from other major music shops such as MVC, HMV and Virgin.
Brief - P1
A new computer system will help keep track of customers. It will also help to control the stock as it could re-order stock automatically. It will provide the facility to create an Internet site to reach a wider audience and encourage more business. Mail merged letters can be sent to customers reminding them of any special offers. The shop can print posters advertising the shop and any available job spaces. The system will also attract more young people to the shop as it will give it a more modern image.
Analysis+Research-P1+P12
The main purpose of the computer system would be to organise the stock, to keep track of customers and to record sales. It would be to advertise the shop and help bring a wider range of ages. A website would also be created to attract younger people.
The layout of the shop will mean three computers will be needed. One in the manager's office, one by the till and one in the storeroom to keep control of stock. The system will need to be small as there is not much space available.
The Shop Layout:
The three computers this will help the shop run smoothly and speed up the service. A main server will link the computers. The product prices will be linked
by the website. The shops computer network will link the database and the shop finances.
Three computers will be needed. These will be high quality, fast and will have a modern image. The software needed will be:
* Database - MS Access
* Leaflets - MS Publisher
* Website - MS Front Page
* Spreadsheet - MS Excel
The data required will need to be quick to access but secure. The data required is as follows:
* List of customers who use the shop - data protection act applies.
* Information on the staff, like salaries and appraisal details - data protection act applies.
* List of all stock.
* List of suppliers.
I need to think about how to capture the information and how to put it in to the computer system. The suppliers would be typed in on the keyboard, with reference to the Internet. The list of stock would be recorded by the tills sending a list of sales to the manager's computer; the manager will then re-order stock when he feels it is necessary. The list of customers will be recorded by taking down the customers details after each sale.
The documents would move through the system using the network. Any work that needs to be taken home would be e-mailed or put on to CD. CD's would also be used to back up data in the event of a system failure.
All of the week's products will need to be added up by using SUM formulae. The customers will be sorted alphabetically by name. The manager may want to find out what customers like certain types of music. They could also search for a particular product that is not selling well. The lists would then be saved or printed for later use.
The staff would need to be trained how to use the system. All staff would either have to go on a training course, or use a training manual. A help guide would also be accessible on the computer. There would also be booklets to use.
The data would be made secure from viruses or hacking by installing a firewall. Passwords would be applied to all necessary documents and users would have to enter a password to access the computers.
It would be helpful to back-up the data on the system because this would prevent any documents from being lost. This would be done by transferring necessary documents on to CD. This procedure would be carried out every week.
Dis/Advantages of using ICT - P1
ADVANTAGES:
* It would be easier to keep track of customers - a database with customer details to monitor their purchasing habits and mail out offers to them.
* Stock control would be more efficient.
* You would be able to make a website.
* You could introduce online shopping.
* Online information - customer e-mail Q&A.
* Finance system/accounts.
* Staff payroll system.
* System will make it easier to find out what sells and what doesn't.
DISADVANTAGES:
* The system may be costly.
* Training may be required to use the system.
* Data could by lost from hackers or accidental loss.
* The system may slow things down initially.
The installation of a new computer system will clearly be an advantage for the shop even though it will cost a lot of money to buy and set up properly with good security and back-up systems and it will also be expensive to train staff to learn the new system well enough not to slow the business down.
The new computer system will lead to more efficient working systems which will save money by speeding up things like the payroll and accounting. It will improve stock control and stop money being lost when CDs aren't available to buy. A customer database will help to increase sales by improving marketing campaigns. A website will enable the shop to be open 24/7 and reach people outside the town.
User requirements - P2
Manager: The manager's job would be to make sure the shop runs smoothly and reaches the required standard needed to keep it in business. They would also have to make sure that the other members of staff are working to an expectable standard. The manager would also sort out finance. The managers work half a week each in the shop and bring necessary work home with them. They would require a computer of their own that would be linked to the network. They would require a Microsoft office package and other basic programs. The managers would already have a basic knowledge of ICT but would need to go on various courses to learn new skills. The managers would require a link to the Internet (for research etc.). They would need security (a firewall). The system would have to be easy to use as well.
Technician: The technician's job would be to make sure the computer system is running smoothly, to do necessary repairs and checks. The technician would be part time and would come in once every week. They would require a laptop of their own and the necessary tools needed for the job. They would need to go on a more advanced training course in computers. They would require from the system ease of use and it would need to be low maintenance.
Staff: The shop will employ six staff they work 8.30 until 5.30 alternate days. The three staff would take alternate turns at operating the till. The other two will help to keep the shops shelves organised, and assist customers in finding what they want. A computer would be needed at the checkout which they would all share. They would already have enough ICT skills to operate the computer and would require basic training. The computer would need to be fast and easy to use.
Diagram of information flow - P2
Specification - P2
. The service must be quicker and more efficient.
2. It must make it easier to keep track of customers.
3. It must provide a website, which would mean a 24-hour service.
4. Stock must be easier to keep track of.
5. It must be easier to keep a record of what sells and what doesn't.
6. You must be able to e-mail customers with special offers.
7. The equipment must be cheap, but of good quality - the shop has around £3500 to spend on the new computer system.
8. The system must be easy to ...
This is a preview of the whole essay
2. It must make it easier to keep track of customers.
3. It must provide a website, which would mean a 24-hour service.
4. Stock must be easier to keep track of.
5. It must be easier to keep a record of what sells and what doesn't.
6. You must be able to e-mail customers with special offers.
7. The equipment must be cheap, but of good quality - the shop has around £3500 to spend on the new computer system.
8. The system must be easy to maintain and reliable.
9. The server must have a hard drive of 80GB or more and an operating speed of 2GHz or more.
0. The computers must have an operating speed of over 2GHz and hard drives of 60GB or more.
System requirements - P3
Hardware
The main server used will require a screen, along with a keyboard and mouse. The server chosen is an IBM eServer, it has 512 MB of ram, an 80 GB hard drive and 2.6 GHz operating speed. The total cost of the server is £500. The screen chosen is a Dell E156FP 15" mainstream LCD Flat Panel Monitor. The total cost is £178.60. A computer in the manager's office will be needed. It will need a keyboard, a mouse and a screen. The computer chosen is a Dell DimensionTM 9150; it has an operating speed of 3GHz, a 160GB hard drive and 512 MB of RAM. The total cost for the computer (including mouse, keyboard and screen) is £699.A computer will also be needed at the checkout. This will allow the staff to check for stock availability and for stock to be re-ordered when a purchase is made. It will also allow the customer to order an item at the till that is currently out of stock. The computer chosen is a Dell Dimension TM 9150. A computer will be needed in the stock room it will require a screen, a keyboard and a mouse. The computer chosen is a Dell Dimension TM 9150.
Input devices
The till will need a barcode scanner, the scanner chosen is Peninsula Phoenix II CCD Barcode Reader, it was chosen because it is good value for money and comes with software. The price for the product is £99. The computer will also need a credit card reader, a built in cash register and a chip and pin. The manager's computer will need a disk drive to read relevant information. A Chip and Pin device will be needed for customers to enter their pin code securely. The device chosen is the Ingenico 3330 PIN Pad Terminal. The Chip and Pin device costs £155.
Output devices
The server, the checkout computer and the manager's computer will all require modems. A printer would be needed with the stock room computer to print lists of stock, address labels, invoices and receipts. The printer chosen is an hp LaserJet 3800. The printer costs £60. A printer will be needed for the manager's computer, the printer will allow the manger to take necessary work home with him and he can print leaflets etc. The printer chosen is an hp LaserJet 3800. The computer at the till will require a printer for receipts. The printer chosen is Epson TMU-220D. The main server will require a disk drive to back up the hard drive by recording the data onto a CD. The checkout computer would power a small speaker system around the shop, which would play various CDs sold in the shop and would also keep customers up to date on all of the shop's special offers and forthcoming sales. The system chosen is the Logitech x-620. The system costs £99.
Software Required
The operating system chosen for the computers is Microsoft Windows XP Professional. It was chosen because it is compatible with the other software chosen and easy to use.
Microsoft Word will be used to produce letters and catalogues to send out to customers. Word was chosen because it is easy to use and is compatible with Microsoft Access.
Microsoft Access will be used to store the names and contact details of various customers that have registered on the website. Access was chosen because it can be used in conjunction with Microsoft Word for sending out letters using the mail merge function.
Microsoft Excel will be used to record all accounts, stock and finance which will be automatically updated when a purchase is made. Excel was chose because of its ease of use, for the formulae which perform complicated calculations and the chart function for visual analysis.
Microsoft Explorer will be used to explore the Internet. It was chosen because it is compatible with the other programmes and easy to use.
iTunes will be used for the in-shop music playlists, which will allow customers to listen to music they may wish to buy. iTunes was chosen because it is easy to use and to make playlists.
Microsoft PowerPoint will be used to make the shop's website. FrontPage was chosen because it is easy to use and compatible with the other Microsoft programmes used.
The software used for operating the barcode scanner with the computer will come in a package. The scanner chosen is Peninsula Phoenix II CCD Barcode Reader it was chosen because it comes with software, is good value for money and it is easy to use and set up.
The security software chosen is the McAfee Internet Security Suite. It was chosen because it is reliable and widely used.
Training Required
Manager: The manager already has a basic knowledge of Microsoft Office. However, it would be useful if the manager knew more advanced skills, so he will be sent on an advanced users training course hosted by Learndirect. The course length is 8 hours and will cost £50. Skills that he would acquire on the advanced training course include the more advanced features of Word 2003, such as creating and inserting tables, graphic objects and charts, customising toolbars and using mail-merge for letters, envelopes and labels.
He will still need a help manual which he can refer to if he comes across a problem. The manual he will use is the Step-by-Step Office 2003, which provides simple, easy to read training. It will cost £25.
Technician: The Technician has good experience in his job. He will still require a training manual so he can be more efficient. The manual he will use is the Haines computer troubleshooting. It will cost £15.99. He can use it for reference if he comes across a difficult problem.
Staff: The staff have a very basic understanding of Microsoft Office and will need to go on a training course so that they can use the system quickly. The course they will go on is a Microsoft Office training course. The course is run by abcdee. The Office training includes Excel, Word, PowerPoint and Outlook. The company offer training in Microsoft Office at all levels, from introductory through to advance. The course lasts for two days and costs £377, then a further £37 for each additional person.
Diagram of system links - P3
Social, moral + cultural issues - P7
Social Issues
* The staff need to be trained to use the computer so that they find it easy to operate. An initial Induction programme should be in place for new members of staff.
* Working conditions would be affected because the computers would cut down on work load. But there could be implications on staff's health through increased use of computers.
* As the system will make the shop operate in a more efficient way not as many staff would be required to produce the same amount of work. But as the shop becomes more successful, more staff may be needed to expand the business. So whilst creating less job opportunities in the short-term the system may eventually create more job opportunities in the long-term.
* We need to ensure that the working environment for the staff is conducive to enhancing employee performance. There are Acts which employers need to adhere to on working conditions. Pay and conditions are also of importance for ensuring quality staff with appropriate experience for the position they are employed in. There are minimum wage conditions which will apply to all staff employed by the business.
* The introduction of computers would mean that people would interact with each other less because they are concentrating on the computer rather than what is going on around them. This could be regarded as unhealthy so steps could be taken to enable them to talk via an online messaging programme such as MSN Messenger. Staff meetings could also be organised so that staff can interact and discuss matters that they feel are important.
Moral Issues
* Staff computer terminals must be set up so that staff users don't suffer from poor posture or repetitive strain injuries.
* Staff will be encouraged to take regular breaks to ensure that they do not get eye strain.
* Suitable lighting conditions should be put in place to avoid possible eye problems.
* Care should be taken to ensure that staff members' fitness is not adversely affected by sitting at the computer for long periods.
* The computer purchased should conform to all the required safety standards.
* The computer and all the software should be official products and not pirated, and should be purchased from morally responsible suppliers who follow fair employment, fair pricing and sound environmental policies.
* The computer purchased should be as environmentally-friendly as possible, ensuring that it:
o contains as many recyclable parts as possible;
o is energy efficient;
o operates efficiently, so that it doesn't consume excessive quantities of spare parts or consumable supplies;
o is durable so that it doesn't need replacing after a short time;
o is recyclable when it reaches the end of its life cycle.
Cultural Issues
* It is hard to limit the staff's use of the Internet.
* A Sound Advice IT Policy can be put in place to prevent abuse of the Internet; this includes viewing or sending things of an indecent or racist nature.
* Staff will be asked to sign and adhere to the shop's IT Policy.
* Filtering software will be put in place to block any offensive content being accessed or downloaded.
* The shop will have to adhere to the Data Protection Act as regards the personal information it holds on its customers and advise them that their private details will be safe.
* When the shop sends out promotional material by e-mail, it must ensure that recipients have agreed to receive this information, so that it is not classed as spam.
* The computer system could affect people of certain religions. For example some people don't believe in using technology therefore restrictions will apply.
* The computer system could set a bad example to younger and older people. It may make them think its ok to sit around on a computer all day, when they really should be out and about.
* By introducing the new system other companies may be influenced to introduce a new system too. Members of the public may also realise the shop has a new system and decide to get a computer for themselves.
* Staff that work on the computers may get behind on their work and if they don't have a computer at home they would not be able to catch up easily, so they may feel pressured to get a computer for themselves.
* The new computer system could affect people's moods negatively because they would be on the computer all day and may become depressed. The shop should introduce a policy so that all staff have sufficient breaks from the computers and maybe have shifts.
Identification of task subsystems - P4
Microsoft Word
Mail Merge Letter
. Create the letter.
2. Import the Sound Advice logo, for shop recognition.
3. Format the letter in a clear modern style and highlight any special offers.
4. Insert merge fields, then merge the name and address fields from Sound Advice's customer database and then print.
Microsoft Excel
Financial & Stock Control spreadsheet
. Create separate worksheets for each product type.
2. Enter details of individual items and prices.
3. Enter formulae to calculate revenues earned and stock re-ordering requirements.
4. Create charts for quick visual analysis.
Microsoft PowerPoint
Website
. Import Sound Advice logo for each web page to keep the overall look the same.
2. Select general colour scheme and design to increase visual appeal and uniformity.
3. Create necessary buttons to make the site easy to navigate.
4. Create hyperlinks between different pages to make navigation possible.
Microsoft Access
Customer details database
. Create a table using Design View and select the fields for recording all the required customer details.
2. Create an easy-to-use form using the Access Wizard for entering customer details.
3. Create a customer interests table using Design View.
4. Create a Relationship to cross-reference customers and interests.
5. Design a Query using the Wizard, and run the query to find out which customers have specific interests in a certain product type.
Examples of work completed in the main applications
Microsoft Word
200 innocent people die as troops open fire on crowds
by Sam Taylor, St. Petersburg correspondent
St. Petersburg is a city in shock. It is nearly two months since Sunday 22nd January when the soldiers of the Imperial Guard opened fire on a crowd of 150,000 demonstrators, including women and children, outside Tsar Nicolas II's Winter Palace, killing 40 people. And the bloodshed didn't end there, as clashes continued throughout the day. The final death toll has been estimated at about 200 people.
A wedge seems to have been driven between the tsar, once known as the Father of the Nation, and the people. The leader of the demonstrators, Father Georgi Gapon, has written "We no longer have a tsar. Today a river of blood divides him from the Russian people."
Background to the Massacre
Things have been going wrong in Russia for some time. One of the main problems has been the disastrous war with Japan, which has seen humiliating military defeats, the worst being the destruction of the Russian fleet in the naval battle in the Gulf of Tsushima.
The shooting of the Minister for Education in 1902 and the assassination of the Minster of the Interior in 1904 in a bomb attack also show the political unrest that has been affecting the country.
In January this year, unrest spread to the workers, with a strike at the Putilov ironworks in St. Petersburg, which was soon followed by a wave of other strikes which brought the city to a standstill.
Unrest spreading
News of the St. Petersburg massacre was quick to spread to other parts of Russia. Reports suggest that nearly 3,000 manor houses belonging to rich noblemen have been burned down. Crime is getting out of control and strikes are crippling Russia's industry and railways.
There has also been a breakdown of law and order in some Russian
Eye witness account
The Times' chief reporter in St. Petersburg has been to the city's main hospital and interviewed some of the wounded.
Anna Kournikova was one of the lucky ones. Although shot in both legs, she is expected to make a full recovery. She told The Times, "It was Sunday and I went with my husband and three children to the Winter Palace. There were many people, but we were peaceful, singing hymns and carrying crosses. All of a sudden I heard someone shouting that the Imperial Guard had lined up ahead of us. The next thing I remember is the sound of shooting and people running and screaming. I thank God my family survived this crime against the people."
Microsoft Excel
This screenshot shows my practice work towards my TPT Excel spreadsheet. It is a view of the Summary sheet which contains the formulae used to calculate the weekly sales totals for each product type.
On the page shown, I am experimenting with charts to show the week's sales. Using the chart wizard, I have selected the cells required to create the chart and chosen a 2D pie chart style to represent the data that I want to demonstrate.
Microsoft PowerPoint
This is a PowerPoint presentation that was created for a form period.
It used videos, sound and images clips that were found on the internet then inserted into the presentation.
Microsoft Access
Above is a customer interests table taken from my practice work for the TPT. It shows each customer number registered with a selection of musical interests. Below is a screenshot of my customer information form at the design stage.
2 0 0 6
Timed Practical Task
Action Plan
Situation
NOTE PLAY is a small independent music and games shop in the town of Webberly. The shop sells new products such as CDs and games consoles, and second-hand items including old vinyl LPs or singles and consoles and games that have been traded in.
Although a lot of customers are local, NOTE PLAY also advertises in specialist magazines which attract customers from the rest of the world. NOTE PLAY also has a mail order service.
As the business grows, the owners have decided to buy a new computer system so that they can use ICT to update internal systems and grow their business with online marketing and sales.
Problem
The new computer system at NOTE PLAY will require the following:
. Setting up a database of customer details and their interests for use in mail-merged items such as advertising or invoices and linked to a database of stock to produce invoices for sales.
2. Creating a spreadsheet to help manage the finances of the shop (i.e. calculate stock value, invoices for customers, etc)
3. Designing a website for NOTE PLAY (areas for both the games and the record sales which are likely to have different sales audiences [i.e. adults and teenagers] including hyperlinks and on-line ordering
4. DTP a catalogue, advertisement or stationery for mail-order purposes.
Brief, Analysis and Research
. For the DATABASE
The database is for keeping records of customer contact details, purchase history and interests. Mail merged letters can also be created using the database so letters can be sent to customers quickly.
NOTE PLAY caters for all musical tastes for all ages (Jazz, Funk, Disco, Pop, Rock, Heavy Metal, Folk, and Classical). A younger audience is aimed at for games/consoles - Xbox, Nintendo, Playstation 2, PSP - game types include strategy, RPG, sports and driving.
The database has been given a simple layout to make it quick and easy to use by all levels of staff.
Microsoft Access will be used to create the database which will store the names and contact details of various customers that have either made purchases or registered on the website. Access was chosen because it can be used in conjunction with Microsoft Word for sending out letters using the mail merge function.
The data required will be customer names and contact details, as well as their musical interests, the date they registered and whether or not they are loyalty card holders. The contact details will be covered by the Data Protection Act.
The customers' contact details and interests will be captured in a number of ways. Firstly, when a customer makes a purchase in the shop, they will be given a form to complete. These forms will be transferred to the database by staff at a later date. A second way will be for customers phoning the shop, who will be asked to give their details over the phone to a member of staff who will either input the data directly onto the database, or fill out the details on a form for transfer later. Thirdly, interested customers will be able to provide their details themselves over the internet, using an online customer form.
Here is an example of a good database for use by the NOTE PLAY music store.
It contains:
. A table which sets out the customer details to be collected.
2. A form for capturing customer details.
3. A table which sets out the type of musical interests that customers may have.
4. A relationship chart.
5. A query that can search for details of all the customers who like a particular type of music.
. This is the table which sets out the type of customer details to be collected.
2. This is an example of the Customer Details form used in the database. It has a simple format for ease of use by any level of staff and has a clear, simple and distinctive logo in the header for instant recognition.
3. This is the Customer interests table:
4&5. This is the Query form showing the relationship between the customer contact details and the customer's interests. This can be used to refine lists of customers by their specific interests and can help with special marketing promotions, especially when used alongside Mail Merge letters and advertising.
Here you can see the relationship is formed by dragging customer-to-customer number.
2. For the SPREADSHEET
The purpose of the spreadsheet is to maintain a record of financial transactions and items sold.
The financial records include details of revenue received from sales. The manager can use this data to work out the shop's profits and the money required to order in replacement stock.
By recording items sold, the shop can use the spreadsheet to manage the stock to assist in the re-ordering process and analyse customer preferences to see what is popular and what is not.
The spreadsheet will be used by the staff and the manager. The staff will type in the information and use it to re-order stock. The manager will analyse the information and update any other necessary documents.
The spreadsheet needs to be easy to understand and use. Using tabs at the bottom of the screen makes it easy to locate and access the other sheets quickly; they also mean you do not have to have a series of separate documents. Using graphs means the information is easier to understand and can be analysed quickly. Showing the formulae in a different document also makes it easier to understand how the technical aspect of the spreadsheet works and shows how it was created.
Tabs are used instead of having separate documents. They also make it easier to access other sheets.
Showing formulae in a different document makes it easier to understand how the technical aspect of the spreadsheet works.
Graphs can be used to make it easier to analyse the information quickly.
I have chosen Microsoft Excel to create the spreadsheet. The application was chosen because it is simple to understand and easy to use. The finished result can look professional, but uncomplicated.
The data required to populate the spreadsheet will include:
* The cost of buying the stock for sale in the shop (price from supplier)
* The prices that the stock will be on sale for (sale price)
* The number of each item sold
The Data Protection Act will not limit this data.
The spreadsheet design and the data it contains will be generated manually. When the spreadsheet is used in the shop, the data will be updated automatically at the stock re-ordering stage or at the point of sale.
Formulae will be used to calculate weekly totals for stock purchases, sales revenues, profit margins and stock re-ordering requirements.
A number of tables will be contained in the spreadsheet:
* A "Summary" table will record the weekly sales totals performance and it will contain formulae for calculating profits, stock re-ordering requirements, the highest selling product and the average weekly sales totals. The data will be used to generate a graph which will provide a visual summary of the data.
* Each product type will have its own table on a separate "Sales Record and Stock Control" worksheet, which will list the sales of each individual item, the item price and the total payment received. Worksheets will be set up for Vinyl, CDs, Consoles and Games. The data on each worksheet will generate a graph to display the sales records.
Below is an example of one of the Sales Record and Stock Control worksheets which contains formulae in columns G =SUM(F11-E11), H =SUM(E11) and I =SUM(D11*E11).
Above is the original spreadsheet without the formulae showing.
The same Sales Record and Stock Control sheet with "Show Formulas" option selected to reveal methodology.
The shop logo should be on every sheet. It should be positioned in the same place on each sheet.
3. For the WEBSITE
The purpose of the website is to encourage more business, as it will provide a 24 hour service and does not require any working staff during those times. The website has the potential to reach the millions of people around the world who have internet access and therefore may attract people to the shop who would have otherwise not have visited it.
The website should attract younger people of both sexes who are interested in music and computer games as they are considered to be the main Internet users. The website will have a simple layout, easy to navigate, with a clear font and will utilise bold attractive colours to attract a youthful audience.
I have chosen PowerPoint to make the website. It was chosen because it is easy to use and can still produce a professional looking site. The other programmes such as FrontPage and Dreamweaver take much longer to produce a site and time restrictions make PowerPoint a more sensible choice.
The website will need to provide details of all the different products that the shop will be selling (vinyl, CDs, games consoles and games). It will also need the following areas:
* Customer Registration
* Shopping Basket
* Checkout
* Clearance Items
* Terms and Conditions
* Frequently Asked Questions (FAQ)
Some good examples of websites are shown over the page.
1 2 3 4
. The shop logo is positioned in the top left hand corner on every screen and is written in an eye-catching style.
2. The layout is easy to read, modern and inviting. There are bold headings that illustrate the point clearly and effectively.
3. It is easy to navigate the site.
4. The special offers and new releases are well set out and easy to find.
1 2 3 4
. There is a search option so you can find what you are looking for quickly and easily.
2. There is a shopping basket constantly on display so it is easy to see what you are currently about to purchase.
3. There are large 'BUY' icons so it is easy to see how to purchase the item.
4. The item description is detailed and it is easy to see how much you are saving compared to the recommended retail price.
1 2 3 4
. The shop logo is positioned in the same place on every screen.
2. The layout is easy to read.
3. It is easy to navigate the site.
4. The special offers are well set out and easy to find.
1 2 3 4
. There is a search option so you can find what you are looking for quickly and easily.
2. The new releases are well set out and easy to find.
3. It is easy to access your shopping basket so it is easy to see what you are currently about to purchase.
4. It is easy to find out more about a particular item or service.
4. For the Advertisement
The purpose of the advertisement is to attract more people to the shop. This is a cheap, quick and easy way to entice more business.
The intended audience for the advertisement is anyone with an interest in music. The advertisement targets anyone of any age and gender.
The leaflet needs to be memorable and friendly. It needs to advertise good products the shop sells and include any discounts or coming sales. It also needs to make it clear where the shop is and exactly what it sells. The shops website should also be clearly printed in a bold font so people can get a better idea of what the shop sells online and possibly make an online purchase.
I have chosen Microsoft Publisher to create the advertisement. I chose it because it is easy to use and templates are available unlike some other programmes.
The data required will be any coming sales or discounted items. The name of the shop's website. Details of what the shop sells and where it is located. None of this information will be limited by the data protection act and will be easy to obtain.
The information will be generated by me and typed into the provided text boxes on Microsoft Publisher then the font will be adjusted if it is necessary.
Good examples of Advertisements are shown over the page.
1 2 3 4 5
. The details and location of the shop are clearly displayed.
2. The title of the shop is eye-catching and in a bold, easy to read font.
3. The shops motto is displayed in a memorable way and in bold easy to read font, so you will remember it.
4. The types of music the shop sells are clearly set out eye-catching and easy to read.
5. The design is relevant to a music shop and attractive.
1 2 3 4 5 6
. The details and location of the shop are clearly displayed.
2. The title of the shop is eye-catching and in a bold, easy to read font.
3. The shops motto is displayed in a memorable way and in bold easy to read font, so you will remember it.
4. The types of music the shop sells are clearly set out eye-catching and easy to read.
5. The white text on the dark grey background stands out and is very effective.
6. The design is relevant to a music shop and attractive.
Time Management Table
Step
Application
Description of task
Time for step
Time elapsed
Microsoft Access
Create Database
.5 hrs
.30 hrs
2
Microsoft Word
Create Mail Merged Letter
0.3 hrs
.48 hrs
3
Microsoft Word/Paint
Design Store Logo
0.2 hrs
2.00 hrs
4
PowerPoint
Creation of website
.25 hrs
3.25 hrs
5
Microsoft Excel
Develop Financial Control System - Sales records, stock control
0.75 hrs
4.10 hrs
6
Microsoft Publisher
Create Advertisement
0.30 hrs
4.40 hrs
7
Spare time
To check through the applications created (or in case of emergencies)
20 mins
5.00 hrs
2 0 0 6
Timed Practical Task
5 Hours Supervised Work
Database
Spreadsheet
Website
All of the pages I created have been hyperlinked so that they can be easily navigated. If I had had more time I would have liked to include more pages on my website. I would have also liked to develop the pages I already had by adding more detail.
Mail Merged Letters
Note Play(tm)
2 West Street
Webberly
Surrey
GU2 6FR
Robin Smart
4 Forest Lane
Petersfield
Hampshire
HA67 1HE
24th February 2006
Dear Robin
Here at Note Play we have 25% off all Jazz CDs!
On Friday 13th March we are open until 9pm! The evening will finish with a raffle, and the prize will be a Jazz CD!
Hope to see you there Robin!
Yours sincerely
David Hill
Manager
This is a letter I have mail merged from the database that I created on Microsoft Access. The letter is worded enthusiastically and in a friendly way so that the reader will feel it is directed at them personally and that it is not just a chain letter. The letter contains relevant information to the reader's specific interest
Note Play(tm)
2 West Street
Webberly
Surrey
GU2 6FR
Poppy Williams
Dove Cottage
Bognor Regis
West Sussex
PO33 2XX
24th February 2006
Dear Poppy
Here at Note Play we have 25% off all Jazz CDs!
On Friday 13th March we are open until 9pm! The evening will finish with a raffle, and the prize will be a Jazz CD!
Hope to see you there Robin!
Yours sincerely
David Hill
Manager
This is another letter I have mail merged from the database. The template is the same but the names and addresses have changed. Other letters can be merged for different interest events.
Note Play(tm)
2 West Street
Webberly
Surrey
GU2 6FR
"Forename""Surname"
"Address_line_1"
"Address_line_2"
"Address_line_3"
"Postcode"
24th February 2006
Dear "Forename"
Here at Note Play we have 25% off all "Interest " CDs!
On Friday 13th March we are open until 9pm! The evening will finish with a raffle, and the prize will be a "Interest " CD!
Hope to see you there "Forename"!
Yours sincerely
David Hill
Manager
I have shown this letter with the merge fields showing so that the merged parts of the letter are apparent. If I had more time I would have perhaps included some colour in the letter to make it more inviting. I would have also expanded the context of the letter a bit more.
Advertisement
If I had had more time during the exam I would have liked to include some more colour, even though white on black can be effective adding colour would make separate things stand out even more and make the advertisement more memorable and inviting.
Supporting Work/Communications
I used Google instead of other search programmes because it is easy to use and generally produces accurate results.
I used ePALS to e-mail my project because it is the e-mail account the school provide you with and it is easy to use.
Evaluation tests - TPT3 + P11
Test
Purpose of test
Test data
Expected result
Actual result
Website Address
Enter 'www.noteplay.co.uk'
Note Play website accessed
?
2
Links between web pages
Click on on-screen buttons
Selected page accessed
(
3
Updating the spreadsheet
Update the necessary information by filling in the grid squares
The totals, averages etc will update themselves automatically if the formulas are correct
?
4
Adding a customer's details on the database
Fill in the customer information form with the necessary information
The database should update itself
?
5
Selecting an interest
Type in the desired interest in to the parameter value dialogue box
The customers with the selected interest will be listed
?
6
Checking the spreadsheet formulae are present
Go to Tools - Options - Show Formulae, to reveal formulae
Formulae should be present in all necessary cells
?
7
Checking the spreadsheet formulae function correctly
Input incorrect value (e.g. Text not numbers).
"VALUE" reference appears in cells containing formulae and an exclamation mark should appear
?
8
Mail Merge function
Write letter, insert merge fields and merge letter from Note Play's customer database
All merge fields should be replaced with addresses, names etc
(
Negative Results
2. The test produced an unexpected result because certain pages were hyperlinked incorrectly. The mistakes were found and the buttons were hyperlinked to the correct page numbers.
8. The test proved that the mail merge function failed due to incorrect fields being inserted. The mistakes were found and the correct fields were inserted and the mail merge functioned correctly.
Effectiveness report - TPT3 + P6 + P11
Specification Point
Application
Task
Rating
Comments
MS Word
Mail merge letter
Word was more than adequate for creating a letter in a simple style with the Note Play logo and the shop's uniform font for the mail merge letter.
2
MS Publisher
Advertisement
Creating the advertisement in Publisher was effective as the result was eye-catching. Whilst a package such as Quark might have introduced more complex images and colouring, it would involve more investment in staff training and might not be worthwhile.
3
MS Excel
Financial Control
The Excel sheet was easy to set up and the Summary sheet which included weekly round-up statistics and charts made it simple for staff to gain a quick overview of the state of the business.
4
MS Powerpoint
Website
The overall look of the website was good, but to produce a more professional Java-enabled website with it would have been better to use MS Frontpage or Dreamweaver.
5
MS Access
Customer Details
The Access database met all the requirements for storing customer contact details and their interests. It also fulfilled all the criteria for providing data for the mail merge.
6
MS Word/Paint
Design Store Logo
The logo design was basic and perhaps not very eye-catching or memorable to customers. It might have been better to use a package such as Quark to make a more sophisticated and colourful logo.
Rating Key:
= Poor = Average = Good
Note Play- TPT Work Produced
Database
In my TPT I created a database using Microsoft Access to store the contact details and interests of customers that had registered on the website or purchased items in the shop. Access was also chosen because it can be used in conjunction with Microsoft Word for sending out letters using the mail merge function.
An Access form was used to capture customer details such as address, telephone number, e-mail, musical preferences and whether or not they were Note Play loyalty card holders. The form was designed to be simple to complete for staff or online customer registration.
A feature of Access that was particularly relevant for my Action Plan was the Query function which allows all customers with a specific interest to be retrieved.
Correspondence
Microsoft Word was be used to produce letters and catalogues to send out to customers. Word was chosen because it is easy to use, universally used and is compatible with Microsoft Access for the purposes of mail merge.
Spreadsheet
Using Microsoft Excel I created a spreadsheet to record all accounts, stock and finance which will be automatically updated when a purchase is made.
Excel was chosen because of its ease of use, for the formulae functions which enable complex calculations to be performed and for the chart function which helps the manager to visualise the shop's performance.
Website
Microsoft PowerPoint was used to make the shop's website. Although this software wouldn't be my first choice for creating a website, I believe that the results fulfilled the criteria for creating an eye-catching and visually appealing website, with a recognisable and memorable format and simple navigation which would be suitable for all types of user.
PowerPoint was chosen because it is easy to use and compatible with the other Microsoft programmes used. I used features such as hyperlinks and buttons to allow easy navigation.
Independent working
I was pleased with how well I did and although there were some minor problems, the overall result was pleasing. My plans were successful but there was some information in them that was not needed, so to save time the information could have been removed. I did not need any help during the exam, but before the exam I made sure I asked about anything I was unsure about. Before the exam I had to learn about new skills that I had not previously come across, such as creating a database in Microsoft Access, mail merging a letter from the database and creating a website in Microsoft PowerPoint. Most of these skills I found using the Internet or by asking people.
Planning
I felt that I did not have enough time to complete all tasks to a high standard. Some of the documents I created were not as efficient as they could have been and took up more time than they were scheduled for. I had very little time left at the end to check my work as almost all of the 5 hours was spent finishing the tasks. I also found that I had left little time to complete my DTP work so I had to stick with a basic design.
Additional information
I would have liked to have added more detail to the documents I created. For example, adding some more items to the spreadsheet and some more names to the database. I would have also liked to have added some more pages to the website and some more colours to the advertisement. Otherwise, I was pleased with the overall result of the work that I produced in the given time.
2 Sam Taylor 2007
Sam Taylor 2007