Words: 2647
Learning Outcomes: In conclusion, students should be able to: Create a professional-looking documentation with sufficient coverage. Prepare a financial accounting spreadsheet with appropriate formulas, functions, formatting and charts. To create tables in a database, forms to enter data into the tables and a variety of queries to be performed based on the table. To create and design a set of presentation slides based on the facts gathered from a research. Understand the different applications of Microsoft Office towards easing problem solving methods in habitual corporate situations.
QUESTION 1- SPREADSHEET You have just started a small business of your own, selling three different brands f stationery. You have hired three employees, each in charge of selling a specific brand. Using the spreadsheet skills which you have acquired from your Practical IT Skills (PITS) class, you have decided to create a spreadsheet template. The template should only contain permanent data, functions, calculation, and information. Any data to be entered by the users based on monthly or day-to- day transaction are considered non-permanent (i. E. Items bought from suppliers, items sold, etc… , and therefore should not be saved within the template. For every new month, the template is opened once and saved as a new workbook tit a name corresponding to the month and year of the current time. The spreadsheet would help you keep track of your inventory, sales, and employees’ salary plus the commission every month. The inventory sheet allows you to keep track of the number of items bought from the suppliers, the number of items sold and the number of items left to be sold. It also keeps record of the cost and selling price for each type of item.
The sales sheet allows your employees to enter the number of items they have sold, which would automatically update the inventory sheet. The data update is remissive to the each branded items only by the corresponding employees to avoid any uncalled-for mishaps. The employees’ salary sheet allows you to keep track of each employee’s salary every month, along with their commission (5% per item sold). You have decided that the target sales revenue per employee is ARM 1500 per month. If an employee has achieved the target, the commission rate is increased to 15% per item sold after the targeted amount.
This worksheet keeps track of the items sold and the amount of revenue received by each employee, and automatically calculates the commission together with the monthly salary. In addition, you want to analyze the sales revenue coming in, the cost for items and labor, and the profit expected (excluding the rent for the shop lot, utility bills, etc… ). The following are the things you should consider in your spreadsheet: 1 . The main page consisting of your company name, company address, company contact details, company logo, and hyperlinks that would allow users to navigate to all the different sheets.
All of the different sheets should also have a hyperlink that allows users to navigate back to the main page. [1 mark] 2. The inventory sheet, which should consist of the following (but not limited to): . Product ID that is uniquely assigned to all items b. Product description that describes the item c. Number of each item bought from suppliers d. Initial cost per item bought from suppliers e. Selling price of each item (40% in addition to the corresponding initial cost per item) f. Number of each item sold g.
Number of on hand for each item that are in stock [3 marks] 3. The sales sheet(s), which should consist of the following (but not limited to): a. Product ID, product description, selling price, and the number of on hand should be displayed exactly as in the inventory (no modification of data is allowed here) b. Number of item sold that WOUld automatically update the inventors number of item sold (access given only to the employee with the corresponding brand) **Hint: Protect all non-modified access **Hint: Use passwords to enable access to the employees [5 marks] 4.
The employees’ salary sheet should consist of the following (but not limited to): a. Staffs ID that is uniquely assigned to all employees b. Staffs name c. Staffs fix monthly salary d. Staffs commission i. If the target sales revenue is less than RAMMER, the commission is 5% from the selling price of the item sold ii. If the target sales revenue is equal to or greater Han ARM 1500, the items sold after that will be counted with a commission of 15% from the selling price. E. Staffs performance i. If the target sales revenue is equal to or greater than ARM 1500, the performance is “Good”. i. If the target sales revenue is less than RMI 500 but greater than ROOM, the performance is “Above average”. Iii. If the target sales revenue is less than RMI 000 but greater than ARMOR, the performance is “Satisfactory’. Iv. If the target sales revenue is less than ARMOR, the performance is “Below average”. [4 marks] 5. The analysis sheet should consist of the following (but not limited to): a. The ales revenue for each brand b. The total sales revenue for all brands c. The cost for each brand d. The total cost for all brands e.
The salary plus commission for each employee f. The total salary plus commission for all employees g. The profit (excluding the rent for the shop lot, utility bills, etc… ) h. Appropriate charts for analysis-purpose (minimum 3 different charts) [5 marks] Recalling to what you have learnt in your PITS class, you realize the significance of having absolute and/or mixed referencing, conditional formatting, and data validation in your spreadsheet. You also understand that using an automated broadsheet allows user-friendly operation and reduces redundant actions.
You want what is best for your business; hence you decided to implement them wherever applicable. [5 marks] Note that you are to create a template spreadsheet to be used monthly; therefore be sure only PERMANENT data, functions, calculation, and information are within the spreadsheet when you save it as a template. Use the name of your company followed by the word template as the template name. To test whether the functionality of the template is appropriate, use the template to create a new workbook. Enter appropriate data into the spreadsheet, and save. Use the current month and year as the name of the workbook. 2 marks] [Total: 25 marks] QUESTION 2- WORD PROCESSOR On the first working day for each month, you will deliver a letter-like report to each of your employee, reflecting on their performance for the previous month. You want the letter-like report to look as professional as possible. Create the letter-like report with the use of mail merge functions, consisting of the following: 1 . The letterhead of you company (which include the company name, address, contact details, and logo) and a watermark 2. The issuing date 3. The Staff name and ID 4. The report containing: a. The target sales revenue b.
The actual sales revenue accumulated by the employee c. The employee’s work performance (below average, satisfactory, above average, etc… ) d. Explanation on the commission rate e. The amount of commission earned by the employee f. Final remarks for the employee to take note on (disciplinary words, encouraging words, complementing words, etc… ) 5. The signatory and position 6. Footer (which include company’s slogan or image of company’s product) [5 marks] While in PITS class, you have learnt that documentation is very important as it explains the essential elements of your research or product.
In this case, you want to create a documentation that explains the spreadsheet you have created in detail (i. E. What, how, and why you have implemented such functions), with proper and relevant corrections that would enhance your explanation. You decided to write the documentation while keeping in mind that your younger sibling (who ONLY has the BASIC knowledge of working on spreadsheet) will help you to maintain your spreadsheet, and therefore, the content should be written clearly with proper explanation on the procedures, functions, and formulae found in the spreadsheet.
As your younger sibling will also help you to deliver he employees’ performance, you should also provide a clear explanation and procedure on using the mail merge function. Although for now, your younger sibling is the only potential audience of your documents, you have decided to go with a formal documentation as it reflects professionalism and business-oriented that may save you time in the future. Therefore you should consider the following: 1. A proper and appropriate cover page 2. An acknowledgment to those you would give an appreciation to, associating with the given and performed tasks 3.
A table of contents that allows the readers o quickly navigate to a specific section or chapter 4. An abstract that explain the coverage of the document 5. An introduction that explains the needs for the documentation 6. The body contents with proper citations and corrections: a. Explanation that provides Walworth for the spreadsheet b. Explanation on the functions and formulae for used in EACH worksheet (i. E. What each function of the formulae is for, why the function/formula is used, what each formula does or calculates, etc… ) c.
Explanation on absolute and/or mixed referencing, conditional formatting, and data validation found in the spreadsheet (i. . What they are for, why they are implemented, what steps are involved in developing them, etc… ) d. Explanation on the charts (i. E. What they are for, why they are created, what steps are involved in developing them, etc… ) e. Explanation on mail merge (i. E. What it is, what steps are involved to accomplish it, etc… ) 7. A conclusion which summarizes the covered contents, noting of the implications of results, as well as providing recommendations 8.
A reference list consisting of all sources which you have referred to in order to make your spreadsheet and documentation successful 9. The appendices consisting of the letter-like reports, and any other relevant materials You should also pay close attention to the following formats: 1 . Typeface: Times New Roman 2. Font size: 12 points 3. Font color: Black (Blue is acceptable for headings) 4. Alignment: Justify 5. Line spacing: 1. 5 [20 marks] QUESTION 3- DATABASE You have now acquired the knowledge of creating the database and manipulating the data through queries.
By gaining the database skills, you envisioned great possibilities and your curiosity pushed you to build a database system closely resembled to your spreadsheet, Your database should consist of ALL of the objects you have learnt in your PITS class: 1 . Properly defined tables in which the data is stored 2. Properly designed forms (which is used as an interface of the tables for user to enter the data) with function buttons [5 marks] 3. Correctly defined queries which allow accurate data selection and manipulation [5 marks] 4.
Professionally designed reports specifically made for the queries [5 marks] 5. Organized switchboard which allow the users to navigate the objects within the database, with the help of macros [5 marks] Create your queries based on the following statements: 1 . Display all product ID and item description from the inventory table, grouped by the brand name. 2. Display product ID, product description and product brand name where the selling price is greater than ARM 100. 3. Calculate and display the total sales revenue for each brand name. 4.
Create a new price list where the selling price for each product is discounted by 5%. Display the product ID, product description, and the new price list. 5. Calculate the staffs’ commission earnings based on a fixed commission rate of 5%. 6. Display the staff ID, staff name, and the total earnings (salary plus fixed commission rate f 5%) where the total earnings are less than ARM 1000. 7. Calculate the staffs’ commission earnings based on the 5% of their sales revenue when the sales revenue is less than ARM 1500, and 15% commission rate for each items sold after hitting the target of ARM 1500. . Display the employee ID and employee name with the highest sales revenue. 9. Display staff ID, staff name, and sales revenue for staffs whose first name starts with the letter within the range of M-S. 10. Determine the most selling item. Display the product ID, product description, selling price, and staff name. For each of the objects created, be sure to name them appropriately. QUESTION 4- SLIDES You are glad that you have taken the PITS module as it has helped you a lot in developing your own customized systems for your company.
However, towards the end of the semester, you realize that there are so many assignment deadlines approaching; hence, you are unable to prepare a documentation for the database. Worry that you may forget the important elements and procedures if you were to do the documentation last (after all the other deadlines are met), you decided to ask your younger sibling to prepare it. You will present the database objects and steps to create each of the objects to our younger sibling as clearly as possible so that your younger sibling can write a good documentation on it.
By now, you have already learnt how to prepare the presentation slides. Because you want to maintain your professional image, you want to create a good and effective presentation slides that can capture your younger siblings attention, and at the same time, provide good guidelines and explanation to each objects. The effectiveness of your presentation depends on how well you structured your slides. Therefore, be sure that the slides you have created can guide you in delivering the information. [15 marks] You should include the following in your slides: 1. Title page 2. Introduction 3.
Main menu (hyperlinks that will allow you to quickly navigate to a specific object) 4. Body contents (each with hyperlink to navigate back to the main menu): a. Tables in design view (Not in dataset view) b. Forms in form view (Not in design view) c. Queries in design view (Not in dataset view) d. Reports in report view (Not in design view) e. Switchboard manager and form view 5. Conclusion 6. Reference Since you want to captivate the attention of your younger sibling, you should consider implementing the following into your slides: 1. Automated animations and key-controlled animation (whichever is most appropriate) 2.
Transition SUBMISSION Submit the following on the assigned date. Please bind the printed documents to ensure that all pages are intact and in proper order, and attach the softly together. 1. Hardcopy (printed documents) must include: a. The documentation written for the spreadsheet and mail merge, inclusive of the letter-like reports provided in the appendix b. The presentation slides (6 slides per page) 2. Softly (in CD) must include: a. The spreadsheet template named after the company b. The workbook named after the month and year . The letter-like report d. The database for mail merge e. The documentation f.
The database g. The slides Assessment Criteria Distinction Demonstrate a comprehensive understanding of the features and functions in Ms Office with detailed evidence displayed during presentation. No errors, such as in formulas, were encountered in the documentation with regard to the usage of the package. High level of effort performed, exceptional and thorough knowledge and understanding displayed with regard to facilities and services of the application software. The assignment presented in a professional manner, sing not only the basic of the application packages but also various advanced features.
In order to obtain a grade at this level, the student should be able to present oral arguments in clear and cogent manner as well as answer most questions confidently. Credit Adequate effort conducted with fair detail of evidence presented. Moderate level of understanding and knowledge displayed with regards to the application software. Good level of documentation presented. Some level of understanding was evident in the documentation and presentation. The assignment should be in professional manner, using some basic applications of the application cakes.