Tableau_Dashboard_Exercise_3511
pdf
keyboard_arrow_up
School
Dalhousie University *
*We aren’t endorsed by this school
Course
3511
Subject
Communications
Date
Apr 3, 2024
Type
Pages
8
Uploaded by JudgePony3794
COMM3511 || Management Information Systems || Gonzalez 1 of 8 Dashboard Making in Tableau: ERPsim Game Data (Part 2) Purpose With the previous Tableau assignment, we have learned how to conduct some visualizations in Tableau using the ERPsim game data. In the current exercise, you will learn how to create a simple dashboard
for logistics dairy company in the ERPsim game. Your business assignment in this exercise is to create a series of Key Performance Indicators (KPIs)
and design a dashboard for the top performing team in the simulation. Dashboards: Dashboards create a link between business strategy and operations. They provide indicators to monitor performance against objectives and provide visibility into business trends and opportunities for the company. In business, it is a decision-making tool for all hierarchical levels. It helps to synthesize information and to present it concisely using indicators, targets, and alerts. As with an automobile dashboard, it gives the driver specific information to assist him in his duties and to inform him or her of probable emergencies, a manager dashboard provides with the information needed to achieve the company’s objectives, to take action, when the situation demands it and to adjust the operations in order to achieve the targeted performances. Dashboards in the Logistic Dairy Company - ERPsim game: In the ERPsim Dairy simulation, virtual companies sell all the same products in a market with identical conditions for each company. Only a few elements allow companies to differentiate themselves from their competitors: • Their central warehouse procurement strategy; • Their distribution strategy towards regional warehouses; • Their pricing strategy. The content material of this week will provide more information about how to create KPIs and better visualizations. This exercise includes three parts. Part I guides on how to prepare the dataset before working on it. Part II
guides you on how to create one Key Performance Indicator (KPI) using conditional formatting in Tableau. Part II
presents
further instructions for developing more KPIs.
COMM3511 || Management Information Systems || Gonzalez 2 of 8 PART I: Preparing the data The ERPsim game data used for this exercise was derived from the “
Diary
Logistics Game
” where each company was engaged in procurement, distribution and sales of 6 different dairy products (e.g., milk, cream, yoghurt, butter, cheese, and ice cream) in a German-based market divided in 3 regions (North, West, and South). The game was played over several rounds (up to 8 rounds of 10 virtual days each). The procurement and sales processes of this game was similar to the water distribution game you played in class. That is, each team had to forecast their sales using the created Planned Indep. Requ. (MD61) in the system, execute MRP, and convert Purchase Requisitions into Purchase Orders. In regard to the sales process, each identified different pricing strategies during the game but were not promoting their products with marketing expenses. As per the distribution process, in the Logistics game, each team had to manage transportation and warehousing costs in order to maximize profit. The costs were 1,000 Euros per purchase order and 1,000 per regional transfer from the company’s main warehouse to the regional warehouses (North, West, South). In addition to these costs, there was a base capacity (sum of all products stored in all storage locations) of 4,000 units and extra daily fee for each additional 1,000 boxes of 300 Euros. So, each team/company needed to manage these costs and forecast appropriately to reduce the shortage of products in the stores. These game details are shown in Figure 1 and a graphical representation of the cash-to-cash cycle of the game is shown in Figure 2 at the end of this document. Th complete game data for this game includes 14 Tables. When you import the data to Tableau, these are the tables you will see (Figure alongside). In the previous lab exercise, you used only the Sales table from this data set; but now you have access to all data generated from that game. An overview of the most commonly used measures/dimensions for the Sales table are: •
Sim_Round = Round •
Sim_Date = Day •
Material Description = Product •
Storage Location = Warehouse (i.e. North, South, East) •
Quantity = The sales order quantity purchased by the customer, or # of product sold •
Net_Value = The revenue generated (Quantity sold of each item * unit price) •
Cost = The cost of the product sold based on its valuation •
Net Price = the average price sold •
Contribution_Margin = Net_Value - Cost For further understanding of the attributes of the data, please refer to the ERPsim Game Data Directory
file provided with the materials for this exercise. This directory will help you understand the data and create more meaningful measures for your KPIs.
COMM3511 || Management Information Systems || Gonzalez 3 of 8 Since the goal is to create different KPIs and visualizations that could help a company (Team) perform better during the game, we need to add and connect the tables for that analysis. For this exercise, we are going to add only 4 tables: Current
Inventory
, Current_Inventory_KPI,
Sales,
and Company Valuation
. You can add more if desired. 1. Drag and drop
Current
Inventory
to the blank space; then drag the Inventory_KPI. 2. To successfully connect the tables, find the matching field between the tables, select the dimension Material Description
in both tables. The tables should be successfully connected (Figure alongside) 3. Drag and connect Sales to the
Current
Inventory table
. You can select Material Description
for both tables again in this connection. 4. Finally, drag and connect Company Valuation
to the Sales
table. You can select the dimension Sales Organization
for Sales
and Company Code
for Company Valuation
. These dimensions are the same. The connections of the tables might look like the Figure below. However, you can also connect the Tables differently. Because the goal of this exercise is to create KPIs and a Dashboard for the top performing team in the simulation; let’s first found out which that team was. 5. Create simple bar chart for the Company Valuation per Team. Double click on the Company Valuation measure and the dimension Company Code
from the Company Valuation table. Who was the top performing team? 8. Click on Sheet 1, now you can start your KPIs and visualizations.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
COMM3511 || Management Information Systems || Gonzalez 4 of 8 PART II: Creating a KPI with Conditional Formatting KPI stands for Key Performance Indicator. These are measurable units or indicators within your data set(s) that are useful tools of measuring the success, or shortcomings of a particular project or areas affecting the data set. A Dashboard is a collection of KPIs to help analyse and monitor specific areas in an organization. The best way to think of it, is in terms of a car dashboard. You have a speedometer, your fuel gauge and engine indicators. These put together, help augment your abilities to drive your car as well as to monitor the conditions of the car. Examples of common KPIs could be customer satisfaction scores, whether they have increased, decreased or remained the same, stock orders to regional stores to see if products are selling out, or gathering dust on shells. In the context of the ERPsim Dairy game, each team (company) buys boxes of packaged dairy products in six varieties and resells them to retailers. Each team operates in a competitive market. One of the company’s key business objectives is to have sufficient inventory of all products at any time in all three geographical areas. The specific objective can be formulated as follows: The strategy is to replenish every three days. The objective is to ensure that the company has enough inventories to meet the demand for at least two days for all products in all areas. The company uses an integrated information system, SAP ERP. This system provides access to inventory levels in real time as a simple table. The proposed new system aims to include, in a dashboard, information to facilitate inventory management. The system should: •
Help forecast the demand for the next five days of each product in each region in order to set inventory targets; •
Provide alerts, at any given time, when inventory levels are dangerously low; •
Allow the team/company to retrospectively evaluate their inventory management. Given the above, the company needs to identify KPIs for this purpose. Below, two indicators are suggested and developed using Tableau. Strategy and objective:
The strategy is to replenish every three days. The objective is to ensure that the company has enough inventories to meet the demands for at least two days for all products in all areas. Indicator Targets How it should be used Calculate for each product and area the average sales for a 5-
day period. Use the calculated value to set the inventory targets for each product and area. Calculate the current inventory levels for each product as measured in days of average sales. Alerts
- Green, if more than 5 days. Yellow, if between 2 and 5 days. Red, if less than 2 days Track the current levels of inventories. Initiate replenishment if less than 2 days.
COMM3511 || Management Information Systems || Gonzalez 5 of 8 First KPI: We need to calculate for each product and area the average sales for 5-day period. The 5-day period is used in order to comply with the replenishment strategy of 3 days, taking into account shipping delays in supplying the central warehouse, delivery times to regional warehouses, and the time to refresh data used for the ERPsim dashboard in real time. 1. Find the table of Current Inventory KPI
on your left Data panel and create a calculated field from the Quantity Sold
measure using the formula (5*([
Quantity Sold
]/[
Nb_Steps Available
])), name this measure “
Sales Forecast
” (Figure alongside). 2. Double click on Sales Forecast
, Material Description
and Storage Location,
then select the Table visualization from the Show Me
panel. 3. Click swap rows and column
from the menu to change the format of the table 4. Drag and drop Storage Location
to Filters
and select only 03N, 03S, 03W. 5. Drag and drop the dimension Plant
to Filters
and select team II
. 6. Change title to “
Sales Forecast (5 days average sales)”.
The final table should look like the Figure alongside. This table can be used as a KPI during the ERPsim simulation game
to set the inventory targets for each product and area. Second KPI: We now will create alerts in our second KPI using conditional formatting. Conditional formatting is used to compare actual inventory levels with established targets. You might want the system to alert you when inventory levels are low. In this case, we will use a green colour to show when inventories exceed the equivalent of five days of sales (your forecast); a yellow colour to show when inventories are equivalent to two to five days; and a red colour to show when inventory levels represent less than two days of sales. We will use the same table as in the previous indicator, the Current Inventory KPI that provides data on Average Sales
and current inventory levels for all products and storage locations. 1. Create a calculated field from the Quantity Sold
measure using the formula [
Quantity Sold
]/[
Nb_Steps Available
], name this measure “
Average daily sales
” . 2. Create a calculated field from the Current Inventory
measure using the formula [
Current Inventory
]/[
Average daily sales] (new measure created in the previous step
),
name this measure “
Inventory in daily sales
” .
COMM3511 || Management Information Systems || Gonzalez 6 of 8 Now we want to create the alerts for the levels of inventory using conditional formatting. To do so: 3. Create a calculated field from Average daily sales measure, and enter the following condition in the pop-
up window (see Figure alongside) Now, let’s visualize this indicator. 4. Drag Inventory in daily sales
to Rows; Storage Location
and Material Description
to Columns, and select the stacked bars
as graph from the Show Me
panel. 5. Drag and drop Storage Location
to Filters
and select 03N, 03S, 03W
. 6. Drag and drop Plant
to Filters
and select plant II 7. Drag Inventory levels Alert
to the Color
in the Marks panel. Then click on Color to Edit the colors in accordance to the alert, green, red and yellow. 8. Click swap rows and column
from the menu to pivot the axis. 8. Change title to “Inventory in daily sales” The final table should look like the Figure below. This graph can be used as a KPI during the ERPsim simulation game to track the current levels of inventories and alert you when you should initiate replenishment. This is your 2
nd
KPI J
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
COMM3511 || Management Information Systems || Gonzalez 7 of 8 PART III: Additional Data Visualizations 1.
Following the rationale of the previous KPIs, create TWO additional KPIs that could for example assist other roles in the company (e.g., price/marketing manager; general managers/CEO, etc) Suggestions could be: •
A price manager might be interested in ensuring a target contribution percentage across all products (e.g., from the Sales
table, you can track the Contribution Margin Pct
and set Alerts to go above or below certain target using conditional formatting explained above) If you choose this path, make sure you justify your target levels with a simple reasoning. •
A general manger or CEO might be interested in ensuring a positive company valuation through the game. (e.g., a simple trend graph of the company valuation per round could be enough) 2.
Create a Dashboard with the four KPIs in this exercise. Make sure the formatting is appropriate and complete (e.g., title, labels and colors when needed) At the end of this exercise, you need to submit TWO files: One PDF file which includes the screenshots of the visualizations done earlier and explanation of your KPIs. One Tableau file (file name.twbx) with your work for part II and III of this assignment. Please save your files as “Lastname_Tableau”. (e.g., Gonzalez_Tableau.pdf & Gonzalez_Tableau.twbx) and submit it in the designated dropbox in BrightSpace. If you work on the Tableau application at the computer labs, you need to save your work on Tableau Public. Create an account on Tableau Public and save it there. Then, you can download your work as a PDF and Tableau workbook format. NOTE:
This is an INDIVIDUAL assignment, you can consult with classmates, but make sure you submit your own creation. Rubric Criteria Level 1 Level 2 Level 3 (full points) KPIs – Follow along & New (2 points) First two KPIs have some errors. Second two KPIs are incomplete. Key formatting is missing First two KPIs are complete. Second two KPIs are complete but lack of rationale/justification. Key formatting is fine First two KPIs are complete. Second two KPIs are excellent, dynamic, well explained. Key formatting is excellent. Dashboard Presentation (0.5 points) Static, poor use of visual and attributes, plain. Somewhat interactive, good use of visuals attributes, somewhat diverse. Interactive, Excellent visual attributes, diverse. END OF DOCUMENT
COMM3511 || Management Information Systems || Gonzalez 8 of 8 Figure 2. Cash-to-cash Figure 1. Logistics Game Details