CMIS 567-73C - Exercise 2a and 2b - Vy T

docx

School

Southern Illinois University, Edwardsville *

*We aren’t endorsed by this school

Course

CMIS-567

Subject

Business

Date

Feb 20, 2024

Type

docx

Pages

Uploaded by enovy200

1 Exercise 2a and 2b – GBI Data Analysis and Reports Vy Tuyet Truong Southern Illinois University Edwardsville CMIS 567-73C: Data Analytics Project for Business Dr. Joseph Vithayathil February 4, 2024

2 Table of Contents I. GBI Reports 3 Executive Summary 3 Report 1 – Bike Sales Report for Alster Cycling in 2011 4 Report 2 – Seasonality 6 Report 3 – Average Days Between Quotation and Payment 9 II. ERPSIM Analysis 10 Executive Summary 10 Question 1 11 Question 2 11 Question 3 12 Question 4 13 Question 5 15 Question 6 16 Question 7 18 Question 8 20 Question 9 21 Question 10 22

3 I. GBI Reports Executive Summary To evaluate the sales data of the Global Bike Inc. enterprise in the US and Germany, Hanna Ortmann, Alex Kuhn, and Andrea Marino requested three different reports. Report 1 was done according to Hannah Ortmann’s request. This report presents an overview of the bike sales performance of a customer, Alster Cycling, in 2011. The result was found through the use of SAP Predictive Analytics with the data source of the Global Bike Inc. (GBI) data set. The analysis involved four specific components from the data set, including the quantity of the specified products, the “Bikes” in the material group dimension, the customer “Alster Cycling” in the customer dimension, and the year 2011 in the year dimension. Alex Kuhn would like to know more about the seasonality in their materials; thus, report 2 represents an analysis of materials that do not exhibit seasonality for Global Bikes Inc. (GBI). The objective is to provide a comprehensive overview of revenue performance in USD, trends, and factors influencing the sales or usage patterns of all products. For this analysis, the GBI data set, which includes the Revenue measure, Month dimension, and Material Master Description dimension, was used. SAP Predictive Analytics was used to create the necessary data visualizations to display the seasonality of each product more effectively. When it comes to the report 3, Andrea Marino and Hanna Ortmann request to analyze the average number of days between quotation and payment for various sales for eight areas in the US and Germany, excluding the results of the year 2013. For this analysis, the GBI data set, which includes the Quote Date dimension, Payment Receipt Date dimension, and Sales Area Description dimension, was used. The objective is to evaluate the efficiency of the sales process in terms of the turnaround time and identify areas for improvement. Similar to the previous reports, SAP Predictive Analytics was utilized to visualize the results.

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

4 Report 1 – Bike Sales Report for Alster Cycling in 2011 Data Preparations A column chart (Figure 1) was created to find the number of bikes sold to this customer in 2011, with the Quantity measure on the y-axis, the Material Group Description and Customer Name dimensions on the Legend Color, and the Year dimension on the x-axis. Filtering was applied to the Material Group Description to select “Finished Bikes” and to Customer Name to select customer Alster Cycling. This process was also applied to the Year to select the year 2011. For this analysis, another column chart (Figure 2) was also created to display the sales in quantity to Alster Cycling from 2006 to 2013. The Quantity measure was plotted on the y-axis, the Year dimension on the x-axis, and the Customer Name and Material Group Description dimensions on the Legend Color. The analysis focuses specifically on sales to Alster Cycling for "Finished Bikes," so the chart is filtered only to show data for that customer and material group. Results and Recommendations According to Figure 1, the number of bikes sold in 2011 to customer “Alster Cycling” was 343. However, based on Figure 2, the sales for 2011 to this customer were not the highest among all the years. The data shows that there was a slight increase from 2010 to 2012. However, there was a significant decline after 2012. This detail embodies the importance of further strengthening the relationship with this customer as well as different R&D to meet this customer’s demand in order to improve sales for this particular customer.

