File - Saleena`s E
Contents
1. Staff Forename Staff Username Staff Password 5 5 611 amp H11 Cupcakes 612 amp H12 Receptionist 613 amp H13 Haaris IPER LEFT uF 14 G14 amp H14 Macros If there is an error regarding the Macro s within the Workbooks then a Message Box will appear informing you of the Macro Error As shown below It is essential that you double click on the Debug option given in order to load Microsoft Visual Basic Editor VBA It would be very helpful if you have a basic understanding of the Visual Basic Language as you will be able to use it in this particular area in order to find out more about the Error The Visual Basic software is very helpful in the sense that it will highlight the area which is causing the error in bright yellow Macro Error Message Box Microsoft Visual Basi Run time error 1004 Method Select of object Sheets failed Technical Guide Sabz Cupcakes Data Validation The Shared Workbook contains certain cells that are protected with Data Validation this is to prevent you from altering or modifying any significant data The table below lists all the locations in the Shared Workbook where Data Validation is implemented it is very likely that you will receive an Error Message if you attempt to input any insignificant data into the system whilst Validation being active Worksheet Cell Name Cell Number Validation T O O cee 3 and 30 The 1 ee 3 and 30 The O Se O and 10 32. Paste button 9 Although though the file is password protected before opening the Master Workbook you may want to store it in a private location on the network for security purposes 10 You can now use the system as the files have been successfully copied Opening the Shared Workbook In order to start the operating system you are required to go to the area of the shared network in which the Shared Workbook is saved safely and double click on it as you would with any other file or folder You will be asked whether they would like to enable Macro s typically they would enable the Macros in order for the system to function correctly Microsoft Office Security Options Microsoft Office Security Options Security Alerts Multiple Issues Macro Macros have been disabled Macros might contain viruses or other security hazards Do not enable this content unless you trust the source of this file Warning It is not possible to determine that this content came from a trustworthy source You should leave this content disabled unless the content provides critical functionality and you trust its source More information File Path G Mr James Sabz Cupcakes Workbooks Main Final Shared_Workbook xism Help protect me from unknown content recommended O Enable this content Data Connection Data connections have been blocked If you choose to enable data connections your computer may no longer be secure Do
3. Technical Guide Sabz Cupcakes 2013 CR A e q A o f e o o o N f e ee e N d L e L L e 6 X m J Technical Guide 20 1 2 Sabz Cupcakes Technical Guide Sabz Cupcakes Introduction Sabz Cupcake is a local business which bakes and decorates cupcakes for existing as well as new customers it owned by a well known Business woman Mrs Khan also known as Saba Khan The owner and manager of the business Mrs Khan has previously kept all details regarding her business in a little hard back book however she is now struggling to maintain her financial accounts and feels the need to implement a system that will do her and her business justice She feels the need for her system to be easy to use yet very professional as this will enable her to work efficiently Mrs Khan employs a number of staff she has a receptionist that takes care of the orders that come through along with performing secretarial duties In addition she has a very loyal baker who has the duty of baking and decorating the cupcakes accordingly This is why Mrs Khan had requested that the main system to be stored on a shared area of the network in order for her members of staff to be able to have access to the main system using their unique usernames and passwords Records of the staff s usernames and passwords are kept in the Personnel worksheet within the Master Workbook Content Heading Page Number Specification Page 3 Requir
4. Chart 3 Summary Worksheet 177775 Main Menu Customer Input Row Labels v lsum of Total Filling Order Input 1 Icing Quantity Order Input 2 Size Sponge Theme Grand Total Customer Values History Row lav Sum of Icing Price Sum of Sponge Price Sum of Filling Price Sum of Theme Price Sum of Quantity Price Sum of Size Price Aroberts 8 90 9 20 13 50 25 00 Order History Jhaaris 6 50 8 00 12 00 24 00 Skhan 10 50 11 80 18 00 28 00 Grand Tot 25 90 29 00 43 50 77 00 St _Main menu Ee Order mpat a phen Basket Invoice So Order story osa summary Row Labels Sum of Total Total 221 75 Total Including VAT 266 10 VAT 20 44 35 Grand Total 532 20 gt Technical Guide Sabz Cupcakes 2013 Figure 11 Chart 1 Total sales of individual Fields E Total Filling Quantity Figure 12 Chart 2 Sales Performance of Personnel Le E Sum of Sum of Icing Price E Sum of Sum of Sponge Price EM O B E Sum of Sum of Filling Price E Sum of Sum of Theme Price E Sum of Sum of Quantity Price E Sum of Sum of Size Price Jhaaris Skhan Technical Guide Sabz Cupcakes The Stock worksheet informs Mrs Khan of the original stock and the current stock of all the fields that are required for the purpose of her cupcake making and decorating business Icing Sponge Filling as well as the stock levels in other
5. and 30 5 and 8 3 and 30 10 and 12 10 and 12 Technical Guide Sabz Cupcakes Functions amp Formulas In order to protect against future proofing all the cells which are not necessarily required you are prohibited to their usage However if there seems to be a significant error with the calculations regarding your customers order then you have the ability to check the main Functions and Formulas that are inputted into the system The main Functions and Formulas are displayed below within the table provided as the full list of Functions and Formulas are kept in the Formative Testing document Worksheet Cell Name Functions and Formulas Basket Size IF OrderInput2 K11 1 Orderlnput2 F13 IF OrderInput2 K11 2 OrderInput2 H13 IF OrderInput2 K11 3 OrderInput2 J13 0 Basket Quantity IF OrderInput2 SL 20 1 OrderInput2 E22 IF Orderinput2 SLS20 2 0rderinput2 G22 IF OrderInput2 SL 20 3 OrderlInput2 122 IF Orderinput2 SLS20 4 0rderinput2 K22 0 Basket IF AND 16 gt 0 18 gt 0 110 gt 0 112 gt 0 114 gt 0 116 gt 0 16 18 110 114 116 VAT 20 IF ISNUMBER 119 ROUNDUP 119 100 VAT 2 Total Inc VAT IF AND 119 gt 0 120 gt 0 SUM 119 120 Technical Guide Sabz Cupcakes Data Output Worksheet The shared workbook will produce three main types of outputs the first being an Invoice that the customer will receive through their letterbox after the order process has been complete The
6. not enable this content unless you trust the source of this file File Path G Mr James Sabz Cupcakes Workbooks Main Final Shared_Workbook xlsm Help protect me from unknown content recommended Technical Guide Sabz Cupcakes igure 2 Welcome Message Box Welcome to Sabz Cupcakes System You will typically welcomed to the system with a Login worksheet where you are required to enter a correct and functioning username and password in order to proceed The default username is User and the default password is Pass eure 3 Login Worksheet WON HD WO PWN e Username Password c i l In order to start the operating system you are required to go to the area of the master network in which Opening the Master Workbook the Master Workbook is saved safely and double click on it as you would with any other file or folder The Master Workbook is password protected therefore you are required to know the default password Cupcakes in order to access the protected workbook Technical Guide Sabz Cupcakes Password Protection Box Unprotect Workbook Password Protection Error r Microsoft Excel capitalization A The password vou supplied is not correct verify that the CAPS LOCK key is off and be sure to use the correct Altering Passwords In order to change either the password for the Master Workbook from the default one you a
7. second being a series of Pivot Charts that compare numerous variables using the Pivot Table stored in the Summary worksheet The third being a series of stock checks of all fields required within the business for example Icings Sponges and Fillings However only Mrs Khan of Sabz Cupcake can access the Summary worksheet and the Stock worksheet using a password as it is password protected Invoice The Invoice worksheet is necessary because it is important for the customer to be aware of how their money was being spent The Invoice worksheet would also enable the client to thank the customer for placing their order with Sabz Cupcake The invoice ends with a message thanking the customer for placing their order with Sabz Cupcakes It was vital to add my client Mrs Khan s signature to the invoice so that it would look far more professional and it would prevent Mrs Khan from having to sign each invoice individually by hand The Print Macro button enables you to see the customers invoice in print preview The Continue Macro returns the you to the Main Menu indicating that the order process has been completed Invoice Worksheet Invoice i Sabz Cupcake S Elio eL 64 Kingston Road Print Penarth Customer Cardiff Input CF12 FPP Continue Order Input Tel 2920173529 Fax 02920173529 Order Input Customer ID Tool Web w Dabzlupeake com YAT Mo 7891234 Basket ii Emily Gauci l Kent Stree
8. ation inputted was saved under wrong headings within the Customer History Worksheet resulted in opening up Microsoft Visual Basic Editor in order to re arrange some of the cells in which the Macro was assigned to so that the customer s details were successfully pasted underneath the correct headings therefore resolving the error Fortunately no errors were found at the Summative Testing stage as they had been resolved in the previous Formative Testing stage
9. d for security purposes in other words from preventing Sabz Cupcake personnel from modifying the data on the system and also from preventing potential customers and existing customers from trying to access the system to place an order with Sabz Cupcake Specific cells within the Shared Workbook are locked from preventing individuals from modifying data When you enter the Shared Workbook they will be welcomed by the Main Menu worksheet Technical Guide Sabz Cupcakes System Configuration You are most likely to receive both workbooks the Shared as well as the Master on a Compact Disk you will be expected to copy the Shared workbook and the Master Workbook onto the area of network so that you are able to change some of the basic settings using the Default Options It is important to take note of the fact that the Compact Disk will include all relevant documents such as the User Manual and the Technical Guide Loading the System 1 Insert the Compact Disk into your Computing System 2 Goon My Computer 3 Double Click on the Compact Disk Drive The Compact Disk will be called SabzCupcakes 4 Double Click on the Folder named as Workbooks 5 Double Click on both Files Shared_Workbook and Master_Workbook 6 Right Click on one of them and select the Copy button 7 Go to the desired location in which you want to save both of the files onto the Network 8 Right Click again and select the
10. ed Resources Page 4 System Configuration Page 5 Data Validation Page 9 Formulas and Functions Page 10 Data Outputs Page 11 Print Outs Page 15 Troubleshooting and Testing Page 16 Technical Guide Sabz Cupcakes Specification Sabz Cupcake is a local business which bakes and decorates cupcakes for existing as well as new customers The owner and manager of the business Saba Khan also well known as Mrs Khan has previously kept details of the order history however she is now struggling to maintain her financial accounts and feels the need to implement a system that will do her and her business justice She feels the need for her system to be easy to use yet very professional as this will enable her to work efficiently My client Mrs Khan of Sabz Cupcake has asked for a record of all the Customers and Orders to be kept in the Customer History worksheet and the Order History worksheet The history of all the customers and orders are kept for the promotional aspects of the business Therefore everything that is inputted into the Customer Input worksheet and the Order Input 1 worksheet as well as the Order Input 2 worksheet by you it will automatically be inputted into the Customer History worksheet and the Order History worksheet Mirs Khan also wants her customers to be able to order her famous cupcakes through an online ordering form Any significant information entered into these orderi
11. formation for the customer s benefit as well as Mrs Khan s benefit 1 Invoice 2 Chart 1 Pivot Table that sorts Total Sales of Individual Fields Icing Sponge Filling Theme Quantity Size 3 Chart 2 Pivot Table that sorts Sales Performance of Personnel 4 Chart 3 Pivot Table that sorts Total Amount e 14 Print Options Printer Name i EPSON 5X430 network Status Off Line Type EPSON 5X430 Series Where 192 168 0 13 Comment E Frint to file Print range Copies Find Printer 9 all Number of copies 1 Page s From T Print what Collat i T colate Selection Entire workbook Active sheet s Table Ignore print areas Technical Guide Sabz Cupcakes Troubleshooting amp Testing Whilst doing the testing in the Project Log stumbled upon a few errors in my Shared Workbook as well as in my Master Workbook Fortunately these errors were minor and were easily fixed produced a Macro button that copied the Customers detail inputted into the Customer Input worksheet and the Customer s details into the Customer History worksheet assigned the Macro and began to test it to make sure that the Macro worked as wished went about this by entering all the relevant customer information into the Customer Input Worksheet and selecting the Save Macro Unfortunately the Macro did not seem to work as well as had hoped it would as some of the customer inform
12. k Strawberry Filling 50 49 High Stock Hazelnut 50 49 High Stock Pineapple Sponge Cake 50 49 High Stock Strawberry Cream Filling 50 46 High Stock Peanut Butter 50 48 High Stock Coconut Sponge Cake BU 49 High Stock Rasberry Filling 50 49 High Stock Caramel 50 49 High Stock Strawberry Sponge Cake 50 48 High Stock Lemon Filling 50 49 High Stock Coffee 50 49 High Stock Raspberry Sponge Cake 50 47 High Stock Apricot Filling 50 48 High Stock Order Input Invoice Customer Order BubbleGum 50 48 High Stock Cherry Sponge Cake 50 47 High Stock Stock Themes Origina Current Stock Levels Size Origina Current Stock Levels Qauntity Origina Current Stock Levels Summary Birthday 25 20 High Stock Small 25 18 High Stock Singular 25 19 High Stock New Born 25 22 High Stock Medium 25 21 High Stock Box of 2 25 19 High Stock Mothers Day 25 22 High Stock Large 25 14 High Stock Box of 4 25 16 High Stock Fathers Day 25 23 High Stock Box of 6 25 17 High Stock Valentines 25 23 High Stock Anniversary 25 22 High Stock Engagement 25 21 High Stock Wedding 25 24 High Stock New Year 25 24 High Stock Easter 25 25 High Stock Halloween 25 24 High Stock Christmas 25 24 High Stock Thanks Giving 25 24 High Stock Eid 25 23 High Stock Bar Mitzvah 25 25 High Stock Hanukkah 25 25 High Stock cg seas Diwali 25 25 High Stock Pepita 13 Technical Guide Sabz Cupcakes 201 Print Outs Sabz Cupcake system has been designed to print out the following pages of in
13. ng forms by the customers will automatically be included in the order history Mirs Khan has asked me to complete a summary using the Customer History worksheet and the Order History worksheet The Summary worksheet includes an archive of all the previous Customer data and Order data The Summary sheet is fairly significant to the owner manager of Sabz Cupcakes as it highlights the Order History and the Customer History in great detail The majority of the pivot tables displayed in the Summary are created using the Pivot Table option Mrs Khan wanted me to highlight the Sales Performance of her Personnel in the Summary worksheet so that she is able to a keep record of the sales made by her personnel consequently allowing her to compare the teams data efficiently Whilst most of the charts displayed in the Summary are be created using the Pivot Chart option Mirs Khan had requested an Invoice worksheet to be designed The invoice includes the Business details the customer s details and the order details The invoice also includes a little Thank You message directed to the customer themselves The invoice was designed in a specific way so that the owner manager of the Sabz Cupcakes will be able to easily print it out on an A4 paper and send it to the customers via post if required The Master Workbook is password protected so that only Mrs Khan is able to enter the system Protection was implemente
14. re required to open the workbook with the current password and then follow the steps listed below 1 Double Click on the Review Tab 2 Double Click on the Protect Workbook Option 3 A Unprotect Workbook Option box will appear 4 Enter your Password 5 Then Double Click on the Review Tab 6 After that Double Click on the Protect Workbook Option 7 Enter new desired Password Altering Usernames and Passwords In order to alter or modify the usernames and or passwords of the personnel that are setup on the system you are required to load up the Master Workbook and go onto the Personnel worksheet You will then see the see the following table Personnel Table Staff Forename Staff Password Default USERPass SKHAN Cupcakes SKHANCupcakes AROBERTS Receptionist AROBERTSReceptionist Haaris JHAARIS JHAARISBaker Technical Guide Sabz Cupcakes In order to enter a new user to the Personnel Worksheet you need to type the user s Forename and Surname beneath the columns provided using the following formula in the Staff Username UPPER LEFT E12 1 amp F12 you will be provided with a unique username This particular formula takes the first letter of the users Forename and the whole surname of the user and combines them both together The password is determined by the user as they have the choice the ability to choose it Personnel Table Formula View
15. t Invoice Whitchurch CF72 BFR r istorg 2042013 OA0R2013 Order Histor Cupcake Details Cupcake Type Stock White Chocolate anilla Sponge Cake Summary illi Custurd Filling Birthda Small Singular Pete DE Total Inc YAT E 6 00 Thank vou For placing your order with Sabz Cupcakes hope that vou are more than satisfied with the Final product that wou will receive in the short space time given Itis significant to remember that the Payment is due within 7 dave of this dated Letter Itis also very important For wou to keep vour Customer ID safe as this is required to track vour order Thank you Technical Guide Sabz Cupcakes Pivot Tables amp Pivot Charts All the Pivot Tables are saved onto the Summary Worksheet of the Shared Workbook These Pivot Tables were created using the information stored in the Customer History worksheet and the Order History worksheet Pivot Charts were created using the Pivot Tables stored within the Summary worksheet for the purpose of visual comparisons to be made Consequently Chart 1 Chart 2 and Chart 3 are Pivot Charts created using those Pivot Tables The Shared Workbook has the following three Pivot Tables assembled into it gt Pivot Table that sorts Total Sales of Individual Fields Icing Sponge Filling Theme Chart 1 gt Pivot Table that sorts Sales Performance of Personnel Chart 2 gt Pivot Table that sorts Total Amount
16. words whether there is a High level of stock or a Low level of stock The Stock worksheet is password protected so that only Mrs Khan can access the worksheet This is also done to prevent her personnel from modifying the significant data Stock Worksheet BEEEEECERENE SE a M7 si Icings lOrigina Current Stock Levels Sponges Origina Current Stock Levels Filling Origina Current Stock Level PEER white Chocolate 50 48 High Stock Vanilla Sponge Cake 50 49 High Stock White Chocolate Chip Filling 50 47 High Stock Input Milk Chocolate 50 49 High Stock Milk Chocolate Sponge Cake 50 49 High Stock Milk Chocolate Chip Filling 50 49 High Stock Dark Chocolate 50 47 High Stock Dark Chocolate Sponge Cake BU 46 High Stock Dark Choclate Chip Filling BU 46 High Stock Vanilla 50 47 High Stock White Chocolate Sponge Cake 50 49 High Stock Chocolate Mousse Filling 50 49 High Stock Strawberry 50 49 High Stock Coffee Sponge Cake 50 49 High Stock Caramel Filling 50 48 High Stock Order Input Rasberry 50 48 High Stock Toffee Sponge Cake 50 46 High Stock Peanut Butter Filling 50 46 High Stock Banana 50 47 High Stock Fruit Sponge Cake 50 48 High Stock Custurd Filling 50 49 High Stock Basket Orange 50 48 High Stock Lemon Sponge Cake 50 48 High Stock Cream Cheese Filling 50 48 High Stock Lemon 50 46 High Stock Orange Sponge Cake 50 48 High Stock Creme Patissiere Filling 50 48 High Stock ButterScotch 50 49 High Stock Madeira Sponge Cake 50 49 High Stoc
Download Pdf Manuals
Related Search