Student Name Denys Albesko
Registration number
Centre name Coulsdon College
Centre number 14342
Assessor name Susan Buckley
Assessor signature
Date
Deadline date for unit 27/2/04
Date work handed in
Centre statement on ‘working independently’. (Comment on amount of support given)
Final points score awarded (out of 24)
Final grade (Below Pass, E, D, C, B, A)
Unit Grade Below Pass E D C B A
Raw Mark 0 - 6 7 - 9 10 - 12 13 - 15 16 - 18 19 - 24
ASSESSMENT EVIDENCE FOR UNIT 3 or X Page ref Comment
BANNER COVERAGE OF THE BANNER
A spreadsheet solution to meet specified user requirements,
involving the use of at least six of the more complex spreadsheet facilities (Please list below)
1. List and tables
2. Drop down boxes to select data for entry
3. Macro buttons
4. Validation boxes
5. Multiple sheets with links between them
6. A lot of formulas and forms
User and technical documentation,
including a test report
THERE MUST BE EVIDENCE TO SHOW THE FOLLOWING:
GRADE ‘E’ CRITERIA
E1 A clear design specification that meets user requirements, 5-10
including appropriate:
selection of more complex facilities,
details of sources of data,
outline screen data entry forms,
calculations required,
user aids to operation and
how output is presented
E2 Suitable data entry facilities, 5-10
including input messages and
macros that:
reduce keystrokes and
improve user efficiency
E3 Suitable printed or screen output that makes appropriate use of: 5-10
cell formats
charts or graphs
page or screen layout
graphic images
E4 Clear technical documentation identifying: 24-25
formulae used
functions used, and
screen and printed report layouts 11-23
E5 Clear user documentation with:
copies of menus and screens, and
E6 Testing of your spreadsheet against the design specification and 26-32
careful checking of:
the accuracy of the data used and
the output generated
GRADE ‘C’ CRITERIA
C1 A good understanding of spreadsheet design and attention to detail by creating:
an imaginative,
customised spreadsheet
that makes good use of design and
layout facilities
C2 Detailed test specifications together with
examples of a full range of:
acceptable input
unacceptable input
associated expected output and
any associated error messages
C3 That you can work independently to produce your work to agreed deadlines
GRADE ‘A’ CRITERIA
A1 A good understanding of the purpose and value of more complex facilities by using them effectively in your spreadsheet design
A2 Customised data input using facilities such as
forms
dialogue boxes and
list boxes
or methods of equal value (please list below)
that are clear
well laid out
suitably labelled and
that validate data input
A3 Comprehensive records of spreadsheet drafting,
testing and
refinement that show
how the spreadsheet was developed and
how any problems were resolved
A4 High-quality, clear user documentation
making good use of graphic images
in detailed instructions for use with examples of:
menus and data input screens,
possible error messages
Specification
I have been asked by a company named “One&one ltd.” to create a database on a spreadsheet for them.
This company sells such goods as digital TVs, Computers, Different parts of computers like memory cards, video cards etc. The company also sells CDs and DVDs. They also want to know what profit they will make over they after they sell all the possible items. They also want to provide a look up system so they can see weather they have a certain product in the stock or not.
The company expects from the new electrical system database of all the stock in the shop how much it costs to buy and sell the stock and how much they have in the stock. They also want invoices and purchase forms with the price of the stock how many they want and what the total comes to, on the invoice they need to know what the price of the stock will come to after Vat and if the customers gets a discount or not.
Plan
The system I have designed consists of the following:
Main Menu
I will create a main page with macros on it which will have control keys which will link them to other pages in the workbook.
Database
The data base will give information on the stock which includes all the services the company providing and the prices. The database will also be used for v-lookup.
Invoices
To show how much each customer owes the company and what they want to buy, this will template and formulae which will be created using the vlookup table and functions which automatically calculate when data is input.
Purchase order
This is to show what the company needs in respects to stock ad how much it will cost to order the sock.
Product lookup
To help find products easily and quickly using a V-lookup of the company’s database.
Main Page
This page will help to give access to the other pages in the system using macros.
Database
Invoices
Cash
Cheque
Debit
Credit Card
Purchase order
Product lookup
Stock add system
This information will be shown through the v-lookup() which will be created on this sheet.
The six complex facilities I will use in my excel system will be the following:
1.) List and tables
2.) Drop down boxes to select data for entry
3.) Macro buttons
4.) Validation boxes
5.) Multiple sheets with links between them
6.) Form templates
User Guide
Getting started:
Here I will describe how to open the company system in easy and understandable steps.
First of all
Turn on your computer and wait for the windows screen to load up. When the computer has loaded you will see this screen:
Once you have this screen you will have to double click the excel icon shown below:
Once you have double clicked on the icon the computer will automatically load up excel, wait until you have got this screen on your computer:
Once you have done this your screen will look like this, you then need to open necessary files to open the system.
When you have clicked on the open icon you will have this on your screen:
Once you have this screen you will need to select the ‘Electronic database’ file by clicking on it, then you have to press ‘Open button’.
Once you have opened the electronic database for One&One Ltd. you will see the screen like this:
Now you will be able to learn how to use the function of the system in the next chapter: such as the macro buttons and the V-lookup function.
Now you will learn how to use each of the different pages in the system.
Once you have loaded the system, you are now ready to learn how to use different functions on the different pages.
If the system is loaded properly you should have the main page on the screen:
As you may notice there are six macro buttons on the page, which will take you to the different pages in the system as, shown above.
To open a different page in the system you will have to click on one of the buttons we suggest for tanning purposes you should click on the database button.
Database
Once you have clicked on the database button on the main menu you should now have this screen on your computer:
Here you can change Items’ names, ID code, put your quantity of the items in stock and put cost price.
Warning: do not change information of other columns.
If you accidentally put ID codes in the wrong order just press ‘Sort Data’:
There are also some macros on the bottom of the sheet:
The ‘Print’ button is use to print current sheet. The ‘Save’ button is used to save entire worksheet. The refresh button resets all old values in the database.
Purchase form
When you open Purchase form it should look like this:
The bottom of the sheet contains the purchase form:
This form has everything you need to make the right purchase.
Product lookup
Product lookup is used to check if you have some specific item or not.
When you open the ‘Product lookup’ sheet it has to look like this:
You can choose any ID code and the Item, Amount in stock, Cost price and Sale price of the product will be displayed automatically.
Invoice form
The invoice form is used by the customers to order company’s products. My invoice form has the address of the customer and the company, company’s logo and contact details, it also has today’s date:
I also designed a table for invoice form:
As you have already noticed, the customer will be able to get a 5% discount on everything if he/she bye more then 5 items. I also have to mention that if customer’s city is London the delivery will be just £20.
Total Selling price graph and Total profit graph
I also have two graphs which show the total selling price of the products and the total profit:
I have completed the description and now you are completely ready to work with it alone.
Documentation
Unit 3
In this document I will explain how I created the macro buttons, the v-lookup on both the purchase order form and the invoice form, I will also explain several other formulas I used for this assignment.
Main Menu
I have 6 macro buttons which link to the different pages of the system.
1st one is Purchase form macro;
2nd one is Product lookup macro;
3rd one is Invoice macro;
4th one is Database macro;
5th one is Profit macro;
6th one is Total selling price macro.
I recorded each macro by opening tools tab and choosing Record new macro in macro menu. I opened the correct page for each macro and stopped recording. Afterwards I created 6 buttons and put the right macro in each button.
Database
On the database there are number of a different formulas, which are need on the spreadsheet. I have total price formula which is (Amount * Selling Price), total price formula including VAT (Amount * Selling Price + Amount * Selling Price * 17.5%), formula for selling price which is 10% bigger then Cost price (Cost Price + Cost Price * * 10%), Profit per item (Selling Price – Cost Price), Total Profit (Profit Per Item * Amount).
I also have a lot of macros. 1st returns you to the main page. I recorded it the same way as I recorded the macros in main page. The 2nd one sorts the data; I recorded it by using sort data from the data tab. The function of this macro is to sort numbers if they are in the wrong order. I also have refresh function which resets all the values on the database page to zero. I also have macro which prints page, I recorded it by pressing print button and stopping the recording. I recorded the save macro in the same way as I recorded print macro --- just by pressing save while recording a macro.
Invoice and Purchase forms, Product lookup
For these pages I used very important formula. Vlookup is usually used for electronic databases. Its function is to show products from database when the specific code is selected. Vlookup function in the invoice looks like this: =VLOOKUP(C19,Database!$B$8:$J$17,2)
It means that for specific value of sell C19 Vlookup has to look into the database and copy the right value from Item column.
The macros refresh, print, save, return to main page were recorded in the same way as they were on the Database page.
I also have ‘If’ function on the invoice page on the discount page. It means if the quantity of bought items more then 5 then customer will receive a 5% discount.
I also have two graphs which show total selling price and profit. I have created them by using a chart wizard.
What will I test? Outcome Result Page No.
‘Database Macro’ on the main page Worked Opens ‘Database’ sheet. Pages 14 – 17 in my user guide.
Page 29 testing.
Testing ‘Sort Data’ macro on the database page Did not work. Fixed. Working properly. Sorts data in the database. Page 15 in my user guide.
Pages 30 - 31 testing.
Database formulas Worked Checking products and their prices, calculates totals, calculates VAT. Page 15 in my user guide.
Pages 31 – 32 testing.
‘If’ function (invoice sheet) Worked Decides values of discount and delivery. Page 21 in my user guide.
Pages 32 - 34 testing.
‘Vlookup’ function Worked Puts the required data into table (it could be quantity, selling price, description, etc.). Pages 31 – 32 in my testing.
Pages 24 – 25 in my documentation.
Pages 23 –24 in my user guide.
Complete Testing
Main page
‘Purchase Form’ Macro Worked Opens Purchase Form. Pages 19 – 21 in my user guide.
‘Product lookup’ Macro Worked Opens Product lookup. Page 21 in my user guide.
‘Profit’ Macro Worked Opens Profit graph. Pages 23 – 24 in my user guide.
‘Total selling price’ Macro Worked Opens Total selling price graph. Pages 23 – 24 in my user guide.
‘Invoice’ Macro Worked Opens Invoice form. Pages 22 – 23 in my user guide.
‘Database’ Macro Worked Opens Database sheet. Pages 14 – 17 in my user guide.
Page 29 in my testing.
Database sheet
‘Back to main page’ Macro Worked Returns you to main page. Page 18 in my user guide.
Testing ‘Sort Data’ macro on the database page Did not work. Fixed. Working properly. Sorts data in the database. Page 15 in my user guide.
Pages 30 – 31 testing.
Database formulas Worked Checking products and their prices, calculates totals, calculates VAT. Page 15 in my user guide.
Pages 31 – 32 testing.
‘Print’ Macro Worked Prints current sheet. Page 19 in my user guide.
‘Save’ Macro Worked Saves entire worksheet. Page 19 in my user guide.
‘Refresh’ Macro Worked Resets current data on the database sheet. Page 19 in my user guide.
Purchase Form
‘Back to main page’ Macro Worked Returns you to main page. Page 20 in my user guide.
‘Save’ Macro Worked Saves entire worksheet. Page 20 in my user guide.
‘Refresh’ Macro Worked Resets all data in purchase form. Page 20 in my user guide.
‘Print’ Macro Worked Prints current sheet. Page 20 in my user guide.
‘Vlookup’ function Worked Puts the required data into table (it could be quantity, selling price, description, etc.). Pages 31 – 32 in my testing.
Pages 24 – 25 in my documentation.
Pages 23 –24 in my user guide.
Product lookup
‘Back to main page’ Macro Worked Returns you to main page. Page 21 in my user guide.
‘Save Macro’ Worked Saves entire worksheet. Page 21 in my user guide.
‘Print’ Macro Worked Prints current sheet. Page 21 in my user guide.
‘Vlookup’ function Worked Puts the required data into table (it could be quantity, selling price, description, etc.). Pages 31 – 32 in my testing.
Pages 24 – 25 in my documentation.
Pages 23 –24 in my user guide.
Invoice form
‘Back to main page’ Macro Worked Returns you to main page. Pages 22-23 in my user guide.
‘If’ function Worked Decides values of discount and delivery. Page 21 in my user guide.
Pages 32 - 34 testing.
‘Print’, ‘Save’, ‘Refresh’ Macros Worked Prints sheet.Saves worksheet.Resets data Pages 22-23 in my user guide.
Testing screendumps
Macros
Testing ‘Database Macro’ on the main page:
Macro is working and it opens database when we press the ‘Database’ button.
Testing ‘Sort data’ macro on the database page:
This macro doesn’t work because an error has occurred. We will have to fix it. I will delete the current button with macro by pressing on it with right button and pressing delete on the keyboard. To record a new macro I will have to choose ‘Record new macro’ from the tools panel.
Now when recording is in progress we will have to press ‘Sort data’ button in the Data panel. After this we have to choose sort by ID code and set Ascending order. The last thing we have to do is to press ‘Stop recording’ button.
Now we have to create a new ‘Sort data’ button.
Testing new ‘Sort data’ button:
The macro is working and we don’t receive any error reports.
Formulas
Database formulas:
I used a lot of formulas in this assignment. For sale price I had to use this formula: $I$2*E8+E8. Sell I2 is the absolute because it shows how much bigger Sale price from the cost price.
So the formula is ‘Percentage * Cost price + Cost price = Sale price’.
Total selling price = Amount in stock * Selling price.
This is very common formula.
Total selling price with VAT = Total selling price + Total selling price * 17.5%.
This formula means that Total selling price with VAT supposed to be bigger on 17.5% from Total selling price.
Profit shows how much profit you will get from selling one unit of current goods.
The formula is ‘Sale price - Cost price’.
Total profit shows how much profit you will get from selling the whole stock.
Total profit = Profit * Amount in stock.
For this assignment I also used an ‘If’ function. For example:
This function means that if cell G12 equals “London” (we have to put quotation mark to show Microsoft Excel that data is a text data) then the delivery costs only £20.00, but if cell G12 equals to any other value then delivery costs £100.00. Cell G12 is in customer details and it shows customer’s city.
I also used ‘If’ function for discount:
If customer buys more then any 5 items he gets a 5% discount on everything he buys. This formula is a little bit complex then the previous one. It shows that if sum of the quantity column is higher then 5 then discount equals 5% from total. We are going to use this in subtotal. To find the subtotal we have to find the sum of Total, VAT and Delivery and extract the price of discount.
For this assignment I also used a ‘Vlookup’ function:
‘Vlookup’ function shows information from other sheets of the same document using the information it has. For example: under ID code 2 we have Sony Media player which costs £149.99 and the current amount of Sony Vaio Media players in the stock is 50. ‘Vlookup’ function used the information from database.
Evaluation
For this assignment I used a lot of techniques like Macros, different formulas, shading, I used different fonts and combined colors and tables. I tested all of macros and formulas and pleased with their work. At this point everything works properly.
Student Name Denys Albesko
Registration number
Centre name Coulsdon College
Centre number 14342
Assessor name Susan Buckley
Assessor signature
Date
Deadline date for unit 27/2/04
Date work handed in
Centre statement on ‘working independently’. (Comment on amount of support given)
Final points score awarded (out of 24)
Final grade (Below Pass, E, D, C, B, A)
Unit Grade Below Pass E D C B A
Raw Mark 0 - 6 7 - 9 10 - 12 13 - 15 16 - 18 19 - 24
ASSESSMENT EVIDENCE FOR UNIT 3 or X Page ref Comment
BANNER COVERAGE OF THE BANNER
A spreadsheet solution to meet specified user requirements,
involving the use of at least six of the more complex spreadsheet facilities (Please list below)
1. List and tables
2. Drop down boxes to select data for entry
3. Macro buttons
4. Validation boxes
5. Multiple sheets with links between them
6. A lot of formulas and forms
User and technical documentation,
including a test report
THERE MUST BE EVIDENCE TO SHOW THE FOLLOWING:
GRADE ‘E’ CRITERIA
E1 A clear design specification that meets user requirements, 5-10
including appropriate:
selection of more complex facilities,
details of sources of data,
outline screen data entry forms,
calculations required,
user aids to operation and
how output is presented
E2 Suitable data entry facilities, 5-10
including input messages and
macros that:
reduce keystrokes and
improve user efficiency
E3 Suitable printed or screen output that makes appropriate use of: 5-10
cell formats
charts or graphs
page or screen layout
graphic images
E4 Clear technical documentation identifying: 24-25
formulae used
functions used, and
screen and printed report layouts 11-23
E5 Clear user documentation with:
copies of menus and screens, and
E6 Testing of your spreadsheet against the design specification and 26-32
careful checking of:
the accuracy of the data used and
the output generated
GRADE ‘C’ CRITERIA
C1 A good understanding of spreadsheet design and attention to detail by creating:
an imaginative,
customised spreadsheet
that makes good use of design and
layout facilities
C2 Detailed test specifications together with
examples of a full range of:
acceptable input
unacceptable input
associated expected output and
any associated error messages
C3 That you can work independently to produce your work to agreed deadlines
GRADE ‘A’ CRITERIA
A1 A good understanding of the purpose and value of more complex facilities by using them effectively in your spreadsheet design
A2 Customised data input using facilities such as
forms
dialogue boxes and
list boxes
or methods of equal value (please list below)
that are clear
well laid out
suitably labelled and
that validate data input
A3 Comprehensive records of spreadsheet drafting,
testing and
refinement that show
how the spreadsheet was developed and
how any problems were resolved
A4 High-quality, clear user documentation
making good use of graphic images
in detailed instructions for use with examples of:
menus and data input screens,
possible error messages
Specification
I have been asked by a company named “One&one ltd.” to create a database on a spreadsheet for them.
This company sells such goods as digital TVs, Computers, Different parts of computers like memory cards, video cards etc. The company also sells CDs and DVDs. They also want to know what profit they will make over they after they sell all the possible items. They also want to provide a look up system so they can see weather they have a certain product in the stock or not.
The company expects from the new electrical system database of all the stock in the shop how much it costs to buy and sell the stock and how much they have in the stock. They also want invoices and purchase forms with the price of the stock how many they want and what the total comes to, on the invoice they need to know what the price of the stock will come to after Vat and if the customers gets a discount or not.
Plan
The system I have designed consists of the following:
Main Menu
I will create a main page with macros on it which will have control keys which will link them to other pages in the workbook.
Database
The data base will give information on the stock which includes all the services the company providing and the prices. The database will also be used for v-lookup.
Invoices
To show how much each customer owes the company and what they want to buy, this will template and formulae which will be created using the vlookup table and functions which automatically calculate when data is input.
Purchase order
This is to show what the company needs in respects to stock ad how much it will cost to order the sock.
Product lookup
To help find products easily and quickly using a V-lookup of the company’s database.
Main Page
This page will help to give access to the other pages in the system using macros.
Database
Invoices
Cash
Cheque
Debit
Credit Card
Purchase order
Product lookup
Stock add system
This information will be shown through the v-lookup() which will be created on this sheet.
The six complex facilities I will use in my excel system will be the following:
1.) List and tables
2.) Drop down boxes to select data for entry
3.) Macro buttons
4.) Validation boxes
5.) Multiple sheets with links between them
6.) Form templates
User Guide
Getting started:
Here I will describe how to open the company system in easy and understandable steps.
First of all
Turn on your computer and wait for the windows screen to load up. When the computer has loaded you will see this screen:
Once you have this screen you will have to double click the excel icon shown below:
Once you have double clicked on the icon the computer will automatically load up excel, wait until you have got this screen on your computer:
Once you have done this your screen will look like this, you then need to open necessary files to open the system.
When you have clicked on the open icon you will have this on your screen:
Once you have this screen you will need to select the ‘Electronic database’ file by clicking on it, then you have to press ‘Open button’.
Once you have opened the electronic database for One&One Ltd. you will see the screen like this:
Now you will be able to learn how to use the function of the system in the next chapter: such as the macro buttons and the V-lookup function.
Now you will learn how to use each of the different pages in the system.
Once you have loaded the system, you are now ready to learn how to use different functions on the different pages.
If the system is loaded properly you should have the main page on the screen:
As you may notice there are six macro buttons on the page, which will take you to the different pages in the system as, shown above.
To open a different page in the system you will have to click on one of the buttons we suggest for tanning purposes you should click on the database button.
Database
Once you have clicked on the database button on the main menu you should now have this screen on your computer:
Here you can change Items’ names, ID code, put your quantity of the items in stock and put cost price.
Warning: do not change information of other columns.
If you accidentally put ID codes in the wrong order just press ‘Sort Data’:
There are also some macros on the bottom of the sheet:
The ‘Print’ button is use to print current sheet. The ‘Save’ button is used to save entire worksheet. The refresh button resets all old values in the database.
Purchase form
When you open Purchase form it should look like this:
The bottom of the sheet contains the purchase form:
This form has everything you need to make the right purchase.
Product lookup
Product lookup is used to check if you have some specific item or not.
When you open the ‘Product lookup’ sheet it has to look like this:
You can choose any ID code and the Item, Amount in stock, Cost price and Sale price of the product will be displayed automatically.
Invoice form
The invoice form is used by the customers to order company’s products. My invoice form has the address of the customer and the company, company’s logo and contact details, it also has today’s date:
I also designed a table for invoice form:
As you have already noticed, the customer will be able to get a 5% discount on everything if he/she bye more then 5 items. I also have to mention that if customer’s city is London the delivery will be just £20.
Total Selling price graph and Total profit graph
I also have two graphs which show the total selling price of the products and the total profit:
I have completed the description and now you are completely ready to work with it alone.
Documentation
Unit 3
In this document I will explain how I created the macro buttons, the v-lookup on both the purchase order form and the invoice form, I will also explain several other formulas I used for this assignment.
Main Menu
I have 6 macro buttons which link to the different pages of the system.
1st one is Purchase form macro;
2nd one is Product lookup macro;
3rd one is Invoice macro;
4th one is Database macro;
5th one is Profit macro;
6th one is Total selling price macro.
I recorded each macro by opening tools tab and choosing Record new macro in macro menu. I opened the correct page for each macro and stopped recording. Afterwards I created 6 buttons and put the right macro in each button.
Database
On the database there are number of a different formulas, which are need on the spreadsheet. I have total price formula which is (Amount * Selling Price), total price formula including VAT (Amount * Selling Price + Amount * Selling Price * 17.5%), formula for selling price which is 10% bigger then Cost price (Cost Price + Cost Price * * 10%), Profit per item (Selling Price – Cost Price), Total Profit (Profit Per Item * Amount).
I also have a lot of macros. 1st returns you to the main page. I recorded it the same way as I recorded the macros in main page. The 2nd one sorts the data; I recorded it by using sort data from the data tab. The function of this macro is to sort numbers if they are in the wrong order. I also have refresh function which resets all the values on the database page to zero. I also have macro which prints page, I recorded it by pressing print button and stopping the recording. I recorded the save macro in the same way as I recorded print macro --- just by pressing save while recording a macro.
Invoice and Purchase forms, Product lookup
For these pages I used very important formula. Vlookup is usually used for electronic databases. Its function is to show products from database when the specific code is selected. Vlookup function in the invoice looks like this: =VLOOKUP(C19,Database!$B$8:$J$17,2)
It means that for specific value of sell C19 Vlookup has to look into the database and copy the right value from Item column.
The macros refresh, print, save, return to main page were recorded in the same way as they were on the Database page.
I also have ‘If’ function on the invoice page on the discount page. It means if the quantity of bought items more then 5 then customer will receive a 5% discount.
I also have two graphs which show total selling price and profit. I have created them by using a chart wizard.
What will I test? Outcome Result Page No.
‘Database Macro’ on the main page Worked Opens ‘Database’ sheet. Pages 14 – 17 in my user guide.
Page 29 testing.
Testing ‘Sort Data’ macro on the database page Did not work. Fixed. Working properly. Sorts data in the database. Page 15 in my user guide.
Pages 30 - 31 testing.
Database formulas Worked Checking products and their prices, calculates totals, calculates VAT. Page 15 in my user guide.
Pages 31 – 32 testing.
‘If’ function (invoice sheet) Worked Decides values of discount and delivery. Page 21 in my user guide.
Pages 32 - 34 testing.
‘Vlookup’ function Worked Puts the required data into table (it could be quantity, selling price, description, etc.). Pages 31 – 32 in my testing.
Pages 24 – 25 in my documentation.
Pages 23 –24 in my user guide.
Complete Testing
Main page
‘Purchase Form’ Macro Worked Opens Purchase Form. Pages 19 – 21 in my user guide.
‘Product lookup’ Macro Worked Opens Product lookup. Page 21 in my user guide.
‘Profit’ Macro Worked Opens Profit graph. Pages 23 – 24 in my user guide.
‘Total selling price’ Macro Worked Opens Total selling price graph. Pages 23 – 24 in my user guide.
‘Invoice’ Macro Worked Opens Invoice form. Pages 22 – 23 in my user guide.
‘Database’ Macro Worked Opens Database sheet. Pages 14 – 17 in my user guide.
Page 29 in my testing.
Database sheet
‘Back to main page’ Macro Worked Returns you to main page. Page 18 in my user guide.
Testing ‘Sort Data’ macro on the database page Did not work. Fixed. Working properly. Sorts data in the database. Page 15 in my user guide.
Pages 30 – 31 testing.
Database formulas Worked Checking products and their prices, calculates totals, calculates VAT. Page 15 in my user guide.
Pages 31 – 32 testing.
‘Print’ Macro Worked Prints current sheet. Page 19 in my user guide.
‘Save’ Macro Worked Saves entire worksheet. Page 19 in my user guide.
‘Refresh’ Macro Worked Resets current data on the database sheet. Page 19 in my user guide.
Purchase Form
‘Back to main page’ Macro Worked Returns you to main page. Page 20 in my user guide.
‘Save’ Macro Worked Saves entire worksheet. Page 20 in my user guide.
‘Refresh’ Macro Worked Resets all data in purchase form. Page 20 in my user guide.
‘Print’ Macro Worked Prints current sheet. Page 20 in my user guide.
‘Vlookup’ function Worked Puts the required data into table (it could be quantity, selling price, description, etc.). Pages 31 – 32 in my testing.
Pages 24 – 25 in my documentation.
Pages 23 –24 in my user guide.
Product lookup
‘Back to main page’ Macro Worked Returns you to main page. Page 21 in my user guide.
‘Save Macro’ Worked Saves entire worksheet. Page 21 in my user guide.
‘Print’ Macro Worked Prints current sheet. Page 21 in my user guide.
‘Vlookup’ function Worked Puts the required data into table (it could be quantity, selling price, description, etc.). Pages 31 – 32 in my testing.
Pages 24 – 25 in my documentation.
Pages 23 –24 in my user guide.
Invoice form
‘Back to main page’ Macro Worked Returns you to main page. Pages 22-23 in my user guide.
‘If’ function Worked Decides values of discount and delivery. Page 21 in my user guide.
Pages 32 - 34 testing.
‘Print’, ‘Save’, ‘Refresh’ Macros Worked Prints sheet.Saves worksheet.Resets data Pages 22-23 in my user guide.
Testing screendumps
Macros
Testing ‘Database Macro’ on the main page:
Macro is working and it opens database when we press the ‘Database’ button.
Testing ‘Sort data’ macro on the database page:
This macro doesn’t work because an error has occurred. We will have to fix it. I will delete the current button with macro by pressing on it with right button and pressing delete on the keyboard. To record a new macro I will have to choose ‘Record new macro’ from the tools panel.
Now when recording is in progress we will have to press ‘Sort data’ button in the Data panel. After this we have to choose sort by ID code and set Ascending order. The last thing we have to do is to press ‘Stop recording’ button.
Now we have to create a new ‘Sort data’ button.
Testing new ‘Sort data’ button:
The macro is working and we don’t receive any error reports.
Formulas
Database formulas:
I used a lot of formulas in this assignment. For sale price I had to use this formula: $I$2*E8+E8. Sell I2 is the absolute because it shows how much bigger Sale price from the cost price.
So the formula is ‘Percentage * Cost price + Cost price = Sale price’.
Total selling price = Amount in stock * Selling price.
This is very common formula.
Total selling price with VAT = Total selling price + Total selling price * 17.5%.
This formula means that Total selling price with VAT supposed to be bigger on 17.5% from Total selling price.
Profit shows how much profit you will get from selling one unit of current goods.
The formula is ‘Sale price - Cost price’.
Total profit shows how much profit you will get from selling the whole stock.
Total profit = Profit * Amount in stock.
For this assignment I also used an ‘If’ function. For example:
This function means that if cell G12 equals “London” (we have to put quotation mark to show Microsoft Excel that data is a text data) then the delivery costs only £20.00, but if cell G12 equals to any other value then delivery costs £100.00. Cell G12 is in customer details and it shows customer’s city.
I also used ‘If’ function for discount:
If customer buys more then any 5 items he gets a 5% discount on everything he buys. This formula is a little bit complex then the previous one. It shows that if sum of the quantity column is higher then 5 then discount equals 5% from total. We are going to use this in subtotal. To find the subtotal we have to find the sum of Total, VAT and Delivery and extract the price of discount.
For this assignment I also used a ‘Vlookup’ function:
‘Vlookup’ function shows information from other sheets of the same document using the information it has. For example: under ID code 2 we have Sony Media player which costs £149.99 and the current amount of Sony Vaio Media players in the stock is 50. ‘Vlookup’ function used the information from database.
Evaluation
For this assignment I used a lot of techniques like Macros, different formulas, shading, I used different fonts and combined colors and tables. I tested all of macros and formulas and pleased with their work. At this point everything works properly.
Student Name Denys Albesko
Registration number
Centre name Coulsdon College
Centre number 14342
Assessor name Susan Buckley
Assessor signature
Date
Deadline date for unit 27/2/04
Date work handed in
Centre statement on ‘working independently’. (Comment on amount of support given)
Final points score awarded (out of 24)
Final grade (Below Pass, E, D, C, B, A)
Unit Grade Below Pass E D C B A
Raw Mark 0 - 6 7 - 9 10 - 12 13 - 15 16 - 18 19 - 24
ASSESSMENT EVIDENCE FOR UNIT 3 or X Page ref Comment
BANNER COVERAGE OF THE BANNER
A spreadsheet solution to meet specified user requirements,
involving the use of at least six of the more complex spreadsheet facilities (Please list below)
1. List and tables
2. Drop down boxes to select data for entry
3. Macro buttons
4. Validation boxes
5. Multiple sheets with links between them
6. A lot of formulas and forms
User and technical documentation,
including a test report
THERE MUST BE EVIDENCE TO SHOW THE FOLLOWING:
GRADE ‘E’ CRITERIA
E1 A clear design specification that meets user requirements, 5-10
including appropriate:
selection of more complex facilities,
details of sources of data,
outline screen data entry forms,
calculations required,
user aids to operation and
how output is presented
E2 Suitable data entry facilities, 5-10
including input messages and
macros that:
reduce keystrokes and
improve user efficiency
E3 Suitable printed or screen output that makes appropriate use of: 5-10
cell formats
charts or graphs
page or screen layout
graphic images
E4 Clear technical documentation identifying: 24-25
formulae used
functions used, and
screen and printed report layouts 11-23
E5 Clear user documentation with:
copies of menus and screens, and
E6 Testing of your spreadsheet against the design specification and 26-32
careful checking of:
the accuracy of the data used and
the output generated
GRADE ‘C’ CRITERIA
C1 A good understanding of spreadsheet design and attention to detail by creating:
an imaginative,
customised spreadsheet
that makes good use of design and
layout facilities
C2 Detailed test specifications together with
examples of a full range of:
acceptable input
unacceptable input
associated expected output and
any associated error messages
C3 That you can work independently to produce your work to agreed deadlines
GRADE ‘A’ CRITERIA
A1 A good understanding of the purpose and value of more complex facilities by using them effectively in your spreadsheet design
A2 Customised data input using facilities such as
forms
dialogue boxes and
list boxes
or methods of equal value (please list below)
that are clear
well laid out
suitably labelled and
that validate data input
A3 Comprehensive records of spreadsheet drafting,
testing and
refinement that show
how the spreadsheet was developed and
how any problems were resolved
A4 High-quality, clear user documentation
making good use of graphic images
in detailed instructions for use with examples of:
menus and data input screens,
possible error messages
Specification
I have been asked by a company named “One&one ltd.” to create a database on a spreadsheet for them.
This company sells such goods as digital TVs, Computers, Different parts of computers like memory cards, video cards etc. The company also sells CDs and DVDs. They also want to know what profit they will make over they after they sell all the possible items. They also want to provide a look up system so they can see weather they have a certain product in the stock or not.
The company expects from the new electrical system database of all the stock in the shop how much it costs to buy and sell the stock and how much they have in the stock. They also want invoices and purchase forms with the price of the stock how many they want and what the total comes to, on the invoice they need to know what the price of the stock will come to after Vat and if the customers gets a discount or not.
Plan
The system I have designed consists of the following:
Main Menu
I will create a main page with macros on it which will have control keys which will link them to other pages in the workbook.
Database
The data base will give information on the stock which includes all the services the company providing and the prices. The database will also be used for v-lookup.
Invoices
To show how much each customer owes the company and what they want to buy, this will template and formulae which will be created using the vlookup table and functions which automatically calculate when data is input.
Purchase order
This is to show what the company needs in respects to stock ad how much it will cost to order the sock.
Product lookup
To help find products easily and quickly using a V-lookup of the company’s database.
Main Page
This page will help to give access to the other pages in the system using macros.
Database
Invoices
Cash
Cheque
Debit
Credit Card
Purchase order
Product lookup
Stock add system
This information will be shown through the v-lookup() which will be created on this sheet.
The six complex facilities I will use in my excel system will be the following:
1.) List and tables
2.) Drop down boxes to select data for entry
3.) Macro buttons
4.) Validation boxes
5.) Multiple sheets with links between them
6.) Form templates
User Guide
Getting started:
Here I will describe how to open the company system in easy and understandable steps.
First of all
Turn on your computer and wait for the windows screen to load up. When the computer has loaded you will see this screen:
Once you have this screen you will have to double click the excel icon shown below:
Once you have double clicked on the icon the computer will automatically load up excel, wait until you have got this screen on your computer:
Once you have done this your screen will look like this, you then need to open necessary files to open the system.
When you have clicked on the open icon you will have this on your screen:
Once you have this screen you will need to select the ‘Electronic database’ file by clicking on it, then you have to press ‘Open button’.
Once you have opened the electronic database for One&One Ltd. you will see the screen like this:
Now you will be able to learn how to use the function of the system in the next chapter: such as the macro buttons and the V-lookup function.
Now you will learn how to use each of the different pages in the system.
Once you have loaded the system, you are now ready to learn how to use different functions on the different pages.
If the system is loaded properly you should have the main page on the screen:
As you may notice there are six macro buttons on the page, which will take you to the different pages in the system as, shown above.
To open a different page in the system you will have to click on one of the buttons we suggest for tanning purposes you should click on the database button.
Database
Once you have clicked on the database button on the main menu you should now have this screen on your computer:
Here you can change Items’ names, ID code, put your quantity of the items in stock and put cost price.
Warning: do not change information of other columns.
If you accidentally put ID codes in the wrong order just press ‘Sort Data’:
There are also some macros on the bottom of the sheet:
The ‘Print’ button is use to print current sheet. The ‘Save’ button is used to save entire worksheet. The refresh button resets all old values in the database.
Purchase form
When you open Purchase form it should look like this:
The bottom of the sheet contains the purchase form:
This form has everything you need to make the right purchase.
Product lookup
Product lookup is used to check if you have some specific item or not.
When you open the ‘Product lookup’ sheet it has to look like this:
You can choose any ID code and the Item, Amount in stock, Cost price and Sale price of the product will be displayed automatically.
Invoice form
The invoice form is used by the customers to order company’s products. My invoice form has the address of the customer and the company, company’s logo and contact details, it also has today’s date:
I also designed a table for invoice form:
As you have already noticed, the customer will be able to get a 5% discount on everything if he/she bye more then 5 items. I also have to mention that if customer’s city is London the delivery will be just £20.
Total Selling price graph and Total profit graph
I also have two graphs which show the total selling price of the products and the total profit:
I have completed the description and now you are completely ready to work with it alone.
Documentation
Unit 3
In this document I will explain how I created the macro buttons, the v-lookup on both the purchase order form and the invoice form, I will also explain several other formulas I used for this assignment.
Main Menu
I have 6 macro buttons which link to the different pages of the system.
1st one is Purchase form macro;
2nd one is Product lookup macro;
3rd one is Invoice macro;
4th one is Database macro;
5th one is Profit macro;
6th one is Total selling price macro.
I recorded each macro by opening tools tab and choosing Record new macro in macro menu. I opened the correct page for each macro and stopped recording. Afterwards I created 6 buttons and put the right macro in each button.
Database
On the database there are number of a different formulas, which are need on the spreadsheet. I have total price formula which is (Amount * Selling Price), total price formula including VAT (Amount * Selling Price + Amount * Selling Price * 17.5%), formula for selling price which is 10% bigger then Cost price (Cost Price + Cost Price * * 10%), Profit per item (Selling Price – Cost Price), Total Profit (Profit Per Item * Amount).
I also have a lot of macros. 1st returns you to the main page. I recorded it the same way as I recorded the macros in main page. The 2nd one sorts the data; I recorded it by using sort data from the data tab. The function of this macro is to sort numbers if they are in the wrong order. I also have refresh function which resets all the values on the database page to zero. I also have macro which prints page, I recorded it by pressing print button and stopping the recording. I recorded the save macro in the same way as I recorded print macro --- just by pressing save while recording a macro.
Invoice and Purchase forms, Product lookup
For these pages I used very important formula. Vlookup is usually used for electronic databases. Its function is to show products from database when the specific code is selected. Vlookup function in the invoice looks like this: =VLOOKUP(C19,Database!$B$8:$J$17,2)
It means that for specific value of sell C19 Vlookup has to look into the database and copy the right value from Item column.
The macros refresh, print, save, return to main page were recorded in the same way as they were on the Database page.
I also have ‘If’ function on the invoice page on the discount page. It means if the quantity of bought items more then 5 then customer will receive a 5% discount.
I also have two graphs which show total selling price and profit. I have created them by using a chart wizard.
What will I test? Outcome Result Page No.
‘Database Macro’ on the main page Worked Opens ‘Database’ sheet. Pages 14 – 17 in my user guide.
Page 29 testing.
Testing ‘Sort Data’ macro on the database page Did not work. Fixed. Working properly. Sorts data in the database. Page 15 in my user guide.
Pages 30 - 31 testing.
Database formulas Worked Checking products and their prices, calculates totals, calculates VAT. Page 15 in my user guide.
Pages 31 – 32 testing.
‘If’ function (invoice sheet) Worked Decides values of discount and delivery. Page 21 in my user guide.
Pages 32 - 34 testing.
‘Vlookup’ function Worked Puts the required data into table (it could be quantity, selling price, description, etc.). Pages 31 – 32 in my testing.
Pages 24 – 25 in my documentation.
Pages 23 –24 in my user guide.
Complete Testing
Main page
‘Purchase Form’ Macro Worked Opens Purchase Form. Pages 19 – 21 in my user guide.
‘Product lookup’ Macro Worked Opens Product lookup. Page 21 in my user guide.
‘Profit’ Macro Worked Opens Profit graph. Pages 23 – 24 in my user guide.
‘Total selling price’ Macro Worked Opens Total selling price graph. Pages 23 – 24 in my user guide.
‘Invoice’ Macro Worked Opens Invoice form. Pages 22 – 23 in my user guide.
‘Database’ Macro Worked Opens Database sheet. Pages 14 – 17 in my user guide.
Page 29 in my testing.
Database sheet
‘Back to main page’ Macro Worked Returns you to main page. Page 18 in my user guide.
Testing ‘Sort Data’ macro on the database page Did not work. Fixed. Working properly. Sorts data in the database. Page 15 in my user guide.
Pages 30 – 31 testing.
Database formulas Worked Checking products and their prices, calculates totals, calculates VAT. Page 15 in my user guide.
Pages 31 – 32 testing.
‘Print’ Macro Worked Prints current sheet. Page 19 in my user guide.
‘Save’ Macro Worked Saves entire worksheet. Page 19 in my user guide.
‘Refresh’ Macro Worked Resets current data on the database sheet. Page 19 in my user guide.
Purchase Form
‘Back to main page’ Macro Worked Returns you to main page. Page 20 in my user guide.
‘Save’ Macro Worked Saves entire worksheet. Page 20 in my user guide.
‘Refresh’ Macro Worked Resets all data in purchase form. Page 20 in my user guide.
‘Print’ Macro Worked Prints current sheet. Page 20 in my user guide.
‘Vlookup’ function Worked Puts the required data into table (it could be quantity, selling price, description, etc.). Pages 31 – 32 in my testing.
Pages 24 – 25 in my documentation.
Pages 23 –24 in my user guide.
Product lookup
‘Back to main page’ Macro Worked Returns you to main page. Page 21 in my user guide.
‘Save Macro’ Worked Saves entire worksheet. Page 21 in my user guide.
‘Print’ Macro Worked Prints current sheet. Page 21 in my user guide.
‘Vlookup’ function Worked Puts the required data into table (it could be quantity, selling price, description, etc.). Pages 31 – 32 in my testing.
Pages 24 – 25 in my documentation.
Pages 23 –24 in my user guide.
Invoice form
‘Back to main page’ Macro Worked Returns you to main page. Pages 22-23 in my user guide.
‘If’ function Worked Decides values of discount and delivery. Page 21 in my user guide.
Pages 32 - 34 testing.
‘Print’, ‘Save’, ‘Refresh’ Macros Worked Prints sheet.Saves worksheet.Resets data Pages 22-23 in my user guide.
Testing screendumps
Macros
Testing ‘Database Macro’ on the main page:
Macro is working and it opens database when we press the ‘Database’ button.
Testing ‘Sort data’ macro on the database page:
This macro doesn’t work because an error has occurred. We will have to fix it. I will delete the current button with macro by pressing on it with right button and pressing delete on the keyboard. To record a new macro I will have to choose ‘Record new macro’ from the tools panel.
Now when recording is in progress we will have to press ‘Sort data’ button in the Data panel. After this we have to choose sort by ID code and set Ascending order. The last thing we have to do is to press ‘Stop recording’ button.
Now we have to create a new ‘Sort data’ button.
Testing new ‘Sort data’ button:
The macro is working and we don’t receive any error reports.
Formulas
Database formulas:
I used a lot of formulas in this assignment. For sale price I had to use this formula: $I$2*E8+E8. Sell I2 is the absolute because it shows how much bigger Sale price from the cost price.
So the formula is ‘Percentage * Cost price + Cost price = Sale price’.
Total selling price = Amount in stock * Selling price.
This is very common formula.
Total selling price with VAT = Total selling price + Total selling price * 17.5%.
This formula means that Total selling price with VAT supposed to be bigger on 17.5% from Total selling price.
Profit shows how much profit you will get from selling one unit of current goods.
The formula is ‘Sale price - Cost price’.
Total profit shows how much profit you will get from selling the whole stock.
Total profit = Profit * Amount in stock.
For this assignment I also used an ‘If’ function. For example:
This function means that if cell G12 equals “London” (we have to put quotation mark to show Microsoft Excel that data is a text data) then the delivery costs only £20.00, but if cell G12 equals to any other value then delivery costs £100.00. Cell G12 is in customer details and it shows customer’s city.
I also used ‘If’ function for discount:
If customer buys more then any 5 items he gets a 5% discount on everything he buys. This formula is a little bit complex then the previous one. It shows that if sum of the quantity column is higher then 5 then discount equals 5% from total. We are going to use this in subtotal. To find the subtotal we have to find the sum of Total, VAT and Delivery and extract the price of discount.
For this assignment I also used a ‘Vlookup’ function:
‘Vlookup’ function shows information from other sheets of the same document using the information it has. For example: under ID code 2 we have Sony Media player which costs £149.99 and the current amount of Sony Vaio Media players in the stock is 50. ‘Vlookup’ function used the information from database.
Evaluation
For this assignment I used a lot of techniques like Macros, different formulas, shading, I used different fonts and combined colors and tables. I tested all of macros and formulas and pleased with their work. At this point everything works properly.
Student Name Denys Albesko
Registration number
Centre name Coulsdon College
Centre number 14342
Assessor name Susan Buckley
Assessor signature
Date
Deadline date for unit 27/2/04
Date work handed in
Centre statement on ‘working independently’. (Comment on amount of support given)
Final points score awarded (out of 24)
Final grade (Below Pass, E, D, C, B, A)
Unit Grade Below Pass E D C B A
Raw Mark 0 - 6 7 - 9 10 - 12 13 - 15 16 - 18 19 - 24
ASSESSMENT EVIDENCE FOR UNIT 3 or X Page ref Comment
BANNER COVERAGE OF THE BANNER
A spreadsheet solution to meet specified user requirements,
involving the use of at least six of the more complex spreadsheet facilities (Please list below)
1. List and tables
2. Drop down boxes to select data for entry
3. Macro buttons
4. Validation boxes
5. Multiple sheets with links between them
6. A lot of formulas and forms
User and technical documentation,
including a test report
THERE MUST BE EVIDENCE TO SHOW THE FOLLOWING:
GRADE ‘E’ CRITERIA
E1 A clear design specification that meets user requirements, 5-10
including appropriate:
selection of more complex facilities,
details of sources of data,
outline screen data entry forms,
calculations required,
user aids to operation and
how output is presented
E2 Suitable data entry facilities, 5-10
including input messages and
macros that:
reduce keystrokes and
improve user efficiency
E3 Suitable printed or screen output that makes appropriate use of: 5-10
cell formats
charts or graphs
page or screen layout
graphic images
E4 Clear technical documentation identifying: 24-25
formulae used
functions used, and
screen and printed report layouts 11-23
E5 Clear user documentation with:
copies of menus and screens, and
E6 Testing of your spreadsheet against the design specification and 26-32
careful checking of:
the accuracy of the data used and
the output generated
GRADE ‘C’ CRITERIA
C1 A good understanding of spreadsheet design and attention to detail by creating:
an imaginative,
customised spreadsheet
that makes good use of design and
layout facilities
C2 Detailed test specifications together with
examples of a full range of:
acceptable input
unacceptable input
associated expected output and
any associated error messages
C3 That you can work independently to produce your work to agreed deadlines
GRADE ‘A’ CRITERIA
A1 A good understanding of the purpose and value of more complex facilities by using them effectively in your spreadsheet design
A2 Customised data input using facilities such as
forms
dialogue boxes and
list boxes
or methods of equal value (please list below)
that are clear
well laid out
suitably labelled and
that validate data input
A3 Comprehensive records of spreadsheet drafting,
testing and
refinement that show
how the spreadsheet was developed and
how any problems were resolved
A4 High-quality, clear user documentation
making good use of graphic images
in detailed instructions for use with examples of:
menus and data input screens,
possible error messages
Specification
I have been asked by a company named “One&one ltd.” to create a database on a spreadsheet for them.
This company sells such goods as digital TVs, Computers, Different parts of computers like memory cards, video cards etc. The company also sells CDs and DVDs. They also want to know what profit they will make over they after they sell all the possible items. They also want to provide a look up system so they can see weather they have a certain product in the stock or not.
The company expects from the new electrical system database of all the stock in the shop how much it costs to buy and sell the stock and how much they have in the stock. They also want invoices and purchase forms with the price of the stock how many they want and what the total comes to, on the invoice they need to know what the price of the stock will come to after Vat and if the customers gets a discount or not.
Plan
The system I have designed consists of the following:
Main Menu
I will create a main page with macros on it which will have control keys which will link them to other pages in the workbook.
Database
The data base
