You are an associate manager atSeger, a larger Scandinavian retail store specializing in products for the workplace. Seger is going to put all of the items in the store on sale. You have been given the job ofchoosing acollection of items for the sale. Yourhavechoose which items provided in the inventory you want to sellby analytical methods.
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.See the area for display in Diagram 1.Profit contribution is measured as salesquantity * price * gross margin(where gross margin is measured as a percentage of sales price). There must beexactly11different products (from several categories) displayed in order to draw the interest of consumers. You are also responsible for developinga theme ofyour choice that ties the various goods together into a coherent marking theme.
1 of4
Determining theHistoricalSales of Each Product
The collection of items is provided in theAccessinventory database (Seger_Inventory) containing two tables:ProductsandSales. TheProductstable contains information about the small subset of Seger products considered to be top candidates for the sale, such as physical dimensions (in centimeters), selling price, and margin. TheProduct_IDcode 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). TheSalestable 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. Consider the three represented Lower Mainland areas only (Burnaby, Surrey, and Vancouver) 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 profit contribution. For this purpose, you must create anExcelspreadsheet showing your method for analyzing the contribution of each product.
Import the results of yourhistorical sales query into Excel and use Excel’s arithmetic capabilities tocalculate 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.
2 of4
Notes:
You can make use of 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 use duplicates of the item in the space.
Items must be contained in the display area and should not spill out past the borders.
The height of the display area is3.1 meters.
These are aisles that people will use to walk by
2.50 meters
This is a wall
This is also a wall
3.75 meters
Diagram 1
3 of4
Submission Instructions:
Submit via Canvas Assignments tool the following four files:
1. The Report (4pages)
Create a Word document (in a professionalmemoformat) addressed to your boss (in this case, your TA) outlining your analysis and design for the retail display area. Your document should contain:
Acover pagewith your name, student number, and the name of your TA.
Your 4 pagesMS Word Reportwith the following sections appropriately labeled and formatted:
1) Introduction- A brief introduction that outlines the task and your methodological approach to product selection. You also need to identify your selected theme and justify it as well as 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.
2. Your Access Database
Your databasefile 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 those required for subsequent contribution analysis.Hint: Create the required relationships first.
3. Your Excel Spreadsheet
Your spreadsheet file should contain one sheet showing your contribution calculations and 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.
Marking Criteria:
Students are expected to workindependentlyon this assignment, therefore, TAs have been instructed to award a maximum grade ofnegative 15 (i.e., -150% cheating penalty)to students whose submissions suggest that theycollaborated with otherson 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 NOT be accepted. A grade of zero will be awarded for missed deadlines.