Decision-Making Problems in Supply Chain Management

This report discusses the decision-making problems in facility-level supply chain management system using excel calculations. It analyzes the supply chain process model and optimizes the supply chain model. It also explores the forecasting demand and seasonal factor in supply chain management.

Supply Chain Management
Name of the Student:
Name of School:
Register Number:
Date:
Class/year
Word count:

Table of Contents
Introduction........................................................................................................................................................... 1
Background........................................................................................................................................................... 1
Data collection Method.......................................................................................................................................... 1
Analysis and Evaluation......................................................................................................................................... 7
Conclusion........................................................................................................................................................... 17

Introduction
The working of this report will discuss about the decision-making problems of facility-
level supply chain management system, by using excel calculation. The supply chain
management is a process where the supplier delivers the product to the customers. The purpose
here is to find some issues that occurred in supply chain management, in the past few years.
Analyzing the supply chain process model can include three stages like calculating the
seasonality wise analysis (annual demand), economical wise order quality analysis (EOQ), and
cycle service level (supplier selection) analysis of supply chain management system. The
objective of a decision-making problem is to understand the process of supply chain model,
analyzing the modelling techniques and optimizing the supply chain model. Additionally,
calculating the critical radio of the cost value to find the data warehouse aspects of supply chain
model. At the same time, the cost time, the service level will be interpreted and each stage of the
supply chain process will be investigated.
Background
The supply chain management of the inventory analysis has been implementing five
years of supply chain data analysis. The initial stage of the analysis can calculate the forecast of
the supply chain, which can find the deseasonality, estimating the demand on deseasonality and
seasonalization factor of 5 years. The annual demand table has been containing the year, month
period, time period and demand of the fields. Economic order quality contains fields on demand,
order cost, holding rate, and the unit cost of the fields. Supplier selection table is used for
calculating the values of critical radio of the total unit cost simultaneously to find how many
days are required for the storage of contractor warehouse.
Data collection Method
Forecasting demand:
The annual forecasting demand calculation can be used for the process of predicting a future
event. It can easily find the underlying basis of all the business decision such as:
1

 Production
 Inventory
 Personnel demand values
 Facilities
To find the values of Horizons values of the forecasting can follow the below mentioned three
ways:
 Short-range forecast
 Medium range forecast
 Long range forecast
Short range forecast:
 To calculate the excel value for up to 1 year, generally less the 3 months.
 Calculating the short-range forecast that can include the values of the purchasing value,
to analyze the job scheduling time, workforce level, job assignments, and production
levels. Forecasting usually employs different methodologies and longer term forecasting
values are more accurate.
Medium range forecast:
 To find the medium range forecast calculating the values of 3 months to 3 years.
 The supply chain modelling of the facility level can analyze the sales and production
planning, budgeting of the forecast demand value of the supply chain modelling
management.
Long range forecast
 By calculating the 5 years values of the forecasting annual values of supply chain
management of the demand, the value is 52720.
 The long range forecasting can analyze the data values on new product planning, facility
location, research and development of the forecast demand.
Types of forecasts
1. Economic forecasts
The economic forecasts can specify the economic growth, inflation rate, money
supply based on economic data trends of the product interventions.
2. Demographic forecasts
The demographic forecasting can be used for population aggregate and
disaggregate for the forecasts demand.
3. Technical forecasts
The Technical forecasting can be used for predicting the technological change of
the supply chain forecasts demand.
2

4. Other forecasts-
Forecasts can use the customer to order the product and the supplier can deliver it
within the particular period of time demand.
5. Business forecasts
The business forecasts can calculate the demand’s sales forecasting of supply
chain modelling system.
Seasonal factor
To calculate the Seasonal factor values include the following steps:
 Time required in future.
 Availability of historical data.
 Relevance of historical data into future.
 Demand and sales variability patterns.
 Required forecasting accuracy and likely errors.
 Planning horizon/lead time for operational moves.
By calculating the forecast value of the supply chain model, the value can be found by following
3

the below mentioned steps:
 Deseasonality
 Estimate the seasonality demand.
 Seasonality factor.
