TASK C: Spreadsheet coursework page 3
- The user must find the system easy to use; this will be done by making the spreadsheet user friendly, so that every heading and validation rule is understandable. This will be based on the design of the system; simple data input and all formulae forming calculations automatically.
- A user manual will be based to produce the user step by step through the system.
- Validation checks will be used so that the data inputted is more reliable and not mistyped. Error messages should be helpful as well as user friendly.
- As well as calculating the profit and loss it will also identify to the user whether the company is low in stock of an item. Error messages when impossible data is typed e.g. an item is sold when the company has no more of that item left in stock.
The spreadsheet itself is divided and split into many columns and sections. This section will for example include, a Description of Item column, Price, Trade Price, Profit or Loss and Validation checks like, Restock checks and validation checks, to check the quantity. The spreadsheet itself will function by the member of staff typing in the valid details of the description of item he wishes to sell. The computer will thereby automatically search for the necessary price of the item, and will check the validity of the quantity sold, and whether the company has to restock that specific item.
HEADINGS OF DATABASE
Date Description of item
Quantity Price
Total/Sub-Total VAT
Profit /loss Trade price/retail price
Validation check (whether entry is possible) Restock
Overflow Stock bought
Purchases
TASK C: Spreadsheet coursework page 4
WHAT SPREADSHEET WILL CALCULATE
Total/Sub-Total VAT
Profit /loss Restock
Overflow (whether too many unlikely items are sold e.g. 400 computers)
My specification of problem is the amount of files needed to work out the exact amount of items sold without getting papers lost. That’s why, my spreadsheet will not only be easy to use but will store all the necessary information of a complete month in one piece of paper. So that every year instead of having about 20,000 invoices there will only be 12 main pieces of paper.
The system will be rooted by the shop’s VAT results so that the computer shop won’t be relying on receipts and signatures. The implemented spreadsheet will be simple to use, so that any member of staff can record any events, and that at the end of the month, the VAT file can be edited and printed into use. As well as this to simplify the spreadsheet for the user and make it more sophisticated, I will include validation checks and include a feature to alert the user when they need to Re-stock.
TASK D: Spreadsheet coursework page 1
D: Design Of Solution:
My design of solution is to design a spreadsheet that will organise all the association’s files in a limited amount of paper that will be reinforced by the hard disk, and a floppy disk for backup.
My flow chart (Fig 1) represents the steps for me to use the spreadsheet that I have produced. Acquainting the fact that my coursework is based on the VAT results, and to calculate the Profit or Loss, My spreadsheet should be fast in retrieving data and producing it on paper. I will therefore require producing a Print Screen button, using Macros.
If the individual wanted to gather his profit and losses for a certain amount of time at the bottom part of the screen there will be a box outputting the total amount of money made or lost. This box will be triggered when the purchases Total prices, required are highlighted.
My flow chart (Fig 1) illustrates the major procedures from having to sell something to printing a completed document. The first three boxes have no relevance to the spreadsheet, though from the forth it is based ideally on how my spreadsheet functions. I used the Key to Disk(keyboard) on my forth box because no other input device will be as efficient and cheap in this regard. later we would instantly store it on disk, to ensure it safety and its existence in our files and that we have a second hard copy. Once the description of item has been keyed in, due to the computers high level of intelligence, it will automatically calculate the amount due, and the heaviness of the VAT. The screen displays the Profit/Loss and adds it to the final sales. Finally a choice is made whether to print the document or not.
TASK D: Spreadsheet coursework page 2
Start
Customer enters
Shop
Buys
Something
Yes
Input sale on computer
By typing onto spreadsheet
Store on disk
Spreadsheet calculates amount
Of items sold and price due.
Calculations are made to give retail
Price and to give the total price due.
Displays Profit/
Loss and adds to
Final sales.
No
Print?
Yes
Printed Document
End
TASK D: Spreadsheet coursework page 3
D: Design Of ICT System
At the end of the day when stock levels have changed due to customers buying them, the manager/salesman does the following procedure:
TASK E (i): Spreadsheet coursework page 1
E: IMPLEMENTATION OF SOLUTION:
- Hardware Resources Required
There are many types of technologies enlisted in the market, some of which are very highly rated, though if I was to choose sophisticated equipment I will also have to consider the price, not just its use.
To ensure that every employee can get to a computer to input information that he has sold, refunded or brought, I will hope to set up a network. This network will consist of a few computers linked up as one, so that all employees can reach a computer from wherever they are. These computers will then be further connected to one powerful machine that will control and overlook every other computer so that it is possible to reach the filing system easier. In terms of other branches around the world, the issue concerning the Internet becomes useful. As if it is possible to set up a link of e-mails and connections to other countries and cities so that all of the networked computers can all register in the same spreadsheet, into one dominant computer as the server. Though this idea consumes a lot of money, which is a major problem, though if applied the spreadsheet, is applied in the Internet then the filing systems of this organisation can be made very simple and organised.
Another aspect, which I should shed some light on, is the concept of magnetic disks; these are disks that backup information. This may be said to be have dealt with by the floppy disks, which it is indeed, within its disk space limit. What I mean by this is that Backup disks can store much more information than an average floppy disk. This becomes useful when there is a lot of data that requires to be saved, but will not fit on a single disk but on numerous floppy disks. The good thing about the backup drive is that it is not too expensive, as the disks usually cost around £5 and the backup disk drive around £100.
Magnetic stripe readers are very useful in terms of reading credit cards, though the current company I am investigating do not accept credit cards, so therefore there will be no need to buy or input a stripe reader.
TASK E (i): Spreadsheet coursework page 2
There are many input devices that can be used to input information, among those are touching screens and scanners, then there are the basic input devices like a keyboard and mouse. In accordance with my coursework I will not need any high specification hardware like a touch screen or an OCR, as part of my aim is to make this spreadsheet as easy to use as possible, and high tech hardware will only cost a lot and complicate the situation. Therefore I will only use the basic mouse and keyboard to implement my needs, as well as this my requirements for the computer, is a Pentium, with an minimum requirement of a 2 Gig hard disk, 8MB of Ram, and compact with windows 95. This is due to the fact that, the spreadsheet will need a system that can fulfil its needs and store a lot of information. Windows 3.11 can be used but will run less efficiently.
Further more I will require a powerful machine like the Pentium 200 with MMX technology, this is due to its momentous speed and its capability to with hold a Windows NT workstation. This computer will take a maximum of 1 minute (preferably 30 seconds) to function from the moment I switch on the computer until it logs on onto a spreadsheet. The saving of data must be fast and efficient (around 5 seconds will be respectable) as it will waste valuable customer and labour time. Also in respect to saving data I will require a computer, which will work fast enough to retrieve data that I hope to only take 15 seconds maximum to log on into a spreadsheet (MS Excel 97) and a file.
TASK E (ii): Spreadsheet coursework page 1
- Software Resources Required
Word Processors:
This package takes on a fantastic, attribute aboard, which is its talent for typing out projects. Some can ensure that a high quality of work can be produced if used to its full potential. It allows the information typed to be displayed on the Visual Display Unit (VDU) and can be edited before being printed on paper. The package also contains useful extras like spell check and Bullet pointing.
Some High quality word processors allow and contain graphical software to be done on it, though it lacks the potential of Desktop Publishing software, of drawing neat posters combined with text, like Microsoft Publisher.
Databases:
Databases are series of files that can be stores in a computer, which can be accessed in many different ways. Databases are programs that can store information into files. This sort of database is very useful and ideal for designing filing systems on hospitals, surgeries and at a dentist or Doctor, though for financial business data it will not be as appropriate. This is due to the fact that it requires formulae and calculations, while a database will do queries and organise files, therefore a database is not ideal.
Desktop Publishing:
Programs like Microsoft Publisher are files that are exceedingly useful in terms of making posters and designing front covers. As it can combine text and photos, and has some excellent back round covers on a few advanced programs like Publisher 98. Though in terms of organising and calculating data it lacks that efficiency.
TASK E (ii): Spreadsheet coursework page 2
Spreadsheets:
This package is based on calculating data, and producing design presentations like graphs and tables. It can also produce charts and some high standard spreadsheets even locate themselves as a graphical package, like Microsoft Excel 97. This package does like every other package has its upsets, to this package it’s the lack of choice and easiness to use. By this I am referring to, that it is hard to change the spreadsheet into a fashionable and nice display, as it is specifically designed for calculating data and producing information from it. This is why it is often a good idea to copy and paste your work from a spreadsheet to a Word processor when displaying work.
Out of the few main packages I have talked about, I have decided to choose a spreadsheet to do my coursework on, the specific package I am going to use is Microsoft Excel 97. I have chosen this because it suits my requirements, as I do not need the Vat calculation to be used as a poster, but only as reference to the employees themselves and to the manager, so a desktop publishing package is eliminated. As well as this the databases are ideally used for hospitals not organisations like a computer shop as I do not want a list of personal files, but on the contrary, I want a 1-file program that will calculate data. The word processors are used for typing out information in a standard or modelling kind of way, and do not help my cause either. This leaves me with the spreadsheet, which will do what I acquire my program to do, which is calculating data not modelling.
TASK E (iii): Spreadsheet coursework page 1
(iii) Data Collection, data capture and Input:
There are many ways to insert information into ones computer, though it all resolves to one point hardware. What kind of devices does the user have, to insert information into his system; this could vary from the most commonly used input devices, to the most specialised and up class in technology.
Here are a few input devices one can use in his system.
Key to disk:
This is just the simple typing using the keyboard on the computer. It’s a very simple tool and indeed very cheap and is nevertheless efficient. It is used in any ordinary computer, and has disadvantages like time consuming and many easily made mistakes are made.
Microphone easy speaking
This is a two way piece which is when someone speaks through the Hardware, the microphone it appears using the software as text on the screen.
Magnetic Ink Character Recognition
This mechanism is ideally used in banks and building societies, as it is used to print the peculiar characters on cheques. These characters could be numbers or letters, and is printed by using an ink which consists of iron and may be magnetised. The magnetic pattern of the numbers can be read by a Magnetic Ink Character Recognition.
Optical Character Recognition:
This is a scanner with a difference. Not only does it scan images it can also recognise each letter individually.
I.e. as it is often hard to read printed letters and numbers (like distinguishing
TASK E (iii): Spreadsheet coursework page 2
between the number 5 and letter S) the Optical Character recognition scans and
recognises each character not only as a picture but also as an individual character. Therefore due to its technology it can be edited on a database such as Microsoft word, so you can just imagine how expensive this contraption is.
Optical Mark Recognition
This is a gadget that can convert marks(lines) into characters. In simple terms it will convert a code into words and numbers, it is so advanced in its technology that it can convert 26 marks into just one simple letter, so it is unimaginable how much codes and marks it must convert so quickly.
Although this device is magnificent in its speciality, it is of no use to my coursework because I will be using numbers and letters in the first place, and there will be no marks to convert to characters.
Barcode Scanner
A barcode scanner shines light at a barcode, the amount of light that is reflected is measured and the corresponding price is brought up. This is a good method as it is quick and easy to use. But this is a small shop, and the machine along with its software is too expensive.
Having stated different input devices I can now derive that I will use the key to Disk apparatus to input information. I will not use the other devices because they are expensive and their speciality is not of use to me in this coursework. The key to Disk apparatus will be specifically used because it is cheap, easy to use, efficient and overall meets my initial set criteria, for hardware. The keyboard may produce a few errors but at the initial stages of the opening of the organisation such high equipment such as a barcode reader is not necessarily needed.
TASK E (iii): Spreadsheet coursework page 3
The main inputs into the spreadsheet that I will design will contain
- Date
- Description of item
- Quantity
- Price
- VAT
- Total amount of money due
- TOTAL amount of money made, and Item sold etc.
For almost each of the variables mentioned above I will produce a formula and all the calculations will be done quickly, by the entering of a price, description and quantity. As well as this I will hope to format the Spreadsheet by producing rules, which the computer must abide by and read error if a mistake is done.
I.e. if I set the quantity between 1 and 50, if someone will accidentally press the wrong amount that is outside the limit, like 51 or 0 the cell will read error, this cell will be calculated by a formula. These calculations/rules that the computer strings along by is called a validation check. This validation consists of many variables, and must therefore be very important as, especially in my investigation. This is because if a mistake is done and a too large or small a number will result in many corrections to be done, and may even result in a fine as the miss-payment of the VAT, is a type of crime.
This is basically what the Validation check will be exposed to:
As well as this I will hope to add a restock notification that will imply, that if we are low in stock the cell will read re-stock. Though this is only a suggestion that
TASK E (iii): Spreadsheet coursework page 4
I am not positively sure about. This is because, as I have said, my coursework is partly based on the easiness to use, and if I add these complicated formulas it will only complicate things. This is due to the fact that I will need this spreadsheet, to be so easy to use that any of the shops employees will have no problem using it, which is why the idea on re-stock and to a certain extent the validation check are not all good ideas.
As I have mentioned previously in my report, in the hardware section, I am aiming to produce a link between other computers, of other branches, using the Internet and network system (Windows NT 4.0).
TASK E (iv): Spreadsheet coursework page 1
(iv) Data Validation and Verification:
VALIDATION:
Validation processes are designed to check that data is accurate, believable and complete. Validation is carried out by the computer (on line) at the input stage, either as a completed separate program or, for limited applications, as a subroutine prior to the main processing.
Character type Check
-This makes sure that the correct amount of characters has been inserted into the cell, and does not exceed the limit justified
Hash Total
-This is an appliance that is used to check invoices. This is a meaningless total, If it does not appear, it means that the numbers or items have not been typed in correctly, or not all the variables had been keyed in and left blank.
Control Total
-This does the same as the Hash total but has a meaning.
Spell Check
-As well as being used in a spreadsheet it is also famous and commonly used in other computer software appliances, as it notifies if a text had been keyed in incorrectly and needs to be entered accurately.
Check digits
-This appliance checks the digits after an array of values. It is used because large numbers cause confusion, so the computer automatically checks the digits.
TASK E (iv): Spreadsheet coursework page 2
Range
-To check whether data lies between a specified range.
Data Validation
-This is a computer automatic double check. It makes sure the system performs and delivers. It Reaffirms if some data is allowable. In simple terms they will barrier letters in a cell which has been specified as numeric.
Length check
-This will bring to the user’s attention if had entered fewer/more characters into the cell when it is not required.
From all of these validation methods I would choose the range and Data validation as it suits my needs for use in my coursework. The data validation is of use because it will validate almost all the values and results, and the range check will be used as it will help find figures quicker than a manual based version.
Verification:
Validation may be based on the intelligence of the computer, though in verification it is based on the understanding of the student. Verification is the manual check of the user, without using the tools to check whether something is correct, unreasonable or invalid.
The advantage of the verification is that it can work with any value and text and will also find any data because is done manually. As well as it having its advantages it has its disadvantages like it is time consuming, requires more staff and the information is harder to spot.
There are two main ways in the manual Verification system to check whether an entry is correct in invalid.
TASK E (iv): Spreadsheet coursework page 3
This is by using two or more members of staff to insert their recorded information, then from these candidates you can deduce the correct piece of information you wish to find.
This is another form of checking information, though instead of using more than one member of staff, only one candidate is needed. This process is when one person double checks his own work twice to check for mistakes, rather than two individuals typing out both the information twice.
To choose for my company which style of confirming data is correct, I will have to respect the advantages of each form, and take into account the disadvantages it holds. Double entry is less time consuming but consumes more staff, Proof reading consumes less staff but takes longer and is not as efficient because, it is less likely that an individual will identify his own mistake, rather it will be more efficient if another person double checks it. Therefore I have decided to follow neither in particular but adopt ideas from it. I have chosen to let only one member of staff writes up the report, and then later when possible another member of staff will come and double check the work done by the previous member of staff. This way we can get more efficient results and save time for two people writing up the report, and one person double checking it twice.
TASK E (v): Spreadsheet coursework page 1
(v) Data and Program Structures:
FORMULAE:
Hyperlink
-Opens a shortcut or jumps that opens a document stored on your hard drive, a network server, or on the Internet
-I used this tool as a means of doing the IF statements and VLookups. As I needed two spreadsheets to which I can lay out my data. One for recording the shops profits losses and another just to register the values correctly. To convert to a different sheet I will only have to click on a logo and it will transfer me.
-Formula: =HYPERLINK(“J10”,”Validation”), telling
Sum
-Adds all the numbers in a range of cells
-This was used in many events such as the calculation of the stock bought, or it sums(adds) up the amount of VAT due.
-E.g. =sum(D6:D14)
Vlookup
-Searches for a value to the left of the column and then returns the value in the cell/column specified.
-This is a very useful aspect of my system because as well as me conditioning the item I need in an “If Statement“ I can also retrieve data by the Vlookup tool. I will need this for retrieving the prices. This means that once someone enters a description of item, the price will automatically be searched and retrieved.
-E.g. =VLOOKUP(“B6”,A$16:D$25,4) where the “$” sign signifies to keep the cell reference constant (refer to Fig 2)
TASK E (v): Spreadsheet coursework page 2
If Statements
-To with strain a statement with a condition you specify with a true and false content. I.e. can state a value in correspondence with a statement
-This is one of the most vital high level tools I will be using in my spreadsheet, as it will condition a statement and also retrieve data making the spreadsheet more convenient and faster for the user.
-E.g. If(D6>10,“Error”,”Ok”) this is in the overflow column, which portrays the If statement constricting D6’s limits to under 10.
*(Multiplication)
-Multiplies two cells together
-This is a very basic piece of apparatus, but nevertheless essential, as it will be used throughout the spreadsheet to calculate the sub total, the VAT, Total etc. It will do so by multiplying two values together, like the trade Price and quantity sold.
-E.g. =E6*D6 / =F6*0.175
WHAT WILL THE TOOLS MAINLY CALCULATE
The price times the quantity.
The sub-total multiplied by 0.175. This is for the calculation of the individual amount of the VAT not the total price the customer has to pay including the VAT. At the end of the week/month/season, the VAT will be summed and kept as reference once printed out.
This is the summing of both the Vat and the sub-total together to get, the Total amount the customer has to pay.
TASK E (v): Spreadsheet coursework page 3
Every time a customer buys something, a member of staff will type in a description of the item, and the rest will be done automatically, including the finding of the set price. The summing of all the figures at the end shows, how much money we have earned and sold.
Profit or Loss
This will be deduced by the subtraction of the Total from the Purchases (Stock bought multiplied by the trade price). In sheet 2 (=F1-I1=J1)
The spreadsheet will work mainly by automatic calculation. This means that all the user has to do is type in the date description of item and the quantity sold and all the rest will be calculated for the user. This is done by using “Vlookup” formulae and “If statements”. When the description of item is inserted, the price of the item will be searched for in the Vlookup box below the main section of the spreadsheet. This box will entail all the items that are sold at the store and the price for it, the price of the item will be looked for and found from there. That price will then be multiplied by the quantity to get the sub-total. The VAT is then calculated by multiplying the sub-total by 0.175, that answer will then advance to be added with the sub-total to find the total the customer has to pay.
The trade prices of the items are listed in another table along with amount of stock bought. These two will be multiplied together to gain how much money the company has spent on the purchases. Then the Total from what the customer has bought is subtracted from the purchases that the company has made. This way the profit and loss is calculated. Every week or so the profit and loss will be summed, to see how well the business is progressing.
The validation methods were used by using formulae that included “greater than” and “lower than” (>/<) This was used by saying whether a specific cell is greater/lesser than the stated constant number, if yes than state it as an error if no display it as a Ok message. This is basically how the validation works.
TASK E (vi): Spreadsheet coursework page 1
(vi) Output Format:
In the spreadsheet system that I have designed for CNE computers, the manager can now evaluate his finances in various ways. He can now use graphs to compare sales and etc. And he can use the main spreadsheet to display the main and the most necessary data.
Below is an illustration of parts of the system, with explanations on how a user is expected to use it in the running of the system.
- The manager would first have to enter in his data of the items. Then the rest will automatically be calculated for him.
TASK E (vi): Spreadsheet coursework page 2
- A formulae view of the previous screenshot may be seen below:
The above screenshot shows the formulae view of part of the spreadsheet. As you can see from above, Vlookup was used to calculate the Price.
- After the manager has inputted his data, all the table headings below would be worked out automatically. E.g. Profit/Loss.
The manager can now view and draw up many different charts and graphs of his choice to help him model his finances and see how well his business is running.
The following few pages are screenshots of how the data would be presented for the shop employees, and also screenshots of technical use (formula sheets).
TASK E (vi): Spreadsheet coursework page 10
- There is also a selection at the bottom left hand of the screen, where there is an option of starting a new spreadsheet (when the month is over he can begin a second sheet to keep it organised). This will transfer him to another blank new sheet once he clicks on the icon.
-
Another tool, which is inbuilt into my system, is the hyperlink that I have created. This takes him to the Validation page where all the formulas are shown, he could adjust and modify, as he likes with them. This hyperlink exists on both the Spreadsheet and on the Formula sheet.
And
-
TASK F: Spreadsheet coursework page 1
F: Testing Of Solution:
In order to find out whether my formulas had worked or not and whether the system was accurate as I said I tested it out. I have split the tests up under different headings so that it is easier to see the results of my test and also if I do it individually it also becomes more thorough.
According to the screen dumps and the final spreadsheet we can see deliberate mistakes inserted on the final sample model, to prove the spreadsheet really works and identifies mistakes efficiently. In the spreadsheet I entered both correct and incorrect data, and viewed whether or not the validation checks picked them up. For instance if I entered data correctly e.g. quantity sold is less than 10 then there will be no sign reading Error in the overflow column stating “Error”.
TASK F: Spreadsheet coursework page 2
In the “overflow” column, where the formula states that the quantity sold is less than 10 (=If(D6<10,”Erorr”,”Ok”)), where I had placed 11 cables sold an error sign will automatically appear, justifying to the user that a definite mistake was made and requires changing. This is due to the fact that it will place unnecessary confusion in the company’s profit and losses and an overflow in the quantity sold.
Hash Display
- Another function in the spreadsheet was to make sure that mistakes weren’t made, in terms of typing in more items sold, than we had actually bought. In simple terms the validation check is a more specific solution to the overflow column, as it will make sure, that the company had not supposedly sold more items than the organisation had even bought. (i.e. bought 3 monitors sold 5) where this incident occurs it will read an error sign in this screen, making the user more aware. So to show to the manager that an error has been made, a hash value would appear.
Another test I will perform is to check the Vlookup procedure. I predict that my formula will work and if I change the prices in the lookup table and I change the names of the product then it will also change in the main table.
TASK F: Spreadsheet coursework page 3
Below is a screenshot before I made the changes.
Below is the screenshot after I made the changes in the lookup tables.
TASK F: Spreadsheet coursework page 4
I have only changed the first four products and as you can see my prediction was correct and the Vlookup formulas did work and the main table data changed when the lookup data was changed.
Now I will test whether my validations and formulas are correct. I will do this by typing in a text instead of a value. I predict that when I do this, an error message will appear in the cells that are related to it.
Below is a screenshot of the data before I changed it. Everything is normal.
TASK F: Spreadsheet coursework page 5
As you can see from the screenshot below, errors have been discovered.
A simple mistake clearly represents the error to a manager. This can be seen from the screenshot above.
My final validation is to check the stock level. If the company was low in the amount of a specific item. The cell would portray a “restock” sign every time the stock bought minus the quantity sold was less than two (If(P6-D6<2,”restock”,”ok”)). This makes sure that there is at least 2 pieces of every item in the store, and if not then employees will know of the shortage and order some more once a customer demands it.
The trade price of the item and the price of the item are enlisted just below the list of items sold. This is placed in dedication to the Vlookup tool enclosed in the
TASK F: Spreadsheet coursework page 6
program so that whenever an employee wishes to insert an item sold, all he has to do is write the description of item, and the price and trade price will automatically appear, saving time and increasing the speed of the process of writing in a sold item. Which is what sophisticates the spreadsheet for the producer but simplifies the job for the user, which matches my initial evaluation criteria.
The sub-total VAT and Total were all linked to the price and quantity sold. The VAT was linked directly to the price, and varied according to the sub-total. Almost each column was summed up at the end as the total.
-
To double check whether or not my system worked, I re-did all the calculations by hand to double check. The Profit/loss column was all correct, and so were all the rest of the calculations, so overall I can say that all my testing proved that my system worked.
TASK H: Spreadsheet coursework page 1
H: Evaluation:
As I have finally established and completed my spreadsheet, I can derive and deduce many factors and mistakes myself. Overall the final spreadsheet meets my targets set in the initial evaluation criteria. It is faster, more organised and reliable software package that will receive stock taking and the VAT calculation. It is very simple to use, with automatic calculation and insertion of the price on the screen the moment the item is keyed in the computer. It can store a lot of information in minimum the possible space (memory used), as it saves efficiently and very rapidly.
The spreadsheet set in theory calculates practically all the basic measurements needed to be calculated in a stock-taking package. These will include:-
VAT
Profit and losses
Price of item sold
Totals
Stock bought
Quantity sold
The beauty of these headings is easy to read from, as it is easily recognised what will go under each column.
This spreadsheet is circulated around the fact that, it must be complex for me to produce but as simple and quick to use for the user. In relation to this my spreadsheet is incredibly successful. Incorporating Vlookup and If statement, producing Validation methods to get a more accurate result, and a higher possibility that the data typed in is correct.
If I was working at a higher level I would have made the formulas read-only formulas, so that the symbolic representation (formulas) can not be deleted which will therefore avoid the possibility of a corruption in the system.
In addition to improving my spreadsheet I will definitely include more validation formulas to increase the percentage of validness in the spreadsheet. For
TASK H: Spreadsheet coursework page 2
instance at a higher level I will provide a validation formula that will adjust to the item specified. I.e. It is possible to sell more than 10 computer cables, but is unlikely to sell more than 10 computers to the same person, this is why I would have liked to justify the overflow validation constricting it to each kind of item.
E.g. For a cable the quantity sold will rise to a maximum of about 25, where as a Pentium 3 processor only a maximum of about 4.
Further improvements are contained go under the headings of quantity, Price, VAT and Total where another validation method can be used to investigate for minus figures (i.e. below the number 0). Additional work can be made to find how to persuade the computer to write, “Profit” if the sale made a profit and vice-versa for “Loss”.
In relation to my initial set criteria my uses of VLookup and the If statements used are undoubtedly useful and causes less hassle to the user. The initial target was to develop a spreadsheet to calculate the VAT, my final spreadsheet is able to do this and much more, I have now created a spreadsheet that can calculate everything in great detail I have produced an entire package to satisfy a company’s entire needs.
Not only have I produced a package that can calculate the VAT, I have also made it easier for the user to function the filing system. This is due to the reason that I have produced “IF and Vlookup statements” so that once the member of staff types in the description of item, the price of the item, how much VAT is due, and the total amount is shown. As well as this it automatically shows our profits and losses after the presently sold item. These listings will be confidently confirmed by using the validation and verification formulas.
My final judgement on the final spreadsheet produced is that it is an asset to any company who functions it and hopefully in the future it can progress to be even more efficient.
