代做BSYS864: Supply Chain Analytics: Discovering and Interpreting Data Assignment 2代做留学生SQL语言程序

BSYS864: Supply Chain Analytics: Discovering and Interpreting Data

Assignment 2: Berlin Brewery case study using anyLogistix

Berlin Brewery

1.1 Description of Case Study

BERLIN BREWERY is a Berlin beer brand that is well-known for its small, traditional, handmade beer. Currently, they have one brewery (factory) and one DC in Berlin. Although they have been in business briefly, they already have 50 customers in eight countries. Nevertheless, they face several problems which need to be solved. On the one hand, the German beer market, the primary market with the highest sales for BERLIN BREWERY, can be characterized as a mature market with high prices and pressure from competition. A sales crisis has been going on for years, and competition is intensifying due to consolidation processes implemented by international brewing companies. To expand their distribution network and reduce costs by inventory management improvements, a simulation and optimisation analysis must be performed to evaluate their current SC performance and develop suggestions for its improvement.

In this report, we will start by providing an overview of the German and international beer market and explaining the relevant technical details of the Berlin Brewery and its supply chain. Next, we will describe the current problem, followed by the developments of the GFA, NO, and Simulation, along with their respective results. Finally, based on the computational results, we will provide recommendations for management.

Beer is an alcoholic and carbonated beverage made by fermenting water, malt, and hops. The production of beer can be traced back to farmers who lived in the 4th millennium BC during Babylonian and pharaonic times. In Europe, the addition of hops to water only became generally valid in the 14th century. In the early Middle Ages, beer was primarily brewed in monasteries. The top-fermented brewing industry flourished, particularly in northern Germany. After the Thirty Years’ War, Bavaria gradually emerged as a significant beer-producing region. To preserve the purity and tradition of beer, the Bavarian dukes Albrecht IV and William IV regulated the raw materials with strict purity laws. As per these laws, only barley, hops, and water could be used to make beer. The purity requirement of 1516 is the oldest still-valid food legislation in the world (o.V.2017).

The German brewing tradition enjoys a prestigious reputation worldwide. According to the German Brewers’ Federation (o.V.2017), there were around 5,000 beer brands in Germany at the beginning of 2015 and over 6,000 in 2017. The trend is rising, although consumption is decreasing.

The market for beer products and the German brewing industry is marked by high prices and competition. There has been a sales crisis for several years, and competition is increasing due to the consolidation processes implemented by international brewing companies.

The SC of a brewery is highly complex. First, essential ingredients (hops, malt, yeast, and water) and extra ingredients have to be shipped to the brewery. Shipping hops are particularly complex as the product is very sensitive: moisture, heat, and oxygen reduce its brewing value over time. As a result, hops must be stored between -2° and +3° Celsius. After the brewing process, the young beer is stored in tanks at a temperature of 1° to 2° Celsius for three weeks and three months. At this time, the beer ferments and gets its characteristic colour. In the final steps, the beer is filtered, bottled, and labelled (Plank, 2013). Figure 1 summarises the logistics process of the craft beer industry.

Figure 1: Logistics process of the craft beer industry

Warehousing, picking, and loading are usually carried out by company employees, but this depends on the size and internal factors for each company. Some companies set up their own service companies for in-house logistics, but most breweries engage external companies for sorting empties, cleaning empties, and cleaning the plant. Compared to other industries, the distribution of beer is diversified as many different channels have to be covered. These include the classic food retail trade, beverage disposal markets, food discounters, petrol stations, and gas stations. However, the importance of different distribution channels has changed in recent years. Food retailers (especially discounters) have gained more importance in the brewing industry, while the importance of gastronomy has declined. In general, all channels receive the beer either directly from the breweries (it’s possible that a third-party logistics provider takes care of transportation) or through a beverage wholesaler.

1.2 Problem Statement

