Ассоциации
-
Mr D. Albesko
203 Norbury Crescent
Croydon
SW16 4JX
Phone 01284 346879 Fax 01284 6578908Our Ref: SLS/RWT
30 September 2004
Health Club
124 High Street
Bury St Edmunds
Suffolk
W29 7HGDear Mr Nash
I would like to make a complaint about the state of the pool in your Health Club. Please pay your attention on the condition of the floor in the pool. Yesterday I was with my friends in your Health Club and one of them nearly had an injury when he slipped. I didn’t like the state of your floor in the pool even before this accident, but now I’ve made my mind to tell you my opinion.
Try to think about the ways of solving this problem. Can you put some kind of pool carpets? May be you could change the whole state of your pool, for e. g. increase the room of the pool.
I think you should pay attention on the state of your gym equipment as well. I experienced some problems while training on your Running Machine. There is something wrong with the electronic on it.
Thank you, for your attention to my problem.Yours sincerely
Denys Albesko
Enc.
Mr D. Albesko
203 Norbury Crescent
Croydon
SW16 4JX
Phone 01284 346879 Fax 01284 6578908Our Ref: SLS/RWT
30 September 2004
Health Club
124 High Street
Bury St Edmunds
Suffolk
W29 7HGDear Mr Nash
I would like to make a complaint about the state of the pool in your Health Club. Please pay your attention on the condition of the floor in the pool. Yesterday I was in your Health Club with my friends and one of them nearly had an injury when he slipped. I didn’t like the state of your floor in the pool even before this accident, but now I’ve made my mind to tell you my opinions.
Try to think about the ways of solving this problem. Can you put some kind of pool carpets? May be you should change the whole state of your pool, for e.g. increase the size of the pool.
I think you should pay attention to the state of your gym equipment as well. I experienced some problems while training on your Running Machine. There is something wrong with the electronics on it.
Thank you for your attention to my problems.
Yours sincerely
Denys Albesko
Enc.
Mr D. Albesko
203 Norbury Crescent
Croydon
SW16 4JX
Phone 01284 346879 Fax 01284 6578908Our Ref: SLS/RWT
30 September 2004
Health Club
124 High Street
Bury St Edmunds
Suffolk
W29 7HGDear Mr Nash
I would like to make a complaint about the state of the pool in your Health Club. Please pay your attention on the condition of the floor in the pool. Yesterday I was in your Health Club with my friends and one of them nearly had an injury when he slipped. I didn’t like the state of the floor in the pool even before this accident, but now I’ve made my mind to tell you my opinions.
Try to think about the ways of solving this problem. Can you put some kind of pool carpets? May be you should change the whole state of your pool, for e.g. increase the size of the pool.
I think you should pay attention to the state of your gym equipment as well. I experienced some problems while training on your Running Machine. There is something wrong with the electronics on it.
Thank you for your attention to my problems.
Yours sincerely
Denys Albesko
Enc.
Evaluation
For this assignment I had to make a complaint to my Health Club about the bad state of its pool or gym equipment. Firstly I wrote my address on the top and the date with the Health Club address below. The address and date are in the left part of the letter. So it’s very easy to find addresses and dates. Then I started my letter with the word ‘Dear’ and put the name of the Health Club manager. I also put ‘Mr’ before his, this shows respect and good manners.Afterwards I started to write a letter. I was writing it in a formal style. In the end of the letter I put my thanks for attention to the complaint, I also put ‘Yours sincerely’. Then I put my signature in a freestyle script and finished a letter by writing ‘Enc.’. I improved my first letter in the second one by removing spare spaces in the begging of the paragraphs and putting another spaces.
My third letter had to be perfect that is why I’ve checked all my mistakes and removing them. I put all the signs of punctuation and preserved the main features.
-
Student Name Denys Albesko
Registration number
Centre name Coulsdon College
Centre number 14342
Assessor name Susan Buckley
Assessor signature
DateDeadline date for unit 27/2/04
Date work handed inCentre 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 - 24ASSESSMENT EVIDENCE FOR UNIT 3 or X Page ref CommentBANNER 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 inputA3 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 messagesSpecification
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 CardPurchase 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 templatesUser 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 3In 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 screendumpsMacros
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
DateDeadline date for unit 27/2/04
Date work handed inCentre 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 - 24ASSESSMENT EVIDENCE FOR UNIT 3 or X Page ref CommentBANNER 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 inputA3 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 messagesSpecification
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 CardPurchase 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 templatesUser 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 3In 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 screendumpsMacros
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
DateDeadline date for unit 27/2/04
Date work handed inCentre 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 - 24ASSESSMENT EVIDENCE FOR UNIT 3 or X Page ref CommentBANNER 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 inputA3 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 messagesSpecification
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 CardPurchase 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 templatesUser 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 3In 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 screendumpsMacros
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
DateDeadline date for unit 27/2/04
Date work handed inCentre 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 - 24ASSESSMENT EVIDENCE FOR UNIT 3 or X Page ref CommentBANNER 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 inputA3 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 messagesSpecification
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 -
Student Name Denys Albesko
Registration number
Centre name Coulsdon College
Centre number 14342
Assessor name Susan Buckley
Assessor signature
DateDeadline date for unit 27/2/04
Date work handed inCentre 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 - 24ASSESSMENT EVIDENCE FOR UNIT 3 or X Page ref CommentBANNER 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 inputA3 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 messagesSpecification
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 CardPurchase 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 templatesUser 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 3In 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 screendumpsMacros
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
DateDeadline date for unit 27/2/04
Date work handed inCentre 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 - 24ASSESSMENT EVIDENCE FOR UNIT 3 or X Page ref CommentBANNER 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 inputA3 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 messagesSpecification
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 CardPurchase 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 templatesUser 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 3In 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 screendumpsMacros
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.g
-
Student Name Denys Albesko
Registration number
Centre name Coulsdon College
Centre number 14342
Assessor name Susan Buckley
Assessor signature
DateDeadline date for unit 27/2/04
Date work handed inCentre 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 - 24ASSESSMENT EVIDENCE FOR UNIT 3 or X Page ref CommentBANNER 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 inputA3 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 messagesSpecification
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 CardPurchase 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 templatesUser 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 3In 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 screendumpsMacros
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.fw
-
Student Name Denys Albesko
Registration number
Centre name Coulsdon College
Centre number 14342
Assessor name Susan Buckley
Assessor signature
DateDeadline date for unit 27/2/04
Date work handed inCentre 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 - 24ASSESSMENT EVIDENCE FOR UNIT 3 or X Page ref CommentBANNER 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 inputA3 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 messagesSpecification
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 CardPurchase 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 templatesUser 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 3In 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 screendumpsMacros
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. -
РТР - антена
-
антена-упала вчера)
-
Упала Вчера == Клуб Здоровья!!!!!!
-
Клуб Здоровья ====> аспирин спасет мир!
-
Darkarchangel ты дурак?
аспирин спасет мир! =>бред -
бред ===> состояние души
-
состояние души ==> тоска
-
тоска ===> осень
-
осень :arrow: затерянный во времени
-
затерянный во времени ===> чужой среди чужих
-
чужой среди чужих - свой среди своих
-
свой среди своих - семья
-
семья -> дети
-
Дети - Муму
