Demand Planning And Fulfillment - Project 1

Running head: DEMAND PLANNING AND FULFILLMENT 1

Demand Planning and Fulfillment - Project 1:

Develop a “Simple” Inventory Control Policy

Jin Liu - 50%

Qi Hu - 50%

Rutgers University

October 27, 2017

DEMAND PLANNING AND FULFILLMENT 2

Executive Summary

This is usually done using Economic Order Quantities (EOQ) which shows the optimum levels

of inventory one is supposed to keep so that the holding costs and ordering costs are at their

minimum. This ensures that the manager does not keep too much stock and also, he does not run

out of stock when the customers need them. EOQ has been analyzed through combining the case

of the Woodstock Appliance Company, serving as the basis for managers in identifying the

optimal order batch, so as to control the inventory, reduce the order cost and holding cost. The

Woodstock Appliance Company rents a warehouse in NJ to store the products it produces before

delivering them to the customers. Each year, the company spends much of its income on renting

the warehouse. The warehouse is large and renting it costs much to the company. That is why it

is essential to reduce the number of products that are simultaneously stored in the warehouse in

order to reduce the rent spending. Considering the fact that the majority of the products stay in

the warehouse for no longer than a week, it is necessary to develop a strategy to reduce the

spending on the warehouse rent by optimizing the supply chain and reducing the time of storing

for each product. There is a need to normalize the amount of produced goods and the demand in

order to reduce the amount of time the products stay in the warehouse. Therefore, we

recommend that, calculating order cycles for each product that tells us how often we should

place an order. After we calculate the order cycles for each product, some order cycles may not

be in whole numbers; therefore, it is necessary to round up and down to see which order cycle

yields to the lowest total cost. Our goal is to satisfy customer demand while keeping our costs at

the lowest.

DEMAND PLANNING AND FULFILLMENT 3

Introduction

With today’s uncertain economy, businesses are seeking to forecast and control their

inventory in order to lower their cost. Inventory control is crucial to the companies in the aspect

of fulfilling customer demand as well as reducing obsolete stock that wastes the money. For

instance, companies must have enough inventory to satisfy customer demand. It does not mean

to purchase a large quantity that accumulates much higher costs (storage cost, ordering cost, and

holding cost) comparing to the revenue. It is necessary to use inventory control model like

Economic Order Quantity(EOQ) to manage the amount of order quantity. Economic Order

Quantity is a type of fixed order quantity model that determines the amount of an item to be

purchased or manufactured at one time. The intent is to minimize the combined costs of

acquiring and carrying inventory. When the company orders in accordance to the Economic

Order Quantity, they will be able to minimize their ordering cost and holding cost. Acquiring

cost, also called ordering cost, is the sum of the fixed costs that are incurred each time an item is

ordered, those fixed costs include ordering process costs, logistics costs, machine setup costs,

etc. Carrying cost, also called holding cost, is usually a percentage of the inventory value, it

includes storage cost, capital cost, insurance, etc.

The purpose of this project is to use the effective model to determine the EOQ for

ordering, minimize the total cost using Solver and find the minimal total cost under different

cycle time. Cycle time is the total time from the beginning to the end of your process, as defined

by you and your customer.

Problem

The Woodstock Appliance Company carries four products. The numerical information is

summarized in the following table.

DEMAND PLANNING AND FULFILLMENT 4

Product

Annual Demand(D)

5,000

10,000

30,000

300

Order Cost (S)

$400

$700

$100

$250

Holding Cost Rate

10%

Purchase Price (P)

$500

$250

$80

$1,000

Space/Unit

Woodstock rents a warehouse in NJ to store and distribute its inventory of the four

products. The rent is $7 per square feet per year. The warehouse manager asks to develop a

“simple” inventory control policy (i.e., the order quantities/cycles) and determine how much

storage space to rent.

Develop three spreadsheet models to answer the following:

1. Try EOQ formula for each product and calculate inventory costs, storage space needed

and total cost;

2. Develop an optimization model and use Solver to determine the order quantities and

storage space that result in minimization of total cost

3. Round up and round down the order cycle to find the total cost that is closest to the result

of II.

Purpose

The goal of this project is to find the EOQ of the product and use the EOQ to calculate

related total costs for all four products. Secondly, develop a total cost minimization model by

using solver that reflects different EOQ that results in different costs under constraints.Last but

not least, we round up and round down the cycle time to the closest total cost comparing to the

total cost we found in second part.

DEMAND PLANNING AND FULFILLMENT 5

Quantitative analysis

Since a lot of numerical information is provided to us, the demand (D), order cost (S),

