GCSE Excel Project ICT

Authors Avatar

Checklist for Spreadsheet Project

ALL THESE MUST BE USED AS HEADINGS IN THE PROJECT

Title Page

Jack Jones, Custom Made Computers and Parts by WARLORDTF using Microsoft Excel 2007.

Identify (5 marks)

Background Details, Identifying the user

The user is the owner of a computer construction and maintenance shop, which runs under the name of WARLORDTF. WARLORDTF is a shop, which makes computers and performs maintenance on all computers whether they are Windows or Mackintosh. The Specialist, Alex Heather owns the shop and manages it on his own. Currently he has 5 business contracts for his computers and approximately 35 Private customers a week.

When business purchases are made, they are treated almost like private purchases. He gives them a piece of paper with each of the specifications of all the components the person then specifies the components and then Alex checks his stock and checks what he has to order then informs the business customer that they will be in stock and then writes down the order and files it into his folder. The customer then pays for the product and informs Alex of times they are available to collect.

After the customer has left and he has closed shop, Alex then checks his inbox and constructs the computers to the specifications to the inbox sheets after the computer has been built he moves the sheet of paper that was in the folder to the top of the pile. Next day, when the shop opens he checks his Folder and calls the customer to inform them that they must collect their computer. They collect the computer and the sheet that is currently in the folder is moved into an archive. The archive consists of another folder, which has all of the previous customers’ orders.

Statement of the problem

Currently Alex is using a paper-based system where all the information is stored on paper and handwriting. Currently it takes approximately 1 hour to complete a transaction and as more corporate businesses want to invest in his computers his workload is greatly increased reducing the amount of time he has to provide transactions.

Lost/Misplaced Orders

On occasion, when Alex has hand written up the order he can sometimes place the order in the incorrect place or lose it completely causing hassle for him, as he has to attempt to contact the customer and inform them that their order will have to be re-made, this is quite unprofessional and could cause loss of business/sales.

Human Error

In addition, after the customer has specified the parts he/she wants into the computer, sometimes, when he is copying the name of a product down, he could make a mistake (E.g. RAM 1 GB could be an error for RAM 2 GB). In addition, an error could be made in the reading of a component, (the same example applies) which can be quite tiresome as it would involve the computer incorrectly made to be scrapped and rebuilt causing further loss of revenue.

Another issue we have is the calculating part of the invoicing, as Alex hand writes the invoice and checks prices and writes them onto the invoice and at the bottom end he then has to count up the numbers to form a total, being human he may make a mistake and over (or under) charge.

Illegibility of Handwriting

Sometimes, when there is a backlog of customers Alex rushes through the order writing down quickly the specifications of the computer components, if done haphazardly can be quite illegible and increasing the likelihood of error. In addition, the card given to the customers has to be re-written every week as the paper becomes tatty and torn by the end of each week, unfortunately, as Alex only went to secondary school; he relied on typing to replace his poor handwriting. However, he makes a special effort with the component information page, which takes him approximately 55 - 60 mins.

Recalling Information From Archive

When a customer returns Alex checks through the archive to see their previous order and purchase, this happens occasionally but mainly with the companies. Alex then gets his ‘Archive’ folder and searches through for the relevant sheets of paper however, this could potentially take a long time as his archives size is increases by approximately 30-40 sheets per day.

Manual Solution Considered

The card providing the information about the component could be photocopied from one initial copy, or better still one handwritten copy laminated to increase the life of the paper. In addition, a questionnaire-style form could be given to the customers, of which only one would be made (and photocopied), then they fill in the order and give it to him where he can total the price and charge accordingly. Also the archive could be sorted in two ways either a) alphabetical order with a filing cabinet ordering the customers records in some order; however this is only slightly improving the system as error and discombobulating can arise, as some records could be misplaced due to a number of other limiting factors. b) The archive sheets could be sorted by date limits (such as monthly of weekly folders). However, that could be again thrown off if Alex has to order components and there are delays, which could throw timing off completely, also specific customer details are hard to attain, as it would mean associating customers to a date, which is a shared value, not something individual like their name, which is the standard arbitrary value.

Two alternate software solutions considered

There are two computerised options within Microsoft both are within the Microsoft Office 2007 Suite.

Microsoft Excel

