The screenshot shows the macro used to add new customers straight to the database. With a click of a button, all the relevant information is automatically transferred. This is done by recording a macro then copying and pasting the information to the relevant cells on the correspondent sheet, then inserting a new line for the next entry. The macro is then stopped and with the “send to database button” the information circled in a red box, is transferred to the customer database. The purpose of this is so the user does not need to copy and paste or manually type the information in.
The next shot shows the sheet working perfectly. There is one macro used in it, it allows the user to go back to the main menu directory and therefore click on to any other sheet. It also shows the added entry from the macro that transfers the client’s information with the click of a button. Other functions which are on this sheet is that it includes an auto filter which allows the headings to be sorted into a custom way and also the user can pick one client from it and only there details appear. The auto filter is on every heading so the information can be viewed and customised from other headings. Furthermore, cells in the A, B column have been formatted so only letters can be inputted, as it is the name. Column H has been formatted for only numbers, J has been formatted so only dates can be in those cells and K is formatted for currency, as it is the amount paid.
This screenshot shows the sheet working with the auto filter, the links that click on to an email message already with clients email address and the macro button that directs the user back to the main menu. Also in row 15, it shows the customer which comes from the order form.
Product Information sheet
The purpose of this sheet is that it provides the user with the information of the products. It includes a unique 4-digit code that the user specified in the user’s needs. Along with the code, there is a description of the product, the price and the quantity remaining. The sheet allows the user to keep track of what products the company sell, as all of the products available would be listed. Moreover, after each order the user manually changes the quantity remaining which makes sure the quantity remaining is always checked. The worksheet works by four main column headings: product code; product name; price and quantity remaining. All work together to provide the necessary information that the user would require.
Screenshot showing the validation working for when the quantity falls below 3
Validation is used so the user is reminded with a message. This works by data > validation and then setting rules for the sheet to follow, so if the value becomes lower than 3 (as with the conditional formatting) a message appears telling the user to re-order stock and asking if the user wants to continue.
If the user presses yes, then the conditional formatting comes into play.
This screenshot shows the conditional formatting used in this sheet. The conditional formatting highlights the quantity remaining column when the stocks run below three. In following screenshots, it will show the quantity above three and the cell is not highlighted because it is not below three, which is what I have set the formatting to do. To do this I went to format > conditional formatting and from there I set the rules for when the cell is highlighted.
When the validation and conditional formatted cells (all cells under the quantity remaining heading) the cell is not highlighted and has no messages.
In addition, this sheet has been protected which means that nobody except the managing director who would have the password to the workbook can access the restricted areas. In the case of this product information sheet, I have locked the price cells so that no one except the managing director can change the prices. This would make sure there is no unofficial changing of the prices. The only way prices can be changed is to unprotect the sheet with the password. To protect the sheet you format the cells you want to protect and make sure it says they are locked, then by protecting the sheet.
Screen shot showing the protecting sheet process
Screenshot shows the final product information sheet with the four headings and nine example products
In this sheet, it has an auto filter for the four top headings that allows the user to organise and list the products in any way they want to i.e. ascending alphabetically or numerically or with least quantity remaining. It also has the main menu button in the top right. Moreover, the conditional formatting and validation of cells is programmed to work when a quantity remaining cell falls below three, as shown in screen shots above i.e. the message and highlighted cells.
Order Form Sheet
The purpose of this sheet is to act as an invoice sheet that can be printed off, sent to customers, and filed away for the firm – if they wish to. In addition, it can be “saved as” another file name so that the generic sheet can be used more than once. The delivery charge, sub total, discount given, grand total and the products automatically appear after the product code is typed. Furthermore, the information from this sheet can be transferred to the customer database by clicking the “Send to database” button. This sheet works out everything for the user and the main purpose is to save time for the user as once the formulas have been programmed the totals and so fourth will be correct as long as the requirements do not change.
This screenshot shows all the formulas displayed. The vlookup feature formula is linked to the product information sheet. The table used in the product information sheet allow the user to type in the product code in the A column and with the vlookup formula the price of the product would appear. With the price appearing, I used that cell to multiply with the quantity to work out a subtotal. The subtotal is then multiplied with a discount rate to work out another total. The discount is worked out by using another table (discount table), with same process as vlookup, it allows a figure to be entered and the discount automatically appears. Other formulas like the delivery charge are a standard £10 with additional £1 per mile, which is worked out automatically through another formula in cell D28. The installation is manually entered, if the client wishes. The total adds up the sub total with discount, delivery and installation and then the final price. The final price needs to be entered again in cell D32 as this allows the total to be transferred to the customer database along with the other personal information.
The formulas work out all the relevant calculations (totals, delivery etc) which the user would have done manually before the system, the system eradicates any human error from occurring when calculating totals.
This sheet also includes a drop down menu (right) of card payments, so the user would not need to keep typing out the card names instead they use this drop down list.
The final order sheet has all the space for clients personal information, payment information, all totals automatically calculated and can all be sent to the database for further use i.e. mail merged letter.
The following is the extremely long macro that transfers the information from the order form to the customer database. The following is the processes that I went through to produce the macro.
Sub please()
'
' please Macro
' Macro recorded 01/02/2006 by tarun
'
'
Range("B4").Select
Selection.Copy
Sheets("Cust_datab").Select
ActiveSheet.Paste
Range("B14").Select
Sheets("Order form").Select
Range("B5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cust_datab").Select
ActiveSheet.Paste
Range("C14").Select
Sheets("Order form").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cust_datab").Select
ActiveSheet.Paste
Range("D14").Select
Sheets("Order form").Select
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cust_datab").Select
ActiveSheet.Paste
Range("E14").Select
Sheets("Order form").Select
Range("B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cust_datab").Select
ActiveSheet.Paste
Range("F14").Select
Sheets("Order form").Select
Range("B9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B43").Select
Sheets("Cust_datab").Select
ActiveSheet.Paste
Range("G14").Select
Sheets("Order form").Select
Range("B10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cust_datab").Select
ActiveSheet.Paste
Range("H14").Select
Sheets("Order form").Select
Range("B11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cust_datab").Select
ActiveSheet.Paste
Range("I14").Select
Sheets("Order form").Select
Range("B12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cust_datab").Select
ActiveSheet.Paste
Range("J14").Select
Sheets("Order form").Select
Range("E2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cust_datab").Select
ActiveSheet.Paste
Range("K14").Select
Sheets("Order form").Select
Range("D32").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cust_datab").Select
ActiveSheet.Paste
Rows("14:14").Select
Range("G14").Activate
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
End Sub
With this created and saved as a macro under the name “please”, I was able to create the button in the right hand corner of the sheet that transfers the information to the customer database. Above shows how the macro is seen in Visual Basic.
Screenshot of the final order form sheet with an example client
The final sheet includes all the formulas mentioned in previous paragraphs. In addition, the vlookup function to enable the discount to work correctly. Furthermore the macro which transfers information from this sheet to the customer database, also the drop down table for card payments are all used in this sheet.
Discount table
The purpose of this sheet is so the vlookup function on the order form works, without the table the discount would need to be calculated manually. Instead, the table has prices at which certain discounts are given. The user stated in their needs that they needed some kind of discount feature. In the order form, the vlookup compares the sub total to the discount table and orders above a certain price get a certain discount as shown below.
Screenshot of discount table
The discount table sheet has the prices for which the vlookup should follow and what percentage discount to give. The sheet also contains the macro, which allows the user to navigate their way back to the main menu without any confusion, with the button in the right hand corner, created in the same way as created on the other sheets in the workbook.
Monthly Review
The purpose of the monthly review sheet is for managing director, who wishes to analyse the sales monthly. The sheet is broken up into individual days and the amount of sales in each day and the total for that day. There then is a monthly sales total for the number of sales and the money spent. The managing director from there can then create graphs and charts from the information.
Screenshot of the cell and their names (only 11 examples – all 31 days would be used for the user)
Both the total cells have been named so that they can be used in the yearly review sheet, so that the totals automatically go to the yearly review sheet.
The user gathers the information for each day by going to the customer database and filtering the order date. The user can then copy and paste into this sheet and the formulas work out the totals. The totals are then transferred into the yearly review, if the totals are changed in this sheet, they will also automatically change in the yearly review.
To get the figures on to this sheet, the data has to be copied at pasted from the customer database. However, to separate the months and years the user can customise the auto filter, which is why I used one for the user.
This sheet and custom auto filter show how the user can separate all the records therefore; they can then copy and paste the information into the monthly review.
The sheet above shows all the formulas used for the sheet. The formulas automatically work out the totals. The totals can then be transferred to the yearly review.
Example of final monthly review sheet with 11 examples
The final sheet includes macros that take the user to the main menu, as on all sheets. In addition to the main menu button, there is a button, which takes the user to the yearly review sheet, this is extremely useful, as they want to switch between the two screens often, to compare and check. The macros were made in the same was as in other previous sheets. Furthermore, the sheet contains all the formulas (shown in previous screenshots) which transfers the totals to the yearly review and calculates the total of the months.
Yearly Review
The purpose of the yearly review is so that the managing director can analyse the figures from that year. From this sheet; charts, graphs and so on can be created to further examine the figures and statistics of Home Office LTD. The sheet displays the each month’s totals, but not in as much detail as the monthly review. From there, the managing director can compare each month’s total sales and units sold. The sheet also allows the user to navigate back to the main menu or the current monthly review sheet.
The screenshot above shows the formulae used
In this sheet, the information from the monthly review sheets is entered automatically using formulas. The “total” cells were named in the monthly review sheet (as explained before) and from there, we can use the “=” and the cell name, in the yearly review sheet and the figures will appear. In the monthly review, the cells were named “jansales” and “jantotal”. With the formulas in cells B6 and C6, the figures from January automatically appear, even if the totals change in the monthly review sheet, they will also automatically change in the yearly review. By naming the cells in each monthly review, all the monthly sales and totals will be entered into the yearly review automatically.
With the formulas at the bottom of the sheet, cells B18 and C18, all the totals from each month are calculated automatically, and if the figures change in any of the cells in the yearly or monthly review, the totals are automatically updated.
In the top right hand, there are two buttons assigned to two macros. The macros/buttons are used to go back to the main menu, as with all the other sheets in the workbook. In addition, there is a button that takes the user to “current month review” sheet, this is another shortcut for when the managing director analyses the data and needs to switch between monthly and yearly review sheets.
Screenshot (above) shows the final yearly review sheet, with only one example of January.
The final yearly review sheet shows the totals, which automatically go into the relevant cells, without the user having to manually type it all out each time. The sheet has all the relevant formulas used for working out and analysing the data. The formulas used, adjust the total each time another month is added. The months are added by naming cells and then using that name in the relevant cell. Furthermore, it has all the buttons, which the user wants to direct themselves to the main menu and to the current monthly review.
Throughout the project, I have used one main piece of software, which is Microsoft Excel. The reason for this is that Excel is a massive calculating, database creating, analytical workbook, which can store thousands of pieces of information and can calculate more sums and information from the original. This programme is perfect for the end user as it has everything that they need. It stores clients, creates sheets with analytical monthly and yearly reviews. The software also has a facility that calculates complicated sums, formulas, percentages and so on without any humans having to work it out themselves, they only need to type in the formula once and the programme will remember it. In the programme, other functions allow the user to do things such as transfer data to one sheet to another with the use of a macro. This once recorded, and then need not be edited unless the sheet is dramatically changed. All of these features help the user have a simple workbook where they can create order forms/invoices, keep track of their stocks, hold a list of all their clients, analyse sales and other figures within the sheet to create graphs and charts.
The other programme used in the project was Microsoft Word. This is used for typing up my findings and all the details of the project. In addition, Word can be used for the Home Office LTD company as they can create mail – merged letters. This means they can then send out generically created letters and have the clients name, address, etc all personalised in the letter without having to type it out themselves. All they need to do is link the mail merge to the database in Excel, which has all the relevant information. Another package used for the project is Visual Basic; it is within the Excel package. With Visual Basic, it allows the user to create the many macros used. It was perfect for this project as without it, all of the buttons would have been extremely difficult to produce as easily as with Visual Basic.