The purpose of this assignment is to give you experience using a range of software productivity tools to address a realistic business task.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

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 Grende, a large Scandinavian retail store specializing in products for the workplace. Grende 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 13 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.

 

Determining the Historical Sales of Each Product:

The collection of items is provided in the MS Access inventory database (Grende_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 Summer. Given the seasonality of some of the products, you should look at sales from the start of May to the end of August 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!

 

Creating a Retail Display Diagram:

Finally, you should use a general-purpose graphics tool like MS Visio or diagrams.net https://app.diagrams.net/ to create a retail display diagram.

• Use the selected graphics tool to map out the layout of the retail display area. 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.

• In Visio, you can use Basic Shapes 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). If you are using the Diagrams.net, you can use General to draw simple shapes and label them as your items or the predefined objects under Floorplans.

 

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of