logistics analytics

1

Case Study – Part I

Richard has been hired by Buckeye Bookstore as a demand planner at the beginning of 2020. His first job is to forecast the sales for the first 4 months in 2020. The historical sales records in the past two years are listed below:

Month Sales in $1000s Month Sales in $1000s 2018 2019

Jan 27 Jan 27.4 Feb 29.5 Feb 37.3 Mar 38.1 Mar 44.6 Apr 43.6 Apr 43.8

May 25.9 May 31.2 Jun 32.4 Jun 39.7 Jul 39.9 Jul 46.7

Aug 42.3 Aug 48.1 Sept 24.7 Sep 32.1 Oct 36.1 Oct 40.7 Nov 43.4 Nov 46.6 Dec 44.7 Dec 47.7

The store manager has told Richard that the sales of the bookstore cycles every four months. Richard also finds a sticky note attached on the wall scribbled “Winter’s, 𝛼𝛼 = 𝛾𝛾 = 0.5”, which was left by the ex-planner who resigned last month. So, Richard first adopts the ex-planner’s approach to forecast the monthly sales of Jan.2020 - Apr.2020.

However, after exploring the historical data, Richard notices that the sales patterns include not only seasonality but also an increasing trend. He remembers what he learned from his logistics analytics course and thus uses a more comprehensive approach than the previous planner, which is appropriate to the sales records by considering the seasonality and the trend. After comparing some forecasting performance measurements, he is satisfied with this new forecast approach and immediately adds another line on that note “𝛽𝛽 = 0.15”.

Richard shows the results to his manager and suggests that they update the forecast method. But his manager asks: “Why don’t you also try linear regression approach using the time index as a predictor”? Although Richard is pretty sure simple linear regression is not a good fit here, he listens to his manager and fits the linear model. He uses the entire past 24 months data as input to forecast the monthly sales of Jan.2020 - Apr.2020. After showing the manager the fitted line of each method, Richard also shows the manager some error measurements of the three forecasting approaches to support his recommendation.

2

Instructions:

1. Based on the historical data in the past two years, please use the forecast method that the previous planner adopted to forecast monthly sales during Jan.2020 - Apr.2020.

2. Based on the historical data in the past two years, please use the forecast method that Richard suggested to forecast monthly sales during Jan.2020 - Apr.2020.

3. Based on the historical data in the past two years, please use simple linear regression that the manager asked to forecast monthly sales during Jan.2020 - Apr.2020.

4. Plot a line graph for each method that you used.

5. Use appropriate error measurements to support Richard’s recommendation.

Hints:

1. As Richard is forecasting four periods ahead, m = 4 in the Winter’s method. Based on the historical data, a season includes 4 time periods.

2. Initialize the Level for the first four periods:

𝐿𝐿1 = 𝐿𝐿2 = 𝐿𝐿3 = 𝐿𝐿4 = 𝐴𝐴𝐴𝐴𝐴𝐴𝐴𝐴𝐴𝐴𝐴𝐴𝐴𝐴 𝑠𝑠𝐴𝐴𝑠𝑠𝐴𝐴𝑠𝑠 𝑖𝑖𝑖𝑖 𝑡𝑡ℎ𝐴𝐴 𝑓𝑓𝑖𝑖𝐴𝐴𝑠𝑠𝑡𝑡 4 𝑚𝑚𝑚𝑚𝑖𝑖𝑡𝑡ℎ𝑠𝑠 𝑖𝑖𝑖𝑖 2018.

3. Using the decomposition approach to calculate Seasonal Index, such as 𝑆𝑆1 = 𝐷𝐷1 𝐿𝐿1

, ….

4. Initialize the Trend for the first period only: 𝑇𝑇1 = 1.

5. When you run the simple linear regression for case study part I, make sure to code your "date" column as 1, 2, 3, 4, ..., 27, 28, instead of using a date format, such as 201801, 201802, ..., 202003, 202004, which will disrupt your independent variable's structure and your coefficients.

6. To make the error measurements of different approaches comparable, don’t include the first 4 periods (Jan. 2018 – Apr. 2018) when you calculate the errors.

7. Using software packages will significantly save your time in repeated calculated. In our lectures, we have introduced how to solve the problems in this case using MS Excel. However, you are allowed to use other software that you prefer.

3

