Objectives:

✅ Minimize storage costs

✅ Optimize SKU placement for fast retrieval
✅ Reduce material handling time (forklift efficiency, picking routes)
✅ Balance inventory storage with throughput
✅ Ensure FIFO/LIFO storage strategies
✅ Reserve space for packaging materials

The problem can be structured as a Mixed Integer Linear Programming (MILP) model,
where we determine the optimal number of storage locations and SKU placement while
minimizing storage costs and material handling time.

Since you are solving this warehouse optimization problem using Excel Solver, I'll guide you
step by step on how to structure the model and then create a visual warehouse layout
diagram.

1. Required Data for Warehouse Optimization

To create an efficient warehouse layout, I need the following additional details:

A. Warehouse Constraints & Dimensions

 Total warehouse area (sq. ft.): How much space is available?

 Warehouse height (ft.): To determine vertical storage capacity.

 Number of racks available: If already decided.

 Aisle width (ft.): To ensure proper forklift navigation.

 Maximum weight per rack (if applicable).

B. Storage Optimization Inputs

 Storage types used (pallet racking, shelving, bulk storage, mezzanine levels, etc.).

 FIFO/LIFO strategy per SKU (for correct rack allocation).

 Temperature-controlled zones (for perishable items like beef subprimals).

 Dedicated space for packaging materials (trays, plastic wrap, boxes, labels).

C. Picking & Handling Optimization

 Number of forklifts available.

 Picking strategy (zone picking, batch picking, wave picking).

  Inbound & outbound throughput (how many shipments per day?).

 Shipping dock locations (how many docks, where are they located?).

D. Cost Optimization Factors

 Storage cost per rack/pallet (for Solver cost minimization).

 Material handling cost per movement (to optimize route efficiency).

 Labor costs (if applicable).

Based on the data provided, the Excel Solver model will: ✅ Minimize Storage Cost: Assign
SKUs to racks while minimizing total storage expenses.
✅ Optimize SKU Placement: Place high-demand SKUs in accessible areas.
✅ Reduce Handling Time: Minimize travel distance for forklifts and workers.
✅ Balance Inventory Storage with Throughput: Avoid congestion in high-movement zones.
✅ Allocate Space for FIFO/LIFO Compliance: Maintain proper product rotation.
✅ Reserve Dedicated Space for Packaging Materials.

2. Decision Variables (Solver Adjustable Cells)

In Excel, we define Solver decision variables, which are the values Solver will change to
optimize the warehouse layout.

 XiX_{i}Xi = Number of racks assigned to SKU iii

 YiY_{i}Yi = Binary variable (1 if SKU iii is assigned a specific zone, 0 otherwise)

 ZiZ_{i}Zi = FIFO/LIFO indicator (1 for FIFO, 0 for LIFO)

3. Objective Function (Minimize Total Cost)

Solver will minimize total warehouse cost, which consists of storage cost + material
handling cost.

Minimize Cs∑iXi+Cm∑iPiXi\text{Minimize } C_s \sum_{i} X_i + C_m \sum_{i} P_i

X_iMinimize Csi∑Xi+Cmi∑PiXi

Where:

 CsC_sCs = Storage cost per rack

 CmC_mCm = Material handling cost per unit movement

 PiP_iPi = Distance factor for SKU iii from picking to shipping zones

In Excel, the formula for Total Cost will be:

excel
CopyEdit

=SUMPRODUCT(Racks_Assigned, Storage_Cost_Per_Rack) + SUMPRODUCT(Racks_Assigned,

Handling_Cost_Per_Rack)

4. Constraints in Excel Solver

We will now define constraints that must be met.

A. Storage Capacity Constraints

∑iXi≤Total Available Racks\sum_{i} X_i \leq \text{Total Available Racks}i∑Xi

≤Total Available Racks

 Ensures we don’t allocate more racks than available.

 Excel Formula:

excel

CopyEdit

=SUM(Racks_Assigned) <= Total_Racks_Available

B. SKU Demand Constraints

Each SKU must have enough racks to store its required volume.

Xi≥Total Volume RequirediRack CapacityX_i \geq \frac{\text{Total Volume Required}_i}{\

text{Rack Capacity}}Xi≥Rack CapacityTotal Volume Requiredi

 Excel Formula (per SKU row):

excel

CopyEdit

=Racks_Assigned >= Total_Volume_Required / Rack_Capacity

C. FIFO/LIFO Constraints

Perishable products must follow FIFO, while non-perishable materials use LIFO.

Zi=1if SKU follows FIFO, else Zi=0Z_i = 1 \quad \text{if SKU follows FIFO, else } Z_i = 0Zi
=1if SKU follows FIFO, else Zi=0

 Excel Formula (per SKU row):

