BUS 237 – Assignment 2: Retail Display
1 of 6
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, highmargin
products may be profitable even if they do not account for the largest number of items sold. Conversely, lowprice,
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 Astrid, a large Scandinavian retail store specializing in products for the workplace. Astrid
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. Some notes about organizing this space are also provided.
Directions:
Your job is to analyze the gross margin of a collection of goods to maximize potential profit contribution. The trick is that
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 percent. There must be exactly 15 different products (including at
least one item from each category) 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 (see Figure 2 for an example).
You should consider three things when making the choice of your items:
1) The contribution of the item to overall profitability of the space.
2) The size dimensions of the items.
3) The functionality of the space and how the item fits into the workplace theme.
BUS 237 – Assignment 2: Retail Display
2 of 6
Determining the Historical Sales of Each Product:
The collection of items is provided in the MS Access inventory database (Astrid_Inventory) containing two tables:
Products and Sales.
The Products table contains information about items available for sale. 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 table also includes product’s physical dimensions (in centimeters), selling price,
and gross margin (expressed in percent).
The Sales table provides the sales quantity (in number of units sold) by date and retail outlet — you should estimate sales
volume using this data.
• Use the query feature in 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 in the end of Fall. Given the seasonality of some of the products, you should look at sales
from the start of September to the end of December time frame only. Moreover, since customers’ preferences
change over time, you should consider only the most recent year’s sales.
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 MS Excel spreadsheet showing your method for analyzing the profit of each product.
• Export the results of your historical sales query into Excel table and calculate the profit contribution.
§ Use sort feature to rank the list of products in terms of contribution (highest profit at the top).
§ ONLY keep the records about the products you want to display in the store – remove the rest of the items
from the sheet.
§ Use conditional formatting to visually highlight the profit amounts.
§ Summarize the sales and profits and calculate the projected gross margin for all selected products.
• 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.
An example of how your spreadsheet might be organized is provided below:
Item ID Item Price Quantity Gross Margin Sale Profit
Item E Table $ 16.95 80 66% $ 1,356.00 $ 894.96
Item A Chair $ 19.95 45 52% 897.75 466.83
Item B Rug $ 3.50 130 46% 455.00 209.30
Item F Sofa $ 5.00 60 30% 300.00 90.00
Item D Shelf $ 9.95 12 47% 119.40 56.12
Item C Bowl $ 9.95 10 56% 99.50 55.72
Totals $ 3,227.65 $ 1,772.93
Overall GM 54.93%
NOTE: DO NOT SIMPLY COPY THE ABOVE EXAMPLE!
BUS 237 – Assignment 2: Retail Display
3 of 6
Creating a Retail Display Diagram:
Finally, you should use a general-purpose graphics tool like MS 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).
With shapes placed on the Visio 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).
• You can include props (e.g., computer, telephone, etc.) These items are not in your inventory, and are not for sale;
just use them as decoration that fits within the workplace theme. Do not label the prop items!
• See an example of workplace diagram in Figure 2. This is example only; it does not match assignment requirements.
• If you do not know how to do something in Visio, search for help online or use internal Help feature.
‘Bird’s Eye’ view from the top:
§ All of your chosen items must be displayed
within the borders of the display area
§ Each chosen item must be labeled
§ You can also display duplicates of the chosen
items (do not label duplicated items)
§ You can include props inside the space (do
not label those)
§ The length of two side walls are 3.5 meters
§ The length of the back wall is 4 meters
Dashed line represents
open aisle for people to
enter the display area
Solid lines
represent walls
Figure 1: Retail Display Area
BUS 237 – Assignment 2: Retail Display
4 of 6
Figure 2 shows an example of the workplace themed display area created using the office layout shapes in MS Visio:
Figure 2: Workplace Display Example
BUS 237 – Assignment 2: Retail Display
5 of 6
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 the following sections properly
labeled/formatted:
1) Cover Page with assignment info, course info, TA name, your name and student number (Insert > Cover Page).
2) Memo – A brief introduction that outlines your task and methodological approach to product selection. It is
important to describe the contribution rationale you used for your selection.
3) 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.
4) Chart (copy/paste from Excel) – A chart that compares the contribution from each item in your display.
5) 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 as “QuerySalesFall2018” 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 profit contribution calculations, plus a second sheet
showing your contribution chart. Each sheet tab should be named appropriately. All other sheets should be removed.
The spreadsheet should be well formatted and 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 on the last page for the evaluation criteria used to grade this assignment.
• Plagiarism: Students are expected to work independently on this assignment; therefore, a maximum grade of zero
(0) will be given to students whose submissions suggest that they collaborated with others on their assignments. This
applies to the original author of the work, as well as to those whose submissions resemble it!
• NO late submissions will be accepted for this assignment!
Contact your TA immediately if you are having any problems with the Canvas submission process!
BUS 237 – Assignment 2: Grading Key
6 of 6
Criteria Exemplary
(20)
Good
(15)
Acceptable
(10)
Unacceptable
(5)
Formatting
& Writing
Follows formatting
instructions; Great
vocabulary; No spelling or
grammar errors
Follows formatting
instructions; Good
vocabulary; 1-2 spelling or
grammar errors
Minor issues with formatting;
Inappropriate report
elements (cover page, memo,
table, chart, display);
Reasonable vocabulary; 3-4
spelling or grammar errors
Major issues with formatting;
Missing report elements
(cover page, memo, table,
chart, display); More than 5
spelling or grammar errors;
Over the page limit
Choice of
Items
Excellent justification;
Correct number of items
Appropriate justification;
Correct number of items
Weak justification; Correct
number of items
Missing justification AND/OR
Incorrect number of items
Database
Analysis
Appropriate query in Access Incorrect query/query name
OR relationship in Access
Incorrect query/query name
AND relationship in Access
Missing query AND/OR
relationship in Access
Missing MS Access (0 score)
Spreadsheet
Analysis
Appropriate formulas, chart
type, and formatting in Excel
Appropriate formulas and
chart type in Excel; Minor
issues with formatting (styles,
titles, labels, tabs, numbers)
Incorrect formulas and/or
chart type in Excel; Some
issues with formatting (styles,
titles, labels, tabs, numbers)
Missing formulas and/or
chart in Excel; Major issues
with formatting (styles, titles,
labels, tabs, numbers)
Missing MS Excel (0 score)
Display
Layout
Creative display layout;
Items correctly labeled and
sized in Visio
Appropriate display layout;
Minor issues with items’
labeling and/or sizing
Some issues with the display
layout; Several issues with
items’ labeling and/or sizing
Missing labels in the model;
Wrong dimensions; Not all
items displayed
Missing MS Visio (0 score)
Total Grade (out of 100) à LATE ONLINE SUBMISSION = 0 GRADE !!!