5 Figure 1: The Number of Bikes Sold to Alster Cycling in 2011 Figure 2: The Number of Bikes Sold to Alster Cycling from 2006 to 2013

6 Report 2 – Seasonality Data Preparations For the purpose of the charts' clarity, the products were separated into three different line charts. Figure 3 includes the Air Pump, Deluxe Touring Bike (black), Deluxe Touring Bike (red), Deluxe Touring Bike (silver), Elbow Pads, and First Aid Kit. Figure 4 includes the Knee Pads, Men's Off-Road Bike, Off Road Helmet, Professional Touring Bike (black), Professional Touring Bike (red), and Professional Touring Bike (silver). Figure 5 includes the Repair Kit, Road Helmet, T-shirt, Water Bottle, Water Bottle Cage, and Women's Off-Road Bike. Additionally, despite Alex Kuhn's request to show the charts' data labels, this makes them unreadable. Thus, we decided not to show the labels on the charts. Line charts were created to visualize the seasonality of each product sold by Global Bike Inc. (GBI). The Revenue USD measure was plotted on the y-axis, while the Month dimension was on the x-axis. The Material Master Description dimension was also added to the Trellis-Rows. To better illustrate the seasonality, the average of the Revenue measure was also calculated. Results and Recommendations Based on Figure 3, 4, and 5, the results show that Deluxe Touring Bike (black, red, and silver), Men’s Off-Road Bike, Professional Touring Bike (black, red, and silver), and Women’s Off-Road Bike are displaying seasonal trends. Products that did not show seasonality are Air Pump, Elbow Pads, First Aid Kit, Knee Pads, Off Road Helmet, Repair Kit, Road Helmet, T-shirt, Watter Bottle, and Water Bottle Cage. According to this information, it has been observed that the sales of finished bikes are influenced by seasonal trends, whereas the sales of bike-related accessories do not follow the same pattern. The sales of bikes reach their highest point between February and May, while they hit the lowest point between July and September. Based on this seasonal trend, GBI can make informed decisions to allocate their resources towards the products that exhibit seasonality during their peak season. This will help improve sales forecasts and enhance customer satisfaction.

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

7 Figure 3: GBI Products’ Revenues in the 12-Month Period (A) Figure 4: GBI Products’ Revenues in the 12-Month Period (B)

8 Figure 5: GBI Products’ Revenues in the 12-Month Period (C)

9 Report 3 – Average Days Between Quotation and Payment Data Preparations A column chart (Figure 6) was created, with the Days Diff between Quote & Payment measure on the y-axis and the Sales Area Description dimension on the Legend Color. The Days Diff between Quote & Payment was calculated based on the difference between Quote Date and Payment Receipt Date. It was then created to be a measure. Since the average number of days was expected, the Days Diff between Quote & Payment measure’s aggregation function was changed to Average instead of Sum. Additionally, the results of the year 2013 were also excluded from the data. Results and Recommendations Based on Figure 6, it can be seen that Germany North-Wholesale for Bicycles provides its customers with the longest time period of approximately 44 days to complete their payments. On the other hand, Germany South-Wholesale for Accessories offers a shorter time span of approximately 40 days for customers to make their payments. Overall, GBI has a relatively similar timespan, around 40 days, between Quote Day and Payment Receipt Day for all sales areas. These time periods are important as they play a crucial role in determining the efficiency of cash flow management and customer satisfaction. Longer payment periods tend to lead to increased customer satisfaction, but at the same time, if the payment waiting period is too long, it could negatively affect the cash flow of the business. Therefore, GBI needs to strike a balance between these two factors for each market segment, as this balance is essential for the overall success of the business. Figure 6: Average Time Span in Days between Quotation and Payment Excluding 2013

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

