Simon Fraser BUS 237- Assignment 2 Retail Display

BUS 237 – Assignment 2: Retail DisplayDue Date/Time: Check Canvas Assignments for the due date/time for your section! Objective and BackgroundThe purpose of this assignment is to give you experience using a range of software productivity tools toaddress a realistic business task. The task is to create a visual layout for a retail display area. You will useMicrosoft Access to get sales information for a range of products, Microsoft Excel to calculate theprofitability of each product, and Microsoft Visio to create a layout for the products given a fixed amountof display space. Finally, you will use Microsoft Word to bring all the elements of your analysis togetherinto a coherent and professional report.Any retail business must display goods in order to sell them. The art in developing effective retailmanagement is utilizing the space in the store in order to display items that provide the largestcontribution to overall profit. Retailers attempt to draw maximum attention to their most profitableproducts.Profitability is a function of the total profit earned on the sale of each item and the number sold. Thus,high-price, high-margin products may be profitable even if they do not account for the largest number ofitems sold. Conversely, low-price, low-margin products may be profitable if the products achieve highsales volumes. An effective retail manager must not only be good at choosing profitable items, he or shemust also be creative in displaying these goods as an understandable theme so that customers want tobuy the goods. The most effective retail managers therefore carefully balance profitability and displaydesign. ScenarioYou are an associate manager at Seger, a larger Scandinavian retail store specializing in products for theworkplace. Seger is going to put all of the items in the store on sale. You have been given the job ofcreating a display for a collection of items of your own choosing for the sale. Your job is first to choosewhich items provided in the inventory you want to sell. Once you have chosen the items, you need toposition them so that they fit into the retail display area provided. The dimensions of the display area areprovided in Figure 1 below. Some notes about organizing this space are also provided. DirectionsYour job is to analyze profitability of a collection of goods to maximize potential contribution. There is alimited space to display the goods, so not all of the goods available can (or should) be chosen. Profitcontribution is measured as sales quantity * price * gross margin (where gross margin is measured as apercentage of sales price). There must be exactly 11 different products (from several categories)displayed in order to draw the interest of consumers. You are also responsible for developing a theme of1 of 5 your choice that ties the various goods together into a coherent display. The only theme that you are NOTallowed to select is the “Work at Home” theme.You should make three considerations as you choose your items:1) The contribution of the item to overall profitability.2) The functionality of the display area and how the item fits into your selected theme.3) The size dimensions of the item. Determining the Historical Sales of Each ProductThe collection of items is provided in the Access inventory database (Seger_Inventory) containing twotables: Products and Sales. The Products table contains information about the small subset of Segerproducts considered to be top candidates for the sale, such as physical dimensions (in centimeters), sellingprice, and margin. The Product_ID code reflects the category of the inventory item (100 = desks & tables,200 = chairs, 300 = storage & shelves, 400 = boxes, 500 = lamps, 600 = vases, 700 = decoration, 800 = rugs,900 = beds). The Sales table provides sales (in number of units sold) by date and retail outlet — youshould estimate sales volume using this data. Use the query feature in Microsoft Access to join the tables and calculate aggregate sales for eachproduct. Consider the three represented Lower Mainland areas only (Burnaby, Surrey, andVancouver) since regional sales volume is very important. Calculating the Retailing Contribution of Each Product:The boss has asked you to select the products for the retail display based on their potential profitcontribution. For this purpose, you must create an Excel spreadsheet showing your method for analyzingthe contribution of each product. Import the results of your historical sales query into Excel and use Excel’s arithmetic capabilities tocalculate the necessary measures and ratios….e Excel’s sort feature to rank the list of products in terms of contribution (highest profit at the top).Only keep the products you want to display in the store – remove the rest of the inventory productsfrom the sheet.Create an appropriate chart in Excel that shows the amount of profit generated by each product. Usedata labels to highlight the markers on the chart so you know which marker belongs to which product. Creating a Retail Display Diagram:Finally, you should use a general-purpose graphics tool like Microsoft Visio to create a retail displaydiagram. Use Visio to map out the layout of the retail display area. Visio permits you to enter the exactdimensions for shapes so your diagram should give a precise indication of how the space is used.Make sure to label each product.You should have no product extending beyond the boundaries of the displayed area as defined inFigure 1. 2 of 5 You can use Basic Shapes in Visio to draw simple shapes (e.g., circles, squares, etc.) and label themas your items. You can also use predefined Office Layout shapes in Visio (e.g., office accessories,equipment, and furniture).Figure 1: Display Area (Looking down– “Bird’s Eye” View) These are aisles that people willuse to walk by Notes: 2.50meters This isa wall This is also a wall 3 of 5 3.75meters You can make use of shelvingand other props in the space.You should assume that otherplaces in the store can storestock for actual sales.All of your chosen items mustbe displayed in the space. Youare welcome to use duplicatesof the item in the space.Items must be contained in thedisplay area and should notspill out past the borders.The height of the display areais 3.1 meters. Some notes about use of MS Visio for this assignment:Placing shapes on the canvas:Select Basic Diagram to draw simple shapes and label them as your items.Search for Office Layout to start with a template that includes office related shapes Online help: Visio basicsGetting Started with Visio, A beginner’s guide to Visio, Basic Tasks in Visio, andCreate an office layout diagram With shapes placed on the canvas: Set item dimensions by right-clicking on the shape and selecting View > Size & Position Window.Make sure you use proper units (cm = centimeters) for dimensions. In this window you can specify the exact measures for each item (to match the items measurements statedin the database). Since we are looking at the display from the top, you should use the Width and Depthdimensions from the Products table to set the Width and Height (the depth dimension of the objectbecomes height dimension if you are viewing that object from the top) values in Visio. Label the items by double-clicking on shape and typing the item’s label (e.g., product name) There are other ways to label your items, such as using the color legend, text labels, etc. You can alsocolor the items and use a legend to better display the stacked items. Click on each item and from themain menu select Format > Fill then select a Color (you can also use a toolbar).4 of 5 Submission Instructions:Submit via Canvas Assignments tool the following four files:1. The Report (5 pages)Create a Word document (in a professional memo format) addressed to your boss (in this case, your TA)outlining your analysis and design for the retail display area. Your document should contain: A cover page with your name, student number, and the name of your TA.Your 4 pages MS Word Report with the following sections appropriately labeled and formatted:1) Introduction – A brief introduction that outlines the task and your methodological approach toproduct selection. You also need to identify your selected theme and justify it as well as describethe contribution rationale you used for your selection.2) Table (copy/paste from Excel) – A list of the products you have selected for display. This list shouldshow the critical elements of your contribution calculations and include only the products youselected for display. Your design of the spreadsheet is an important part of the assignment.3) Chart (copy/paste from Excel) – A chart that compares the contribution from each item in yourdisplay.4) Display diagram (copy/paste from Visio) – A diagram showing your retail display area. 2. Your Access DatabaseYour database file should contain a single saved query named “ProfitBC2017” plus the original two tablesprovided. The query should join the necessary tables and apply criteria to limit the results to thoserequired for subsequent contribution analysis. Hint: Create the required relationships first. 3. Your Excel SpreadsheetYour spreadsheet file should contain one sheet showing your contribution calculations and a second sheetshowing your contribution chart. Each sheet should be named appropriately. All other sheets should beremoved. The spreadsheet should be well laid out and well documented. 4. Your Visio DiagramYour diagram file should contain a visual representation of the retail display area. The layout of productsand their labels should be clearly marked. All dimensions should be to scale.Marking Criteria: See the assignment grading key for the evaluation criteria used to grade this assignment.Students are expected to work independently on this assignment, therefore, TAs have beeninstructed to award a maximum grade of negative 15 (i.e., -150% cheating penalty) to studentswhose submissions suggest that they collaborated with others on their assignments (report andsupporting files). This applies to the original author of the work, as well as to those whosesubmissions resemble it!Late submissions will NOT be accepted. A grade of zero will be awarded for missed deadlines. Contact your TA immediately if you are having any problems with the Canvas submission process!5 of 5