Currently, the BERLIN BREWERY’s Brewhouse consists of 5 tanks, which have a total capacity of 20hl. Assuming that demand for its beer is rising, the BERLIN BREWERY can expand its Brewhouse with more tanks. The whole process, from the original brewing to the finished product, requires between four to six weeks, depending on how long each type of beer must be stored. As the production location in Berlin is small, all beer produced is stored in the only DC in Berlin. An external service provider takes care of all logistical requirements. Currently, most of the beer BERLIN BREWERY sells is sold in Berlin, though the craft beer is also sold to wholesalers all over Germany. Since 2023, customer locations include Switzerland, Austria, Sweden, Norway, France, Italy, and Spain. BERLIN BREWERY collaborates with three suppliers in Germany who deliver empty beer bottles in crates, as well as the hops and malt. Empty bottles in crates are delivered from a location close to Nuremberg, the hops come from Koblenz, and a supplier delivers malt from a location close to Dresden. BERLIN BREWERY’s current sales figures and further financial figures, which are important for the analysis, are summarised in the next step. One-off acquisition costs for brewing equipment is €300,000. Moreover, maintenance costs (including energy and electricity) for the location of the factory are €80,000 per month or €2,630 per day.

Assuming that one beer crate (6.6 liters) consists of 20 bottles, the whole cost for one crate is €10. The costs can be broken down as follows in Table 1.

BOM             Usage per beer crate                     Costs per beer crate in Euro (€)

Hops             7.92 gr.                                        0.32

Malt              1390 gr.                                       2.22

Crate             20 bottles                                    6.00

Production processing costs                               1.46

Total              20 bottles                                    10.00

Table 1: Bill of material, based on information of BERLIN BREWERY

Carrying costs, which include warehousing costs, handling inside inventory, and inventory costs, are estimated to be €0.005 per beer crate (€0.2 per pallet) per day. The transportation from the BERLIN BREWERY factory to the DCs is calculated as volume-distance-based transport, and costs are €0.00175 per km/beer crate (€0.07 per pallet). The transport from the three suppliers to the BERLIN BREWERY factory is paid for by the suppliers. Inbound and outbound costs are shipment processing costs. Outbound costs are assumed to be €0.66 per beer crate (€26.40 per pallet), and inbound costs are €1 per crate (€40 per pallet). Hops and malt are delivered in a one-kilogram packaging unit: one pallet of malt or hops equals 40 packaging units.

BERLIN BREWERY wishes to expand its sales and work as efficiently as possible to increase profit. To reach these goals, several problems must be overcome: as mentioned in the first chapter, beer consumption in Germany, BERLIN BREWERY’s main market, is decreasing, and the market as a whole is highly competitive. The German beer market is mature and nearly saturated. Two potential solutions exist, the first one is expansion into other countries, and the second one is to increase sales to existing customers. These options instigate further challenges as BERLIN BREWERY has only one DC in Berlin. Long routes and long delivery times to individual customers are the main problems. Because of the long routes, BERLIN BREWERY can respond only relatively inflexibly to spontaneous requests, and a high number of unnecessary routes might be taken. There is also the risk of manmade or natural disruptions that can influence service quality (e.g. a storm destroys DC).

In summary, the goal of BERLIN BREWERY is to expand its distribution network, serve its customers as efficiently and satisfactorily as possible, raise its sales numbers, and increase profit. This is possible by optimising their SC: an optimal number of DCs and suitable locations for these DCs must be found to save as much logistics costs as possible. Loss of quality and delivery problems should be avoided.

Fig. 2 depicts the current situation. Production is in the center of Berlin, and the raw ingredients are shipped by truck directly from Dresden, Nuremberg, and Koblenz. To store as little as possible, raw materials are delivered on demand and used directly (JIT- just-in-time) for the pro-duction. The beer is delivered to 50 customers all over Europe.

Figure 2: Current network of BERLIN BREWERY

To provide a better understanding of the circumstances, a few assumptions are made:

All prices and costs are shown and calculated in Euro.

All processes are considered in terms of (beer) crates or pallet specifications, rather than bottles. This is because BERLIN BREWERY sells their beer only in whole crates, and these terms help to simplify the model. In one beer crate, there are always 20 bottles of beer, which have 0.33 liters of content per bottle (6.6 liters per crate).

1 pallet = 40 beer crates = 800 beer bottles

The recycling deposit on bottles is not considered.

Transportation costs from the factory to all DCs are the same.

Transportation-/ handling costs from the DCs to the customers are adapted to the price level of the actual country.