10 II. ERPSIM Analysis Executive summary Many courses at SAP University Alliances member schools use ERP Simulation Game (ERPSIM) to introduce students to the role of integrated business processes using a business simulation game. In this simulation, multiple teams would be competing against each other with the goal of strategizing and running their company for profit maximization. In order to understand more about the performance of each team participating in this simulation game, various data visualizations were created to better display these teams’ decisions and performance in regard to their revenues in USD, pricing for each product, distribution channels, and market share. For the purpose of this analysis, the ERPSIM game data was used with the help of the SAP Predictive Analytics tool to generate data visualizations to display the performance of ten teams. Here, several adjustments were made to fix errors made by SAP Predictive Analytics during the data importing process. Specifically, the values in Round were incorrectly identified as measures; thus, they were changed to numeric values. Additionally, the aggregation of the Price measure was originally shown as Sum, which was changed to Max.

11 Question 1: Which team had the highest revenue? What was the revenue? A column chart (Figure 7) was created with the Revenue measure on the y-axis and the Team dimension on the Legend Color. The result shows that team RR had the highest revenue of 32,297,797.51 USD. Team NN is in second place with a revenue of 27,520,449.32 USD. With the revenue differences coming up to approximately 4.78 million USD, team RR demonstrates a better strategy in terms of running their company than team NN. This proves that team RR may be the winner if they manage to keep their costs low, as the objective of this simulation is to maximize profits. Figure 7: All Teams’ Revenues in USD Question 2: What product had the highest revenue? What was the revenue? A column chart (Figure 8) was created with the Revenue measure on the y-axis and the Product dimension on the Legend Color. According to this chart, the product named 500g Nut Muesli generated the highest revenue of 24,445,956.53 USD, while the 1kg Mixed Fruit Muesli generated only 7,344,220.80 USD in revenue. These results can be explained by two possible reasons. Firstly, it could be because customers prefer 500g Nut Muesli over the other products. Secondly, the cost of producing 1kg Mixed Fruit Muesli is the highest among all products, which significantly impacts its revenue, resulting in it having the lowest revenue. This

12 reflects the importance of maintaining a low cost associated with each product’s production while ensuring that it meets the customers’ expectations. Figure 8: All Products’ Revenues in USD Question 3: Display the trend of revenue over rounds for each team. A line chart (Figure 9) was created with the Revenue in USD measure on the y-axis, the Round dimension on the x-axis, and the Team dimension on the Legend Color. Most teams in Figure 9 show similar increasing trends over the eight rounds, except for Team NN and RR. Team NN's revenue exhibited significant changes throughout the rounds. Notably, in Round 4, team NN's revenue had a substantial increase, making it the team with the highest revenue for that round. Unfortunately, its revenue also dropped significantly in Round 5, causing it to drop down to 5th place. Nevertheless, its revenue experienced a steady increase, ultimately placing it in 2nd place in the last round. On the other hand, team RR faced a decline in round 2, but they were able to keep a steady increase from round 3 to round 8, making it the winner in terms of revenue in this simulation game. Despite the good performance from team RR, not all teams

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

13 managed the same thing. Team LL, in particular, had the lowest revenue of 674,104.07 USD in the last round, officially placing them in last place among all the teams. Although team LL showed significant improvement in rounds 2 and 6, they failed to maintain a consistent performance throughout the game. Figure 9: All Team’s Revenue in USD throughout The Eight Rounds Question 4: What is the market share of each team by product? A column chart (Figure 10) was created to illustrate the market share of each team by product. The Percentage – Revenue measure was plotted on the y-axis while the Product dimension was on the x-axis and the Team dimension on the Legend Color. The Percentage – Revenue measure was created by adding the Revenue measure on the y-axis first. Then, the percentage calculation was added to the Revenue measure while the Revenue measure was removed from the chart, creating Figure 10.

14 Based on Figure 10, the teams with the highest market shares in each product category are shown in the table below: Product Team with the highest market share 1kg Blueberry Muesli Team RR 1kg Mixed Fruit Muesli Team NN 1kg Nut Muesli Team PP 1kg Original Muesli Team RR 1kg Raisin Muesli Team SS 1 kg Strawberry Muesli Team RR 500g Blueberry Muesli Team OO 500g Mixed Fruit Muesli Team OO 500g Nut Muesli Team NN 500g Original Muesli Team TT 500g Raisin Muesli Team NN 500g Strawberry Muesli Team OO The table above shows that teams RR, NN, and OO repeatedly dominated multiple different product categories. This displays a pattern that if a team has effective strategies, this allows them to have adequate resources and capability to dominate more than one market. Figure 10: Percentage – Revenue of Each Team by Product

