Business Management | Resources

Excel Guidance For Manual Warehouse Management - B2b Cross Border E Commerce

Nov 17, 2022 | Shivphool Singh and Konark Ogra

Introduction Warehouse management refers to the oversight of operations in a warehouse. This includes receiving, tracking, and storing inventory, managing shipping, workload planning, and monitoring the movement of goods. For any warehouse manager, Tracking the overall operating profit in terms of SKUs and customers is critical. All the warehouse management processes can be categorized into 3 subcategories- Demand Forecasting Inventory Management Tracking financial performance

Excel Guidance for Manual Warehouse management

Demand Forecasting- Forecasting models are one of the many tools businesses use to predict outcomes regarding sales, supply and demand, consumer behavior, and more. These models are especially beneficial in the field of sales and marketing.

Various forecasting models can be applied based on situational requirements. The chosen model must include the following things-

1- Use of available data source

2- It must consider the stage of the product’s life cycle for which it is making the forecast.

3- Model accuracy vs inherent cost trade-off i.e purpose of forecasting

4- A combination of qualitative, time series, and casual models for forecasting

Inventory Management and Strategic Warehousing- Inventory management plays a critical role in defining strategic warehousing. The right balance of desired SKUs plays an important role while fulfilling the demand and creating an effective impact in managing a healthy balance sheet. Inventory management always plays a trade-off between the cost of keeping the inventory and the revenue earning. In a direction, where overstocking or wrong stocking can affect negatively put inventory-keeping expense stresses. In the same manner, understocking will leads to unfulfilled demand leading to a loss of credibility with customers. 

Tracking Financial Performance- The financial performance of a typical warehouse can be tracked in two terms-

  1. SKUs financial to track the overall operating profit/loss 

  2. Customer wise financial to track the overall profit/loss

Ways of warehouse management

Manual and digital methods may both be used to monitor and control the warehouse's most crucial operations. Management approaches may be chosen based on several criteria, including the size of the business, the number of product lines managed, the needed operating profit margin, and the desired turnover rate.

Warehouses with a larger number of SKUs, fewer or no customers, and greater turnover rates are ideal candidates for a fully digital warehouse management system. Greater transparency and better control are the trade-offs for the larger initial expenditure required by this method of administration.

Smaller warehouses are more likely to adopt a manual management system. It may cause unbalanced management, inefficiency, and a host of other issues in this context. If these issues are resolved, the warehouse's overall performance may be optimized, making this tool ideal for those in charge of maintaining it.

Excel Guidance for Manual Warehouse management

Excel with proper modeling is one of the most powerful tools that can be used in manual warehouse management. For small-scale warehouses, It is an efficient and free-of-course tool that can be used for enhancing performance in different parts like inventory management, demand forecasting, and overall performance tracking.

Excel Model link- Rural_Handmade_DATA.xlsm - Google Sheet

Instruction Manual

The excel model is created for assisting warehouse management. It has mainly 4 sections i.e. forecasting, inventory management, monthly performance tracker, and live tracking dashboard for different parameters.

Forecasting Workbook Manual

1- Forecasting Model Sheet

Model Result

The model calculates the three-month forecast data for any SKU.

Basic Methodology

The model is designed based on winter's prediction methodology.

It includes all three parameters- level, trend, and seasonality.

All three smoothing factors are considered i.e. Alpha, Beta, and Gamma

The Error is minimized post-process using a solver.

Getting Started

Enter the real demand for a month (let’s assume for April in 5th year)

Calculate level, trend, and seasonal factor for the same month)

Calculate RMSE for the same month.

Minimize RMSE value using Solver to optimize alpha, beta, and gamma values.

Calculate the forecast value for the next three months i.e May, June, and July.


Step-wise Instruction


Fill the real demand for a single month ex. Apr in 5th year


Drag the level, trend, and seasonal factors to auto-populate the value for the month.


Use the below-mentioned formula to calculate the demand forecast for the next three months ex. May, June & July.


Minimize the RMSE value for adjusting Alpha, Beta, and Gamma values using a solver.


Use the below-mentioned formula to calculate the demand forecast for the next three months ex. May, June & July.

May= (April level+1*April trend) *(April seasonal factor for the previous year)

June= (April level+2*April trend) *(April seasonal factor for the previous year)

July= (April level+3*April trend) *(April seasonal factor for the previous year)

Inventory Management Workbook Manual

2- Inventory management

Model Result

The model gives the day-to-day level inventory movement. It also triggers the reordering time and quantity needed to reorder with the supplier.

Basic Methodology

The model is designed based on EOQ and a continuous ordering system.

The service level is taken as 95% i.e. out of 100 demand cycles, It will be able to fulfill the demand completely in 95 cycles, and in the rest, the partial demand will be met.

Getting Started

Calculate the yearly demand and demand standard deviation using the backside calculation side

Calculate EOQ, No of Orders that need to be placed yearly, and Cycle time.

Calculate the safety stock and Reorder point for the ROP value setting.

In the inventory management sheet, fill the details of day-wise retail and inventory received (if any)

Trigger the reorder by EOQ value once the closing inventory of the day level goes down below ROP.

E0Q= ((2*Annual mean demand* Ordering Cost)/ (holding cost)) ^0.

No of orders= Annual mean demand/EOQ

Order cycle=365/No of orders

SS= z value @95% service level*(Std. deviation of demand over lead time)

ROP= mean demand *lead time + SS

Monthly Performance Tracker Workbook Manual

3- Monthly Performance Indicator

Model Result

The model gives the monthly financial performance in terms of operating profit. The profit track is reflected in two ways. Firstly, it shows the SKU-specific profit earned by the warehouse and in second way, It gives the overall profit calculation for a retail customer.

Basic Methodology

The monthly performance indicator sheet works on the basic idea of profit i.e. (Revenue - cost)/ cost. It allocates all the fixed and variable costs of warehouse management to SKUs in proportion to the revenue generated.

Getting Started

Update the no of specific SKUs sold wrt to customer data.

Update the overall manufacturing cost and selling price per SKU wrt to customer mapping.

Put the overall monthly warehouse fixed and variable management cost in "Warehouse total Facility Storage & Ops cost".

Dashboard Workbook Manual

4- Dashboard

Model Result

The model gives the day-to-day inventory received (If any), opening inventory, SKU sold, and Closing inventory visual representation. It is a dynamic dashboard that will keep on changing based on real-time data.

Basic Methodology

The dashboard works based on the data sheet generated i.e. forecasting, Inventory, and backside calculation.

Getting Started

Update the item received (if any) and SKU sold in the inventory management sheet on daily basis.

Update the real demand every month in the forecasting sheet.

Check the visual representation of each critical data in the live dashboard.

I Understand

By continuing to use this site you consent to the use of cookies.

Know More.