Through this assignment, students will gain hands-on experience with software applications essential for their future work. This assignment requires the creation of an interactive application using Microsoft Office 201 3, in particular Microsoft Access & Excel. The purpose Of this assignment is to test the students’ ability in operating and managing business data in both Access databases and Excel spreadsheets. Through the tutorials, student will be taught the basic skills to complete this assignment, and provided with practical exercises similar to those in the assignment.
Therefore, it is essential that students attend each tutorial in order to be able to successful complete the assignment. The assignment is worth 25% of your overall grade achievable in this course. This is an individual assignment and MUST be done by each student. The Access database and Excel template of the expected worksheets are available on the MAGGOT’S Blackboard site. The Excel template must be used as the basis for your workbook. You may change its visual formatting (colors, fonts, etc. ) to provide a professional finished product.
The assignment requires no prior technical background. Moreover, it is signed for business students in general to appreciate basic IS applications. Prior familiarity with the software tools could be beneficial, but will not guarantee a significant advantage or higher marks. YOUR TASK This assignment requires you to complete an Access database file using Microsoft Access 2013 and a workbook using Microsoft Excel 2013 based on the specification in this document: The Access database file should contain the required forms, queries, and reports you developed.
The Excel workbook should use the data generated from Access queries. You must include appropriate formulae and functions squired to set up the workbook In such a way that the formulae and functions are fully flexible and dynamic. CASE BACKGROUND AND SCENARIO The University of Queensland Business School Merchandise Store, one of the important branches of promoting the image of SQ Business School, has been dedicated to providing first-class products with the SUBS icon. The products cover a variety of categories, e. G. Bags, pants, shorts, clocks, etc.
VERSION: 4 FEBRUARY 2015 PAGE I I To assist the officer of Squab’s Merchandise Store in managing the store and making business decisions, you are required to manage the store’s database ND create a workbook that will allow him to record the staff/product information, analyses sales performance, as well as making strategic decisions (e. G. Free gift distribution). An access database and one workbook template have been provided on the course Blackboard site. The specific tasks are described below. MS ACCESS DATABASE CONTENT You have been provided with a Microsoft Access 201 3 Database that contains seven tables.
You will need to use the data in these tables to do the below tasks. In addition to these tasks you will need to develop a number of queries s described in the relevant section of the MS Excel Workbook Content tasks. You are required to set up the relevant relationships between the tables given. SALES ANALYSIS (2013-2014) REPORT Sing a query, create a professional report that compares the sales performance between 2013 and 2014. In particular, every year there are five regular events for selling the products. Each of the events covers a period of time.
The report should shows all of the relevant information (event name, category, product name, style/color, sales dollars in the novo respective years. For each product name and category it should also show the difference between the two years as a dollar figure and as a percentage. Each event should be summarized. The report should contain page numbers. Moreover, your name should be displayed on the left side of the report footer section in the form of “Report prepared by: Your Name”. The final report should be printed as a portrait AY PDF and included in the ZIP archive described later.
Ensure that all elements in the report are visible and positioned appropriately. EMPLOYEE REPORTING Using a query, create a professional report that shows the employee information. The report should show the employee ID, full name, birthday, phone number, address, email, job, superannuation percentage, annual salary, annual superannuation and annual remuneration. The report should contain page numbers. Moreover, your name should be displayed on the left side of the report footer section in the form of “Report prepared by: Your Name”.
The final report should be printed as a landscape AY PDF and included in the ZIP archive described later. Ensure that all elements in the report are visible and positioned appropriately. MS EXCEL WORKBOOK CONTENT The workbook provided to you contains six worksheets. A description and required tasks for each worksheet are provided below as follows. DOCUMENTATION SHEET – ASSIGNMENT OVERVIEW First enter your details: Student name and student ID. In addition, list any assumptions that you have made when you developed your assignment.
The assumptions allow examiners to understand your work in context. If you do not make any assumptions please sate “No assumptions made”. Assumptions to be considered when making must be reasonably logical. Include only the assumptions you make in the Excel component in this section. STAFF SHEET The staff sheet keeps track of the employees employed at SQ Business School Merchandise Store. The Access file contains the employee’s information. The employees’ annual salaries are determined by their functions.
Retrieve the required information from Access using the Access: Employee Reporting query. You should insert the formula that calculate the superannuation in Australian dollars. The staffs age should also be calculated. This formula should be dynamic, I. E. It should be current every time the spreadsheet is opened. In addition, the suburb where staff lives would be generated using some text or lookup functions. Based on the fact that some staff lives far away from campus, the office also decides to provide appropriate traffic allowances for them.
The information for traffic is provided on this spreadsheet. Insert an appropriate formula to VERSION: 4 FEBRUARY 2015 PAGE | 2 calculate the annual traffic allowance for each staff member. Staff members do not receive more superannuation as a result of the traffic allowance. Finally, insert the formula that calculates the total annual salary, total annual superannuation and total traffic allowance for all staff. PRODUCT SHEET The product sheet records the product information and keeps track of the sales information in the past year.
Using a query generate the relevant information required. Create effective and efficient formula to gain: total sales dollars, cost dollars, sales profits, average price, average cost, net profit, name and style/color of the product with the most sales in 2014, and the name and style/color of the product with the least sales in 2014. In particular, when you calculate the net profit, you do not need to take the cost of the ‘free gift’ (which will be issued in the Free Gift planning Sheet) and any tax into consideration.
FREE GIFT PLANNING SHEET In terms of the sales performance in 2014, the merchandise store will send a number of free gifts to the SQ Business School Alumni to promote the image of the SQ Business School and meanwhile attract more customers thereby increasing its sales profits. If the net profit of 2014 is less than $20,000, no free gift will be sent out in 2015. If the net profit of 2014 is between $20,000 and 327,500, the store will send out 1 ,500 free gifts (any combination of SUBS Hats and SUBS Water Bottles).
If the net profit of 2014 is between 27,500 and 7,500, the store will send 2,500 free gifts (any combination of SUBS Hats and JIBS Water Bottles) and up to 1000 LOLLS Rubber Wristbands (the number of bands is half the number of SUBS Hats or 1 000, whichever is lower). If the net profit Of 2014 is over 47,500, the store will send 2,500 free gifts (any combination of SUBS Hats and JIBS Water Bottles) and up to 2000 JIBS Rubber Wristbands (the number of bands is half the number of SUBS Hats or 2000, whichever is lower).
Use a data validation list populated with every 5% between 0% and 100% to achieve the equal percent (or nearest to equal, favoring cheaper costs) of total costs for the two primary gifts (hats and water bottles). In your submitted solution, please set the value such that the percent of total cost of each primary gift is 50% (or nearest achievable percentage). SALES PLANNING 201 5 SHEET [SELF-LEARNING COMPONENT] This spreadsheet is created to make the sales plan for the 2015 year.
It basically contains the sales information of 201 4, the current inventory, and the possible sales plan for 2015. Retrieve the required data from Access using a query to different products’ name, price, cost, profit per unity, current inventory, total units sales in 2014 and order quantities (consider all styles and colors to be identical). Use the number of free gifts calculated on the previous sheet and do not take tax into consideration. Create a formula to calculate the net profits in 2014 that takes the cost of the gifts into consideration.
In 2015, the officer Of SQUAB’S Merchandise Store plans to make a net profit (taking the free gift into consideration) of at least $65,000, considerably higher than the 2014 figure. You need to analyses if the current inventory allows achieving the 201 5 target net profit or at least $65,000? If the target figure cannot be achieved using the current inventory, how many packages of products need to be ordered to achieve the goal? How many of each product should be sold? Use the Solver tool to determine the amount of products to be sold based on the current inventory and fewest product package orders.
Keep all your workings in answer reports generated by Solver. Color the tabs of your most significant answer reports (final solution[s]) sheets. Place remaining answer report sheets at the end of the workbook. Each package ordered contains the number of products indicated in the Access database. Each package has a flat shipping and handling fee of $250 to be included in the 201 5 profit calculation; excess products are rolled over to the following year and their costs are included in that years profit calculation.