Calculating the seasonality value can be used for finding the annual demand of the supply chain
management of the facility level.
Calculating the Deseasonality of the values can be used in excel formula as,
Seasonality value=Sum(demand value, estimate deseasonality)/number of time period.
By calculating the first 3 time periods, the value is 17167. By calculating the annual forecasting
demand value by using excel formula,= Sum (D14:D16). Next, plot the graph in the demand
value.
Plot the graph in the demand value.
4

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
15,000
15,500
16,000
16,500
17,000
17,500
18,000
18,500
DEMAND
The 1st year of the product demand value which can be compared with the five year demand
value is great than the units of supply chain model.
Calculate the linear regression of the total cost of the TD intercept by using the below mentioned
formula,
TD intercept =Linest (time period, desonalization,ture, true).
The Linest function of array function can be used for the return of the product delivery time
period data, to the multiple cells.
TD intercept TD intercept
42.52525253 16961.91919
8.077019314 57.39800668
0.776035167 73.36317116
27.71989365 8
149192.7609 43057.23906
Total cost of the TD intercept in five year time period
The total cost value is 43057.23906.
Supply chain management of the safe stock.
5

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
15,000
15,500
16,000
16,500
17,000
17,500
18,000
18,500
Chart Title
Series1 Series2 Series3
Economic order of quality can be used to find the total number of relevant cost on the safety
stock on the value of supply chain. The safe stock of the supply chain inventory process can
calculatethe value of the unit cost, ordering cost and the holding cost value of the supply chain
factuality level.
To find the value of Economical order of the quality, use the following formula,
Economical order of the quality=c_t*(D/Q)+c_*h*Q/2
EOQ Total relevant cost value is, 59874.54.
The order list value of inventory is reduce
 Optimal quality Q¿
 Time T ¿
 Cost Cs
Economic Order Quantity
Demand D 52720
ordering cost c_t 2000
unit cost c_t 85
Holding rate h 20%
EOQ Q* 3522.0
6

Total relevant Cost TRC*
59874.5
4
Analysis and Evaluation
Critical Ratio and Application
The critical ratio of the supply chain modelling of the facility levels can find the value of
shortage cost and exceed cost of the critical ratio of supply chain system.
The cycle service level can be used for the customer to order the product and the supplier can
calculate how much number of days is required to deliver the product to the customer of the
construct warehouse.
To calculate the critical ratio value, use the following steps:
The order list value of inventory is reduce
Optimal quality Q¿
Time T ¿
Cost Cs
cs is denoted as the shortage cost
ce: is denoted as the exceed cost
P is donated as the ordering cost
Unit cost is 65
Selling cost is 2000
Cs=p-c=2000-85=1935
ce =65
Critical radio value is= Cs
Cs+ ce = 1935
1935−65 =66
7

The critical ratio of the supply chain value is 66.
To find the value of QB¿
Q¿ vs. critical radio
1 2 3 4 5 6 7 8 9 10
0
20
40
60
80
100
120
Series2
Series1
Optimization of supply chain model:
For analyzing the optimization cost,
Optimization cost= =SQRT(2*c_t*D/(c_*h))
8

To analyze the total relevant cost value, use the following formula,
Total cost Relevant= =c_t*(D/Q)+c_*h*Q/2
9

YEAR
MONTH
REPORTED Period,t
Dm
and
Deseasona
lization
Estimated
Deseasonalization
Demand
SEASONA
L Factor
1 1 1
16,5
00 17004 0.97
1 5 2
17,0
00 17167 17047 1.00
1 9 3
18,0
00 17100 17089 1.05
2 1 4
16,3
00 17133 17132 0.95
2 5 5
17,1
00 17067 17175 1.00
2 9 6
17,8
00 17167 17217 1.03
3 1 7
16,6
00 17233 17260 0.96
3 5 8 17,3 17250 17302 1.00
10

00
3 9 9
17,8
50 17333 17345 1.03
4 1 10
16,8
50 17400 17387 0.97
4 5 11
17,5
00 17533 17430 1.00
4 9 12
18,2
50 17472 1.04
5 13
168
70 17515 0.96
5 14
175
43 17557 1.00
5 15
183
07 17600 1.04
ANNUAL FORCASTING
DEMAND (5 YEARS)
527
20
The aspect of the supply chain management is analyzing the cost, time and service level of the
output of interpretation.
Calculating the value of EOQ= =ROUND (SQRT (2*Ordering cost*DEMAND/ (Unit
cost*Holding rate)), 0)
muDL=DEMAND/Num_of_period
Safety stock= =NORM.S.INV(CSL)
To find the total cost= mu_DL+Safety_factor_K*sigma_DL
Analyze the total unit cost= =Unit cost*DEMAND
Demand 52720
Std 6000
Unit Cost 65
11

Ordering
Cost 2000
Holding
Rate 10%
CSL 80%
Lead time 5
weeks in
year 52
Num of
Periods 26
EQO 3522
mu DL 2028
sigma DL 1177
Safety factor
k
0.84162
1
order point 3426800
Total unit cost
342680
0
C_S 1935
C_e 65
critial radio 66
Days required to sign the Warehouse
contract
66.6666
7
Number of Days required to Sign the Warehouse Contract
Supply chain management of the warehouse contract can be used to find how many days
are required to sign the warehouse contract, to deliver the product to the customer. The supply
chain management of the supplier can deliver the product to the customer, monthly, weekly and
on yearly basis. In this case, the supply chain manager can sign the product to the supplier to find
how many day are required to deliver the product to the customer. The initial stage of calculation
is critical ratio value of the sequencing and scheduling order of the suppliers. The Schedule time
which can follow various priority rules to decide of the sequence of supplier. Firstly, for
scheduling time and calculating the annual demand values of the critical ratio mean value which
can be used for the standard deviation and monthly demand values (2000) is divide by 12 months
and its value is72000 for the supply chain management.
12