One year consists of two periods:

o Summer period: 01.05.2023 - 31.10.2023

Demand: 60% of total sales (due to higher demand during warm months)

o Winter period: 01.11.2023 - 30.04.2024

Demand: 40% of total sales.

Orders are received every seven days (static demand).

Transportation speed is 75km/h, the capacity of a truck is 1,320 crates, which equals 33 pallets which are single stocked.

Price per crate for customer: €70.00.

We assume that BERLIN BREWERY will sell 260,405 crates within the coming summer pe riod (151,035 crates) and winter period (109,370 crates). Fig. 3 below shows the customers, according to the summer and winter periods. Substantial sales are made in Germany, especially in Berlin (32,807 beer crates per year) and the least sales are achieved in Basel, Switzerland (871 beer crates per year).

Figure 3: Distribution of sales by country and period, own illustration

The main customers are beverage retailers, which furnish to smaller retailers or restaurants. Therefore, it is considered that only one wholesaler is supplied per city. This wholesaler makes resales independently. As a result, no further storage costs are incurred, as no further DC is required. Transportation to the wholesalers and handling is currently being handled entirely by a logistics service provider as BERLIN BREWERY does not yet have the necessary capacity and occupancy rate for profitable shipment. This service provider picks up the goods in the brewery, stores them in their own warehouse, and transports them directly to the distributor as needed. The current financial performance is presented in Table 2.

KPIs                                     €

Transportation cost                356,991

Other cost                             986,550

Inbound processing cost         805,117

Outbound processing cost       448,895

Inventory purchases               2,182,011

Production                             490,133

Revenue                                15,684,866

Profit                                     10,407,351

Table 2: Current cost structure

1.3 Greenfield Analysis (GFA)

Now, conduct a GFA. This GFA aims to determine optimal DC locations in the SC subject to minimum total transportation costs.

Creating an ALX model

Step 1. Import scenario Berlin Brewery Data.

Step 2. Check the tables Customers, DCs and Factories, Demand, Unit Conversions, and Products.

Performing experiments

Step 1. Go to GFA Experiment and run it for “Number of sites = 7”.

Step 2. Analyze the results using statistics “Flows” and “New Sites”:

a) What are the optimal coordinates of the new DCs?

b) What is the maximum distance from an optimal DC location to a customer?

c) Which costs, relevant for choosing an optimal DC location, were not considered in this GFA analysis?

After completing the GFA analysis, convert the scenario to Berlin Brewery NO.

1.4 Network Optimization (NO)

The NO offers the possibility of optimising an existing SC according to maximum possible profit. In this case, the GFA solutions will be taken into account to optimise the SC. Having checked the suggested GFA sites for DCs, the SC manager of BERLIN BREWERY analysed those locations further regarding additional factors such as availability of warehouses to rent, construction costs for building new warehouses, fixed costs, infrastructure, future de- mand forecasts, etc. As a result, some of the GFA-suggested locations are moved (Figure 4 ).

Figure 4: Alternative DCs locations

The NO goal is to find the SC design with the highest possible profit. Several parameters must be input as data to define the NO problem from a mathematical perspective. Each of the DCs has an inventory capacity of min. 5,800 beer crates and max. 11,600 beer crates, as well as a one-week inventory range. The brewery can stock 10,000 crates at maximum and should carry an inventory of at least 5,000 crates. Customers and their demands remain the same as those of the GFA. To avoid confusion, the DCs are now marked as red icons. Green icons are added to symbolize the suppliers of beer ingredients. These suppliers are located in Nuremberg, Koblenz, and Dresden.

Table 3 covers the costs of the sites. These numbers have been adjusted to the income ratios of each country. Two of them, Berlin and Bochum, are in Germany, and the overall prices in Berlin are cheaper than in western Germany.

Table 3: Cost structure per site

Production costs at the brewery are €1.46. Revenue is €70.00.

Creating an ALX model

Step 1. Open scenario Berlin Brewery NO.

Step 2. Check data in tables “DCs and Factories”, “Facility Expenses”, “Paths”, “Processing Costs”, “Product Flows”, “Product Storages”, “Production”, “Products”, and “Vehicle Types”. Explain the data in the tables above. The data in these tables should correspond to Table 3.