Case Study – Part II

Buckeye Bookstore has partnered with several large bookstores in Ohio. One of its partners in Cleveland, Cavs Bookstore, is facing an inventory shortage for the best-sold business analytics books. Cavs Bookstore requests Buckeye Bookstore to send them four types of best-sold business analytics books. Cavs Bookstore will cover all the related transportation costs and will pay Buckeye Bookstore 115% of the total value (which is procurement cost here). Cindy, a supply chain planner, is in charge of the transportation plan. She has selected four books. Data about weight, size, and value per box are listed below.

Book Name Weight/Box (lbs.) Size/Box (Cubic Meter) Value/Box ($1000) Finance Analytics 40 0.15 3

Logistics Analytics 50 0.1 5 Marketing Analytics 60 0.2 4.5

Operations Analytics 70 0.25 2.5

Cindy sent the above table to Cavs Bookstore and has got it confirmed. Cavs Bookstore also told Cindy that they need no fewer than 10 boxes for each book. Cavs Bookstore will use a truck that allows a maximum cargo load of 50000 pounds and a maximum capacity of 8 cubic meters. The truck driver will only accept full-box books. The supply chain manager has told Cindy, she should maximize the total value that Buckeye Bookstore can earn from these shipments.

For Cindy, dealing with these kinds of transportation-related optimization tasks is her main duty. Meanwhile, as the supply chain department is a cross-functional team, she also helps other departments to solve their problems. One of the projects that Cindy worked with the marketing department is about advertising Buckeye Bookstore. The marketing team is considering to post 5 advertisements on different channels. Each channel cannot post more than 3 advertisements, and it’s OK not to post any advertisement on a channel. The below table predicts the potential profits can be generated by posting various numbers of advertisements on different channels. The goal for the marketing department is to strategically post advertisements on different channels to maximize potential profits.

Total Profits ($ million) No. of Advertisement Facebook YouTube Twitter

0 0 0 0 1 1 0 -1 2 3 2 2 3 3 3 4

4

Instructions:

1. Formulate both problems using appropriate mathematical models.

2. Solve the models to find optimal solutions and provide suggestions to Buckeye Bookstore.

Hints:

1. Use 𝑥𝑥𝑖𝑖 to represent boxs of book i loaded on the truck. (i = 1 represents Finance Analytics; i = 2 represents Logistics Analytics; i = 3 represents Marketing Analytics; i = 4 represents Operations Analytics)

2. Use 𝑥𝑥𝑖𝑖𝑖𝑖 to represent the number of advertisements i posted on the channel j. (j = 1 represents Facebook; j = 2 represents YouTube; j = 3 represents Twitter)

3. Using software packages will significantly save your time in solving optimization problems. In our lectures, we have introduced how to solve the problems in this case using MS Excel. However, you are allowed to use other software that you prefer.

5

Overall Instructions of Case Study

1. You may work in teams and upload only one write-up for the team. Students in one group will receive the same grades in this case study. Please try to be active in group meetings. Verified negative complaints received from team members will influence your participation score.

2. The due date of the case study is Dec. 04, 2020, at 11:59 pm. Late submission (after 11:59 pm) will receive a grade of 0.

3. Although there are two parts, your reports should read as a consolidated piece. The managerial report should include 5 parts – title page, table of contents, executive summary, the main body of the report, and technical appendix, with font size 12 and double line space. The report should be no more than 15 pages (excluding the technical appendix). Although there are no page limits for the technical appendix, you should provide sufficient technical details in a neat and organized format.

a) The title page should include at least the title of the report and members’ names. b) The table of contents should list all the sections with page numbers. c) The one-page executive summary should highlight your results, conclusions, and

suggestions for each problem. If you are not sure how to write an executive summary, please refer to your previous business writing class(es), pick up a book at the library, or try to find reliable examples online.

d) The main body of the report consists of sections for each problem set. Each section should briefly introduce the faced problem, adopted method, models, software packages, results, and necessary visualization (table or graph). Make sure to address all issues listed above, but don’t necessarily do it in a question/answer format. Turn-in a management report, not a homework assignment.

e) The technical appendix is the place to throw in raw data, models, and outputs /screenshots, and all other technical related supplements. The existence of this part will help other analysts to review your decision processes, double check your results, and archive the approaches that you adopted.