The warehouse contract of the scheduling time before calculating the total manpower of
the organization company. The customer can order the total product on 3963.18. We can calculate
the total number of days by using the value by following the following steps:
The total number of days can be used for calculating the Mean value and standard
deviation. By calculating the month value of (30 days), the mean value is 2000 and the standard
deviation value is 6000 for the supply chain management system. The number of warehouse
contract can calculate the annual demand of (5 years) of the mean value i.e., =24000. The supply
chain demand of the supplier which is allocating the contract on weekly basis, of the model
calculating the annual demand is divided by 52 week of the value i.e., 461.54. After that, the
Supply chain model of the warehouse contract calculates how many days are required to sign the
contract for the yearly basis by using the yearly demand value which is divided by 30 and the
calculated value is 66 days. For calculating the number of days for the warehouse contract, the
following steps and formula are followed,
Given,
unit cost 65
selling
price 2000
c_s 1935
c_e 65
Calculating the annual demand values of (5 years) mean value=Mean*12 =24000, standard
deviation is =SD*SORT (12) =72000
13

Calculating the weekly demand values of (5 years) mean value=Mean/ (30/7) =461.54, standard
deviation is =SD*SORT (30/7) = 28.57142857
14

Calculating the daily demand values of (5 years) mean value=Mean/30 = 66.66666667, standard
deviation is =SD*SORT (30) = 180000
Supply Chain Perspective
The reproduction model takes the dynamic behaviors of the supply chain in to account
and includes the planning production behavior of a supplying company based on the planning
model. The supplier manager can be used for the reproduction model to determine what kind of
parameters don’t affect the product and company’s performance and then proposed a new
management rules. Quantitative results that prove the benefits of integrating forecast order for an
aeronautic supplier has been provided. The perceptive of the supply chain model is to find the
total relevant cost as 59874.54, which is calculated with the ordering cost i.e., 2000, and various
types of demand is divided by EOQ of the supply chain, which can be additionally added to the
unit cost of the product, then multiply by the holding rate of the product which is divided with
EOQ and is divided by 2, where it finds the value of the total relevant cost for the supply chain
management. The supplier selection of the perspective can calculate the order point, Total unit of
cost, and critical ratio of the warehouse contract which is used for developing the business
marketing of the supply chain management system.
15

Impact of Certain Parameter Changes
Impact of parameter change
values
Economic order quality
Demand D 52720
ordering cost
c_
t 2000
unit cost
c_
t 65
Holding rate h 10%
EOQ
Q
* 3522.0
Total relevant
cost
41383.8
7
The certain parameters of change value are,
Given,
Inventory holding cost =10%
Unit cost =65
Delivery lead time =5
The Economical of the unit cost quality values can be calculated by using the given formula,
EOQ==SQRT (2*c_t*D/I4*h)
16

The EOQ of the change of different value of the parameters account inventory can calculate the
holding cost such as storage, ordering costs and shortage costs deliver lead time of the supply
chain management. We can use the following formula,
Q=
√ 2 DS
H
Q = EOQ units
D = demand in units (typically on annual basis)
S = order cost (per purchase order)
H = holding costs (per unit, per year)
Inventory cost of the holding rates
The inventory holding rate has been used for the fraction of customer demand that is met through
immediate stock availability without order or lose of sales. Fill the inventory holding rate from
the service level indicator. The findings of the holding cost of the inventory value is divided by
the annual value of the inventory. If the cost is 65 and the value of inventory is 2000, the holding
cost of the inventory is 10% percent of the supplier product of the total relevant value which is
201581.24 of the supply chain management.
Lead time
17

Delivery Lead time has very distinct meanings depending on the industry being considered. The
lead time is 5, which is used for calculating the critical ratio of the inventory management
system. One of the key factors that needs to be taken into account for inventory optimization is
the lead time from the inventory control angle. This angle is typically most useful for the
suppliers and the customers.
Conclusion
This report successfully implements the supply chain management of the facility level and finds
the critical ratio value, which is 66. The excel calculation is used to find the critical ratio. To
understand the forecast of the modeling techniques and optimization techniques of the supply
chain, the calculated forecasting value is evaluated as 52720.The analysis of the aspect of supply
chain management requires cost, time period, and service level of the value. The critical ratio
value and safe stock on the contract warehouse is calculated as 66.66667 days.
18