Performing experiments

Step 1. Go to NO Experiment and run it.

Step 2. Analyze the results using statistics “Result Dashboard”, “Product Flows”, “Produc-tion Flows”, “Demand fulfilment”, and “Overall Stats” etc.:

a) What is the most profitable SC design?

b) Is demand for all customers satisfied?

c) What is the total profit of the most profitable SC?

d) Compare the optimal SC design as computed in the NO and the initial SC design, (factory and DC in Germany) in terms of profit.

e) How can network optimisation results guide the selection of supply chain redesign strategies?

f) What trade-offs might arise from implementing changes suggested by network optimisation in the supply chain redesign process?

After completing the NO analysis, convert the scenario to Berlin Brewery SIM

1.5 Simulation

Simulation can be used in many ways. It promotes understanding of how the SC will react in the event of disruptions, such as outages and/or external influences. To run different simulations, the outcome of the NO has been used. In the case of BERLIN BREWERY, a two-month disruption has been simulated to see what happens if only one DC is kept or if a second is rented in Spain. Differences in, for example, the service level and profit could be decisive. For the simulation, assumptions must be made to make the model more realistic. The previous assumptions remain unchanged, and a lead time of three days is added. The sourcing policies are de- fined as follow: for the DCs, the program should choose the closest dynamic source. In this case, there is only one factory and the customers should choose the fastest dynamic source to receive orders as soon as possible.

In simulation, we extend our analysis by adding the following features:

- We transit from flows (as in NO) to orders. i.e., the customer demand is no more considered as an aggregated flow during a period but it is now generated as orders in certain intervals, e.g., 10 crates per day.

- We introduce inventory control to manage ordering processes.

- We introduce sourcing policy (e.g., single vs. multiple sourcing) to manage replenishment processes

- We introduce shipment control (LTL/FTL) to manage shipment processes.

First, we simulate the SC with two DCs in Germany and Spain, using the following data (Table 4).

Table 4. Parameters for simulation model

To evaluate the simulation results, we consider six KPIs according to the needs of BERLIN BREWERY:

(1) Financial KPIs, such as profit, revenue and costs

(2) Service level by products, which is calculated as (number of outgoing orders / number of placed orders), where an outgoing order is an order that is not dropped

(3) Inventory available, including backlog at DCs.

With all of the parameters described, we now run the simulations for one year, from May 1, 2023, to April 30, 2024.

Creating an ALX model

Step 1. Open scenario Berlin Brewery SIM.

Step 2. Check data from Table 4 in tables “DCs and Factories”, “Inventory”, “Sourcing”, and “Paths”. Explain the data in the aforementioned tables. The data in these tables should corre-spond to Table 4.

Performing experiments

Step 1. Go to Simulation Experiment and run it.

Step 2. Analyze the results using the KPI Dashboard “Revenue, Profit, Costs”, “Service Level”, “Average Inventory including Backlog”.

a) What are the profit, revenue, and costs of the SC? Does this result match with the NO results? Explain.

b) Is demand for all customers satisfied? Explain.

c) How would you assess the inventory dynamics within the supply chain? Describe the changes in inventory behaviour observed during the latter part of the simulation period.

1.6. Risk analysis: Two-month disruption at one of the DCs

This analysis simulates a two-month disruption of the DC in Berlin from August 1st to September 30th for two different network design scenarios, i.e., with two DCs and with a single DC in Berlin.

Creating an ALX model

Step 1. Open scenario Berlin Brewery SIM.

Step 2. Check data from Table 4 in tables “DCs and Factories”, “Inventory”, “Sourcing”, and Brewery Disruption Risk with 1 DC.

Step 2. Check data about the disruption in table “Events”.

Performing experiments

Step 1. Go to Simulation Experiment and run it.

Step 2. Analyse the results using the KPI Dashboard “Revenue, Profit, Costs”, “Service Level”, “Average Inventory including Backlog”.

a) What are the profit, revenue, and costs of the SC for the two different network design Scenarios?

b) Is demand for all customers satisfied? Explain.

c) What is your judgement on the inventory dynamics in the SC? Explain the change in inventory dynamics in the disruption period.