15 Question 5: Are there any products that do not sell in specific distribution channels? A heat map (Figure 11) was created with the Revenue measure on the Area Color, the Product dimension on the Area Name, and Distribution Channel on the Area Name 2. The heat map was able to show how the distribution channel system works and which products were carried by each channel. Specifically, Distribution Channel 10 does not sell the following products: 500g Blueberry Muesli, 500g Mixed Fruit Muesli, 500g Nut Muesli, 500g Original Muesli, 500g Raisin Muesli, and 500g Strawberry Muesli. Different from this channel, Distribution Channel 14 does not sell 1kg Blueberry Muesli, 1kg Mixed Fruit Muesli, 1kg Nut Muesli, 1kg Original Muesli, 1kg Raisin Muesli, and 1kg Strawberry Muesli. On the other hand, Distribution Channel 12 sells all products. From here, we know that Channel 10 specializes in only carrying 1kg of Muesli while Channel 14 specializes in selling 500g of Muesli. Thus, when deciding on the distribution channel, each team needs to pay attention to their products’ weight in order to find the appropriate distribution channel. Figure 11: The Products’ Revenues in USD and Their Distribution Channels

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

16 Question 6: What were the highest prices paid for various products per team? Which team sold the most expensive Muesli? Two column charts (Figures 12 and 13) were created to visualize the prices set for various products per team. The Price measure was plotted on the y-axis, the Product dimension on the x-axis, and the Team dimension on the Trellis-Rows. The Team dimension was then filtered to select teams KK, LL, MM, NN, and OO to create Figure 12. This step was repeated to re-select teams PP, QQ, RR, SS, and TT to create Figure 13. Additionally, another column chart (Figure 14) was created to display the team with the most expensive Muesli, with the Price measure – filtered to select top 1 – on the y-axis, Product dimension on the x-axis, and Team dimension on the Legend Color. According to Figures 12 and 13, the highest prices paid for each product per team were organized in the table below: Team Highest Price (USD) Product Team KK $7.50 1kg Nut Muesli Team LL $7.50 1kg Blueberry Muesli Team MM $7.82 1kg Blueberry Muesli Team NN $6.75 1kg Mixed Fruit Muesli Team OO $7.02 500g Strawberry Muesli Team PP $8.00 1 kg Raisin Muesli Team QQ $7.80 1kg Strawberry Muesli Team RR $7.20 1 kg Mixed Fruit Muesli Team SS $6.50 500 Blueberry Muesli 500 Strawberry Muesli Team TT $7.25 1kg Raisin Muesli From this table, it shows that despite the product and the product size, most teams selected relatively similar pricing for their products. This might affect the teams’ revenues as many customers were expecting to pay less for smaller products.

17 When it comes to the most expensive Muesli, Figure 14 shows that team PP sold the most expensive Muesli for 8.00 USD for their 1kg Raisin Muesli. However, a higher price does not necessarily mean that their pricing strategy was bad. There are other factors that can determine the success of team PP's 1kg Raisin Muesli in the market. For example, the uniqueness of the product and its marketing strategy can also play a significant role. Figure 12: Each Product’s Price Per Team (A) Figure 13: Each Product’s Price Per Team (B)

