Individual Project Instructions

OBJECTIVE

Your objective is to use analytical tools to understand and improve business performance and to present your analysis using effective data visualizations in a dashboard format.

FoodiesNW sells specialty food items to customers around the world. Increasing competition and costs are cutting into the company's profits. FoodiesNW's CEO has contacted you and asked you to design a prototype dashboard that can identify potential problems and opportunities and improve the company's performance and profitability. You will create a dashboard in Excel and then write a memo to the CEO describing your dashboard and how it can benefit the company. You will also be invited to present your dashboard to a team of expert analysts (335 students) during the first week of December.

MEMO

The main deliverable for your individual project is a memo you will write to the CEO of FoodiesNW. The memo will provide a description of the system you have developed and how it will be beneficial to the company.

Your memo should be clear and brief. The CEO has requested dashboards from more than 100 analysts, so the contents, purpose, and value of the dashboard you are proposing should be easily identifiable.

Your memo will include the following sections (in order):

Screenshot.Copy and paste a screenshot of your dashboard. If your dashboard elements don't have easily visible titles, please label them in your Word document or by hand. Please print this page in color if possible.

The problem. Describe the general problem your system is addressing. Your system should focus on a general problem, which can be broad-such as: increasing sales or reducing costs, or narrow-such as: determining which countries or products to drop. All of the reports you include in the dashboard should address different aspects of this problem.

Dashboard elements. Provide each element on your dashboard a descriptive name and a number. Aim for 3-4 items on your dashboard and list the interactive element first. For each element provide the following:

- number (1, 2, 3...)
- elementtitle
- a very brief description of content/data included in that element
- type of element (table, pivot chart, bar chart, etc.)
- brief discussion of how the element will add value by supporting decisions or process changes. "The chart adds value by showing which customers have purchased most." Is not sufficient. Think about how the information will be used and the decisions or processes that can be improved as a result.

Summary and conclusions. A brief and clear summary of how the dashboard as a whole addresses the problem you've described above, and the preliminary conclusions you have drawn from the data. Although you are an information analyst and not a food industry expert, your insights can be helpful for demonstrating the value of your dashboard as well as improving company performance.

SYSTEM SOLUTION

Relationships in Access

Upload the data from the tabs in the FoodiesNW Excel file into a database in Access. Create relationships between the files and enforce integrity. Organize the files into a normal REA format and adjust each table so all attributes are visible. Print a copy of the relationship report.

Create a dashboard

Create a dashboard in Excel that contains three or four related elements that address a likely problem or information need at FoodiesNW. To figure out the focus for your dashboard, begin by exploring the data to identify elements that would be interesting and informative.

• Sort the data. Remember that you can sort using multiple sort levels. Can you see any patterns, trends, or relationships?

• Subtotal the data using various categories. Do any large or small amounts or differences stand out?

• Graph the data using a variety of elements and chart types. sales over time to see trends. Are there any peaks and lows in sales? Is there any time of year during which sales are highest or lowest?

• Pivot the data to see totals, counts, or averages of various categories. Are there any highs? Are there any lows that need to be addressed?

• You can also try what-if analyses, to see what would happen if, for example, quantities or prices changed, or you can do statistical analysis to evaluate the significance of relationships you have identified.

Select three or four related items to include in your dashboard. These elements will most likely be charts, but you may also include nicely formatted and highlighted tables.

One of these elements should be interactive, so that the chart or table changes as the user makes changes on the screen. For example, you can include menus or slicers that changes values in a pivot chart, or you can include cells into which the user enters data manually or using a dropdown menu, you can create macros that the user can run by clicking on a button, etc. The value of the interactive element should be addressed when you describe the element in your memo, for example you could say "an interactive slicer allows the user to look at performance in different time periods".

Once you have created all your elements, paste them together on the first tab of your Excel worksheet. You can cut + paste, copy + paste, etc. Rearrange sheets by dragging tabs right and left.

The main purpose of the dashboard is to provide a visual display of quantitative information that helps the user understand the company and make decisions. Make sure your dashboard achieves that purpose.

Document the dashboard elements

For each element in your dashboard, provide a step by step description of your analysis using narratives and screenshots.

Number and title your elements as you did in the memo. For each element, begin your documentation with a screenshot of the element. For interactive elements, you may wish to include more than one image to show how your interactive feature works.

As you did in the group project, walk me through the process you used to create the element, for example by showing small screenshots of your Access queries, screenshots that show the pivot chart fields used to build the pivot tables, etc.

Also provide brief narrative descriptions of any work you did to make the element understandable and visually appealing, for example by describing how you changed labels, colors, grids, etc. Make your descriptions easy to understand and follow.

Deliverables

1) Your memo and systems solution document should be printed and brought to class during the final exam. If possible, print your dashboard in color. We will discuss these in class, and students will be asked to volunteer to show their dashboard on the screen.

2) The document containing your memo and systems solution should be saved in the Dropbox in D2L under the following name: Lastname_Firstname_Report.

3) The excel file containing your dashboard on the first tab should be saved in the Dropbox in D2L under the following name: Lastname_Firstname_Dashboard.

The company has presented you with three years' worth of sales data that includes the following:

• product category
• name of the salesperson
• country from which the order originated
• order date
• product ordered
• quantity of product ordered
• product name
• unit price for the product
• any discount given to the customer off the item price

Here are snippets of data contained in the worksheets:

Categories

Customers

Employees

Order details

Orders

Products

Shippers.

Attachment:- Project_Instructions.rar