BUS 237 - Assignment 2: Retail Display | Complete Solution

BUS 237 - Assignment 2: Retail Display
1 of 4
Due Date/Time: Check Canvas Assignments for the due date/time for your section!
Objective and Background:
The purpose of this assignment is to give you experience using a range of software productivity tools to address a realistic business task. The task is to create a visual layout for a retail display area. You will use Microsoft Access to get sales information for a range of products, Microsoft Excel to calculate the profitability of each product, and Microsoft Visio to create a layout for the products given a fixed amount of display space. Finally, you will use Microsoft Word to bring all the elements of your analysis together into a coherent and professional report.
Any retail business must display goods in order to sell them. The art in developing effective retail management is utilizing the space in the store in order to display items that provide the largest contribution to overall profit. Retailers attempt to draw maximum attention to their most profitable products.
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 of items sold. Conversely, low-price, low-margin products may be profitable if the products achieve high sales volumes. An effective retail manager must not only be good at choosing profitable items, he or she must also be creative in displaying these goods as an understandable theme so that customers want to buy the goods. The most effective retail managers therefore carefully balance profitability and display design.
Scenario:
You are an associate manager at Teka, a larger Norwegian retail store specializing in products for the workplace. Teka is going to put all of the items in the store on sale. You have been given the job of creating a display for a collection of items of your own choosing for the sale. Your job is first to choose which items provided in the inventory you want to sell. Once you have chosen the items, you need to position them so that they fit into the retail display area provided. The dimensions of the display area are provided in Figure 1 below. Some notes about organizing this space are also provided.
Directions:
Your job is to analyze profitability of a collection of goods to maximize potential contribution. There is a limited space to display the goods, so not all of the goods available can (or should) be chosen. Profit contribution is measured as sales quantity * price * gross margin (where gross margin is measured as a percentage 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 workplace theme that ties the various goods together into a coherent display.
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 the workplace theme.
3) The size dimensions of the item.
BUS 237 - Assignment 2: Retail Display
2 of 4
Determining the Historical Sales of Each Product:
The collection of items is provided in the Access inventory database (Teka Inventory) containing two tables: Products and Sales. The Products table contains information about the small subset of Teka products considered to be top candidates for the sale, such as physical dimensions (in centimeters), selling price, 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 — you should estimate sales volume using this data.
 Use the query feature in Microsoft Access to join the tables and calculate aggregate sales for each product. Do not worry about the location of the sales—you are interested in total sales only.
 You are running the sale from the start of January to the end of April. Given the seasonality of some of the products, you should look only at sales in the same time frame. Moreover, since decorating trends come and go, you should consider only the most recent year’s sales only.
Calculating the Retailing Contribution of Each Product:
The boss has asked you to select the products for the retail display based on their potential profit contribution. For this purpose, you must create an Excel spreadsheet showing your method for analyzing the contribution of each product.
 Import the results of your historical sales query into Excel and use Excel’s arithmetic capabilities to calculate the necessary measures and ratios.
 Use 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 products from the sheet.
 Create an appropriate chart in Excel that shows the amount of profit generated by each product. Use data 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 display diagram.
 Use Visio to map out the layout of the retail display area. Visio permits you to enter the exact dimensions 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 in Figure 1.
 You can use Basic Shapes in Visio to draw simple shapes (e.g., circles, squares, etc.) and label them as your items. You can also use predefined Office Layout shapes in Visio (e.g., office accessories, equipment, and furniture).
Figure 1: Retail Display Area (‘Bird's Eye’ top view)
You can use shelving and other props in the space
You should assume that other places in the store can store stock for actual sales
All of your chosen items must be displayed in the space
You are welcome to display duplicates of the items
Items must be contained in the display area and should not spill out past the borders
The height of the display area is 3.0 meters
Each walled side is 4.0 meters long
Solid lines represent walls
Dashed line represents open space for customers to enter the display area
4.0 m
4.0 m
BUS 237 - Assignment 2: Retail Display
3 of 4
Some notes about use of MS Visio for this assignment:
Placing shapes on the canvas:
To access Basic Shapes to draw simple shapes and label them as your items, select the following category in Visio: Block Diagram > Basic Diagram > Basic Shapes
Online help: Visio basics
http://office.microsoft.com/en-ca/visio-help/basic-tasks-in-visio-2010-HA101835290.aspx
To access Office Shapes to draw complex shapes and label them as your items, select the following category in Visio: Building Plan > Office Layout (Metric) > then select any of the Office shapes (Accessories, Equipment, and Furniture)
Online lesson: A quick introduction to office layouts
http://office.microsoft.com/en-us/visio-help/a-quick-introduction-to-office-layouts-RZ001131613.aspx?CTT=1
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 stated in the database). Since we are looking at the display from the top, you should use the Width and Depth dimensions from the Products table to set the Width and Height (the depth dimension of the object becomes 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 also color the items and use a legend to better display the stacked items. Click on each item and from the main menu select Format > Fill then select a Color (you can also use a toolbar).
BUS 237 - Assignment 2: Retail Display
4 of 4
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 to product selection. It is important to describe the 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 should show the critical elements of your contribution calculations and include only the products you selected 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 your display.
4) Display diagram (copy/paste from Visio) – A diagram showing your retail display area.
2. Your Access Database
Your database file should contain a single saved query named “QuerySalesSpring2016” plus the original two tables provided. The query should join the necessary tables and apply criteria to limit the results to those required for subsequent contribution analysis.
3. Your Excel Spreadsheet
Your spreadsheet file should contain one sheet showing your contribution calculations plus a second sheet showing your contribution chart. Each sheet should be named appropriately. All other sheets should be removed. The spreadsheet should be well laid out and well documented so that your boss can make sense of it.
4. Your Visio Diagram
Your diagram file should contain a visual representation of the retail display area. The layout of products and 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 been instructed to award a maximum grade of negative 15 (i.e., -150% cheating penalty) to students whose submissions suggest that they collaborated with others on their assignments (report and supporting files). This applies to the original author of the work, as well as to those whose submissions resemble it!
 Late submissions will be penalized at the rate of 1 mark per day (-10%).
 Missing assignments will result in an incomplete grade (N). This automatically becomes a failed grade (F) at the end of the semester, if the requirements (i.e., submitting the assignment) are not satisfied.
Contact your TA immediately if you are having any problems with the Canvas submission process!