holding cost rate, purchase price (P) and space/unit are known.

First spreadsheet:

First, we calculate the holding cost (H) by multiplying the holding cost rate by the

purchase price (P). Then we use the known data to calculate the Economic Order Quantity

(EOQ) for each product. The formula for calculating the EOQ is EOQ=sqrt(2*D*S/H). We can

now calculate the annual ordering cost, annual holding cost and annual inventory cost based on

the EOQ we calculated. The formula for calculating annual ordering cost is D*S/EOQ. And the

formula for calculating annual holding cost is EOQ*H/2. After we calculate the annual ordering

cost and holding cost for each product, we can get the annual inventory cost for each product by

adding the annual ordering cost and annual holding cost together. Therefore, we can get the total

inventory cost=$43,651.61 for all the products. The next step is to calculate the storage cost to

solve for the total cost. First, we calculate the storage space needed for inventory, storage

space=12Q1+25Q2+5Q3+20Q4, where Q’s are order sizes for the four. And then we can

calculate the cost of storage by multiplying the storage space by $7 per square feet. Finally, we

will approach to the end by adding storage cost and total inventory cost for all four products to

get total cost=$234,101.47. The first spreadsheet mainly shows that when the order cycle is not

constrained and the optimal order quantity for the first product equals 135.4589, 2

EOQ=748.3315, 3

EOQ=886.0254, 4

EOQ=38.7298 the minimum total cost is equal to

$137,236.36.

Second spreadsheet:

DEMAND PLANNING AND FULFILLMENT 6

For the second spreadsheet, we set a constraint for the order cycle on the basis of the first

table. Therefore, we first calculate the order cycle for each product with the formula: Order

Cycle=(Q*/D) *52. Then, we use the built-in solver function of Excel to set the total cost as the

objective and EOQ as the changing variable. For constraints, according to the problem, we can

define the order cycle ≥ 1. We find that the total cost is equal to 137,236. This value is much

smaller than the total cost in the first table.

Third spreadsheet:

After we use Excel Solver to solve for the minimization of total cost and their order

cycles under constraints. The next step is to round up or round down the order cycle to make it a

whole number, so we know exactly when to order. Therefore, we need to recalculate the EOQ by

using formula Q

*D/52. From there, we can repeat the same step of calculating annual

ordering cost, holding cost, inventory cost, total inventory cost, and storage cost in the first

spreadsheet. There are four scenarios when we try to round up and round down, their total cost is

different. However, the one that is closest to the minimal total cost we found in the second

spreadsheet is the order cycle of 1,1,1,3 (weeks) correspond the perspective products.

Conclusion

From the information, the problem provides, we utilize the information and transform

them into statistics that help a company to make a decision of how much to order (EOQ) and

how often to order(T) in order to minimize their total cost. The purpose of this project helps us to

understand how the EOQ works and how EOQ and costs react under constraints and then

approach to the cycle time that leads to lower total cost and their respected EOQ, storage cost,

and total inventory cost. We recommend using the models in this project to the manager because

it can yield to the highest profit of a company by keeping the cost low.

DEMAND PLANNING AND FULFILLMENT 7

Appendix 1: Excel Worksheet

Product

Annual

Demand(D)

5,000

10,000

30,000

300

Order Cost (S)

$400

$700

$100

$250

Holding Cost Rate

10%

Purchase Price (P)

$500

$250

$80

$1,000

Space/Unit

holding

Cost/unit(H)

$50

$25

$100

product

Economic

Order

Quantity

Holding

Cost

Ordering

Cost

Inventory

Cost

Storage

Space

282.8427125

$7,071.07

$14,142.14

3394.113

748.3314774

$9,354.14

$18,708.29

18708.29

866.0254038

$3,464.10

$6,928.20

4330.127

38.72983346

$1,936.49

$3,872.98

774.5967

$43,651.61

27207.12

Total inventory

cost

$43,651.61

storage space

190449.8622

total overal cost

$234,101.47

Place new order. It's free, fast and safe

Paper type

Deadline

Page count

550 words

Total price: $ 15.00

Our customers say

Jeff Curtis

USA, Student

"I'm fully satisfied with the essay I've just received. When I read it, I felt like it was exactly what I wanted to say, but couldn’t find the necessary words. Thank you!"

Ian McGregor

UK, Student

"I don’t know what I would do without your assistance! With your help, I met my deadline just in time and the work was very professional. I will be back in several days with another assignment!"

Shannon Williams

Canada, Student

"It was the perfect experience! I enjoyed working with my writer, he delivered my work on time and followed all the guidelines about the referencing and contents."