Microsoft Excel 2007 is a great new application with almost limitless data storage it has 1’048’576 Rows, 16’384 Columns, 17’179’869’184 Cells each can contain 32’767 Characters, which means a total number of 562’932’773’552’128 Characters per sheet! Data Validation can be inserted into cells to ensure correct details are inserted. Mail Merge is also available but can be quite had to control and set up. Also with excel formulae can be inserted to control and manipulate the data inserted. Links and macros to other workbooks and sheets increase versatility of this program.

After data has been inserted into the spreadsheet function, mathematical functions can be created and with the results graphs or charts to display average data easily. In addition, there is a sub-operand called VLOOKUP, VLOOKUP stands for Vertical LOOKUP, which searches for a value in the first column of a table array and returns a value in the same row from another column in the table array. In addition, ‘Conditional Formatting’ can be placed on cells to change cell properties based on the cell contents.

Microsoft Access

The Second solution available in the Microsoft office 2007 suite is Microsoft Access, which hold many advantages. The system is primarily a database function, which links together using tables. Once data is inserted to the tables, they can be checked with data Validation and edited easily input forms can be created to enable to user to insert data easier. Table Relationships can be created to link data value together, once linked this information can be access from any of the tables. Queries enable to user to search for information or a data value with greater ease, however the initial file sizes can be large, when more information and data is inserted it makes it easier to use the information.

Proposed solution

The software I am going to use, to solve the issues stated prior, is going to be Microsoft Excel as it has an easy spreadsheet format, where data can be entered and manipulated to your will. The system will be based on a multipage form, which is linked from the main page, which contains the customer’s choices. If the customer wishes to change a request, he/she would just re-click the link to the selection page and re-select their choice. After the customer has selected their allocated parts they then move onto a validation page which then their order is verified visually, then they click the last button, it saves (in a separate file), prints the order (to be given to Alex), resets the forms, saves and closes. The file is going to be reused repeatedly for multiple customers.

At least 3 Quantitative objectives THESE MUST BE CHECKED BY YOUR TEACHER.

  1. The user wants an electronic archive, which will stay separate from the main file, which gets updates as each customer completes his/her order. This archive will contain the customer’s information and the order that he/she is going to make.

  1. The user wants the order, which has been completed by the customer, to be printed out (in a hard copy format). This would enable the user to build the computer using the specifics from the printout and therefore would not need to check the backup archive. In addition, after the computer is built, the user can then stick the printout onto the customer’s computer and use it as a label, to prevent confusion.

  1. The user would like to have his customers view four static tables (where he can edit the prices) and the customer can choose the component by clicking on it’s name and therefore activates a macro which will transfer the selected item onto the invoice. If the customer changes his/her mind then they can return to the static table to review the choice and re-select accordingly (and delete the previous order and write the new order in). The customer will repeat the process until he/she has made a final decision of choosing singular components to build up the computer; also, the accumulative total will be calculated. The customer can then input billing/address details and have their order archived (objective 1) and printed three times (Objective 2) one for the customer, one for the accounts and one for the computer construction.

Analyse (9 marks)

Appropriate Software identified

The software being used is part of the Microsoft Office suite (Microsoft Access and Microsoft Word and Microsoft Excel) there are many other programs that could substitute for the MS Suite, but we use MS Suite because it is easy-to-use and is the ‘typical‛ choice of program to use. Excel will be used for the invoice function, which will enable the customer to select the parts and put them to order. An Internet Connection is optional if the user wishes to check stock prices online. In addition, as a precautionary measure Norton Antivirus will be installed to prevent any virii causing harm to the computer or obtaining any customer information (especially business information).

Join now!

Appropriate Hardware identified

This is the computer and printer I believe is suitable for use now and in the future of the Maintenance and the running of this efficient system:

Manufacturer: Packard Bell BV

Processor: Intel(R) Core(TM) 2 CPU 6300 @ 1.86GHz (2 CPUs)

Memory: 4066MB RAM

Hard Drive: 200 GB Total

Video Card: NVIDIA GeForce 7300 SE

Monitor: SyncMaster 911N/920N/920Nx, SyncMaster Magic CX918N

Sound Card: Realtek HD Audio output

Speakers/Headphones: Realtek HD audio

Keyboard: Terminal Server Keyboard Driver

Mouse: Terminal Server Mouse Driver

Operating System:

Windows XP Home Edition (5.1, Build 2600) Service Pack 2

(2600.xpsp_sp2_qfe.070227-2300)

...

This is a preview of the whole essay