excel

CopyEdit

=IF(FIFO_LIFO="FIFO", 1, 0)

 Then, Solver ensures FIFO SKUs are allocated first in the picking sequence.

D. Aisle Width & Material Handling Optimization

To minimize congestion, we enforce aisle width constraints.

∑iPiXi≤Max Allowable Travel Distance\sum_{i} P_i X_i \leq \text{Max Allowable Travel
Distance}i∑PiXi≤Max Allowable Travel Distance

 Excel Formula:

excel

CopyEdit

=SUMPRODUCT(Racks_Assigned, Handling_Cost_Per_Rack) <= Max_Travel_Distance

E. Cost Constraints

Total Storage Cost≤Budget Constraint\text{Total Storage Cost} \leq \text{Budget

Constraint}Total Storage Cost≤Budget Constraint

 Excel Formula:

excel

CopyEdit

=SUMPRODUCT(Racks_Assigned, Storage_Cost_Per_Rack) <= Budget

5. Setting Up Solver in Excel

Step 1: Define the Solver Objective

 Set Objective Cell = Total_Cost

 Objective = Minimize

Step 2: Set Decision Variables

 Adjustable Cells = Racks_Assigned

Step 3: Add Constraints

  SUM(Racks_Assigned) <= Total_Racks_Available

 Racks_Assigned >= Total_Volume_Required / Rack_Capacity

 SUMPRODUCT(Racks_Assigned, Handling_Cost_Per_Rack) <= Max_Travel_Distance

 SUMPRODUCT(Racks_Assigned, Storage_Cost_Per_Rack) <= Budget

Step 4: Choose Solving Method

 Select GRG Nonlinear or Simplex LP (for linear optimization)

C:\Users\somus\Downloads\Time series

py Monthly_Sales.py

📌 Phase 1: Project Planning & Data Collection

1. Define Warehouse Goals & Constraints

 o Space Optimization (Storage vs. Throughput)

o SKU Placement for Fast Retrieval

o Material Handling Efficiency (Forklifts, Pick Paths)

o FIFO/LIFO Compliance

o Cost Optimization (Storage, Labor, Handling)

2. Gather Warehouse Data

o Warehouse Layout Dimensions (Total Area, Aisle Width, Dock Locations)

o Racking System (Rack Height, Depth, Number of Levels, Max Load)

o SKU Data (Product Dimensions, Turnover, Temperature Needs, FIFO/LIFO)

o Equipment Details (Forklifts, Pickers, Conveyors)

o Throughput Data (Inbound/Outbound Volumes, Pick Rates)

o Cost Data (Storage Costs, Handling Costs, Labor Costs)

📌 Phase 2: Layout Planning & Space Optimization

3. Develop Warehouse Layout Scenarios

o Fixed vs. Flexible Storage Areas

o FIFO/LIFO Storage Strategies

o Zoning for Perishables, Fast-Moving Items, Bulk Storage

o Dock Placement Optimization

4. Optimize Racking & Aisle Configuration

o Adjust Rack Heights for Maximum Storage

o Determine Aisle Widths for Forklift Efficiency

o Optimize Pick Paths (Minimize Travel Time)

5. Excel Solver Model for Space Optimization

o Define Decision Variables (Rack Placement, SKU Location, Aisle Widths)

o Set Constraints (Warehouse Space, Throughput, Cost, FIFO/LIFO)

o Run Solver to Optimize Space & Costs

📌 Phase 3: Simulation & Validation

6. Simulate Warehouse Operations

o Use Simulation Software (AnyLogic, FlexSim, Arena, Simio)

o Model Inbound, Storage, Picking, and Outbound Movements

o Test Scenarios (Congestion, Forklift Delays, Picking Strategies)

o Optimize Based on Results

7. Data Analysis & Performance Metrics

o Storage Utilization (%)

o Picking & Putaway Efficiency

o Travel Distance Reduction (%)

o Throughput Improvement (%)

o Cost Savings from Optimization

📌 Phase 4: Implementation & Reporting

8. Finalize Layout & Optimization Strategy

o Recommend Best Layout for Execution

o Suggest Picking Strategies (Zone, Wave, Batch)

o Identify Cost Savings & ROI

9. Generate Final Report & Presentation

o Include Optimized Layout Diagram

o Show Excel Solver Model Results

o Highlight Simulation Outcomes & Insights

Box Dimensions: ~ 24" L x 16" W x 10" H

Cubic Volume: ~ 2.2 - 2.5 ft³

Warehouse dimension: 600000 sqft

Aisle width:Industry standard

Racks:You should consider the number based on the optimized design

docks:2

per day throughput is attached in the diagram

10 forklifts are available