Global Bike Case Study

Metadata

The data set for your analysis includes variables related to Global Bike's most recent sales data. You should be familiar with most of the variables, but here are some hints on this data set.

ProdCat

Product category

CatDescr

Product category description

OrderItem

Line item in the sales order. First line is "10", second is "20", and so on.

Division

"AS" = Accessories, "BI" = Bicycles

UnitOfMeasure

"ST" = stcke in German translates to piece or each in English

Years

2007 through 2019

Problem Statement

The sales team at Global Bike (GB) is concerned about new competitors in the market. A strategic planning meeting is scheduled for the first of the month. Your job is to explore the data and provide insights on GB's current customer base and sales data.

You need to complete the following analyses for the sales team. Be sure to include screen shots to support each of your answers and analyses. Bullet points or numbered items are acceptable for the report format.

1.(35%) Identify two (2) interesting and distinctly different relationships amongst three or more variables of the data set and discuss each briefly. You might hypothesize as to why the relationships are what they are. For example, "It makes sense that X is positively related to Y and Z because they are ...."

a.appropriate visualization to illustrate the relationships you discovered. Use at least three (3) variables for each relationship. Do not use the same three variables for both of your visualizations. Filter, sort and/or rank data if needed and explain what you filter if you do. You might even show the settings in your screen shot. Be sure to identify the aggregation of your measures for each chart, perhaps in the title.

b.Explain to Nina why each of these visualizations is important to her team.

2.(30%) Nina Kane and her colleagues in Germany believe that Global Bike has 4 groups of customers with distinct characteristics. She also knows that customers have changed over the past few years and so any analysis that you provide on the groups should be focused on the newest data available. cluster analysis for Nina. What can you tell Nina about the 4 customer groups? Use the following as a guide to help you answer this question.

a.Define how you clustered the transactions for Nina. What variables did you use? Did you have any preprocessors? If so, what were they?

b.What can you tell Nina about the relationships of the members of each cluster and the clusters' relationships to each other? Perhaps a simple way to is to explain the Summary and/or the Cluster Representations produced by the algorithm. Include screen shots of the cluster results.

c.Explain other characteristics of the clusters by creating properly structured visualizations, (one visualization each), of the following. Hint: be sure to use the cluster number as one of the dimensions in your visualization. Be sure to identify the aggregation of your measures for each chart, perhaps in the title.

(1)Sales revenues by region and product category

(2)Profitability (gross margin ratio) by customer and division

3.(20%) The sales team is also interested in sales trends overall. forecast of sales for the next 48 months. Hint: use Triple Exponential Smoothing instead of R-Triple Exponential Smoothing for this forecast for "better" results.

a.Explain what value of alpha, beta, and gamma you chose and why you chose what you did.

b.Is the forecast reliable? Why or why not?

Hints

geo hierarchy with targeted dimension of City and Geographical level City.

Avoid using column or bar charts for every answer.

Grading Parameters

Did you choose the right model(s) for the question asked?

oDid you get the correct answers to specific questions?

Were you able to accurately explain the results of your model(s)?

explanations show an understanding of the data in the data set?

Did you choose the best visualizations for the data represented?

oDid you choose the correct variables aggregated at the appropriate level for the question at hand?

oAre your visualizations properly formatted, (i.e., titles and legends), and easily interpreted?

(15%) business report writing format: The report is presented in a professional looking document, using informative headings and figures/tables where appropriate and demonstrating technical writing skills.

Based on the case and previous calculations, please answer the following short answer questions. Note: Your instructor will need to manually grade these questions. 1. Refer to Exhibit :3 [Financial Breakdown). What were the largest and smallest divisions by net sales in 2017? Identify the one most important division in terms of the proportionate net earnings for the company 2. Based on the nancials presented in Exhibits 1 and 2. how would you describe P&G's nancial position in the years 2016 and 2017? Do you think the company ought to make any major changes to improve its nancial outlook? What changes would you suggest? P6-3 (L02,4) (Analysis of Alternatives) Assume that Wal-Mart Stores, Inc. has decided to surface and maintain for 10 years a vacant lot next to one of its stores to serve as a parking lot for customers. Management is considering the following bids involv- ing two different qualities of surfacing for a parking area of 12,000 square yards. Bid A: A surface that costs $5.75 per square yard to install. This surface will have to be replaced at the end of 5 years. The annual maintenance cost on this surface is estimated at 25 cents per square yard for each year except the last year of its service. The replacement surface will be similar to the initial surface. Bid B: A surface that costs $10.50 per square yard to install. This surface has a probable useful life of 10 years and will require annual maintenance in each year except the last year, at an estimated cost of 9 cents per square yard.IOrs A and B will be able to perform the required year-end maintenance, that Ellison's cost of funds is Who, and the machine will be purchased on January 1. from which vendor should the press be purchased? P6-9 (L02,4) (Analysis of Business Problems) James Kirk is a financial executive with McDowell Enterprises. Although James Kirk has not had any formal training in finance or accounting, he has a "good sense" for numbers and has helped the company grow from a very small company ($500,000 sales) to a large operation ($45 million in sales). With the business growing steadily, however, the company needs to make a number of difficult financial decisions in which James Kirk feels a little "over his head." He therefore has decided to hire a new employee with "numbers" expertise to help him. As a basis for determining whom to employ, he has decided to ask each prospective employee to prepare answers to questions relating to the following situations he has encountered recently. Here are the questions. (a) In 2016, McDowell Enterprises negotiated and closed a long-term lease contract for newly constructed truck terminals and freight storage facilities. The buildings were constructed on land owned by the company. On January 1, 2017, McDowell took possession of the leased property. The 20-year lease is effective for the period January 1, 2017, through December 31, 2036. Advance rental payments of $800,000 are payable to the lessor (owner of facilities) on January 1 of each of the first 10 years of the lease term. Advance payments of $400,000 are due on January 1 for each of the last 10 years of the lease term. McDowell has an option to purchase all the leased facilities for $1 on December 31, 2036. At the time the lease was negotiated, the fair value of the truck terminals and freight storage facilities was approximately $7,200,000. If the company had borrowed the money to purchase the facilities, it would have had to pay 10% interest. Should the company have purchased rather than leased the facilities? (b) Last year the company exchanged a piece of land for a non-interest-bearing note. The note is to be paid at the rate of $15,000 per year for 9 years, beginning one year from the date of disposal of the land. An appropriate rate of interest for the note was 11%. At the time the land was originally purchased, it cost $90,000. What is the fair value of the note? (c) The company has always followed the policy to take any cash discounts on goods purchased. Recently, the company purchased a large amount of raw materials at a price of $800,000 with terms 1/10, n/30 on which it took the discount