18 Figure 14: Team With the Highest Price Question 7: Which team sold the most quantity of muesli? For that team, what was the most sold product? A tree map (Figure 15) was created to visualize the quantity of products sold by each team. This map was created by plotting the Quantity measure on the Area Weight and the Team dimension on the Area Name. Another tree map (Figure 16) was created to display the most sold product for the team with the highest total quantity. Figure 16 was plotted with the Quantity measure on the Area Weight and the Team and Product dimensions on the Area Name. In this map, the team was filtered to select the highest- ranking team in terms of quantity. Based on Figure 15, team RR sold the most Muesli products with 7,646,580 in quantity, while team NN followed closely after team RR with 7,215,500 in quantity sold. As determined throughout the analysis, team RR earned the highest revenues, and team NN was placed second in terms of revenue ranking. From the quantity sold demonstrated in Figure 15, we can determine that one reason RR has the highest revenues was that they sold the most products out of all the teams. According to Figure 16, the most-sold product by team RR was the 1kg Original Muesli, with 2,066,277 sold in quantity. This was followed closely by the 1kg Blueberry Muesli with 1,923,745 and 1kg Strawberry Muesli with 1,764,790 sold in

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

19 quantity. This shows that team RR was able to implement effective strategies for their products, leading to higher sales in quantity compared to other teams. Figure 15: Quantity Sold by Team Figure 16: Quantity Sold by Team RR and Product Category

20 Question 8: What three products have high price and high revenue? A bubble chart was created by plotting the Price measure on the x-axis, the Quantity measure on the y-axis, the Revenue in USD measure on the Bubble Width, and the Product dimension on the Legend Color. Figure 17 was created by filtering the top three products with the highest prices, while Figure 18 was created by selecting the top three products with the highest revenues. Based on the information in Figure 17, the most expensive products were 1kg Raisin Muesli, priced at 8.00 USD, followed by 1kg Blueberry Muesli at 7.82 USD, and 1kg Strawberry Muesli at 7.80 USD. On the other hand, looking at the data in Figure 18, the products with the highest revenues were 500g Nut Muesli, generating 24,445,956.53 USD, followed by 1kg Original Muesli with 22,859,203.17 USD and 1kg Nut Muesli with 22,419,498.79 USD in revenue. According to the results depicted in these two charts, it is evident that products with the highest prices do not necessarily generate the highest revenues. Therefore, it is crucial to determine pricing strategies based on factors such as the product's characteristics, production costs, and customer expectations.

21 Figure 17: Top 3 in Price by Product Figure 18: Top 3 in Revenue by Product Question 9: Show the days on which individual teams did not have any revenue. What team made the highest revenue on a single day (which round)?

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

22 A heat map (Figure 19) was used to visualize the revenues earned by each team on each day of the eight-round simulation. The Revenue measure was assigned to the Area Color, while the Round and Day Dimensions were assigned to the Area Name and the Team dimension to the Area Name 2. Figure 20 was obtained by filtering the data to show the top three highest revenues earned on a single day. According to Figure 19, the gray area is the days on which individual teams did not have any revenue. Based on Figure 20, it was observed that team NN generated the highest revenue of 496,639.90 USD on a single day (day 18) in round 4. This finding was also consistent with the trend of revenue over the rounds for each team, where team NN displayed the highest peak in revenues during round 4. However, this does not mean that team NN has the highest overall revenues. Figure 19: Revenues by Round, Day, and Team

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

23 Figure 20: Top Three Revenue by Round, Day, and Team Question 10: What product on what day and round brought the highest revenue (for which team)? A column chart was created, which had the Revenue measure on the y-axis and the Round Day dimensions on the x-axis. The Product and Team dimensions were added to the Legend Color. After that, the data was filtered to select only the product with the highest revenues, which resulted in the creation of Figure 21. This figure clearly indicates that 1kg Nut Muesli on day 8 in round 2 by team LL brought the highest revenue of 244,904.40 USD. According to the earlier revenue trend assessment of each team, team LL occupied the last place in the final round. However, the revenue of team LL reached its peak in rounds 2 and 6, based on Figure 9 data. From this information, it can be inferred that team LL's strategies for round 2 were successful, but they either failed to maintain their plan or changed it without taking other factors into account, resulting in their poor performance and last-place finish in the final round.

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

24 Figure 21: Top 1 Revenue by Day, Round, Product, and Team

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

CMIS 567-73C - Exercise 2a and 2b - Vy T

Related Documents